0

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).

1

3 Answers 3

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.

4
  • How do you know the FRA is limited to 60G? I don't think I ever setup flash-back recovery.
    – user46688
    Commented Aug 20, 2021 at 16:02
  • @user46688 No, I recommended slightly increasing the FRA just so the database can be started (what you asked), after which you can stop it cleanly, then move it to the hard drive you mentioned, where you can start it again and sort out why the FRA got full, which is another topic. My guess: your database is in archivelog mode, but the filesystem was full, so not even the backup scripts could run properly, and there was no backup running which would normally delete the archivelogs. Commented Aug 20, 2021 at 16:07
  • That solved it! Curious, how to identify unnecessary files from FRA that I can delete?
    – user46688
    Commented Aug 20, 2021 at 16:46
  • ask another question, and add a link to this one if that helps.
    – Hannah Vernon
    Commented Aug 20, 2021 at 18:02
1

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;

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.