ORA-16014 and ORA-00312

Scene:

Sql> alter system archive log current;
Alter system archive Log current
*
An error occurred on line 1th:
ORA-16014: Log 2 sequence# 34 not archived, no destinations available
ORA-00312: Online log 2 thread 1: ' +data_dg/orcl/onlinelog/group_2.258.885126161 '
ORA-00312: Online log 2 thread 1: '/orabak/clog/orcl02.log '

The environment is an individual experimental environment.

Investigation:

When you cut the log, you find that the log cannot be archived, and then perform the following troubleshooting

1. Check the log format to determine that the log format Log_archive_format correct

2. Check the Log archive directory is correct, including location, permissions, free space, OK

3. Viewing the error log

Cat/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_18338.trc

ORA-00313: Unable to open member of Log Group 4 (for thread 2)
Dde:problem Key ' ORA 313 ' was flood controlled (0x1) (no incident)
ORA-00313: Unable to open member of Log Group 4 (for thread 2)
ORA-00313: Unable to open member of Log Group 4 (for thread 2)
Initial buffer sizes:read 1024K, overflow 832K, change 805K
Dde:problem Key ' ORA 313 ' was flood controlled (0x1) (no incident)
ORA-00313: Unable to open member of Log Group 4 (for thread 2)
ORA-00313: Unable to open member of Log Group 4 (for thread 2)

You can see a certain overflow in memory

Analysis:

After the investigation can be determined that the log itself has a problem, the more official explanation is as follows

< Span style= "BACKGROUND-COLOR:INHERIT; Font-family: Song body; Font-size:9pt ">    &NBSP; ora-01624:log&NBSP; string &NBSP; needed for Crash recovery of Instance&NBSP; string &NBSP; (Thread&NBSP; string ).

Cause:a log cannot be dropped or cleared until the thread ' s checkpoint have advanced out of the log.

Action:if the database is not open and then open it. Crash recovery would advance the checkpoint. If The database is open force a global checkpoint. If The log is corrupted so and the database cannot be opened, it could be necessary to do incomplete recovery until cancel At the This log.

It probably means that the checkpoint process has been working on the file and cannot be deleted or purged of its contents, similar to being stuck, and can be recovered by recover. With the error log, we can speculate that the problem is likely due to a memory overflow.

To view memory:

< Span style= "BACKGROUND-COLOR:INHERIT; Font-family: Song body; Font-size:9pt ">[[email protected" ~]# free-m
             total       used       free     shared &N Bsp  buffers     Cached
Mem:          1878       1736        142          0          6       &NBSP;683
-/+ Buffers/cache:       1045        832
Swap:         3999 &NB Sp      354       3645

There is not enough free memory, and the swap partition has been swapped, and it does not have enough memory.

Solution:

Database has Rman backup, boot to mount state for recover.

Sql>shutdown Immediate

Sql>startup Mount;

Sql>recover

ORA-00283: Recovery session canceled due to an error

ORA-00264: No Recovery required

Sql>alter database open;

The database has changed.

Sql>alter System archive Log current;

The system has changed.

Review:

Recover when the error, but the library can be archived after startup, and is non-destructive recovery, no loss of data. So what happened in this process?

Recover will read the log files, undo and other files, the database transaction rollback roll forward, and in the Open database when the database file is also processed.

View alert to see the following procedure

thread 2 advanced to log sequence (before internal Thread enable)
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL1/TRACE/ORCL1_ORA_18338.TRC:
ORA-00313: Unable to open member of Log Group 4 (for thread 2)
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL1/TRACE/ORCL1_ORA_18338.TRC:
ORA-00313: Unable to open member of Log Group 4 (for thread 2)
archived Log entry added for thread 2 sequence ID 0xb9a2cd8c dest 1:
Thread 2 Advanced-to-log sequence (after internal Thread enable)
Wed Jul 18:28:47
ARC2 started with pid=35, OS id=18513
Wed Jul 18:28:47
Thread 1 opened at log sequence
Current log# 1 seq# mem# 0: +data_dg/orcl/onlinelog/group_1.257.885126161
Current log# 1 seq# mem# 1:/orabak/clog/orcl01.log
successful open of Redo thread 1
MTTR advisory is disabled because Fast_start_mttr_target are not set
Wed Jul 18:28:47
smon:enabling Cache Recovery
Instance recovery:looking for dead threads
Instance recovery:lock domain Invalid but no dead threads
Arc1:archival started

You can see that Oracle has cached the recovery and processed the dead process.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

ORA-16014 and ORA-00312