Archive for the ‘FRA’ Category

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)

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.

Enable Flash Recovery Area In 11g

Posted: March 5, 2013 in FRA

Here are steps to enable the flash recovery area in 11g and then check the existing backup policy is working fine or not.
My goals are:

1) Eanble the flash recovery area
2) Enable the flashback logs for database
3) Only archive logs should go in flash recovery area

Till, now archives are getting generated on normal mountpoint on server. But, to implement the automatic deletion of archives
in physical standby environment, it required to setup FRA at PROD as well as standby site. I will come to standby part in next
post. For this post, i will only be doing FRA implement on PROD.

Currently, archives are generated on normal disk location.


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u05/oradata/CORE/
Oldest online log sequence 2088
Next log sequence to archive 2090
Current log sequence 2090


SQL> alter system set db_recovery_file_dest_size=10g scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/u05/oradata/CORE' scope=both;

System altered.

Now, i have enabled the FRA by setting above two parameters. Both parameters are dynamic and doesn’t require a database restart.
Enable database flashback logs as:


SQL> alter database flashback on;

Database altered.

Now, change the location of archive logs, so that archives now generated in FRA.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CORE' scope=both;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2089
Next log sequence to archive 2091
Current log sequence 2091
SQL>
SQL>

Now, if i go to FRA location, i will see the directory structure as:


/u05/oradata/CORE
oracore@cph-core-db01-s $ ls -lrt
total 40
drwxr-xr-x 2 oracore oinstall 256 Mar 29 2012 lost+found
drwxr-x--- 4 oracore oinstall 256 Mar 5 11:35 CORE

A new folder will be created with database name (CORE is DB name in my case). If i go to core, i will see following:

oracore@cph-core-db01-s $ cd CORE
oracore@cph-core-db01-s $ ls -lrt
total 0
drwxr-x--- 2 oracore oinstall 256 Mar 5 11:32 flashback
drwxr-x--- 3 oracore oinstall 256 Mar 5 11:35 archivelog

flashback : This will contain the file (.flb) containing the flashback logs for database.

archivelog: This will conatin the archivelogs generated in OMF format and a new folder will be creates automatically
for daily archives.

Apart from these folders, few more folders will be created depending upon you put backups in FRA or not.

backupset: This will contain the RMAN backup files.

datafile : This will contain the image copies backups.

autobackup: This will contain the controlfile autobackup.

controlfile: This will contain the controlfile, if you put one of the controlfiles in FRA

onlinelogs : This will contain online redo log member, if one member is put in FRA.

Now, when i switch logfile, archive got generated in FRA and parameter log_archive_format has no effect on file name
generated in FRA.


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2089
Next log sequence to archive 2091
Current log sequence 2091
SQL>

I checked the backup policy for archives, which says archive backedup 2 times should be deleted and this is
working fine after implementation of FRA too. No need to say that other backups L0 & L1 also ran fine.


RMAN> RUN {
2> ALLOCATE CHANNEL ch1 TYPE DISK;
3> sql 'alter system archive log current';
4> BACKUP FORMAT '/oraclebackup/CORE/ARC_1HR_%d_%D%M%Y_%u_%s_%p' ARCHIVELOG ALL NOT BACKED UP 2 TIMES TAG ='ARC_1HR';
5> DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;
RELEASE CHANNEL ch1;
6> 7> }

allocated channel: ch1
channel ch1: SID=223 device type=DISK

sql statement: alter system archive log current

Starting backup at 05-MAR-13
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2095 RECID=3426 STAMP=809266809
input archived log thread=1 sequence=2096 RECID=3427 STAMP=809266816
input archived log thread=1 sequence=2097 RECID=3428 STAMP=809266821
input archived log thread=1 sequence=2098 RECID=3429 STAMP=809266882
input archived log thread=1 sequence=2099 RECID=3430 STAMP=809266883
input archived log thread=1 sequence=2100 RECID=3431 STAMP=809266917
input archived log thread=1 sequence=2101 RECID=3432 STAMP=809266918
channel ch1: starting piece 1 at 05-MAR-13
channel ch1: finished piece 1 at 05-MAR-13
piece handle=/oraclebackup/CORE/ARC_1HR_CORE_05032013_6mo3orn7_214_1 tag=ARC_1HR comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAR-13

RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2098_8mco9l5v_.arc thread=1 sequence=2098
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2099_8mco9m9k_.arc thread=1 sequence=2099
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2100_8mcobobl_.arc thread=1 sequence=2100
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2101_8mcobpc2_.arc thread=1 sequence=2101
List of Archived Log Copies for database with db_unique_name CORE
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
757680 1 2095 A 05-MAR-13
Name: /u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2095_8mco7921_.arc

757684 1 2096 A 05-MAR-13
Name: /u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2096_8mco7jkw_.arc

757689 1 2097 A 05-MAR-13
Name: /u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2097_8mco7o0z_.arc

deleted archived log
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2095_8mco7921_.arc RECID=3426 STAMP=809266809
deleted archived log
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2096_8mco7jkw_.arc RECID=3427 STAMP=809266816
deleted archived log
archived log file name=/u05/oradata/CORE/CORE/archivelog/2013_03_05/o1_mf_1_2097_8mco7o0z_.arc RECID=3428 STAMP=809266821
Deleted 3 objects

released channel: ch1

RMAN> exit