Applied information doesn't synced between primary and standby.

From: Bin <wangbinlxx_at_gmail.com>
Date: 18 Feb 2007 20:20:51 -0800
Message-ID: <1171858851.525667.174820@p10g2000cwp.googlegroups.com>

I try to test using DELETION POLICY TO APPLIED ON STANDBY to manage archived redo logs on primary DB.

Primary DB:
db_recovery_file_dest /u01/oradata/prod/ flash_recovery_area

db_recovery_file_dest_size      460M -- set very small for testing.
log_archive_dest_2                 SERVICE=std OPTIONAL REOPEN=60

*._log_deletion_policy='ALL'

RMAN on Primary DB:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; Then I run DML to generate redos on Primary DB to make flash_recovery_area full.

du -sh /u01/oradata/prod/flash_recovery_area/PROD/archivelog/ 2007_02_19
451M /u01/oradata/prod/flash_recovery_area/PROD/archivelog/ 2007_02_19
ls -lrt /u01/oradata/prod/flash_recovery_area/PROD/archivelog/ 2007_02_19/
total 461088
-rw-r----- 1 oracle dba 47112192 Feb 19 03:02
o1_mf_1_116_2xl4sd4j_.arc
-rw-r----- 1 oracle dba 47112704 Feb 19 03:33
o1_mf_1_117_2xl6mxc2_.arc
-rw-r----- 1 oracle dba 47119360 Feb 19 03:33
o1_mf_1_118_2xl6n2xs_.arc
...
-rw-r----- 1 oracle dba 47119360 Feb 19 03:35
o1_mf_1_125_2xl6qoor_.arc

NAME                                     SPACE_LIMIT SPACE_USED

SPACE_RECLAIMABLE

---------- ----------------- /u01/oradata/prod/flash_recovery_area 460 449.324219 0

And in alert log of Primary DB, it says
ORA-19815: WARNING: db_recovery_file_dest_size of 482344960 bytes is 100.00% used, and has 0 remaining bytes available.

Then Primary DB hung.

However, all the archived redo logs has applied on standby, primary DB should remove those archived redo logs automatically.

Run this on standby DB:
select applied, thread#, max(sequence#), to_char(MAX(COMPLETION_TIME),'yyyymmdd hh24:mi:ss') COMPLETION_TIME from v$archived_log
group by applied, thread#;

APP THREAD# sequence# COMPLETION_TIME
--- ---------- -------------- -----------------
YES 1 125 20070219 03:35:27 But if I check Primary DB,

SQL> r
select applied, sequence#, to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') COMPLETION_TIME
from v$archived_log
where sequence# > 115 and NAME ='std'

APP SEQUENCE# COMPLETION_TIME
--- ---------- -----------------

YES        116 20070219 03:02:16
NO         117 20070219 03:33:27
YES        118 20070219 03:33:34
NO         119 20070219 03:33:41
NO         120 20070219 03:33:47
NO         121 20070219 03:34:09
NO         122 20070219 03:34:16
NO         123 20070219 03:34:23
YES        124 20070219 03:34:29
YES        125 20070219 03:35:27

It seems that applied information doesn't sync properly from standby to primary. For example, o1_mf_1_117_2xl6mxc2_.arc is marked applied in standby and NO in primary.

What went wrong here?

Thanks,
Bin Received on Sun Feb 18 2007 - 22:20:51 CST