Database Crash with Tablespace in Hot Backup

Monday, June 6, 2011

I encountered this problem twice before a few years ago and most recently last week.  Adding this to my notes when it happens again a few years from now.

Snippet from alert log showing database crashed.

Fri May 27 19:07:27 Eastern Daylight Time 2011
Errors in file i:\oradba\admin\pd102\bdump\pd102_pmon_1464.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [kksCursorFreeCallBack+112] [PC:0x71E70C] [ADDR:0x68] [UNABLE_TO_READ] []

Fri May 27 19:07:45 Eastern Daylight Time 2011
LGWR: terminating instance due to error 472
Fri May 27 19:07:59 Eastern Daylight Time 2011
Instance terminated by LGWR, pid = 1508

Login to restart the database.

> sqlplus "sys as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 27 20:52:14 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter password:
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size                  2077040 bytes
Variable Size             201330320 bytes
Database Buffers          939524096 bytes
Redo Buffers               14696448 bytes
Database mounted.

ORA-01113: file 8 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 8: 'I:\ORADATA\PD102\TMF43WT.DBF'

I reviewed the alert log and was able to determine the data file specified was in backup mode when the database terminated.  Following the instructions in Oracle Support Doc ID 183367.1, I executed the commands below to resolve the problem.

SQL> alter database datafile 'I:\ORADATA\PD102\TMF43WT.DBF' end backup;

Database altered.

SQL> alter database open;

Database altered.

References
Common Causes and Solutions on ORA-1113 Error Found in Backup & Recovery (Doc ID 183367.1)

AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP *******************************************************
1. Mount the database.

   SQL> STARTUP MOUNT;

2. Find out which datafiles were in hot backup mode when the database crashed or was shutdown abort or the machine was rebooted by running the query:

   SQL> SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE#;

3. For each of the files returned by the above query, issue the command:

   SQL> ALTER DATABASE DATAFILE '<full path name>' END BACKUP;

4. Open the database.

   SQL> ALTER DATABASE OPEN;