分析ROLLBACK是否产生日志信息

先介绍分析日志的方法,采用LOGMNR

流程如下：

1. SQL> @F:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmslm.sql  
2.   
3. 程序包已创建。  
4.   
5.   
6. 授权成功。  
7.   
8.   
9. 同义词已创建。  
10.   
11. SQL> @F:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmslmd.sql  
12.   
13. 程序包已创建。  
14.   
15.   
16. 同义词已创建。  
17.   
18.   
19. SQL> select log_mode from v$database;  
20.   
21. LOG_MODE                                                                          
22. ------------                                                                      
23. NOARCHIVELOG                                                                      
24.   
25. SQL> @F:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmslms.sql  
26.   
27. 程序包已创建。  
28.   
29. 没有错误。  
30.   
31. 授权成功。  
32.   
33. SQL> shutdown immediate;  
34. 数据库已经关闭。  
35. 已经卸载数据库。  
36. ORACLE 例程已经关闭。  
37. SQL> startup mount;  
38. ORACLE 例程已经启动。  
39.   
40. Total System Global Area  778387456 bytes                                         
41. Fixed Size                  1374808 bytes                                         
42. Variable Size             301991336 bytes                                         
43. Database Buffers          469762048 bytes                                         
44. Redo Buffers                5259264 bytes                                         
45. 数据库装载完毕。  
46. --开启归档  
47. SQL> alter database archivelog;  
48.   
49. 数据库已更改。  
50.   
51. SQL> alter database open;  
52.   
53. 数据库已更改。  
54. --创建日志字典路径  
55. SQL> alter system set utl_file_dir='d:\logmin' scope=spfile;  
56.   
57. 系统已更改。  
58.   
59. SQL> shutdown immediate;  
60. 数据库已经关闭。  
61. 已经卸载数据库。  
62. ORACLE 例程已经关闭。  
63. SQL> startup  
64. ORACLE 例程已经启动。  
65.   
66. Total System Global Area  778387456 bytes                                         
67. Fixed Size                  1374808 bytes                                         
68. Variable Size             301991336 bytes                                         
69. Database Buffers          469762048 bytes                                         
70. Redo Buffers                5259264 bytes                                         
71. 数据库装载完毕。  
72. 数据库已经打开。  
73. SQL> begin  
74.   2  dbms_logmnr_d.build(  
75.   3  dictionary_filename=>'logmin_dict.dat',  
76.   4  dictionary_location=>'d:\logmin');  
77.   5  end;  
78.   6  /  
79.   
80. PL/SQL 过程已成功完成。  
81.   
82. SQL> execute dbms_logmnr.add_logfile(options=>dbms_logmnr.new,logfilename=>'F:\app\Administrator\oradata\orcl\REDO01.LOG');  
83.   
84. PL/SQL 过程已成功完成。  
85.                                                                
86.   
87. SQL> begin  
88.   2  dbms_logmnr.start_logmnr(  
89.   3   dictfilename=>'d:\logmin\logmin_dict.dat'  
90.   4  );  
91.   5  end;  
92.   6  /  
93.   
94. PL/SQL 过程已成功完成。  
95.   
96. SQL> select count(*) from v$logmnr_contents;  
97.   
98.   COUNT(*)                                                                        
99. ----------                                                                        
100.      50496                                                                        
101.   
102. SQL> create table log_back as select * from v$logmnr_contents;  
103.   
104. 表已创建。  
105.   
106. SQL>execute dbms_logmnr.end_logmnr();  
107. PL/SQL 过程已成功完成。  
108.   
109. SQL> spool off  

直接使用包来分析日志(11g r2的版本)

1. --使用包分析日志的方法  
2. exec dbms_logmnr.add_logfile('/data/archive/redo001.log',dbms_logmnr.new);  
3. exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);  
4. --查询日志信息  
5. select timestamp,scn,sql_redo  
6. from v$logmnr_contents  
7. where lower(sql_redo) like '%insert%'  
8. and seg_name='TEST'  
9. and seg_owner='TEST';  
10.   
11. dbms_logmnr.end_logmnr(); --停止日志分析  

 

注意：
如果所有操作只回滚不提交的话，最后生成的归档日志是不会有任何人工操作数据的(可能会有一些系统操作的数据信息).
SQL> select group#,archived,status,first_time from v$log;

    GROUP# ARC STATUS           FIRST_TIME
---------- --- ---------------- -------------------
         1 YES INACTIVE         2012-10-25 11:17:50
         2 NO  CURRENT          2012-10-25 11:17:57
         3 YES INACTIVE         2012-10-25 11:17:39
也就是现在分析1和3,v$logmnr_contents表里没有任何信息(之前我只有做rollback,未做任何commit事务).只有分析2、CURRENT状态的才有数据.

还要注意的是如果分析活动的日志(如现在分析GROUP 2),有时候不能使用INSERT INTO TT select * from v$logmnr_contents,来保存日志数据,它会提示如下错误:

第 1 行出现错误:
ORA-00310: 归档日志包含序列 290; 要求序列 287
ORA-00334: 归档日志: 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG'

这个不是很清楚原因,个人猜想可能是ARC进程正在归档造成的,此时查询V$LOG发现如下：

SQL> select group#,archived,status,first_time from v$log

    GROUP# ARC STATUS           FIRST_TIME
---------- --- ---------------- -------------------
         1 YES ACTIVE           2012-10-25 12:08:45
         2 YES ACTIVE           2012-10-25 12:09:41
         3 NO  CURRENT          2012-10-25 12:09:45

 

在分析日志组2(状态为:CURRENT)的数据时发现,如果insert操作后(
语句为:
begin
  for i in 1..1000 loop
INSERT into tt002 values(i,'aa');
end loop;
end;)
不rollback,也不commit.分析后的日志信息如下：
TIMESTAMP                  SCN                                                  
------------------- ----------                                                  
SQL_REDO                                                                        
--------------------------------------------------------------------------------
2012-10-25 11:34:38    3813555                                                  
insert into "TEST"."TT002"("I","J") values ('1','aa');                  
                                                                                
2012-10-25 11:34:38    3813555                                                  
insert into "TEST"."TT002"("I","J") values ('2','aa');                  
                                                                                
2012-10-25 11:34:38    3813555                                                  
insert into "TEST"."TT002"("I","J") values ('3','aa');  

......

如果ROLLBACK之后,产生的日志信息如下:
SQL> select timestamp,scn,sql_redo
  2  from v$logmnr_contents
  3  where lower(sql_redo) like '%tt002%' and lower(sql_redo) like '%delete%';

TIMESTAMP                  SCN                                                 
------------------- ----------                                                  
SQL_REDO                                                                        
--------------------------------------------------------------------------------
2012-10-25 11:36:26    3813606                                                  
delete from "TEST"."TT002" where ROWID = 'AAAS0lAAEAAAAFbAGB';          
                                                                                
2012-10-25 11:36:26    3813606                                                  
delete from "TEST"."TT002" where ROWID = 'AAAS0lAAEAAAAFbAGA';          
                                                                                
2012-10-25 11:36:26    3813606                                                  
delete from "TEST"."TT002" where ROWID = 'AAAS0lAAEAAAAFbAF/';  

.....

也就是说回滚INSERT对应产生了DELETE的日志信息.