Database Crash with Tablespace in Hot Backup

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;

Advertisements

4 Responses to Database Crash with Tablespace in Hot Backup

  1. File recovery will be a challenging concern needing significant focus as well as careful approach. The greater details you will find the more it’s almost guaranteed that either in error or even because of some specialized problem you will notice these details vanishing out of the blue. What you ought to do immediately after that’s to implement a few computer software qualified to come back all the problems to the prior state. The actual achievement of these procedure will in reality rely on a number of factors however, you should expect 90-95% restoration when a good software can be used no more functions had been done using the pc.

  2. Chris Grabowy says:

    Perhaps…

    ALTER DATABASE END BACKUP;

    …is an option.

  3. It is actually ideal the perfect time to generate options to the long term and it’s time for it to feel very special. I have master this article and if I could just I must help you a few exciting difficulties as well as ideas. You could could possibly generate future content with this document. My spouse and i would like to understand far more concerns about this!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: