oracle从rac恢复单机,Oracle RAC异机恢复至单机

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;