数据迁移_老集群RAC迁移数据恢复到新集群RAC
作者：Eric
微信：loveoracle11g
1、把老集群RAC备份的数据远程拷贝到新集群RAC
[root@old-rac-node1 ~]# cd /backup/rman/orcl
[root@old-rac-node1 orcl]# ls -l *1119*
[root@old-rac-node1 orcl]# scp *1119* root@192.168.10.10:/backup/rman/orcl
密码：oracle
[root@rac-node1 ~]# su - oracle
[oracle@rac-node1 ~]$ cd /backup/rman/orcl
[oracle@rac-node1 orcl]$ ll
total 7357616
-rw-r----- 1 oracle asmadmin 242676224 Nov 16 02:55 arch_5dtiajdc_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 241586176 Nov 16 02:55 arch_5etiajdc_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 203652096 Nov 16 02:55 arch_5ftiajdg_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 19202048 Nov 16 02:55 ctl_file_5gtiajdi_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 5939224576 Nov 16 02:56 data_level0_59tiajbk_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 868433920 Nov 16 02:56 data_level0_5atiajbk_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 19202048 Nov 16 02:56 data_level0_5btiajbr_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 98304 Nov 16 02:56 data_level0_5ctiajbu_1_1_20181115.bak
-rw-r----- 1 oracle asmadmin 98304 Nov 16 02:56 orcl_spfile_5htiajdl_1_1_20181115.bak
[oracle@rac-node1 orcl]$ pwd
/backup/rman/orcl
2、新集群要恢复的数据库，要开启归档
# su - grid
# srvctl stop database -d orcl
---------------------------------------->node1
# su - oracle
# export ORACLE_SID=orcl1
# sqlplus / as sysdba
SQL> startup mount ;
SQL> alter database archivelog ;
---------------------------------------->node2
# su - oracle
# export ORACLE_SID=orcl2
# sqlplus / as sysdba
SQL> startup mount ;
SQL> alter database archivelog ;
---------------------------------------->node1
SQL> alter database open ;
---------------------------------------->node2
SQL> alter database open ;
3、停掉要恢复的数据库orcl
直接停掉 # srvctl stop database -d orcl
# su - grid
# crs_stat -t -v
# crsctl stat res -t
[oracle@rac-node1 ~]$ export ORACLE_SID=orcl1
[oracle@rac-node1 ~]$ sqlplus / as sysdba
SQL> shutdown immediate ;
[oracle@rac-node2 ~]$ export ORACLE_SID=orcl2
[oracle@rac-node2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate ;
启动到mount状态
[oracle@rac-node1 orcl]$ rman target /
RMAN> shutdown immediate ;
RMAN> startup mount ;
4、恢复参数文件spfile
RMAN> restore spfile to '+DATA/spfileorcl.ora' from '/backup/rman/orcl/orcl_spfile_5htiajdl_1_1_20181115.bak';
RMAN> shutdown immediate ;
RMAN> exit ;
[oracle@rac-node1 orcl]$ sqlplus / as sysdba
SQL> startup force nomount ;
ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA' SID='*';
ALTER SYSTEM SET CONTROL_FILES='+DATA','+FRA' SCOPE=SPFILE SID='*';
5、恢复控制文件controlfile
[oracle@rac-node1 orcl]$ rman target /
RMAN> startup force nomount ;
RMAN> restore controlfile from '/backup/rman/orcl/ctl_file_5gtiajdi_1_1_20181115.bak' ;
RMAN> alter database mount ;
6、恢复数据文件datafile
RMAN> catalog start with '/backup/rman/orcl/' ;
输入：yes
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
allocate channel c3 device type DISK;
allocate channel c4 device type DISK;
restore database;
switch datafile all;
switch tempfile all;
recover database delete archivelog;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2018 04:04:48
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 1939 and starting SCN of 105650523
再执行下run
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
allocate channel c3 device type DISK;
allocate channel c4 device type DISK;
set until sequence 1939 thread 2; # 问题在这
restore database;
switch datafile all;
switch tempfile all;
recover database delete archivelog;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
7、重命名redolog文件
SQL> set linesize 200 ;
SQL> select 'alter database rename file '''||MEMBER||''' to '''||'+data/orcl/onlinelog/'||regexp_substr(MEMBER,'[^\/]+',1,4)||''';' from v$logfile;
'ALTERDATABASERENAMEFILE'''||MEMBER||'''TO'''||'+DATA/orcl/ONLINELOG/'||REGEXP_SUBSTR(MEMBER,'[^\/]+',1,4)||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '+DATADG/orcl/onlinelog/group_1.261.967309327' to '+data/orcl/onlinelog/group_1.261.967309327';
alter database rename file '+FRADG/orcl/onlinelog/group_1.257.967309327' to '+data/orcl/onlinelog/group_1.257.967309327';
alter database rename file '+DATADG/orcl/onlinelog/group_2.265.967309327' to '+data/orcl/onlinelog/group_2.265.967309327';
alter database rename file '+FRADG/orcl/onlinelog/group_2.259.967309329' to '+data/orcl/onlinelog/group_2.259.967309329';
alter database rename file '+DATADG/orcl/onlinelog/group_3.262.967309409' to '+data/orcl/onlinelog/group_3.262.967309409';
alter database rename file '+FRADG/orcl/onlinelog/group_3.258.967309409' to '+data/orcl/onlinelog/group_3.258.967309409';
alter database rename file '+DATADG/orcl/onlinelog/group_4.266.967310049' to '+data/orcl/onlinelog/group_4.266.967310049';
alter database rename file '+FRADG/orcl/onlinelog/group_4.260.967310049' to '+data/orcl/onlinelog/group_4.260.967310049';
alter database rename file '+DATADG/orcl/onlinelog/group_5.268.967308939' to '+data/orcl/onlinelog/group_5.268.967308939';
alter database rename file '+FRADG/orcl/onlinelog/group_5.261.967308939' to '+data/orcl/onlinelog/group_5.261.967308939';
alter database rename file '+DATADG/orcl/onlinelog/group_6.269.967308939' to '+data/orcl/onlinelog/group_6.269.967308939';
'ALTERDATABASERENAMEFILE'''||MEMBER||'''TO'''||'+DATA/orcl/ONLINELOG/'||REGEXP_SUBSTR(MEMBER,'[^\/]+',1,4)||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '+FRADG/orcl/onlinelog/group_6.262.967308939' to '+data/orcl/onlinelog/group_6.262.967308939';
alter database rename file '+DATADG/orcl/onlinelog/group_7.270.967308941' to '+data/orcl/onlinelog/group_7.270.967308941';
alter database rename file '+FRADG/orcl/onlinelog/group_7.263.967308941' to '+data/orcl/onlinelog/group_7.263.967308941';
alter database rename file '+DATADG/orcl/onlinelog/group_8.271.967309023' to '+data/orcl/onlinelog/group_8.271.967309023';
alter database rename file '+FRADG/orcl/onlinelog/group_8.264.967309023' to '+data/orcl/onlinelog/group_8.264.967309023';
alter database rename file '+DATADG/orcl/onlinelog/group_9.272.967309023' to '+data/orcl/onlinelog/group_9.272.967309023';
alter database rename file '+FRADG/orcl/onlinelog/group_9.265.967309025' to '+data/orcl/onlinelog/group_9.265.967309025';
alter database rename file '+DATADG/orcl/onlinelog/group_10.273.967309025' to '+data/orcl/onlinelog/group_10.273.967309025';
alter database rename file '+FRADG/orcl/onlinelog/group_10.266.967309025' to '+data/orcl/onlinelog/group_10.266.967309025';
20 rows selected.
执行上面的输出sql
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database clear logfile group 10;
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING ; # 报错不管它
8、打开数据库
SQL> alter database open resetlogs ;
重启数据库
startup force ;
9、检查
检查参数文件spfile
SQL> show parameter spfile ;
检查控制文件controlfile
SQL> select NAME from v$controlfile ;
检查数据文件datafile
SQL> select NAME from v$datafile ;
检查日志文件logfile
SQL> select MEMBER from v$logfile ;
查看字符集
select userenv('LANGUAGE') FROM DUAL ;
集群实例状态
select instance_name ,status from gv$instance ;
检查集群状态
直接停掉# srvctl status database -d orcl
# srvctl status instance -d orcl -i orcl1
# srvctl status instance -d orcl -i orcl2
# srvctl start database -d orcl
# srvctl status database -d orcl
查看# su - grid
# crs_stat -t -v
# crsctl stat res -t