RAC部署单实例备库DG问题记录

--观察
select THREAD#,group#,process,client_process,sequence#,status from v$managed_standby
   THREAD# GROUP#                                   PROCESS   CLIENT_P  SEQUENCE# STATUS
---------- ---------------------------------------- --------- -------- ---------- ------------
         2 11                                       ARCH      ARCH             35 CLOSING
         0 N/A                                      ARCH      ARCH              0 CONNECTED
         0 N/A                                      ARCH      ARCH              0 CONNECTED
         1 10                                       ARCH      ARCH            219 CLOSING
         1 N/A                                      MRP0      N/A             220 APPLYING_LOG
         0 N/A                                      RFS       ARCH              0 IDLE
         0 N/A                                      RFS       UNKNOWN           0 IDLE
         1 1                                        RFS       LGWR            220 IDLE
         0 N/A                                      RFS       UNKNOWN           0 IDLE
         0 N/A                                      RFS       ARCH              0 IDLE
         0 N/A                                      RFS       UNKNOWN           0 IDLE

   THREAD# GROUP#                                   PROCESS   CLIENT_P  SEQUENCE# STATUS
---------- ---------------------------------------- --------- -------- ---------- ------------
         2 3                                        RFS       LGWR             36 IDLE
         0 N/A                                      RFS       UNKNOWN           0 IDLE

13 rows selected.

说明：进行日志应用时需要 REDO LOG，日志组3是属于 thread 1 的，但在进行日志应用时 thread 2
可以使用日志组3.

--观察数据库告警日志
Mon Aug 22 18:27:10 2016
Archived Log entry 26 added for thread 2 sequence 30 rlc 919517894 ID 0x45e7d46 dest 2:
Mon Aug 22 18:27:10 2016
Media Recovery Log /data/posdbarch/2_30_919517894.dbf
Media Recovery Log /data/posdbarch/1_216_919517894.dbf
Media Recovery Log /data/posdbarch/1_217_919517894.dbf
Media Recovery Log /data/posdbarch/1_218_919517894.dbf
Media Recovery Log /data/posdbarch/2_31_919517894.dbf
Media Recovery Log /data/posdbarch/2_32_919517894.dbf
Media Recovery Log /data/posdbarch/2_33_919517894.dbf
Media Recovery Waiting for thread 1 sequence 219 (in transit)
Recovery of Online Redo Log: Thread 1 Group 10 Seq 219 Reading mem 0
  Mem# 0: /data/oradata/posdb/group_10_1
Media Recovery Log /data/posdbarch/2_34_919517894.dbf
Media Recovery Waiting for thread 2 sequence 35 (in transit)
Recovery of Online Redo Log: Thread 2 Group 11 Seq 35 Reading mem 0
  Mem# 0: /data/oradata/posdb/group_11_1
Mon Aug 22 23:45:28 2016
RFS[7]: Selected log 12 for thread 2 sequence 36 dbid 73328966 branch 919517894
Mon Aug 22 23:45:28 2016
Media Recovery Waiting for thread 2 sequence 36 (in transit)
Recovery of Online Redo Log: Thread 2 Group 12 Seq 36 Reading mem 0
  Mem# 0: /data/oradata/posdb/group_12_1
Mon Aug 22 23:45:29 2016
Archived Log entry 27 added for thread 2 sequence 35 ID 0x45e7d46 dest 1:
Mon Aug 22 23:45:29 2016
RFS[2]: Selected log 9 for thread 1 sequence 220 dbid 73328966 branch 919517894
Mon Aug 22 23:45:31 2016
Archived Log entry 28 added for thread 1 sequence 219 ID 0x45e7d46 dest 1:
Media Recovery Waiting for thread 1 sequence 220 (in transit)
Recovery of Online Redo Log: Thread 1 Group 9 Seq 220 Reading mem 0
  Mem# 0: /data/oradata/posdb/group_9_1


说明：备库是使用 STANDBY REDO LOG接受主库的 REDO LOG，然后进行日志应用时把在备库生成的日志条目写入
备库的 REDO LOG



问题：备库删除 redo log group 报 ORA-01567、ORA-00312
SQL> alter database drop logfile group 8;
alter database drop logfile group 8
*
ERROR at line 1:
ORA-01567: dropping log 8 would leave less than 2 log files for instance posdb2
(thread 2)
ORA-00312: online log 8 thread 2: '+data'

