ORACLE 单机迁移从文件系统到裸设备实验环境,node1是生产库 10205,node3是新主机新库 11203环境准备,准备存储,创建裸设备裸设备规划/oradata/ketty/ketty/control01.ctl /dev/raw/raw1
/oradata/ketty/ketty/control02.ctl /dev/raw/raw2
/oradata/ketty/ketty/control03.ctl /dev/raw/raw3
/oradata/ketty/ketty/users01.dbf /dev/raw/raw5
/oradata/ketty/ketty/sysaux01.dbf /dev/raw/raw6
/oradata/ketty/ketty/undotbs01.dbf /dev/raw/raw7
/oradata/ketty/ketty/system01.dbf /dev/raw/raw8
/oradata/ketty/ketty/data00a.dbf /dev/raw/raw9
/oradata/ketty/ketty/redo01.log /dev/raw/raw10
/oradata/ketty/ketty/redo02.log /dev/raw/raw11
/oradata/ketty/ketty/redo03.log /dev/raw/raw12
/oradata/ketty/ketty/temp01.dbf /dev/raw/raw13
spfile /dev/raw/raw14
[root@node3 rules.d]# vi 60-raw.rules
[root@node3 rules.d]# cat 60-raw.rules
# Enter raw device bindings here.
#
# An example would be:
# ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
# ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="sdb2", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdb3", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sdb5", RUN+="/bin/raw /dev/raw/raw5 %N"
ACTION=="add", KERNEL=="sdb6", RUN+="/bin/raw /dev/raw/raw6 %N"
ACTION=="add", KERNEL=="sdb7", RUN+="/bin/raw /dev/raw/raw7 %N"
ACTION=="add", KERNEL=="sdb8", RUN+="/bin/raw /dev/raw/raw8 %N"
ACTION=="add", KERNEL=="sdb9", RUN+="/bin/raw /dev/raw/raw9 %N"
ACTION=="add", KERNEL=="sdb10", RUN+="/bin/raw /dev/raw/raw10 %N"
ACTION=="add", KERNEL=="sdb11", RUN+="/bin/raw /dev/raw/raw11 %N"
ACTION=="add", KERNEL=="sdb12", RUN+="/bin/raw /dev/raw/raw12 %N"
ACTION=="add", KERNEL=="sdb13", RUN+="/bin/raw /dev/raw/raw13 %N"
ACTION=="add", KERNEL=="sdb14", RUN+="/bin/raw /dev/raw/raw14 %N"
KERNEL=="raw*",OWNER=="oracle",GROUP=="dba",MODE=="660"
[root@node3 rules.d]# start_udev
Starting udev: [ OK ]
[root@node3 rules.d]# raw -qa
/dev/raw/raw1:bound to major 8, minor 17
/dev/raw/raw2:bound to major 8, minor 18
/dev/raw/raw3:bound to major 8, minor 19
/dev/raw/raw5:bound to major 8, minor 21
/dev/raw/raw6:bound to major 8, minor 22
/dev/raw/raw7:bound to major 8, minor 23
/dev/raw/raw8:bound to major 8, minor 24
/dev/raw/raw9:bound to major 8, minor 25
/dev/raw/raw10:bound to major 8, minor 26
/dev/raw/raw11:bound to major 8, minor 27
/dev/raw/raw12:bound to major 8, minor 28
/dev/raw/raw13:bound to major 8, minor 29
/dev/raw/raw14:bound to major 8, minor 30
生产库RMAN全备
[oracle@Node1 scripts]$ cat rman_full.sh
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin:
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export ORACLE_SID=ketty
rman target / nocatalog log /tmp/rman_full.log append<<EOF
run
{allocate channel c1 type disk;
allocate channel c2 type disk;
backup filesperset 3 database format '/rman/ketty/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/rman/ketty/arch_%d_%T_%s_%p' delete input ;
backup current controlfile format '/rman/ketty/ctl_%d_%T_%s_%p';
}
EOF
rman target / nocatalog log /tmp/rman_delete.log append<<EOF
allocate channel for maintenance type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-1';
delete noprompt obsolete;
EOF
[oracle@Node1 scripts]$ . rman_full.sh
connected to target database: KETTY (DBID=1901303438)
using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: c1
channel c1: sid=158 devtype=DISK
allocated channel: c2
channel c2: sid=141 devtype=DISK
Starting backup at 18-DEC-14
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/ketty/ketty/system01.dbf
input datafile fno=00005 name=/oradata/ketty/ketty/data00a.dbf
将备份集、归档、pfile拷贝到新主机
[oracle@Node1 ketty]$ scp * 192.168.20.30:/rman/ketty/
The authenticity of host '192.168.20.30 (192.168.20.30)' can't be established.
RSA key fingerprint is 96:79:ab:8f:bf:84:15:95:ca:fd:e7:6b:bd:58:00:00.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.20.30' (RSA) to the list of known hosts.
oracle@192.168.20.30's password:
full_KETTY_20141218_6_1 100% 371MB 14.8MB/s 00:25
full_KETTY_20141218_7_1 100% 285MB 15.8MB/s 00:18
full_KETTY_20141218_8_1 100% 6944KB 6.8MB/s 00:01
full_KETTY_20141218_9_1 100% 96KB 96.0KB/s 00:00
ctl_KETTY_20141218_10_1 100% 6944KB 6.8MB/s 00:01
sys@KETTY> create pfile from spfile;
[oracle@Node1 scripts]$ scp /oracle/app/oracle/product/10.2/db_1/dbs/initketty.ora 192.168.20.30:/oracle/app/oracle/product/11.2/db_home1/dbs/
oracle@192.168.20.30's password:
initketty.ora 100% 1102 1.1KB/s 00:00
[oracle@Node1 ketty]$ scp /arch/ketty/* 192.168.20.30:/arch/ketty/
oracle@192.168.20.30's password:
1_6_866414561.arc 100% 943KB 943.0KB/s 00:00
1_7_866414561.arc 100% 1024 1.0KB/s 00:00
1_8_866414561.arc 100% 1024 1.0KB/s 00:00
1_9_866414561.arc 100% 1024 1.0KB/s 00:00
新库修复控制文件 recoverdb
在新主机目标库 更改pfile,删掉adump,修改以下参数
*.background_dump_dest='/oracle/app/oracle/diag/rdbms/ketty/ketty/trace'
*.control_files='/oradata/ketty/ketty/control01.ctl','/oradata/ketty/ketty/control02.ctl','/oradata/ketty/ketty/control03.ctl'
*.core_dump_dest='/oracle/app/oracle/diag/rdbms/ketty/ketty/cdump'
*.log_archive_dest_10='location=/arch/ketty'
*.user_dump_dest='/oracle/app/oracle/diag/rdbms/ketty/ketty/trace'
在目标库恢复控制文件
[oracle@node3 rdbms]$ export ORACLE_SID=ketty
[oracle@node3 rdbms]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 18 17:06:23 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 283930624 bytes
Fixed Size 2227664 bytes
Variable Size 100663856 bytes
Database Buffers 176160768 bytes
Redo Buffers 4878336 bytes
[oracle@node3 dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 18 18:02:01 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: KETTY (not mounted)
RMAN> restore controlfile from '/rman/ketty/ctl_KETTY_20141218_10_1';
Starting restore at 18-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/ketty/ketty/control01.ctl
output file name=/oradata/ketty/ketty/control02.ctl
output file name=/oradata/ketty/ketty/control03.ctl
Finished restore at 18-DEC-14
SQL> alter database mount;
Database altered.
发现在新主机的控制文件还记录这原数据文件的位置
SQL> select FILE#,NAME from v$datafile;
FILE# NAME
---------- ----------------------------------------
1 /oradata/ketty/ketty/system01.dbf
2 /oradata/ketty/ketty/undotbs01.dbf
3 /oradata/ketty/ketty/sysaux01.dbf
4 /oradata/ketty/ketty/users01.dbf
5 /oradata/ketty/ketty/data00a.dbf
修改控制文件中记录的数据文件的位置,恢复数据库
RMAN> run
2> {allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> set newname for datafile 1 to '/dev/raw/raw8';
5> set newname for datafile 2 to '/dev/raw/raw7';
6> set newname for datafile 3 to '/dev/raw/raw6';
7> set newname for datafile 4 to '/dev/raw/raw5';
8> set newname for datafile 5 to '/dev/raw/raw9';
9> restore database;
10> switch datafile all;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=18 device type=DISK
allocated channel: c2
channel c2: SID=21 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 18-DEC-14
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /dev/raw/raw8
channel c1: restoring datafile 00005 to /dev/raw/raw9
channel c1: reading from backup piece /rman/ketty/full_KETTY_20141218_6_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to /dev/raw/raw7
channel c2: restoring datafile 00003 to /dev/raw/raw6
channel c2: restoring datafile 00004 to /dev/raw/raw5
channel c2: reading from backup piece /rman/ketty/full_KETTY_20141218_7_1
channel c1: piece handle=/rman/ketty/full_KETTY_20141218_6_1 tag=TAG20141218T164058
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:25
channel c2: piece handle=/rman/ketty/full_KETTY_20141218_7_1 tag=TAG20141218T164058
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:25
Finished restore at 18-DEC-14
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=866657558 file name=/dev/raw/raw8
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=866657558 file name=/dev/raw/raw7
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=866657558 file name=/dev/raw/raw6
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=866657558 file name=/dev/raw/raw5
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=866657558 file name=/dev/raw/raw9
released channel: c1
released channel: c2
查看数据文件已经存储在裸设备了
SQL> select FILE#,NAME from v$datafile;
FILE# NAME
---------- ----------------------------------------
1 /dev/raw/raw8
2 /dev/raw/raw7
3 /dev/raw/raw6
4 /dev/raw/raw5
5 /dev/raw/raw9
追加归档到目标库
RMAN> recover database;
Starting recover at 18-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
datafile 5 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /arch/ketty/1_6_866414561.arc
archived log for thread 1 with sequence 7 is already on disk as file /arch/ketty/1_7_866414561.arc
archived log for thread 1 with sequence 8 is already on disk as file /arch/ketty/1_8_866414561.arc
archived log for thread 1 with sequence 9 is already on disk as file /arch/ketty/1_9_866414561.arc
archived log file name=/arch/ketty/1_6_866414561.arc thread=1 sequence=6
archived log file name=/arch/ketty/1_7_866414561.arc thread=1 sequence=7
archived log file name=/arch/ketty/1_8_866414561.arc thread=1 sequence=8
archived log file name=/arch/ketty/1_9_866414561.arc thread=1 sequence=9
unable to find archived log
archived log thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2014 18:15:35
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 629155
从生产库传输归档到新库,recoverdb
sys@KETTY> alter system archive log current;
System altered.
sys@KETTY> /
System altered.
sys@KETTY> /
System altered.
[oracle@Node1 ketty]$ scp /arch/ketty/* 192.168.20.30:/arch/ketty/
oracle@192.168.20.30's password:
1_10_866414561.arc 100% 1794KB 1.8MB/s 00:00
1_11_866414561.arc 100% 1024 1.0KB/s 00:00
1_12_866414561.arc 100% 1024 1.0KB/s 00:00
1_6_866414561.arc 100% 943KB 943.0KB/s 00:00
1_7_866414561.arc 100% 1024 1.0KB/s 00:00
1_8_866414561.arc 100% 1024 1.0KB/s 00:00
1_9_866414561.arc 100% 1024 1.0KB/s 00:00
在目标库recover
RMAN> recover database;
Starting recover at 18-DEC-14
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
archived log file name=/arch/ketty/1_10_866414561.arc thread=1 sequence=10
archived log file name=/arch/ketty/1_11_866414561.arc thread=1 sequence=11
archived log file name=/arch/ketty/1_12_866414561.arc thread=1 sequence=12
unable to find archived log
archived log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2014 18:17:23
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13 and starting SCN of 631480
生产库停应用,切归档,传归档到新库
[oracle@Node1 ketty]$ lsnrctl stop ketty
[oracle@Node1 ketty]$ kill -9 `ps -ef grep ketty grep LOCAL=NO awk '{print $2}'`
断掉一切外围连接,dblink等
再次切归档,新库recoverdb,切一圈,客户看这也放心呀
sys@KETTY> alter system archive log current;
System altered.
sys@KETTY> /
System altered.
sys@KETTY> /
System altered.
sys@KETTY> /
[oracle@Node1 ketty]$ scp /arch/ketty/* 192.168.20.30:/arch/ketty/
oracle@192.168.20.30's password:
1_10_866414561.arc 100% 1794KB 1.8MB/s 00:00
1_11_866414561.arc 100% 1024 1.0KB/s 00:00
1_12_866414561.arc 100% 1024 1.0KB/s 00:00
1_13_866414561.arc 100% 7680 7.5KB/s 00:00
1_14_866414561.arc 100% 1024 1.0KB/s 00:00
1_15_866414561.arc 100% 1536 1.5KB/s 00:00
1_16_866414561.arc 100% 1024 1.0KB/s 00:00
1_6_866414561.arc 100% 943KB 943.0KB/s 00:00
1_7_866414561.arc 100% 1024 1.0KB/s 00:00
1_8_866414561.arc 100% 1024 1.0KB/s 00:00
1_9_866414561.arc 100% 1024 1.0KB/s 00:00
在目标库继续recover
RMAN> recover database;
Starting recover at 18-DEC-14
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
archived log file name=/arch/ketty/1_13_866414561.arc thread=1 sequence=13
archived log file name=/arch/ketty/1_14_866414561.arc thread=1 sequence=14
archived log file name=/arch/ketty/1_15_866414561.arc thread=1 sequence=15
archived log file name=/arch/ketty/1_16_866414561.arc thread=1 sequence=16
unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2014 18:24:52
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 631623
新库不完全恢复,同时升级
将redo 和临时文件更改到新的位置
SQL> select member from v$logfile;
MEMBER
-----------------------------------
/oradata/ketty/ketty/redo03.log
/oradata/ketty/ketty/redo02.log
/oradata/ketty/ketty/redo01.log
SQL> alter database rename file '/oradata/ketty/ketty/redo01.log' to '/dev/raw/raw10';
Database altered.
SQL> alter database rename file '/oradata/ketty/ketty/redo02.log' to '/dev/raw/raw11';
Database altered.
SQL> alter database rename file '/oradata/ketty/ketty/redo03.log' to '/dev/raw/raw12';
SQL> select name from v$tempfile;
NAME
----------------------------------------
/oradata/ketty/ketty/temp01.dbf
[oracle@node3 dbs]$ ll /oradata/ketty/ketty/
total 29732
-rw-r----- 1 oracle oinstall 9781248 Dec 18 18:32 control01.ctl
-rw-r----- 1 oracle oinstall 9781248 Dec 18 18:32 control02.ctl
-rw-r----- 1 oracle oinstall 9781248 Dec 18 18:32 control03.ctl
-rw-r----- 1 oracle oinstall 20979712 Dec 18 18:32 temp01.dbf
在目标库以升级模式打开,并重置日志
SQL> alter database open resetlogs upgrade;
SQL> @?/rdbms/catupgrd.sql
升级完之后检查各组件状态
SQL> select COMP_NAME,STATUS from dba_registry;
把控制文件、临时文件和spfile更改到裸设备。
SQL> alter database tempfile '/oradata/ketty/ketty/temp01.dbf' drop;
Database altered.
SQL> alter tablespace temp add tempfile '/dev/raw/raw13' size 20m;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw13
SQL> shutdown immediate
[oracle@node3 dbs]$ dd if=/oradata/ketty/ketty/control01.ctl of=/dev/raw/raw1
19104+0 records in
19104+0 records out
9781248 bytes (9.8 MB) copied, 2.71671 seconds, 3.6 MB/s
[oracle@node3 dbs]$ dd if=/oradata/ketty/ketty/control02.ctl of=/dev/raw/raw2
19104+0 records in
19104+0 records out
9781248 bytes (9.8 MB) copied, 2.90413 seconds, 3.4 MB/s
[oracle@node3 dbs]$ dd if=/oradata/ketty/ketty/control03.ctl of=/dev/raw/raw3
19104+0 records in
19104+0 records out
9781248 bytes (9.8 MB) copied, 2.85222 seconds, 3.4 MB/s
[oracle@node3 dbs]$ vi initketty.ora
*.control_files='/dev/raw/raw1','/dev/raw/raw2','/dev/raw/raw3'
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 283930624 bytes
Fixed Size 2227664 bytes
Variable Size 100663856 bytes
Database Buffers 176160768 bytes
Redo Buffers 4878336 bytes
SQL> create spfile='/dev/raw/raw14' from pfile;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
[oracle@node3 dbs]$ vi initketty.ora
[oracle@node3 dbs]$ cat initketty.ora
spfile='/dev/raw/raw1'
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 283930624 bytes
Fixed Size 2227664 bytes
Variable Size 100663856 bytes
Database Buffers 176160768 bytes
Redo Buffers 4878336 bytes
Database mounted.
Database opened.
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw8
/dev/raw/raw7
/dev/raw/raw6
/dev/raw/raw5
/dev/raw/raw9
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw13
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/dev/raw/raw12
/dev/raw/raw11
/dev/raw/raw10
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw3
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /dev/raw/raw14
ok 齐了