flashback之――挖掘SCN（DDL和DML操作示例）

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

1、查询当前日志组21:43:00 sys@TESTDB11>select * from v$log;

? ? ? ? ?1 ? ?1 ? ?36 ? 52428800 ? 512 ? ? 1 NO ?CURRENT ? ? 1349824

2、查询日志文件 21:42:44 sys@TESTDB11>select * from v$logfile;

GROUP# STATUS ?TYPE ? ?MEMBER ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? IS_

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

? ? ? ? ?3 ? ? ? ? ONLINE ?+DATA/testdb11/redo03.log ? ? ? ? ? ? ? ? ? ? ? ? ?NO

? ? ? ? ?2 ? ? ? ? ONLINE ?+DATA/testdb11/redo02.log ? ? ? ? ? ? ? ? ? ? ? ? ?NO

? ? ? ? ?1 ? ? ? ? ONLINE ?+DATA/testdb11/redo01.log ? ? ? ? ? ? ? ? ? ? ? ? ?NO

3、查询归档日志文件21:42:28 sys@TESTDB11>select name from v$archived_log;

/home/oracle/archivelog_bak/TestDB111_31_846843855.dbf

/home/oracle/archivelog_bak/TestDB111_32_846843855.dbf

/home/oracle/archivelog_bak/TestDB111_33_846843855.dbf

/home/oracle/archivelog_bak/TestDB111_34_846843855.dbf

/home/oracle/archivelog_bak/TestDB111_35_846843855.dbf

/home/oracle/archivelog_bak/TestDB111_36_846843855.dbf

DML操作 挖scn和时间点

依次在sqlplus中执行 NEW当前的日志组多个加逗号隔开，ADDFILE最后一次归档文件

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - ? ? ?

? ?LOGFILENAME => '+DATA/testdb11/redo01.log',- ? ? ? ? ? ??

? ?OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/home/oracle/archivelog_bak/TestDB111_36_846843855.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

? ?

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -

? ?DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

EXECUTE DBMS_LOGMNR.END_LOGMNR;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

col username for a10

col sql_redo for a50

select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='t1' order by scn;

1363373 2014-05-20 20:15:41 ??

俩种闪回

flashback table scott.t1 to scn 1363373;

flashback table scott.t1 to timestmp to_timestmp('2014-05-20 20:15:41','yyyy-mm-dd hh24:mi:ss');

例：DML 操作闪回表

SQL> create table t1 as select * from scott.dept;

Table created.

SQL> select * from t1;

? ? DEPTNO DNAME ? ? ? ? ?LOC

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

? ? ? ? 10 ACCOUNTING ? ? NEW YORK

? ? ? ? 20 RESEARCH ? ? ? DALLAS

? ? ? ? 30 SALES ? ? ? ? ?CHICAGO

? ? ? ? 40 OPERATIONS ? ? BOSTON

SQL> delete t1;

4 rows deleted.

SQL> insert into t1 select * from scott.dept where deptno=10;

1 row created.

SQL> select * from t1;

? ? DEPTNO DNAME ? ? ? ? ?LOC

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

? ? ? ? 10 ACCOUNTING ? ? NEW YORK

SQL> commit;

Commit complete.

SQL> select * from v$log;

? ? GROUP# ? ?THREAD# ?SEQUENCE# ? ? ?BYTES ? ?MEMBERS ARC STATUS ? ? ? ? ? FIRST_CHANGE# FIRST_TIME

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

? ? ? ? ?1 ? ? ? ? ?1 ? ? ? ? 86 ? 52428800 ? ? ? ? ?2 YES INACTIVE ? ? ? ? ? ? ? ?862829 2014-07-22 16:00:01

? ? ? ? ?2 ? ? ? ? ?1 ? ? ? ? 87 ? 52428800 ? ? ? ? ?2 YES INACTIVE ? ? ? ? ? ? ? ?862850 2014-07-22 16:00:03

? ? ? ? ?3 ? ? ? ? ?1 ? ? ? ? 88 ? 52428800 ? ? ? ? ?2 NO ?CURRENT ? ? ? ? ? ? ? ? 862976 2014-07-22 16:02:18

首先

开启database补充日志

alter database add supplemental log data;

当前日志组

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - ? ? ?

? ?LOGFILENAME => '/prod_log/prod/redo13.log',- ? ? ? ? ? ??

? ?OPTIONS => DBMS_LOGMNR.NEW);

最后一次归档可写多个，倒序写

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/arch/1_87_853529715.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

? ?

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/arch/1_86_853529715.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

?

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/arch/1_85_853529715.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

? ? ??

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -

? ?DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

EXECUTE DBMS_LOGMNR.END_LOGMNR;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

col username for a10

col sql_redo for a50

select username,sql_redo from v$logmnr_contents where seg_name='T1' order by scn;

USERNAME ? ? ? ? ?SCN TIMESTAMP ? ? ? ? ? SQL_REDO

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

SYS ? ? ? ? ? ?863211 2014-07-22 16:10:20 insert into "SYS"."T1"("DEPTNO","DNAME","LOC") val

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ues ('40','OPERATIONS','BOSTON');

SYS ? ? ? ? ? ?863228 2014-07-22 16:10:51 delete from "SYS"."T1" where "DEPTNO" = '10' and "

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? DNAME" = 'ACCOUNTING' and "LOC" = 'NEW YORK' and R

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? OWID = 'AAAM4GAABAAAO2iAAA';

SYS ? ? ? ? ? ?863228 2014-07-22 16:10:51 delete from "SYS"."T1" where "DEPTNO" = '20' and "

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? DNAME" = 'RESEARCH' and "LOC" = 'DALLAS' and ROWID

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?= 'AAAM4GAABAAAO2iAAB';

