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

发布时间：2020-12-15 17:55:07 所属栏目：百科来源：网络整理

导读：开启闪回： http://www.voidcn.com/article/p-okbbpchw-bbu.html ? 过程：激活日志--删数据--查日志--logmnr--关库--不用转储--mount--flashback数据库（时间减一秒）--read only 开库看正确与否--关库--mount--开库resetlogs DDL语句的恢复：类似不完全恢复

开启闪回： http://www.voidcn.com/article/p-okbbpchw-bbu.html

过程：激活日志--删数据--查日志--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(*)

----------

??????? 14

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(*)

----------

???????? 0

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(*)

----------

??????? 14

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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!