應用反饋oracle 11G adg似乎資料不同步資料了，懷疑目錄是不是有問題

檢查

1.1 主庫

SQL> col dest_name for a25;

SQL> select dest_name,status,error from v$archive_dest;

DEST_NAME STATUS

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

--status列有錯誤，提示歸檔目錄log_archive_dest_2無法建立日誌

由此判斷，standby備庫歸檔可能是滿了或者出問題了

show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE

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

log_archive_dest_2 string service=orcldg LGWR SYNC VALID

_FOR=(ONLINE_LOGFILES,PRIMARY_

ROLE) DB_UNIQUE_NAME=orcldg

1.2 備庫歸檔日誌檢視

登入備庫，檢視歸檔目錄為/oradata/oraBACK。df -h 檢視該目錄（ /oradata/oraBACK）已經100%；

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata/oraBACK

Oldest online log sequence 95915

Next log sequence to archive 0

Current log sequence 95917

SQL> show parameter arch;

NAME TYPE VALUE

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

archive_lag_target integer 0

log_archive_config string DG_CONFIG=(orcl,orcldg)

log_archive_dest string

log_archive_dest_1 string LOCATION=/oradata/oraBACK VALI

D_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=orcldg

log_archive_dest_10 string

log_archive_dest_11 string

.......

log_archive_dest_18 string

log_archive_dest_19 string

log_archive_dest_2 string SERVICE=orcl LGWR ASYNC VALID_

