Incomplete recovery of Oracle based on user management

From the perspective of the recovery type, Oracle data recovery can be divided into two types: full recovery and Incomplete recovery. Actually, I am familiar with Oracle
After the architecture, there will be a general concept for Oracle recovery. Because the peripheral components of Oracle are mainly composed of different files, different types of files have different
Therefore, as long as you understand its role, it is more conducive to understanding and understanding the backup and recovery of the oralce database. To put it bluntly, full recovery means restoring the database to the latest SCN.
The moment is lossless recovery. Incomplete recovery is lossy recovery, which is mostly used to recover user misoperations and archive log loss. This document describes Incomplete recovery based on user management.

 

I. Incomplete recovery features

1. Incomplete recovery

Incomplete recovery only restores data to a specific time point or a specific SCN, rather than the current time point. Incomplete recovery will affect the entire database.
Status. After Incomplete recovery is successful, you usually need to use the resetlogs option to open the database. When resetlogs is used, the SCN counter is not reset.
Log sequence ends and a new log serial number starts. In Oracle, it is called to generate a new incarnation. Oracle will reset the online redo day.
Therefore, we recommend that you repeat the full backup database after resetlogs.

2. Incomplete recovery

Media failure results in damage to some or all online redo logs
User error may cause data loss. For example, if a table is removed due to negligence, the user submits invalid data to the table.
The archived redo log is lost and cannot be completely restored (complete recovery)
The current control file is lost. You must use the backup control file to open the (open) database.

3. Incomplete recovery steps

Shut down the database and back up the database (to prevent recovery failure)
Start database to mount status
Restore all data files. You can also choose to restore the control file (note that you need to restore all data files, not just damaged files)
Restore the database to a certain time point, sequence, or system change number
Use the resetlogs keyword to open a database

4. Note

The precondition for Incomplete recovery is that the volume l database is in the Mount state, that is, the parameter file. The control file exists and is available.
We recommend that you back up the data before and after recovery to avoid unnecessary losses caused by recovery failures.
After Incomplete recovery is complete, open the (open) database using open resetlogs and generate a new incarnation
Check whether the database is restored to the correct time point, that is, whether the required data can be found. If the recovery time is incorrect, you need to reset the database incarnation for recovery again.

The example in this article is for ease of demonstration. fault data is not backed up before recovery, nor is it backed up after resetlog
Note: Oracle 10 Gb can be recovered without backing up the database after resetlogs. It can be recovered through resetlogs.

5. Types of incomplete media recovery

Time-based recovery restores data to the specified time point
User-controlled recovery (cancel-based recovery) stops recovery after the user submits cancel (this option is invalid when RMAN is used)
Change-based recovery restores data to the specified SCN
Recover data to the specified redo log sequence number (valid only when RMAN is used)

 

Ii. Demonstration of Incomplete recovery based on user management

-- 1. Until time recovery (recovery to the specified time point) sys @ sybo2sz> Conn/As sysdbaconnected. sys @ sybo2sz> archive log list; --> the current database is in archive mode. Database Log mode archive modeautomatic archival enabledarchive destination/u02/database/sybo2sz/archive/oldest online log sequence 0 next log sequence to archive 1 Current Log sequence 1sys @ sybo2sz> @ db_hot_bak --> hot backup of the database ho CP/u02/database/sybo2sz/oradata/syssybo2sz. DBF/u02/database/sybo2sz/backup/hotbakho CP/u02/database/sybo2sz/Undo/undotbssybo2sz. DBF/u02/database/sybo2sz/backup/hotbakho CP/u02/database/sybo2sz/oradata/sysauxsybo2sz. DBF/u02/database/sybo2sz/backup/hotbakho CP/u02/database/sybo2sz/Undo/undotbssybo2sz2. DBF/u02/database/sybo2sz/backup/hotbakho CP/u02/database/sybo2sz/oradata/logs/u02/database/sybo2sz/backup/hotbakho CP/u02/database/sybo2sz/ oradata/logs/u02/database/sybo2sz/backup/hotbakho CP/u02/database/sybo2sz/oradata/logs/u02/database/sybo2sz/backup/hotbakho CP/u02/database/ sybo2sz/oradata/sybo2sz_stock_l_tbl.dbf/u02/database/sybo2sz/backup/hotbaksys @ 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; --> truncatetable truncated.12: 41: 02 sys @ sybo2sz> insert into dept select 50, 'dev', 'sz 'from dual; --> adds two records to the table DEPT and submits the 12:41:14 sys @ sybo2sz> insert into dept select 60, 'hr ', 'gz 'from dual; 12:41:19 sys @ sybo2sz> commit; Commit complete.12: 41: 22 sys @ sybo2sz> alter system checkpoint; --> execute the Checkpoint Process to write the log system altered.12: 41: 31 sys @ sybo2sz> shutdown immediate; --> close database 12:42:25 sys @ sybo2sz> startup Mount; --> Start database to mount status Oracle instance started. total system global area 599785472 bytesfixed size 2074568 bytesvariable size 213911608 bytesdatabase buffers 377487360 bytesredo buffers 6311936 bytesdatabase mounted. --> The following describes how to restore the backup data. Because not all data files are in the same path, therefore, we performed CP 12:42:36 sys @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/Sybo * multiple times *. 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 /. 12:43:50 sys @ sybo2sz> recover database until time '2017-08-22: 12: 40: 55'; --> restore media recovery complete.12: 44: 07 sys @ sybo2sz> alter database open resetlogs; --> after the media Shard is restored successfully, open database altered.12: 44: 20 sys @ sybo2sz> select count (*) from EMP in resetlog mode; --> the EMP table is successfully restored to count (*) ---------- 1412: 44: 28 sys @ sybo2sz> select * from Dept; --> because the recovery time is before the new record is inserted, therefore, the new record Lost deptno dname loc ---------- ----------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations boston4 rows selected. -- 2. unitl SCN recovery (recovery based on system change numbers) sys @ sybo2sz> @ db_hot_bak --> Hot Standby Database SYS @ sybo2sz> select * from Dept; deptno dname loc ---------- ------------ ------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations boston4 rows selected. sys @ sybo2sz> insert into dept select 50, 'dev', 'sz 'from dual; --> adds 1 row created record for the table Dept. sys @ sybo2sz> commit; Commit complete. sys @ sybo2sz> select current_scn from V $ database; --> view the current SCN for future recovery using current_scn ----------- 471613sys @ sybo2sz> insert into dept select 60, 'hr ', 'gz 'from dual; --> Add a record to the table dept again to check whether sys @ sybo2sz> commit; Commit complete is lost after recovery. sys @ sybo2sz> Delete from EMP where deptno = 10; --> delete 3 rows deleted from deptno = 10 in the EMP table. sys @ sybo2sz> commit; Commit complete. sys @ sybo2sz> alter system checkpoint; --> execute the Checkpoint Process System altered. sys @ sybo2sz> shutdown immediate; sys @ sybo2sz> startup Mount; Oracle instance started. total system global area 599785472 bytesfixed size 2074568 bytesvariable size 218105912 bytesdatabase buffers 373293056 bytesredo buffers 6311936 bytesdatabase mounted. sys @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/Sybo *. DBF/u02/database/sybo2sz/oradata /. --> restore Database SYS @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/sys *. DBF/u02/database/sybo2sz/oradata /. sys @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/undotbs */u02/database/sybo2sz/Undo /. sys @ sybo2sz> recover database until change 471613 --> Restore database media recovery complete Based on SCN. sys @ sybo2sz> alter database open resetlogs; --> Use resetlog to open database altered. sys @ sybo2sz> select * from Dept; --> deptno dname loc ---------- -------------- ------------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations Boston 50 Dev sz5 rows selected is lost in operations after SCN. -- 3. Until cancel recovery (based on abandoned recovery) --> author: Robinson Cheng --> blog: http://blog.csdn.net/robinson_0612 sys @ sybo2sz> @ db_hot_bak --> Hot Standby Database SYS @ sybo2sz> select * from Dept; deptno dname loc ---------- ------------ ------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations Boston 50 Dev szsys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/archive --> current existing archive log total 348k-rw-r ----- 1 Oracle oinstall 340 K 2012-08-22 arch_792003491_1_1.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 arch_792003491_1_2.arc-rw-r ----- 1 Oracle oinstall 1.0 K 2012-08-22 arch_792003491_1_3.arcsys @ sybo2sz> alter Switch logfile; --> switch the log system altered. sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/archive --> we can see that the arch_792003491_1_4.arctotal 416k-rw-r ----- 1 Oracle oinstall 340 K 2012-08-22 arch_792003491_1_1.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 arch_792003491_1_2.arc-rw-r ----- 1 Oracle oinstall 1.0 K 2012-08-22 arch_792003491_1_3.arc-rw-r ----- 1 Oracle oinstall 66 K 2012-08-22 arch_792003491_00004.arcsys @ sybo2sz> insert into dept select 60, 'hr', 'shanghai' from dual; sys @ sybo2sz> insert into dept select 70, 'infa', 'hongkong' from dual; sys @ sybo2sz> commit; Commit complete. sys @ sybo2sz> alter system checkpoint; --> switch the log system altered. sys @ sybo2sz> alter system archive log current; System altered. sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/archivetotal 420k-rw-r ----- 1 Oracle oinstall 340 K 2012-08-22 arch_792003491_1_1.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 arch_792003491_1_2.arc-rw-r ----- 1 Oracle oinstall 1.0 k 2012-08-22 arch_792003491_1_3.arc-rw-r ----- 1 Oracle oinstall 66 K 2012-08-22 arch_792003491_1_4.arc-rw-r ----- 1 Oracle oinstall 2.5 k 2012-08-22 arch_792003491_1_5.arcsys @ sybo2sz> insert into dept select 80, 'market', 'beijing' from dual; sys @ sybo2sz> commit; Commit complete. sys @ sybo2sz> alter system archive log current; System altered. sys @ sybo2sz> Ho LS-hltr/u02/database/sybo2sz/archive --> the system has added several new archive logs Total Bytes k-rw-r ----- 1 Oracle oinstall 340 K 2012-08-22 arch_792003491_1_1.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 arch_792003491_1_2.arc-rw-r ----- 1 Oracle oinstall 1.0 K 2012-08-22 arch_792003491_1_3.arc-rw-r ----- 1 Oracle oinstall 66 K 2012-08-22 arch_792003491_1_4.arc-rw-r ----- 1 Oracle oinstall 2.5 k 2012-08-22 arch_792003491_1_5.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 logs @ sybo2sz> Ho strings/u02/database/sybo2sz/archive/logs | grep Hongkong --> the new record already exists in the archived log hongkongsys @ sybo2sz> Ho strings/u02/database/sybo2sz/ archive/logs | grep beijingbeijingsys @ sybo2sz> Ho RM/u02/database/sybo2sz/archive/logs --> simulate partial archive log loss sys @ sybo2sz> Ho LS-hltr/u02/Database /sybo2sz/archivetotal 420k-rw-r ----- 1 Oracle oinstall 340 K 2012-08-22 arch_792003491_1_1.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 arch_792003491_1_2.arc-rw-r ----- 1 Oracle oinstall 1.0 K 2012-08-22 arch_792003491_1_3.arc-rw-r ----- 1 Oracle oinstall 66 K 2012-08-22 arch_792003491_1_4.arc-rw-r ----- 1 Oracle oinstall 2.0 k 2012-08-22 arch_792003491_1_6.arcsys @ sybo2sz> shutdown immediate; sys @ sybo2sz> startup Mount; Oracle instance started. total system global area 599785472 bytesfixed size 2074568 bytesvariable size 243271736 bytesdatabase buffers 348127232 bytesredo buffers 6311936 bytesdatabase mounted. sys @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/Sybo *. DBF/u02/database/sybo2sz/oradata /. --> restore Database SYS @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/sys *. DBF/u02/database/sybo2sz/oradata /. sys @ sybo2sz> Ho CP/u02/database/sybo2sz/backup/hotbak/undotbs */u02/database/sybo2sz/Undo /. sys @ sybo2sz> recover database until cancel; --> recover database ORA-00279 Based on cancel: Change 494124 generated at 08/22/2012 17:02:30 needed for thread 1ora-00289: Suggestion: /u02/database/sybo2sz/archive/arch_792003491_1_4.arcORA-00280: Change 494124 for thread 1 is in sequence #4 specify log: {<RET> = suggested | filename | auto | cancel}/u02/database/sybo2sz/archive/arch_792003491_00004.arc --> restore to the archive log ORA-00279 with 4 tails: change 494189 generated at 08/22/2012 17:04:46 needed for thread 1ora-00289: Suggestion:/u02/database/sybo2sz/archive/arch_792003491_1_5.arcORA-00280: Change 494189 for thread 1 is in sequence #5ora-00278: log File '/u02/database/sybo2sz/archive/arch_792003491_00004.arc' no longer needed for this recoveryspecify log: {<RET> = suggested | filename | auto | cancel} cancel --> 5th log files are lost. Enter cancelmedia recovery canceled. sys @ sybo2sz> alter database open resetlogs; --> resetlogs open database altered. sys @ sybo2sz> select * from Dept; --> because archiving log 5 is lost, therefore, deptno dname loc ---------- -------------- ------------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations Boston 50 Dev sz5 rows selected will be lost for all subsequent operations.

Iii. More references

 

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion (when using plus archivelog)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)