Here is a simple basic troubleshooting that happened today.
I was starting the database to encounter the error below.
I was starting the database to encounter the error below.
SQL> startup ORACLE instance started. Total System Global Area 1071333376 bytes Fixed Size 1375792 bytes Variable Size 662700496 bytes Database Buffers 402653184 bytes Redo Buffers 4603904 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 5636 Session ID: 5 Serial number: 3Checking the alert log for what has just happened and why is this throwing an error, I can find the following in the alert log file.
... ... Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.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:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 41948672 bytes disk space from 4039114752 limit ARCH: Error 19809 Creating archive log file to 'E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_02_04\O1_MF_1_229_%U_.ARC' Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc: ORA-16038: log 1 sequence# 229 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 1 thread 1: 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG' USER (ospid: 5636): terminating the instance due to error 16038 ARC1: Archival started Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc0_6168.trc: ORA-01092: ORACLE instance terminated. Disconnection forced ARC2: Archival started Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc0_6168.trc: ORA-01092: ORACLE instance terminated. Disconnection forced Thu Feb 04 10:20:47 2016 ARC3 started with pid=23, OS id=6272 Instance terminated by USER, pid = 5636See the highlighted last line which states the process ID responsible for the instance termination. The flash recovery area for my test database is set at 3 GB and now it is full.
Solution:
Clear some of the files by backing up or deletion from the flash recovery area and retry to startup the database.
I now have cleared the space after taking a backup of required archive logs.
Note: Deleting files manually from OS level will not be considered as freeing up space for the recovery area as they are managed by RMAN and Oracle. In this case again the startup will fail. You need to login to RMAN, crosscheck all the recovery area files and delete expired archive log and backup files to let Oracle know that we now have free space in the flash recovery area.
After performing these above, we can start up the database.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1375792 bytes
Variable Size 662700496 bytes
Database Buffers 402653184 bytes
Redo Buffers 4603904 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\Priya>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 4 10:45:20 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1421168703, not open)
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
validation failed for archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC RECID=83 STAMP=899754777
validation failed for archived log
...
...
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_227_CBV8YSFP_.ARC RECID=177 STAMP=902576057
validation failed for archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_228_CBV903F8_.ARC RECID=178 STAMP=902576095
Crosschecked 96 objects
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
83 1 133 X 29-DEC-15
Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC
84 1 134 X 29-DEC-15
Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_134_C855T36B_.ARC
...
...
...
178 1 228 X 31-JAN-16
Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_228_CBV903F8_.ARC
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC RECID=83 STAMP=899754777
deleted archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_134_C855T36B_.ARC RECID=84 STAMP=899754787
...
...
...
RMAN> exit
Recovery Manager complete.
C:\Users\Priya>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 10:46:15 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open;
Database altered.
SQL>
Now my database is open again.Happy troubleshooting!
