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.
 
至此恢復測試完成.