Tuesday, July 28, 2015

ORA-03113: & ORA-19809: & ORA-19804: &ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used,

It might be just because the lack of space in the recovery dest.

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;


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


Sat May 30 08:28:20 2015
ARC2 started with pid=22, OS id=38112
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Sat May 30 08:28:20 2015
ARC3 started with pid=23, OS id=39704
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_arc1_39816.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
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.
************************************************************************
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44892672 bytes disk space from 4039114752 limit
ARCH: Error 19809 Creating archive log file to 'E:\APP\MURTHY\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_05_30\O1_MF_1_98_%U_.ARC'
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-16038: log 2 sequence# 98 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'E:\APP\MURTHY\ORADATA\ORCL\REDO02.LOG'
USER (ospid: 38608): terminating the instance due to error 16038
************************************************************************
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.
************************************************************************
Instance terminated by USER, pid = 38608

To check the free space :

select name,
floor(space_limit/1024/1024) "Size_MB",
ceil(space_used/1024/1024) "Used_MB"
from v$recovery_file_dest
order by name;
/
  Solution : 

  open CMD>

1. RMAN
2. connect /
3. crosscheck archivelog all;
4. delete archivelog all completed before 'sysdate -7';
5. Prompt for Yes or No
6. Y and enter
7. exit

C:\Users\jnmurthy>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 30 08:44:13 2015

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

connected to target database: ORCL (DBID=1381995653)

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 OFF; # default
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; # default
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 NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\MURTHY\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default


before deleted the file location and size
SYS@orcl:SQL>show parameter recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size           big integer 30M


Above 3rd and 4th command check for the archieve log file and delete the old archieve log file which is older than the week from current date.
So Now we have free space in "flash_recovery_area" .
 After deleted the backup-ed archive files:

SYS@orcl:SQL>show parameter recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size           big integer 300M
After successfully deleted will get the space and then try to startup the db.

SYS@orcl:SQL>startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             713032144 bytes
Database Buffers          352321536 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.

more commands :

SQL> show parameter alert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE

SQL> show parameter BACKGROUND_DUMP_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      e:\app\****\diag\rdbms\za1\za1\trace

SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 3852M

No comments:

Post a Comment

Thanks for giving comments!!