透過RMAN備份standby database成功恢復還原

前言：為了提升系統安全，通常會在primary DB+standby DB之外再做一份備份。但直接對primary DB備份可能會帶來效能問題，所以決定在standby DB(open_mode:READ ONLY WITH APPLY)跑RMAN備份。模擬primary DB+standby DB都掛掉，透過RMAN備份恢復DB
(Oracle 11.0.2.4)

操作步驟(實驗證明是錯誤的！正確方法在後面)：
standby中執行 backup database -> backup archivelog -> backup controlfile 
新恢復DB中執行 restore controlfile -> restore database -> recover database -> open (read only)

具體操作(錯誤)：
standby中
RMAN> backup database format='/data/rmanbak/fd_%U_%T.bak';
RMAN> backup archivelog from time 'sysdate-0.5' format 'al_%U_%T.bak'
RMAN> backup current controlfile format '/data/rmanbak/cf_%U_%T.bak';
新恢復DB中
RMAN> startup nomount;
RMAN> restore controlfile from '/data/rmanbak/cf_drt2a0st_1_1_20180507.bak';
RMAN> restore database;
RMAN> alter database mount;
RMAN> recover database;
Starting recover at 07-MAY-18
using channel ORA_DISK_1
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/07/2018 14:26:01
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 1129 and starting SCN of 11096139396 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1128 and starting SCN of 11096132481 found to restore
SQL> alter database open;    --OPEN時報錯data file 1需要恢復
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/ind/system01.dbf'   
SQL> recover standby database;    --recover時要求新的archivelog恢復，但因為模擬的原primary+standby掛掉了，沒法copy新的archivelog來應用
ORA-00279: change 11094270515 generated at 05/07/2018 09:30:53 needed for
thread 1
ORA-00289: suggestion : /data/ind/arch/1_1123_964343049.arc
ORA-00280: change 11094270515 for thread 1 is in sequence #1123
Specify log: {=suggested | filename | AUTO | CANCEL}

檢視controlfile SCN(v$database.CURRENT_SCN) > datafile SCN(v$datafile_header.CHECKPOINT_CHANGE#) ，說明了上面需要archivelog恢復現象
SQL> select OPEN_MODE,CONTROLFILE_TYPE,DATABASE_ROLE,to_char(CURRENT_SCN)  from v$database;
OPEN_MODE            CONTROL DATABASE_ROLE TO_CHAR(CURRENT_SCN)
-------------------- ------- ---------------- ----------------------------------------
MOUNTED              STANDBY PHYSICAL STANDBY 11096139395
SQL> select FILE#,to_char(CHECKPOINT_CHANGE#) SCN from v$datafile_header;
     FILE# SCN
---------- ----------------------------------------
         1 11094270515
         2 11094270515
         3 11094270515
         4 11094270515
         5 11094270515
         6 11094270515
         7 11094270515
         8 11094270515
         9 11094270515
        10 11094270515

準備用resetlogs方式open又報錯
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database
至此，驗證此方法不行。
------------------------------------------------------------------------------------------------------------------

正確方法操作步驟：
standby中執行 backup database -> backup controlfile -> backup archivelog
新恢復DB中執行 restore controlfile -> restore database ->catalog start with 'archivelog' -> recover database -> open (read only)
具體操作(正確)：
standby中
RMAN> backup database format='/data/rmanbak/fd_%U_%T.bak';
RMAN> backup current controlfile format '/data/rmanbak/cf_%U_%T.bak';
RMAN> backup archivelog from time 'sysdate-0.5' format 'al_%U_%T.bak'
新恢復DB中
RMAN> startup nomount;
RMAN> restore controlfile from '/data/rmanbak/cf_drt2a0st_1_1_20180507.bak';
RMAN> restore database;
RMAN> alter database mount;
RMAN> CATALOG START WITH '/data/rmanbak/al_dst2a7mn_1_1_20180507.bak';
searching for all files that match the pattern /data/rmanbak/al_dst2a7mn_1_1_20180507.bak
List of Files Unknown to the Database
=====================================
File Name: /data/rmanbak/al_dst2a7mn_1_1_20180507.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data/rmanbak/al_dst2a7mn_1_1_20180507.bak
RMAN> recover database;
SQL> alter database open;  --可以正常open(read only)，但然如需要切為主庫，啟用standby即可
Database altered.

檢視controlfile SCN(v$database.CURRENT_SCN) +1 = datafile SCN(v$datafile_header.CHECKPOINT_CHANGE#) 達到此一致可以OPEN。
當然如果對standby備份時取消掉實時應用(alter database recover managed standby database cancel;) ，也可以達到一致性目前，並recover open成功