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 hh34: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 hh34: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 hh34: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;

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 hh34:mi:ss');

闪回查询

select * from t1 as of timestamp to_timestamp('2014-07-22 16:10:50','yyyy-mm-dd hh34: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 hh34:mi:ss');

注：可能出现的报错信息

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

select * from t1 as of timestamp to_timestamp('2014-07-22 16:10:20','yyyy-mm-dd hh34: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 hh34:mi:ss');

flashback table t1 to timestamp to_timestamp('2014-07-22 16:10:30','yyyy-mm-dd hh34: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 hh34:mi:ss';

col username for a10

col sql_redo for a50

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

SQL> select username,scn,timestamp,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 hh34: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 hh34: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