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