重做日志文件基本维护简析(四)
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
4 /u01/oracle/9i/oradata/gt9i/redo04a.log
4 /u01/oracle/9i/oradata/gt9i/redo04b.log
4.4. 删除日志组
sys@GT9I> alter database drop logfile group 4
2 ;
Database altered.
sys@GT9I> select group# from v$log;
GROUP#
----------
1
2
3
sys@GT9I> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/oracle/9i/oradata/gt9i/redo01.log
2 /u01/oracle/9i/oradata/gt9i/redo02.log
3 /u01/oracle/9i/oradata/gt9i/redo03.log
5. 日志挖掘
日志挖掘也即是logminer。
首先创建字典文件。
sys@GT9I> alter system set utl_file_dir='/u01/oracle/9i/oradata/gt9i/dict' scope=spfile;
System altered.
sys@GT9I> startup force;
ORACLE instance started.
Total System Global Area 286752136 bytes
Fixed Size 740744 bytes
Variable Size 150994944 bytes
Database Buffers 134217728 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
sys@GT9I>
1 BEGIN
2 DBMS_LOGMNR_D.build (
3 dictionary_filename => 'mydict1.ora',
4 dictionary_location => '/u01/oracle/9i/oradata/gt9i/dict');
5 END; www.2cto.com
6 /
PL/SQL procedure successfully completed.
做些测试，我这里用另外一个用户执行创建表、插入、删除操作，还创建了一个过程，最后删除表和过程。
添加日志文件
查看下当前的日志文件：
sys@GT9I> select status,member from v$logfile;
STATUS MEMBER
---------- --------------------------------------------------
/u01/oracle/9i/oradata/gt9i/redo03.log
STALE /u01/oracle/9i/oradata/gt9i/redo02.log
STALE /u01/oracle/9i/oradata/gt9i/redo01.log
sys@GT9I> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 12 104857600 1 NO INACTIVE 3389635 16-MAY-12
2 1 10 104857600 1 NO INACTIVE 3099675 14-MAY-12
3 1 13 104857600 1 NO CURRENT 3414320 16-MAY-12
这里是group=3的组是当前组。
BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/u01/oracle/9i/oradata/gt9i/redo03.log');
/*
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/u01/oracle/9i/oradata/gt9i/redo01.log');
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/u01/oracle/9i/oradata/gt9i/redo02.log');
*/
END;
/
开始挖掘
首先开启参数，否则无法挖掘到ddl语句。
alter database add supplemental log data;
BEGIN
DBMS_LOGMNR.start_logmnr (
dictfilename => '/u01/oracle/9i/oradata/gt9i/dict/mydict1.ora',
starttime => TO_DATE('20120516 16:20:00','yyyymmdd hh24:mi:ss'),
endtime => TO_DATE('20120516 16:28:00','yyyymmdd hh24:mi:ss'));
END;
/
接着可以查询挖掘到的内容，注意必须在同一个会话内进行查询：
Select Scn, Operation, Sql_Redo, Sql_Undo From V$logmnr_Contents;
最后停止挖掘：
BEGIN www.2cto.com
DBMS_LOGMNR.end_logmnr;
END;
/
以上测试在9i测试通过，但是9i当中无法找到DDL语句；相反，找10g当中可以看到insert、delete等DDL语句，但是在