Shulou(Shulou.com)06/02 Report--

As a powerful backup and recovery tool of Oracle, RMAN can help us restore the database to a specified point in time, which is a manifestation of Oracle incomplete recovery, through which we can recover the data we have lost. Here to retrieve incorrect TRUNCATE table data as an example to demonstrate the incomplete recovery function of RMAN.

1. Adjust the database to archive mode

Ora10g@secdb / home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Wed Oct 19 22:10:38 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Sys@ora10g > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 73

Current log sequence 77

Sys@ora10g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sys@ora10g > startup mount

ORACLE instance started.

Total System Global Area 536870912 bytes

Fixed Size 1220460 bytes

Variable Size 318767252 bytes

Database Buffers 209715200 bytes

Redo Buffers 7168000 bytes

Database mounted.

Sys@ora10g > alter database archivelog

Database altered.

Sys@ora10g > alter database open

Database altered.

two。 Use RMAN to back up the database

1) back up the database

Ora10g@secdb / home/oracle$ rman target /

Recovery Manager: Release 10.2.0.1.0-Production on Wed Oct 19 22:16:17 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to target database: ORA10G (DBID=4067278754)

RMAN > backup database

Starting backup at 20111019 22:16:35

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=214 devtype=DISK

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Input datafile fno=00010 name=/oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf

Input datafile fno=00003 name=/oracle/ora10gR2/oradata/ora10g/sysaux01.dbf

Input datafile fno=00001 name=/oracle/ora10gR2/oradata/ora10g/system01.dbf

Input datafile fno=00002 name=/oracle/ora10gR2/oradata/ora10g/undotbs01.dbf

Input datafile fno=00005 name=/home/oracle/tbs_sec_d_01.dbf

Input datafile fno=00004 name=/oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf

Input datafile fno=00008 name=/oracle/ora10gR2/oradata/ora10g/tbs01.dbf

Input datafile fno=00009 name=/oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf

Input datafile fno=00016 name=/u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf

Input datafile fno=00017 name=/u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf

Input datafile fno=00007 name=/oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf

Input datafile fno=00006 name=/oracle/ora10gR2/oradata/ora10g/users.dbf

Channel ORA_DISK_1: starting piece 1 at 20111019 22:16:36

Channel ORA_DISK_1: finished piece 1 at 20111019 22:17:41

Piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Channel ORA_DISK_1: starting full datafile backupset

Channel ORA_DISK_1: specifying datafile (s) in backupset

Input datafile fno=00011 name=/u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf

Input datafile fno=00012 name=/u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf

Input datafile fno=00013 name=/u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf

Input datafile fno=00014 name=/u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf

Input datafile fno=00015 name=/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf

Channel ORA_DISK_1: starting piece 1 at 20111019 22:17:42

Channel ORA_DISK_1: finished piece 1 at 20111019 22:17:45

Piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 20111019 22:17:45

Starting Control File and SPFILE Autobackup at 20111019 22:17:45

Piece handle=/db_backup/rman_backup/c-4067278754-20111019-00 comment=NONE

Finished Control File and SPFILE Autobackup at 20111019 22:17:48

2) View backup information

RMAN > list backup

List of Backup Sets

=

BS Key Type LV Size Device Type Elapsed Time Completion Time

48 Full 1.10G DISK 00:00:57 20111019 22:17:33

BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221636

Piece Name: / oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp

List of Datafiles in backup set 48

File LV Type Ckp SCN Ckp Time Name

1 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/system01.dbf

2 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/undotbs01.dbf

3 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/sysaux01.dbf

4 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf

5 Full 6494715 20111019 22:16:36 / home/oracle/tbs_sec_d_01.dbf

6 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/users.dbf

7 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf

8 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/tbs01.dbf

9 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf

10 Full 6494715 20111019 22:16:36 / oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf

16 Full 6494715 20111019 22:16:36 / u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf

17 Full 6494715 20111019 22:16:36 / u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

49 Full 1.02M DISK 00:00:01 20111019 22:17:43

BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221636

Piece Name: / oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp

List of Datafiles in backup set 49

File LV Type Ckp SCN Ckp Time Name

11 Full 6494738 20111019 22:17:42 / u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf

12 Full 6494738 20111019 22:17:42 / u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf

13 Full 6494738 20111019 22:17:42 / u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf

14 Full 6494738 20111019 22:17:42 / u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf

15 Full 6494738 20111019 22:17:42 / u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

50 Full 6.83M DISK 00:00:00 20111019 22:17:45

BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221745

Piece Name: / db_backup/rman_backup/c-4067278754-20111019-00

Control File Included: Ckp SCN: 6494743 Ckp time: 20111019 22:17:45

SPFILE Included: Modification time: 20111019 22:11:53

3. Simulating database failure-- incorrect TRUNCATE of table

1) sec users connected to the database

Ora10g@secdb / home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Wed Oct 19 22:20:57 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Sys@ora10g > conn sec/oracle_1

