【引自wzl_up的博客】由于某种误操作需要恢复数据库。当然我们都知道，假如自己之前有备份数据库可以用rman进行恢复，但是这样的情况就会恢复倒备份的时刻状态，而不是误操作的时候的状态，也可以叫做造成恢复过度了。于是我们可以用不完全恢复。
注意：不完全恢复是需要关闭数据库的，所以对于7*24的数据库不适用，而且在误操作之前是要有备份的。
下面我们来模拟数据误操作以后的不完全恢复。
1. 备份数据库
- RMAN> backup database;
- Starting backup at 10-APR-17
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- input datafile file number=00001 name=/u01/oracle/oradata/orcl/system01.dbf
- input datafile file number=00002 name=/u01/oracle/oradata/orcl/sysaux01.dbf
- input datafile file number=00005 name=/u01/oracle/oradata/orcl/user02.dbf
- input datafile file number=00006 name=/u01/oracle/oradata/orcl/tmpspace0327
- input datafile file number=00007 name=/u01/oracle/oradata/orcl/undotbs02.dbf
- input datafile file number=00008 name=/u01/oracle/oradata/orcl/example.dbf
- input datafile file number=00004 name=/u01/oracle/oradata/orcl/users01.dbf
- channel ORA_DISK_1: starting piece 1 at 10-APR-17
- channel ORA_DISK_1: finished piece 1 at 10-APR-17
- piece handle=/tmp/0us1cc3q_1_1 tag=TAG20170410T225138 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
- channel ORA_DISK_1: starting full datafile backup set
- channel ORA_DISK_1: specifying datafile(s) in backup set
- including current control file in backup set
- including current SPFILE in backup set
- channel ORA_DISK_1: starting piece 1 at 10-APR-17
- channel ORA_DISK_1: finished piece 1 at 10-APR-17
- piece handle=/tmp/0vs1cc6f_1_1 tag=TAG20170410T225138 comment=NONE
- channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
- Finished backup at 10-APR-17
2 .删数据(完全删除)
- SQL> drop table emp purge;
- Table dropped.
3. 数据挖掘
数据不完全恢复的时候我们要知道scn或者时间点。需要从日志挖掘中找。
- SQL> desc dbms_logmnr;
- PROCEDURE ADD_LOGFILE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- LOGFILENAME VARCHAR2 IN
- OPTIONS BINARY_INTEGER IN DEFAULT
- # 在dbms_logmnr这个包中有一个过程ADD_LOGFILE ，这个就是需要把挖掘的日志添加进去，再打开挖掘，可以看到参数只需要用一个logfilename ，日志文件名，所以我们需要找到删除数据的时候适用的日志文件是哪个
- SQL> select group#,members,status from v$log;
- GROUP# MEMBERS STATUS
- ---------- ---------- ----------------
- 1 1 CURRENT
- 2 1 INACTIVE
- 3 1 INACTIVE
- # 可以看出当前适用的日志文件是1
- SQL> select group#,member,status from v$logfile;
- GROUP#
- ----------
- MEMBER STATUS
- ---------------------------------------------------------------------- -------
- 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> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/orcl/redo01.log');
- PL/SQL procedure successfully completed.
启动日志挖掘
- SQL> desc dbms_logmnr;
- PROCEDURE START_LOGMNR
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- STARTSCN NUMBER IN DEFAULT
- ENDSCN NUMBER IN DEFAULT
- STARTTIME DATE IN DEFAULT
- ENDTIME DATE IN DEFAULT
- DICTFILENAME VARCHAR2 IN DEFAULT
- OPTIONS BINARY_INTEGER IN DEFAULT
- # 我们可以看到包dbms_logmnr里面有一个start_logmnr 过程，用这个过程启动挖掘
- SQL> execute dbms_logmnr.START_LOGMNR(options=>dbms_logmnr.dict_from_online_catalog);
- PL/SQL procedure successfully completed.
- # 启动挖掘成功
从挖掘中找到误操作数据当时的scn和时间，在v$logmnr_contents;
- SQL> select scn,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,SQL_REDO,SQL_UNDO from v$logmnr_contents where table_name = 'EMP';
- SCN TIMESTAMP SQL_REDO SQL_UNDO
- ---------- -------------------- -------------------- --------------------
- 2507301 2017-04-10 22:54:49 drop table emp purge
到此为止，我们已经挖掘成功，找到scn和时间点
注意：如果用时间点恢复的时候要减1秒
最后关闭数据挖掘
- SQL> exec dbms_logmnr.end_logmnr;
- PL/SQL procedure successfully completed.
4. 不完全恢复
- RMAN> shutdown immediate;
- using target database control file instead of recovery catalog
- database closed
- database dismounted
- Oracle instance shut down
- #先关闭数据库
- RMAN> startup mount;
- connected to target database (not started)
- Oracle instance started
- database mounted
- Total System Global Area 584568832 bytes
- Fixed Size 2230552 bytes
- Variable Size 444597992 bytes
- Database Buffers 130023424 bytes
- Redo Buffers 7716864 bytes
- #启动倒mount状态下
- RMAN> run{
- 2> set until scn 2507301;
- 3> restore database;
- 4> recover database;
- 5> alter database open resetlogs;
- 6> }
- executing command: SET until clause
- Starting restore at 10-APR-17
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=137 device type=DISK
- channel ORA_DISK_1: starting datafile backup set restore
- channel ORA_DISK_1: specifying datafile(s) to restore from backup set
- channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf
- channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/orcl/sysaux01.dbf
- channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf
- channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/oradata/orcl/user02.dbf
- channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/orcl/tmpspace0327
- channel ORA_DISK_1: restoring datafile 00007 to /u01/oracle/oradata/orcl/undotbs02.dbf
- channel ORA_DISK_1: restoring datafile 00008 to /u01/oracle/oradata/orcl/example.dbf
- channel ORA_DISK_1: reading from backup piece /tmp/0us1cc3q_1_1
- channel ORA_DISK_1: piece handle=/tmp/0us1cc3q_1_1 tag=TAG20170410T225138
- channel ORA_DISK_1: restored backup piece 1
- channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
- Finished restore at 10-APR-17
- Starting recover at 10-APR-17
- using channel ORA_DISK_1
- starting media recovery
- media recovery complete, elapsed time: 00:00:01
- Finished recover at 10-APR-17
- database opened
- # 用上面run{}脚本进行恢复，restore，recover,open resetlogs;
- # 成功
5. 验证
- SQL> desc emp;
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- EMPNO NOT NULL NUMBER(4)
- ENAME VARCHAR2(10)
- JOB VARCHAR2(9)
- MGR NUMBER(4)
- HIREDATE DATE
- SAL NUMBER(7,2)
- COMM NUMBER(7,2)
- DEPTNO NUMBER(2)
- SQL> select count(*) from emp;
- COUNT(*)
- ----------
- # 被删除的emp表恢复成功
6. 重新备份数据库
注意：恢复倒误操作时刻成功了，但是误操作之后做的事情就会丢失，所以要谨慎，而且需要关闭数据。
在oracle11g 中还可以在线克隆的方式避免关闭数据库：
方法大致步骤：
1) 在线克隆一个备库
2)在备库中进行不完全恢复
3) 把备库中部完全恢复成功的数据拷贝到主库
这样既可以不用关闭数据库，也解决了不完全恢复有可能造成的部分数据丢失。具体操作演示这里就不说了。
