Oracle 11g LogMiner解析redo日志

416次阅读

没有评论

共计 14530 个字符，预计需要花费 37 分钟才能阅读完成。

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

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> 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、获取到反向操作语句后，再将反向操作语句重新执行一遍

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’;
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’;

6.9、查询最后结果显示，数据全部找回

SQL> select sal from scott.emp;

SAL
———-
800
1600
1250
2975
0
0
2450
3000
5000
1500
1100

SAL
———-
950
3000
1300

14 rows selected.

SQL>

七、结束 logminer

exec dbms_logmnr.end_logmnr();

如：

SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

SQL>

***************************************************************************************************************************
***************************************************************************************************************************

八、使用 logminer 分析归档日志

8.1、数据库运行在归档模式

8.2、数据库已开启补全日志（附加日志）

8.3、查看字典文件生成目录

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string /u03
SQL>

8.4、为了方便实验，授予 Scott 用户 dba 权限

SQL> grant dba to scott;

Grant succeeded.

SQL>
SQL> conn scott/oracle
Connected.
SQL>

8.5、查看数据库当前日志文件及归档信息

SQL> set linesize 120
SQL>
SQL> select GROUP#,THREAD#,SEQUENCE#,ARCHIVED,MEMBERS,status from v$log;

GROUP# THREAD# SEQUENCE# ARC MEMBERS STATUS
———- ———- ———- — ———- —————-
1 1 154 YES 1 INACTIVE
2 1 153 YES 1 INACTIVE
3 1 155 NO 1 CURRENT

SQL>

8.6、创建数据字典文件目录

SQL> begin
dbms_logmnr_d.build(‘007dict.data’,’/u03′);
end;
/ 2 3 4

PL/SQL procedure successfully completed.

SQL>

8.7、创建实验表

SQL> create table xxx as select * from emp;

Table created.

SQL> select * from xxx;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7369 SMITH CLERK 7902 1980-12-17 00:00:00 0 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 0 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 0 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 0 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 0 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 0 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 0 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 0 20
7839 KING PRESIDENT 1981-11-17 00:00:00 0 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 0 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 0 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7900 JAMES CLERK 7698 1981-12-03 00:00:00 0 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 0 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 0 10

14 rows selected.

SQL>
SQL>
SQL> update xxx set job=sal;

14 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL>

8.8、查看数据库当前归档信息

SQL> select GROUP#,THREAD#,SEQUENCE#,ARCHIVED,MEMBERS,status from v$log;

GROUP# THREAD# SEQUENCE# ARC MEMBERS STATUS
———- ———- ———- — ———- —————-
1 1 154 YES 1 INACTIVE
2 1 156 NO 1 CURRENT
3 1 155 YES 1 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,ARCHIVED,MEMBERS,status from v$log;

GROUP# THREAD# SEQUENCE# ARC MEMBERS STATUS
———- ———- ———- — ———- —————-
1 1 157 NO 1 CURRENT
2 1 156 YES 1 ACTIVE
3 1 155 YES 1 ACTIVE

SQL>

8.9、添加要分析的归档文件

SQL> execute dbms_logmnr.add_logfile(‘/u01/app/oracle/flash_recovery_area/OR11G/archivelog/2018_09_20/o1_mf_1_153_ft6k0418_.arc’,DBMS_LOGMNR.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(‘/u01/app/oracle/flash_recovery_area/OR11G/archivelog/2018_09_20/o1_mf_1_154_ft6k04lg_.arc’,DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(‘/u01/app/oracle/flash_recovery_area/OR11G/archivelog/2018_09_20/o1_mf_1_155_ft6mvo0s_.arc’,DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(‘/u01/app/oracle/flash_recovery_area/OR11G/archivelog/2018_09_20/o1_mf_1_156_ft6mwq0j_.arc’,DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(‘/u01/app/oracle/flash_recovery_area/OR11G/archivelog/2018_09_20/o1_mf_1_158_ft6n833c_.arc’,DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(‘/u01/app/oracle/flash_recovery_area/OR11G/archivelog/2018_09_20/o1_mf_1_157_ft6n80o4_.arc’,DBMS_LOGMNR.addfile);

PL/SQL procedure successfully completed.

SQL>

8.10、执行分析

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>’/u03/007dict.data’);

PL/SQL procedure successfully completed.

SQL>

8.11、将分析结果持久化保存

因为 logminer 分析处理的结果只有本 session 能看到，所以可以先用表把数据记录，然后再进行分析：

SQL> Create table df01 as select * from v$logmnr_contents;

Table created.

SQL>
8.12、分析数据表

Oracle 11g LogMiner 解析 redo 日志