FOR=(ONLINE_LOGFILES,PRIMARY_R

cd /oradata/oraBACK

ls -lrt |more

-rw-r----- 1 oracle oinstall 43429888 7月 17 09:07 ./1_64658_951127686.dbf

-rw-r----- 1 oracle oinstall 45067264 7月 18 17:03 ./1_65000_951127686.dbf

-rw-r----- 1 oracle oinstall 1186816 7月 18 02:00 ./1_64866_951127686.dbf

-rw-r----- 1 oracle oinstall 40103936 7月 20 00:00 ./1_65393_951127686.dbf

-rw-r----- 1 oracle oinstall 36207616 7月 19 01:25 ./1_65139_951127686.dbf

-rw-r----- 1 oracle oinstall 45067776 7月 20 00:03 ./1_65396_951127686.dbf

-rw-r----- 1 oracle oinstall 46229504 7月 20 09:04 ./1_65481_951127686.dbf

-rw-r----- 1 oracle oinstall 49913344 7月 18 21:08 ./1_65030_951127686.dbf

7月份的歸檔竟然還在，現在時間是2022-11-18了

find -mtime +120 -name "*.dbf" -exec ls -lrt {} \;

120天前還有歸檔日誌檔案存在

1.3 檢視standby的MRP0程式，當前應用到 95504號日誌

從庫

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_GAP 1 95504 0 0

檢視主庫應用過的日誌

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

col name for a55

SELECT THREAD#, name,SEQUENCE#, applied,next_time FROM V$ARCHIVED_LOG where next_time>'2022-11-15' and next_time>'2022-11-16';

--標記在 95504

THREAD# NAME SEQUENCE# APPLIED NEXT_TIME

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

1 orcldg 95497 YES 2022-11-17 21:08:26

1 95497 NO 2022-11-17 21:08:26

1.4 清理30天前的歸檔日誌

還有很多歸檔日誌檔案，想想這是多久沒有刪除了

cd /oradata/oraBACK

find -mtime +90 -name "*.dbf" -exec ls -lrt {} \;

find -mtime +120 -name "*.dbf" -exec rm -fr {} \;

find -mtime +90 -name "*.dbf" -exec rm -fr {} \;

find -mtime +60 -name "*.dbf" -exec rm -fr {} \;

find -mtime +30 -name "*.dbf" -exec rm -fr {} \;

1.5 檢視備庫應用情況

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

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

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_GAP 1 95525 0 0

RFS RECEIVING 1 95527 47105 2048

RFS RECEIVING 1 95526 16385 2048

RFS RECEIVING 1 95525 16385 2048

檢視主從延遲情況

SQL> select name,value from v$dataguard_stats;

NAME VALUE

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

transport lag +00 00:25:16

apply lag +00 00:25:16

apply finish time +00 00:00:00.024

estimated startup time 9

有25分鐘延遲， 40分鐘後再檢視，apply lag的value為0

SQL> /

NAME VALUE

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

transport lag +00 00:00:00

apply lag +00 00:00:00

apply finish time +00 00:00:00.000

estimated startup time 9

檢視主庫應用過的日誌，標記為yes的，應用到最後三個日誌，主從追平

SELECT THREAD#, name,SEQUENCE#, applied,next_time FROM V$ARCHIVED_LOG where next_time>'2022-11-15';

orcldg 95907 YES 2022-11-18 17:37:05

1 orcldg 95908 YES 2022-11-18 17:37:10

1 orcldg 95911 YES 2022-11-18 17:43:37

1 orcldg 95910 YES 2022-11-18 17:40:04

1 orcldg 95914 YES 2022-11-18 17:49:37

THREAD# NAME SEQUENCE# APPLIED NEXT_TIME

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

1 orcldg 95913 YES 2022-11-18 17:43:52

1 orcldg 95915 YES 2022-11-18 17:55:37

1 orcldg 95912 YES 2022-11-18 17:43:47

1 orcldg 95918 YES 2022-11-18 18:07:37

1 orcldg 95919 YES 2022-11-18 18:13:38

1 orcldg 95916 YES 2022-11-18 18:01:37

1 orcldg 95917 YES 2022-11-18 18:03:07

1 orcldg 95922 YES 2022-11-18 18:30:20

1 /oradata/oraBACK/1_95922_951127686.dbf 95922 NO 2022-11-18 18:30:20

1 orcldg 95923 YES 2022-11-18 18:30:21

1 /oradata/oraBACK/1_95923_951127686.dbf 95923 NO 2022-11-18 18:30:21

THREAD# NAME SEQUENCE# APPLIED NEXT_TIME

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

1 orcldg 95924 YES 2022-11-18 18:30:22

1 /oradata/oraBACK/1_95924_951127686.dbf 95924 NO 2022-11-18 18:30:22

檢視主庫error，ERROR為空，錯誤消失了，至此主從重新同步

col dest_name for a30

set line 220

col error for a30

select dest_name,status,error from v$ARCHIVE_DEST;

DEST_NAME STATUS ERROR

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

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 VALID

LOG_ARCHIVE_DEST_3 INACTIVE

LOG_ARCHIVE_DEST_4 INACTIVE

LOG_ARCHIVE_DEST_5 INACTIVE

LOG_ARCHIVE_DEST_6 INACTIVE

LOG_ARCHIVE_DEST_7 INACTIVE

LOG_ARCHIVE_DEST_8 INACTIVE

LOG_ARCHIVE_DEST_9 INACTIVE

LOG_ARCHIVE_DEST_10 INACTIVE

LOG_ARCHIVE_DEST_11 INACTIVE

DEST_NAME STATUS ERROR

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

LOG_ARCHIVE_DEST_12 INACTIVE

LOG_ARCHIVE_DEST_13 INACTIVE

LOG_ARCHIVE_DEST_14 INACTIVE

LOG_ARCHIVE_DEST_15 INACTIVE

LOG_ARCHIVE_DEST_16 INACTIVE

LOG_ARCHIVE_DEST_17 INACTIVE

LOG_ARCHIVE_DEST_18 INACTIVE

LOG_ARCHIVE_DEST_19 INACTIVE

LOG_ARCHIVE_DEST_20 INACTIVE

LOG_ARCHIVE_DEST_21 INACTIVE

LOG_ARCHIVE_DEST_22 INACTIVE

DEST_NAME STATUS ERROR

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

LOG_ARCHIVE_DEST_23 INACTIVE

LOG_ARCHIVE_DEST_24 INACTIVE

LOG_ARCHIVE_DEST_25 INACTIVE

LOG_ARCHIVE_DEST_26 INACTIVE

LOG_ARCHIVE_DEST_27 INACTIVE

LOG_ARCHIVE_DEST_28 INACTIVE

LOG_ARCHIVE_DEST_29 INACTIVE

LOG_ARCHIVE_DEST_30 INACTIVE

LOG_ARCHIVE_DEST_31 INACTIVE

31 rows selected.

oracle adg備庫歸檔滿了無法同步

相關文章