Using Oracle8

Chapter 14

Performing Database Recovery

• Recovery Strategies
• Analyzing the Failure and Determining Recovery Options
• Recovering from the Loss of General Data Files
  • Recovering from a Lost Data File in the User Tablespace
  • Recovering from a Lost Data File in a Read-Only Tablespace
  • Recovering from a Lost Data File in an Index Tablespace
• Recovering from a Lost Data File in a Rollback Tablespace
  • Database is Down
  • Database Is Up and Running
• Recovering from Loss of System Tablespace
  • Recovering with a Cold Backup
  • Recovering with a Hot Backup
  • Recovering with Missing Redo Data
• Recovering from the Loss of a Control File
  • Recovering with a Mirrored Control File
  • Recovering Without a Mirrored Control File
• Recovering from Loss of Online Redo Logs

• Determine the extent of system failure
• Recover from loss of general data files
• Recover from loss of system tablespace
• Recover from loss of control files

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:

• Reduce the size of components that need to be recovered.
• Use Oracle8's table and index partitioning features. Using partitions will minimize the impact of the failure on the rest of the system.
• Ensure that the backup can be easily and quickly accessed in the event of a failure.
• Test your backups to avoid any surprises.
• Ensure that you are familiar with the recovery procedures that have to be followed based on the type of failure. Keep the common recovery scripts handy.
• Design the database to promote autonomous components. When the components are autonomous, their impact on the rest of the system is minimal and the recovery is faster.

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.

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.

6. 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.
7. 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:

• What led to the failure: power outage, upgrade, maintenance (OS, hardware, network, database)?
• How did the database go down: shutdown abort, crash, normal shutdown?
• Are there any operating system errors?
• Was the server rebooted?

Are there any errors in the operating system log?

• Are there any errors in the alert log?
• Were trace files generated?
• How critical is the lost data?
• Are you running Oracle Parallel Server?
• Have you attempted any kind of recovery so far? If so, what steps have already been performed?
• What's your backup strategy?
• If you have cold backups, how was the database shut down when the cold backups were taken?
• Are you running in archive log mode?
• Do you have all archive logs accessible without any break in the sequence numbers?
• Are the online redo logs mirrored?
• Are the control files mirrored?
• Do you have recent full database export?
• What else was running when the system crashed?
• Can you bring the instance up?
• Can you mount the database? Open it?
• What are your system availability requirements?
• What size is the database?
• Are you using raw files?
• What events (if any) are set in your INIT.ORA?
• How many rollback segments do you have?

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:

• The message 00205, 00000, "error in identifying controlfile, check alert log for more info" means the system couldn't find a control file of the specified name and size. Check that all control files are online and that they're the same files that the system created at cold start time.
• The message 00280, 00000, "change %s for thread %s is in sequence #%s" helps you find the redo log file with the specified change number requested by other messages. Use this information to specify the required archived redo log files for other errors.
• The message 00312, 00000, "online log %s thread %s: '%s'" reports the filename for details of another message. Other messages will accompany this message; see the associated messages for the appropriate action to take.
• The message 00314, 00000, "log %s of thread %s, expected sequence# %s doesn't match %s" means that the online log is corrupted or is an old version. You need to find and install correct log versions or reset the log.
• The message 00376, 00000, "file %s cannot be read at this time" indicates that Oracle is trying to read from an unreadable file-more than likely, an offline file. Check the file's status and bring it online, if necessary.
• The message 00604, 00000, "error occurred at recursive SQL level %s" indicates that an error occurred while processing a recursive SQL statement (which applies to internal dictionary tables). Usually this error is accompanied by other errors. If the situation described in the next error can be corrected, do so; otherwise, contact Oracle Support.
• The message 01110, 00000, "data file %s: '%s'" reports a filename for details of another error. See the accompanying errors for analyzing the problem further.
• The message 01116, 00000, "error in opening database file %s" usually means that the file isn't accessible. The solution is to restore the database file.
• The message 01157, 00000, "cannot identify data file %s - file not found" means that the background process couldn't find one of the data files. The database will prohibit access to this file but other files will be unaffected; however, the first instance to open the database will need to access all online data files. Another error from the operating system will describe why the file wasn't found. To solve this problem, make the file available to database and then open the database or do an ALTER SYSTEM CHECK DATAFILES.
• The message 01194, 00000, "file %s needs more recovery to be consistent" means that an incomplete recovery session was started, but an insufficient number of logs was applied to make the file consistent. The reported file wasn't closed cleanly when it was last opened by the database. You must recover this file to a time when it wasn't being updated. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery. Either apply more logs until the file is consistent or restore the file from an older backup and repeat the recovery.

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:

• You're trying to start the database and get error message ORA-1157, ORA-1110 and possibly an operating system error.
• You're trying to shut down the database in normal or immediate mode and get error message ORA-1116, ORA-1110 and possibly an operating system error.

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#;

5. 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.

6. 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.

7. 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:
• If you can afford losing the database changes since the most recent cold backup, restore the backup and continue with the recovery.
• If you can't afford to lose the database changes, you have to recreate the tablespace as described in the following section, "Recovering with Missing Redo Data."

5. 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.

6. 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

3. Offline drop the data file:

Svrmgrl> ALTER DATABASE DATAFILE 'fullpath of datafile'
                   OFFLINE DROP;

4. Open the database:

Svrmgrl> ALTER DATABASE OPEN;

5. Drop the user tablespace:

Svrmgrl> DROP TABLESPACE tablespace_name
         INCLUDING CONTENTS;

6. 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:

• At the time of the last backup, the tablespace was read-only but it has been made read-write since that time.
• At the time of the last backup, the tablespace was read-write but it has been made read-only since that time.

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

