【引自wzl_up的博客】由于某种误操作需要恢复数据库。当然我们都知道，假如自己之前有备份数据库可以用rman进行恢复，但是这样的情况就会恢复倒备份的时刻状态，而不是误操作的时候的状态，也可以叫做造成恢复过度了。于是我们可以用不完全恢复。

注意：不完全恢复是需要关闭数据库的，所以对于7*24的数据库不适用，而且在误操作之前是要有备份的。

下面我们来模拟数据误操作以后的不完全恢复。

1. 备份数据库

RMAN>backupdatabase;

Startingbackupat10-APR-17
usingchannelORA_DISK_1
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00001name=/u01/oracle/oradata/orcl/system01.dbf
inputdatafilefilenumber=00002name=/u01/oracle/oradata/orcl/sysaux01.dbf
inputdatafilefilenumber=00005name=/u01/oracle/oradata/orcl/user02.dbf
inputdatafilefilenumber=00006name=/u01/oracle/oradata/orcl/tmpspace0327
inputdatafilefilenumber=00007name=/u01/oracle/oradata/orcl/undotbs02.dbf
inputdatafilefilenumber=00008name=/u01/oracle/oradata/orcl/example.dbf
inputdatafilefilenumber=00004name=/u01/oracle/oradata/orcl/users01.dbf
channelORA_DISK_1:startingpiece1at10-APR-17
channelORA_DISK_1:finishedpiece1at10-APR-17
piecehandle=/tmp/0us1cc3q_1_1tag=TAG20170410T225138comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:01:25
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
includingcurrentcontrolfileinbackupset
includingcurrentSPFILEinbackupset
channelORA_DISK_1:startingpiece1at10-APR-17
channelORA_DISK_1:finishedpiece1at10-APR-17
piecehandle=/tmp/0vs1cc6f_1_1tag=TAG20170410T225138comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:02
Finishedbackupat10-APR-17

2 .删数据(完全删除)

SQL>droptableemppurge;

Tabledropped.

3. 数据挖掘

数据不完全恢复的时候我们要知道scn或者时间点。需要从日志挖掘中找。

SQL>descdbms_logmnr;
PROCEDUREADD_LOGFILE
ArgumentNameTypeIn/OutDefault?
-------------------------------------------------------------------
LOGFILENAMEVARCHAR2IN
OPTIONSBINARY_INTEGERINDEFAULT
#在dbms_logmnr这个包中有一个过程ADD_LOGFILE，这个就是需要把挖掘的日志添加进去，再打开挖掘，可以看到参数只需要用一个logfilename，日志文件名，所以我们需要找到删除数据的时候适用的日志文件是哪个
SQL>selectgroup#,members,statusfromv$log;

GROUP#MEMBERSSTATUS
------------------------------------
11CURRENT
21INACTIVE
31INACTIVE
#可以看出当前适用的日志文件是1
SQL>selectgroup#,member,statusfromv$logfile;

GROUP#
----------
MEMBERSTATUS
-----------------------------------------------------------------------------
3
/u01/oracle/oradata/orcl/redo03.log

2
/u01/oracle/oradata/orcl/redo02.log

1
/u01/oracle/oradata/orcl/redo01.log
#看以看出日志文件1的名字是‘/u01/oracle/oradata/orcl/redo01.log’

把日志文件添加进挖掘里面

SQL>execdbms_logmnr.add_logfile('/u01/oracle/oradata/orcl/redo01.log');

PL/SQLproceduresuccessfullycompleted.

启动日志挖掘

