Physical Standby – Configure Automatic Archive Deletion From Standby

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

In my previous post (Physical Standby), i have configured Physical standby database with FRA implemented on Primary as well as standby database. Now, Let’s see how we can configure the automatic archive deletion from standby.

Actually, this automatic archive deletion is feature of FRA. I am generating the archives in FRA only, so, whenever there is a space issue in FRA, it tends to delet the unwanted files from FRA, which in my case are archive logs which are applied on standby.

Here is size of FRA folder on primary:

/u05/oradata/PLMQDBS/flash
oraplq_s@myhost1 $ ls -lrt
total 0
drwxr-x— 4 oraplq_s oinstall 256 Mar 5 14:45 PLMQDBS
oraplq_s@myhost1 $ du -sm PLMQDBS
268.32 PLMQDBS

Here is size of FRA folder at standby:

oracore@myhost $ pwd
/u05/oradata/CORE/flash
oracore@myhost $ ls -lrt
total 0
drwxr-x— 4 oracore oinstall 256 Mar 5 16:56 PLMQDBST
oracore@myhost $ du -sm PLMQDBST
593.02 PLMQDBST
oracore@myhost $

Now, Let’s reduce the size of FRA folder on standby and see if archives got deleted or not. Since current size is 593MB, so if i change the value of db_recovery_file_dest_size to near 600MB, FRA will be in space crunch and should start the clean up. Let check:

SQL> show parameter db_recovery

NAME TYPE VALUE
———————————— ——————————– ——————————
db_recovery_file_dest string /u05/oradata/CORE/flash
db_recovery_file_dest_size big integer 7G
SQL>
SQL> alter system set db_recovery_file_dest_size=600M scope=both;
alter system set db_recovery_file_dest_size=600M scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL> alter system set db_recovery_file_dest_size=600M;

System altered.

As soon as i changed the FRA folder size on standby to 600MB, old archive logs are delete from FRA automatically. Below is small output from the init.ora file of standby database:

RFS[2]: Opened log for thread 1 sequence 3065 dbid 165665636 branch 781470756
Sun Mar 10 16:25:23 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3064_8mrpfq48_.arc
Archived Log entry 35 added for thread 1 sequence 3065 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3066 dbid 165665636 branch 781470756
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3065_8ms9gyt4_.arc
Archived Log entry 36 added for thread 1 sequence 3066 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3067 dbid 165665636 branch 781470756
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3066_8ms9h4bm_.arc
Media Recovery Waiting for thread 1 sequence 3067 (in transit)
Sun Mar 10 16:48:08 2013
ALTER SYSTEM SET db_recovery_file_dest_size=’600M’ SCOPE=MEMORY;
Sun Mar 10 16:48:09 2013
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3031_8mk06df6_.arc
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3032_8mk06dv4_.arc
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3033_8mk06lhs_.arc
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3034_8mk08z0z_.arc
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3035_8mkgdcs9_.arc
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_07/o1_mf_1_3036_8mkgdgxw_.arc

Now, checked the size of FRA folder on standby, and space has been released by archive deletion.

oracore@myhost $ du -sm PLMQDBST
496.12 PLMQDBST

Now, let’s check same thing on Primary also. I know that it will work on primary as well, but i want to check if there is any cascade effect of delete from primary to standby i.e. if archives got deleted on primary due to FRA sapce issue then will the archives will be deleted from standby too, even if standby FRA has space. Let’s check this out.

Current size of FRA folder on Primary:

oraplq_s@myhost1 $ du -sm PLMQDBS
245.95 PLMQDBS

Now, i changed the size of FRA on Primary to 250M, just to put pressure on FRA space.

SQL> show parameter db_recovery

NAME TYPE VALUE
———————————— ——————————– ——————————
db_recovery_file_dest string /u05/oradata/PLMQDBS/flash
db_recovery_file_dest_size big integer 2G

SQL> alter system set db_recovery_file_dest_size=250M scope=both;

System altered.

Then i simulated some work, just to generate archives.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
A_OB TABLE

SQL> insert into A_OB select * from dba_objects;

13795 rows created.

SQL> /

13795 rows created.

SQL> /

13795 rows created.

SQL> /

13795 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

As soon as log switch log occurs, archives got deleted for primary FRA. Below is some portion of init.ora file from primary:

Sun Mar 10 17:09:02 2013
Thread 1 advanced to log sequence 3081 (LGWR switch)
Current log# 3 seq# 3081 mem# 0: /u02/oradata/PLMQDBS/redo03a.log
Current log# 3 seq# 3081 mem# 1: /u03/oradata/PLMQDBS/redo03b.log
Sun Mar 10 17:09:02 2013
Archived Log entry 3172 added for thread 1 sequence 3080 ID 0x9e06a64 dest 1:
Sun Mar 10 17:10:13 2013
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3065_8ms9h479_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3066_8ms9h5dn_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3067_8mscjgf6_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3068_8mscjhnt_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3069_8msckk6t_.arc
Sun Mar 10 17:10:26 2013
Thread 1 advanced to log sequence 3082 (LGWR switch)
Current log# 1 seq# 3082 mem# 0: /u02/oradata/PLMQDBS/redo01a.log
Current log# 1 seq# 3082 mem# 1: /u03/oradata/PLMQDBS/redo01b.log
Sun Mar 10 17:10:26 2013
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3070_8mscngqq_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3071_8mscog3g_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3072_8mscoh8q_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3073_8mscojfw_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3074_8mscwrbz_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3075_8mscwslk_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3076_8mscwvpx_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3077_8mscy6hb_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3078_8mscy7j5_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/archivelog/2013_03_10/o1_mf_1_3079_8mscy9b1_.arc
Deleted Oracle managed file /u05/oradata/PLMQDBS/flash/PLMQDBS/flashback/o1_mf_8mdodmhv_.flb
Archived Log entry 3174 added for thread 1 sequence 3081 ID 0x9e06a64 dest 1:

At the same time there is no archive deleting at standby DB. Below is lines for init.ora of standby:

Sun Mar 10 17:07:36 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3077_8mscwvrm_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3078_8mscy6l3_.arc
Media Recovery Waiting for thread 1 sequence 3079 (in transit)
Archived Log entry 49 added for thread 1 sequence 3079 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3080 dbid 165665636 branch 781470756
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3079_8mscy7n0_.arc
Media Recovery Waiting for thread 1 sequence 3080 (in transit)
Sun Mar 10 17:09:02 2013
Archived Log entry 50 added for thread 1 sequence 3080 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3081 dbid 165665636 branch 781470756
Sun Mar 10 17:09:07 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3080_8mscy9gz_.arc
Media Recovery Waiting for thread 1 sequence 3081 (in transit)
Sun Mar 10 17:10:26 2013
Archived Log entry 51 added for thread 1 sequence 3081 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3082 dbid 165665636 branch 781470756
Sun Mar 10 17:10:28 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3081_8msd0ynd_.arc
Media Recovery Waiting for thread 1 sequence 3082 (in transit)
Archived Log entry 52 added for thread 1 sequence 3082 rlc 781470756 ID 0x9e06a64 dest 2:
RFS[2]: Opened log for thread 1 sequence 3083 dbid 165665636 branch 781470756
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_10/o1_mf_1_3082_8msd3lqr_.arc

So, it can be concluded that there is no cascading effects of archive logs deletion from primary to the standby. Whenever there is space issue on primary archives (unwanted files) will be deleted from FRA of primary only and same case goes for standby by. Both FRA’s are managed independently of each other.

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