oracle 11g rac 恢复,11G RAC 异机恢复至单实例测试

1 背景描述：

将RAC 异机恢复至单实例，以检验数据的可用性。

1.1 RACDB数据库基本配置信息

主机节点1: scdb1  操作系统：AIX6100

节点1: scdb2  操作系统：AIX6100

数据库版本Oracle11.2.0.1 RAC

OS用户名/组uid=501(oracle) gid=500(oinstall) groups=502(dba),503(oper),504(asmdba)

数据库名RACDB

实例名RACDB1

RACDB2

数据库字符集ZHS16GBK

数据文件存储方式ASM

1.2 恢复目标机的情况：

主机csdb  操作系统：AIX6100

数据库版本Oracle11.2.0.1 单机

OS用户名/组uid=501(oracle) gid=500(oinstall) groups=502(dba),503(oper),504(asmdba)

数据库名RACDB

实例名RACDB

数据库字符集ZHS16GBK

数据文件存储方式ASM

2 恢复的相关信息：

2.1 确认备份的脚本是否正确备份

[scdb1:root]more bck_all

connect target sys/******@RACDB

connect catalog rman_RACDB/******@rman;

run

{

allocate channel t1 type 'sbt_tape'

parms 'ENV=(NSR_SERVER=xxxx)';

backup full database

filesperset 4

format 'full_%d_%U'

(database include current controlfile);

sql 'alter system archive log current';

backup archivelog all

format 'arch_%t_%s_%p'

skip inaccessible

delete input;

release channel t1;

}

3 恢复的详细步骤

开始恢复：

3.1 在rac库上创建pfile

SQL> create pfile='/tmp/initRACDB.ora' from spfile;

将pfile通过FTP传传到csdb上

3.2 在目标机上修改initRACDB.ora ，修改成如下：

RACDB1.__db_cache_size=754974720

RACDB1.__java_pool_size=33554432

RACDB1.__large_pool_size=16777216

RACDB1.__oracle_base='/software/oracle'#ORACLE_BASE set from environment

RACDB1.__pga_aggregate_target=3942645760

RACDB1.__sga_target=2936012800

RACDB1.__shared_io_pool_size=0

RACDB1.__shared_pool_size=2046820352

RACDB1.__streams_pool_size=33554432

*.audit_file_dest='/software/oracle/admin/RACDB/adump'

*.cluster_database=false

*.compatible='11.2.0.0.0'

*.control_files='+RACDBDG/RACDB/controlfile/current.256.716919673'

#*.control_files='/software/backup/controlfil01.ctl'

*.db_block_size=8192

*.db_create_file_dest='+RACDBDG'

*.db_domain=''

*.db_files=2048

*.db_name='RACDB'

*.db_recovery_file_dest='+RACDBDG'

*.db_recovery_file_dest_size=85899345920

*.diagnostic_dest='/software/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'

RACDB1.instance_number=1

RACDB1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.105)(PORT=1521))))'

*.memory_target=6871318528

*.O7_DICTIONARY_ACCESSIBILITY=FALSE

*.open_cursors=300

*.processes=1500

#*.remote_listener='RACDB-cluster-scan:1521'

*.remote_login_passwordfile='exclusive'

*.sessions=1655

RACDB1.thread=1

RACDB1.undo_tablespace='UNDOTBS1'’

3.3 在目标机建立相应目录

在csdb上的ASM，配置好RACDBDG，并在上面建立相应的目录，然后在/software/oracle/admin/RACDB/ 下也建立bdump等相应目录。用orapwd产生pwd密码文件。

建diskgroup

Grid> sqlplus / as sysasm

Create  diskgroup RACDBDG external redundancy disk ‘/dev/ /dev/rhdiskpower1’,’/dev/ rhdiskpower12’;

Exit

Asmcmd

Cd +RACDBDG

Cd RACDB

Mkdir controlfile

Mkdir datafile

Mkdir onlinelog

3.4 在目标机上用RMAN恢复

[csdb:oracle]rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RACDB (not mounted)

RMAN> connect catalog rman_RACDB/rman_RACDB@rman

connected to recovery catalog database

RMAN>    run {

2> allocate channel t1 type 'sbt_tape'

3>  parms 'ENV=(NSR_SERVER=xxxx,NSR_CLIENT=xxx)';

4> restore controlfile;

5> }

allocated channel: t1

channel t1: SID=574 device type=SBT_TAPE

channel t1: NMO v5.0.0.0

channel t1: starting datafile backup set restore

channel t1: restoring control file

channel t1: reading from backup piece mklnrbhi_1_1

channel t1: piece handle=mklnrbhi_1_1 tag=TAG20100914T013937

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:01:46

output file name=+RACDBDG/RACDB/controlfile/current.256.729681003

Finished restore at

released channel: t1

RMAN>  run {

2> allocate channel t1 type 'sbt_tape'

3>  parms 'ENV=(NSR_SERVER=xxxx)';

4> sql 'alter database mount';

5> set  until sequence 659 thread 1;

6> set newname for datafile  10 to  '+RACDBDG/RACDB/datafile/ts_xx1';

7> set newname for datafile  13 to  '+RACDBDG/RACDB/datafile/ts_xx2';

8> set newname for datafile  15 to  '+RACDBDG/RACDB/datafile/ts_xx3';

9> set newname for datafile  22 to  '+RACDBDG/RACDB/datafile/ts_xx4';

10> set newname for datafile  14 to  '+RACDBDG/RACDB/datafile/ts_ixx5';

11> set newname for datafile  17 to  '+RACDBDG/RACDB/datafile/ts_xx6';

12> set newname for datafile  23 to  '+RACDBDG/RACDB/datafile/ts_xx7;

13> set newname for datafile  24 to  '+RACDBDG/RACDB/datafile/ts_xx8';

14> set newname for datafile  6  to  '+RACDBDG/RACDB/datafile/ts_xx9';

15> set newname for datafile  8  to  '+RACDBDG/RACDB/datafile/ts_xx10';

16> set newname for datafile  16 to  '+RACDBDG/RACDB/datafile/ts_xx11';

17> set newname for datafile  19 to  '+RACDBDG/RACDB/datafile/ts_xx12';

18> set newname for datafile  7  to  '+RACDBDG/RACDB/datafile/ts_xx13';

19> set newname for datafile  9  to  '+RACDBDG/RACDB/datafile/ts_xx14';

20> set newname for datafile  18 to  '+RACDBDG/RACDB/datafile/ts_xx15';

21> set newname for datafile  20 to  '+RACDBDG/RACDB/datafile/ts_xx16';

22> set newname for datafile  3  to  '+RACDBDG/RACDB/datafile/undotbs1';

23> set newname for datafile  5  to  '+RACDBDG/RACDB/datafile/users';

24> set newname for datafile  11 to  '+RACDBDG/RACDB/datafile/ts_xx18';

25> set newname for datafile  21 to  '+RACDBDG/RACDB/datafile/ts_xx19';

26> set newname for datafile  1  to  '+RACDBDG/RACDB/datafile/system';

27> set newname for datafile  2  to  '+RACDBDG/RACDB/datafile/sysaux ;

28> set newname for datafile  4  to  '+RACDBDG/RACDB/datafile/undotbs2';

29> set newname for datafile  12 to  '+RACDBDG/RACDB/datafile/ts_xx20';

30> restore database;

31> switch datafile all;

32> }

allocated channel: t1

channel t1: SID=574 device type=SBT_TAPE

channel t1: NMO v5.0.0.0

sql statement: alter database mount

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at

Starting implicit crosscheck backup at

Finished implicit crosscheck backup at

Starting implicit crosscheck copy at

Finished implicit crosscheck copy at

searching for all files in the recovery area

cataloging files...

no files cataloged

中间过程略

.....

released channel: t1

3.5 恢复归档到指定的目录：

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     654

Next log sequence to archive   658

Current log sequence           658

SQL> alter system set log_archive_dest_1='location=/software/oracle/arch';

System altered.

SQL>

恢复节点2的归档日志

run{

allocate channel t1 type 'sbt_tape'

parms 'ENV=(NSR_SERVER=xxx,NSR_CLIENT=scdb2)';

set archivelog destination to '/software/oracle/arch';

restore archivelog from sequence 1023 thread 2 until sequence 1028 thread 2;

}

恢复节点1的归档日志

run{

allocate channel t1 type 'sbt_tape'

parms 'ENV=(NSR_SERVER=xxx,NSR_CLIENT=scdb2)';

set archivelog destination to '/software/oracle/arch';

restore archivelog from sequence 756 thread 1 until sequence 760 thread 1; }

3.6 将redolog改名：

[csdb:oracle]sqlplus / as sysdba

alter database rename file '+DGSYSTEM/RACDB/onlinelog/group_1.258.724171631'   to '+RACDBDG/RACDB/onlinelog/group_1.258.724171631';

alter database rename file '+dgarch/RACDB/onlinelog/group_2.1043.724171657' to '+RACDBDG/RACDB/onlinelog/group_2.1043.724171657';

alter database rename file '+dgarch/RACDB/onlinelog/group_1.431.724171637'  to '+RACDBDG/RACDB/onlinelog/group_1.431.724171637';

......

alter database rename file '+DGSYSTEM/RACDB/onlinelog/group_2.257.724171651'   to '+RACDBDG/RACDB/onlinelog/group_2.257.724171651';

alter database rename file '+dgarch/RACDB/onlinelog/group_10.547.724170047' to '+RACDBDG/RACDB/onlinelog/group_10.547.724170047';

3.7 打开数据库：

SQL>  recover database using backup controlfile until cancel;

ORA-00279: change 11321006301221 generated at 08/22/201x 02:29:54 needed for

thread 1

ORA-00289: suggestion : /software/oracle/arch/1_660_716919669.dbf

ORA-00280: change 11321006301221 for thread 1 is in sequence #660

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL>  alter database open resetlogs;

Database altered.

SQL>

3.8 删除多余的线程。

SQL> select THREAD#, STATUS, ENABLED from v$thread;

THREAD# STATUS ENABLED

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

1 OPEN   PUBLIC

2 CLOSED PUBLIC

SQL> select group# from v$log where thread#=2; --查出可以删除的日志文件．

SQL>ALTER DATABASE DISABLE THREAD 2; --disable将被删除日志文件的thread.

SQL> alter database drop logfile group 3; 删除日志组．

alter database drop logfile group 4;

alter database drop logfile group 8;

alter database drop logfile group 9；

alter database drop logfile group 10

3.9  删除不用的UNDO_TABLESPACE．

SQL> show parameter undo;  --看正在使用的，在前面已经移除了UNDO_TABLESPACES2．

SQL> select tablespace_name from dba_tablespaces where contents='UNDO'; －－查ＵＮＤＯ表空间．

SQL>drop tablespace UNDOTABS2 including contents and datafiles; －－删除ＵＮＤＯ表空间．

临时表空间处理．

SQL> alter tablespace temp add tempfile  '+RACDBDG/RACDB/tempfile

/temp01.dbf' SIZE 200M;

3.10 创建spfile,重新启动DB

SQL> Create spfile from memory;

File created.

SQL> startup force

ORACLE instance started.

Total System Global Area 6847938560 bytes

Fixed Size                  2219808 bytes

Variable Size            6090129632 bytes

Database Buffers          738197504 bytes

Redo Buffers               17391616 bytes

Database mounted.

Database opened.

至此恢复测试完成.