Oracle 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 file ‘]’ || name || q’[’ to ]’ || q’[’/oracle/app/oradata/racdb/]’ || substr(name,instr(name,’/’,-1)+1) || q’[’;]’ from v$datafile;

----------------------------------------------------------------------------------------------------------------

alter database rename file ‘+DATA/racdb/system01.dbf’ to ‘/oracle/app/oradata/racdb/system01.dbf’;

alter database rename file ‘+DATA/racdb/sysaux01.dbf’ to ‘/oracle/app/oradata/racdb/sysaux01.dbf’;

alter database rename file ‘+DATA/racdb/undotbs01.dbf’ to ‘/oracle/app/oradata/racdb/undotbs01.dbf’;

alter database rename file ‘+DATA/racdb/users01.dbf’ to ‘/oracle/app/oradata/racdb/users01.dbf’;

alter database rename file ‘+DATA/racdb/example01.dbf’ to ‘/oracle/app/oradata/racdb/example01.dbf’;

alter database rename file ‘+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;

ORA-01623：

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/app/oradata/racdb/temp01.dbf’ size 100M autoextend on;