SQL>descdbms_logmnr;
PROCEDURESTART_LOGMNR
ArgumentNameTypeIn/OutDefault?
-------------------------------------------------------------------
STARTSCNNUMBERINDEFAULT
ENDSCNNUMBERINDEFAULT
STARTTIMEDATEINDEFAULT
ENDTIMEDATEINDEFAULT
DICTFILENAMEVARCHAR2INDEFAULT
OPTIONSBINARY_INTEGERINDEFAULT
#我们可以看到包dbms_logmnr里面有一个start_logmnr过程，用这个过程启动挖掘
SQL>executedbms_logmnr.START_LOGMNR(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQLproceduresuccessfullycompleted.
#启动挖掘成功

从挖掘中找到误操作数据当时的scn和时间，在v$logmnr_contents;

SQL>selectscn,to_char(TIMESTAMP,'yyyy-mm-ddhh24:mi:ss')TIMESTAMP,SQL_REDO,SQL_UNDOfromv$logmnr_contentswheretable_name='EMP';

SCNTIMESTAMPSQL_REDOSQL_UNDO
----------------------------------------------------------------------
25073012017-04-1022:54:49droptableemppurge

到此为止，我们已经挖掘成功，找到scn和时间点

注意：如果用时间点恢复的时候要减1秒

最后关闭数据挖掘

SQL>execdbms_logmnr.end_logmnr;

PL/SQLproceduresuccessfullycompleted.

4. 不完全恢复

RMAN>shutdownimmediate;

usingtargetdatabasecontrolfileinsteadofrecoverycatalog
databaseclosed
databasedismounted
Oracleinstanceshutdown
#先关闭数据库
RMAN>startupmount;

connectedtotargetdatabase(notstarted)
Oracleinstancestarted
databasemounted

TotalSystemGlobalArea584568832bytes

FixedSize2230552bytes
VariableSize444597992bytes
DatabaseBuffers130023424bytes
RedoBuffers7716864bytes
#启动倒mount状态下
RMAN>run{
2>setuntilscn2507301;
3>restoredatabase;
4>recoverdatabase;
5>alterdatabaseopenresetlogs;
6>}

executingcommand:SETuntilclause

Startingrestoreat10-APR-17
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=137devicetype=DISK

channelORA_DISK_1:startingdatafilebackupsetrestore
channelORA_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_DISK_1:restoringdatafile00001to/u01/oracle/oradata/orcl/system01.dbf
channelORA_DISK_1:restoringdatafile00002to/u01/oracle/oradata/orcl/sysaux01.dbf
channelORA_DISK_1:restoringdatafile00004to/u01/oracle/oradata/orcl/users01.dbf
channelORA_DISK_1:restoringdatafile00005to/u01/oracle/oradata/orcl/user02.dbf
channelORA_DISK_1:restoringdatafile00006to/u01/oracle/oradata/orcl/tmpspace0327
channelORA_DISK_1:restoringdatafile00007to/u01/oracle/oradata/orcl/undotbs02.dbf
channelORA_DISK_1:restoringdatafile00008to/u01/oracle/oradata/orcl/example.dbf
channelORA_DISK_1:readingfrombackuppiece/tmp/0us1cc3q_1_1
channelORA_DISK_1:piecehandle=/tmp/0us1cc3q_1_1tag=TAG20170410T225138
channelORA_DISK_1:restoredbackuppiece1
channelORA_DISK_1:restorecomplete,elapsedtime:00:01:15
Finishedrestoreat10-APR-17

Startingrecoverat10-APR-17
usingchannelORA_DISK_1

startingmediarecovery
mediarecoverycomplete,elapsedtime:00:00:01

Finishedrecoverat10-APR-17

databaseopened
#用上面run{}脚本进行恢复，restore，recover,openresetlogs;
#成功

5. 验证

SQL>descemp;
NameNull?Type
-----------------------------------------------------------------------------
EMPNONOTNULLNUMBER(4)
ENAMEVARCHAR2(10)
JOBVARCHAR2(9)
MGRNUMBER(4)
HIREDATEDATE
SALNUMBER(7,2)
COMMNUMBER(7,2)
DEPTNONUMBER(2)

SQL>selectcount(*)fromemp;

COUNT(*)
----------
#被删除的emp表恢复成功

6. 重新备份数据库

注意：恢复倒误操作时刻成功了，但是误操作之后做的事情就会丢失，所以要谨慎，而且需要关闭数据。

在oracle11g 中还可以在线克隆的方式避免关闭数据库：

方法大致步骤：

1) 在线克隆一个备库

2)在备库中进行不完全恢复

3) 把备库中部完全恢复成功的数据拷贝到主库

这样既可以不用关闭数据库，也解决了不完全恢复有可能造成的部分数据丢失。具体操作演示这里就不说了。