Blog - comments

MOXX offers mobile internet access when you travel in France. Simply rent a Pocket WiFi device and s...
wifi france
Hello David,I think Oracle is behaving differently with 12.1.0.2.x. At least in my case I was able t...
David D'Acquisto

Bravo, il fallait y penser Mery Christmas

Eric

Great !! Thank you very much.

SEK

Great !! Thank you very much.

SEK
Blog Franck Pachot Archivelog deletion policy for Standby Database in Oracle Data Guard

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Archivelog deletion policy for Standby Database in Oracle Data Guard

Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That's good practice! But did you ever check that it works as expected?

What I mean is this:

  • The archived logs that you don't need are reclaimable by the FRA when space is needed
  • And the archived logs that are required for availability (standby or backup) are not deleted.

It's not an easy thing to check because Oracle doesn't show which archive log is reclaimable. Only the total reclaimable space is shown in v$recovery_area_usage. But that is not sufficient to validate which archivelog sequence is concerned. I'll show you below a query that returns the reclaimable status from the archived logs. And you will see that until 12c the APPLIED ON ALL STANDBY does not work as expected. You've probably seen a FRA full at standby site and solved it by deleting archived logs. But this is not the right solution because the FRA is supposed to do that.

Let's look at an example I encountered recently. The archivelog deletion policy is set correctly:

 

RMAN> show archivelog deletion policy;

RMAN configuration parameters for database with db_unique_name DATABASE_SITE2 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

 

This configuration, an 11g feature, allows to delete an archive log as soon as it is applied to all standby destinations. Note that it works if I manually do a 'delete archivelog all;' but I expect that the archivelogs in the FRA becomes reclaimable automatically.

Unfortunately, this is not the case and the FRA is growing:

 


SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG';

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG                      61.11                     43.02             467

 

Let's check everything. We are on the standby database:

 


SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

 

The archivelogs are going to the Fast Recovery Area:

 


SQL> show parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                 DEST, valid_for=(ALL_LOGFILES,
                                                  ALL_ROLES)

 

All archived logs are applied (we are in SYNC AFFIRM):

 


DGMGRL> show database 'DATABASE_SITE2';

Database - DATABASE_SITE2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    DATABASE

Database Status:
SUCCESS

 

Well, with that configuration, I expect that all archivelogs are reclaimable - except the current one.

Let's investigate. V$RECOVERY_AREA_USAGE is an aggregate view. If we check its definition, we see that the reclaimable size comes from x$kccagf.rectype.

So I'll use it in in conjunction with v$archived_log in order to give the detail about which archived logs are reclaimable:

 


SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
           ,count(*),min(sequence#),max(sequence#)
     from v$archived_log left outer join sys.x$kccagf using(recid) 
     where is_recovery_dest_file='YES' and name is not null
     group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/

APPLIED   DELETED RECLAIMABLE   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES       NO      YES                429           5938           6366
YES       NO      NO                  37           6367           6403
IN-MEMORY NO      NO                   1           6404           6404

 

The problem is there: Because of a bug (Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA) the archivelogs are not marked as reclaimable when the database is in mount mode.

The workaround is to execute dbms_backup_restore.refreshagedfiles. This is what must be scheduled (maybe daily) on the standby. It can be a good idea to do it at the same time as a daily 'delete obsolete', so here is the way to call it from RMAN:

RMAN> sql "begin dbms_backup_restore.refreshagedfiles; end;";

But I've found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag - even when there is no change.

Then, you can run a daily job that does CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY on your database, whatever the role is.

It's different for the database where you do the backup, because you want to be sure that the backup is done before an archivelog is deleted: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

This is a good way to prevent anyone from changing the configuration and keep it close to the backup scripts. At dbi services, we advise to keep the same configuration on all Data Guard sites for the same database so that a switchover can be done without any problem. For this reason, having a script that depends on the place where the backups are done is a better alternative than a configuration that depends on the database role.

Finally, here is the state of our reclaimable archivelogs after any of these solutions:

 


APPLIED   DELETED RECLAIMABLE   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES       NO      YES                466           5938           6403
IN-MEMORY NO      NO                   1           6404           6404

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG                      61.11                     61.09             467

 

All applied archived logs are reclaimable and the FRA will never be full.

You can check the primary as well. Are you sure that Oracle will never delete an archived log that has not been backed up ? Check your deletion policy.

Here is the full query I use for that:

 

column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
 ,decode(rectype,11,'YES','NO') reclaimable,count(*)
 ,to_char(min(completion_time),'dd-mon hh24:mi') first_time
 ,to_char(max(completion_time),'dd-mon hh24:mi') last_time
 ,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/

 

This is the result on primary where the last archivelog backup has run around 21:00


APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
NO        YES                1 NO               277 15-jan 17:56 19-jan 09:49      5936     6212
NO        NO                 1 YES              339 19-jan 10:09 22-jan 21:07      6213     6516
NO        NO                 0 NO                33 22-jan 21:27 23-jan 07:57      6517     6549


That is fine according to my policy APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK


And here is the result on standby where the workaround job has run around 06:00 and redo apply is in SYNC


APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
YES       YES                0 NO               746 07-jan 13:27 17-jan 11:17      5320     6065
YES       NO                 0 YES              477 17-jan 11:37 23-jan 05:37      6066     6542
YES       NO                 0 NO                 8 23-jan 05:57 23-jan 08:14      6543     6550
IN-MEMORY NO                 0 NO                 1 23-jan 08:15 23-jan 08:15      6551     6551


This is good for my policy APPLIED ON ALL STANDBY - except that because of the bug mentioned above, redo applied since 06:00 are not yet reclaimable.

 

Rate this blog entry:
3

I'm a Senior Consultant, and Oracle Technology Leader at dbi services (Switzerland).
Certified DBA (OCM 11g, OCP 12c, Performance Tuning Expert, Exadata Implementation) I cover all database areas: architecture, data modeling, database design, tuning, operation, and training.
My preferred area is troubleshooting oracle and performance tuning, especially when I acheive to enable an efficient collaboration between the developers and the
operational team.


As an Oracle Ace, I participate in the Oracle Community in forums, blogs, articles and presentation. You can follow my activity on this blog: RSS and my twitter account: @FranckPachot


O_Database12c_Admin_Professional_clrOCE_ODb11gPerfTun_clr11gocm_logoO_ACELogo_clr


 


 


 




Comments

  • Guest
    jko Friday, 14 March 2014

    Tested and validated:-)
    Cheers
    jko

  • Guest
    jun lu Friday, 30 May 2014

    "Then, you can run a daily job that does CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY on your database, whatever the role is."
    how to do this? a shell script?

  • Franck Pachot
    Franck Pachot Friday, 30 May 2014

    jun lu,
    usually we have a job that check the database role, and do the backup depending on the role. Then when you do the backup you also do 'APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK' and in the other cases you just do 'CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY'

  • Guest
    Rick chen Monday, 17 November 2014

    if the daily database&archivelog backup are executed on primary database, then on the standby site, just do 'CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY' cannot remove the applied archivelog files on standby site. should any other delete operations be taken on standby site ?
    Thanks
    Rick

  • Franck Pachot
    Franck Pachot Monday, 17 November 2014

    Hi Rick,
    Yes. The archived logs should become 'reclaimable' once applied. But you may encounter the bug above where the 'reclaimable' status is not refreshed automatically on a database in mount. You have either to exec dbms_backup_restore.refreshagedfiles; or to run the 'configure archivelog deletion policy' again.

  • Guest
    Rick Chen Monday, 17 November 2014

    Thanks Franck,
    ++1.My DG is in oracle12c(12.1.0.1), is this bug fixed in oracle12c ?
    ++2.The daily backup is executed in primary site, there are many archive log files in standby site. I run 'CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY' manually on standby site, no any archived log file being deleted. this is my problem. is there any other delete operation required on standby site?
    Regards and thanks,
    Rick

  • Franck Pachot
    Franck Pachot Monday, 17 November 2014

    Hi rick,
    As far as i know that bug is fixed in 12c and with your configuration you should not need any delete operation on standby site. You can use my query above to check which files (and whether they were applied).
    you can have all detail with the query in: http://www.dbi-services.com/index.php/blog/entry/drilling-down-vrecoveryareausage
    Regards,
    Franck.

  • Guest
    Rick CHEN Tuesday, 18 November 2014

    Thanks Franck,
    It doesnot work in my DG. Daily backup is on primary site. query on bote sites:
    --On Primary
    SQL> show parameter db_recover

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest string /u01/app/oracle/fast_recovery_
    area
    db_recovery_file_dest_size big integer 4800M
    SQL> select * from v$recovery_area_usage;

    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
    ----------------------- ------------------ ------------------------- --------------- ----------
    CONTROL FILE .21 0 1 0
    REDO LOG 7.29 0 7 0
    ARCHIVED LOG 1.81 1.81 12 0
    BACKUP PIECE .43 .22 2 0
    IMAGE COPY 0 0 0 0
    FLASHBACK LOG 0 0 0 0
    FOREIGN ARCHIVED LOG 0 0 0 0
    AUXILIARY DATAFILE COPY 0 0 0 0

    8 rows selected.

    SQL> select sequence#, name, applied, deleted, IS_RECOVERY_DEST_FILE, backup_count, standby_dest from v$archived_log where sequence# between 2005 and 2014;

    SEQUENCE# NAME APPLIED DEL IS_ BACKUP_COUNT STA
    ---------- ---------------------------------------------------------------------------------------- --------- --- --- ------------ ---
    2005 sbdb YES NO NO 0 YES
    2005 NO YES YES 0 NO
    2006 sbdb YES NO NO 0 YES
    2006 NO YES YES 0 NO
    2007 sbdb YES NO NO 0 YES
    2007 NO YES YES 0 NO
    2008 sbdb YES NO NO 0 YES
    2008 NO YES YES 0 NO
    2009 sbdb YES NO NO 0 YES
    2009 NO YES YES 0 NO
    2010 sbdb YES NO NO 0 YES
    2010 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2010_b6nbzjyq_.arc NO NO YES 1 NO
    2011 sbdb YES NO NO 0 YES
    2011 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2011_b6nc6mtq_.arc NO NO YES 1 NO
    2012 sbdb YES NO NO 0 YES
    2012 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2012_b6nc6n6c_.arc NO NO YES 1 NO
    2013 sbdb YES NO NO 0 YES
    2013 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2013_b6nc6s33_.arc NO NO YES 1 NO
    2014 sbdb YES NO NO 0 YES
    2014 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2014_b6nz59ml_.arc NO NO YES 0 NO

    20 rows selected.

    SQL> select database_role, open_mode, current_scn, db_unique_name from v$database;

    DATABASE_ROLE OPEN_MODE CURRENT_SCN DB_UNIQUE_NAME
    ---------------- -------------------- ----------- ------------------------------
    PRIMARY READ WRITE 32384354 prod

    SQL>

    --On standby:
    SQL> show parameter db_recover

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest string /home/oracle/fra
    db_recovery_file_dest_size big integer 4800M
    SQL> select * from v$recovery_area_usage;

    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
    ----------------------- ------------------ ------------------------- --------------- ----------
    CONTROL FILE .26 0 1 0
    REDO LOG 7.29 0 7 0
    ARCHIVED LOG 5.95 0 54 0
    BACKUP PIECE .52 .26 2 0
    IMAGE COPY 0 0 0 0
    FLASHBACK LOG 0 0 0 0
    FOREIGN ARCHIVED LOG 0 0 0 0
    AUXILIARY DATAFILE COPY 0 0 0 0

    8 rows selected.

    SQL> select sequence#, name, applied, deleted, IS_RECOVERY_DEST_FILE, backup_count, standby_dest from v$archived_log where sequence# between 2005 and 2014;

    SEQUENCE# NAME APPLIED DEL IS_ BACKUP_COUNT STA
    ---------- ------------------------------------------------------------------------ --------- --- --- ------------ ---
    2005 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2005_b6mrj0xb_.arc YES NO YES 0 NO
    2006 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2006_b6mrv5wl_.arc YES NO YES 0 NO
    2007 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2007_b6mrxzl8_.arc YES NO YES 0 NO
    2008 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2008_b6ms1d1l_.arc YES NO YES 0 NO
    2009 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2009_b6nbzjp5_.arc YES NO YES 0 NO
    2010 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2010_b6nbzky2_.arc YES NO YES 0 NO
    2011 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2011_b6nc6nqn_.arc YES NO YES 0 NO
    2012 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2012_b6nc6o6b_.arc YES NO YES 0 NO
    2013 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2013_b6nc6t1j_.arc YES NO YES 0 NO
    2014 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2014_b6nz5bh1_.arc YES NO YES 0 NO

    10 rows selected.

    SQL> select database_role, open_mode, current_scn, db_unique_name from v$database;

    DATABASE_ROLE OPEN_MODE CURRENT_SCN DB_UNIQUE_NAME
    ---------------- -------------------- ----------- ------------------------------
    PHYSICAL STANDBY READ ONLY WITH APPLY 32384482 sbdb

    SQL>

  • Guest
    Rick CHEN Tuesday, 18 November 2014

    Continued:
    After RMAN backup&delete taken in primary site, all archived log files (applied on all standby) have been deleted, but those files on standby site havenot, still there. checked and confirmed on both DB and OS (on standby site).

    Regards,
    Rick CHEN

  • Guest
    Rick CHEN Tuesday, 18 November 2014

    --On Primary:
    SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable,
    count(*),min(sequence#),max(sequence#)
    2 3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file='YES' and name is not null
    5 group by applied,deleted,decode(rectype,11,'YES','NO') order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- --- ---------- -------------- --------------
    NO NO YES 11 2010 2020
    NO NO NO 1 2021 2021

    SQL>

    --On Standby
    SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable,
    count(*),min(sequence#),max(sequence#)
    2 3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file='YES' and name is not null
    5 group by applied,deleted,decode(rectype,11,'YES','NO') order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- --- ---------- -------------- --------------
    YES NO NO 53 1968 2020
    IN-MEMORY NO NO 1 2021 2021

    SQL>

  • Franck Pachot
    Franck Pachot Tuesday, 18 November 2014

    Hi Rick,
    Thanks to have given all information.
    From that it seems that there is still a bug. Another bug because the one I was talking about is only when database is mount. Archivelog that have APPLIED=YES should become reclaimable when ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY. You have only one standby (no cascading), right? I think you can open a SR with that.

  • Guest
    Rick CHEN Tuesday, 18 November 2014

    Thanks Franck,
    ++1. Yes, only one standby standby site, and no any cascading standby site.
    ++2. May I make it clear, is it on the standby site or primar site, to execute "CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY" ?
    ++3. Let me change standby DB to mount status with MRP started, and check rectype of archived log files on standby site.
    Regards,
    Rick CHEN

  • Franck Pachot
    Franck Pachot Tuesday, 18 November 2014

    It's on the standby site that you ever re-run the configure or executed the refreshagedfiles, as a workaround for the bug

  • Guest
    Rick CHEN Tuesday, 18 November 2014

    --On standby site:
    restart standby database to mounted status and start MRP process
    --On primary site:
    several archive log current operations.

    then check again:
    --Primary site:
    SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable,
    count(*),min(sequence#),max(sequence#)
    2 3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file='YES' and name is not null
    5 group by applied,deleted,decode(rectype,11,'YES','NO') order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- --- ---------- -------------- --------------
    NO NO YES 18 2010 2027
    NO NO NO 1 2028 2028

    SQL>

    --standby site:
    SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable,
    2 count(*),min(sequence#),max(sequence#)
    3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file='YES' and name is not null
    5 group by applied,deleted,decode(rectype,11,'YES','NO') order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- --- ---------- -------------- --------------
    YES NO NO 60 1968 2027
    IN-MEMORY NO NO 1 2028 2028

    SQL>

  • Guest
    Rick CHEN Wednesday, 19 November 2014

    Thanks Franck,
    On standby site re-run configure manually , it works.
    Now the question is how to delete all those applied&reclaimable archived log files on standby site.
    When backup and delete archivelog all on primary, it seems no affact on standby site. Reclaimable archived log files have NOT been deleted on standby site.
    --On Primary Site:
    SQL> select applied,deleted, rectype, decode(rectype,11,'YES','NO') reclaimable, count(*),min(sequence#),max(sequence#)
    2 from v$archived_log left outer join sys.x$kccagf using(recid)
    where is_recovery_dest_file='YES' and name is not null
    3 4 group by applied,deleted,rectype, decode(rectype,11,'YES','NO')
    5 order by 6;

    APPLIED DEL RECTYPE REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- ---------- --- ---------- -------------- --------------
    NO NO 11 YES 3 2079 2081
    NO NO NO 2 2082 2083

    SQL>
    --On Standby Site:
    SQL> select applied,deleted, rectype, decode(rectype,11,'YES','NO') reclaimable, count(*),min(sequence#),max(sequence#)
    2 from v$archived_log left outer join sys.x$kccagf using(recid)
    3 where is_recovery_dest_file='YES' and name is not null
    4 group by applied,deleted,rectype, decode(rectype,11,'YES','NO')
    5 order by 6;

    APPLIED DEL RECTYPE REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- ---------- --- ---------- -------------- --------------
    YES NO 11 YES 113 1968 2080
    YES NO NO 2 2081 2082
    IN-MEMORY NO NO 1 2083 2083

    SQL>

  • Franck Pachot
    Franck Pachot Wednesday, 19 November 2014

    Hi, I've read only quickly and will come back on it (I'm currently attending DOAG) but it seems that it works. Once files are flagged as reclaimable then they will be deleted once oracle needs space on the FRA.

  • Guest
    Rick CHEN Wednesday, 19 November 2014

    Yes, it does.
    Waiting for oracle delete once space needed on FRA is a passive solution.
    Is there any active solution to delete those reclaimable archived logs to release free space ? especially if it is on the primary site with RMAN backup.

    Regards,
    Rick

  • Franck Pachot
    Franck Pachot Thursday, 20 November 2014

    Hi Rick,
    Yes there is. The delete archivelog (without FORCE) from rman should delete only the reclaimable archived logs. But you usually don't need that. The big advantage of the Fast Recovery Area is that the files are managed by Oracle. You just have to adapt monitoring to monitor v$recovery_area_usage.
    Personally, I find the 'passive solution' term a bit pejorative, as it is the exactly what's cool in the feature: you keep files as long you don't need to delete them. Then faster when you need to recover from them.

  • Guest
    Rick Chen Friday, 21 November 2014

    Thanks Franck,
    Let's change active/passive solution to other words. how about "RMAN script manual management" and "Oracle server automatic management"
    I wonder how we can delete those reclaimable archived logs of standby site by using "RMAN script management" on primary site ?
    You know, sometime we just want to manage archived logs on both sites, e.g. by "RMAN script" , what is the specific delete statement ?
    Regards and thanks,
    Rick Chen

  • Guest
    Rick Chen Friday, 21 November 2014

    Sometimes with a group of transactions generating many archived logs, shipped and applied on standby site, and are reclaimable. With monitoring v$reocvery_area_usage most space are used, but we are not sure when those space will be reclaimed by oracle server.
    In the test DG testing environments, waiting more than one hour, all reclaimable archived logs have not been deleted yet by oracle server automatically.
    SQL> select * from v$recovery_area_usage;

    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
    ----------------------- ------------------ ------------------------- --------------- ----------
    CONTROL FILE .6 0 1 0
    REDO LOG 17.09 0 7 0
    ARCHIVED LOG 59.87 59.87 44 0
    BACKUP PIECE .61 0 1 0
    IMAGE COPY 0 0 0 0
    FLASHBACK LOG 0 0 0 0
    FOREIGN ARCHIVED LOG 0 0 0 0
    AUXILIARY DATAFILE COPY 0 0 0 0

    8 rows selected.

    SQL> select applied,deleted, rectype, decode(rectype,11,'YES','NO') reclaimable, count(*),min(sequence#),max(sequence#)
    2 from v$archived_log left outer join sys.x$kccagf using(recid)
    3 where is_recovery_dest_file='YES' and name is not null
    4 group by applied,deleted,rectype, decode(rectype,11,'YES','NO')
    5 order by 6;

    APPLIED DEL RECTYPE REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    --------- --- ---------- --- ---------- -------------- --------------
    YES NO 11 YES 43 2120 2162
    NO NO NO 1 2163 2163

    SQL>

  • Franck Pachot
    Franck Pachot Sunday, 23 November 2014

    I still say that you don't have to delete archivelogs because they are managed by oracle. That's the reason for FRA and deletion policy.
    If you want to manage standby archived logs from the primary you have to use a rman catalog and play with the CHANGE ARCHIVELOG ... DB_UNIQUE_NAME:
    https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta009.htm#sthref530

  • Guest
    RIck CHEN Monday, 24 November 2014

    Thanks a lot Franck.
    I agree to use FRA and RMAN deletion policy to manage standby site archived logs automatically. On the other hand, "RMAN scripts management" is ready but will not been used in routine archived logs management.
    Really much appreciated.
    Regards,
    Rick

Leave your comment

Guest Sunday, 21 December 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter