背景：測試環境突然發現，主備庫不能同步了，檢視備庫的日誌發現備庫一直處於等待接收日誌的狀態；

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

Dataguard從庫日誌不同步的原因

相關文章