Oracle10g Recovery Operation overview

1. the system automatically restores the instance when it is restarted. Judgment basis: the scn of the data file is inconsistent with the control file. Step: (1) Use onlineredolog, before the data

1. the system automatically restores the instance when it is restarted. Judgment basis: the scn of the data file is inconsistent with the control file. Step: (1) use the online redo log, before the data

1. instance recovery
The system automatically recovers when the instance is restarted.
Judgment basis: the scn of the data file is inconsistent with the control file.
Steps:
(1) use the online redo log to roll the data to the SCN that is consistent with the control file.
(2) Use the undo tablespace to roll back uncommitted transactions.
Result: the data in the database is retained to the last submitted content before the instance exception.

2. User error recovery

User errors include incorrect modification of user data and accidental deletion of data tables.
Restoration Technology: Flashback Query)
Flashback Drop and table space recycle bin (Tablespace's recycle bin)
Flashback Table)
LogMiner

2.1 flashback Query

Premise: the undo tablespace is sufficient to accommodate the data modified by the user over a period of time.
Time limit for undo tablespace to retain historical modification data (UNDO_RETETION initial variable)

Query the data of the table employees 15 minutes ago:

SELECT employee_id, last_name, email FROM hr. employees
As of timestamp (effecimestamp-interval '15' minute)
WHERE employee_id = 101;

Query the data of the table employees at the specified time:

SELECT employee_id, last_name, email FROM hr. employees
As of timestamp (
To_timestamp ('01-Sep-04 16:18:57. 84 ', 'dd-Mon-RR HH24: MI: SS. ff '))
WHERE employee_id = 101;

2.2 Flashback Drop and tablespace Recycle Bin

Each Oracle tablespace contains a Recycle bin, which stores deleted tables and table-related content (indexes, etc)
The space occupied by the deleted table is not recycled immediately, but can be seen in the DBA_FREE_SPACE view.

# Restore the deleted data table to the status before deletion (including table data ):

SQL> FLASHBACK TABLE order_items TO BEFORE DROP;

# Restore the deleted data table and rename it:
SQL> FLASHBACK TABLE order_items TO BEFORE DROP RENAME TO order_items_old;

If the same table is deleted and restored multiple times, you can query the view RECYCLEBIN if you want to restore it to a previous version.
Alternatively, run the show recyclebin command and use the table name.

Restrictions:
It can only be used for tablespaces that are not locally managed by the system.
Combined Bitmap indexes, integrity constraints, and materialized views cannot be stored in Recyclebin.
If you use Drop Index to delete an Index, the deleted Index is not saved (only the table and county Index are deleted ).

2.3 Flashback table

Allows you to restore one or more tables to the specified time in the history. You do not need to use time-consuming operations.
Flashback Table uses the Undo operation on related transactions to restore the Table and the undo tablespace.
(Flashback Drop directly recycles the space occupied by the recovery table)
You must enable the row movement function. The undo operation may change the recorded rowid.

# Enable Row Movement
SQL> ALTER TABLE hr. employees ENABLE ROW MOVEMENT;

# Use Flashback Table to restore the Table to the specified time
SQL> FLASHBACK TABLE hr. employees [,...]
To timestamp systimestamp-interval '15' minute;

2.4 LogMiner

Extract all DDL and DML activity statements from the Redo Log.
Use the V $ LOGMNR_CONTENTS view (the DBMS_LOGMNR.START_LOGMNR () process must be executed first)
The LogMiner tool itself cannot be used to restore the database.
Extract only the SQL statements used to restore the database from RedoLog.

3. control file recovery

If a control file error occurs, the Oracle instance stops running.
If it is not stopped, you must manually execute: SHUTDOWN ABORT

Copy the intact control file to the target address and modify the problematic
Control file path to new control file
You can also delete problematic control file entries in the initialization parameters.
When the control file is incorrect, modify the initialization parameter, which must be in NOMOUNT state.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> SHOW PARAMETER CONTROL_FILES;
Or
SQL> SELECT name, value FROM v $ spparameters
WHERE name = 'control _ files ';
SQL> ALTER SYSTEM SET CONTROL_FILES = '...', '...', '...' SCOPE = SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

4. redo log recovery

As long as there is a Redo Log file available in a group, the Oracle instance will not crash.
(That is, if all the redo log files in a redo log group are invalid, the Oracle example will crash)
Use the V $ LOGFILE view to query the status of the current redo log file.

If an error redo log file exists in a redo log group, follow these steps:

# Confirm which file has an error (in which group)
SQL> SELECT * FROM v $ logfile ORDER BY group #;

# Archive the redo logs of this group
SQL> ALTER SYSTEM ARCHIVE LOG GROUP ;

# Clear the problematic redo log group and recreate it
SQL> ALTER DATABASE CLEAR LOGFILE GROUP ;

5. Restore key system data files

Including SYSTEM tablespace and UNDO tablespace.

5.1 In NOARCHIVELOG Mode

It can only depend on whether full backup is available for the database. If yes, it can only be restored to full backup.

5.2 In ARCHIVELOG Mode

SQL> SHUTDOWN ABORT; # force stop
SQL> STARTUP MOUNT; # It can only be restored in the Mount state

In Perform Recovery of EM, select recover data file and corresponding SYSTEM tablespace data file.
You can specify the target path to be restored. The system will execute the RMAN script for recovery.

SQL> ALTER DATABASE OPEN; # OPEN the DATABASE after recovery

6. Restore non-system data files

6.1 In NOARCHIVELOG Mode

It can only depend on whether full backup is available for the database. If yes, it can only be restored to full backup.

6.2 In ARCHIVELOG Mode

Only the database objects related to the lost data files are affected.
You can also execute Perform Recovery in EM to restore data.

# Viewing data files
SQL> SELECT t. name, d. name FROM v $ tablespace t
JOIN v $ datafile d USING (ts #)
WHERE t. name = 'users ';

You can also run the RMAN command to restore the data files numbered 4 and 7:

$ Rman target/
RMAN> run {SQL 'alter database datafile 4 offline ';
SQL 'alter database datafile 7 offline ';
Restore datafile 4, 7;
Recover datafile 4, 7;
SQL 'alter database datafile 4 online ';
SQL 'alter database datafile 7 online ';}