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

發(fā)布時(shí)間：2020-05-21 13:52:55 來(lái)源：網(wǎng)絡(luò) 閱讀：698 作者：君落塵欄目：關(guān)系型數(shù)據(jù)庫(kù)

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

1、查詢當(dāng)前日志組21:43:00 sys@TESTDB11>select * from v$log;

1 1 36 52428800 512 1 NO CURRENT 1349824

2、查詢?nèi)罩疚募?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和時(shí)間點(diǎn)

依次在sqlplus中執(zhí)行 NEW當(dāng)前的日志組多個(gè)加逗號(hào)隔開(kāi)，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

首先

開(kāi)啟database補(bǔ)充日志

alter database add supplemental log data;

當(dāng)前日志組

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

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

OPTIONS => DBMS_LOGMNR.NEW);

最后一次歸檔可寫(xiě)多個(gè)，倒序?qū)?/strong>

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';

開(kāi)啟行遷移

alter table t1 enable row movement;

基于scn

flashback table t1 to scn 863227;

基于時(shí)間點(diǎn)

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，用閃回查詢創(chuàng)建

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

注：可能出現(xiàn)的報(bào)錯(cuò)信息

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

時(shí)間點(diǎn)找的不對(duì)，應(yīng)該找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操作閃回?cái)?shù)據(jù)庫(kù) ***注：最好在備庫(kù)上做閃回?cái)?shù)據(jù)庫(kù)操作，再邏輯導(dǎo)入到主庫(kù)中

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.

設(shè)置參數(shù)，存放數(shù)據(jù)字典

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.

建立數(shù)據(jù)字典文件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;

執(zhí)行分析

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

查看分析結(jié)果

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');

關(guān)庫(kù)到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

向AI問(wèn)一下細(xì)節(jié)

推薦閱讀：

DML 、DDL、DCL區(qū)別以及定義

Oracle的基本操作-dml，ddl，dcl

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

ddl dml 挖掘scn

上一篇新聞：
?如何部署Golang程序到服務(wù)器？

下一篇新聞：
什么是php重載？

猜你喜歡

虛擬主機(jī)怎么上傳access

國(guó)內(nèi)高防虛擬主機(jī)租用怎么選擇

java測(cè)試類(lèi)如何調(diào)用

java抽象類(lèi)如何寫(xiě)單元測(cè)試

spring單元測(cè)試重復(fù)加載問(wèn)題怎么解決

Spring循環(huán)依賴問(wèn)題怎么解決

spring三級(jí)緩存有哪些特點(diǎn)

java如何編寫(xiě)測(cè)試類(lèi)

oracle兩個(gè)結(jié)果集取交集的方法是什么

oracle兩個(gè)表怎么取差集

最新資訊

Android Studio中控件的陰影效果如何實(shí)現(xiàn)

控件事件監(jiān)聽(tīng)器編寫(xiě)規(guī)范

控件單元測(cè)試編寫(xiě)與執(zhí)行

Android Studio控件API變更追蹤

控件狀態(tài)保存與恢復(fù)機(jī)制

控件觸摸反饋?zhàn)远x

控件動(dòng)態(tài)添加與移除技巧

控件無(wú)障礙設(shè)計(jì)在Android Studio中

控件權(quán)限與隱私保護(hù)考量

控件樣式繼承與覆蓋策略

相關(guān)推薦

DML、DDL、DCL語(yǔ)言介紹

DML和DDL的對(duì)比分析

DBMS中DDL和DML有哪些區(qū)別

如何使用sqlplus創(chuàng)建DDL和DML操作方法

基于flashback_scn的expdp導(dǎo)出方法是什么

expdp之query、flashback_scn參數(shù)的使用

Hive中DDL和DML的示例分析

DDL、DML和DCL的區(qū)別與理解是什么

使用sqlplus怎么創(chuàng)建DDL和DML

MySQL中DDL和DML的示例分析

相關(guān)標(biāo)簽

fiddler middleware middle fiddler抓包 paddle fiddle paddlepaddle ddl語(yǔ)句通過(guò)ddl語(yǔ)句生成er模型 paddleocr dml語(yǔ)句