[20160805]logminer使用问题2.txt

--前一阵子看itpub有人想通过logminer来抽取归档同步数据库，实际上这个非常不科学，存在一定的风险，我觉得很多情况下会漏掉一些sql

--语句，通过例子来说明。

--首先说明logminer用来补救一些异常操作还是可行的。

1.测试环境说明：

SCOTT@book> @ &r/ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter database add supplemental log data;

Database altered.

SCOTT@book> alter database FORCE LOGGING ;

SCOTT@book> select SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database ;

SUPPLEME FOR

-------- ---

YES YES

SCOTT@book> create table emp1 as select * from emp;

Table created.

--//安全期间，我还重启数据库。

2.测试1：

SCOTT@book> alter system archive log current ;

System altered.

SCOTT@book> @ &r/logfile

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME GROUP# STATUS TYPE MEMBER IS_

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

1 1 418 52428800 512 1 NO CURRENT 13245390164 2016-08-05 10:49:19 2.814750E+14 1 ONLINE /mnt/ramdisk/book/redo01.log NO

2 1 416 52428800 512 1 YES INACTIVE 13245389809 2016-08-05 10:36:39 13245389934 2016-08-05 10:39:39 2 ONLINE /mnt/ramdisk/book/redo02.log NO

3 1 417 52428800 512 1 YES ACTIVE 13245389934 2016-08-05 10:39:39 13245390164 2016-08-05 10:49:19 3 ONLINE /mnt/ramdisk/book/redo03.log NO

--当前使用/mnt/ramdisk/book/redo01.log。seq=418.

SCOTT@book> alter table emp1 move tablespace users;

Table altered.

--我现在移动emp1表,再来分析归档seq=411的日志情况。参看链接http://blog.itpub.net/267265/viewspace-2123023/

--//使用logminer分析：

Begin

SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle/archivelog/book/1_411_907434361.dbf', sys.dbms_logmnr.New);

end;

SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle/archivelog/book/1_412_907434361.dbf', sys.dbms_logmnr.AddFile);

SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG+sys.dbms_logmnr.COMMITTED_DATA_ONLY);

--注意：我前面的测试在下一个日志才提交的。

SCOTT@book> @ &r/pt2 'select *from v$logmnr_contents where seg_name=''EMP1'' and rownum=1';

old 6: from table(xmlsequence(cursor( &1 )))

new 6: from table(xmlsequence(cursor( select *from v$logmnr_contents where seg_name='EMP1' and rownum=1 )))

ROW_NUM COL_NUM COL_NAME COL_VALUE

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

1 1 SCN 13245387377

2 START_SCN 13245387377

3 COMMIT_SCN 13245387689

4 TIMESTAMP 2016-08-05 09:41:35

5 START_TIMESTAMP 2016-08-05 09:41:35

6 COMMIT_TIMESTAMP 2016-08-05 09:54:30

7 XIDUSN 10

8 XIDSLT 30

9 XIDSQN 54930

10 XID 0A001E0092D60000

11 PXIDUSN 10

12 PXIDSLT 30

13 PXIDSQN 54930

14 PXID 0A001E0092D60000

15 OPERATION UPDATE

16 OPERATION_CODE 3

17 ROLLBACK 0

18 SEG_OWNER SCOTT

19 SEG_NAME EMP1

20 TABLE_NAME EMP1

21 SEG_TYPE 2

22 SEG_TYPE_NAME TABLE

23 TABLE_SPACE USERS

24 ROW_ID AAAXY4AAEAAAAIzAAA

25 USERNAME SCOTT

26 OS_USERNAME UNKNOWN

27 MACHINE_NAME UNKNOWN

28 AUDIT_SESSIONID 35982267

29 SESSION_x0023_ 232

30 SERIAL_x0023_ 5

31 SESSION_INFO UNKNOWN

32 THREAD_x0023_ 1

33 SEQUENCE_x0023_ 2

34 RBASQN 411

35 RBABLK 8

36 RBABYTE 16

37 UBAFIL 3

38 UBABLK 0

39 UBAREC 0

40 UBASQN 0

41 ABS_FILE_x0023_ 3

42 REL_FILE_x0023_ 4

43 DATA_BLK_x0023_ 563

44 DATA_OBJ_x0023_ 95800

45 DATA_OBJV_x0023_ 1

46 DATA_OBJD_x0023_ 95800

47 SQL_REDO update "SCOTT"."EMP1" set "ENAME" = '1234567DEF' where "ENAME" = 'SMITH' and ROWID = 'AAAXY4AAEAAAAI

