使用logminer做redolog的分析（做在线日志分析）

来源：互联网发布：屏幕解锁软件编辑：程序博客网时间：2024/05/01 12:04

1、 数据库的参数文件中做以下设置参数添加

添加参数：

log_archive_start=true

log_archive_format=%S.arc

log_archive_dest=/home/oracle/arch

UTL_FILE_DIR=/u02/oralog

写入到spfile中：

SQL> create spfile from pfile='/home/oracle/admin/rac1/pfile/init.ora.882006125233';

File created.

SQL>

2、 启动数据库到归档模式：

确认当前模式：

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /home/oracle/product/9.2.0.4/dbs/arch

Oldest online log sequence 7

Current log sequence 9

SQL>

启动到archive模式：

SQL> alter database archivelog;

Database altered.

SQL>

SQL> alter system archive log start;

System altered.

3、 确定archive环境已经生效：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/arch

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

SQL>

SQL> show parameter utl

NAME TYPE VALUE

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

utl_file_dir string /u02/oralog

SQL> show parameter log_archive;

NAME TYPE VALUE

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

log_archive_dest string /home/oracle/arch

log_archive_dest_1 string

SQL>

SQL> alter database open;

Database altered.

SQL>

4、 确定dbms_logmnr是否存在：

SQL> desc dbms_logmnr

PROCEDURE ADD_LOGFILE

Argument Name Type In/Out Default?

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

LOGFILENAME VARCHAR2 IN

OPTIONS BINARY_INTEGER IN DEFAULT

FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER

Argument Name Type In/Out Default?

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

SQL_REDO_UNDO RAW IN

COLUMN_NAME VARCHAR2 IN DEFAULT

PROCEDURE END_LOGMNR

FUNCTION MINE_VALUE RETURNS VARCHAR2

Argument Name Type In/Out Default?

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

SQL_REDO_UNDO RAW IN

COLUMN_NAME VARCHAR2 IN DEFAULT

PROCEDURE START_LOGMNR

Argument Name Type In/Out Default?

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

STARTSCN NUMBER IN DEFAULT

ENDSCN NUMBER IN DEFAULT

STARTTIME DATE IN DEFAULT

ENDTIME DATE IN DEFAULT

DICTFILENAME VARCHAR2 IN DEFAULT

OPTIONS BINARY_INTEGER IN DEFAULT

SQL> desc dbms_logmnr_d

PROCEDURE BUILD

Argument Name Type In/Out Default?

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

DICTIONARY_FILENAME VARCHAR2 IN DEFAULT

DICTIONARY_LOCATION VARCHAR2 IN DEFAULT

OPTIONS NUMBER IN DEFAULT

PROCEDURE SET_TABLESPACE

Argument Name Type In/Out Default?

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

NEW_TABLESPACE VARCHAR2 IN DEFAULT

DICTIONARY_TABLESPACE VARCHAR2 IN DEFAULT

SPILL_TABLESPACE VARCHAR2 IN DEFAULT

SQL>

5、 定义数据字典：

SQL> execute dbms_logmnr_d.build('oralog.ora','/u02/oralog');

PL/SQL procedure successfully completed.

SQL>

6、 将归档日志添加到字典中：

检查当前日志组为group1：

SQL> /

truncating (as requested) before column FIRST_CHANGE#

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS

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

1 1 10 104857600 1 NO CURRENT 24-O

2 1 8 104857600 1 YES INACTIVE 08-S

3 1 9 104857600 1 YES INACTIVE 08-S

SQL>

执行：

SQL> execute dbms_logmnr.add_logfile('/home/oracle/oradata/rac1/redo01.log',DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>

7、 启动分析：

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/u02/oralog/oralog.ora');

PL/SQL procedure successfully completed.

SQL>

检查数据：

SQL> select count(*) from v$logmnr_contents;

COUNT(*)

----------

11307

SQL> select SQL_REDO,SQL_UNDO from v$logmnr_contents where upper(operation)='UPDATE';

rows will be truncated

SQL_REDO

8、 结束LogMiner会话：

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL>