基于oracle的数据挖掘,oracle 基于数据挖掘的不完全恢复

由于某种误操作需要恢复数据库。当然我们都知道，假如自己之前有备份数据库可以用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 NameTypeIn/Out Default?

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

LOGFILENAMEVARCHAR2IN

OPTIONSBINARY_INTEGERIN 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 NameTypeIn/Out Default?

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

STARTSCNNUMBERIN DEFAULT

ENDSCN NUMBERIN DEFAULT

STARTTIMEDATEIN DEFAULT

ENDTIMEDATEIN DEFAULT

DICTFILENAMEVARCHAR2IN DEFAULT

OPTIONSBINARY_INTEGERIN 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 TIMESTAMPSQL_REDO SQL_UNDO

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

2507301 2017-04-10 22:54:49drop 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) 把备库中部完全恢复成功的数据拷贝到主库

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