Wednesday, August 13, 2014

ORA-01113 when Tablespace is in Backup Mode

On a 9.2.0.4 database, while logging in to the database, it was throwing error "ORA-01033: ORACLE initialization or shutdown in progress". This error means that database is either no-mount or mount state. So I logged in as SYSDBA and tried to open the database.

/////////////////////////////////////////////////////////////////////////////////////////
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: 'D:\ORACLE\ORADATA\PRODDB\EX_DATA01.DBF'
/////////////////////////////////////////////////////////////////////////////////////////

Apparently it was showing that datafile might have been corrupted and needs media recovery. But when I checked the alert log file, it was showing a message as follows which confirmed that datafile is actually not corrupted, but tablespace is BACKUP MODE.

/////////////////////////////////////////////////////////////////////////////////////////
ALTER TABLESPACE "PROD_DATA" END BACKUP
Sun Aug 10 03:24:04 2014
Completed: ALTER TABLESPACE "UAT_DATA" END BACKUP
Sun Aug 10 03:24:04 2014
ALTER TABLESPACE "EX_DATA" BEGIN BACKUP
Cc
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 512
  timed_statistics         = TRUE
/////////////////////////////////////////////////////////////////////////////////////////

Here I realized that that there was an instance crash while tablespace EX_DATA was in backup mode. Querying v$backup returned me following information

/////////////////////////////////////////////////////////////////////////////////////////
SQL> select * from v$backup where file#=10;

     FILE#   STATUS                CHANGE#   TIME
         10     ACTIVE                6016417       10-AUG-14

/////////////////////////////////////////////////////////////////////////////////////////

Solution
Taking tablespace EX_DATA out of back up mode resolved the issue.

/////////////////////////////////////////////////////////////////////////////////////////
SQL> alter tablespace ex_data end backup;

Tablespace altered.

SQL> alter database open;

Database altered.
/////////////////////////////////////////////////////////////////////////////////////////

I tried to reproduce same problem on 11.2.0.1 and I saw that in 11g, the error message for same type of scenario clearly gives hint of checking the datafile for being in BACKUP MODE.

ORA-10873: file 4 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 4: 'D:\APP\SQURESHI\ORADATA\SALMAN112\USERS01.DBF'

I am not sure if 10g returns the same error message, but at least starting from 11g there is a clear indication of possibility of the datafile being under backup mode rather than simply giving impression that datafile is corrupted.


No comments: