Encountering ORA-16038, ORA-19809, and ORA-00312 errors while attempting to start the DB

book

Article ID: 160372

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

The Database becomes unresponsive and, when attempting to start up, throws errors not unlike the following:

ORA-16038: log one sequence 3144 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312 online log 1 thread 1 <path to redo log file>

 

There may be plenty of disk space available.

 

Another set of symptoms can be a HTTP 404 error when logging in to the Enforce UI and in the alert_protect.log following error is found:
db_recovery_file_dest_size is 100% full.

In the Windows event viewer getting the following event.

Event Id:20
Source: Oracle
Archive process error. ORACLE Instance protect - Can not allocate log. archival required.

When listner status is checked with the command "lsnrctl status" it states:


Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XYZ)(PORT=1521)))
The listener supports no services
The command completed successfully

When connecting to the database using the following command it states;

connect protect/protect as sysdba
ORA-12514 TNS:Listener does not currently know of service requested in connect descriptor.

Resolution

What is happening is the database has "archive logging" set to on.  The archive logs that are being generated have consumed all of the allocated space.  If there is still disk space available where the ORACLE_HOME is, then there is a strong likelihood that the actual log archive destination has not been set, and that Oracle is using the default destination, which is db_recovery_file_dest.  This particular directory location has a size restriction designated by db_recovery_file_dest_size.  It doesn't matter how much space is available; if the volume of the archive files exceeds the db_recovery_file_dest_size, the DB will halt.

If archiving has been turned on, then that needs to be in conjunction with an appropriate archive remediation solution.  Using archiving means that you want to create "hot backups".

 

The fix involves resizing the db_recovery_file_dest_size parameter, then either putting an appropriate backup solution in place or stopping archive logging and deleting every thing in db_recovery_file_dest.

The db_recovery_file_dest_resize will get the database working again, but is a temporary solution.

 

1) Increase the parameter db_recovery_file_dest_size
     SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4g SCOPE=BOTH;

2) Stop using the db_recovery_file_dest by unsetting the parameter.
    (This assumes you never really wanted to use this option)

3. Backup and delete archivelogs
    RMAN> backup archivelog all delete input;

It is the dba's responsibility to ensure that the FRA size is sufficient to account for their retention policy.

 

 

This may be caused by:

Using the default Oracle-Suggested strategy as implemented by EM in Oracle10gR1
Retention policy changed from the default redundancy of 1 to recovery window of  15 days

When the retention policy is changed to recovery window or to a redundancy > 1, RMAN cannot satisfy the retention policy if there is only one copy of database and it is recovered to the latest point in time.  In order to satisfy the retention policy, it keeps all the incrementals and archivelogs since database creation.

Symantec DLP Technical support does not support backup methods other than "cold backup".  A qualified dba is necessary to establish a hot backup solution, like RMAN, that will clean up archive logs after backing them up.