logminer抽取日志案例

客户发现了一个表的数据没有了，要求我找原因，不能在生产库上做。我一听，傻眼了，哪个天杀的干的这种事！看来这回客户动真格了，不过干这种事，很累，会掉层皮，但还是答应做了，我跟客户说，容我整理一下步骤，另外把这一段时间的归档留住一下：
思路大抵是这样的：
1、在搭建一个测试环境，目录与生产库基本相当
2、做异机恢复
3、由于原数据库太大，所以只考虑恢复数据库必要的表空间：system\sysaux\undo就可以了！
4、使用logminer来挖掘日志

 具体实施：
1、从生产库创建一个pfile,然后拷贝到测试环境，有些参数需要修改一下，这个就不细说

 测试环境中，启动数据库到nomount状态下：
SQL> startup nomount pfile='/u02/pfile.ora';
 ORACLE instance started.

 Total System Global Area 701485056 bytes
 Fixed Size 2216664 bytes
 Variable Size 301993256 bytes
 Database Buffers 394264576 bytes
 Redo Buffers 3010560 bytes

 2、创建一个密码文件，这个也可以在后来创建也行

[oracle@testdata u02]$ orapwd file='/u01/oracle/product/11.2.0/db1/dbs/pwduptest.ora' password=zixin01 entries=10;

 3、开始恢复

 如果有catalog的话，可以用rman target / catalog username/password@catalog
如果没有的话就直接rman target / 就行了。
 我将备份拷贝到测试机相应的位置，然后进行恢复

[oracle@testdata u02]$ rman target /

 Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 10 15:58:25 2011

 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

 connected to target database: UPTEST (not mounted)

--首先恢复的是控制文件

RMAN> restore control from '/u02/backup/UPTEST_CTL_20110508_1.bak

 Starting restore at 10-MAY-11
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=19 device type=DISK

 channel ORA_DISK_1: restoring control file
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
 Finished restore at 10-MAY-11

--恢复数据库文件

RMAN> alter database mount;
 run
 {
 set newname for datafile '/u01/oradata/undotbs1' to '/u01/oradata/undotbs1.dbf';
 set newname for datafile '/u01/oradata/system' to '/u01/oradata/system.dbf';
 set newname for datafile '/u01/oradata/sysaux' to '/u01/oradata/sysaux.dbf';
 restore database skip forever tablespace INCATEST,USERS,DB_USERS;
 switch datafile all;
 }

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 Starting restore at 10-MAY-11
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=18 device type=DISK

 channel ORA_DISK_1: starting datafile backup set restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/system.dbf
 channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sysaux.dbf
 channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/undotbs1.dbf
 channel ORA_DISK_1: reading from backup piece /u02/backup/UPTEST_DF_20110508_1.bak
 channel ORA_DISK_1: piece handle=/u02/backup/UPTEST_DF_20110508_1.bak tag=TAG20110508T083516
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: reading from backup piece /u02/backup/UPTEST_DF_20110510_2.bak
 channel ORA_DISK_1: piece handle=/u02/backup/UPTEST_DF_20110508_2.bak tag=TAG20110508T083516
 channel ORA_DISK_1: restored backup piece 2
 channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
 Finished restore at 10-MAY-11

 datafile 1 switched to datafile copy
 input datafile copy RECID=8 STAMP=782929747 file name=/u01/oradata/system.dbf
 datafile 2 switched to datafile copy
 input datafile copy RECID=9 STAMP=782929747 file name=/u01/oradata/sysaux.dbf
 datafile 3 switched to datafile copy
 input datafile copy RECID=10 STAMP=782929747 file name=/u01/oradata/undotbs1.dbf

--数据库的恢复
RMAN> recover database skip forever tablespace INCATEST,USERS,DB_USERS; ----使用skip的时候，会将不恢复的文件offline drop!

 Starting recover at 10-MAY-11
 using channel ORA_DISK_1

 Executing: alter database datafile 7 offline drop
 Executing: alter database datafile 8 offline drop
 Executing: alter database datafile 4 offline drop
 Executing: alter database datafile 5 offline drop
 Executing: alter database datafile 6 offline drop
 Executing: alter database datafile 9 offline drop

 starting media recovery

 archived log file name=/u01/oradata/882170_1_782312693.log thread=1 sequence=882170
 archived log file name=/u01/oradata/882171_1_782312693.log thread=1 sequence=882171
 archived log file name=/u01/oradata/882172_1_782312693.log thread=1 sequence=882172
 archived log file name=/u01/oradata/882173_1_782312693.log thread=1 sequence=882173
 unable to find archived log
 archived log thread=1 sequence=882174
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 05/10/2011 16:36:41
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 882174 and starting SCN of 67322261057
所有的归档，我不可能全用，所以到此为些，我只需要数据字典来分析日志而己

4、试着开一下数据库
[oracle@testdata ~]$ sqlplus "/as sysdba"

 SQL*Plus: Release 11.2.0.1.0 Production on Thu May 10 16:37:38 2011

 Copyright (c) 1982, 2009, Oracle. All rights reserved.

 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> select status from v$instance;

 STATUS
 ------------
 MOUNTED

 SQL> alter database open resetlogs;

 Database altered.

数据库打开了，那就比较好说了！

5、恢复归档日志，通过查询

 由于归档日志，由于我的数据库已经被resetlogs打开control,要把日志再恢复回来，必须关掉数据库！
 所以，重新恢复一个控制文件出来，把参数文件里的控制文件指向新恢复出来的控制文件

 开始恢复归档:

RMAN> run {
 2> set archivelog destination to '/u01/archback';
 3> SQL 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"';
 4> restore archivelog time between '2011-05-07 09:00:00' and '2011-05-10 10:10:00';
 5> }

经过一段时间，归档日志恢复出来了，现在可以用logminer来发现谁在干坏事了

6、使用logminer分析归档

--必须运行以下脚本，建立相应的包和表

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql

 Package created.

 Grant succeeded.

 Synonym created.

 SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

 Package created.

 Synonym created.

 SQL> @$ORACLE_HOME/rdbms/admin/dbmslms.sql

 Package created.

 No errors.

 Grant succeeded.

--指出数据字典存放的位置（先建好目录），并重启数据库

SQL> alter system set utl_file_dir='/u02/my_dictory' scope=spfile;

 System altered.

 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.

 Total System Global Area 701485056 bytes
 Fixed Size 2216664 bytes
 Variable Size 322964776 bytes
 Database Buffers 373293056 bytes
 Redo Buffers 3010560 bytes
 Database mounted.
 Database opened.

 SQL> EXECUTE dbms_logmnr_d.build( 'mydictionary.ora', '/u02/my_dictory');

--加入要分析的归档日志

execute dbms_logmnr.add_logfile('/u01/oradata/882101_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882102_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882103_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882104_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882105_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882106_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882107_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882108_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882109_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882110_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882111_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882112_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882113_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882114_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882115_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882116_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882117_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882118_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882119_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882120_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882121_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882122_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882123_1_782312693.log',dbms_logmnr.addfile);

--分析归档日志

EXECUTE dbms_logmnr.start_logmnr( DictFileName=>'/u02/my_dictory/mydictionary.ora');

将临时表的数据插入到一个固定表中
create table logmnrtab1 as select * from v$logmnr_contents;

 commit;

结束分析

SQL> exec sys.dbms_logmnr.end_logmnr;

 

 5、根据表查询所要找的对象和用户

SELECT sql_redo FROM logmnrtab1 WHERE username='-----' AND seg_name='-----';

根据条件可以判断出谁干的坏事！后来查出来是一个家伙在9号上午10点左右drop了表，然后马上又重建！那哥们被我害了

 实际上，我在测试环境中，每次分析100个归档日志，产生了4个表，每个表约40个g，最后根据sqllog\sessioninfo等，找到当事人的机器、ip、osname及sql语句等信息，具体的，大家可以测试看一下！

 要注意查看以下几个临时表

V$LOGMNR_DICTIONARY-------查询使用的数据字典文件

V$LOGMNR_PARAMETERS-------查询当前LogMiner设定的参数

V$LOGMNR_LOGS-------查询分析的日志文件

V$LOGMNR_CONTENTS-------日志文件的内容

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

转载于:http://blog.itpub.net/21752515/viewspace-1062683/