dataguard 歸檔丟失,備庫基於SCN恢復
dataguard 歸檔丟失(主庫中無此丟失歸檔處理),備庫基於SCN恢復
環境:
OS: CentOS 6.5
DB: Oracle 10.2.0.5
１.主備庫環境
主庫:
SQL> select dbid,name,LOG_MODE,open_mode,db_unique_name,DATABASE_ROLE,PROTECTION_MODE from v$database;
DBID NAME LOG_MODE OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
---------- --------- ------------ ---------- -------------------- ---------------- --------------------
351758316 NETDATA ARCHIVELOG READ WRITE netdata_pd PRIMARY MAXIMUM PERFORMANCE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/netdata
Oldest online log sequence 65
Next log sequence to archive 67
Current log sequence 67
SQL>
備庫:
SQL> select dbid,name,LOG_MODE,open_mode,db_unique_name,DATABASE_ROLE,PROTECTION_MODE from v$database;
DBID NAME LOG_MODE OPEN_MODE DB_UNIQUE_ DATABASE_ROLE PROTECTION_MODE
---------- ---------- ------------ ---------- ---------- ---------------- --------------------
351758316 NETDATA ARCHIVELOG MOUNTED netdata_sd PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/netdata
Oldest online log sequence 63
Next log sequence to archive 0
Current log sequence 67
SQL>
2.模擬歸檔丟失
備庫操作:
備庫取消歸檔應用
SQL> ALTER DATABASE recover managed standby DATABASE cancel;
Database altered.
備庫以只讀模式開啟
SQL> alter database open read only;
Database altered.
SQL>
這張表是我建的測試表，用一個job跑的插入資料(每隔3S插入一條
SQL> select count(*) from hr.test;
COUNT(*)
----------
28501
SQL>
主庫操作:
SQL> select count(*) from hr.test;
COUNT(*)
----------
31143
SQL>
可以看到記錄數不一致，這是因為歸還沒有傳輸過來
主備歸檔情況
主庫操作
SQL> set line 200
SQL> set pagesize 200
SQL> col name format A50
SQL> select name,SEQUENCE#,APPLIED from v$archived_log where dest_id=1;
/u01/app/oracle/archive/netdata/1_52_857898543.arc 52 NO
/u01/app/oracle/archive/netdata/1_53_857898543.arc 53 NO
/u01/app/oracle/archive/netdata/1_54_857898543.arc 54 NO
/u01/app/oracle/archive/netdata/1_55_857898543.arc 55 NO
/u01/app/oracle/archive/netdata/1_56_857898543.arc 56 NO
/u01/app/oracle/archive/netdata/1_57_857898543.arc 57 NO
/u01/app/oracle/archive/netdata/1_58_857898543.arc 58 NO
/u01/app/oracle/archive/netdata/1_59_857898543.arc 59 NO
/u01/app/oracle/archive/netdata/1_60_857898543.arc 60 NO
/u01/app/oracle/archive/netdata/1_61_857898543.arc 61 NO
/u01/app/oracle/archive/netdata/1_62_857898543.arc 62 NO
/u01/app/oracle/archive/netdata/1_63_857898543.arc 63 NO
/u01/app/oracle/archive/netdata/1_64_857898543.arc 64 NO
/u01/app/oracle/archive/netdata/1_65_857898543.arc 65 NO
/u01/app/oracle/archive/netdata/1_66_857898543.arc 66 NO
64 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
12 1 65 104857600 2 YES INACTIVE 715138 13-SEP-14
13 1 66 104857600 2 YES INACTIVE 715158 13-SEP-14
14 1 67 104857600 2 NO CURRENT 715164 13-SEP-14
SQL>
備庫操作
SQL> set line 200
SQL> set pagesize 200
SQL> col name format A50
SQL> select name,SEQUENCE#,APPLIED from v$archived_log where dest_id=1;
NAME SEQUENCE# APP
-------------------------------------------------- ---------- ---
/u01/app/oracle/archive/netdata/1_64_857898543.arc 64 YES
/u01/app/oracle/archive/netdata/1_65_857898543.arc 65 YES
/u01/app/oracle/archive/netdata/1_66_857898543.arc 66 YES
/u01/app/oracle/archive/netdata/1_63_857898543.arc 63 YES
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
12 1 65 104857600 2 YES CLEARING 715138 13-SEP-14
13 1 63 104857600 2 YES CLEARING 714611 13-SEP-14
14 1 67 104857600 2 YES CLEARING_CURRENT 715164 13-SEP-14
主庫操作
SQL> ALTER system SET log_archive_dest_state_2 = 'defer';
System altered.
SQL>
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL>
刪除歸檔
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/netdata
Oldest online log sequence 70
Next log sequence to archive 72
Current log sequence 72
SQL>
刪除歸檔
[oracle@oracle10g-dg1-213-100 netdata]$ rm -rvf 1\_7*
已刪除"1_70_857898543.arc"
已刪除"1_71_857898543.arc"
已刪除"1_7_857893401.dbf"
主庫開啟備庫歸檔
SQL> ALTER system SET log_archive_dest_state_2 = 'enable';
備庫重新開啟應用
SQL> ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;
Database altered.
SQL>
System altered.
SQL>
查詢備庫alert.log
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 67
Fetching gap sequence in thread 1, gap sequence 67-70
Sat Sep 13 21:00:27 CST 2014
Completed: ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION
Sat Sep 13 21:00:57 CST 2014
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 67-70
DBID 351758316 branch 857898543
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------
有gap產生了
備庫查詢
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
NAME SEQUENCE# APP
-------------------------------------------------- ---------- ---
/u01/app/oracle/archive/netdata/1_66_857898543.arc 66 YES
主庫查詢
SQL> SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM v$archived_log WHERE SEQUENCE# > 67 ORDER BY 1;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
68 745961 745971
69 745971 745984
70 745984 745988
71 745988 746005
SQL>
主庫
基於SCN增量備份
RMAN> backup device type disk incremental from scn 715164 database format '/u01/backup/netdata_incre%U.bbk';
Starting backup at 13-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/netdata/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/netdata/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/netdata/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/netdata/HTSDK_01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/netdata/HTSDK_INDEX_01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/netdata/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/netdata/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-SEP-14
channel ORA_DISK_1: finished piece 1 at 13-SEP-14
piece handle=/u01/backup/netdata_incre0jpie810_1_1.bbk tag=TAG20140913T212904 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:18
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-SEP-14
channel ORA_DISK_1: finished piece 1 at 13-SEP-14
piece handle=/u01/backup/netdata_incre0kpie83e_1_1.bbk tag=TAG20140913T212904 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 13-SEP-14
RMAN>
主庫重新生成控制檔案
SQL> ALTER DATABASE CREATE standby controlfile AS '/u01/backup/standby.ctl';
Database altered.
複製檔案至備庫
[oracle@oracle10g-dg1-213-100 backup]$ scp * oracle@192.168.213.101:/u01/backup/
oracle@192.168.213.101's password:
netdata_incre0jpie810_1_1.bbk
100% 17MB 8.5MB/s 00:02
netdata_incre0kpie83e_1_1.bbk
100% 7200KB 7.0MB/s 00:01
standby.ctl
100% 7120KB 7.0MB/s 00:01
備庫操作
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 704643072 bytes
Fixed Size 2098912 bytes
Variable Size 192940320 bytes
Database Buffers 503316480 bytes
Redo Buffers 6287360 bytes
SQL> quit
恢復控制檔案
RMAN> restore controlfile from '/u01/backup/standby.ctl';
Starting restore at 13-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/u01/app/oracle/oradata/netdata/control01.ctl
output filename=/u01/app/oracle/oradata/netdata/control02.ctl
output filename=/u01/app/oracle/oradata/netdata/control03.ctl
Finished restore at 13-SEP-14
RMAN>
RMAN> catalog start with '/u01/backup'
2> ;
searching for all files that match the pattern /u01/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/netdata_incre0jpie810_1_1.bbk
File Name: /u01/backup/standby.ctl
File Name: /u01/backup/control01.ctl
File Name: /u01/backup/netdata_incre0kpie83e_1_1.bbk
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup/netdata_incre0jpie810_1_1.bbk
File Name: /u01/backup/standby.ctl
File Name: /u01/backup/control01.ctl
File Name: /u01/backup/netdata_incre0kpie83e_1_1.bbk
RMAN> recover DATABASE noredo;
Starting recover at 13-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/netdata/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/netdata/undotbs01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/netdata/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/netdata/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/netdata/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/netdata/HTSDK_01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/netdata/HTSDK_INDEX_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/netdata_incre0jpie810_1_1.bbk
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/netdata_incre0jpie810_1_1.bbk tag=TAG20140913T212904
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished recover at 13-SEP-14
RMAN>
查詢alert 日誌
Sat Sep 13 22:04:34 CST 2014
RFS LogMiner: Client disabled from further notification
Sat Sep 13 22:04:54 CST 2014
Incremental restore complete of datafile 4 /u01/app/oracle/oradata/netdata/users01.dbf
checkpoint is 753898
Incremental restore complete of datafile 2 /u01/app/oracle/oradata/netdata/undotbs01.dbf
checkpoint is 753898
last deallocation scn is 669702
Incremental restore complete of datafile 5 /u01/app/oracle/oradata/netdata/example01.dbf
checkpoint is 753898
last deallocation scn is 399417
Incremental restore complete of datafile 6 /u01/app/oracle/oradata/netdata/HTSDK_01.dbf
checkpoint is 753898
Incremental restore complete of datafile 7 /u01/app/oracle/oradata/netdata/HTSDK_INDEX_01.dbf
checkpoint is 753898
Incremental restore complete of datafile 3 /u01/app/oracle/oradata/netdata/sysaux01.dbf
checkpoint is 753898
last deallocation scn is 421688
Incremental restore complete of datafile 1 /u01/app/oracle/oradata/netdata/system01.dbf
checkpoint is 753898
last deallocation scn is 472342
備庫重新應用
SQL> ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;
Database altered.
SQL>
測試歸檔是否
SQL> alter system switch logfile;
System altered.
SQL>
查詢主備
備庫操作
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/netdata
Oldest online log sequence 71
Next log sequence to archive 0
Current log sequence 73
col name format A50;
col dest_name format A40
col error format A20
set line 200;
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
NAME SEQUENCE# APP
-------------------------------------------------- ---------- ---
/u01/app/oracle/archive/netdata/1_72_857898543.arc 72 YES
col dest_name format A40
SQL> select dest_name,status,error from v$archive_dest where rownum<3;
DEST_NAME STATUS ERROR
---------------------------------------- --------- --------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
SQL>
主庫操作
archive log list;
col name format A50;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/netdata
Oldest online log sequence 71
Next log sequence to archive 73
Current log sequence 73
col dest_name format A40
col error format A20
set line 200;
select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
NAME SEQUENCE# APP
-------------------------------------------------- ---------- ---
/u01/app/oracle/archive/netdata/1_72_857898543.arc 72 NO
netdata_sd 72 YES
col dest_name format A40
SQL> select dest_name,status,error from v$archive_dest where rownum<3;
DEST_NAME STATUS ERROR
---------------------------------------- --------- --------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24486203/viewspace-1698671/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dg丟失歸檔，使用rman增量備份恢復
- 【BBED】丟失歸檔檔案情況下的恢復
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- oracle基於SCN增量恢復Oracle
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- DATA GUARD主庫丟失資料檔案的恢復(2)
- 電腦檔案丟失資料恢復資料恢復
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- 丟失的隨身碟檔案如何恢復？
- MongoDB資料庫報錯，資料庫檔案丟失資料恢復案例MongoDB資料庫資料恢復
- Oracle Redo丟失恢復方案Oracle
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 伺服器資料丟失了怎麼恢復/分割槽丟失恢復教程伺服器
- 從dataguard備份的恢復機制
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- 分割槽丟失資料恢復資料恢復
- 硬碟資料丟失如何恢復？硬碟
- 基於linux系統，fsck後資料丟失的資料恢復方案Linux資料恢復
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- 伺服器儲存金蝶資料庫丟失恢復伺服器資料庫
- 12C針對cdb全備與 PDB執行不完全恢復（基於SCN)
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- win10 ppt檔案丟失怎麼恢復_win10 ppt文件丟失如何找回Win10
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- macOS Big Sur系統如何恢復丟失的資料檔案？Mac
- 伺服器RAID資料丟失恢復伺服器AI
- OMV資料恢復NAS陣列丟失資料恢復陣列
- 如何恢復伺服器資料丟失伺服器
- chkdsk 後資料丟失的恢復方法
- OGG整合抽取模式丟失歸檔處理模式