Oracle 11g LogMiner解析redo日志

栏目: 数据库 · 发布时间: 5年前

内容简介：一、设置日期格式（方便查看）SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;System altered.

一、设置日期格式（方便查看）

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> 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> alter database archivelog;

Database altered.

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> 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> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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> 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> Update scott.emp set sal=0;

14 rows updated.

SQL> commit;

Commit complete.

SQL>

此时scott.emp表的工资都为0了，发现搞错了，需要恢复。

6.4、创建要挖掘的日志列表：

因为这里只有一个日志，那么只需要添加一个就好了(第一个日志用new“=>dbms_logmnr.new”)

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,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 =>dbms_logmnr.new,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');

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>

说明：

dbms_logmnr.new --用于建一个日志分析表

dbms_logmnr.addfile --用于加，入用于分析的日志文件

dbms_logmnr.removefile --用于移出，用于分析的日志文件

如果是删除日志就用removefile，如：

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.removefile,logfilename =>'/u01/app/oracle/oradata/or11g/redo03.log');

6.5、开始挖掘：

exec dbms_logmnr.start_logmnr(dictfilename=>'/u03/dfdict.data');

如：

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/u03/dfdict.data');

PL/SQL procedure successfully completed.

SQL>

6.6、查看结果（这个是查询到当初误操作的语句，是通过redo查出来的）：

select sql_redo from v$logmnr_contents where seg_name='EMP';

如：

SQL> select sql_redo from v$logmnr_contents where seg_name='EMP';

SQL_REDO

------------------------------------------------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7369' and "SAL" = '800' and ROWID = 'AAAR3xAAEAAAACXAAA';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7499' and "SAL" = '1600' and ROWID = 'AAAR3xAAEAAAACXAAB';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7521' and "SAL" = '1250' and ROWID = 'AAAR3xAAEAAAACXAAC';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7566' and "SAL" = '2975' and ROWID = 'AAAR3xAAEAAAACXAAD';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7654' and "SAL" = '1250' and ROWID = 'AAAR3xAAEAAAACXAAE';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7698' and "SAL" = '2850' and ROWID = 'AAAR3xAAEAAAACXAAF';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7782' and "SAL" = '2450' and ROWID = 'AAAR3xAAEAAAACXAAG';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7788' and "SAL" = '3000' and ROWID = 'AAAR3xAAEAAAACXAAH';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7839' and "SAL" = '5000' and ROWID = 'AAAR3xAAEAAAACXAAI';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7844' and "SAL" = '1500' and ROWID = 'AAAR3xAAEAAAACXAAJ';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7876' and "SAL" = '1100' and ROWID = 'AAAR3xAAEAAAACXAAK';

SQL_REDO

------------------------------------------------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7900' and "SAL" = '950' and ROWID = 'AAAR3xAAEAAAACXAAL';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7902' and "SAL" = '3000' and ROWID = 'AAAR3xAAEAAAACXAAM';

update "SCOTT"."EMP" set "SAL" = '0' where "EMPNO" = '7934' and "SAL" = '1300' and ROWID = 'AAAR3xAAEAAAACXAAN';

14 rows selected.

SQL>

6.7、现在我需要将数据还原��去，那么就需要执行误操作的相反的操作。（通过undo来查出对应的反向语句）

select sql_undo from v$logmnr_contents where seg_name='EMP';

如：

SQL> select sql_undo from v$logmnr_contents where seg_name='EMP';

SQL_UNDO

------------------------------------------------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '800' where "EMPNO" = '7369' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAA';

update "SCOTT"."EMP" set "SAL" = '1600' where "EMPNO" = '7499' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAB';

update "SCOTT"."EMP" set "SAL" = '1250' where "EMPNO" = '7521' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAC';

update "SCOTT"."EMP" set "SAL" = '2975' where "EMPNO" = '7566' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAD';

update "SCOTT"."EMP" set "SAL" = '1250' where "EMPNO" = '7654' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAE';

update "SCOTT"."EMP" set "SAL" = '2850' where "EMPNO" = '7698' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAF';

update "SCOTT"."EMP" set "SAL" = '2450' where "EMPNO" = '7782' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAG';

update "SCOTT"."EMP" set "SAL" = '3000' where "EMPNO" = '7788' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAH';

update "SCOTT"."EMP" set "SAL" = '5000' where "EMPNO" = '7839' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAI';

update "SCOTT"."EMP" set "SAL" = '1500' where "EMPNO" = '7844' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAJ';

update "SCOTT"."EMP" set "SAL" = '1100' where "EMPNO" = '7876' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAK';

SQL_UNDO

------------------------------------------------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '950' where "EMPNO" = '7900' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAL';

update "SCOTT"."EMP" set "SAL" = '3000' where "EMPNO" = '7902' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAM';

update "SCOTT"."EMP" set "SAL" = '1300' where "EMPNO" = '7934' and "SAL" = '0' and ROWID = 'AAAR3xAAEAAAACXAAN';

14 rows selected.

SQL>

6.8、获取到反向操作语句后，再将反向操作语句重新执行一遍