Optimizing Oracle Downtime and database recovery

oracle| Recovery | data | database | Optimization 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

Event 5: Lost control file

When the database is up and running, it is not usually possible to detect a problem with the control file, if the control file is lost or corrupted, Oracle will not understand that the next time the database starts, it will cause a ORA-205 error (identifying the error of the control file "%s"), and a system-level error,

If you just lost one of the control files, you can use the following steps to recover:

1. If it's running, shut it down first.

2. Find out the reason for losing the control file, is it due to hardware problem (disk or controller)?

3. If it is not a hardware problem, copy a good copy of the control file to the missing location and skip to step 5,

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

5, edit Initsid.ora or Configsid.ora, update control_files to reflect the latest control file location

6, start the database

Event 6: Loss of all control files

The following steps can be used to recover:

1. Close the database

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

3, start the database in Nomount state

4, use create Controlfile to recreate the control file, you can also back up the control file to a trace file, and then execute the file

5, on the database for media recovery

6, open the database

7. Close the database using shutdown normal

8, a cold backup of the database

Event 7: Missing an index

The easiest way to do this is to re-create the missing index

Event 8: Missing an inactive redo log

If the redo data is lost, the recovery will be incomplete and the table space involved must be recreated. To re-create the tablespace, you can use a full database export, which makes it easy to import data and recreate the objects in that tablespace. You can use the following steps to recover:

1, through alter system to switch redo log files

2. Close the database

3. Startup Mount Database

4, offline deletion of data files involved

5, open the database

6, delete the user's table space, including the content,

7. Recreate the table space and the objects in it through a full database backup
　
Event 9: Missing active redo log

As discussed in Event 8, if the redo data is lost, the recovery will be incomplete, the table space involved must be recreated, and the following steps can be used to recover:

1. Close the database

2. Startup Mount Database

3, offline deletion of data files involved

4, open the database

5, delete the user's table space, including the content,

6. Recreate the table space and the objects in it through a full database backup

Be aware that the active transaction will be lost

Event 10: Missing archived redo log files

If the archived redo log files are missing, a cold backup should be done immediately, preferably a full database export, no lost archive redo log file Any recovery will be incomplete,

Event 11: Missing active rollback segment

This refers to the loss of a rollback segment of a data file, this is a critical recovery process, it is mainly in the maintenance of active transactions. This assumes that the database is already up and that you want to save the currently running transaction. To use the following recovery procedure, the database must be running in Archivelog mode.

You can use the following steps to recover:

1, do not close the database, for this event, database startup is easier to solve problems than shutdown,

2. Make all rollback segments that belong to the data file offline

3. Delete all offline rollback segments

4. In the 2nd step above, if you roll back the active transaction in the paragraph, you will not be able to take it offline and run the following query to see which things are active:




SELECT segment_name, xacts Active_tx, V.status
From V$rollstat V, Dba_rollback_segs
WHERE tablespace_name = ' tablespace_name ' and
segment_id = USN;

If the above query has no results, all of the rollback segments are offline, but if the query above returns one or more rows and its status is pending OFFLINE, the Active_tx column with the 0 value can be checked, and the rollback segments with the values in the roll will soon be offline; A value other than 0 indicates that there are active transactions on them that need to be committed or rolled back,

5. Process the active transaction and execute the following query to see which user's transactions are assigned to the rollback segment:


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 know which users have active transactions on the rollback segment of the "Pending offline", you can ask them to commit or rollback their transactions, or you can use the following commands to kill their processes:

ALTER SYSTEM KILL session ' Sid, Serial# ';

6. After you have processed all activities, perform the following steps:

Discard the table space and all of its contents

Recreate the rollback table space

Recreate the rollback segments and make them online

Event 12: Missing all rollback segments

Under such an event, all active transactions are lost and a rollback segment needs to be recreated. Such a large problem may be caused by a hardware problem and can be recovered using the following steps:

1. Close the database

2, use Dbverify to verify all data files

3, to solve other hardware problems or data file corruption

4. Start the database instance in the form of startup Mount

5. Performing media recovery on the database
　
6, open the database

7, create a new rollback segment as needed

Event 13: Corrupted Export File

If the export file is not available, then you should back up the database and make a full database export, assuming that the database itself is not a problem, and if the database is damaged, follow these steps:

1, the ORA-1157 error message usually indicates that one or more data files are corrupted. To find out which tables are affected, they should be tables in the data file specified in the error message

2, skipping the bad block of data, the data from the table selected to the temporary table,

3, discard the damaged table

4. Rename temporary table to discarded table

5. Re-establish all indexes on the affected table

6. Use the Validate STRUCTURE cascade option to analyze all damaged tables

Note that the data in the corrupted block will be lost and cannot be recovered

Event 14: Shutdown on Hot backup

If you suddenly shut down when a hot backup is in progress, some of these tablespaces will probably be in backup mode, and when you try to open the database, it will only mount and indicate that some of the tablespaces are in hot backup mode, and you will not be able to leave the tablespace out of hot backup mode because the database is not open. You can use the following steps to recover:

1. Startup Mount Database

2, query v$backup to see which data files in the active state,

3, by using the command alter DATABASE DataFile end backup. To detach these data files from backup mode

4, open the database

Event 15: Restore to a particular point in time

The following steps can be used to perform point-in-time recovery

1. Close database Instance

2, start the database instance in Nomount state

3, use the until option to restore the database

4, open the database

5. Shutdown NORMAL

6, start the database instance

Event 16: Revert to a particular event or activity

You can use the following steps to recover:

1. Close database Instance

2, the Nomount state to start the database instance;

3, use until Cancel to restore the database, provide archived redo log file request until the activity/event

4, enter Cancel to cancel the recovery

5, open the database;

6, use normal mode to shut down the database

7, start the database instance

Conclusion
　
High availability is important for any business, and ORACLE DBAs can plan to make sure downtime is minimized, and this article discusses different strategies to achieve this goal.