Mar 31, 2013

ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used,



SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             385877416 bytes
Database Buffers          385875968 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4152
Session ID: 5 Serial number: 3

Check the alert log for further investination.
==============================================
contents from alert log
=======================
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file c:\app\rpilla\diag\rdbms\mntest1\mntest1\trace\mntest1_arc2_4956.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2866688 bytes disk space from 4039114752 limit

ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 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.
************************************************************************
I used the 4th suggestion by deleting archive log files from db_recovery_file_dest.

login

rman target /

list backup;
list copy;
delete archivelog all;

2 comments:

  1. HI RAVI,,

    IF THE DB IS DOWN HOW THIS RMAN TARGET WILL GET INTO IT,, FOR THE SAME ISSUE FOLLOWED YOUR 4TH SUGGESTION BUT RMAN CLEARLY SAYS YOUR DB IS DOWN, SO THERE IS NO USE..

    MY WORK AROUND IS

    RECOVER DATABASE UNTIL CANCEL;
    ALTER DATABASE OPEN RESETLOGS;

    THEN I FOLLOWED YOUR SUGGESTIONS..

    KINDLY EXPLAIN ME HOW WE CAN ABLE TO LOG INTO RMAN WITHOUT DB UP AND RUNNING,, AND ANOTHER QUESTION IS IF A LESS IMPORTANT DB HOW TO LEAVE IT WITHOUT MONITORING ITS ARCHIVE LOGS, IS THERE ANY LIMIT OR ANY KIND OF DELETE PROGRAM IS AVAILABLE,, PLEASE SUGGEST ME

    ReplyDelete
  2. Hi Hameed,

    Thanks for going through my blog and sorry to reply late.
    Regarding your 1st question, if the db is down, as in my case above, i started but it ended up in mount state, you can mount it from rman as below

    C:\>rman target /

    Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 14 12:12:49 2015

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    connected to target database (not started)

    RMAN> startup mount;

    Oracle instance started
    database mounted

    Total System Global Area 1670221824 bytes

    Fixed Size 2281816 bytes
    Variable Size 1040191144 bytes
    Database Buffers 620756992 bytes
    Redo Buffers 6991872 bytes

    RMAN>

    Regarding your 2nd question, if you are taking rman backup then you can configure rman to delete the archive logs after it is backed up for example "CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;"

    RMAN> show all;

    using target database control file instead of recovery catalog
    RMAN configuration parameters for database with db_unique_name ORCL are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\RPILLA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

    ReplyDelete