ORA-01113 - When Opening the Database

Hi,

I am updating post after posts today, just to make sure I update all the posts which I had to before we get into the new year

Few months back we were cloning a database, during which we had to re-create the database controlfile inorder to rename the database. After creating the controlfile when we tried to open the database with RESETLOGS it failed with below error

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

On checking the alert log we found that the UNDO tablespace that was used in our pfile was not of the same name as in the source database and hence alter database open resetlogs had failed

ORA-30012: undo tablespace 'APPS_UNDOTS1' does not exist or of wrong type

Tue Apr 03 10:01:24 PDT 2012

Error 30012 happened during db open, shutting down database

USER: terminating instance due to error 30012

Instance terminated by USER, pid = 16192

ORA-1092 signalled during: alter database open resetlogs

We then shutdown the database and made the changes to the pfile and mounted the database and tried to open the database using RESETLOGS

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

We got the above error because before the command failed the last time it had done the below (as per the alert log)

1. Reset the log sequence number
2. Recreated the new redo log files as per the new controlfile created

and probably had also completed 

1. Updating the datafiles and online redologs with the new SCN

Hence we issued the alter database open command without RESETLOGS option and it also errored with below error

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 1: '/oracle/oradata/T2C/fs01/system_01.dbf'

First we thought that database copy was wrong but the initial behaviour of the "alter database open restlogs" command and also the sanity checks we had done before issuing the command made us sure that there was no issue with the copy

As the database had abruptly shutdown during the "alter database open resetlogs" we thought that the datafile system_01.dbf was inconsistent but we werre unsure how many other files were inconsistent hence we decided to issue the alter database recover command. Below was the result of it

SQL> startup mount

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size 2110032 bytes

Variable Size 5771365808 bytes

Database Buffers 2734686208 bytes

Redo Buffers 81772544 bytes

Database mounted.

SQL> alter database recover ;

Database altered.

######## alert log excerpt for the alter database recover command ##########

alter database recover

Tue Apr 03 10:36:37 PDT 2012

Media Recovery Start

parallel recovery started with 3 processes

Tue Apr 03 10:36:57 PDT 2012

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0

Mem# 0: /oracle/oraredo/TS2COE/fs01/redo_1_01.log

Tue Apr 03 10:36:57 PDT 2012

Media Recovery Complete (TS2COE1)

Completed: alter database recover

##############################################################################

Once the recovery was completed we tried to open the database and were successful.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- ----------

T2C MOUNTED

SQL> alter database open;

Database altered.

-- Tanveer