DML误操作基于时间点的不完全恢复

来源：互联网发布：上网记录监控软件编辑：程序博客网时间：2024/06/07 07:39

不完全恢复（必须归档）的步骤：

1、先做全备份（最好是关库备份）。

2、通过logmnr找到误操作的时间点

3、转储所有数据文件。其他不用。

4、startup mount；

5、把数据文件恢复到错误发生的时间点。

6.开库resetlogs；

7.再全备份一次。

utl

1、首先创建一个ult目录。

[oracle@rtest oracle]$ mkdir utl

[oracle@rtest oracle]$ cd utl/

[oracle@rtest utl]$ pwd

/u01/app/oracle/utl

然后修改：

sys@TEST0910> show parameter utl

NAME TYPE VALUE

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

create_stored_outlines string

utl_file_dir string

sys@TEST0910> alter system set utl_file_dir='/u01/app/oracle/utl';

alter system set utl_file_dir='/u01/app/oracle/utl'

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

2、使路径生效，shutdown immediate后startup

sys@TEST0910> alter system set utl_file_dir='/u01/app/oracle/utl' scope=spfile;

System altered.

sys@TEST0910> show parameter utl

NAME TYPE VALUE

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

create_stored_outlines string

utl_file_dir string

sys@TEST0910> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TEST0910> startup

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes

Variable Size 587203928 bytes

Database Buffers 1895825408 bytes

Redo Buffers 20078592 bytes

Database mounted.

Database opened.

sys@TEST0910> show parameter utl

NAME TYPE VALUE

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

create_stored_outlines string

utl_file_dir string /u01/app/oracle/utl

3、破坏试验之前，备份

冷备份：sys@TEST0910> @/u01/app/oracle/bak/cold_bak

热备份：sys@TEST0910> @/u01/app/oracle/bak/hot_bak

破坏性试验：dml语句的不完全恢复

注意utl参数

过程：删数据--查日志--logmnr--关库--转储--mount--不完全恢复--开库resetlogs

4、log miner主要目的是找出dml或ddl操作的时间和位置：

alter database add supplemental log data这样才找得到。一定要激活。

log miner要用sys用户执行：

在使用log miner之前，先添加辅助日志：

sys@TEST0910> select count(*) from scott.test8;

COUNT(*)

----------

sys@TEST0910> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;即激活logmnr

Database altered.

5、误操作不带条件的删除

sys@TEST0910> delete from scott.test8;

14 rows deleted.

sys@TEST0910> commit;

Commit complete.

sys@TEST0910> select count(*) from scott.test8;

COUNT(*)

----------

1 row selected.

6、查日志和归档日志，方便redo日志和归档日志的抓取。

sys@TEST0910> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 4 52428800 512 1 YES INACTIVE 1879536 22-SEP-13 1884037 22-SEP-13

2 1 5 52428800 512 1 NO CURRENT 1884037 22-SEP-13 2.8147E+14

3 1 3 52428800 512 1 YES INACTIVE 1868444 22-SEP-13 1879536 22-SEP-13

sys@TEST0910> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

2 ONLINE /u01/app/oracle/oradata/test0910/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/test0910/redo01.log NO

3 ONLINE /u01/app/oracle/oradata/test0910/redo03.log NO

sys@TEST0910> select name from v$archived_log;

NAME

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

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_3_93xzwjf2_.arc

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc

logminer官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1009063

7、使用logminer抓取日志，获取误操作的时间点。

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

> LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo02.log', - 当前日志组

> OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

> LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc', - 最新归档日志

> OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.START_LOGMNR( -

> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

设置时间格式：

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

Session altered.

8、查询logminer内容

sys@TEST0910> select username,scn,timestamp,sql_redo from v$logmnr_contents

2 where seg_name='TEST8' order by scn;

USERNAME SCN TIMESTAMP SQL_REDO

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

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7369'

and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MG

R" = '7902' and "HIREDATE" = TO_DATE('1980-12-17 0

0:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '80

0' and "COMM" IS NULL and "DEPTNO" = '20' and ROWI

D = 'AAAS9mAAEAAAAJbAAA';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7499'

and "ENAME" = 'ALLEN' and "JOB" = 'SALESMAN' and

"MGR" = '7698' and "HIREDATE" = TO_DATE('1981-02-2

0 00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" =

'1600' and "COMM" = '300' and "DEPTNO" = '30' and

ROWID = 'AAAS9mAAEAAAAJbAAB';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7521'

and "ENAME" = 'WARD' and "JOB" = 'SALESMAN' and "

MGR" = '7698' and "HIREDATE" = TO_DATE('1981-02-22

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '

1250' and "COMM" = '500' and "DEPTNO" = '30' and R

OWID = 'AAAS9mAAEAAAAJbAAC';

SYS 1885628 2013-09-22 12:37;24 delete from "SCOTT"."TEST8" where "EMPNO" = '7566'

and "ENAME" = 'JONES' and "JOB" = 'MANAGER' and "

MGR" = '7839' and "HIREDATE" = TO_DATE('1981-04-02

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '

2975' and "COMM" IS NULL and "DEPTNO" = '20' and R

OWID = 'AAAS9mAAEAAAAJbAAD';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7654'

and "ENAME" = 'MARTIN' and "JOB" = 'SALESMAN' and

"MGR" = '7698' and "HIREDATE" = TO_DATE('1981-09-

28 00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" =

'1250' and "COMM" = '1400' and "DEPTNO" = '30' an

d ROWID = 'AAAS9mAAEAAAAJbAAE';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7698'

and "ENAME" = 'BLAKE' and "JOB" = 'MANAGER' and "

MGR" = '7839' and "HIREDATE" = TO_DATE('1981-05-01

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '

2850' and "COMM" IS NULL and "DEPTNO" = '30' and R

OWID = 'AAAS9mAAEAAAAJbAAF';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7934'

and "ENAME" = 'MILLER' and "JOB" = 'CLERK' and "M

GR" = '7782' and "HIREDATE" = TO_DATE('1982-01-23

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '1

300' and "COMM" IS NULL and "DEPTNO" = '10' and RO

WID = 'AAAS9mAAEAAAAJbAAN';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7788'

and "ENAME" = 'SCOTT' and "JOB" = 'ANALYST' and "

MGR" = '7566' and "HIREDATE" = TO_DATE('1987-04-19

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '

3000' and "COMM" IS NULL and "DEPTNO" = '20' and R

OWID = 'AAAS9mAAEAAAAJbAAH';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7839'

and "ENAME" = 'KING' and "JOB" = 'PRESIDENT' and

"MGR" IS NULL and "HIREDATE" = TO_DATE('1981-11-17

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '

5000' and "COMM" IS NULL and "DEPTNO" = '10' and R

OWID = 'AAAS9mAAEAAAAJbAAI';

SYS 1885628 2013-09-22 12:37;24 delete from "SCOTT"."TEST8" where "EMPNO" = '7844'

and "ENAME" = 'TURNER' and "JOB" = 'SALESMAN' and

"MGR" = '7698' and "HIREDATE" = TO_DATE('1981-09-

08 00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" =

'1500' and "COMM" = '0' and "DEPTNO" = '30' and R

OWID = 'AAAS9mAAEAAAAJbAAJ';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7876'

and "ENAME" = 'ADAMS' and "JOB" = 'CLERK' and "MG

R" = '7788' and "HIREDATE" = TO_DATE('1987-05-23 0

0:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '11

00' and "COMM" IS NULL and "DEPTNO" = '20' and ROW

ID = 'AAAS9mAAEAAAAJbAAK';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7900'

and "ENAME" = 'JAMES' and "JOB" = 'CLERK' and "MG

R" = '7698' and "HIREDATE" = TO_DATE('1981-12-03 0

0:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '95

0' and "COMM" IS NULL and "DEPTNO" = '30' and ROWI

D = 'AAAS9mAAEAAAAJbAAL';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7902'

and "ENAME" = 'FORD' and "JOB" = 'ANALYST' and "M

GR" = '7566' and "HIREDATE" = TO_DATE('1981-12-03

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '3

000' and "COMM" IS NULL and "DEPTNO" = '20' and RO

WID = 'AAAS9mAAEAAAAJbAAM';

SYS 1885628 2013-09-22 12:37:24 delete from "SCOTT"."TEST8" where "EMPNO" = '7782'

and "ENAME" = 'CLARK' and "JOB" = 'MANAGER' and "

MGR" = '7839' and "HIREDATE" = TO_DATE('1981-06-09

00:00;00', 'yyyy-mm-dd hh24:mi;ss') and "SAL" = '

2450' and "COMM" IS NULL and "DEPTNO" = '10' and R

OWID = 'AAAS9mAAEAAAAJbAAG';

14 rows selected.

记住这个时间点：

2013-09-22 12:37:24

日志找到，结束查找

sys@TEST0910> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

9、模拟断电

通过cp冷备转储数据文件到原来位置

sys@TEST0910> shutdown abort;

ORACLE instance shut down.

10.冷备转储到原来的位置

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/users01.dbf /u01/app/oracle/oradata/test0910/disk1/users01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/example01.dbf /u01/app/oracle/oradata/test0910/example01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/lxtb01.dbf /u01/app/oracle/oradata/test0910/lxtb01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/sysaux01.dbf /u01/app/oracle/oradata/test0910/sysaux01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/system01.dbf /u01/app/oracle/oradata/test0910/system01.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/testtb.dbf /u01/app/oracle/oradata/test0910/testtb.dbf

[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/undotbs01.dbf /u01/app/oracle/oradata/test0910/undotbs01.dbf

11、起库。到mount状态

sys@TEST0910> startup mount;

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes

Variable Size 587203928 bytes

Database Buffers 1895825408 bytes

Redo Buffers 20078592 bytes

Database mounted.

12、基于时间点的误操作恢复，并用resetlogs打开数据库。查询数据，丢失一部分数据。

sys@TEST0910> recover database until time '2013-09-22 12:37:24 ';

Media recovery complete.

sys@TEST0910>

sys@TEST0910> alter database open resetlogs;

Database altered.

sys@TEST0910> select count(*) from scott.test8;

COUNT(*)

----------