Oracle基于數據挖掘的不完全恢復
作者：wzl_up
由于某種誤操作需要恢復數據庫。當然我們都知道，假如自己之前有備份數據庫可以用rman進行恢復，但是這樣的情況就會恢復倒備份的時刻狀態，而不是誤操作的時候的狀態，也可以叫做造成恢復過度了。于是我們可以用不完全恢復。
【引自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) 把備庫中部完全恢復成功的數據拷貝到主庫
這樣既可以不用關閉數據庫，也解決了不完全恢復有可能造成的部分數據丟失。具體操作演示這里就不說了。
責任編輯：龐桂玉
來源：
wzl_up的博客
