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

應用反饋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

ARCH   CONNECTED 0    0       0 0

ARCH   CONNECTED 0    0       0 0

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

ARCH   CONNECTED 0    0       0   0

ARCH   CONNECTED 0    0       0   0

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.