Optimize Oracle downtime and database recovery

The ORACLE tutorial is to optimize the Oracle downtime and database recovery.

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:

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

[NextPage]

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.

Event 10: Lost archived Redo log files

If the archived redo log file is lost, a cold backup should be performed immediately, and it is best to export the entire database at the same time. Any restoration of the redo log file without the Lost Archive will be incomplete,

Event 11: The rollback segment of the lost activity

This refers to the loss of a data file in a rollback segment, which is a critical recovery process and mainly stores active transactions. It is assumed that the database is up and you want to save the currently running transactions. To use the following recovery procedure, the database must run in archivelog mode.

You can perform the following steps to restore the data:

1. Do not close the database. For such events, it is easier to start the database to solve problems than to close the database,

2. Make all rollback segments in the data file offline

3. Delete all offline rollback segments

4. in step 1 above, if there is an active transaction in the rollback segment, you cannot make it offline and run the following query to check which transactions are active:

If the preceding query has no results, all rollback segments are OFFLINE. However, if the preceding query returns one or more rows and the status is pending offline, check the ACTIVE_TX columns of these rollback segments and the rollback segments with 0 values will soon be offline. However, a non-0 value indicates that there are active transactions on them, they need to be submitted or rolled back,

5. Process active transactions and execute the following query to check which user transactions are assigned to the rollback segment:

After you know which users have active transactions in the "pending offline" rollback segment, you can ask them to submit or roll back their transactions, alternatively, you can use the following command to kill their processes:

Alter system kill session 'sid, serial #';

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

Discard the tablespace and all its contents

Recreate the rollback tablespace

Recreate rollback segments and make them online

[NextPage]

Event 12: All rollback segments are lost.

In this case, all active transactions are lost and rollback segments need to be created again. This major problem may be caused by a hardware problem. You can perform the following steps to recover it:

[1] [2] Next page

The ORACLE tutorial is to optimize the Oracle downtime and database recovery.
1. Shut down the database

2. Use DBVERIFY to verify all data files

3. solve other hardware problems or damaged data files

4. Start the database instance as startup mount

5. Restore media in the database
　
6. Open the database

7. Create a New rollback segment as needed

Event 13: exported files are corrupted.

If the exported file cannot be used, you should cold back up the database and export a full database. This assumes that the database is normal. If the database is damaged, perform the following steps:

1. ORA-1157 error messages usually indicate that one or more data files are corrupted. Find out which tables are affected and they should be the tables in the data file specified in the error message

2. Skip bad data blocks and select the data from the table to the temporary table,

3. Discard the damaged table

4. rename a temporary table as a discarded table

5. re-create all indexes on the affected table

6. Use the validate structure cascade option to analyze all corrupted tables.

It should be noted that data in damaged blocks will be lost and cannot be recovered.

Event 14: shutdown during Hot Backup

If you suddenly shut down when hot backup is in progress, some of the tablespace may be in backup mode. When you try to open the database, it will only mount, it also indicates that some tablespaces are in hot backup mode. Because the database cannot be opened, you cannot remove the tablespaces from the hot backup mode. You can perform the following steps to restore them:

1. startup mount Database

2. query v $ backup to view which data files are in the ACTIVE state,

3. Run the alter database datafile end backup command to remove the data files from the BACKUP mode.

4. Open the database

Event 15: Restore to a special time point

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

1. Shut down database instances

2. Start the database instance in NOMOUNT status

3. Use the UNTIL option to restore the database

4. Open the database

5. Shutdown NORMAL

6. Start a database instance

Event 16: recover to a special event or activity

You can use the following steps to restore:

1. Shut down database instances

2. Start the database instance in NOMOUNT state;

3. Use until cancel to restore the database and provide archived redo log file requests UNTIL this activity/event occurs.

4. Enter CANCEL to CANCEL restoration.

5. Open the database;

6. Shut down the database in NORMAL mode.

7. Start a database instance

Conclusion
　
High Availability is important for any business. ORACLE DBAs can use plans to minimize downtime. This article discusses different strategies to achieve this purpose.

Previous Page

Previous Page [1] [2]