ITPub博客

首页 > 数据库 > Oracle > 查询表的历史操作内容

查询表的历史操作内容

原创 Oracle 作者:roc_phoenix 时间:2016-01-27 10:55:51 0 删除 编辑
今天开发说晚上6点左右发现一个表有异常,数据量猛增。 问可否查询是什么原因造成的。

沟通之后,得知 表结构有改变,与晚6点左右发现,具体操作时间不得知。

首先查询表最后一次变更的时间


SQL> select CREATED,LAST_DDL_TIME from user_objects where object_name='TAR_BUSINESSANDBUNGALOW_REL';

CREATED             LAST_DDL_TIME
------------------- -------------------
2015-12-07 00:40:19 2015-12-07 00:46:53


看内存中是否有相应的历史记录

select distinct sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where BLOCKING_SESSION>0 and sample_time>=to_date('2015-12-07 00:30:00','yyyy-mm-dd hh24:mi:ss') and sample_time<=to_date('2015-12-07 18:00:00','yyyy-mm-dd hh24:mi:ss');

根据sql_id 去查询相关表内容   没有发现相关历史内容
select dbid,command_type,sql_text from dba_hist_sqltext where sql_id in ('4j882m6r2jyg7','cdgb2atf9huhc') and sql_text like '%TAR_BUSINESSANDBUNGALOW_REL%';

决定采用日志挖掘方式

查看在线日志信息。   时间太久在线日志已经不能挖掘, 决定挖掘归档。

SQL> set line 132
SQL> set wrap off
SQL> select * from v$Log;
truncating (as requested) before column NEXT_CHANGE#


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- -----------
         1          1      38317 1073741824        512          1 NO  CURRENT             1.4706E+13 2015-12-08 09:47:59
         2          1      38310 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 01:02:45 2015-12-08
         3          1      38311 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 02:06:15 2015-12-08
         4          1      38312 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:21:42 2015-12-08
         5          1      38313 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:21:48 2015-12-08
         6          2      42347 1073741824        512          1 YES INACTIVE            1.4706E+13 2015-12-08 08:52:48 2015-12-08
         7          2      42348 1073741824        512          1 YES ACTIVE              1.4706E+13 2015-12-08 09:36:06 2015-12-08
         8          2      42349 1073741824        512          1 NO  CURRENT             1.4706E+13 2015-12-08 09:47:57
         9          2      42342 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 01:02:44 2015-12-08
        10          2      42343 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:08:26 2015-12-08
        11          1      38314 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:43:33 2015-12-08

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- -----------
        12          1      38315 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 06:42:30 2015-12-08
        13          1      38316 1073741824        512          1 YES ACTIVE              1.4706E+13 2015-12-08 08:52:45 2015-12-08
        14          2      42346 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:21:49 2015-12-08
        15          2      42344 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:09:12 2015-12-08
        16          2      42345 1073741824        512          1 YES INACTIVE            1.4705E+13 2015-12-08 05:21:41 2015-12-08

16 rows selected.




查看归档情况  发现7号凌晨到1点 一共有4个归档切换。

SQL> select * from (select to_char(first_time,'yyyy-mm-dd hh24') hour1,count(*) from gv$log_history group by to_char(first_time,'yyyy-mm-dd hh24') order by hour1 desc) where rownum<30;

HOUR1           COUNT(*)
------------- ----------
2015-12-08 09          2
2015-12-08 08          4
2015-12-08 06          2
2015-12-08 05         14
2015-12-08 02          2
2015-12-08 01          8
2015-12-08 00          2
2015-12-07 23         22
2015-12-07 22          2
2015-12-07 18          2
2015-12-07 16          4

HOUR1           COUNT(*)
------------- ----------
2015-12-07 15          2
2015-12-07 14          4
2015-12-07 12          2
2015-12-07 11         32
2015-12-07 10          6
2015-12-07 09          2
2015-12-07 08          2
2015-12-07 05         10
2015-12-07 04          8
2015-12-07 01          8
2015-12-07 00          4

HOUR1           COUNT(*)
------------- ----------
2015-12-06 23         24
2015-12-06 19         16
2015-12-06 18         18
2015-12-06 17          4
2015-12-06 15         18
2015-12-06 14          6
2015-12-06 13          4

29 rows selected.


每个实例分别切换了2个归档

SQL> select * from (select INST_ID,to_char(first_time,'yyyy-mm-dd hh24') hour1,count(*) from gv$log_history group by INST_ID,to_char(first_time,'yyyy-mm-dd hh24') order by hour1 desc) where rownum<80;

   INST_ID HOUR1           COUNT(*)
---------- ------------- ----------
         1 2015-12-08 09          1
         2 2015-12-08 09          1
         1 2015-12-08 08          2
         2 2015-12-08 08          2
         1 2015-12-08 06          1
         2 2015-12-08 06          1
         1 2015-12-08 05          7
         2 2015-12-08 05          7
         1 2015-12-08 02          1
         2 2015-12-08 02          1
         1 2015-12-08 01          4

   INST_ID HOUR1           COUNT(*)
