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