在DATAGUARD中手工處理日誌GAP的方法
在DATAGUARD中手工處理日誌GAP的方法
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
SQL> select sequence#,status,process from v$managed_standby;
SEQUENCE# STATUS PROCESS
---------- ------------ ---------
51 CLOSING ARCH
0 CONNECTED ARCH
0 CONNECTED ARCH
50 CLOSING ARCH
49 WAIT_FOR_GAP MRP0
原主庫：
SQL> select first_time,sequence#,applied from v$archived_log ;
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-22 14:42:47 33 YES
2014-10-22 14:45:31 34 YES
2014-10-22 14:45:37 35 YES
2014-10-22 14:45:37 35 YES
2014-10-22 14:48:35 36 YES
2014-10-22 14:48:35 36 YES
2014-10-22 15:57:40 37 YES
2014-10-22 15:57:40 37 YES
2014-10-22 16:00:01 38 YES
2014-10-22 16:00:01 38 YES
2014-10-22 16:02:54 39 YES
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-22 16:02:54 39 YES
2014-10-22 16:03:00 40 YES
2014-10-22 16:03:00 40 YES
2014-10-22 16:03:50 41 YES
2014-10-22 16:03:50 41 YES
2014-10-22 17:25:45 42 YES
2014-10-22 17:25:45 42 YES
2014-10-23 00:00:26 43 YES
2014-10-23 00:00:26 43 YES
2014-10-23 09:23:28 44 YES
2014-10-23 09:23:28 44 YES
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-23 13:40:14 45 YES
2014-10-23 13:40:14 45 YES
2014-10-23 13:52:21 46 YES
2014-10-23 13:52:21 46 YES
2014-10-23 13:57:05 47 YES
2014-10-23 13:57:05 47 NO
2014-10-23 15:16:43 48 YES
2014-10-23 15:16:43 48 NO
2014-10-23 15:22:20 50 NO
2014-10-23 15:34:35 51 NO
原備庫：
SQL> select first_time,sequence#,applied from v$archived_log ;
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-22 14:45:37 35 YES
2014-10-22 14:48:35 36 YES
2014-10-22 15:57:40 37 YES
2014-10-22 16:00:01 38 YES
2014-10-22 16:02:54 39 YES
2014-10-22 16:03:00 40 YES
2014-10-22 16:03:50 41 YES
2014-10-22 17:25:45 42 YES
2014-10-23 00:00:26 43 YES
2014-10-23 09:23:28 44 YES
2014-10-23 13:40:14 45 YES
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-23 13:52:21 46 YES
2014-10-23 13:57:05 47 YES
2014-10-23 15:16:43 48 YES
2014-10-23 15:20:18 49 NO
2014-10-23 15:22:20 50 NO
2014-10-23 15:22:20 50 NO
2014-10-23 15:34:35 51 NO
2014-10-23 15:34:35 51 NO
2014-10-23 15:34:38 52 NO
手工處理日誌GAP的步驟
：
1、在備庫檢查是否有日誌缺失
SQL> select * from V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 49 49
從上面的資訊可以看出，備庫中缺失了49到49的日誌。
2、在主庫中查詢缺失的日誌的所在路徑和名稱
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 49 AND 49;
NAME
--------------------------------------------------------------------------------
/arch/testdb2/1_49_861630171.dbf
如果把日誌移動到其他路徑，則把日誌所在路徑換成當前實際所在路徑。
3、把日誌複製到備庫上
原備庫：
[oracle@testdb2 dbs]$ cd /arch/testdb2/
[oracle@testdb2 testdb2]$ ll
total 5744
-rw-r-----. 1 oracle oinstall 143872 Oct 23 13:57 1_46_861630171.dbf
-rw-r-----. 1 oracle oinstall 5345280 Oct 23 15:16 1_47_861630171.dbf
-rw-r-----. 1 oracle oinstall 106496 Oct 23 15:20 1_48_861630171.dbf
-rw-r-----. 1 oracle oinstall 82432 Oct 23 15:22 1_49_861630171.dbf
-rw-r-----. 1 oracle oinstall 24576 Oct 23 15:34 1_50_861630171.dbf
-rw-r-----. 1 oracle oinstall 1024 Oct 23 15:34 1_51_861630171.dbf
-rw-r-----. 1 oracle oinstall 166400 Oct 23 15:35 1_52_861630171.dbf
[oracle@testdb2 testdb2]$ scp -p 1_49_861630171.dbf oracle@192.168.9.235:/arch/testdb1/
Warning: Permanently added '192.168.9.235' (RSA) to the list of known hosts.
oracle@192.168.9.235's password:
1_49_861630171.dbf 100% 81KB 80.5KB/s 00:00
[oracle@testdb2 testdb2]$
4、在備庫（原主庫）上手工註冊上一步中從主庫複製來的日誌
SQL> ALTER DATABASE REGISTER LOGFILE '/arch/testdb1/1_49_861630171.dbf';
Database altered.
......
5、稍等片刻，觀察備庫的alert日誌資訊
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /app/oracle/diag/rdbms/testdb1
/testdb1/trace
core_dump_dest string /app/oracle/diag/rdbms/testdb1
/testdb1/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /app/oracle/diag/rdbms/testdb1
/testdb1/trace
[oracle@testdb1 trace]:testdb1> cd /app/oracle/diag/rdbms/testdb1/testdb1/trace
[oracle@testdb1 trace]:testdb1>
[oracle@testdb1 trace]:testdb1> tail -f alert_testdb1.log
ALTER DATABASE REGISTER LOGFILE '/arch/testdb1/1_49_861630171.dbf'
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Completed: ALTER DATABASE REGISTER LOGFILE '/arch/testdb1/1_49_861630171.dbf'
Thu Oct 23 16:30:51 2014
Media Recovery Log /arch/testdb1/1_49_861630171.dbf
Media Recovery Log /arch/testdb1/1_50_861630171.dbf
Media Recovery Log /arch/testdb1/1_51_861630171.dbf
Recovery of Online Redo Log: Thread 1 Group 4 Seq 52 Reading mem 0
Mem# 0: /app/oracle/oradata/testdb1/stdby_redo04.log
從以上資訊，可以看出之前註冊的日誌已經被正常應用。
SQL>create or replace directory alert as '/app/oracle/diag/rdbms/testdb1/testdb1/trace';
SQL> select * from alert where rownum<20;
SQL>select * from alert where log like '%ORA-%';
6、檢查備庫是否還有日誌GAP
SQL> select * from V$ARCHIVE_GAP;
no rows selected
如果有行返回，則重複2-5步，直到查詢結果是"no rows selected"。
如果日誌只是臨時移動到其他地方，過後會再移回原路徑，則不用這麼大費周折手工去手工處理了，把日誌拷回原處後FAL會自動處理GAP。
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
SQL> select sequence#,status,process from v$managed_standby;
SEQUENCE# STATUS PROCESS
---------- ------------ ---------
51 CLOSING ARCH
0 CONNECTED ARCH
0 CONNECTED ARCH
50 CLOSING ARCH
49 WAIT_FOR_GAP MRP0
原主庫：
SQL> select first_time,sequence#,applied from v$archived_log ;
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-22 14:42:47 33 YES
2014-10-22 14:45:31 34 YES
2014-10-22 14:45:37 35 YES
2014-10-22 14:45:37 35 YES
2014-10-22 14:48:35 36 YES
2014-10-22 14:48:35 36 YES
2014-10-22 15:57:40 37 YES
2014-10-22 15:57:40 37 YES
2014-10-22 16:00:01 38 YES
2014-10-22 16:00:01 38 YES
2014-10-22 16:02:54 39 YES
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-22 16:02:54 39 YES
2014-10-22 16:03:00 40 YES
2014-10-22 16:03:00 40 YES
2014-10-22 16:03:50 41 YES
2014-10-22 16:03:50 41 YES
2014-10-22 17:25:45 42 YES
2014-10-22 17:25:45 42 YES
2014-10-23 00:00:26 43 YES
2014-10-23 00:00:26 43 YES
2014-10-23 09:23:28 44 YES
2014-10-23 09:23:28 44 YES
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-23 13:40:14 45 YES
2014-10-23 13:40:14 45 YES
2014-10-23 13:52:21 46 YES
2014-10-23 13:52:21 46 YES
2014-10-23 13:57:05 47 YES
2014-10-23 13:57:05 47 NO
2014-10-23 15:16:43 48 YES
2014-10-23 15:16:43 48 NO
2014-10-23 15:22:20 50 NO
2014-10-23 15:34:35 51 NO
原備庫：
SQL> select first_time,sequence#,applied from v$archived_log ;
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-22 14:45:37 35 YES
2014-10-22 14:48:35 36 YES
2014-10-22 15:57:40 37 YES
2014-10-22 16:00:01 38 YES
2014-10-22 16:02:54 39 YES
2014-10-22 16:03:00 40 YES
2014-10-22 16:03:50 41 YES
2014-10-22 17:25:45 42 YES
2014-10-23 00:00:26 43 YES
2014-10-23 09:23:28 44 YES
2014-10-23 13:40:14 45 YES
FIRST_TIME SEQUENCE# APPLIED
------------------- ---------- ---------
2014-10-23 13:52:21 46 YES
2014-10-23 13:57:05 47 YES
2014-10-23 15:16:43 48 YES
2014-10-23 15:20:18 49 NO
2014-10-23 15:22:20 50 NO
2014-10-23 15:22:20 50 NO
2014-10-23 15:34:35 51 NO
2014-10-23 15:34:35 51 NO
2014-10-23 15:34:38 52 NO
手工處理日誌GAP的步驟
：
1、在備庫檢查是否有日誌缺失
SQL> select * from V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 49 49
從上面的資訊可以看出，備庫中缺失了49到49的日誌。
2、在主庫中查詢缺失的日誌的所在路徑和名稱
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 49 AND 49;
NAME
--------------------------------------------------------------------------------
/arch/testdb2/1_49_861630171.dbf
如果把日誌移動到其他路徑，則把日誌所在路徑換成當前實際所在路徑。
3、把日誌複製到備庫上
原備庫：
[oracle@testdb2 dbs]$ cd /arch/testdb2/
[oracle@testdb2 testdb2]$ ll
total 5744
-rw-r-----. 1 oracle oinstall 143872 Oct 23 13:57 1_46_861630171.dbf
-rw-r-----. 1 oracle oinstall 5345280 Oct 23 15:16 1_47_861630171.dbf
-rw-r-----. 1 oracle oinstall 106496 Oct 23 15:20 1_48_861630171.dbf
-rw-r-----. 1 oracle oinstall 82432 Oct 23 15:22 1_49_861630171.dbf
-rw-r-----. 1 oracle oinstall 24576 Oct 23 15:34 1_50_861630171.dbf
-rw-r-----. 1 oracle oinstall 1024 Oct 23 15:34 1_51_861630171.dbf
-rw-r-----. 1 oracle oinstall 166400 Oct 23 15:35 1_52_861630171.dbf
[oracle@testdb2 testdb2]$ scp -p 1_49_861630171.dbf oracle@192.168.9.235:/arch/testdb1/
Warning: Permanently added '192.168.9.235' (RSA) to the list of known hosts.
oracle@192.168.9.235's password:
1_49_861630171.dbf 100% 81KB 80.5KB/s 00:00
[oracle@testdb2 testdb2]$
4、在備庫（原主庫）上手工註冊上一步中從主庫複製來的日誌
SQL> ALTER DATABASE REGISTER LOGFILE '/arch/testdb1/1_49_861630171.dbf';
Database altered.
......
5、稍等片刻，觀察備庫的alert日誌資訊
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /app/oracle/diag/rdbms/testdb1
/testdb1/trace
core_dump_dest string /app/oracle/diag/rdbms/testdb1
/testdb1/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /app/oracle/diag/rdbms/testdb1
/testdb1/trace
[oracle@testdb1 trace]:testdb1> cd /app/oracle/diag/rdbms/testdb1/testdb1/trace
[oracle@testdb1 trace]:testdb1>
[oracle@testdb1 trace]:testdb1> tail -f alert_testdb1.log
ALTER DATABASE REGISTER LOGFILE '/arch/testdb1/1_49_861630171.dbf'
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Completed: ALTER DATABASE REGISTER LOGFILE '/arch/testdb1/1_49_861630171.dbf'
Thu Oct 23 16:30:51 2014
Media Recovery Log /arch/testdb1/1_49_861630171.dbf
Media Recovery Log /arch/testdb1/1_50_861630171.dbf
Media Recovery Log /arch/testdb1/1_51_861630171.dbf
Recovery of Online Redo Log: Thread 1 Group 4 Seq 52 Reading mem 0
Mem# 0: /app/oracle/oradata/testdb1/stdby_redo04.log
從以上資訊，可以看出之前註冊的日誌已經被正常應用。
SQL>create or replace directory alert as '/app/oracle/diag/rdbms/testdb1/testdb1/trace';
SQL> select * from alert where rownum<20;
SQL>select * from alert where log like '%ORA-%';
6、檢查備庫是否還有日誌GAP
SQL> select * from V$ARCHIVE_GAP;
no rows selected
如果有行返回，則重複2-5步，直到查詢結果是"no rows selected"。
如果日誌只是臨時移動到其他地方，過後會再移回原路徑，則不用這麼大費周折手工去手工處理了，把日誌拷回原處後FAL會自動處理GAP。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1308247/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DATAGUARD中手工處理日誌GAP
- ORACLE DATAGUARD中手工處理日誌v$archive_GAP的方法OracleHive
- 【DG】 DataGuard 中處理archive gap的方法Hive
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 【DATAGUARD】手工恢復備庫日誌中斷
- ORACLE 11G DATAGUARD 日誌中斷處理方案Oracle
- DATAGUARD手工註冊歸檔日誌（轉載）
- [zt] 手工處理Standby 歸檔間隔(gap)的問題
- oracle 中 alert 報警日誌過大的處理方法Oracle
- SQL Server事務日誌的處理方法SQLServer
- Flink 在又拍雲日誌批處理中的實踐
- nginx日誌處理Nginx
- orbeon form 的日誌處理ORBORM
- 使用RMAN增量備份處理Dataguard因歸檔丟失造成的gap
- PHP日誌處理類PHP
- alert日誌中出現Private Strand Flush Not Complete的處理方法
- shell日誌顏色處理
- 指令碼處理iOS的Crash日誌指令碼iOS
- 處理Apache日誌的Bash指令碼Apache指令碼
- SQL Server日誌檔案總結及日誌滿的處理SQLServer
- ES & Filebeat 使用 Pipeline 處理日誌中的 @timestamp
- DataGuard日誌傳輸模式模式
- SQL Server 2005日誌檔案損壞的處理方法SQLServer
- 手工建立多路重做日誌組
- 物理Data Guard中哪個程式處理Redo GAP
- 一次dg 因密碼檔案與gap引起歸檔日誌無法應用的處理密碼
- node錯誤處理與日誌
- logstash kafka output 日誌處理Kafka
- oracle alert日誌亂碼處理Oracle
- strom打造日誌處理系統
- Db2 日誌處理二DB2
- [zt] SQL Server日誌檔案總結及日誌滿的處理SQLServer
- ORACLE 告警日誌alert過大的處理Oracle
- apache日誌匯入oracle(日誌經過python處理)ApacheOraclePython
- Redo Gap 處理與優化優化
- DG發現gap處理流程
- Oracle聯機日誌檔案丟失或損壞的處理方法Oracle
- sql server日誌檔案總結及日誌滿的處理辦法SQLServer