Oracle_dg歸檔丟失問題處理
環境：
OS：RedHat 6.3
DB: Oracle Database 11g Enterprise Edition Release 12.1.0.2.0
故障：主,備庫歸檔檔案只保留7天，超過7天的歸檔會自動刪除；
由於備庫伺服器停機過一段時間，導致主庫部分歸檔還沒有傳到備庫，就已經被刪除了；
解決方案：
(1)如果資料量很小，可以考慮重建備庫；
(2)
如果資料量很大，可以使用Rman基於SCN的增量備份來修復GAP問題；
本文主要講解第二種解決方案：
Rman基於SCN的增量備份來修復GAP
(1)問題現象
(2)查詢主庫中是否存在產生GAP的歸檔檔案
(3)確定增量恢復的起始SCN號
(4)主庫:使用Rman基於SCN的增量備份
(5)備庫:恢復
(6)測試
(1)問題現象
---備庫無法open，報錯無法獲取sequence# 43968
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-16043: Redo apply has been canceled.
ORA-16016: archived log for thread 1 sequence# 43968 unavailable
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
---警告日誌：
Wed Nov 15 16:04:56 2017
Errors in file /u01/app/oracle/diag/rdbms/standby_orcl/orcl/trace/orcl_pr00_13628.trc:
ORA-16043: Redo apply has been canceled.
ORA-16016: archived log for thread 1 sequence# 43968 unavailable
Wed Nov 15 16:04:56 2017
Standby Crash Recovery aborted due to error 10877.
Wed Nov 15 16:04:56 2017
Errors in file /u01/app/oracle/diag/rdbms/standby_orcl/orcl/trace/orcl_ora_12435.trc:
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
Wed Nov 15 16:04:56 2017
Completed Standby Crash Recovery.
Wed Nov 15 16:04:56 2017
Errors in file /u01/app/oracle/diag/rdbms/standby_orcl/orcl/trace/orcl_ora_12435.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-16043: Redo apply has been canceled.
ORA-16016: archived log for thread 1 sequence# 43968 unavailable
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10458 signalled during: alter database open...
(2)查詢主庫中是否存在產生GAP的歸檔檔案
---備庫gap
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1
43968 50948
---主庫
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 43968 AND 50948;
NAME
--------------------------------------------------------------------------------
......
/u01/ora_arch/1_50943_910299442.dbf
/u01/ora_arch/1_50944_910299442.dbf
/u01/ora_arch/1_50945_910299442.dbf
/u01/ora_arch/1_50946_910299442.dbf
/u01/ora_arch/1_50947_910299442.dbf
/u01/ora_arch/1_50948_910299442.dbf
3966 rows selected.
---發現主備部分歸檔檔案已經自動刪除，無法直接恢復
[root@CHENorcdb1 ~]# cd /u01/ora_arch/
[root@CHENorcdb1 ora_arch]# ll -rth > 1116.txt
[root@CHENorcdb1 ora_arch]# vi 1116.txt
......
(3)確定增量恢復的起始SCN號
---備庫
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
7 1812569
5 1812569
4 1879904814
6 1879904814
14 1879904814
9 1879904814
10 1879904814
11 1879904814
12 1879904814
13 1879904814
3 1879904814
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1879904814
8 1879904814
13 rows selected.
主庫：
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
7 1812569
5 1812569
4 2205980530
6 2205980530
14 2205980530
9 2205980530
10 2205980530
11 2205980530
12 2205980530
13 2205980530
3 2205980530
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2205980530
8 2205980530
13 rows selected.
(4)主庫:使用Rman基於SCN的增量備份
[oracle@CHENorcdb1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Nov 16 13:18:45 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1438080174)
RMAN>
run
{
allocate channel c3 device type disk;
backup as compressed backupset incremental from scn 1879904814 database format '/home/oracle/rman1116/%U';
release channel c3;
}
---將備份傳到備庫
[oracle@CHENorcdb1 oracle]# tar -zcvf rman1116.tar.gz rman1116/
rman1116/
rman1116/0vsjna6b_1_1
rman1116/10sjna9v_1_1
rman1116/12sjnabm_1_1
[oracle@CHENorcdb1 ~]$ scp -P 49622 rman1116.tar.gz 192.168.5.50:/home/oracle/
oracle@192.168.5.50's password:
rman1116.tar.gz 100% 496MB 99.1MB/s 00:05
(5)備庫:恢復
[oracle@CHENorcdb2 ~]$ tar -zxvf rman1116.tar.gz
---1 nomount備庫
SQL> shutdown immediate.
SQL> startup nomount
---2 透過備份恢復控制檔案
RMAN>
restore standby controlfile from '/home/oracle/rman1116/12sjnabm_1_1';
Starting restore at 16-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/orcl/standby_control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/standby_control02.ctl
Finished restore at 16-NOV-17
---3 mount備庫
SQL> alter database mount standby database;
Database altered.
---4 恢復備庫
RMAN> catalog start with '/home/oracle/rman1116';
RMAN> recover database;
......
archived log file name=/u01/ora_arch/1_51211_910299442.dbf thread=1 sequence=51211
media recovery complete, elapsed time: 00:00:05
Finished recover at 16-NOV-17
(6)驗證
---1 open備庫
SQL> alter database open;
---3 啟動應用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
---4 檢視歸檔GAP
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
---5 檢視當前序列號
SQL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
7 1812569
5 1812569
4 2206259949
6 2206259949
14 2206259949
9 2206259949
10 2206259949
11 2206259949
12 2206259949
13 2206259949
3 2206259949
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2206259949
8 2206259949
13 rows selected.
---主庫:建立測試資料
SQL> create table test1116 as select level as id from dual connect by level <=10;
---備庫:查詢測試資料
SQL> select * from test1116;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2147373/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OGG整合抽取模式丟失歸檔處理模式
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 非歸檔下日誌檔案丟失的處理辦法
- 控制檔案丟失處理方法
- redo log檔案丟失處理措施
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- Oracle全部索引丟失導致的效率問題處理Oracle索引
- DG 主庫丟失歸檔
- dfm檔案資料丟失問題
- 如何處理RabbitMQ 訊息堆積和訊息丟失問題MQ
- standby無法使用歸檔日誌問題處理
- Nginx session丟失問題處理解決方法NginxSession
- oracle 案例-控制檔案丟失故障處理過程Oracle
- 【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復
- [zt] 手工處理Standby 歸檔間隔(gap)的問題
- Oracle OS認證、口令檔案、密碼丟失處理Oracle密碼
- dg 主庫丟失歸檔 解決方案
- 一次資料庫不能歸檔問題的處理資料庫
- 【BBED】丟失歸檔檔案情況下的恢復
- 非歸檔丟失日誌檔案的恢復
- coreldraw檔案丟失(損壞)的恢復處理辦法
- lvm資訊丟失處理辦法LVM
- 【轉】 一次資料庫不能歸檔問題的處理資料庫
- Oracle 10g DG 主庫丟失歸檔Oracle 10g
- GoldenGate更新丟失問題Go
- rman 恢復---歸檔丟失and資料檔案損壞
- 歸檔模式下資料檔案丟失的恢復模式
- Jison解決JS處理後端返回的Long型資料精度丟失問題JS後端
- 關於Cordova框架對URL攔截導致通訊丟失問題的處理框架
- Centos修改DNS重啟或者重啟network服務後丟失問題處理CentOSDNS
- 【故障處理】RAC環境第二節點無法歸檔的詭異問題處理
- 關於丟失表空間資料檔案的處理方式
- 手工清除歸檔處理歸檔空間滿
- 週末又一次歸檔空間不足問題處理
- redis資料丟失你怎麼處理？Redis
- Oracle歸檔檔案丟失導致OGG不用啟動Oracle
- 歸檔模式下丟失普通資料檔案並恢復模式
- 在歸檔模式下丟失日誌檔案的恢復模式