Connected.

2) View the number of rows in the T table

Sec@ora10g > select count (*) from t

COUNT (*)

-

one thousand

The T-table now contains 1000 pieces of data.

3) check the current time for later recovery using RMAN

Sec@ora10g > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

Sec@ora10g > select sysdate from dual

SYSDATE

-

2011-10-19 22:21:38

4) the erroneous deletion of T table is simulated here (TRUNCATE method of DDL type)

Sec@ora10g > truncate table t

Table truncated.

Sec@ora10g > select sysdate from dual

SYSDATE

-

2011-10-19 22:22:05

Sec@ora10g > select count (*) from t

COUNT (*)

-

0

4. Use RMAN to recover to the point in time before the failure occurred

The goal of the recovery point here is the 22:21:38 time of 2011-10-19 before the T-table is deleted.

[important reminder] before using RMAN to complete a time-based incomplete recovery, it is best to make a backup of the site. We only need to back up the control files and log files of the database. When the recovery does not meet our requirements, we can restore the control files and log files and restore them again.

1) start the database to mount state

Sec@ora10g > conn / as sysdba

Connected.

Sys@ora10g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sys@ora10g > startup mount

ORACLE instance started.

Total System Global Area 536870912 bytes

Fixed Size 1220460 bytes

Variable Size 318767252 bytes

Database Buffers 209715200 bytes

Redo Buffers 7168000 bytes

Database mounted.

2) use RMAN script to restore the database to a specified point in time

(1) the recovery script is as follows

Run {

Allocate channel c1 type disk

Allocate channel c2 type disk

Sql 'alter session set nls_date_format= "yyyy-mm-dd hh34:mi:ss''

Set until time = '2011-10-19 22 22 21 purl 38'

Restore database

Recover database

Alter database open resetlogs;}

(2) restore process records

RMAN > run {

2 > allocate channel C1 type disk

3 > allocate channel c2 type disk

4 > sql 'alter session set nls_date_format= "yyyy-mm-dd hh34:mi:ss"'

5 > set until time = '2011-10-19 22 purl 21 purl 38'

6 > restore database

7 > recover database

8 > alter database open resetlogs;}

Using target database control file instead of recovery catalog

Allocated channel: c1

Channel c1: sid=211 devtype=DISK

Allocated channel: c2

Channel c2: sid=210 devtype=DISK

Sql statement: alter session set nls_date_format= "yyyy-mm-dd hh34:mi:ss"

Executing command: SET until clause

Starting restore at 20111019 22:31:04

Channel c1: starting datafile backupset restore

Channel C1: specifying datafile (s) to restore from backup set

Restoring datafile 00001 to / oracle/ora10gR2/oradata/ora10g/system01.dbf

Restoring datafile 00002 to / oracle/ora10gR2/oradata/ora10g/undotbs01.dbf

Restoring datafile 00003 to / oracle/ora10gR2/oradata/ora10g/sysaux01.dbf

Restoring datafile 00004 to / oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf

Restoring datafile 00005 to / home/oracle/tbs_sec_d_01.dbf

Restoring datafile 00006 to / oracle/ora10gR2/oradata/ora10g/users.dbf

Restoring datafile 00007 to / oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf

Restoring datafile 00008 to / oracle/ora10gR2/oradata/ora10g/tbs01.dbf

Restoring datafile 00009 to / oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf

Restoring datafile 00010 to / oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf

Restoring datafile 00016 to / u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf

Restoring datafile 00017 to / u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf

Channel c1: reading from backup piece / oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp

Channel c2: starting datafile backupset restore

Channel c2: specifying datafile (s) to restore from backup set

Restoring datafile 00011 to / u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf

Restoring datafile 00012 to / u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf

Restoring datafile 00013 to / u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf

Restoring datafile 00014 to / u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf

Restoring datafile 00015 to / u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf

Channel c2: reading from backup piece / oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp

Channel c2: restored backup piece 1

Piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636

Channel c2: restore complete, elapsed time: 00:00:08

Channel c1: restored backup piece 1

Piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636

Channel c1: restore complete, elapsed time: 00:00:53

Finished restore at 20111019 22:31:58

Starting recover at 20111019 22:31:58

Starting media recovery

Media recovery complete, elapsed time: 00:00:01

Finished recover at 20111019 22:32:00

Database opened

Released channel: c1

Released channel: c2

5. Verify the recovery results

Ora10g@secdb / home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Wed Oct 19 22:35:37 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Sys@ora10g >

Sys@ora10g >

Sys@ora10g > conn sec/oracle_1

Connected.

Sec@ora10g > select count (*) from t

COUNT (*)

-

one thousand

So far, the failure that has caused the loss of T-table data due to TRUNCATE has been successfully recovered.

6. Summary

Oracle's RMAN tool is very powerful, and here is just a common use of incomplete recovery. RMAN is an indispensable helper when customizing the backup and recovery strategy of Oracle database.

Good luck.

Secooler

11.10.19

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.