---------- ------------- ----------
         2 2015-12-08 01          4
         1 2015-12-08 00          1
         2 2015-12-08 00          1
         1 2015-12-07 23         11
         2 2015-12-07 23         11
         1 2015-12-07 22          1
         2 2015-12-07 22          1
         1 2015-12-07 18          1
         2 2015-12-07 18          1
         1 2015-12-07 16          2
         2 2015-12-07 16          2

   INST_ID HOUR1           COUNT(*)
---------- ------------- ----------
         1 2015-12-07 15          1
         2 2015-12-07 15          1
         1 2015-12-07 14          2
         2 2015-12-07 14          2
         1 2015-12-07 12          1
         2 2015-12-07 12          1
         1 2015-12-07 11         16
         2 2015-12-07 11         16
         1 2015-12-07 10          3
         2 2015-12-07 10          3
         1 2015-12-07 09          1

   INST_ID HOUR1           COUNT(*)
---------- ------------- ----------
         2 2015-12-07 09          1
         1 2015-12-07 08          1
         2 2015-12-07 08          1
         1 2015-12-07 05          5
         2 2015-12-07 05          5
         1 2015-12-07 04          4
         2 2015-12-07 04          4
         1 2015-12-07 01          4
         2 2015-12-07 01          4
         1 2015-12-07 00          2
         2 2015-12-07 00          2


进入归档目录,找出7号2个实例头两个归档文件之后,进行加载,分析。

SQL> show user;
USER is "SYS"
SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+arch/jxdb/archivelog/2015_12_07/thread_1_seq_38289.3248.897783017', Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+arch/jxdb/archivelog/2015_12_07/thread_1_seq_38290.4057.897786071',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+arch/jxdb/archivelog/2015_12_07/thread_2_seq_42303.3913.897784731',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+arch/jxdb/archivelog/2015_12_07/thread_2_seq_42304.4686.897787153',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.


查询DDL操作的总个数。

SQL> select count(*) from v$logmnr_contents where operation IN ('DDL') and seg_owner='ADMIN' and TABLE_NAME = 'TAR_BUSINESSANDBUNGALOW_REL';

  COUNT(*)
----------
         7

创建表,把操作存放表中   结束分析。

SQL> set timing on
SQL> create table log_jyc_ddl_machine as select OS_USERNAME,MACHINE_NAME,timestamp,commit_timestamp,seg_owner,seg_name,seg_type_name,table_space,row_id,session_info,username,operation,sql_redo,sql_undo
  2  from v$logmnr_contents where operation IN ('DDL') and seg_owner='ADMIN' and TABLE_NAME = 'TAR_BUSINESSANDBUNGALOW_REL';

Table created.

Elapsed: 00:04:36.14
SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20


查询结果 一目了然。



查看此表,发现数据量跟增加过后的原表相吻合。



现在无法查出是谁操作,可以查询监听日志来确认。

# su - oracle
[YOU HAVE NEW MAIL]
JXDB2:/home/oracle$cd /u01/app/oracle/diag
JXDB2:/u01/app/oracle/diag$ls
asm       clients   crs       diagtool  lsnrctl   netcman   ofm       rdbms     tnslsnr
JXDB2:/u01/app/oracle/diag$cd tnslsnr
JXDB2:/u01/app/oracle/diag/tnslsnr$ls
JXDB2
JXDB2:/u01/app/oracle/diag/tnslsnr$cd jxdb2
ksh: jxdb2:  not found.
JXDB2:/u01/app/oracle/diag/tnslsnr$ls
JXDB2
JXDB2:/u01/app/oracle/diag/tnslsnr$ls
JXDB2
JXDB2:/u01/app/oracle/diag/tnslsnr$cd *
JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2$ls
listener_dg
JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2$cd lis*
JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2/listener_dg$ls
alert          incident       lck            metadata_dgif  stage          trace
cdump          incpkg         metadata       metadata_pv    sweep
JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2/listener_dg$cd trace
JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2/listener_dg/trace$ls
listener_dg.log

JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2/listener_dg/trace$pwd
/u01/app/oracle/diag/tnslsnr/JXDB2/listener_dg/trace
JXDB2:/u01/app/oracle/diag/tnslsnr/JXDB2/listener_dg/trace$ls -lt
total 136
-rw-r-----    1 oracle   oinstall      69172 Jan 20 2015  listener_dg.log

实例2

# su - grid

JXDB2:/u01/grid/diag/tnslsnr/JXDB2/listener/trace$date
Tue Dec  8 10:37:53 GMT+08:00 2015
JXDB2:/u01/grid/diag/tnslsnr/JXDB2/listener/trace$tail -3000 listener.log|more


根据里面的内容 过滤出  7号凌晨哪些主机登陆了数据库,把主机名告诉开发。

grep 07-DEC-2015 listener.log > 2015-12-07.txt

grep "07-DEC-2015 00:4" listener.log > t.txt

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30491527/viewspace-1982903/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-09-18

  • 博文量
    48
  • 访问量
    125661