经常遇到有DATAGUARD备机不使用standby redo log的情况发生,特别是RAC到单机环境;很多人在说重启一下、不行删除重加之类,还不行了重启下主节点(当然生产环境下比较困难)等;
从从数据库层面,从v$standby_log中可以清楚查出standby redo log所属的THREAD,据此,可以确认standby redo log不被使用的原因,如典型的THREAD不匹配;
如下为一次DATAGUARD备机不使用standby redo log的情况的处理:
1.查询当前v$standby_log中standby redo log所属的THREAD
RFS[1]: Opened log for thread 1 sequence 33830 dbid -596906188 branch 1054672309
Wed May 11 15:23:23 2022
Media Recovery Log /u01/app/db/11.2.0/dbs/arch1_33829_1054672309.dbf
Media Recovery Waiting for thread 1 sequence 33830 (in transit)
…………
SQL> select thread#,group# from v$standby_log;
THREAD# GROUP#
---------- ----------
……
0 11
0 12
0 13
9 rows selected.
2.停止MRP进程,添加STANDBY LOG时指定THREAD
SQL> alter database add standby logfile thread 1 '/u01/app/db/data/stddazdredo24.log' size 1000m;
Database altered.
SQL> select thread#,group# from v$standby_log;
THREAD# GROUP#
---------- ----------
………………
0 13
1 14
1 15
1 16
13 rows selected.
3.再次打开MRP进程,等待主节点切换日志后,可以使用上STANDBY LOG
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select process,status,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
RFS IDLE 0 0 0
RFS IDLE 1 33830 694535
RFS IDLE 0 0 0
MRP0 WAIT_FOR_LOG 1 33830 0
8 rows selected.
SQL> /
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
ARCH CONNECTED 0 0 0
RFS IDLE 0 0 0
RFS IDLE 1 33832 51417
RFS IDLE 0 0 0
MRP0 APPLYING_LOG 1 33832 51716 ====>>.MRP进程在实时恢复,BLOCK在增大
8 rows selected.