如何透過rman的增量備份恢復dataguard中standby端的資料
很多正在使用dataguard的客戶,都會遇到一個棘手的問題: 在備份端與主庫同步的過程中由於網路原因或磁碟問題導致一個或多個歸檔日誌丟失,進而dataguard同步無法繼續。很多客戶都選擇了重新全庫恢復,並重新搭建dataguard。 如果我們的源資料庫非常大(超過100G的資料量),其實可以選擇一種更簡便並高效的恢復方法--透過rman的增量備份恢復dataguard中standby端的資料。
具體恢復過程如下:
1) Stop the managed recovery process (MRP) on the STANDBY database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2) Determine the SCN of the STANDBY database.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
3164433
SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(F.FHSCN)
----------------
3162298
comment:上面一個為控制檔案中記錄的SCN號,另一個為資料檔案頭記錄的SCN號, 我們需要選擇較小SCN號(3162298)的來備份。
3) Take an incremental backup of the PRIMARY database
RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4) Transfer all backup sets to STANDBY server
scp /tmp/ForStandby_* standby:/tmp
5) Catalog the backups in STANDBY controlfile.
RMAN> CATALOG START WITH '/tmp/ForStandby';
6) Recover the STANDBY database with the cataloged incremental backup:
RMAN> RECOVER DATABASE NOREDO;
7) In RMAN, connect to the PRIMARY database and create a standby control file backup:
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
8) Copy the standby control file backup to the STANDBY system.
9) Capture datafile information in STANDBY database.
We now need to refresh the standby controlfile from primary controlfile (for standby) backup. However, since the datafile names are likely different than primary, let's save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if there is any discrepancy. Run below query from Standby and save results for further use.
10) From RMAN, connect to STANDBY database and restore the standby control file:
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
11) Shut down the STANDBY database and startup mount:
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
scp /tmp/ForStandbyCTRL.bck standby:/tmp
12) Catalog datafiles in STANDBY if location/name of datafiles is different
Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, if the directory structure is different between the standby and primary or you are using Oracle managed file names, catalog the datafiles in STANDBY will do the necessary rename operations. If the primary and standby have identical structure and datafile names, this step can be skipped.
Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
To determine if any files have been added to Primary since the standby current scn:
SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298
If the above query returns with 0 zero rows, you can switch the datafiles. This will rename the datafiles to its correct name at the standby site:
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
13) Configure the STANDBY database to use flashback (optional)
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
14) On STANDBY database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
15) On the STANDBY database, start the MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
For more detailed info:
Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
1) Stop the managed recovery process (MRP) on the STANDBY database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2) Determine the SCN of the STANDBY database.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
3164433
SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(F.FHSCN)
----------------
3162298
comment:上面一個為控制檔案中記錄的SCN號,另一個為資料檔案頭記錄的SCN號, 我們需要選擇較小SCN號(3162298)的來備份。
3) Take an incremental backup of the PRIMARY database
RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4) Transfer all backup sets to STANDBY server
scp /tmp/ForStandby_* standby:/tmp
5) Catalog the backups in STANDBY controlfile.
RMAN> CATALOG START WITH '/tmp/ForStandby';
6) Recover the STANDBY database with the cataloged incremental backup:
RMAN> RECOVER DATABASE NOREDO;
7) In RMAN, connect to the PRIMARY database and create a standby control file backup:
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
8) Copy the standby control file backup to the STANDBY system.
9) Capture datafile information in STANDBY database.
We now need to refresh the standby controlfile from primary controlfile (for standby) backup. However, since the datafile names are likely different than primary, let's save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if there is any discrepancy. Run below query from Standby and save results for further use.
10) From RMAN, connect to STANDBY database and restore the standby control file:
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
11) Shut down the STANDBY database and startup mount:
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
scp /tmp/ForStandbyCTRL.bck standby:/tmp
12) Catalog datafiles in STANDBY if location/name of datafiles is different
Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, if the directory structure is different between the standby and primary or you are using Oracle managed file names, catalog the datafiles in STANDBY will do the necessary rename operations. If the primary and standby have identical structure and datafile names, this step can be skipped.
Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
To determine if any files have been added to Primary since the standby current scn:
SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298
If the above query returns with 0 zero rows, you can switch the datafiles. This will rename the datafiles to its correct name at the standby site:
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
13) Configure the STANDBY database to use flashback (optional)
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
14) On STANDBY database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
15) On the STANDBY database, start the MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
For more detailed info:
Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2141842/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 透過RMAN備份standby database成功恢復還原Database
- ORACLE利用STANDBY端RMAN備份進行資料恢復Oracle資料恢復
- 使用RMAN增量備份前滾STANDBY資料庫資料庫
- 直接透過備份恢復資料庫資料庫
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 只存在RMAN備份片的資料庫恢復過程資料庫
- rman備份恢復-rman恢復資料檔案測試
- oracle實驗記錄 (恢復-rman增量備份)Oracle
- 【RMAN】RMAN備份恢復3 RMAN增量備份指令碼與crontab計劃任務指令碼
- 透過搭建恢復目錄實現RMAN異地備份和恢復
- RMAN備份恢復——RAC環境資料庫的備份(zt)資料庫
- RMAN備份恢復--RAC環境資料庫的備份(十)資料庫
- RMAN備份恢復——RAC環境資料庫的備份(一)資料庫
- 使用innobackupex線上增量備份和再增量備份及恢復mysql資料庫MySql資料庫
- dg丟失歸檔,使用rman增量備份恢復
- RMAN恢復(rman只備份資料庫,但不備份歸檔,歸檔透過簡單的rsync或scp來傳送到異地備份)資料庫
- ORACLE 11G透過SCN做增量備份修復standby庫詳細過程Oracle
- dataguard中rman恢復的應用
- mysql innobackupex增量備份恢復MySql
- 【備份恢復】RMAN catalog 恢復目錄資料庫資料庫
- 從dataguard備份的恢復機制
- 備份與恢復--從備份的歸檔日誌中恢復資料
- 如何恢復Hyper Backup備份的資料
- rman恢復資料庫--用備份的控制檔案資料庫
- oracle rman備份恢復的例子Oracle
- 【RMAN】使用增量備份更新資料庫備份映象資料庫
- 【RMAN】Oracle12c之後,rman備份Dataguard備端恢復可能出現邏輯錯誤Oracle
- standby全庫rman備份檔案恢復到異機
- RMAN備份恢復典型案例——資料庫卡頓資料庫
- rman資料庫全庫備份與恢復資料庫
- 非RMAN熱備份資料庫和恢復資料庫
- Oracle資料庫備份與恢復之RMANOracle資料庫
- rman資料備份恢復學習筆記筆記
- RMAN備份恢復原理
- ORACLE中採用rman備份異機恢復資料庫詳細過程Oracle資料庫
- 【備份恢復】從備份恢復資料庫資料庫
- NOARCHIVELOG 模式下使用增量備份恢復資料庫Hive模式資料庫
- 用10.2.0.1RMAN全備恢復10.2.0.4資料庫,透過資料庫