Oracle Incomplete recovery based on user management

Oracle data recovery from the recovery type, cast aside the specific files, the total can be divided into two major types of recovery, one is full recovery, one is not full recovery. In fact, once you are familiar with the Oracle architecture, there is a general concept for Oracle recovery. Because the external part of Oracle consists mainly of different files, each different type of file has different functions, so as long as understand its role, more conducive to understanding and mastering the Oralce database backup and recovery. Back to the point, full recovery is to restore the database to the latest SCN, the moment before the failure, is a lossless recovery. Incomplete recovery is a lossy recovery, which can be used to restore user errors, archive log loss, and more. This article mainly describes the incomplete recovery based on user management.

First, incomplete recovery characteristics

1. Incomplete recovery

Incomplete recovery is simply the recovery of data to a specific point in time or to a specific SCN, rather than the current point in time. Incomplete recovery affects the entire database and needs to be carried out in the Mount state. After an incomplete recovery is successful, you typically need to use the Resetlogs option to open the database. When the resetlogs is used, the SCN counter is not reset, the original log sequence number log sequence will end, and the new log sequence number starts anew. Called in Oracle to produce a new incarnation. Oracle also resets the online redo log content, so it is recommended that the database be resetlogs after the end of the system.

2, incomplete recovery of the situation

Media failure (media failure) causes partial or full online redo log (online redo log) to be corrupted

User error caused data loss, for example, the user inadvertently removed the table, submitted invalid data to the table

Unable to complete recovery due to the loss of the archive redo log (archived redo log) (complete recovery)

The current control file is missing and you must open the (open) database using the backed up control file

3. Steps for incomplete recovery

Close the database and back up the database (to prevent recovery from failing)

Start database to Mount state

Restores all data files while optionally restoring control files (Note that you need to restore all data files, not just corrupted files)

Restore the database to a point in time, sequence, or system change number

Open a database using the Resetlogs keyword

4. Note

The prerequisite for incomplete recovery is that the ORACL database is up to the Mount state, that is, the parameter file, the control file

It is recommended to make a backup before and after the recovery before doing a full recovery to avoid unnecessary loss due to recovery failure

When incomplete recovery is complete, it is recommended that you do not use the Open resetlogs command to open the database directly in read/write mode, but first open the database in read-only mode and check that the database has been restored to the correct point in time. If the point-in-time recovery is incorrect, it is relatively straightforward to redo the restore operation without using the Open resetlogs command.

For recovery results that are earlier than the specified point in time, you only need to redo the recovery operation. If the recovery results exceed the specified point in time, you should restore the database again and restore it again.

The examples in this article are easy to demonstrate and do not back up the failed data prior to recovery, nor do they back up after Resetlog.

Note: Oracle 10g has been able to not back up the database after Resetlogs and will be able to traverse resetlogs when resuming

5. Several types of incomplete media recovery

Time-based recovery (time-based recovery) restores data to a specified point in time

User-controlled recovery (cancel-based recovery) Stop recovery after the user submits Cancel (this option is not valid when using Rman)

SCN-based recovery (change-based recovery) restores data to the specified SCN

Restores the data to the specified redo log ordinal (valid only when Rman is used) by redo log ordinal recovery (log sequence recovery)

Ii. demonstration of incomplete recovery based on user management

--1, until time restore (revert to a specified point in times)

Sys@sybo2sz> Conn/as SYSDBA

Connected.

sys@sybo2sz> archive log list; --> the current database is in archive mode

Database Log Mode Archive mode

Automatic Archival Enabled

Archive destination/u02/database/sybo2sz/archive/

Oldest online log sequence 0

Next log sequence to archive 1

Current log sequence 1

sys@sybo2sz> @db_hot_bak--> The database for hot backup

Ho Cp/u02/database/sybo2sz/oradata/syssybo2sz.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/undo/undotbssybo2sz.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/oradata/sysauxsybo2sz.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/undo/undotbssybo2sz2.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/oradata/sybo2sz_system_tbl.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/oradata/sybo2sz_account_tbl.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/oradata/sybo2sz_stock_tbl.dbf/u02/database/sybo2sz/backup/hotbak

Ho Cp/u02/database/sybo2sz/oradata/sybo2sz_stock_l_tbl.dbf/u02/database/sybo2sz/backup/hotbak

Sys@sybo2sz> set time on;

12:40:07 sys@sybo2sz> CREATE TABLE Dept as select * from Scott.dept;

12:40:31 sys@sybo2sz> CREATE TABLE emp as SELECT * from Scott.emp;

12:40:41 sys@sybo2sz>

12:40:55 sys@sybo2sz> truncate TABLE emp; --> the truncate of the table emp

Table truncated.

12:41:02 sys@sybo2sz> INSERT INTO dept Select, ' DEV ', ' SZ ' from dual; --> add two records to table dept and submit

12:41:14 sys@sybo2sz> INSERT INTO dept Select, ' HR ', ' GZ ' from dual;

12:41:19 sys@sybo2sz> commit;

Commit complete.

12:41:22 sys@sybo2sz> alter system checkpoint; --> performs a checkpoint process to write to the log

System altered.

12:41:31 sys@sybo2sz> shutdown Immediate; --> shut down the database

12:42:25 sys@sybo2sz> startup Mount; --> start Database to Mount state

ORACLE instance started.

Total System Global area 599785472 bytes

Fixed Size 2074568 bytes

Variable Size 213911608 bytes

Database buffers 377487360 bytes

Redo buffers 6311936 bytes

Database mounted. --> The backed-up data is restored below, because not all data files are in the same path, so multiple CP

12:42:36 sys@sybo2sz> ho cp/u02/database/sybo2sz/backup/hotbak/sybo*.dbf/u02/database/sybo2sz/oradata/.

12:42:57 sys@sybo2sz> ho cp/u02/database/sybo2sz/backup/hotbak/sys*.dbf/u02/database/sybo2sz/oradata/.

12:43:24 sys@sybo2sz> ho cp/u02/database/sybo2sz/backup/hotbak/undotbs*/u02/database/sybo2sz/undo/.