oracle查看附加⽇志,Oracle11gLogMiner解析redo⽇志⼀、设置⽇期格式(⽅便查看)
SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
System altered.
SQL>
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'YY
-------------------
2018-09-19 08:33:27
SQL>
⼆、开启补全⽇志
查看补全⽇志是否开启：
SQL> Select
SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,
SUPPLEMENTAL_LOG_DATA_ALL from v$database;
开启补全⽇志：
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key, unique,foreign key) columns;
再次查看：
SQL> Select
SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,
SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES    YES YES YES NO
SQL>
三、开启归档
SQL> archive log list
Database log mode          No Archive Mode
Automatic archival          Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST Oldest online log sequence    6
Current log sequence          8
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size            2217992 bytes
Variable Size          494929912 bytes
Database Buffers      335544320 bytes
Redo Buffers            6590464 bytes
Database mounted.
SQL>
SQL>
SQL> alter database archivelog;
Database altered.
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> archive log list
Database log mode          Archive Mode
Automatic archival          Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST Oldest online log sequence    6
Next log sequence to archive  8
Current log sequence          8
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL>
四、配置LogMiner⼯具
SQL> show parameter UTL_FILE
NAME                    TYPE    VALUE
------------------------------------ ----------- ------------------------------utl_file_dir                string
SQL>
SQL> alter system set utl_file_dir='/u03' scope=spfile; System altered.
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size            2217992 bytes
Variable Size          494929912 bytes
Database Buffers      335544320 bytes
Redo Buffers            6590464 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter UTL_FILE
NAME                    TYPE    VALUE
-
----------------------------------- ----------- ------------------------------
utl_file_dir                string    /u03
SQL>
五、创建数据字典⽂件
数据字典⽂件是⼀个⽂本⽂件，使⽤包DBMS_LOGMNR_D来创建。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发⽣变化，这时就需要重新创建该字典⽂件。另外⼀种情况是在分析另外⼀个数据库⽂件的重作⽇志时，也必须要重新⽣成⼀遍被分析数据库的数据字典⽂件。
SQL> begin
dbms_logmnr_d.build('dfdict.data','/u03');
end;
/
PL/SQL procedure successfully completed.
SQL>
进⼊操作系统⽬录，在/u03下已经⽣成了数据字典⽂件：dfdict.data
[root@RedHat5 u03]# ls
dfdict.data
六、创建要分析的⽇志⽂件列表
Oracle的重作⽇志分为两种，在线(online)和离线(offline)归档⽇志⽂件，我这⾥主要分析归档⽇志，在线⽇志原理⼀样。
6.1、查看⽇志组的状况
SQL> select GROUP# ,SEQUENCE# ,STATUS  from v$log;
GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
1      10 INACTIVE
2      11 CURRENT
3        9 INACTIVE
SQL>
现在做任何操作都是的事物⽇志记录到第⼆个⽇志组的⽇志⽂件(current)
6.2、查看⽇志组成员
SQL> set linesize 120
SQL> col MEMBER format a45
SQL> select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
3 /u01/app/oracle/oradata/or11g/redo03.log
2 /u01/app/oracle/oradata/or11g/redo02.log
1 /u01/app/oracle/oradata/or11g/redo01.log
SQL>
6.3、模拟误操作：
SQL> alter user scott account unlock identified by oracle;
User altered.
SQL> p set sal=0;
14 rows updated.
SQL> commit;
Commit complete.
SQL>
此时p表的⼯资都为0了，发现搞错了，需要恢复。
6.4、创建要挖掘的⽇志列表：
因为这⾥只有⼀个⽇志，那么只需要添加⼀个就好了(第⼀个⽇志⽤new“=>w”)
execute dbms_logmnr.add_logfile(options =>w,logfilename
=>'/u01/app/oracle/oradata/or11g/redo01.log');
如果是有多个⽇志，那么就继续添加(添加其他⽇志⽤add“=>dbms_logmnr.addfile”)：
execute dbms_logmnr.add_logfile(options
=>dbms_logmnr.addfile,logfilename=>'/u01/app/oracle/oradata/or11g/redo02.log');
execute dbms_logmnr.add_logfile(options
=>dbms_logmnr.addfile,logfilename=>'/u01/app/oracle/oradata/or11g/redo03.log');
如：
SQL> execute dbms_logmnr.add_logfile(options =>w,logfilename
=>'/u01/app/oracle/oradata/or11g/redo01.log');
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(options
=>dbms_logmnr.addfile,logfilename=>'/u01/app/oracle/oradata/or11g/redo02.log');
怎样测试oracle11g安装完成PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(options
=>dbms_logmnr.addfile,logfilename=>'/u01/app/oracle/oradata/or11g/redo03.log');
PL/SQL procedure successfully completed.
SQL>
说明：
w --⽤于建⼀个⽇志分析表
dbms_logmnr.addfile --⽤于加，⼊⽤于分析的⽇志⽂件

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。