Optimize Oracle downtime and database recovery

xiaoxiao2021-03-06  74

The steps that minimize Oracle downtime are discussed here. Various forms of shutdown-planned or non-planned - always happen, a DBA should have a correct backup strategy so that you can recover more quickly when there is a problem in the database. The following is a hypothetical backup policy and a database operating condition control file is a mirrored database running in ArchiveLog mode. Every week, the hot backup is performed daily, a full database export event 1: Complete database reconstruction In this case, you can reconstruct the database using a full database export or hot hot backup. Note that no matter which way you choose, the transactions in the online Redo log will be lost. Event 2: The tablespace of the recovery section can be restored using the following steps: 1, start the database 2 in the Restrict mode, recreate the tablespace 3, and import it with the latest full database and use the option = y; 4. Turn off and re-re-started the database instance event 3: Lost the recovery step of the general data file Lost General data file is fixed according to the table space type contained in the lost data file; for example: rollback segment, user table space, index Table space or read-only table space, you may encounter the following error:. Try to launch the database and encounter the wrong information ORA-1157, ORA-1110, may have an operating system error. Try Normal or Immediate-mode mode off the database, may touch ORA-1116, ORA-1110 error messages, and a system error The steps below can be used as recovery: 1. Turn off the database 2, and recover lost data files from the hot backup. STARTUP MOUNT Database 4, perform the following queries to get all your online Redo log files and their corresponding order and first modification numbers:

Select X.Group #, Member, Sequence #, First_Change # from v $ log x, v $ logiled ywhere x.group # = y.group #;

5. If you get the Change # smaller than the online Redo Log is small, then the file cannot be fully recovered, you can have two options:

If you can accept database modifications since the last cold backup, load your backup and continue to restore. If you cannot accept the change of the lost database, you must recreate the tablespace 6, restore the data file 7 by using archive and online REDO logs. Open the database

Event 4: Restore a special table can be recovered with the following steps: 1, use the nearest full database to guide the introduction table, and use Owner =

Tables =

Options

2. Considering the reasons for performance, you may need to reconstruct the table index

Event 5: Lost Control File

When the database is up and run, the problem of the control file is usually detected. If the control file is lost or corrupted, Oracle will not understand that the next database will cause an ORA-205 error (identification control file "% when the next database is started. The error of s), there is also a system-level error,

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

1. If it is running, turn it off first.

2. Find the cause of the loss control file is due to hardware issues (disk or controllers)?

3. If it is not a hardware problem, copy a good copy of the control file to the lost position 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, update Control_Files to reflect the latest control file location 6, start the database

Event 6: Lost all control files

You can use the following steps to recover:

1, turn off the database

2, carry out full database backup, including all data files and redo log files

3, start the database with Nomount status

4. Use Create ControlFile to recreate the control file, you can also back up the control file to a trace file, then execute the file.

5, media recovery on the database

6, open the database

7, use Shutdown Normal to close the database

8. A cold backup for the database

Event 7: Lost an index is the easiest way to recreate the lost index event 8: Lost a non-active Redo log If the REDO data is lost, the recovery will be incomplete, and the table space involved must be recreated. To recreate the tablespace, you can use a full database to export so you can easily import data and recreate the object of the table. You can use the following steps to restore: 1. Switch the Redo Log file 2 through the alter system, turn off the database 3, Startup Mount database 4, offline delete data file 5, open the database 6, remove the user's tablespace, including it Content, 7. Recreate tablespaces with full database backup 9: Lost REDO logs, such as event 8, if it lost REDO data, recovery will be incomplete, must recreate the table involved Space, you can use the following steps: 1. Turn off the database 2, Startup Mount database 3, offline delete data file 4, open the database 5, delete the user's tablespace, including the content, 6, re-rewred through full database backup Creating a table space and objects should be noted that the active transaction will lose the event 10: Lost archived redo log files If the archived redo log file is lost, you should immediately carry out a cold backup, it is best to conduct a full database export, Any recovery without lost archive redo log files will be incomplete, event 11: Lost Roll Rolling Section Here is a data file that lost a rollback segment, this is a critical recovery process, it mainly It is a transaction that is preserved. It is assumed here that the database is already up, and you want to save the currently running transaction. To use the following recovery procedure, the database must run in ArchiveLog mode. You can use the following steps: 1, do not turn off the database, for this event, the database starter is easier to solve the problem than the closing, 2, order all the returned segments in this data file 3, delete all offline rollback segments 4 In step 2 above, if there is an active transaction in the roll, you will not be offline, you can run the following queries to see which things are active:

Select segment_name, XACTS Active_TX, V.StatusFrom V $ ROLLSTAT V, DBA_ROLLBACK_SEGSWHERE TABA_ROLLBACK_SEGSWHERE TABLESPACE_NAME = 'TABLESPACE_NAME' ANDSEGMENT_ID = USN;

If the above query is no result, all returns are offline, but if the above query returns a row or multi-line, and its status is pending offline, then you can check the Active_TX column of these returns, with 0 Value returns will soon be offline; however, the value of non-zero means that there is active transaction above, they need to be submitted or rolled, 5, handling the active transaction, performs the following queries to see which users Assigned to the rollback segment:

Select S.SID, S.Serial #, S.Username, R.Name "Rollback" from V $ Session S, V $ T, V $ ROLLNAME RWHERE R.NAME IN ('Pending_Rollback1', 'Pending_rollback2', .. .. 'pending_rollbackn') andt.taddr = t.addr andt.xidusn = r.usn; After knowing which users have active transactions on the "Pending Offline" rollback, they can ask them to submit or roll back them. Transactions, or you can use the following commands to kill their processes: Alter System Kill Session 'SID, Serial #'; 6. After you have processed all the active transactions, do the following steps: Discard the table space and all of them Recreate the rollback table space Re-create a rollback segment, and let them online

Event 12: Lost All Rolling Segments Under this event, all active transactions will be lost, and you need to recreate the rollback segment. Such a big problem may be due to a hardware problem, and the following steps can be recovered: 1. Turn off the database 2. Use DBVERIFY to verify all data files 3, solve other hardware issues or data files 4, startup mount Way Start Database Instance 5, execute media recovery 6 on the database, open the database 7, create a new rollback segment event as needed 13: Export file corruption If the export file cannot be used, then you should be cold backup database and make a full database Export, this is assumed that the database itself has no problem, and if the database is damaged, the following steps should be performed: 1, ORA-1157 error messages typically represent one or more data files corrupted. Identify which tables are affected, they should be Table 2 in the data file indicated in the error message, skip the bad data block, select the data from the table to the temporary table, 3, discard the damaged Table 4, will temporarily Table 5 with a discarded table 5, re-establish all index 6, using the Validate Structure Cascade option to analyze all corrupted tables to note that the data will be lost in the damage block and cannot recover event 14: When the hot spare is turned off when the hot backup is in progress, some of the tablespaces will might be in the backup mode. When you try to open the database, it will only mount, and indicate some tablespace in the hot backup mode. Since the database cannot be opened, you will not let the tablespace out of the hot backup mode, you can use the following steps to recover: 1. Startup Mount Database 2, query V $ backup to see which data files are in Active Status, 3, by using commands Alter Database DataFile

End Backup. To disengage these data files from backup mode

4, open the database

Event 15: Restore to a special point in time

The following steps can be used to perform Point-in-Time recovery

1. Turn off the database instance

2, start the database instance with Nomount

3, use unsil

转载请注明原文地址:https://www.9cbs.com/read-121045.html

New Post(0)