RAC异机恢复至单机 --- **环境描述：** | 描述 | 主机名 | 实例名 | 版本 | IP | | ------ | ------- | ------ | --------- | --------------- | | 源端 | rac1 | racdb1 | RAC11204 | 192.168.100.155 | | 源端 | rac2 | racdb2 | RAC11204 | 192.168.100.156 | | 目标端 | server1 | racdb | 单机11204 | 192.168.100.101 | **准备RMAN全库备份脚本：** rman target / log=full_log << EOF run { allocate channel d1 device type disk; allocate channel d2 device type disk; allocate channel d3 device type disk; backup as compressed backupset database format '/home/oracle/rman/datafile_%U'; backup archivelog all format '/home/oracle/rman/arch_%U'; backup current controlfile format '/home/oracle/rman/ctr_%U'; release channel d1; release channel d2; release channel d3; } exit EOF **创建本地静态参数文件：** SQL> create pfile='/home/oracle/rman/pfile' from spfile; File created. **备份文件汇总如下：** [oracle@rac1 rman]$ ls arch_0dv7hjpg_1_1 arch_0fv7hjpg_1_1 arch_0ev7hjpg_1_1 datafile_04v7hir3_1_1 datafile_08v7hjnc_1_1 datafile_0av7hjnd_1_1 datafile_0cv7hjp0_1_1 datafile_06v7hir4_1_1 datafile_09v7hjnc_1_1 datafile_0bv7hjov_1_1 ctr_0gv7hjpl_1_1 pfile **压缩备份文件，并将其传输至目标端** [oracle@rac1 ~]$ tar cvf rman.tar.gz rman/ [oracle@rac1 ~]$ scp rman.tar.gz 192.168.100.101:/home/oracle/ **目标库解压：** [oracle@server1 ~]$ tar xf rman.tar.gz **在目标单机手动编写创建参数文件：** racdb.__db_cache_size=352321536 racdb.__java_pool_size=4194304 racdb.__large_pool_size=8388608 racdb.__oracle_base='/oracle/app' racdb.__pga_aggregate_target=335544320 racdb.__sga_target=503316480 racdb.__shared_io_pool_size=0 racdb.__shared_pool_size=125829120 racdb.__streams_pool_size=0 *.audit_file_dest='/oracle/app/admin/racdb/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oracle/app/oradata/racdb/control01.ctl','/oracle/app/oradata/racdb/control02.ctl' *.db_block_size=8192 *.db_name='racdb' *.diagnostic_dest='/oracle/app' *.memory_target=838860800 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=170 *.undo_tablespace='UNDOTBS1' **创建相应的目录：** [oracle@server1 dbs]$ mkdir /oracle/app/admin/racdb/adump -pv [oracle@server1 dbs]$ mkdir /oracle/app/oradata/racdb -pv **启动目标库至nomount状态：** SQL>startup nomount **目标库连接RMAN恢复控制文件：** [oracle@server1]$rman target / RMAN> restore controlfile from '/home/oracle/rman/ctr_0gv7hjpl_1_1'; RMAN> alter database mount; **构建RMAN恢复脚本流程：** **数据文件rename构建：** SQL> select name from v$datafile; NAME \-------------------------------------------------------------------------------- +DATA/racdb/system01.dbf +DATA/racdb/sysaux01.dbf +DATA/racdb/undotbs01.dbf +DATA/racdb/users01.dbf +DATA/racdb/example01.dbf +DATA/racdb/undotbs02.dbf **拼接语句：** **sqlplus：** SQL> set pages 0 SQL> set line 500 SQL> select q'[alter database rename ']' || name || q'[' to ]' || q'['/oracle/app/oradata/racdb/]' || substr(name,instr(name,'/',-1)+1) || q'[';]' from v$datafile; \------------------------------------------------------------------------------------------ alter database rename '+DATA/racdb/system01.dbf' to '/oracle/app/oradata/racdb/system01.dbf'; alter database rename '+DATA/racdb/sysaux01.dbf' to '/oracle/app/oradata/racdb/sysaux01.dbf'; alter database rename '+DATA/racdb/undotbs01.dbf' to '/oracle/app/oradata/racdb/undotbs01.dbf'; alter database rename '+DATA/racdb/users01.dbf' to '/oracle/app/oradata/racdb/users01.dbf'; alter database rename '+DATA/racdb/example01.dbf' to '/oracle/app/oradata/racdb/example01.dbf'; alter database rename '+DATA/racdb/undotbs02.dbf' to '/oracle/app/oradata/racdb/undotbs02.dbf'; **RMAN：** select q'[set newname for datafile ']' || name || q'[' to ]' || q'['/oracle/app/oradata/racdb/]' || substr(name,instr(name,'/',-1)+1) || q'[';]' from v$datafile; \------------------------------------------------------------------------------------------ set newname for datafile '+DATA/racdb/system01.dbf' to '/oracle/app/oradata/racdb/system01.dbf'; set newname for datafile '+DATA/racdb/sysaux01.dbf' to '/oracle/app/oradata/racdb/sysaux01.dbf'; set newname for datafile '+DATA/racdb/undotbs01.dbf' to '/oracle/app/oradata/racdb/undotbs01.dbf'; set newname for datafile '+DATA/racdb/users01.dbf' to '/oracle/app/oradata/racdb/users01.dbf'; set newname for datafile '+DATA/racdb/example01.dbf' to '/oracle/app/oradata/racdb/example01.dbf'; set newname for datafile '+DATA/racdb/undotbs02.dbf' to '/oracle/app/oradata/racdb/undotbs02.dbf'; **RMAN脚本如下：** recovery.sh: rman target / log=recover_log << EOF run { allocate channel d1 device type disk; allocate channel d2 device type disk; allocate channel d3 device type disk; **crosscheck backup;** **delete expired backup;** **crosscheck archivelog all;** **delete expired archivelog all;** **catalog start with '/home/oracle/rman';** set newname for datafile '+DATA/racdb/system01.dbf' to '/oracle/app/oradata/racdb/system01.dbf'; set newname for datafile '+DATA/racdb/sysaux01.dbf' to '/oracle/app/oradata/racdb/sysaux01.dbf'; set newname for datafile '+DATA/racdb/undotbs01.dbf' to '/oracle/app/oradata/racdb/undotbs01.dbf'; set newname for datafile '+DATA/racdb/users01.dbf' to '/oracle/app/oradata/racdb/users01.dbf'; set newname for datafile '+DATA/racdb/example01.dbf' to '/oracle/app/oradata/racdb/example01.dbf'; set newname for datafile '+DATA/racdb/undotbs02.dbf' to '/oracle/app/oradata/racdb/undotbs02.dbf'; release channel d1; release channel d2; release channel d3; restore database; switch datafile all; } exit EOF **执行recover：** RMAN> recover database; Starting recover at 11-AUG-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=192 device type=DISK starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=2 sequence=46 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=58 channel ORA_DISK_1: reading from backup piece /home/oracle/rman/arch_0fv7hjpg_1_1 channel ORA_DISK_1: piece handle=/home/oracle/rman/arch_0fv7hjpg_1_1 tag=TAG20200810T201231 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/oracle/app/product/11.2.0/db_1/dbs/arch1_58_1046190639.dbf thread=1 sequence=58 archived log file name=/oracle/app/product/11.2.0/db_1/dbs/arch2_46_1046190639.dbf thread=2 sequence=46 unable to find archived log archived log thread=1 sequence=59 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/11/2020 05:23:21 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 59 and starting SCN of 2470036 **查看SCN信息：** SQL> select file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 2470036 2 2470036 3 2470036 4 2470036 5 2470036 6 2470036 6 rows selected. SQL> select file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 2470036 2 2470036 3 2470036 4 2470036 5 2470036 6 2470036 **数据文件头一致，尝试打开：** SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00349: failure obtaining block size for '+DATA/racdb/redo01.log' **重建控制文件：** SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt'; 修改为如下： STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS ARCHIVELOG MAXLOGFILES 192 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 32 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oracle/app/oradata/racdb/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/oracle/app/oradata/racdb/redo02.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oracle/app/oradata/racdb/system01.dbf', '/oracle/app/oradata/racdb/sysaux01.dbf', '/oracle/app/oradata/racdb/undotbs01.dbf', '/oracle/app/oradata/racdb/users01.dbf', '/oracle/app/oradata/racdb/example01.dbf', '/oracle/app/oradata/racdb/undotbs02.dbf' CHARACTER SET ZHS16GBK ; **一致性关库：** SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. **脚本创建控制文件：** SQL> @ctl.txt ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 494931024 bytes Database Buffers 331350016 bytes Redo Buffers 6565888 bytes Control file created. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: **ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled** **解决办法：** SQL>alter database add logfile thread 2 group 3 '/oracle/app/oradata/racdb/redo03_2.log' size 50M SQL>alter database add logfile thread 2 group 4 '/oracle/app/oradata/racdb/redo04_2.log' size 50M SQL> alter database open resetlogs; Database altered. SQL>alter database drop logfile group 3; SQL>alter database drop logfile group 4; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/app/oradata/racdb/temp01.dbf' size 100M autoextend on;