--观察
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的日志
原因：备库的密码文件是从主库节点一复制过来的，主库节点二与主库节点一的密码文件不一致
处理：复制主库节点一的密码文件替换主库节点二的密码文件
RAC部署单实例备库DG问题记录
-
lixiaxin200319
2016-08-23 16:59:16 -
Oracle
- 原创