oracle adg备库归档满了无法同步

  • long_small

    2022-11-18 20:07:52
  • Oracle
  • 原创

应用反馈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.


请使用浏览器的分享功能分享到微信等