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 and the control file.

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, we recommend that you do not directly use the open resetlogs command to open the (OPEN) database in read/write mode. Instead, you should first open the database in read-only mode and check whether the database has been

Restore the database to the correct time point. If the recovery time is incorrect, it is relatively easy to re-execute the recovery operation without using the open resetlogs command.

If the restoration result is earlier than the specified time point, you only need to re-execute the restoration operation. If the restoration result exceeds the specified time point, you should restore the database and restore it 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 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 --> hot backup of the database

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; --> truncate table emp

Table truncated.

12:41:02 sys @ SYBO2SZ> insert into dept select 50, 'dev', 'sz 'from dual; --> Add two new records for the table dept and submit

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 logs.

System altered.

12:41:31 sys @ SYBO2SZ> shutdown immediate; --> close the database

12:42:25 sys @ SYBO2SZ> startup mount; --> Start the database to the mount status.

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 following command restores the backup data. Since not all data files are in the same path, the 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 /.

12:43:50 sys @ SYBO2SZ> recover database until time '2017-08-22: 12: 40: 55'; --> use until time to restore to the specified time point

Media recovery complete.

12:44:07 sys @ SYBO2SZ> alter database open resetlogs; --> after the media Shard is restored successfully, open the database using resetlog.

Database altered.

12:44:20 sys @ SYBO2SZ> select count (*) from emp; --> the emp table is successfully restored.

COUNT (*)

----------

14

12:44:28 sys @ SYBO2SZ> select * from dept; --> because the recovery time point is before the new record is inserted, the new record is lost.

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

4 rows selected.

-- 2. unitl scn recovery (recovery based on the system change number)

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

4 rows selected.

Sys @ SYBO2SZ> insert into dept select 50, 'dev', 'sz 'from dual; --> Add a record for the table dept

1 row created.

Sys @ SYBO2SZ> commit;

Commit complete.

Sys @ SYBO2SZ> select current_scn from v $ database; --> view the current SCN for subsequent recovery

CURRENT_SCN

-----------

471613

Sys @ SYBO2SZ> insert into dept select 60, 'hr', 'gz 'from dual; --> Add a record to the table dept again to check whether the record is lost after recovery.

Sys @ SYBO2SZ> commit;

Commit complete.

Sys @ SYBO2SZ> delete from emp where deptno = 10; --> delete the deptno = 10 record in the emp table.

3 rows deleted.

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 bytes

Fixed Size 2074568 bytes

Variable Size 218105912 bytes

Database Buffers 373293056 bytes

Redo Buffers 6311936 bytes

Database 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 based on SCN

Media recovery complete.

Sys @ SYBO2SZ> alter database open resetlogs; --> Use resetlog to open a database

Database altered.

Sys @ SYBO2SZ> select * from dept; --> operations after SCN are lost

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 DEV SZ

5 rows selected.

-- 3. until cancel recovery (based on abandoned recovery)

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 SZ

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive --> existing archived logs

Total 348 K

-Rw-r ----- 1 oracle oinstall 340 K 2012-08-22 arch_792003491_20171.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20172.arc

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-08-22 arch_792003491_4243.arc

Sys @ SYBO2SZ> alter system switch logfile; --> switch logs

System altered.

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive --> the added arch_792003491_00004.arc

Total 416 K

-Rw-r ----- 1 oracle oinstall 340 K 2012-08-22 arch_792003491_20171.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20172.arc

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-08-22 arch_792003491_4243.arc

-Rw-r ----- 1 oracle oinstall 66 K 2012-08-22 arch_792003491_00004.arc

Sys @ 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 logs

System altered.

Sys @ SYBO2SZ> alter system archive log current;

System altered.

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive

Total 420 K

-Rw-r ----- 1 oracle oinstall 340 K 2012-08-22 arch_792003491_20171.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20172.arc

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-08-22 arch_792003491_4243.arc

-Rw-r ----- 1 oracle oinstall 66 K 2012-08-22 arch_792003491_00004.arc

-Rw-r ----- 1 oracle oinstall 2.5 K 2012-08-22 arch_792003491_20175.arc

Sys @ 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 --> several new archive logs are added to the system.

Total 424 K

-Rw-r ----- 1 oracle oinstall 340 K 2012-08-22 arch_792003491_20171.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20172.arc

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-08-22 arch_792003491_4243.arc

-Rw-r ----- 1 oracle oinstall 66 K 2012-08-22 arch_792003491_00004.arc

-Rw-r ----- 1 oracle oinstall 2.5 K 2012-08-22 arch_792003491_20175.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20176.arc

Sys @ SYBO2SZ> ho strings/u02/database/SYBO2SZ/archive/arch_792003491_1_5.arc | grep HONGKONG --> the new record already exists in the archive log

HONGKONG

Sys @ SYBO2SZ> ho strings/u02/database/SYBO2SZ/archive/arch_792003491_1_6.arc | grep BEIJING

BEIJING

Sys @ SYBO2SZ> ho rm/u02/database/SYBO2SZ/archive/arch_792003491_4245.arc --> simulate the loss of some archived logs

Sys @ SYBO2SZ> ho ls-hltr/u02/database/SYBO2SZ/archive

Total 420 K

-Rw-r ----- 1 oracle oinstall 340 K 2012-08-22 arch_792003491_20171.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20172.arc

-Rw-r ----- 1 oracle oinstall 1.0 K 2012-08-22 arch_792003491_4243.arc

-Rw-r ----- 1 oracle oinstall 66 K 2012-08-22 arch_792003491_00004.arc

-Rw-r ----- 1 oracle oinstall 2.0 K 2012-08-22 arch_792003491_20176.arc

Sys @ SYBO2SZ> shutdown immediate;

Sys @ SYBO2SZ> startup mount;

ORACLE instance started.

Total System Global Area 599785472 bytes

Fixed Size 2074568 bytes

Variable Size 243271736 bytes

Database Buffers 348127232 bytes

Redo Buffers 6311936 bytes

Database 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; --> Restore database based on cancel

ORA-00279: change 494124 generated at 08/22/2012 17:02:30 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792003491_4244.arc

ORA-00280: change 494124 for thread 1 is in sequence #4

Specify log: {= suggested | filename | AUTO | CANCEL}

/U02/database/SYBO2SZ/archive/arch_792003491_00004.arc --> restore the archived logs with 4 tails

ORA-00279: change 494189 generated at 08/22/2012 17:04:46 needed for thread 1

ORA-00289: suggestion:/u02/database/SYBO2SZ/archive/arch_792003491_4245.arc

ORA-00280: change 494189 for thread 1 is in sequence #5

ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792003491_00004.arc' no longer needed for this recovery

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel --> 5th log files are lost. Enter cancel

Media recovery canceled.

Sys @ SYBO2SZ> alter database open resetlogs; --> resetlogs

Database altered.

Sys @ SYBO2SZ> select * from dept; --> data in all subsequent operations is lost due to the loss of archive log 5.

DEPTNO DNAME LOC

-------------------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 DEV SZ

5 rows selected.

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html