在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。