Logminer实战

相信许多DBA同学都曾遇到过这种情况，由于各种各样的原因，必须对数据库做不完全恢复；但又不确定应该恢复到哪个时间点或SCN才合适，因而反复的执行不完全恢复，悲剧掉了一地，中枪的有木有？温馨提示下，在生产环境中做不完全恢复前，可能的话，先对现场做一次备份，有备无患！前面的是第一个问题，当找对了不完全恢复的时间点并成功执行不完全恢复后，后面的更改如何重做？让客户重演一遍？想都别想？打个比方，当前时间为上午11:00，不完全恢复的时间为上午8:30，把数据库状态恢复到上午8:30后，8:30到11:00这3.5小时的数据库更改如何重演，这时间拖的越长就越悲剧！

Oracle从8i后提供了一个logminer免费的日志挖掘工具，可以解析归档，在线日志中的具体内容，针对前面提出的2个问题，可以很好的解决！下面使用oracle 10g平台来实战下…

一：对数据库执行备份，生产环境中，这种操作都是计划性的，过期的备份还应该定期归档

1. [oracle@rhel6 ~]$ rman target /  
2. Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 9 19:24:02 2012  
3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
4. connected to target database: ORCL (DBID=1289365799)  
5.  
6. RMAN> list backup of database;  
7. using target database control file instead of recovery catalog  
8.  
9. RMAN> backup database;   
10. RMAN> list backup of database summary;  
11.  
12. List of Backups 
13. ===============  
14. Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag  
15. ------- -- -- - ----------- ------------------- ------- ------- ---------- ---  
16. 1       B  F  A DISK        2012-05-09:19:25:20 1       1       NO         TAG20120509T192436 

二：开启数据库的记录附加日志属性，详细作用参考：http://space.itpub.net/7607759/viewspace-462640

1. [oracle@rhel6 ~]$ sqlplus /nolog  
2. SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 9 19:26:14 2012  
3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
4. SQL> conn /as sysdba  
5. Connected.  
6. SQL> select supplemental_log_data_min from v$database;  
7.  
8. SUPPLEME  
9. --------  
10. NO  
11.  
12. SQL> alter database add supplemental log data;  
13. Database altered.  
14.  
15. SQL> select supplemental_log_data_min from v$database;  
16.  
17. SUPPLEME  
18. --------  
19. YES 

三：创建测试数据

1. SQL> conn hr/hr  
2. Connected.  
3. SQL> create table t1 as select * from employees;  
4. Table created.  
5.  
6. SQL> select count(*) from t1;  
7.  
8.   COUNT(*)  
9. ----------  
10.        107  
11.  
12. 这里记录系统时间主要用于后面的对比，真实环境下是得不到这种信息的！  
13. SQL> select sysdate from dual;   
14.  
15. SYSDATE  
16. -------------------  
17. 2012-05-09:19:28:52  
18.  
19. SQL> truncate table t1;  
20. Table truncated.  
21.  
22. 在执行误操作后，用户继续其他的更新操作，同时数据库进行了日志切换  
23. SQL> create table t2 (id number);  
24. Table created.  
25.  
26. SQL> insert into t2 values (1);  
27. 1 row created.  
28.  
29. SQL> insert into t2 values (2);  
30. 1 row created.  
31.  
32. SQL> commit;  
33. Commit complete.  
34.  
35. SQL> conn /as sysdba   
36. Connected.  
37. SQL> archive log list;  
38. Database log mode          Archive Mode  
39. Automatic archival         Enabled  
40. Archive destination        USE_DB_RECOVERY_FILE_DEST  
41. Oldest online log sequence     20  
42. Next log sequence to archive   22  
43. Current log sequence           22  
44.  
45. SQL> alter system switch logfile;  
46. System altered. 

四：问题出现，现在需要将前面truncate的表找回，首先需要确定不完全恢复时间点的问题！下面开始介绍如何使用logminer
1：设置utl_file_dir初始化参数，重启数据库

1. SQL> show parameter utl_file;  
2.  
3. NAME                     TYPE    VALUE  
4. ------------------------------------ ----------- ------------------------------  
5. utl_file_dir                 string  
6.  
7. SQL> alter system set utl_file_dir='/home/oracle/utl_file_dir' scope=spfile;  
8. System altered.  
9.  
10. SQL> shutdown immediate  
11. Database closed.  
12. Database dismounted.  
13. ORACLE instance shut down.  
14. SQL> startup  
15. ORACLE instance started.  
16.  
17. Total System Global Area  213909504 bytes  
18. Fixed Size          2019640 bytes  
19. Variable Size         104861384 bytes  
20. Database Buffers      100663296 bytes  
21. Redo Buffers            6365184 bytes  
22. Database mounted.  
23. Database opened.  
24.  
25. SQL> show parameter utl_file;  
26.  
27. NAME                     TYPE    VALUE  
28. ------------------------------------ ----------- ------------------------------  
29. utl_file_dir                 string  /home/oracle/utl_file_dir  
30.  
31. SQL> !mkdir -p /home/oracle/utl_file_dir 

2：使用sys用户执行下面的3个sql脚本

1. SQL> show user;  
2. USER is "SYS"  
3. SQL> @?/rdbms/admin/dbmslms.sql;  
4. Package created.  
5. No errors.  
6. Grant succeeded.  
7.  
8. SQL> @?/rdbms/admin/dbmslm.sql;  
9. Package created.  
10. Grant succeeded.  
11.  
12. SQL> @?/rdbms/admin/dbmslmd.sql;  
13. Package created. 

3：创建数据字典文件，该字典文件是可选的，但是如果没有它，LogMiner解释出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，我们是无法直接理解的

1. SQL> exec dbms_logmnr_d.build(dictionary_filename => 'V10201dict.ora',dictionary_location => '/home/oracle/utl_file_dir');  
2. PL/SQL procedure successfully completed.  
3.  
4. SQL> !file /home/oracle/utl_file_dir/V10201dict.ora  
5. /home/oracle/utl_file_dir/V10201dict.ora: ASCII English text, with very long lines  
6.  
7. SQL> !du -sh /home/oracle/utl_file_dir/V10201dict.ora  
8. 23M /home/oracle/utl_file_dir/V10201dict.ora 

4：添加在线日志，进行日志挖掘；生产环境中，一般都会将源库的数据字典和日志文件拷贝到测试库上分析，源库不继续以read write方式打开

1. SQL> select member from v$logfile where type='ONLINE';  
2.  
3. MEMBER  
4. ----------------------------------------  
5. /u01/app/oracle/oradata/orcl/redo03.log  
6. /u01/app/oracle/oradata/orcl/redo02.log  
7. /u01/app/oracle/oradata/orcl/redo01.log  
8.  
9. SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/orcl/redo01.log',options => dbms_logmnr.new);  
10. PL/SQL procedure successfully completed.  
11.  
12. SQL> exec dbms_logmnr.start_logmnr(dictfilename => '/home/oracle/utl_file_dir/V10201dict.ora',options => dbms_logmnr.ddl_dict_tracking);  
13. PL/SQL procedure successfully completed. 

5：查看v$logmnr_contents视图是否有相关的信息

1. SQL> select distinct username from v$logmnr_contents;  
2.  
3. USERNAME  
4. ------------------------------  
5. SYSMAN  
6. SYS  
7.  
8. SQL> select count(*) from v$logmnr_contents;  
9.  
10.   COUNT(*)  
11. ----------  
12.     189963 

6：在线日志挖掘完后，发现没有相关的信息，则开始挖掘归档日志文件

1. SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_05_09/o1_mf_1_22_7tnochcr_.arc',options => dbms_logmnr.new);  
2. PL/SQL procedure successfully completed.  
3.  
4. SQL> exec dbms_logmnr.start_logmnr(dictfilename => '/home/oracle/utl_file_dir/V10201dict.ora',options => dbms_logmnr.ddl_dict_tracking);  
5. PL/SQL procedure successfully completed.  
6.  
7. SQL> select distinct username from v$logmnr_contents;  
8.  
9. USERNAME  
10. ------------------------------  
11. HR  
12. SYSMAN  
13. SYS 

7：通过查看v$logmnr_contents可以清楚的确定truncate的具体时间和之后的操作

1. SQL> col sql_redo format a60 trunc  
2. SQL> select sql_redo,timestamp from v$logmnr_contents where username='HR' and table_name='T1' and operation='DDL';  
3.  
4. SQL_REDO                             TIMESTAMP  
5. ------------------------------------------------------------ -------------------  
6. create table t1 as select * from employees;          2012-05-09:19:28:25  
7. truncate table t1;                                   2012-05-09:19:29:03  
8.  
9. SQL> select username,sql_redo,timestamp from v$logmnr_contents where timestamp > '2012-05-09:19:29:03' and username not in ('SYS','SYSMAN');  
10.  
11. USERNAME   SQL_REDO                 TIMESTAMP  
12. ---------- ---------------------------------------- -------------------  
13. HR     set transaction read write;          2012-05-09:19:29:27  
14. HR                          2012-05-09:19:29:27  
15. HR     insert into "SYS"."OBJ$"("OBJ#","DATAOBJ 2012-05-09:19:29:27  
16. HR     set transaction read write;          2012-05-09:19:29:27  
17. HR                          2012-05-09:19:29:27  
18. HR     set transaction read write;          2012-05-09:19:29:27  
19. HR                          2012-05-09:19:29:27  
20. HR     commit;                  2012-05-09:19:29:27  
21. HR     insert into "SYS"."SEG$"("TS#","FILE#"," 2012-05-09:19:29:27  
22. HR     update "SYS"."TSQ$" set "GRANTOR#" = '0' 2012-05-09:19:29:27  
23. HR     commit;                  2012-05-09:19:29:27  
24.  
25. USERNAME   SQL_REDO                 TIMESTAMP  
26. ---------- ---------------------------------------- -------------------  
27. HR     set transaction read write;          2012-05-09:19:29:27  
28. HR                          2012-05-09:19:29:27  
29. HR     commit;                  2012-05-09:19:29:27  
30. HR     insert into "SYS"."TAB$"("OBJ#","DATAOBJ 2012-05-09:19:29:27  
31. HR     insert into "SYS"."COL$"("OBJ#","COL#"," 2012-05-09:19:29:27  
32. HR     create table t2 (id number);         2012-05-09:19:29:27  
33. HR     update "SYS"."SEG$" set "TYPE#" = '5', " 2012-05-09:19:29:27  
34. HR     commit;                  2012-05-09:19:29:27  
35. HR     set transaction read write;          2012-05-09:19:29:39  
36. HR     insert into "HR"."T2"("ID") values ('1') 2012-05-09:19:29:39  
37. HR     insert into "HR"."T2"("ID") values ('2') 2012-05-09:19:29:43  
38.  
39. USERNAME   SQL_REDO                 TIMESTAMP  
40. ---------- ---------------------------------------- -------------------  
41. HR     commit;                  2012-05-09:19:29:43  
42. 23 rows selected. 

五：执行不完全恢复

1. SQL> shutdown immediate  
2. Database closed.  
3. Database dismounted.  
4. ORACLE instance shut down.  
5.  
6. SQL> startup mount  
7. ORACLE instance started.  
8.  
9. Total System Global Area  213909504 bytes  
10. Fixed Size          2019640 bytes  
11. Variable Size         109055688 bytes  
12. Database Buffers       96468992 bytes  
13. Redo Buffers            6365184 bytes  
14. Database mounted.  
15.  
16. [oracle@rhel6 ~]$ rman target /  
17. Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 9 20:21:30 2012  
18. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
19. connected to target database: ORCL (DBID=1289365799, not open)  
20.  
21. RMAN> restore database;  
22.  
23. RMAN> run {  
24. 2> set until time "to_date('2012-05-09:19:29:00','YYYY-MM-DD:HH24:MI:SS')";  
25. 3> recover database;  
26. 4> }  
27.  
28. RMAN> alter database open resetlogs;  
29. database opened  
30.  
31. RMAN> list incarnation;  
32.  
33. List of Database Incarnations  
34. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time  
35. ------- ------- -------- ---------------- --- ---------- ----------  
36. 1       1       ORCL     1289365799       PARENT  1          2005-10-22:21:44:08  
37. 2       2       ORCL     1289365799       PARENT  525876     2011-09-11:15:24:27  
38. 3       3       ORCL     1289365799       CURRENT 831637     2012-05-09:20:26:35 

六：剩下的就是根据v$logmnr_contents视图中的sql_redo补齐之后的数据

1. [oracle@rhel6 ~]$ sqlplus /nolog  
2. SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 9 20:27:34 2012  
3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
4.  
5. SQL> conn hr/hr  
6. Connected.  
7. SQL> select count(*) from t1;  
8.  
9.   COUNT(*)  
10. ----------  
11.        107  
12.  
13. SQL> select count(*) from t2;  
14. select count(*) from t2  
15.                      *  
16. ERROR at line 1:  
17. ORA-00942: table or view does not exist 

七、使用测试库挖掘生产库日志的注意事项 
1. LogMiner必须使用被分析数据库实例产生的字典文件，而不是安装LogMiner的数据库产生的字典文件，另外必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同；
2. 被分析数据库平台必须和当前LogMiner所在数据库平台一样，也就是说如果我们要分析的文件是由运行在UNIX平台上的Oracle 8i产生的，那么也必须在一个运行在UNIX平台上的Oracle实例上运行LogMiner，而不能在其他如Microsoft NT上运行LogMiner。当然两者的硬件条件不一定要求完全一样；
3. LogMiner日志分析工具仅能够分析Oracle 8以后的产品，对于8以前的产品，该工具也无能为力；

补充：
使用下面的语句LogMiner使用logmnrts$表空间来存放所有的LogMiner表。LogMiner默认存放表在System表空间
the following statement will re-create all LogMiner tables to use the logmnrts$ tablespace:
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');

参考如下文章，感谢作者分享！
http://space.itpub.net/22111412/viewspace-612686，
http://hi.baidu.com/lichangzai/blog/item/7a096bd5ae1724cc50da4b1b.html

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/859441如需转载请自行联系原作者

ylw6006