ORA-16014 and ORA-00312, ora-16014ora-00312

ORA-16014 and ORA-00312, ora-16014ora-00312

Scenario:

SQL> alter system archive log current;
Alter system archive log current
*
Row 3 has an error:
ORA-16014: log 2 sequence #34 Unarchived, no available destination
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'

Environment is a personal lab environment.

Troubleshooting:

When the log is switched, the log cannot be archived, and then perform the following troubleshooting:

1. Check the log format and confirm that the log format log_archive_format is correct.

2. Check that the log archiving directory is normal, including the location, permission, and available space.

3. View error logs

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

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

We can see that memory overflow exists.

Analysis:

After troubleshooting, it can be determined that the log itself has a problem. The official explanation is as follows:

The ORA-01624: log string needed for crash recovery of instance string (thread string ).

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

Action: If the database is not open, then open it. crash recovery will advance the checkpoint. if the database is open force a global checkpoint. if the log is too upted so that the database cannot be opened, it may be necessary to do incomplete retil until cancel at this log.

It generally means that the checkpoint process is always processing the file and cannot be deleted or cleared. It is similar to freezing and can be restored through recover. Through the error log, we speculate that this problem may be caused by memory overflow.

View memory:

[Root @ rac1 ~] # Free-m
Total used free shared buffers cached
Mem: 1878 1736 142 0 6 683
-/+ Buffers/cache: 1045 832
Swap: 3999 354 3645

The idle memory is insufficient, and swap partitions are switched. The memory is insufficient.

Solution:

The database has an rman backup and starts to the mount status for recover.

SQL> shutdown immediate

SQL> startup mount;

 

SQL> recover

ORA-00283: resume session canceled due to an error

ORA-00264: recovery not required

SQL> alter database open;

The database has been changed.

SQL> alter system archive log current;

The system has been changed.

Review:

An error is reported during recover, but the database can be archived after startup and can be restored without loss. So what happened in this process?

Recover reads log files, undo files, and other files to perform database transaction rollback and rollback operations. In the open Database, recover also processes database file consistency.

View alert to see the following process

Thread 2 advanced to log sequence 33 (before internal thread enable)
Errors in file/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_18338.trc:
ORA-00313: Unable to open a 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 a member of log Group 4 (for thread 2)
Archived Log entry 56 added for thread 2 sequence 32 ID 0xb9a2cd8c dest 1:
Thread 2 advanced to log sequence 34 (after internal thread enable)
Wed Jul 22 18:28:47 2015
ARC2 started with pid = 35, OS id = 18513
Wed Jul 22 18:28:47 2015
Thread 1 opened at log sequence 39
Current log #1 seq #39 mem #0: + DATA_DG/orcl/onlinelog/group_1.257.885126161
Current log #1 seq #39 mem #1:/orabak/clog/orcl01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jul 22 18:28:47 2015
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
ARC1: Archival started

We can see that Oracle has recovered the cache and processed the dead process.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.