背景:xx项目需要进行数据库迁移，未避免停机时间过长，方案采用rman+恢复归档进行数据库迁移。总体步骤是：白天对单机进行全备后备份归档，然后进行目标rac环境恢复数据库，晚上确定好停机时间后，停止应用，备份归档后拿到目标rac环境进行归档恢复，恢复完毕将单机改成rac，通知业务进行应用验证。
恢复步骤如下：
1.源库先发起备份并在目标库创建adump目录
2.恢复
2.1恢复控制文件
SQL> startup nomount;
rman>restore controlfile from '/dump/CTLBAK_xxxxxxx_20201026_22_1';
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=+DATADG/xxxxxxx/controlfile/current.306.1055693865 output file name=+DATADG/xxxxxxx/controlfile/current.305.1055693865 output file name=+DATADG/xxxxxxx/controlfile/current.304.1055693865 Finished restore at 05-NOV-20
2.2将数据库mount
rman>alter database mount;
2.3检查备份情况
RMAN> list backup summary; --查看已有备份集 List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B F A DISK 26-OCT-20 1 1 NO CTL_BAK 2 B F A DISK 26-OCT-20 1 1 NO DB_FULL_BAK 3 B F A DISK 26-OCT-20 1 1 NO DB_FULL_BAK 4 B F A DISK 26-OCT-20 1 1 NO DB_FULL_BAK 5 B A A DISK 26-OCT-20 1 1 NO ARCH_BAK RMAN> crosscheck backup; --crosscheck备份集，发现已全部标记为EXPIRED allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=6343 device type=DISK crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/arch/backup/CTLBAK_xxxxxxx_20201026_17_1 RECID=1 STAMP=1054835687 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/arch/backup/FULLBAK_xxxxxxx_20201026_18_1 RECID=2 STAMP=1054835899 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/arch/backup/FULLBAK_xxxxxxx_20201026_19_1 RECID=3 STAMP=1054839754 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/arch/backup/FULLBAK_xxxxxxx_20201026_20_1 RECID=4 STAMP=1054843361 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/arch/backup/ARCHBAK_xxxxxxx_20201026_21_1 RECID=5 STAMP=1054843374 Crosschecked 5 objects RMAN> list backup summary; --备份集中Status=X表示备份集已失效 List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B F X DISK 26-OCT-20 1 1 NO CTL_BAK 2 B F X DISK 26-OCT-20 1 1 NO DB_FULL_BAK 3 B F X DISK 26-OCT-20 1 1 NO DB_FULL_BAK 4 B F X DISK 26-OCT-20 1 1 NO DB_FULL_BAK 5 B A X DISK 26-OCT-20 1 1 NO ARCH_BAK RMAN> delete noprompt expired backup; --删除expired的备份集信息 RMAN> catalog backuppiece '/dump/FULLBAK_xxxxxxx_20201105_36_1','/dump/FULLBAK_xxxxxxx_20201105_37_1','/dump/ARCHBAK_xxxxxxx_20201105_39_1'; --注册单个备份片 RMAN> catalog start with '/dump/'; --注册整个目录
2.4编写恢复脚本并执行
rman target / log=/dump/recover1103.log<<EOF run{ set newname for datafile '/oradata/xxxxxxx/system01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/sysaux01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/undotbs01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/users01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_telnetopen01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_index01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen01.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_index02.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_index03.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_index04.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen02.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen03.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen04.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen05.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen06.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen07.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen08.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen09.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_snmpopen10.dbf' to '+DATADG'; set newname for datafile '/oradata/xxxxxxx/tbs_ogg.dbf' to '+DATADG'; set newname for tempfile '/oradata/xxxxxxx/temp01.dbf' to '+DATADG'; restore database; switch datafile all; switch tempfile all; } EOF
2.5 recover数据库
RMAN> recover database;
2.6 源库停应用断监听杀会话并备份归档文件
2.7 追加归档并恢复
RMAN> list backup of archivelog all; RMAN> catalog backuppiece '/dump/ARCHBAK_xxxxxxx_20201105_42_1'; RMAN> recover database; unable to find archived log archived log thread=1 sequence=1132 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/27/2020 11:49:10 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1132 and starting SCN of 1544048253 recover database until scn 1544048253;
3. 恢复1节点
3.1 修改redo组，重命名数据文件
alter database rename file '/oradata/xxxxxxx/redo11.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo12.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo21.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo22.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo31.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo32.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo41.dbf' to '+DATADG'; alter database rename file '/oradata/xxxxxxx/redo42.dbf' to '+DATADG'; alter database drop standby logfile group 6; alter database drop standby logfile group 7; alter database drop standby logfile group 8; ##alter database rename file '/oradata/xxxxxxx/standby02.dbf' to '+DATADG'; ##alter database rename file '/oradata/xxxxxxx/standby03.dbf' to '+DATADG'; ##alter database rename file '/oradata/xxxxxxx/standby04.dbf' to '+DATADG';
3.2 打开一节点
RMAN> alter database open resetlogs; database opened
4. rac添加2节点
4.1 添加2节点的redo组
alter database add logfile thread 2 group 5 ('+DATADG','+DATADG') SIZE 2048M; alter database add logfile thread 2 group 6 ('+DATADG','+DATADG') SIZE 2048M; alter database add logfile thread 2 group 7 ('+DATADG','+DATADG') SIZE 2048M; alter database add logfile thread 2 group 8('+DATADG','+DATADG') SIZE 2048M; ##alter database add logfile thread 2 group 5 ('+DATADG','+DATADG') SIZE 2048M; ##alter database add logfile thread 2 group 9 ('+DATADG','+DATADG') SIZE 2048M; ##alter database add logfile thread 2 group 10 ('+DATADG','+DATADG') SIZE 2048M; ##alter database add logfile thread 2 group 11('+DATADG','+DATADG') SIZE 2048M;
4.2 启动2节点
sql> select thread#,status,enabled from v$thread; sql> alter database enable thread 2;
4.3 修改rac参数
alter system set thread=1 scope=spfile sid='xxxxxxx1'; alter system set thread=2 scope=spfile sid='xxxxxxx2'; alter system set instance_number=1 scope=spfile sid='xxxxxxx1'; alter system set instance_number=2 scope=spfile sid='xxxxxxx2'; alter system set cluster_database_instances=2 scope=spfile; alter system set cluster_database=true scope=spfile;
4.4 添加undo表空间并设置各节点的默认undo
create undo tablespace undotbs2 datafile '+DATADG' SIZE 30G AUTOEXTEND OFF; alter system set undo_tablespace='UNDOTBS1' scope=spfile sid='xxxxxxx1'; alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='xxxxxxx2';
4.5 创建spfile，修改两个节点的pfile指向asm
create pfile from spfile; create spfile='+DATADG' from pfile; cat initxxxxxxx1.ora SPFILE='+DATADG/xxxxxxx/parameterfile/spfile.275.1055714323' initxxxxxxx2.ora SPFILE='+DATADG/xxxxxxx/parameterfile/spfile.275.1055714323'
4.6 打开数据库，测试两个节点是否正常
4.7 在rac里面添加数据库和实例
srvctl add database -d xxxx-o /oracle/app/oracle/product/11.2.0/db -p +DATADG/xxxx/parameterfile/spfile.275.1055714323 srvctl add instance -d xxxx-i xxxxx-n xxxxx srvctl add instance -d xxxx-i xxxxx-n xxxxx
4.8 修改temp表空间并srvctl重启数据库
SQL> alter database tempfile '/oradata/xxxxxxx/temp01.dbf' drop; Database altered. SQL> alter tablespace temp add tempfile '+DATADG' SIZE 30G AUTOEXTEND OFF; Tablespace altered.