Oracle database won't start, ORA-03113

Question

Running Oracle 11.2.0.4.0 on CentOS and discovered the hard drive filled up, giving error in alert log:

ORA-19815: WARNING: db_recovery_file_dest_size of 64424509440 bytes is 99.98% used, and has 10141696 remaining bytes available.

The database was then issued a shutdown command and the alert log shows:

ARCH: Error 19809 Creating archive log file to /home.../archivelog/...

Errors in file /home/.../trace/..._ora_24158.trc:
ORA-16038: log 3 sequence# 11493 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: /home/.../redo03.log

USER (ospid: 24158): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=24158), summary=[abnormal instance termination]

System State dumped to trace file /home/.../trace/..._diag_24124_20210819133650.trc

Dumping diagnostic data in directory=[cdmp_20210819133650], requested by (instance=1, osid=24158), summary=[abnormal instance termination].

Instance terminated by USER, pid = 24158

Then I cleared up 1.7G of space on the hard drive and tried to start the database, which shows the following error:

SQL> startup;
ORACLE instance started.

Total System Global Area 4927172608 bytes
Fixed Size          2261648 bytes
Variable Size         989859184 bytes
Database Buffers     3925868544 bytes
Redo Buffers            9183232 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 24158
Session ID: 96 Serial number: 3

and the alert log shows:

Errors in file /home/...trace/..._ora_17003.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 64424509440 bytes is 99.98% used, and has 10141696 remaining bytes available

ARCH: Error 19809 Creating archive log file to /home.../archivelog/2021_08_20/o1_mf_1_11493_%u_.arc

Errors in file /home.../trace/..._ora_17003.trc:
ORA-16038: log 3 sequence# 11493 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: /home.../redo03.log

USER (ospid: 17003): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=17003), summary=[abnormal instance termination].

System State dumped to trace file /home/.../trace/..._diag_16963_20210820081510.trc

Dumping diagnostic data in directory=[cdmp_20210820081510], requested by (instance=1, osid=17003), summary=[abnormal instance termination].

Instance terminated by USER, pid = 17003

I'm not very experienced with Oracle. Can anyone recommend a solution? My goal is to bring up the database so it works fine, then copy everything to a larger hard drive (move from a 225G SSD to a 1000G HDD).

Answer 1

Even though you deleted some files, the size of the FRA is still limited to 60 GB and it is still full and the current online redo log can not be archived due to this.

Normally I would suggest removing unnecessary files from the FRA, but since you only want to start the database then stop and move it to somewhere else, just increase the size of FRA that is enough to store the next few archivelogs:

startup nomount
show parameter db_recovery_file_dest_size;
alter system set db_recovery_file_dest_size=61G;
show parameter db_recovery_file_dest_size;
alter database mount;
alter database open;

The database should open, after which you can stop it and move the files to where you wanted to.

Answer 2

You removed files from storage but not in such a way that oracle recognizes this. Your fra is full, make room by removing archived logfiles using rman. This assumes that you did backup them. If not, make a full backup after removing the archives.

rman target / delete archivelog all;

Answer 3

Manually deleting files from the directories that are under the FRA does not free up space in the FRA bookkeeping. You have to use the proper oracle utilities. Like using the rman 'delete' command to delete old backups. At this point you could do the following:

rman> crosscheck backup all;
rman> crosscheck archivelog all;
rman> delete expired backup;

The crosscheck command will compare the records of backup pieces, as recorded in the control file, against the files that are actually there. If a file (as listed in the control file repository) is found to not actually exist (because you manually deleted it), the record will be marked 'expired'. The 'delete expired' will then delete those records, and update the FRA accounting.