中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

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

發布時間：2020-05-21 13:52:55 來源：網絡 閱讀：749 作者：君落塵 欄目：關系型數據庫



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


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

















向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
沐川县| 汉寿县| 竹北市| 营口市| 依兰县| 策勒县| 瑞金市| 清苑县| 大关县| 仙居县| 萨迦县| 宝清县| 喜德县| 彰化市| 安国市| 乳山市| 谷城县| 托里县| 怀宁县| 梧州市| 红河县| 马公市| 富宁县| 拉孜县| 马山县| 望江县| 鹤岗市| 绥德县| 类乌齐县| 南雄市| 平度市| 白城市| 凤城市| 肃南| 怀柔区| 湄潭县| 三门县| 宕昌县| 泰兴市| 金沙县| 家居|