Optimize Oracle downtime and database recovery [on]

Here we will discuss the steps to minimize Oracle downtime. Various types of downtime-planned or unplanned-always happen. a dba should have the correct backup policy so that the database can be restored faster when a problem occurs.

The following are hypothetical backup policies and database operating conditions:

The control file is an image.

The database runs in archivelog mode.

Cold backup is performed every week

Daily Hot Backup

Perform full Database Export every day

Event 1: complete database Reconstruction

In this case, you can use full Database Export or hot/cold backup to reconstruct the database. Note that no matter which method you choose, the transactions in the online redo log will be lost.

Event 2: Restore partial tablespace

You can use the following steps to restore:

1. Start the database in restrict Mode

2. Recreate the tablespace.

3. Import with the latest full database export and use the ignore = y option;

4. Shut down and restart the database instance in normal mode.

Event 3: loss of common data files

Recovery steps for data file loss are based on the types of tablespaces contained in the lost data file. For example, rollback segments, user tablespaces, index tablespace or read-only tablespace. You may encounter the following errors:

. An error occurred while trying to start the database and encountering an incorrect information ORA-1157, ORA-1110, and possibly an operating system error

. Attempting to shut down the database in normal or immediate mode may encounter ORA-1116, ORA-1110 error messages, and a system error

The following steps can be used for recovery:

1. Shut down the database

2. Recover lost data files from Hot Backup

3. Startup mount Database

4. Execute the following query to obtain all your online redo log files, their corresponding order, and the first modification Number:

Select x. GROUP #, MEMBER, SEQUENCE #, FIRST_CHANGE #
From v $ log x, V $ LOGILE Y
Where x. GROUP # = Y. GROUP #;

5. If the obtained CHANGE # is smaller than the FIRST_CHANGE #, the file cannot be completely restored. You can have two options:

. If you can accept the loss of database modifications since the last cold backup, load the backup and continue to recover

. If you cannot accept changes to the lost database, you must recreate the tablespace.

6. Restore data files by using archived and online redo logs

7. Open the database

Event 4: restore a special table

You can perform the following steps to restore data:

1. Use the latest full Database Export to import tables, and use the options of owner = and tables =

2. Considering the performance, you may need to recreate the table index.

Event 5: loss of control files

When the database is up and running, the control file problem cannot be detected. If the control file is lost or damaged, Oracle will not understand it, the next time the database is started, it will cause a ORA-205 error (an error that identifies the control file "% s) and a system-level error,

If only one of the control files is lost, follow these steps:

1. if it is running, close it first.

2. Find out the cause of the loss of control files, is it because of hardware problems (disk or controller )?

3. If it is not a hardware problem, copy a good copy of the control file to the lost location and jump to Step 5,

4. If it is a hardware problem, copy a good control file to a reliable location.

5. Edit initsid. ora or configsid. ora and update CONTROL_FILES to reflect the latest control file location.

6. Start the database

Event 6: All control files are lost.

You can perform the following steps to restore data:

1. Shut down the database

2. perform a full database backup, including all data files and redo log files.

3. Start the database in NOMOUNT status

4. Use create controlfile to re-CREATE the control file. You can also back up the control file to a trace file and then execute the file

5. Restore the media in the database

6. Open the database

7. Use shutdown normal to close the database

8. Perform a cold backup for the database

Event 7: an index is lost.

The simplest way is to recreate the missing index.

Event 8: an inactive redo log is lost.

If the redo data is lost, the recovery is incomplete. You must recreate the tablespace involved. To recreate a tablespace, you can use the full database to export the tablespace, so that you can easily import data and recreate the tablespace objects. You can use the following steps to restore:

1. Use Alter system to switch the redo log file

2. Shut down the database

3. startup mount Database

4. delete data files offline

5. Open the database

6. Delete the user's tablespace, including the content,

7. Recreate the tablespace and objects in the tablespace through full database backup
　
Event 9: the active Redo log is lost.

As discussed in event 8, if the redo data is lost, the restoration is incomplete. You must recreate the tablespace involved and perform the following steps to restore it:

1. Shut down the database

2. startup mount Database

3. delete data files offline

4. Open the database

5. Delete the user's tablespace, including the content,

6. Create a tablespace and its objects through full database backup

Note that the active transactions will be lost.