今天开发说晚上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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30491527/viewspace-1982903/
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
