This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 27, 2013 9:27 AM by mseberg RSS

ORA-19804 and recovery catalog

864988 Newbie
Currently Being Moderated
Hello,

My database version is 11.2.0.3.0 , Oracle Linux 6. We use ASM to store FRA.

Because, We had some backup issues 3 days ago, we needed to uncatalog the database and catalog it again to the recovery catalog.
SInce then backups arfe running fine until we start getting the following error;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03015: error occurred in stored script daily_backup

RMAN-03009: failure of backup command on d4_disk_backup channel at 01/25/2013 19:38:40
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 37702598656 bytes disk space from 5497558138880 limit
RMAN-03009: failure of backup command on d4_disk_backup channel at 01/25/2013 19:38:15
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 37959499776 bytes disk space from 5497558138880 limit
I know I have the following options to fix the issue;
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.
But, since we did catalogued this DB 3 days ago, Recovery catalogue doesn 't know anything about previous backups taken so delete expired didn't helped much. Also, the flash recovery area is getting full and we cannot add disk space to ASM. How can I fix the problem?
  • 1. Re: ORA-19804 and recovery catalog
    phaeus Pro
    Currently Being Moderated
    Hello,
    it check your db_recovery_file_dest_size parameter? If you overallocate your FRA you have to free up some diskspace which means delete something you did not need anymore, add disks, or if your diskspace is enough increase maybe your parameter.

    regards
    Peter
  • 2. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    the recovery catalog doesn't know anything about previous backups ...

    Edited by: DBA on Jan 26, 2013 5:09 AM
  • 3. Re: ORA-19804 and recovery catalog
    phaeus Pro
    Currently Being Moderated
    Hello,
    you should delete backup files you did not need anymore with rman so your controlfile or your catalog is up2date.

    http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta016.htm

    regards
    Peter
  • 4. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    Hello,
    Thanks for trying to help me. But, as I said before the recovery catalog doesn't know anything about previous backups so there are not expired nor obsolete from RMAN point of view ...
  • 5. Re: ORA-19804 and recovery catalog
    UweHesse Expert
    Currently Being Moderated
    Did I understand your problem right:
    You have old backups in the recovery area, but no more information about them in the recovery catalog.
    Therefore DELETE commands won't help.
    These old backups consume too much space in the recovery area now.

    You can register all files inside the recovery area with the RMAN command CATALOG RECOVERY AREA
    Then DELETE what you want to.

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com
  • 6. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    Mr, Uwe,
    I have another 3 databases in the same host using the same recovery area destination.

    All 4 databases in the same host have this value;
    SQL> show parameter db_rec
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      +FLASH_DG
    I want to double check with you if I run "catalog recovery area" command from one database, this command doesn't catalog also the files that belong to the other 3 databases?
    Cheers
  • 7. Re: ORA-19804 and recovery catalog
    mseberg Guru
    Currently Being Moderated
    Hello;

    It should not. On my system for example the FRA is

    /u0a/app/oracle/flash_recovery_area/

    But each database has its own folder under "/u01/app/oracle/flash_recovery_area/"

    So if I run "catalog recovery area" it will only do the database I'm connected with.

    If you have to catalog multiple files in a directory you can use "catalog start with"

    example :

    RMAN> catalog start with '/u01/app/oracle/backuparea' noprompt;

    Best Regards

    mseberg
  • 8. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    Hello,

    Good your help. Can I instead delete the FRA directory files from ASM? OR it must be deleted from RMAN-?
    I want to delete those as RMAN doesn't know anything about them and delete expired or obsolete doesn't delete them ..

    *2013_01_14/*
    *2013_01_15/*
    *2013_01_16/*
    *2013_01_17/*
    *2013_01_18/*

    ASMCMD> cd +FLASH_DG/DB1/BACKUPSET
    ASMCMD> ls
    2013_01_14/
    2013_01_15/
    2013_01_16/
    2013_01_17/
    2013_01_18/
    2013_01_19/
    2013_01_20/
    2013_01_21/
    2013_01_22/
    2013_01_23/
    2013_01_24/
    2013_01_25/
    2013_01_26/
    ASMCMD>
  • 9. Re: ORA-19804 and recovery catalog
    mseberg Guru
    Currently Being Moderated
    Hello again;

    If RMAN doesn't know anything about them then it OK to delete them.

    On my system empty folders ( Oracle 11 ) stay around several days in the FRA before Oracle removes them.

    For Oracle 1o refer to this note :

    Flash Recovery Area Housekeeping of empty directories [ID 305970.1]

    Best Regards

    mseberg
  • 10. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    Good.
    is it correct to delete them from ASMCMD like this ?

    *rm -rf +FLASH_DG/DB1/BACKUPSET/2013_01_14/*
    *rm -rf +FLASH_DG/DB1/BACKUPSET/2013_01_15/*
    *rm -rf +FLASH_DG/DB1/BACKUPSET/2013_01_16/*
    *rm -rf +FLASH_DG/DB1/BACKUPSET/2013_01_17/*
    *rm -rf +FLASH_DG/DB1/BACKUPSET/2013_01_18/*

    or it must be done through sqlplus?
  • 11. Re: ORA-19804 and recovery catalog
    mseberg Guru
    Currently Being Moderated
    Hello again;

    asmcmd should be correct.

    And this should also work :

    rm +FLASH_DG/DB1/BACKUPSET/2013_01_14/
    ...
    ...

    Best Regards

    mseberg
  • 12. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    THANK YOU.
    What could be also the correct systax using sqlplus in ASM to delete those files?
  • 13. Re: ORA-19804 and recovery catalog
    mseberg Guru
    Currently Being Moderated
    OK;

    SQL Plus Example :

    ALTER DISKGROUP <DISK_GROUP_NAME>
    DROP FILE '+FLASH_DG/DB1/BACKUPSET/2013_01_14/';

    Beware, if you use SQL Plus but it will not update database ( if that is needed ).

    Best Regards

    mseberg
  • 14. Re: ORA-19804 and recovery catalog
    864988 Newbie
    Currently Being Moderated
    mseberg wrote:
    OK;

    SQL Plus Example :

    ALTER DISKGROUP <DISK_GROUP_NAME>
    DROP FILE '+FLASH_DG/DB1/BACKUPSET/2013_01_14/';

    Beware, if you use SQL Plus but it will not update database ( if that is needed ).

    Best Regards

    mseberg
    Thank you. I am sorry not understand you; What it means it no update database if I use sqlplus?
1 2 Previous Next

Legend

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