Optimizing Oracle Downtime and database recovery

The steps to minimize Oracle downtime are discussed here. All forms of downtime-planned or unplanned-always occur, and a DBA should have the right backup strategy so that it can recover more quickly if there is a problem with the database.

The following are the assumptions of the backup strategy and the operating conditions of the database

The control file is mirrored

Database running in Archivelog mode

A cold backup every week

Daily Hot Backup

Make a full database export every day

Event 1: Complete database refactoring

In this case, you can refactor the database using a full database export or a combination of hot and cold backups. Note that no matter which way you choose, transactions in the online redo log will be lost.

Event 2: Table Space for recovery section

You can use the following steps to recover:

1. Start the database in restrict mode

2. Recreate the table space

3, the use of the latest full database export to import, and the use of ignore=y options;

4. Shutdown and restart the database instance in normal mode

Event 3: Missing a generic data file

The recovery step for losing a generic data file depends on the type of tablespace contained in the missing data file; For example: Rollback segment, user tablespace, Index table space, or read-only tablespace, you may encounter the following error:

. Try to start the database and encounter the wrong information ORA-1157, ORA-1110, there may be an operating system error

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

The following steps can be used as a recovery:

1. Close the database

2. Recover lost data files from hot backup

3. Startup Mount Database

4, execute the following query to get all your online redo log files and their corresponding order and 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 resulting change# than the online redo log the smallest first_change# is also small, then the file can not be fully restored, you can have two options:

. If you can accept a database modification since the last cold backup was lost, mount the backup and resume

. If you cannot accept changes to a lost database, you must re-create the tablespace

6. Recover data files by using archived and online redo log

7, open the database

Event 4: Restore a special table

The following steps can be used to recover:

1. Use the most recent full database export to import tables, and use the owner= and tables= options

2. You may need to rebuild the table index, considering the performance reasons