Saturday, May 17, 2014

ORA-01511, ORA-01512 and ORA-27037

This was run on an Oracle 11 database. I used dbca to create a database recently and, by mistake, I put redo03a.log in the wrong directory (ent_red01 with a zero instead of ent_redo1 with an o). dbca had no way of knowing this was wrong and simply created the directory name I specified in the GUI. You can see what I mean in the output below:

In the database: 

  1  select group#, member from v$logfile
  2* order by 1,2
SQL> /
 
    GROUP# MEMBER
---------- ----------------------------------------
         1 /database/ENTARCH/ent_redo1/redo01a.log
         1 /database/ENTARCH/ent_redo2/redo01b.log
         2 /database/ENTARCH/ent_redo1/redo02a.log
         2 /database/ENTARCH/ent_redo2/redo02b.log
         3 /database/ENTARCH/ent_red01/redo03a.log
         3 /database/ENTARCH/ent_redo2/redo03b.log
 
6 rows selected.
 
SQL>
 
... and in UNIX:
 
ENTARCH /database/ENTARCH > ls -R ent_red*
ent_red01:
redo03a.log
 
ent_redo1:
redo01a.log  redo02a.log
 
ent_redo2:
redo01b.log  redo02b.log  redo03b.log
ENTARCH /database/ENTARCH >
 
I closed and mounted the database:
 
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             335549144 bytes
Database Buffers          176160768 bytes
Redo Buffers                8232960 bytes
Database mounted.
SQL>
 
Then I tried to rename the logfile:
 
SQL> l
  1  alter database rename file
  2  '/database/ENTARCH/ent_red01/redo03a.log' to
  3* '/database/ENTARCH/ent_redo1/redo03a.log'
SQL> /
alter database rename file
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01512: error renaming log file
/database/ENTARCH/ent_red01/redo03a.log - new file
/database/ENTARCH/ent_redo1/redo03a.log not found
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
 
SQL>
 
This failed because you need to move or copy the logfile at the OS level first. As I had only just created this database I decided it was OK to use mv:
 
ENTARCH /database/ENTARCH/ent_red01 > ls
redo03a.log
ENTARCH /database/ENTARCH/ent_red01 > mv * ../ent_redo1
ENTARCH /database/ENTARCH/ent_red01 >
 
Then I was able to rename the file and open the database again:
 
SQL> l
  1  alter database rename file
  2  '/database/ENTARCH/ent_red01/redo03a.log' to
  3* '/database/ENTARCH/ent_redo1/redo03a.log'
SQL> /
 
Database altered.
 
SQL> alter database open
  2  /
 
Database altered.
 
SQL>
 
Finally, I checked that the redo log member looked OK in the database:
 
SQL> l
  1  select group#, member from v$logfile
  2* order by 1,2
SQL> /
 
    GROUP# MEMBER
---------- ----------------------------------------
         1 /database/ENTARCH/ent_redo1/redo01a.log
         1 /database/ENTARCH/ent_redo2/redo01b.log
         2 /database/ENTARCH/ent_redo1/redo02a.log
         2 /database/ENTARCH/ent_redo2/redo02b.log
         3 /database/ENTARCH/ent_redo1/redo03a.log
         3 /database/ENTARCH/ent_redo2/redo03b.log
 
6 rows selected.
 
SQL>
 
... and removed the empty directory:
 
ENTARCH /database/ENTARCH > rmdir ent_red01
ENTARCH /database/ENTARCH >

No comments: