首页 > 资讯 > 数据库 >flashback之——挖掘SCN（DDL和DML操作示例）

935

分享到

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

2024-04-02 19:04:59 935人浏览薄情痞子

摘要

-------------------------------------------------------------------------------------------1、查询当前日志组21:

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

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

您可能感兴趣的文档:

--结束END--

本文标题: flashback之——挖掘SCN（DDL和DML操作示例）

本文链接: https://lsjlt.com/news/34907.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

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

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

procedure和functon后ddl操作的示例分析

JS之页面加载事件、数组操作、DOM节点操作、循环和分支的示例分析

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式