利用增量備份恢復gap歸檔丟失DG
故障現象：
某客戶data guard歸檔出現gap，悲劇的是丟失的歸檔在主庫上被rman備份時刪除了，丟失的歸檔大約有20幾個，資料庫大小約2T，如果重建DG將非常耗時間，因此決定利用增量備份的方式恢復DG，主要步驟如下：
1.備份備庫spfile檔案
SQL> create pfile='/home/ora/pfile_before_recover_dg.ora' from spfile;
File created.
JZH.__db_cache_size=1476395008
JZH.__java_pool_size=16777216
JZH.__large_pool_size=16777216
JZH.__shared_pool_size=251658240
JZH.__streams_pool_size=16777216
*.audit_file_dest='/jzhdgdump/JZH/adump'
*.background_dump_dest='/jzhdgdump/JZH/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oradata/JZH/standby.ctl'#Restore Controlfile
*.core_dump_dest='/jzhdgdump/JZH/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_files=1000
*.db_name='JZH'
*.db_unique_name='jzhdg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JZHXDB)'
*.fal_client='jzhdg'
*.FAL_SERVER='JZH1','JZH2'
*.job_queue_processes=30
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(JZH,jzhdg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jzhdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=JZH1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_RO
LE) DB_UNIQUE_NAME=JZH'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.log'
*.log_archive_max_processes=10
*.log_buffer=524288
*.log_file_name_convert='/oralog/JZH/','/oradata/JZH/'
*.open_cursors=3000
*.pga_aggregate_target=524288000
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.sessions=1655
*.sga_target=1782579200
*.STANDBY_ARCHIVE_DEST='/oradata/standby_arch'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.thread=1
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/jzhdgdump/JZH/udump'
2、檢視當前備庫scn
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
19652214434
3、檢視缺失的archivelog
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 160163 160170
2 151303 151309
4、在主庫執增量備份
[ora@jzhRAC1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat May 23 18:10:52 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JZH (DBID=2072634265)
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> allocate channel d3 type disk;
6> backup as compressed backupset incremental from SCN 19652214434 database format '/home/ora/full_db_%d_%T_%s.bak' include current controlfile for standby
filesperset=5 tag 'FOR STANDBY';
7> release channel d1;
8> release channel d2;
9> release channel d3;
10> }
channel d1: backup set complete, elapsed time: 00:00:35
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00124 name=/oradata/JZH/TP_CTEST15.ORA
input datafile fno=00186 name=/oradata/JZH/TP_LEARNING01.ORA
input datafile fno=00187 name=/oradata/JZH/TP_LEARNING02.ORA
input datafile fno=00188 name=/oradata/JZH/TP_LEARNING03.ORA
input datafile fno=00191 name=/oradata/JZH/TP_LEARNING06.ORA
channel d1: starting piece 1 at 23-MAY-15
channel d3: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232604.bak tag=FOR STANDBY comment=NONE
channel d3: backup set complete, elapsed time: 00:00:36
channel d3: starting compressed full datafile backupset
channel d3: specifying datafile(s) in backupset
input datafile fno=00192 name=/oradata/JZH/TP_LEARNING07.ORA
input datafile fno=00239 name=/oradata/JZH/TP_LEARNING08.ORA
input datafile fno=00250 name=/oradata/JZH/TP_LEARNING09.ORA
input datafile fno=00261 name=/oradata/JZH/TP_CTEST16.ORA
input datafile fno=00267 name=/oradata/JZH/TP_LEARNING10.ORA
channel d3: starting piece 1 at 23-MAY-15
channel d2: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232602.bak tag=FOR STANDBY comment=NONE
channel d2: backup set complete, elapsed time: 00:01:08
channel d2: starting compressed full datafile backupset
channel d2: specifying datafile(s) in backupset
input datafile fno=00272 name=/oradata/JZH/TP_CTEST17.ORA
input datafile fno=00284 name=/oradata/JZH/TP_CTEST18.ORA
input datafile fno=00288 name=/oradata/JZH/TP_LEARNING11.ORA
input datafile fno=00296 name=/oradata/JZH/TP_CTEST19.ORA
input datafile fno=00313 name=/oradata/JZH/TP_LEARNING12.ORA
channel d2: starting piece 1 at 23-MAY-15
channel d1: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232606.bak tag=FOR STANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:32
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00338 name=/oradata/JZH/TP_LEARNING15.ORA
input datafile fno=00342 name=/oradata/JZH/TP_CTEST22.ORA
channel d1: starting piece 1 at 23-MAY-15
channel d3: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232607.bak tag=FOR STANDBY comment=NONE
channel d3: backup set complete, elapsed time: 00:00:32
channel d3: starting compressed full datafile backupset
channel d3: specifying datafile(s) in backupset
including standby control file in backupset
channel d3: starting piece 1 at 23-MAY-15
channel d1: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232609.bak tag=FOR STANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:06
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel d1: starting piece 1 at 23-MAY-15
channel d3: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232610.bak tag=FOR STANDBY comment=NONE
channel d3: backup set complete, elapsed time: 00:00:08
channel d1: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232611.bak tag=FOR STANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
channel d2: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232608.bak tag=FOR STANDBY comment=NONE
channel d2: backup set complete, elapsed time: 00:00:32
Finished backup at 23-MAY-15
released channel: d1
released channel: d2
released channel: d3
5、將備份傳至備庫
scp full_db_JZH_20150523_*.bak 192.168.110.115:~/20150523_recover/
6、恢復備庫
[ora@jzhDG ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat May 23 20:17:59 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JZH (DBID=2072634265, not open)
RMAN> catalog start with '/home/ora/20150523_recover';
[ora@jzhDG ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat May 23 20:25:44 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JZH (DBID=2072634265, not open)
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> allocate channel d3 type disk;
6> restore standby controlfile to '/home/ora/control01.ctl';
7> recover database noredo;
8> release channel d1;
9> release channel d2;
10> release channel d3;
11> }
7、關閉備庫，將恢復出來的control01.ctl覆蓋備庫控制檔案
[ora@jzhDG ~]$ cp control01.ctl /oradata/JZH/standby.ctl
8、啟動備庫至mount狀態
SQL> startup mount
ORACLE instance started.
Total System Global Area 1795162112 bytes
Fixed Size 2084648 bytes
Variable Size 301990104 bytes
Database Buffers 1476395008 bytes
Redo Buffers 14692352 bytes
Database mounted.
9、檢視gap
SQL> select * from v$archive_gap;
no rows selected
10、開啟備庫recover模式
SQL> recover managed standby database disconnect from session;
Media recovery complete.
11、檢視日誌，正在恢復
Sat May 23 21:17:53 2015
Media Recovery Log /oradata/standby_arch/1_161210_706979804.log
Sat May 23 21:18:15 2015
Media Recovery Log /oradata/standby_arch/1_161211_706979804.log
Media Recovery Log /oradata/standby_arch/2_152328_706979804.log
Sat May 23 21:19:14 2015
Media Recovery Log /oradata/standby_arch/1_161212_706979804.log
Sat May 23 21:20:06 2015
Media Recovery Log /oradata/standby_arch/2_152329_706979804.log
Sat May 23 21:20:29 2015
Media Recovery Log /oradata/standby_arch/1_161213_706979804.log
Media Recovery Log /oradata/standby_arch/2_152330_706979804.log
Sat May 23 21:20:50 2015
Media Recovery Log /oradata/standby_arch/2_152331_706979804.log
Media Recovery Log /oradata/standby_arch/1_161214_706979804.log
Sat May 23 21:21:10 2015
Media Recovery Log /oradata/standby_arch/1_161215_706979804.log
Sat May 23 21:21:20 2015
Media Recovery Log /oradata/standby_arch/2_152332_706979804.log
Sat May 23 21:21:31 2015
Media Recovery Log /oradata/standby_arch/1_161216_706979804.log
Sat May 23 21:21:41 2015
Media Recovery Log /oradata/standby_arch/2_152333_706979804.log
Sat May 23 21:21:53 2015
Media Recovery Waiting for thread 1 sequence 161217
某客戶data guard歸檔出現gap，悲劇的是丟失的歸檔在主庫上被rman備份時刪除了，丟失的歸檔大約有20幾個，資料庫大小約2T，如果重建DG將非常耗時間，因此決定利用增量備份的方式恢復DG，主要步驟如下：
1.備份備庫spfile檔案
SQL> create pfile='/home/ora/pfile_before_recover_dg.ora' from spfile;
File created.
JZH.__db_cache_size=1476395008
JZH.__java_pool_size=16777216
JZH.__large_pool_size=16777216
JZH.__shared_pool_size=251658240
JZH.__streams_pool_size=16777216
*.audit_file_dest='/jzhdgdump/JZH/adump'
*.background_dump_dest='/jzhdgdump/JZH/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oradata/JZH/standby.ctl'#Restore Controlfile
*.core_dump_dest='/jzhdgdump/JZH/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_files=1000
*.db_name='JZH'
*.db_unique_name='jzhdg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JZHXDB)'
*.fal_client='jzhdg'
*.FAL_SERVER='JZH1','JZH2'
*.job_queue_processes=30
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(JZH,jzhdg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jzhdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=JZH1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_RO
LE) DB_UNIQUE_NAME=JZH'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.log'
*.log_archive_max_processes=10
*.log_buffer=524288
*.log_file_name_convert='/oralog/JZH/','/oradata/JZH/'
*.open_cursors=3000
*.pga_aggregate_target=524288000
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.sessions=1655
*.sga_target=1782579200
*.STANDBY_ARCHIVE_DEST='/oradata/standby_arch'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.thread=1
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/jzhdgdump/JZH/udump'
2、檢視當前備庫scn
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
19652214434
3、檢視缺失的archivelog
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 160163 160170
2 151303 151309
4、在主庫執增量備份
[ora@jzhRAC1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat May 23 18:10:52 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JZH (DBID=2072634265)
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> allocate channel d3 type disk;
6> backup as compressed backupset incremental from SCN 19652214434 database format '/home/ora/full_db_%d_%T_%s.bak' include current controlfile for standby
filesperset=5 tag 'FOR STANDBY';
7> release channel d1;
8> release channel d2;
9> release channel d3;
10> }
channel d1: backup set complete, elapsed time: 00:00:35
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00124 name=/oradata/JZH/TP_CTEST15.ORA
input datafile fno=00186 name=/oradata/JZH/TP_LEARNING01.ORA
input datafile fno=00187 name=/oradata/JZH/TP_LEARNING02.ORA
input datafile fno=00188 name=/oradata/JZH/TP_LEARNING03.ORA
input datafile fno=00191 name=/oradata/JZH/TP_LEARNING06.ORA
channel d1: starting piece 1 at 23-MAY-15
channel d3: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232604.bak tag=FOR STANDBY comment=NONE
channel d3: backup set complete, elapsed time: 00:00:36
channel d3: starting compressed full datafile backupset
channel d3: specifying datafile(s) in backupset
input datafile fno=00192 name=/oradata/JZH/TP_LEARNING07.ORA
input datafile fno=00239 name=/oradata/JZH/TP_LEARNING08.ORA
input datafile fno=00250 name=/oradata/JZH/TP_LEARNING09.ORA
input datafile fno=00261 name=/oradata/JZH/TP_CTEST16.ORA
input datafile fno=00267 name=/oradata/JZH/TP_LEARNING10.ORA
channel d3: starting piece 1 at 23-MAY-15
channel d2: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232602.bak tag=FOR STANDBY comment=NONE
channel d2: backup set complete, elapsed time: 00:01:08
channel d2: starting compressed full datafile backupset
channel d2: specifying datafile(s) in backupset
input datafile fno=00272 name=/oradata/JZH/TP_CTEST17.ORA
input datafile fno=00284 name=/oradata/JZH/TP_CTEST18.ORA
input datafile fno=00288 name=/oradata/JZH/TP_LEARNING11.ORA
input datafile fno=00296 name=/oradata/JZH/TP_CTEST19.ORA
input datafile fno=00313 name=/oradata/JZH/TP_LEARNING12.ORA
channel d2: starting piece 1 at 23-MAY-15
channel d1: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232606.bak tag=FOR STANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:32
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00338 name=/oradata/JZH/TP_LEARNING15.ORA
input datafile fno=00342 name=/oradata/JZH/TP_CTEST22.ORA
channel d1: starting piece 1 at 23-MAY-15
channel d3: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232607.bak tag=FOR STANDBY comment=NONE
channel d3: backup set complete, elapsed time: 00:00:32
channel d3: starting compressed full datafile backupset
channel d3: specifying datafile(s) in backupset
including standby control file in backupset
channel d3: starting piece 1 at 23-MAY-15
channel d1: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232609.bak tag=FOR STANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:06
channel d1: starting compressed full datafile backupset
channel d1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel d1: starting piece 1 at 23-MAY-15
channel d3: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232610.bak tag=FOR STANDBY comment=NONE
channel d3: backup set complete, elapsed time: 00:00:08
channel d1: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232611.bak tag=FOR STANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
channel d2: finished piece 1 at 23-MAY-15
piece handle=/home/ora/full_db_JZH_20150523_232608.bak tag=FOR STANDBY comment=NONE
channel d2: backup set complete, elapsed time: 00:00:32
Finished backup at 23-MAY-15
released channel: d1
released channel: d2
released channel: d3
5、將備份傳至備庫
scp full_db_JZH_20150523_*.bak 192.168.110.115:~/20150523_recover/
6、恢復備庫
[ora@jzhDG ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat May 23 20:17:59 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JZH (DBID=2072634265, not open)
RMAN> catalog start with '/home/ora/20150523_recover';
[ora@jzhDG ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat May 23 20:25:44 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: JZH (DBID=2072634265, not open)
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> allocate channel d3 type disk;
6> restore standby controlfile to '/home/ora/control01.ctl';
7> recover database noredo;
8> release channel d1;
9> release channel d2;
10> release channel d3;
11> }
7、關閉備庫，將恢復出來的control01.ctl覆蓋備庫控制檔案
[ora@jzhDG ~]$ cp control01.ctl /oradata/JZH/standby.ctl
8、啟動備庫至mount狀態
SQL> startup mount
ORACLE instance started.
Total System Global Area 1795162112 bytes
Fixed Size 2084648 bytes
Variable Size 301990104 bytes
Database Buffers 1476395008 bytes
Redo Buffers 14692352 bytes
Database mounted.
9、檢視gap
SQL> select * from v$archive_gap;
no rows selected
10、開啟備庫recover模式
SQL> recover managed standby database disconnect from session;
Media recovery complete.
11、檢視日誌，正在恢復
Sat May 23 21:17:53 2015
Media Recovery Log /oradata/standby_arch/1_161210_706979804.log
Sat May 23 21:18:15 2015
Media Recovery Log /oradata/standby_arch/1_161211_706979804.log
Media Recovery Log /oradata/standby_arch/2_152328_706979804.log
Sat May 23 21:19:14 2015
Media Recovery Log /oradata/standby_arch/1_161212_706979804.log
Sat May 23 21:20:06 2015
Media Recovery Log /oradata/standby_arch/2_152329_706979804.log
Sat May 23 21:20:29 2015
Media Recovery Log /oradata/standby_arch/1_161213_706979804.log
Media Recovery Log /oradata/standby_arch/2_152330_706979804.log
Sat May 23 21:20:50 2015
Media Recovery Log /oradata/standby_arch/2_152331_706979804.log
Media Recovery Log /oradata/standby_arch/1_161214_706979804.log
Sat May 23 21:21:10 2015
Media Recovery Log /oradata/standby_arch/1_161215_706979804.log
Sat May 23 21:21:20 2015
Media Recovery Log /oradata/standby_arch/2_152332_706979804.log
Sat May 23 21:21:31 2015
Media Recovery Log /oradata/standby_arch/1_161216_706979804.log
Sat May 23 21:21:41 2015
Media Recovery Log /oradata/standby_arch/2_152333_706979804.log
Sat May 23 21:21:53 2015
Media Recovery Waiting for thread 1 sequence 161217
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10271187/viewspace-1668942/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dg丟失歸檔，使用rman增量備份恢復
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- 用增量備份來快速恢復dg
- rman 增量備份恢復
- 【BBED】丟失歸檔檔案情況下的恢復
- DG歸檔日誌缺失恢復
- oracle 增量備份恢復驗證Oracle
- OracleDG備庫恢復–gapOracle
- ORACLE DG從庫 Rman備份恢復Oracle
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- oracle dg 歸檔日誌恢復情況Oracle
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- Oracle dg歸檔同步失敗Oracle
- 電腦檔案丟失資料恢復資料恢復
- 利用innobackupex備份集恢復指定庫
- 從備份片中恢復某個指定得歸檔或者資料檔案
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- 丟失的隨身碟檔案如何恢復？
- RAC備份恢復之Voting備份與恢復
- Oracle Redo丟失恢復方案Oracle
- 伺服器資料丟失了怎麼恢復/分割槽丟失恢復教程伺服器
- RMAN-ERROR：因為找不到過期和丟失的歸檔日誌而備份失敗Error
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- 備份與恢復：polardb資料庫備份與恢復資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 硬碟資料丟失如何恢復？硬碟
- 分割槽丟失資料恢復資料恢復
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- XFS檔案系統的備份、恢復、修復
- 12 使用RMAN備份和恢復檔案
- 利用docker-compose恢復gitlab備份的資料DockerGitlab
- 世界備份日——如果您丟失了所有檔案
- win10 ppt檔案丟失怎麼恢復_win10 ppt文件丟失如何找回Win10
- PostgreSQL12中實現增量備份與任意時間點恢復SQL
- DATA GUARD主庫丟失資料檔案的恢復(3)