flashback database实验（使用lgmnr找出还原时间点）

本文链接：https://blog.csdn.net/rlhua/article/details/11935451

开启闪回： http://blog.csdn.net/rlhua/article/details/11934831

过程：激活日志--删数据--查日志--logmnr--关库--不用转储--mount--flashback数据库（时间减一秒）--read only

开库看正确与否--关库--mount--开库resetlogs

DDL语句的恢复：类似不完全恢复，可以恢复到某个时间点，依赖flashback log.

logmnr依赖的是redo和归档。

flashback database一般用于ddl操作，

dml用flashback query或flashback table。因为他们读undo块

ddl语句不生成undo块

闪回数据库有限制：比如解决不了media recovery等（硬盘坏了等）。还是备份最重要。

1、使用logmnr来记录时间点，首先必须设置有一下参数

show parameter utl; logmnr需要的路径

sys@TEST0910> show parameter utl

NAME TYPE VALUE

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

create_stored_outlines string

utl_file_dir string /u01/app/oracle/utl

使用 flashback database首先设置两个参数：

db_recovery_file_dest string /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size big integer 4122M

sys@TEST0910> show parameter recovery

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area

db_recovery_file_dest_size big integer 4122M

而且需要alter database flashback on

ys@TEST0910> select name,flashback_on from v$database;

NAME FLASHBACK_ON

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

TEST0910 YES

1 row selected.

2、查询原数据

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

COUNT(*)

----------

1 row selected.

3.激活logmnr，注意，必须有此步骤，不然logmnr出不来数据

sys@TEST0910> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

4、误操作DDL操作

sys@TEST0910> truncate table scott.test12;

Table truncated.

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

COUNT(*)

----------

1 row selected.

5、查询当前日志组和归档日志

sys@TEST0910> select * from v$log;

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

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

1 1 7 52428800 512 1 YES INACTIVE 1977009 2013-09-23 15:04:56 1981677 2013-09-23 15:05:11

2 1 8 52428800 512 1 YES ACTIVE 1981677 2013-09-23 15:05:11 1985831 2013-09-23 15:05:41

3 1 9 52428800 512 1 NO CURRENT 1985831 2013-09-23 15:05:41 2.8147E+14

3 rows selected.

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

3 rows selected.

sys@TEST0910> select name from v$archived_log;

NAME

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

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc

61 rows selected.

6、执行logmnr

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -

'/u01/app/oracle/utl', -

DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo03.log', -

OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc', -

OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc', -

OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc', -

OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR( -

DICTFILENAME =>'/u01/app/oracle/utl/dictionary.ora',OPTIONS => -

DBMS_LOGMNR.DDL_DICT_TRACKING);

EXECUTE DBMS_LOGMNR.END_LOGMNR;

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

以下为执行步骤

sys@TEST0910> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -

> '/u01/app/oracle/utl', -

> DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

LogMnr Dictionary Procedure started

LogMnr Dictionary File Opened

Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

> LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo03.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_23/o1_mf_1_8_941485th_.arc', -

> OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

> LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc', -

> OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

> LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc', -

> OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

sys@TEST0910> EXECUTE DBMS_LOGMNR.START_LOGMNR( -

> DICTFILENAME =>'/u01/app/oracle/utl/dictionary.ora',OPTIONS => -

> DBMS_LOGMNR.DDL_DICT_TRACKING);

PL/SQL procedure successfully completed.

7、设置session时间格式

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

Session altered.

8、查询logmnr分析出的数据

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

2 where seg_name='TEST12';

USERNAME SCN TIMESTAMP SQL_REDO

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

SYS 1989009 2013-09-23 15:09:53 truncate table scott.test12;

sys@TEST0910> EXECUTE DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

8、mount状态下闪回

sys@TEST0910> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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.

sys@TEST0910> flashback database to timestamp to_timestamp('2013-09-23 15:09:52','yyyy-mm-dd hh24:mi:ss');

此处注意，时间在logmnr查询出的基础上减1秒

Flashback complete.

9、让数据库置于read only，检查数据是否正确恢复

sys@TEST0910> alter database open read only;

Database altered.

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

COUNT(*)

----------

1 row selected.

10、重新启动数据库，使用resetlogs打开数据库

sys@TEST0910> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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.

sys@TEST0910> alter database open

2 ;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

sys@TEST0910> alter database open resetlogs;

Database altered.