8 Replies Latest reply: Jan 31, 2014 10:05 AM by jgarry RSS

Can I restrict space usage by "archive logs" on FRA?

user130038 Pro
Currently Being Moderated

Hi

 

Env: Oracle 11gR2 (10.2.0.3) EE on ASM, RHEL 6.2 64bit

 

Info from "V$RECOVERY_FILE_DEST":

NAMESPACE_LIMIT_TBSPACE_AVAILABLE_TBPERCENT_FULL
/ora_backup31.4551.5

 

log_archive_dest_1=location=+RECOVERY/

db_recovery_file_dest=/ora_backup

db_recovery_file_dest_size=3298534883328 (3TB)

 

ASM instance has two diskgroups (DATA and RECOVERY). DATA diskgroup size is 1.7TB and RECOVERY is 0.7 TB. RMAN Backups are being stored on disk and not in RECOVERY.

At the moment, there is one prod database using both these diskgroups. I have to move three more databases to this server which will use the same ASM diskgroups.

 

My question is: is there a way to allocate space-usage quota to various databases on the RECOVERY diskgroup? The reason is a concern from one DBA who says that if there is only one RECOVERY diskgroup being used by all prod databases to store "archivelog" files and if any databases causes the RECOVERY diskgroup to get full, all other databases will also be affected by this. So I thought to find out if I can allocate space quota to each database (just like "db_recovery_file_dest_size" parameter which restricts the space usage to 3TB regardless of what is actually available)

 

Please advise if there is a way to assign quotas? If not, should I create separate RECOVERY diskgroups for each database? Should I also create separate DATA diskgroups for each database?

 

What is the standard practice in the industry OR recommendation from Oracle about this?

 

I hope I have explained my situation and question clearly.

 

Best regards

  • 1. Re: Can I restrict space usage by "archive logs" on FRA?
    Aman.... Oracle ACE
    Currently Being Moderated

    AFAIK there is no such limitation that you can impose  on the space usage of a disk group. If you are worrying about the space being consumed by one database, you should limit it's FRA location and also set up your retention policy for the backups and deletion policy for the archive logs to be as restrictive as possible. For the DATA diskgroup, no, as long as the databases are not really really different from each other in terms of workload , there is no reason to make a separate disk group for them. The whole purpose of the disk group is provide consolidation and you shouldn't try to defeat that purpose of it.

     

    HTH

    Aman....

  • 2. Re: Can I restrict space usage by "archive logs" on FRA?
    user130038 Pro
    Currently Being Moderated

    Thanks Aman!

     

    There will be no backups stored in the RECOVERY diskgroups. Backups will go to a disk outside the ASM. The only thing that will go to the RECOVERY diskgroup is the archivelog files.

     

    So I guess to avoid one DB's archivelogs consuming all available space in RECOVERY diskgroup, having separate RECOVERY diskgroups for each DB is not a bad approach - right?

  • 3. Re: Can I restrict space usage by "archive logs" on FRA?
    jgarry Guru
    Currently Being Moderated

    Why not just avoid using the FRA?

  • 4. Re: Can I restrict space usage by "archive logs" on FRA?
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    Maybe I'm missing the point of what you are asking here but if your diskgroup is 3TB, why can't you just set db_recovery_file_dest_size to 1TB for each DB? if db1 fills it's 1TB it stops - it doesnt start pinching space from the other databases. You could extend DB1 temporarily and 'pinch' space from DB2 say but by default db_recovery_file_dest_size is the restriction. I don;t think you can then limit within that db_recovery_file_dest_size what % of space can be arch logs etc but you shouldn't need to?

     

    Cheers,

    Rich

  • 5. Re: Can I restrict space usage by "archive logs" on FRA?
    jgarry Guru
    Currently Being Moderated

    Maybe I don't understand it either, but I think he is saying his configuration is that backups are going to the area controlled by db_recovery_file_dest_size and archives are going elsewhere.  So he wants the elsewhere to have a similar limitation.

     

    So I'm saying it's better to either use FRA as it is designed, for everything, or else control everything yourself in the more classic manner.  The basic assumption of FRA is you have enough disk space to give some simple settings and forget it.  That does not appear to hold for the OP.

     

    I'm welcome to other interpretations.  I run multiple databases sharing an FRA, for the very pinch reason you give, but still have managed to make the over-allotment goof.  Once, anyways...

  • 6. Re: Can I restrict space usage by "archive logs" on FRA?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    OTOH, he could use db_recovery_file_dest set to '+RECOVERY' and db_recovery_file_dest_size  for the archive logs and use a FORMAT clause in the BACKUP DATABASE rman commands to have database backups going elsewhere.

     

     

    Hemant K Chitale


  • 7. Re: Can I restrict space usage by "archive logs" on FRA?
    Aman.... Oracle ACE
    Currently Being Moderated

    user130038 wrote:

     

    Thanks Aman!

     

    There will be no backups stored in the RECOVERY diskgroups. Backups will go to a disk outside the ASM. The only thing that will go to the RECOVERY diskgroup is the archivelog files.

     

    So I guess to avoid one DB's archivelogs consuming all available space in RECOVERY diskgroup, having separate RECOVERY diskgroups for each DB is not a bad approach - right?

    I shall echo the same question what Joel asked, what's the point of using the FRA DG anyways then if you are going to use it as a storage for just archivelogs ? Instead of creating three disk groups just for archived logs, why not limit the size of the FRA over the Recovery DG for each database? Why you are taking backups elsewhere on a disk only? If you really not want to use the DG, why not set up the tapes for the backups ?

     

    HTH

    Aman....

  • 8. Re: Can I restrict space usage by "archive logs" on FRA?
    jgarry Guru
    Currently Being Moderated

    Good point, but we don't know the sizing of db's, backups or archived logs (unless I missed something), and it just all seems less complicated to me to be explicit about everything. 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points