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