开启行迁移

alter table t1 enable row movement;

基于scn

flashback table t1 to scn 863227;

基于时间点

flashback table t1 to timestamp to_timestamp('2014-07-22 16:10:50','yyyy-mm-dd hh24:mi:ss');

闪回查询

select * from t1 as of timestamp to_timestamp('2014-07-22 16:10:50','yyyy-mm-dd hh24:mi:ss');

sys用户不能使用flashback，用闪回查询创建

create table t2 as select * from t1 as of timestamp to_timestamp('2014-07-22 16:10:50','yyyy-mm-dd hh24:mi:ss');

注：可能出现的报错信息

SQL> select * from t1 as of timestamp to_timestamp('2014-07-22 16:10:20','yyyy-mm-dd hh24:mi:ss');

select * from t1 as of timestamp to_timestamp('2014-07-22 16:10:20','yyyy-mm-dd hh24:mi:ss')

? ? ? ? ? ? ? *

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

时间点找的不对，应该找delete删除之前的几秒钟

SQL> flashback table t1 to timestamp to_timestamp('2014-07-22 16:10:30','yyyy-mm-dd hh24:mi:ss');

flashback table t1 to timestamp to_timestamp('2014-07-22 16:10:30','yyyy-mm-dd hh24:mi:ss')

? ? ? ? ? ? ? ? *

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

DDL操作 闪回数据库 ?***注：最好在备库上做闪回数据库操作，再逻辑导入到主库中

SQL> create table t2 as select * from dept;

Table created.

SQL> select * from t2;

? ? DEPTNO DNAME ? ? ? ? ?LOC

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

? ? ? ? 10 ACCOUNTING ? ? NEW YORK

? ? ? ? 20 RESEARCH ? ? ? DALLAS

? ? ? ? 30 SALES ? ? ? ? ?CHICAGO

? ? ? ? 40 OPERATIONS ? ? BOSTON

SQL> drop table t2 purge;

Table dropped.

设置参数，存放数据字典

mkdir /home/oracle/logmnr

SQL> show parameter utl

NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ?VALUE

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

create_stored_outlines ? ? ? ? ? ? ? string

utl_file_dir ? ? ? ? ? ? ? ? ? ? ? ? string

SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area ?570425344 bytes

Fixed Size ? ? ? ? ? ? ? ? ?2022480 bytes

Variable Size ? ? ? ? ? ? 209716144 bytes

Database Buffers ? ? ? ? ?352321536 bytes

Redo Buffers ? ? ? ? ? ? ? ?6365184 bytes

Database mounted.

Database opened.

建立数据字典文件dict.ora

execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);

添加日志分析

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - ? ? ?

? ?LOGFILENAME => '/arch/1_110_853529715.dbf',- ? ? ? ? ? ??

? ?OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/arch/1_109_853529715.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

? ?

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/arch/1_108_853529715.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

?

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

? ?LOGFILENAME => '/arch/1_107_853529715.dbf',-

? ?OPTIONS => DBMS_LOGMNR.ADDFILE);

execute dbms_logmnr.end_logmnr;

执行分析

execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

查看分析结果

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

col username for a10

col sql_redo for a50

select username,sql_redo from v$logmnr_contents where username='SCOTT' and lower(sql_redo) like '%table%';

SQL> select username,sql_redo from v$logmnr_contents where username='SCOTT' and lower(sql_redo) like '%table%';

USERNAME ? ? ? ? ?SCN TIMESTAMP ? ? ? ? ? SQL_REDO

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

SCOTT ? ? ? ? ?898096 2014-07-22 17:54:04 drop table t1 purge;

SCOTT ? ? ? ? ?898346 2014-07-22 17:55:27 create table t2 as select * from dept;

SCOTT ? ? ? ? ?899047 2014-07-22 17:56:24 drop table t2 purge;

flashback database to scn 898096;

flashback database to timestamp to_timestamp('2014-07-22 17:55:45','yyyy-mm-dd hh24:mi:ss');

关库到mount 闪回

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area ?570425344 bytes

Fixed Size ? ? ? ? ? ? ? ? ?2022480 bytes

Variable Size ? ? ? ? ? ? 218104752 bytes

Database Buffers ? ? ? ? ?343932928 bytes

Redo Buffers ? ? ? ? ? ? ? ?6365184 bytes

Database mounted.

SQL> flashback database to timestamp to_timestamp('2014-07-22 17:55:45','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

只读

SQL> alter database open read only;

Database altered.

SQL> select * from scott.t2;

? ? DEPTNO DNAME ? ? ? ? ?LOC

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

? ? ? ? 10 ACCOUNTING ? ? NEW YORK

? ? ? ? 20 RESEARCH ? ? ? DALLAS

? ? ? ? 30 SALES ? ? ? ? ?CHICAGO

? ? ? ? 40 OPERATIONS ? ? BOSTON

SQL> shutdown immediate;

SQL> startup mount;

ORACLE instance started.

Total System Global Area ?570425344 bytes

Fixed Size ? ? ? ? ? ? ? ? ?2022480 bytes

Variable Size ? ? ? ? ? ? 218104752 bytes

Database Buffers ? ? ? ? ?343932928 bytes

Redo Buffers ? ? ? ? ? ? ? ?6365184 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.t2;

? ? DEPTNO DNAME ? ? ? ? ?LOC

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

? ? ? ? 10 ACCOUNTING ? ? NEW YORK

? ? ? ? 20 RESEARCH ? ? ? DALLAS

? ? ? ? 30 SALES ? ? ? ? ?CHICAGO

? ? ? ? 40 OPERATIONS ? ? BOSTON