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 >

How to Move System Tablespace Datafiles

In an earlier post I explained how to move datafiles. The method I used then is NOT suitable for datafiles belonging to the system, undo or temporary tablespaces as they cannot be taken offline. I said I would publish a post about this in due course and here it is.

I created a database using dbca recently. I was in a hurry and forgot to put a slash at the end of the directory name where I wanted to put the datafiles so they ended up like this:

SQL> l
  1  select file_id, file_name
  2  from dba_data_files
  3* order by 1
SQL> /

   FILE_ID FILE_NAME
---------- ------------------------------------------
         1 /agasprd/qcsprod/qcs_systemsystem01.dbf
         2 /agasprd/qcsprod/qcs_systemsysaux01.dbf
         3 /agasprd/qcsprod/qcs_systemundotbs01.dbf
         4 /agasprd/qcsprod/qcs_systemusers01.dbf
         5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

Files 1 to 4 were in the wrong place. File 5, which was created afterwards, is OK. I closed the database and backed it up. Then I renamed the files in UNIX:

UNIX > pwd
/agasprd/qcsprod
UNIX > mv qcs_systemsystem01.dbf qcs_system/system01.dbf
UNIX > mv qcs_systemsysaux01.dbf qcs_system/sysaux01.dbf
UNIX > mv qcs_systemundotbs01.dbf qcs_system/undotbs01.dbf
UNIX > mv qcs_systemusers01.dbf qcs_system/users01.dbf
UNIX >

I mounted the database:

UNIX > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:12:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             390075096 bytes
Database Buffers          121634816 bytes
Redo Buffers                8232960 bytes
Database mounted.

SQL>

... and renamed the files in the database 1 by 1:

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsystem01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/system01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsysaux01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/sysaux01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemundotbs01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/undotbs01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemusers01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/users01.dbf'
SQL> /

Database altered.

SQL>

Finally, I opened the database:

SQL> alter database open;

Database altered.

SQL> select file_id, file_name
  2  from dba_data_files
  3  order by 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------
        1 /agasprd/qcsprod/qcs_system/system01.dbf
        2 /agasprd/qcsprod/qcs_system/sysaux01.dbf
        3 /agasprd/qcsprod/qcs_system/undotbs01.dbf
        4 /agasprd/qcsprod/qcs_system/users01.dbf
        5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

The tempfile was in the wrong place too but I dealt with that by recreating the temporary tablespace.