原因：每个数据库（如果是RAC就是每个实例）必须有两个重做日志组，如果只剩下两个重做日志组时进行删除就会报 ORA-01567、ORA-00312
处理：
--查看备库 thread 2 只有两个 redo log group
SQL> select group#,thread#,STATUS,MEMBERS from v$log;

    GROUP#    THREAD# STATUS              MEMBERS
---------- ---------- ---------------- ----------
         7          1 UNUSED                    1
         2          1 CURRENT                   1
         3          1 UNUSED                    1
         1          1 CLEARING                  1
         5          1 CLEARING                  1
         6          1 CLEARING                  1
         8          2 CLEARING                  1
         4          2 CURRENT                   2

--删除 redo log group 7 ,然后把 redo log group 7添加到 thread 2,然后再删除和添加 redo group 8

SQL>alter database drop logfile group 7;

$ rm /data/oradata/posdb/group_7_1

SQL>alter database add logfile THREAD 2 group 7 ('/data/oradata/posdb/group_7_1') size 500M;

SQL>alter database drop logfile group 8;
SQL>alter database add logfile THREAD 2 group 8 ('/data/oradata/posdb/group_8_1') size 500M;




问题：在备库删除 redo log group 4 报 ORA-01623、ORA-00312、ORA-00312
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance posdb2 (thread 2) - cannot drop
ORA-00312: online log 4 thread 2: '+data'
ORA-00312: online log 4 thread 2: '/data/oradata/posdb/group_4_1'

原因：redo log group 4 是主库的当前日志

1）备库日志信息
SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         2          1 CURRENT                   1 YES
         6          1 CLEARING                  1 YES
         3          1 UNUSED                    1 YES
         1          1 CLEARING                  1 YES
         5          1 CLEARING                  1 YES
         7          2 UNUSED                    1 YES
         8          2 UNUSED                    1 YES
         4          2 CURRENT                   2 YES

2）主库日志信息
SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         1          1 INACTIVE                  1 YES
         2          1 CURRENT                   1 NO
         3          2 INACTIVE                  1 YES
         4          2 CURRENT                   1 NO
         5          1 INACTIVE                  1 YES
         6          1 INACTIVE                  1 YES
         7          2 INACTIVE                  1 YES
         8          2 INACTIVE                  1 YES

8 rows selected.

我们看到备库和主库中的当前日志组都是2和4，我们在主库节点2上切换下日志看备库是否也会改变对应的当前日志

主库节点2日志切换后，当前日志组为8
SQL> alter system switch logfile;

System altered.

SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         1          1 INACTIVE                  1 YES
         2          1 CURRENT                   1 NO
         3          2 INACTIVE                  1 YES
         4          2 ACTIVE                    1 YES
         5          1 INACTIVE                  1 YES
         6          1 INACTIVE                  1 YES
         7          2 INACTIVE                  1 YES
         8          2 CURRENT                   1 NO

备库的当前日志也变成日志组8
SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         1          1 CLEARING                  1 YES
         2          1 CURRENT                   1 YES
         3          1 UNUSED                    1 YES
         4          2 CLEARING                  2 YES
         5          1 CLEARING                  1 YES
         6          1 CLEARING                  1 YES
         7          2 UNUSED                    1 YES
         8          2 CURRENT                   1 YES

这个时候虽然 日志组4已经不是当前日志组了，但状态为 ACTIVE还没有完成归档，要等完成归档后才可以在备库上删除日志组4.



问题：在备库删除日志组4 报错
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance posdb2 (thread 2)
ORA-00312: online log 4 thread 2: '+data'
ORA-00312: online log 4 thread 2: '/data/oradata/posdb/group_4_1'

原因：日志组4在主库中还没完成归档

处理：等日志组4完成归档后再删除
1）查看主库日志组信息，发现日志组4还没完成归档（状态为 ACTIVE）
SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         1          1 INACTIVE                  1 YES
         2          1 CURRENT                   1 NO
         3          2 INACTIVE                  1 YES
         4          2 ACTIVE                    1 YES
         5          1 INACTIVE                  1 YES
         6          1 INACTIVE                  1 YES
         7          2 INACTIVE                  1 YES
         8          2 CURRENT                   1 NO
2）查看主库日志组信息，发现日志组4已经完成归档，此时可以在备库删除日志组4
SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         1          1 INACTIVE                  1 YES
         2          1 CURRENT                   1 NO
         3          2 INACTIVE                  1 YES
         4          2 INACTIVE                  1 YES
         5          1 INACTIVE                  1 YES
         6          1 INACTIVE                  1 YES
         7          2 INACTIVE                  1 YES
         8          2 CURRENT                   1 NO


SQL> alter database drop logfile group 4;




问题：在备库删除日志报
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance posdb2 (thread 2)
ORA-00312: online log 4 thread 2: '+data'
ORA-00312: online log 4 thread 2: '/data/oradata/posdb/group_4_1'

原因：虽然在主库中看到重做日志组4已经不是活动日志组，但在备库中该日志组中的日志还没应用

处理：在备库中启动日志应用等日志组4应用完后再删除日志组4
--在主库查询日志组4已经完成归档不再活动，在备库中还是无法删除

SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC
---------- ---------- ---------------- ---------- ---
         1          1 INACTIVE                  1 YES
         2          1 CURRENT                   1 NO
         3          2 INACTIVE                  1 YES
         4          2 INACTIVE                  1 YES
         5          1 INACTIVE                  1 YES
         6          1 INACTIVE                  1 YES
         7          2 INACTIVE                  1 YES
         8          2 CURRENT                   1 NO

--在备库上查询出日志组4对应的日志序号为37
SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED ,SEQUENCE# from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC  SEQUENCE#
---------- ---------- ---------------- ---------- --- ----------
         1          1 CLEARING                  1 YES        220
         2          1 CURRENT                   1 YES        221
         3          1 UNUSED                    1 YES          0
         4          2 CLEARING                  2 YES         37
         5          1 CLEARING                  1 YES        219
         6          1 CLEARING                  1 YES          0
         7          2 UNUSED                    1 YES          0
         8          2 CURRENT                   1 YES         38

--在主库上查询日志组4在备库上还没应用
SQL> SELECT NAME,DEST_ID,SEQUENCE#, FIRST_TIME, APPLIED,to_char(NEXT_TIME,'yyyy-mm-dd hh24:mi:ss') FROM V$ARCHIVED_LOG where THREAD#=2 ORDER BY SEQUENCE#  ;
NAME
--------------------------------------------------------------------------------
   DEST_ID  SEQUENCE# FIRST_TIME         APPLIED   TO_CHAR(NEXT_TIME,'
---------- ---------- ------------------ --------- -------------------
posdb_dg
         2         37 23-AUG-16          NO        2016-08-23 10:20:27

+ARCH/posdb/archivelog/2016_08_23/thread_2_seq_37.270.920629227
         1         37 23-AUG-16          NO        2016-08-23 10:20:27

--在备库开启日志应用后，日志组4 序号变为0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE  DISCONNECT;

Database altered.

SQL> select group#,thread#,STATUS,MEMBERS,ARCHIVED ,SEQUENCE# from v$log;

    GROUP#    THREAD# STATUS              MEMBERS ARC  SEQUENCE#
---------- ---------- ---------------- ---------- --- ----------
         1          1 CLEARING                  1 YES        220
         2          1 CURRENT                   1 YES        221
         3          1 UNUSED                    1 YES          0
         4          2 CLEARING                  2 YES          0
         5          1 CLEARING                  1 YES        219
         6          1 CLEARING                  1 YES          0
         7          2 UNUSED                    1 YES          0
         8          2 CURRENT                   1 YES         38

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

[oracle@fsdb02 posdb]$ rm group_4_1


SQL> alter database add logfile THREAD 2 group 4 ('/data/oradata/posdb/group_4_1') size 500M;

Database altered.




问题：DG备库启动日志应用后没有应用日志，数据库告警日志没报错也没日志恢复的信息
原因：主库的 tnsnames.ora 服务名配置错误
posdb_dg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
处理：修改为正确的服务名
posdb_dg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_dg)
    )
  )


问题：DG备库可以接受和应用主库RAC节点一的日志（包括在线日志和归档日志），但无法接受到主库RAC节点2的日志
原因：备库的密码文件是从主库节点一复制过来的，主库节点二与主库节点一的密码文件不一致
处理：复制主库节点一的密码文件替换主库节点二的密码文件

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2123862/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2123862/