RMAN作为Oracle强大的备份恢复工具，可以协助我们恢复数据库到指定时间点，这便是Oracle不完全恢复的一种体现，通过这种方法可以找回我们曾经丢失的数据。这里以找回误TRUNCATE表数据为例给大家演示一下RMAN的不完全恢复功能。

[oracle@hear ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 25 09:28:42 2019

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

Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence 4Next log sequence to archive 6Current log sequence 6SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.

Total System Global Area 2137886720 bytesFixed Size 2254952 bytesVariable Size 1744832408 bytesDatabase Buffers 385875968 bytesRedo Buffers 4923392 bytesDatabase mounted.SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

备份数据库

[oracle@hear ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 25 09:50:48 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: HEARDB (DBID=3039837723)

RMAN> backup database;

查看备份信息:

RMAN> list backup;

List of Backup Sets===================

BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------20 Full 1.12G DISK 00:01:35 19-JUN-19 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20190619T233050 Piece Name: /home/oracle/backup/system_0ru4hc1a_1_1 List of Datafiles in backup set 20 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1393463 19-JUN-19 /home/oracle/app/oradata/heardb/system01.dbf 2 Full 1393463 19-JUN-19 /home/oracle/app/oradata/heardb/sysaux01.dbf 3 Full 1393463 19-JUN-19 /home/oracle/app/oradata/heardb/undotbs01.dbf 4 Full 1393463 19-JUN-19 /home/oracle/app/oradata/heardb/users01.dbf 5 Full 1393463 19-JUN-19 /home/oracle/app/oradata/heardb/example01.dbf

SQL> select count(*) from caodeng.r_user;

COUNT(*)---------- 5

SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL> select sysdate from dual;

SYSDATE----------2019-07-25

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE-------------------2019-07-25 09:36:12

删除表

SQL> truncate table caodeng.r_user;

Table truncated.

SQL> select count(*) from caodeng.r_user;

COUNT(*)---------- 0

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.

Total System Global Area 2137886720 bytesFixed Size 2254952 bytesVariable Size 1744832408 bytesDatabase Buffers 385875968 bytesRedo Buffers 4923392 bytesDatabase mounted.

2）使用RMAN脚本恢复数据库到指定时间点（1）恢复脚本如下:

run {allocate channel c1 type disk;allocate channel c2 type disk;sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time = '2019-07-25 09:36:12';restore database;recover database;alter database open resetlogs;}

记录恢复过程:

RMAN> run {allocate channel c1 type disk;allocate channel c2 type disk;sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time = '2019-07-25 09:36:12';restore database;recover database;alter database open resetlogs;}2> 3> 4> 5> 6> 7> 8>

using target database control file instead of recovery catalogallocated channel: c1channel c1: SID=1135 device type=DISK

allocated channel: c2channel c2: SID=2267 device type=DISK

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting restore at 25-JUL-19

channel c1: starting datafile backup set restorechannel c1: specifying datafile(s) to restore from backup setchannel c1: restoring datafile 00001 to /home/oracle/app/oradata/heardb/system01.dbfchannel c1: restoring datafile 00002 to /home/oracle/app/oradata/heardb/sysaux01.dbfchannel c1: restoring datafile 00003 to /home/oracle/app/oradata/heardb/undotbs01.dbfchannel c1: restoring datafile 00004 to /home/oracle/app/oradata/heardb/users01.dbfchannel c1: restoring datafile 00005 to /home/oracle/app/oradata/heardb/example01.dbfchannel c1: reading from backup piece /home/oracle/backups/db_20190725_30u7h90dchannel c1: piece handle=/home/oracle/backups/db_20190725_30u7h90d tag=TAG20190725T082757channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:01:36Finished restore at 25-JUL-19

Starting recover at 25-JUL-19

starting media recoverymedia recovery complete, elapsed time: 00:00:03

Finished recover at 25-JUL-19

database openedreleased channel: c1released channel: c2

[oracle@hear ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 25 09:44:29 2019

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

Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*(^H 2 SQL> select count(*) from caodeng.r_user;

COUNT(*)---------- 5

SQL>

这样删除的r_user表的数据丢失的已经被完全成功恢复