3. Offline drop the data file:

Svrmgrl> ALTER DATABASE DATAFILE 'fullpath of datafile'
                   OFFLINE DROP;

4. Open the database:

Svrmgrl> ALTER DATABASE OPEN;

5. Drop the user tablespace:

Svrmgrl> DROP TABLESPACE tablespace_name
                   INCLUDING CONTENTS;

6. 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

3. Offline drop the lost data file:

Svrmgrl> ALTER DATABASE DATAFILE 'fullpath of datafile' FFLINE DROP;

4. 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.

5. 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:
   • Comment out the ROLLBACK_SEGMENTS parameter.
   • Add the following line to list all the rollback segments originally listed in the ROLLBACK_SEGMENTS parameter:

_Corrupted_rollback_segments
  = (rollback1,rollback2,...,rollbackN)

Now start the database in restricted mode:

Svrmgrl> startup restrict mount

6. Drop the rollback tablespace that contained the lost data file:

Svrmgrl> drop tablespace tablespace_name including contents;

7. Recreate the rollback tablespace with all its rollback segments and be sure to bring them online.
8. Make the database available for general use:

Svrmgrl> alter system disable restricted session;

9. If you had to edit the INITSID.ORA file in step 6, shut down the database and edit the file again as follows:
   • Uncomment the ROLLBACK_SEGMENTS parameter.
   • If you had to perform step 6, remove the following line:

_Corrupted_rollback_segments
  = (rollback1,rollback2,...,rollbackN)

10. 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;

4. If the file is offline, bring it online:

Svrmgrl> ALTER DATABASE DATAFILE 'full path of datafile'
                   ONLINE;

5. 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#;

6. 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:
   • Restore from a full database backup, which may result in data loss.
   • Force the database to open in an inconsistent state, and then rebuild the database.

Open the database in an inconsistent state and rebuild it

1. Shut down the database.
2. Take a full database backup.
3. Make the following changes in your INITSID.ORA file:
   • Add the following lines:

       _allow_resetlogs_corruption = true
       _corrupted_rollback_segments
           = list of all rollback segments

• Comment out the rollback_segments parameter.

4. Do a startup mount.
5. Perform an incomplete recovery of the database:

Svrmgrl> RECOVER DATABASE UNTIL CANCEL;

6. When prompted for the file, typeCANCEL.
7. Reset the logs and open the database:

Svrmgrl> ALTER DATABASE OPEN RESETLOGS;

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

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.

9. 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.

Recover a live database in ARCHIVELOG mode

1. Offline the lost data file:

ALTER DATABASE DATAFILE 'fullpath of datafile' OFFLINE;

2. Restore the data file from a backup.
3. Apply media recovery on the data file:

RECOVER DATAFILE 'fullpath of datafile';

4. 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.

2. 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';

3. Drop all offline rollback segments by running the following command for each segment:

DROP ROLLBACK SEGMENT rollback_segment;

4. 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:

• The database crashed. When trying to start the database, you get ORA-1157, ORA-1110 and possibly an operating system error.
• You're trying to shut down the database in normal or immediate mode and get ORA-1116, ORA-1110 and possibly an operating system error.

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#;

5. 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:
• If you can afford losing the database changes since the most recent cold backup, restore the backup and continue with the recovery.
• If you can't afford to lose the database changes, you have to rebuild the database as described earlier in the section "Recovering with Missing Redo Data."

6. 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.

7. 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#;

5. 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.

6. 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:

• Do you have a backup trace of the control file that accurately reflects the current database structure?
• If you don't have a backup trace of the control file but the database can mount, proceed with the following steps. In the worst-case scenario, when you've lost all the control files, you have to execute the CREATE CONTROLFILE statement.

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.

4. 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.
5. Do a shutdown normal for the database.
6. Take a full backup of the database to protect from any further problems.
7. Execute the following to create a new control file:

Svrmgrl> @create_control.sql

8. Open the database by executing the following at the server manager prompt:

svrmgrl> Alter database open;

9. Shut down the database.
10. 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:

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;

5. Perform media recovery on the database:

Svrmgrl> Recover database;

6. Open the database:

Svrmgrl> alter database open;

7. Do a shutdown normal of the database.
8. 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:

• The database crashed, and you get ORA-1194 upon trying to start the database.
• You're performing media recovery with the following command and get ORA-314, ORA-312, and ORA-376:

"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;

2. Shut down the database.
3. 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.
4. Start the database.
5. 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.

3. Make the following changes in your INITSID.ORA file:
   • Add the following lines:

     _allow_resetlogs_corruption = true
     _corrupted_rollback_segments =
       list of all rollback segments

• Comment out the rollback_segments parameter.

4. Perform a startup mount.
5. Perform an incomplete recovery of the database:

Svrmgrl> RECOVER DATABASE UNTIL CANCEL;

6. When prompted for the file, typeCANCEL.
7. Reset the logs and open the database:

Svrmgrl> ALTER DATABASE OPEN RESETLOGS;

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

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

• _allow_resetlogs_corruption  When this parameter is set to TRUE, it will allow resetlogs even if there are hot backups that need more redo applied or the data files are out of sync for some other reason. It's effective only if you open the database with the RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS;

• _corrupted_rollback_segments  If a rollback segment isn't accessible because the file it's in is corrupted or offline, you can force the system to come up without the rollback segment by specifying the segment in this parameter. This parameter prevents the rollback of active transactions in the specified corrupted rollback segments.
• _offline_rollback_segments  This parameter prevents the rollback of active transactions in the listed offline rollback segments.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.