Oracle Recovery solutions for different failures

In non-catalog mode, the backed-up information is stored in controlfile. So don't forget to back up the control files when you back up the Rman.

1. SPFile lost

Startup Nomount;

Set dbid 3988862108;

Restore SPFile from Autobackup;

or through a file

Restore SPFile from ' path/file_name '

Shutdown immediate;

Set dbid 3988862108;

Startup

2. Controlfile all lost

More Wonderful content: http://www.bianceng.cn/database/Oracle/

The control file has 3 redundancy, rarely occurs in all cases, and when all of the control files are lost, recovery requires the following steps:

Rman>set dbid 3988862108;

Rman>startup Nomount;

Rman>restore controlfile from Autobackup;

Or recover from a file

Rman>restore controlfile from ' file_name ';

Rman>alter database Mount;

Rman>recover database; (Ensure data consistency because the SCN changes in the control file)

Rman>alter database open resetlogs;

The Resetlogs command represents the end of a logical lifetime of a database and the start of another database logical lifetime, and the SCN is not reset each time the Resetlogs command is used, although Oracle resets the log sequence number and resets the online redo log content. This is done to prevent the log sequence from conflicting after incomplete recovery (because there is a time lag between the existing log and the data file).

3. Redo Log File damage

The redo log file is the most demanding component in the database, first of all, its IO requirements for disk are very high, second, once the current group fails, the database crashes immediately, and 100% data loss occurs, so Oracle recommends that at least two members per group, And the log files are locked and safekeeping while the database is running.

The recovery of Redo log is divided into two types: current and non-current

3.1 Current situation

Causing redo damage, many cases are related to sudden power outages. In this case, it is rather troublesome.

(1) If there is archiving and backup, you can use incomplete recovery.

Sql>startup Mount;

Sql>recover database until cancel; Select Auto to restore the available archive logs, and then rerun:

Sql>recover database until cancel; This time the input cancel, completes does not complete restores,

To open data with Resetlogs:

Sql>alter database open resetlogs;

(2) Forced recovery, which may result in inconsistent data

Sql>startup Mount;

Sql>alter system Set "_allow_resetlogs_corruption" =true scope=spfile;

Sql>recover database until cancel;

Sql>alter database open resetlogs;

With good luck, the database opens normally, but because it's opened with _allow_resetlogs_corruption, the data is lost and the database is in an inconsistent state. Therefore, Oracle recommends exporting the database by exp method in this case. After rebuilding the new database, import again.

Redo damage is generally also easily accompanied by the following 2 kinds of errors: ora-600[2662] (SCN-related) and ora-600[4000] (rollback segment related).

Two articles on Metalink describe two ways to handle the situation:

Tech:summary for forcing the "Database Open with" _allow_resetlogs_corruption "with Automatic Undo Management [ID 283945.1 ]

Http://blog.csdn.net/tianlesoftware/archive/2010/12/29/6106083.aspx

ORA-600 [2662] block SCN are ahead of current SCN [ID 28929.1]

Http://blog.csdn.net/tianlesoftware/archive/2010/12/29/6106130.aspx

In both cases the recovery is a bit complicated, and go back to doing a separate test, adding in.

3.2 Non Current situation

Recovery in this case is simpler because the redo log has been archived or is being archived. is not in use. You can view the status of the redo log through V$log.

(1) If the status is inactive, it means that the archive has been completed and the redo log can be cleared directly.

Sql>startup Mount;

Sql> ALTER DATABASE clear logfile Group 3;

Sql>alter database open;

(2) If status is active, indicating that it is being archived, you need to use the following statement:

Sql>startup Mount;

Sql> ALTER DATABASE Clear unarchived logfile Group 3;

Sql>alter database open;

4. Non-system table space damage

If a media failure causes a table space to be unavailable, recovery can take place in the open or Mount state of the database, as follows:

(1) Place the tablespace in the offline state

(2) Repair table space data

(3) Restore the table space and be consistent

(4) The table space online

Rman> SQL ' alter tablespace Dave Offline ';

If the file does not exist, add the immediate parameter

Rman> SQL ' alter tablespace Dave offline Immediate ';

Rman>restore tablespace Dave;

Rman>recovery tablespace Dave;

Rman>sql ' alter tablespace Dave Online ';

5. Corrupted data files

If a media failure results in the loss of a table space data file (This can also refer to the recovery of table space corruption).

Recovery can take place in the open or Mount state of the database in just 4 steps

(1) Place the data file in the offline state

(2) Repair data file (specify data file number)

(3) Recover data files

(4) The data file online

Rman> SQL ' alter datafile 8 offline ';

Rman>restore DataFile 8;

Rman>recovery DataFile 8;

Rman>sql ' alter datafile 8 online ';

6. Incomplete recovery based on Point-in-time/scn/log sequences

Incomplete recovery based on Point-in-time/scn/log sequences, you can restore the database, tablespace, data files, and so on to restore the backup set to save the time of any one point in the/scn/log sequence, but be cautious, the operation must be done before the backup, with the conditions of the best to restore the first to the machine.

6.1 Based on the point of time

run{

Set until Time "To_date (12/29/10 23:00:00 ', ' mm/dd/yy hh24:mi:ss ')";

Restore database;

Recover database;

ALTER DATABASE open resetlogs;

}

Sql>startup Nomount;

Sql>alter session Set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

sql> recover database until time ' 2010-12-29 23:19:00 ';

Sql>alter DATABASE OPEN resetlogs;

ALTER session SET nls_date_format= ' Yyyy-mm-dd HH24:MI:SS ';

Sql>startup Mount;

Sql>restore database until Time "to_date (' 2010-12-29 23:19:00 ', ' yyyy-mm-dd HH24:MI:SS ')";

Sql>recover database until Time "to_date (' 2010-12-29 23:19:00 ', ' yyyy-mm-dd HH24:MI:SS ')";

Sql>alter database open resetlogs;

6.2 Based on SCN:

Sql>startup Mount;

Sql>restore database until SCN 10000;

Sql>recover database until SCN 10000;

Sql>alter database open resetlogs;

6.3 Based on log sequence

Sql>startup Mount;

Sql>restore database until SEQUENCE thread 1; 100 is the log sequence