Recovery

[carl airiel]

I had a problem on recovering a database. I had a full backup of my db about 3 weeks ago. Two weeks ago our bcv process failed.So in short, We don't have a full weekly backup.All we have is 1 full backup(3 weeks) and a lot of archive logs.(logs are updated up to Feb 12, 2006 of 9:00 PM. The db crash happened last feb 13, 2006 of 5:00 AM. Ther crash happened due to abnormal system shutdown. When we tried to up the cluster the db was successfully running. But on the alert logs in stated some ora 1113. When I tried to connect using my 3rd party software for sql. It states that "SHutdown initialization in progress. How can I fix these problems?What type of recovery I will use?Please help me. Thanks

[Paul Sperry]

From Metalink


Subject: Common Causes and Solutions on ORA-1113 Error Found in Backup & Recovery
Doc ID: Note:183367.1 Type: TROUBLESHOOTING
Last Revision Date: 20-APR-2004 Status: PUBLISHED


PURPOSE
-------

To consolidate the common reasons & solutions for the ORA-1113 error.


SCOPE & APPLICATION
--------------------

Customers facing ORA-1113 and analysts requiring information on known issues
with ORA-1113 errors.

ORA-1113
========
An ORA-1113 occurs when a datafile needs recovery.

Error Explanation:
------------------

01113, 00000, "file %s needs media recovery"

Cause: An attempt was made to online or open a database with a file that
is in need of media recovery.
Action: First apply media recovery to the file.

This error is usually followed with ORA-1110 error which will indicate the
name of the datafile that needs media recovery.

Eg:

ORA-01113: file 28 needs media recovery
ORA-01110: data file 28: '/h04/usupport/app/oracle/oradata/v817/nar.dbf'


This error message indicates that a datafile that is not up-to-date with
respect to the controlfile and other datafiles.

Oracle's architecture is tightly coupled in the sense that all database
files i.e., datafiles, redolog files, and controlfiles -- must be in sync
when the database is opened or at the end of a checkpoint.

This implies that the checkpoint SCN (System Commit Number) of all datafiles
must be the same. If that is not the case for a particular datafile, an
ORA-1113 error will be generated.

For example, when you put a tablespace in hot backup mode, the checkpoint
SCN of all its datafiles is frozen at the current value until you issue the
corresponding end backup. If the database crashes during a hot backup and
you try to restart it without doing recovery, you will likely get ORA-1113
for at least one of the datafiles in the tablespace that was being backed up,
since its SCN will probably be lower than that of the controlfile and the
datafiles in other tablespaces.

Likewise, offlining a datafile causes its checkpoint SCN to freeze.
If you simply attempt to online the file without recovering it first, its
SCN will likely be much older than that of the online datafiles, and thus an
ORA-1113 will result.


**********************************************
Before Starting these actions do the following:
**********************************************

Note :

If you are using Oracle9i, use SQL*Plus, instead of Server Manager to
execute the mentioned commands, since Server Manager is not available
in Oracle9i.

Query the V$LOG and V$LOGFILE.

1. If the database is down, you need to mount it first.

SVRMGR> STARTUP MOUNT PFILE=;

2. Then connect internal Server Manager and issue the query:

SVRMGR> CONNECT INTERNAL; or
SQL> connect / as sysdba (for Oracle9i)

SVRMGR> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective sequence and
first change numbers.

The steps to take next depend on the scenario in which the ORA-1113 was issued.
This is discussed in the following sections.


POSSIBLE CAUSES AND SOLUTIONS SUMMARY:
=====================================

I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY


I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
**********************************************************

A. WITH ORACLE 7.1 OR LOWER

1. Mount the database.(If the database is NOT already mounted)

SVRMGR> STARTUP MOUNT PFILE=;

2. Apply media recovery to the database.

SVRMGR> RECOVER DATABASE;

3. Confirm each of the archived logs that you are prompted for until you
receive the message "Media recovery complete".

If you are prompted for an archived log that does not exist, Oracle probably
needs one or more of the online logs to proceed with the recovery. Compare
the sequence number referenced in the ORA-280 message with the sequence
numbers of your online logs. Then enter the full path name of one of the
members of the redo group whose sequence number matches the one you are being asked for.

4. Open the database.

SVRMGR> ALTER DATABASE OPEN;


B. WITH ORACLE 7.2 OR HIGHER

1. Mount the database.

SVRMGR> 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:

SVRMGR> 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:

SVRMGR> ALTER DATABASE DATAFILE '' END BACKUP;

4. Open the database.

SVRMGR> ALTER DATABASE OPEN;


II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
*********************************************************************

A. WITH THE DATABASE IN ARCHIVELOG MODE

1. Mount the database.

SVRMGR> STARTUP MOUNT;

2. Recover the datafile:

SVRMGR> RECOVER DATAFILE '';

If recovering more than one datafile in a tablepace issue a

SVRMGR> RECOVER TABLESPACE;

If recovering more than one tablespace issue a

SVRMGR> RECOVER DATABASE;


3. Confirm each of the archived logs that you are prompted for until you
receive the message "Media recovery complete".

If you are prompted for an archived log that does not exist, Oracle probably
needs one or more of the online logs to proceed with the recovery. Compare
the sequence number referenced in the ORA-280 message with the sequence
numbers of your online logs. Then enter the full path name of one of the
members of the redo group whose sequence number matches the one you are
being asked for.

4. Open the database.

SVRMGR> ALTER DATABASE OPEN;


B. WITH THE DATABASE IN NOARCHIVELOG MODE

In this case, you will only succeed in recovering the datafile or tablespace
if the redo to be applied to it is within the range of your online logs.
Issue the query:

SVRMGR> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

Compare the change number you obtain with the FIRST_CHANGE# of your online logs.

If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs,
the datafile can be recovered. In this case, the procedure to be followed
is analogous to that of scenario II.A above, except that you must always
enter the appropriate online log when prompted, until recovery is finished.

If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file
cannot be recovered.Your options at this point include:

- If the datafile is in a temporary or index tablespace, you may drop
it with an ALTER DATABASE DATAFILE '' OFFLINE DROP
statement and then open the database. Once the database is up, you
must drop the tablespace to which the datafile belongs and recreate it.

- If the datafile is in the SYSTEM or in a rollback tablespace, restore
an up-to-date copy of the datafile (if available) or your most recent
full backup.In case you do not have either of this, then it might not
be possible to recover the database fully. For more details or to
assist you in your decision, please contact Oracle Customer Support.

For all other cases in this scenario, you must weigh the cost of going to a
backup versus the cost of recreating the tablespace involved, as described in
the two previous cases.For more details or to assist you in your decision,
please contact Oracle Customer Support.


III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
**********************************************

1. Recover the datafile:

SVRMGRL> RECOVER DATAFILE '';

If recovering a tablespace, do

SVRMGRL> RECOVER TABLESPACE ;

If recovering a database, do

SVRMGRL> RECOVER DATABASE;

2. Confirm each of the archived logs that you are prompted for until you
receive the message "Media recovery complete".

If you are prompted for an archived log that does not exist, Oracle probably
needs one or more of the online logs to proceed with the recovery. Compare
the sequence number referenced in the ORA-280 message with the sequence
numbers of your online logs. Then enter the full path name of one of the
members of the redo group whose sequence number matches the one you are
being asked for.

3. Open the database.

SVRMGR> ALTER DATABASE OPEN;




IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
*******************************************************************************

If the database is recovered with the
"RECOVER DATABASE USING BACKUP CONTROLFILE;" option without specifying the
"UNTIL CANCEL" option, then upon "ALTER DATABASE OPEN RESETLOGS;" you will
encounter the ORA-1113 error.

Steps to workaround this issue:

1. Recover database again using:

SVRMGR> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

2. Cancel recovery by issuing the "CANCEL" command.

3. Open the database using:

SVRMGR> ALTER DATABASE OPEN RESETLOGS;


Notes that explain other possible recovery scenarios involving ORA-1113:
------------------------------------------------------------------------

Note 116374.1 -- "ORA-1113 on Datafile After Moving Datafile Using USFDMP
Utility"
ORA-1113 on Datafile After Moving Datafile Using USFDMP

Note 1079626.6 -- "VMS: Mount Phase of Database Startup Results in ORA-01113,
ORA-01186 & ORA-01122"
ORA-1113 due to datafile getting locked on OpenVMS

Note 116422.1 -- "ORA-01113 ON RBS DATAFILE DURING STARTUP OF DATABASE"
Recovery from corrupt rollback segments

Note 1020262.102 -- "ORA-01113, ORA-01110: TRYING TO STARTUP DATABASE AFTER
INCOMPLETE RECOVERY" Another scenario of ORA-1113

Note 168115.1 -- "ORA-01113 ORA-01110 on Database Startup after Write Disk failure"
Datafile header contains different SCN comparing to other
database files due to a write disk failure

Note 146039.1 -- "Database Startup Fails with ORA-01113, ORA-01110"
Dropping the datafile while ORA-1113, if the datafile is
not required