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.dbf7月份的归档竟然还在，现在时间是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/oraBACKfind  -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.