48 SQL_UNDO update "SCOTT"."EMP1" set "ENAME" = 'SMITH' where "ENAME" = '1234567DEF' and ROWID = 'AAAXY4AAEAAAAI

49 RS_ID 0x00019b.00000008.0010

50 SSN 0

51 CSF 0

52 STATUS 0

53 REDO_VALUE 452

54 UNDO_VALUE 453

55 SAFE_RESUME_SCN 0

56 CSCN 13245387689

56 rows selected.

SCOTT@book> select * from dba_objects where owner=user and object_name='EMP1';

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME

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

SCOTT EMP1 95800 95806 TABLE 2016-08-05 09:39:11 2016-08-05 10:51:34 2016-08-05:09:39:11 VALID N N N 1

--我前面移动了对象，DATA_OBJECT_ID已经发生了变化。通过rowid已经无法访问。

SCOTT@book> select * from emp1 where rowid='AAAXY4AAEAAAAIzAAA';

select * from emp1 where rowid='AAAXY4AAEAAAAIzAAA'

ERROR at line 1:

ORA-01410: invalid ROWID

--而现在rowid是：

SCOTT@book> select rowid,emp1.* from emp1 where rownum=1;

ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

AAAXY+AAEAAAAODAAA 7369 1234567DEF CLERK 7902 1980-12-17 00:00:00 800 20

SCOTT@book> @ &r/rowid AAAXY+AAEAAAAODAAA

OBJECT FILE BLOCK ROW DBA TEXT

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

95806 4 899 0 4,899 alter system dump datafile 4 block 899 ;

-- DATA_OBJECT_ID 现在是95806。这样你拿到sql_undo 修改也要注意的。

3.测试2.

--如果删除了对象呢？

SCOTT@book> drop table emp1 purge ;

Table dropped.

no rows selected

--这样无法查询到。

SCOTT@book> @ &r/pt2 'select *from v$logmnr_contents where row_id=''AAAXY4AAEAAAAIzAAA''';

ROW_NUM COL_NUM COL_NAME COL_VALUE

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

1 1 SCN 13245387377

2 START_SCN 13245387377

3 COMMIT_SCN 13245387689

4 TIMESTAMP 2016-08-05 09:41:35

5 START_TIMESTAMP 2016-08-05 09:41:35

6 COMMIT_TIMESTAMP 2016-08-05 09:54:30

7 XIDUSN 10

8 XIDSLT 30

9 XIDSQN 54930

10 XID 0A001E0092D60000

11 PXIDUSN 10

12 PXIDSLT 30

13 PXIDSQN 54930

14 PXID 0A001E0092D60000

15 OPERATION UPDATE

16 OPERATION_CODE 3

17 ROLLBACK 0

18 SEG_OWNER UNKNOWN

19 SEG_NAME OBJ# 95800

20 TABLE_NAME OBJ# 95800

21 SEG_TYPE 0

22 SEG_TYPE_NAME UNKNOWN

23 TABLE_SPACE UNKNOWN

24 ROW_ID AAAXY4AAEAAAAIzAAA

25 USERNAME SCOTT

26 OS_USERNAME UNKNOWN

27 MACHINE_NAME UNKNOWN

28 AUDIT_SESSIONID 35982267

29 SESSION_x0023_ 232

30 SERIAL_x0023_ 5

31 SESSION_INFO UNKNOWN

32 THREAD_x0023_ 1

33 SEQUENCE_x0023_ 2

34 RBASQN 411

35 RBABLK 8

36 RBABYTE 16

37 UBAFIL 3

38 UBABLK 0

39 UBAREC 0

40 UBASQN 0

41 ABS_FILE_x0023_ 3

42 REL_FILE_x0023_ 4

43 DATA_BLK_x0023_ 563

44 DATA_OBJ_x0023_ 95800

45 DATA_OBJV_x0023_ 1

46 DATA_OBJD_x0023_ 95800

47 SQL_REDO update "UNKNOWN"."OBJ# 95800" set "COL 2" = HEXTORAW('31323334353637444546') where "COL 2" = HEXTORA

48 SQL_UNDO update "UNKNOWN"."OBJ# 95800" set "COL 2" = HEXTORAW('534d495448') where "COL 2" = HEXTORAW('3132333

49 RS_ID 0x00019b.00000008.0010

50 SSN 0

51 CSF 0

52 INFO Dictionary Mismatch

53 STATUS 2

54 REDO_VALUE 934

55 UNDO_VALUE 935

56 SAFE_RESUME_SCN 0

57 CSCN 13245387689

57 rows selected.

--虽然能取到，显示的是UNKNOWN。

[20160805]logminer使用问题2.txt

继续阅读

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List