Monday, May 07, 2012

ORA-00313, ORA-00312 and ORA-27037 after Recreating your Control File



You may wonder why I'm advertising the book above on this blog as it has nothing to do with Oracle. The reason is that it has a prize-winning story in it by a new author called Caroline Shiach. I could be wrong but I think she is the same Caroline Shiach that I went to school with!

This was tested in an Oracle 10 database running on Linux.

I often have to copy a source database to a target database for testing purposes. I usually do this by closing the source database and copying its datafiles then recreating the control file. This method has been around for at least 15 years and probably longer. I documented it in detail for a colleague in 2001 and, the next time I find these instructions, I will put them on this blog.

For now, I just wanted to show a small problem you might have if you try this yourself. Imagine I have just copied the datafiles from a source database to a target database. Here they are (for simplicity, they are all in the same directory):

[oracle@localhost DB3]$ pwd
/home/oracle/DB3
[oracle@localhost DB3]$ ls -1
sysaux01.dbf
system01.dbf
undotbs01.dbf
users01.dbf
[oracle@localhost DB3]$

Unfortunately I forgot to copy the online redo log files. Then I recreated the control file:

SQL> CREATE CONTROLFILE SET DATABASE "DB3" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/home/oracle/DB3/redo01.log'  SIZE 50M,
  9    GROUP 2 '/home/oracle/DB3/redo02.log'  SIZE 50M,
 10    GROUP 3 '/home/oracle/DB3/redo03.log'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/home/oracle/DB3/system01.dbf',
 14    '/home/oracle/DB3/undotbs01.dbf',
 15    '/home/oracle/DB3/sysaux01.dbf',
 16    '/home/oracle/DB3/users01.dbf'
 17  CHARACTER SET WE8ISO8859P1
 18  ;

Control file created.

SQL>

So then the directory had a control file too:

[oracle@localhost DB3]$ pwd
/home/oracle/DB3
[oracle@localhost DB3]$ ls -1
control01.ctl
sysaux01.dbf
system01.dbf
undotbs01.dbf
users01.dbf
[oracle@localhost DB3]$

I opened the database:

SQL> alter database open resetlogs;

Database altered.

SQL>

Messages like these then appeared in the alert log for each missing redo log member:

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/DB3/redo01.log'
ORA-27037: unable to obtain file status

You can ignore these messages. Oracle automatically creates missing redo log members when a database is first opened after recreating the control file. These new redo log members can be seen in the directory listing below.

[oracle@localhost DB3]$ pwd
/home/oracle/DB3
[oracle@localhost DB3]$ ls -1
control01.ctl
redo01.log
redo02.log
redo03.log
sysaux01.dbf
system01.dbf
undotbs01.dbf
users01.dbf
[oracle@localhost DB3]$

No comments: