'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 14 - Performing Database Recovery

Using Oracle8

Chapter 14

Performing Database Recovery

Recovery Strategies

Analyzing the recovery process involves determining the factors that influence the recovery process. Database size, system complexity, database structure, and application structure are the main factors that influence the mean time to recover (MTTR).

The MTTR can be very critical to the operation of systems that need high availability. You can reduce the MTTR in several ways:

Table 14.1 describes the techniques that can be used for high availability. These strategies should be used to quickly recover in the event of a database failure.

Table 14.1  High availability strategies
Technique
Usage
Advantages
Disadvantages
Object-level recovery using Export, Import, and SQL*Loader Uses Export/ Import to protect dataFast object- level recovery Difficult to scale; you must be aware of object associations
Failover systems using hardware redundancy Failover provided by using another nodeNo data loss due to redundant system No scalability; costly
Oracle standby databasesPrimary database's redo log keeps another database updated, which can be used during recovery Fast recovery; failover; disaster recovery possible Data loss possible; complicated setup and maintenance; potential of replicating data-base corruption
Oracle Symmetric ReplicationUses Oracle's replication feature to provide high availability No data loss; failover; disaster recovery possible; both databases can be used simultaneously Slow recovery due to use of transactions; use of two-phase commit can lead to additional problems while maintaining the database's consistency
Oracle Parallel ServerClustering solution that allows failover to another instance; recovery can proceed simultaneously and is done by the surviving instances No data loss; fast failover; protects against node and cache failures; high scalability; load balancing Tuning can be difficult; application design plays significant part in strategy's success
Triple mirroringUses a third hardware that is a mirror Fast hot backups; fast recoveryCost of triple writes and resilvering
EMC SRDF facilityPhysical I/O- based replication No data loss; failover; disaster recovery possible; faster than Oracle Symmetric Replication Potential of replicating database corruption
Customized store-and- forward replication Makes use of Oracle8 features such as advanced queuing or trigger-based asynchronous replication No data loss; fast recoveryComplex; serializing of transactions

General steps to recover a database system

  1. Detect the failure. The detection of an outage is usually simple: Either the database isn't responding to the application, or the system has displayed explicit error messages. However, a problem such as a corrupt control file may not be detected while the database is running.
  2. Analyze the failure. You should analyze the type and extent of the failure; the recovery procedure will depend on this analysis. This task can take a significant amount of time in large systems.
  3. Determine the components of the database that need recovery. This task can also be significant in large systems. You need to determine which components (such as a table) are lost, and then determine whether you need to recover the tablespace or a data file.
  4. Determine the dependencies between components to be recovered. Usually the components aren't isolated; loss or recovery of a database object can affect other objects. For example, if a table needs recovery, you'll also have to recreate the indexes. This step isn't done automatically by the recovery of the table.
  5. Determine the location of the backup. The closer the backup is to where the recovery is to be performed, the lesser is the MTTR.

Location factors
If the backup is on a disk, is the disk on-site or off-site? Is the disk local or network? Do you have mirrored copies? Are you recovering from a cold or a hot backup? If the backup is on tape, is the tape on-site or off-site? Do you need additional components to access the tape?

  1. Perform the restore. This involves restoring the physical file from disk or tape and placing it at a location where the database can access the file for recovery purposes. The time to restore is affected by file location, file size, file format (raw, export, blocks, or extracts), and possibilities of restore parallelism.
  2. Replay redo logs (for archived databases) and resync the database components.

Analyzing the Failure and Determining Recovery Options

You need to answer several questions to determine the type and extent of failure. Your answers dictate the steps you take to recover the system:

Are there any errors in the operating system log?

Database failures can be detected if you get one of various errors. The following list shows some of the common errors encountered and their solutions:

Recovering from the Loss of General Data Files

The procedure to recover from the loss of general data files depends on the type of tablespace from which the data file is lost-rollback tablespace, user tablespace, index tablespace, or read-only tablespace. Several symptoms can indicate this problem. You might get the following errors:

These errors can indicate a loss of a general data file. It's up to you to determine the type of tablespace involved.

Recovering from a Lost Data File in the User Tablespace

If you determine that the tablespace contains user data, the steps you follow to recover the tablespace depend on whether you have a good backup and whether that backup was cold or hot.

Recovering with a Cold Backup

In this case you're in the NOARCHIVELOG mode. The data file recovery will be complete if the redo to be applied is within the range of your online redo logs.

Recover with a cold backup

  1. Shut down the database.
  2. Restore the lost data file from the backup.
  3. Start the database.
  4. Execute the following query to determine all your online redo log files and their respective sequence and first change numbers: 
SELECT X.GROUP#,  MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG X, V$LOGILE Y
WHERE X.GROUP# = Y.GROUP#;

  1. Determine the CHANGE# of the file to be recovered: 
SELECT FILE#, CHANGE#
FROM V$RECOVER_FILE;
    The data file can be recovered if the CHANGE# obtained is greater than the minimum FIRST_CHANGE# of your online redo logs.

  1. Recover the data file by using the online redo logs: 
RECOVER DATAFILE 'fullpath of the datafile'
    Confirm each log that you're prompted for during the recovery until you receive the message Media Recovery complete.

  1. Open the database: 
ALTER DATABASE OPEN

Recovering with a Hot Backup

In this case you're in the ARCHIVELOG mode. The data file recovery will be complete if the redo to be applied is within the range of your online logs.

Recover with a hot backup

  1. Shut down the database.
  2. Restore the lost data file from the backup.
  3. Start the database.
  4. Execute the following query to determine all your online redo log files and their respective sequence and first change numbers: 
SELECT X.GROUP#,  MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG X, V$LOGILE Y
WHERE X.GROUP# = Y.GROUP#;
    The error that reported the failure should indicate the CHANGE# of the file to recover. If this CHANGE# is less than the minimum FIRST_CHANGE# of your online redo logs, the file can't be completely recovered and you have two choices:

  1. Recover the data file by using the archived and the online redo logs: 
RECOVER DATAFILE 'fullpath of the datafile'
    Confirm each log that you're prompted for during the recovery until you receive the message Media Recovery complete.

Use the online redo logs during recovery
If while performing step 5 you're prompted for a non-existing archived log, you need to use the online redo logs to continue with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online redo logs. Supply the full path name of one of the members of the redo log group whose sequence number matches it.

  1. Open the database: 
ALTER DATABASE OPEN

Recovering with Missing Redo Data

If redo data is missing, the recovery won't be complete as described in the preceding steps and you'll have to recreate the tablespace. To recreate the tablespace, you can either use a good export script that can easily load the data and recreate the objects in that tablespace or load the data through SQL*Loader.

Recover with missing redo data

  1. Shut down the database.
  2. Mount the database: 
Svrmgrl> Startup mount

  1. Offline drop the data file: 
Svrmgrl> ALTER DATABASE DATAFILE 'fullpath of datafile'
                   OFFLINE DROP;

  1. Open the database: 
Svrmgrl> ALTER DATABASE OPEN;

  1. Drop the user tablespace: 
Svrmgrl> DROP TABLESPACE tablespace_name
         INCLUDING CONTENTS;

  1. Recreate the tablespace and the tablespace objects.

Recovering from a Lost Data File in a Read-Only Tablespace

Because read-only tablespaces are never modified, the recovery solution is very simple: Restore the data file from its last backup to its original location. No media recovery is required in this case. There are two exceptions to this procedure, however:

In either scenario, recovery can be performed by following the same steps as described earlier in the section "Recovering from a Lost Data File in a User Tablespace," based on the backup type you have.

Recovering from a Lost Data File in an Index Tablespace

If you determine that the tablespace contains user indexes, you should use the following steps, depending on whether you have a good backup and whether it's a cold or hot backup.

Recovering with a Cold Backup

In this case you're in NOARCHIVELOG mode. The data file recovery will be complete if the redo to be applied is within the range of your online redo logs. To recover from this situation, follow the same steps as described earlier in the "Recovering with a Cold Backup" section of "Recovering from a Lost Data File in a User Tablespace."

Recovering with a Hot Backup

In this case you're in ARCHIVELOG mode. The data file recovery will be complete if the redo to be applied is within the range of your online logs. To recover from this situation, follow the same steps as described earlier in the "Recovering with a Hot Backup" section of "Recovering from a Lost Data File in a User Tablespace."

Recovering with Missing Redo Data

If redo data is missing, the recovery won't be complete. You need to recreate the tablespace. To do so, you can either use a script that can be used to easily create the index or manually create the indexes by issuing CREATE INDEX statements.

Recover with missing redo data

  1. Shut down the database.
  2. Mount the database: 
Svrmgrl> Startup mount

  1. Offline drop the data file: 
Svrmgrl> ALTER DATABASE DATAFILE 'fullpath of datafile'
                   OFFLINE DROP;

  1. Open the database: 
Svrmgrl> ALTER DATABASE OPEN;

  1. Drop the user tablespace: 
Svrmgrl> DROP TABLESPACE tablespace_name
                   INCLUDING CONTENTS;

  1. Recreate the tablespace and all the previously existing indexes in the tablespace.

Recovering from a Lost Data File in a Rollback Tablespace

This recovery scenario is the most critical one, and you should really work with Oracle Support while performing such a recovery. The main issue involved is that you must make sure the active transactions in the rollback segment aren't lost.

In this case, the procedure to follow depends on when the loss was detected.

Database is Down

You're trying to start the database and get ORA-1157, ORA-1110 and operating system errors, as well as determine that the tablespace contains rollback segments. One thing you have to determine is how the database was shut down.

Database Was Cleanly Shut Down

You're certain that the database was shut down via shutdown normal or shutdown immediate. Check the alert log and look at the last shutdown entry. The following log entry indicates that the shutdown was clean: 

'alter database dismount
completed: alter database dismount"

This may be followed by an attempt you made to start, resulting in the ORA errors and a subsequent SHUTDOWN ABORT by Oracle.

Recover a database that has been shut down cleanly

  1. In the INITSID.ORA file, change the ROLLBACK_SEGMENTS parameter by removing all the rollback segments in the tablespace to which the lost data file belongs. If you aren't sure of the rollback segments you need to remove, insert a # at the beginning of the line to comment out the entire ROLLBACK_SEGMENTS entry.
  2. Mount the database in restricted mode: 
Svrmgrl> STARTUP RESTRICT MOUNT

  1. Offline drop the lost data file: 
Svrmgrl> ALTER DATABASE DATAFILE 'fullpath of datafile' FFLINE DROP;

  1. Open the database: 
Svrmgrl> ALTER DATABASE OPEN
    If at this point you receive a message that the statement has been processed, skip to step 7; otherwise, if you get error codes ORA-604, ORA-376, and ORA-1110, continue to step 5.

  1. This step should be performed only if the database didn't open in step 5. Shut down the database and edit the INITSID.ORA file as follows: 
_Corrupted_rollback_segments
  = (rollback1,rollback2,...,rollbackN)
    Now start the database in restricted mode: 
Svrmgrl> startup restrict mount

  1. Drop the rollback tablespace that contained the lost data file: 
Svrmgrl> drop tablespace tablespace_name including contents;

  1. Recreate the rollback tablespace with all its rollback segments and be sure to bring them online.
  2. Make the database available for general use: 
Svrmgrl> alter system disable restricted session;

  1. If you had to edit the INITSID.ORA file in step 6, shut down the database and edit the file again as follows: 
_Corrupted_rollback_segments
  = (rollback1,rollback2,...,rollbackN)

  1. Start the database.

Database Wasn't Cleanly Shut Down

In this scenario, the database was shut down, aborted, or crashed. You can't offline or drop the lost data file because it's almost certain that the rollback segments with extents in the lost data file contain active transactions; you must restore the lost data file from backup and apply media recovery. If the database is in NOARCHIVELOG mode, a complete recovery is possible only if the redo to be applied is in the range of your online redo log files.

Recover a database that wasn't shut down cleanly

  1. Restore the lost data file from a backup.
  2. Mount the database.
  3. Identify whether the file is offline: 
Svrmgrl> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
  1. If the file is offline, bring it online: 
Svrmgrl> ALTER DATABASE DATAFILE 'full path of datafile'
                   ONLINE;

  1. Execute the following query to determine all your online redo log files and their respective sequence and first change numbers: 
SELECT X.GROUP#,  MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG X, V$LOGILE Y
WHERE X.GROUP# = Y.GROUP#;

  1. The file can't be recovered if the CHANGE# is less than the minimum FIRST_CHANGE# of your online redo logs. You now have two options:

Open the database in an inconsistent state and rebuild it

Be careful here!
These steps should be used with extreme caution after taking a full database export-there is a potential for database corruption.

  1. Shut down the database.
  2. Take a full database backup.
  3. Make the following changes in your INITSID.ORA file: 
       _allow_resetlogs_corruption = true
       _corrupted_rollback_segments
           = list of all rollback segments

  1. Do a startup mount.
  2. Perform an incomplete recovery of the database: 
Svrmgrl> RECOVER DATABASE UNTIL CANCEL;

  1. When prompted for the file, typeCANCEL.
  2. Reset the logs and open the database: 
Svrmgrl> ALTER DATABASE OPEN RESETLOGS;

  1. Rebuild the database by taking a full database export and then importing it to a new database.

Rebuilding the database
Rebuilding the database is an essential step in this procedure because forcefully opening the database can corrupt the data-base.

    However, if the CHANGE# is greater than the minimum FIRST_CHANGE# of your redo logs, recover the data file by using the online redo logs: 
RECOVER DATAFILE 'fullpath of the datafile'
    Confirm each log that you're prompted for during the recovery until you receive the message Media Recovery complete.

  1. Open the database: 
ALTER DATABASE OPEN

Database Is Up and Running

Don't shut down the database if you've detected a loss of data file in the rollback tablespace while the database is up and running. It's simpler to resolve the situation with the database up than it is with the database down. You can use two approaches to recover.

Create new rollback segments
You may have to create additional rollback segments in a different tablespace to continue working with the database while the current problem is being addressed.

Recover a live database in ARCHIVELOG mode

  1. Offline the lost data file: 
ALTER DATABASE DATAFILE 'fullpath of datafile' OFFLINE;

  1. Restore the data file from a backup.
  2. Apply media recovery on the data file: 
RECOVER DATAFILE 'fullpath of datafile';

  1. Bring the data file back online: 
ALTER DATABASE DATAFILE 'fullpath of datafile' ONLINE;

Recover a live database, no matter what mode (slower method)

  1. Offline all the rollback segments in the tablespace to which the data file belongs: 
ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;
    Repeat this statement for all rollback segments in the affected tablespace.

  1. Execute the following query to make sure that the rollback segments are offline before they're dropped: 
SELECT SEGMENT_NAME, STATUS
FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'tablespace_name';

  1. Drop all offline rollback segments by running the following command for each segment: 
DROP ROLLBACK SEGMENT rollback_segment;

  1. If there are rollback segments that you tried to offline, but step 2 shows that they're still online, it means that they have active transactions in them. Run the following query to determine the active transactions: 
SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'I' AND
SEGMENT_ID = USN;
    If this query returns no rows, all the rollback segments are offline. If this query returns one or more rows with a status of PENDING OFFLINE, check the ACTIVE_TX column for these rollback segments. Segments with a value of 0 will soon go off-line; a non-zero value, however, indicates that you have active transactions that need to be committed or rolled back.

Dealing with Active Transactions

Execute the following query to identify users who have transactions assigned to the rollback segments: 

SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('pending_rollback1','pending_rollback2',
   ... 'pending_rollbackN') AND
    S.TADDR = T.ADDR AND
    T.XIDUSN = R.USN;

After you determine which users have active transactions in the "pending offline" rollback segments, you can either ask them to commit or roll back their transaction or you can kill their session by executing the following: 

ALTER SYSTEM KILL SESSION 'sid,
serial#';

The following steps can be performed after you have taken care of the active transactions.

Clean up after active transactions

  1. Drop the tablespace including contents.
  2. Recreate the rollback tablespace.
  3. Recreate the rollback segments and bring them online.

Recovering from Loss of System Tablespace

A good backup strategy can be a lifesaver in a situation where a data file from the system tablespace is lost or damaged. In the absence of a good backup, you may be faced with the undesirable alternative of rebuilding the database with possible data loss.

The symptoms of the scenario in which the system tablespace needs recovery are as follows:

You should use the following methods depending on whether you have a good backup and whether that backup is cold or hot.

Recovering with a Cold Backup

In this case you're in NOARCHIVELOG mode. The data file recovery will be complete if the redo to be applied is within the range of your online logs.

Recover with a cold backup

  1. Shut down the database.
  2. Restore the lost data file from the backup.
  3. Start the database.
  4. Execute the following query to determine all your online redo log files and their respective sequence and first change numbers: 
SELECT X.GROUP#,  MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG X, V$LOGILE Y
WHERE X.GROUP# = Y.GROUP#;

  1. Determine the CHANGE# of the file to be recovered: 
SELECT FILE#, CHANGE#
FROM V$RECOVER_FILE;
    If the CHANGE# is greater than the minimum FIRST_CHANGE# of your online redo logs, the data file can be recovered by applying the online redo logs. If the CHANGE# obtained is less than the minimum FIRST_CHANGE# of your online redo logs, the file can't be completely recovered, and you have two choices:

  1. If the CHANGE# is greater than your online redo logs' minimum FIRST_CHANGE#, you can recover the data file by using the online redo logs: 
RECOVER DATAFILE 'fullpath of the datafile'
    Confirm each log that you're prompted for during the recovery until you receive the message Media Recovery complete.

  1. Open the database: 
ALTER DATABASE OPEN

Recovering with a Hot Backup

In this case you are in ARCHIVELOG mode. The data file recovery will be complete if the redo to be applied is within the range of your online redo logs.

Recover with a hot backup

  1. Shut down the database.
  2. Restore the lost data file from the backup.
  3. Startup mount the database.
  4. Execute the following query to determine all your online redo log files and their respective sequence and first change numbers: 
SELECT X.GROUP#,  MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG X, V$LOGILE Y
WHERE X.GROUP# = Y.GROUP#;

  1. Recover the data file by using the archived and the online redo logs: 
RECOVER DATAFILE 'fullpath of the datafile'
    Confirm each log that you're prompted for during the recovery until you receive the message Media Recovery complete.

Use online redo logs for recovery
If you're prompted for a non-existing archived log, you'll need to use the online redo logs to continue with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online redo logs. Supply the full path name of one of the members of the redo log group whose sequence number matches.

  1. Open the database: 
ALTER DATABASE OPEN

Recovering with Missing Redo Data

The recovery won't be complete if redo data is missing. Your option isn't attractive-you'll have to rebuild the database. For this purpose, you can either use the Export/Import utility to take a full database export, or you can do a user/table-level export. SQL*Loader can also be used for this purpose.

Recovering from the Loss of a Control File

The control file is very important for the database. Usually a problem with the control file isn't detected while the database is up and running. If the control file is lost or damaged in such a way that Oracle can't recognize it, a subsequent database startup will result in ORA-205 (error in identifying control file '%s'), along with an operating system-level error.

The recovery procedure for this situation depends on whether you have lost one of the control files from a mirrored configuration or all copies of the current control file. The following methods should be used to recover the database based on the particular situation.

Recovering with a Mirrored Control File

In this scenario, you can use the other copies of the control file for getting the database up and running.

Recover with a mirrored control file

  1. Shut down the instance if it's still running.
  2. Find the cause of the loss of control file. Is it due to a hardware problem (disk or controller)?
  3. If the hardware isn't a problem, make a good copy of the control file to the location of the lost control file and skip to step 6.
  4. If the hardware is the problem, make a good copy of the control file to a reliable location.
  5. Edit INITSID.ORA or CONFIGSID.ORA to update the CONTROL_FILES parameter to reflect the control file's new location.
  6. Start the database.

Recovering Without a Mirrored Control File

In this case, the recovery steps are a bit more involved. First you have to analyze the situation and determine the answers to the following questions:

Recover without a mirrored control file

  1. Shut down the instance (if it's not already shut down).
  2. Startup mount the database.
  3. Get a backup trace of the control file: 
Svrmgrl> alter database backup controlfile to trace;
    This command will create a trace file in USER_DUMP_DEST. (To find the value of USER_DUMP_DEST, use the command show parameter USER_DUMP_DEST; at the Svrmgrl> prompt.) Listing 14.1 shows an example of such a trace file.

  1. Modify the trace file by removing the header information (lines 1-21). Also make any changes desired (such as MAXLOGFILES on line 30 and MAXDATAFILES on line 32) and save the file as create_control.sql.
  2. Do a shutdown normal for the database.
  3. Take a full backup of the database to protect from any further problems.
  4. Execute the following to create a new control file: 
Svrmgrl> @create_control.sql

  1. Open the database by executing the following at the server manager prompt: 
svrmgrl> Alter database open;

  1. Shut down the database.
  2. Take a full database backup.

Listing 14.1  Example control file creation script 

     01:     Dump file E:\ORANT\rdbms80\trace\ORA00167.TRC
     02:     Tue Mar 31 17:06:56 1998
     03:     ORACLE V8.0.3.0.0 - Production vsnsta=0
     04:     vsnsql=c vsnxtr=3
     05:     Windows NT V4.0, OS V5.101, CPU type 586
     06:     Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
     07:     With the Partitioning and Objects options
     08:     PL/SQL Release 8.0.3.0.0 - Production
     09:     Windows NT V4.0, OS V5.101, CPU type 586
     10:     Instance name: sjr
     11:     
     12:     Redo thread mounted by this instance: 1
     13:     
     14:     Oracle process number: 8
     15:     
     16:     pid: a7
     17:     
     18:     Tue Mar 31 17:06:56 1998
     19:     Tue Mar 31 17:06:56 1998
     20:     
     21:     *** SESSION ID:(7.1) 1998.03.31.17.06.56.062
     22:     # The following commands will create a new control file
     23:     # and use it to open the database.
     24:     # Data used by the recovery manager will be lost.
     25:     # Additional logs may be required for media recovery of
     26:     # offline data files. Use this only if the current
     27:     # version of all online logs are available.
     28:     STARTUP NOMOUNT
     29:     CREATE CONTROLFILE REUSE DATABASE "SJR" NORESETLOGS
               NOARCHIVELOG
     30:        MAXLOGFILES 32
     31:        MAXLOGMEMBERS 2
     32:        MAXDATAFILES 254
     33:        MAXINSTANCES 1
     34:        MAXLOGHISTORY 899
     35:        LOGFILE
     36:      GROUP 1 'E:\ORANT\DATABASE\LOGSJR1.ORA'  SIZE 200K,
     37:      GROUP 2 'E:\ORANT\DATABASE\LOGSJR2.ORA'  SIZE 200K
     38:     DATAFILE
     39:      'E:\ORANT\DATABASE\SYS1SJR.ORA',
     40:      'E:\ORANT\DATABASE\RBS1SJR.ORA',
     41:      'E:\ORANT\DATABASE\USR1SJR.ORA',
     42:      'E:\ORANT\DATABASE\TMP1SJR.ORA',
     43:      'E:\ORANT\DATABASE\INDX1SJR.ORA'
     44:     ;
     45:     
     46:     # Recovery is required if any of the datafiles are
     47:     # restored backups, or if the last shutdown was not
     48:     # normal or immediate.
     49:     RECOVER DATABASE
     50:     
     51:     # Database can now be opened normally.
     52:     ALTER DATABASE OPEN;

Recover without an accurate trace file

  1. Shut down the database.
  2. Take a full database backup, including all the data files and redo log files.
  3. Use Server Manager and do a STARTUP NOMOUNT of the database.
  4. Issue a CREATE CONTROLFILE statement such as the following:

Full syntax available
For the CREATE CONTROLFILE statement's complete syntax, see Oracle's SQL reference manual. 

Create Controlfile reuse database "TEST"
   noresetlogs noarchivelog
      Maxlogfiles  50
      Maxlogmembers  3
      Maxdatafiles  500
      Maxinstances   8
      Maxloghistory  500
    Logfile
    Group 1 '/u01/oracle/8.0.4/dbs/log1test.dbf' size 1M,
    Group 2 '/u01/oracle/8.0.4/dbs/log2test.dbf' size 1M,
    Group 3 '/u01/oracle/8.0.4/dbs/log3test.dbf' size 1M,
    Datafile
       '/u01/oracle/8.0.4/dbs/systest.dbf' size 40M,
       '/u01/oracle/8.0.4/dbs/data1test.dbf' size 10M,
       '/u01/oracle/8.0.4/dbs/data2test.dbf' size 20M;

  1. Perform media recovery on the database: 
Svrmgrl> Recover database;

  1. Open the database: 
Svrmgrl> alter database open;

  1. Do a shutdown normal of the database.
  2. Take a cold backup of the database.

Recovering from Loss of Online Redo Logs

Redo logs are important for performing up-to-the-minute database recovery. Several symptoms and errors can indicate that the redo logs are lost or corrupted: 

"RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL"

These errors indicate that the redo logs are misplaced, removed, corrupt, or from an old backup. The recovery technique that you need to use depends on whether you've mirrored your redo logs.

Recover with mirrored redo logs

  1. Identify the files that need recovery: 
Svrmgrl> select * from V$RECOVERY_FILE;
  1. Shut down the database.
  2. Bring all the redo log files to a constant state by copying the archive log with the same sequence number as the redo log that was lost to the original redo log.
  3. Start the database.
  4. If you've lost the current redo logs and your archive logs, the only way that you can open the database is by forcing it to open. This involves corrupting the database.

Recover without mirrored redo logs

  1. Shut down the database.
  2. Take a full database backup.

Caution: Database can become corrupt if you force it to open
These steps use some very dangerous parameters that should be used only on understanding their consequences and with the help of an Oracle Support Services analyst. After the data-base is opened in this manner, you should rebuild the data-base at your earliest chance.

  1. Make the following changes in your INITSID.ORA file: 
     _allow_resetlogs_corruption = true
     _corrupted_rollback_segments =
       list of all rollback segments

  1. Perform a startup mount.
  2. Perform an incomplete recovery of the database: 
Svrmgrl> RECOVER DATABASE UNTIL CANCEL;

  1. When prompted for the file, typeCANCEL.
  2. Reset the logs and open the database: 
Svrmgrl> ALTER DATABASE OPEN RESETLOGS;

  1. Rebuild the database by taking a full database export and then importing it to a new database.

Rebuild the database after forcing it to open
Rebuilding the database is an essential step in this procedure because a forced database-open can corrupt the database.

The following parameters can have a very harmful effect on the database and should be used carefully: 

ALTER DATABASE OPEN RESETLOGS;

© Copyright, Macmillan Computer Publishing. All rights reserved.