ORA-00313: open failed for members of log group 1 of thread 1

This article is a follow-up to the previous article:

• Simplify Cloning by Using Hosts File^[1]

As pointed out in that article, there are limitations and issues with cloning (either an application or a database). This article describes one of the issues.

ORA-00313

After following the cloning steps as described in [1], we have run into this Oracle database exception when trying to bring up our cloned Oracle.  Obviously, this is our fault because we have not done a thorough planning before the cloning.

What this exception tells us is:

• The online log cannot be opened.

What Is the Redo Log?

The most crucial structure for Oracle recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

Where Did We Find This ORA-00313?

From the initialization parameter file (i.e., dbs/init<sid>.ora ) , we have traced down the location of diagnostic destination^[3]:

• diagnostic_dest=/slot/fiz7865/log

From there, we went down to a folder named:

• <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

In there, there is a file named:

• alert_fiz7865.log

From that file, we have found the following entries:

  Lost write protection disabled
  Completed: ALTER DATABASE   MOUNT
  Wed Nov 14 09:42:55 2012
  ALTER DATABASE OPEN
  Errors in file <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace/fiz7865_lgwr_25410.trc:

  ORA-00313: open failed for members of log group 1 of thread 1
  ORA-00312: online log 1 thread 1: '/data1/rup3.redolog/log3.dbf'

Note that Oracle will write the alert_<instname>.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter^[4]. So, you can also find out its location by:

SQL> show parameter BACKGROUND_DUMP_DEST

NAME                   TYPE        VALUE
---------------------- ----------- ------------------------------
background_dump_dest   string     /slot/fiz7865/log/diag/rdbms/fiz7865/fiz7865/trace

What Happened?

When we do the cloning, not everything is contained in a single source directory.  For example, redo log files have been reallocated to another file system (i.e., /data1) which is outside the source directory.  For the Oracle to be fully functional, original redo logs need to be reopened.  If they are not found, an ORA-00313 will be thrown.

How to Find the Redo Log Location

Before you do the cloning, keep the source database up and running.  Then query the logfile location by:

• select * from V$LOGFILE;

References

1. Simplify Cloning by Using Hosts File
2. Managing the Redo Log
3. DIAGNOSTIC_DEST
4. Alert Log
5. Migrating Oracle B2B from Test to Production (T2P) (Chap 10 of the Book "Getting Started with Oracle SOA B2B Integration: A Hands-On Tutorial")
• This section provides a real-world scenario to replicate (clone) the test environment to production for Oracle SOA.
• Oracle Fusion Middleware provides a series of scripts for this task.
6. Oracle Products: What Patching, Migration, and Upgrade Mean?