Physical Standby – Check the effects of deletion policy set to “APPLIED ON ALL STANDBY”

Posted: March 11, 2013 in FRA
Tags: , ,

In my previous two posts (Physical Standby) and (Automatic Archive Deletion),i discussed about creation of standby and deleting archives automatically from standby. Now, it’s time to discuss the effects of setting the archive deletion policy to “APPLIED ON ALL STANDBY” on primary DB.

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Once, archive deletion policy is set to “APPLIED ON ALL STANDBY” then archives will be only deleted from primary, once they are applied on standby db. Suppose, if log_archive_dest_state_2 parameter is set to “DEFER”, in this case archives will not be shipped to standby and that also means that they will not be deleted from FRA, if any space pressure comes. So, whenever, archives are not shipping to standby, be extra carefull for FRA space. Here are some lines from init.ora file of primary, if parameter is “DEFER” and space issue comes for primary FRA:

SQL> alter system set log_archive_dest_state_2=’DEFER’ scope=both;

System altered.

Mon Mar 11 09:25:22 2013
Thread 1 advanced to log sequence 3129 (LGWR switch)
Current log# 3 seq# 3129 mem# 0: /u02/oradata/PLMQDBS/redo03a.log
Current log# 3 seq# 3129 mem# 1: /u03/oradata/PLMQDBS/redo03b.log
Mon Mar 11 09:25:22 2013
Errors in file /u01/app/oraplq_s/admin/diag/rdbms/plmqdbs/PLMQDBS/trace/PLMQDBS_arc3_62914656.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 241172480 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file /u01/app/oraplq_s/admin/diag/rdbms/plmqdbs/PLMQDBS/trace/PLMQDBS_arc3_62914656.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43520 bytes disk space from 241172480 limit
ARC3: Error 19809 Creating archive log file to ‘/u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_11/o1_mf_1_3128_%u_.arc’
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance PLMQDBS – Archival Error
ORA-16038: log 2 sequence# 3128 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: ‘/u02/oradata/PLMQDBS/redo02a.log’
ORA-00312: online log 2 thread 1: ‘/u03/oradata/PLMQDBS/redo02b.log’
Errors in file /u01/app/oraplq_s/admin/diag/rdbms/plmqdbs/PLMQDBS/trace/PLMQDBS_arc3_62914656.trc:
ORA-16038: log 2 sequence# 3128 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: ‘/u02/oradata/PLMQDBS/redo02a.log’
ORA-00312: online log 2 thread 1: ‘/u03/oradata/PLMQDBS/redo02b.log’
Mon Mar 11 09:25:33 2013
Thread 1 advanced to log sequence 3130 (LGWR switch)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s