背景：测试环境突然发现，主备库不能同步了，查看备库的日志发现备库一直处于等待接收日志的状态；

Media Recovery Waiting for thread 1 sequence 34

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Wed Jan 06 16:02:01 2016

解决方法：

排查问题的经过：

1、查看操作系统的空间

[oracle@db02 dbs]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_db01-lv_root 16G 3.5G 12G 24% /

/dev/mapper/vg_db01-LogVol02 20G 13G 6.1G 67% /u01

检查当前的数据库还是有空间的。

2、检查数据库的参数设置

2.1 show parameter log_archive_dest_state_2

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string ENABLE 当前状态要为ENABLE，曾经有朋友这边的参数为defer导致日志停止传输；

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

log_archive_dest_state_29 string enable


启动的命令：ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';

2.2 检查传输路径

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

-------------------- -------- -----------------------------------------------------------------------------------------------------

log_archive_dest_2 string SERVICE=tianjin ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tianjin #正常情况下应该有值

经过检查log_archive_dest_2的值被清空了，所以归档日志当然也传送不到备库；

修改脚本：ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=tianjin ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tianjin';

经过设置，终于可以把主库的数据发送到备库了，两边的数据也同步。

附：备库的日志

Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch

RFS[1]: Assigned to RFS process 16357

RFS[1]: Selected log 4 for thread 1 sequence 40 dbid -1418497875 branch 896836209

Wed Jan 06 16:04:05 2016

Primary database is in MAXIMUM PERFORMANCE mode

RFS[2]: Assigned to RFS process 16359

RFS[2]: Selected log 5 for thread 1 sequence 41 dbid -1418497875 branch 896836209

Wed Jan 06 16:04:05 2016

Archived Log entry 19 added for thread 1 sequence 40 ID 0xab7334ad dest 1:

Wed Jan 06 16:04:05 2016

Fetching gap sequence in thread 1, gap sequence 34-39

Wed Jan 06 16:04:06 2016

RFS[3]: Assigned to RFS process 16361

RFS[3]: Opened log for thread 1 sequence 36 dbid -1418497875 branch 896836209

Wed Jan 06 16:04:06 2016

RFS[4]: Assigned to RFS process 16363

RFS[4]: Opened log for thread 1 sequence 34 dbid -1418497875 branch 896836209

Archived Log entry 20 added for thread 1 sequence 36 rlc 896836209 ID 0xab7334ad dest 2:

Wed Jan 06 16:04:06 2016

RFS[5]: Assigned to RFS process 16365

RFS[5]: Opened log for thread 1 sequence 35 dbid -1418497875 branch 896836209

RFS[3]: Opened log for thread 1 sequence 37 dbid -1418497875 branch 896836209

Archived Log entry 21 added for thread 1 sequence 37 rlc 896836209 ID 0xab7334ad dest 2:

Archived Log entry 22 added for thread 1 sequence 35 rlc 896836209 ID 0xab7334ad dest 2:

Archived Log entry 23 added for thread 1 sequence 34 rlc 896836209 ID 0xab7334ad dest 2:

RFS[3]: Opened log for thread 1 sequence 38 dbid -1418497875 branch 896836209

RFS[5]: Opened log for thread 1 sequence 39 dbid -1418497875 branch 896836209

Archived Log entry 24 added for thread 1 sequence 38 rlc 896836209 ID 0xab7334ad dest 2:

Archived Log entry 25 added for thread 1 sequence 39 rlc 896836209 ID 0xab7334ad dest 2:

Media Recovery Log /u01/app/oracle/arch/1_34_896836209.dbf

Media Recovery Log /u01/app/oracle/arch/1_35_896836209.dbf

Media Recovery Log /u01/app/oracle/arch/1_36_896836209.dbf

Media Recovery Log /u01/app/oracle/arch/1_37_896836209.dbf

Media Recovery Log /u01/app/oracle/arch/1_38_896836209.dbf

Media Recovery Log /u01/app/oracle/arch/1_39_896836209.dbf

Media Recovery Log /u01/app/oracle/arch/1_40_896836209.dbf

ITPub博客

Dataguard从库日志不同步的原因

最新文章