Strange error on starting one of our databases today - why oracle can't just say the recovery file destination is 100% full I have no idea but anyway :
[oracle@ora01 ~]$ srvctl start database -db oradb
PRCR-1079 : Failed to start resource ora.cng.db
CRS-5017: The resource action "ora.oradb.db start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 129203
Session ID: 259 Serial number: 59108
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/product/12.2.0/diag/crs/ora01/crs/trace/ohasd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.oradb.db' on 'ora01' failed
From the alert log, trace files hit 100% on the recovery file dest size - more info from Mr Burleson.
http://www.dba-oracle.com/p_db_recovery_file_dest_size.htm
As this was RAC one node used srvctl to mount the database.
[oracle@ora01 ~]$ srvctl start database -db oradb -o mount
[oracle@ora01 ~]$ . oraenv
ORACLE_SID = [] ? oradb
The Oracle base has been changed from /u01/app/oracle/product/12.2.0 to /u01/app/oracle
[
oracle@ora01 ~]$ sqlplus / as sysdba
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 8192M
SQL> alter system set db_recovery_file_dest_size = 16192M;
System altered.
SQL> alter database open;
Database altered.
Script from the Burleson website :
SQL>
col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
NAME SIZE_MB USED_MB PCT_USED
-------------------------------- ------------ ------------ --------
+DATA 16,192 7,729 48
Just need to ensure the archive logs are cleared down so this does not happen again.
https://www.durham.computer/2019/04/a-very-quick-script-to-clear-down-rman.html
**NB**
Unless the "Archive Destination" is set to use the db_recovery_file_dest the archiver will use +DATA until it it is exhausted and won't max out at 12G; however the recovery area will still be used by the control files etc (see below) and can still reach 100%
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 9750
Next log sequence to archive 9751
Current log sequence 9752
SQL> show parameter db_reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 12G
SQL> l
1* SELECT * FROM V$RECOVERY_AREA_USAGEFILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
------ ------------ ------------------------- --------------- ----------
CONTROL FILE .13 0 1 0
REDO LOG 4.91 0 3 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE .09 0 1 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL>/ (script above)
NAME SIZE_MB USED_MB PCT_USED
-------------------------------- ------------ ------------ --------
+DATA 12,288 632 6
