oracle 11g r2 LogMiner(讲解）.pdf

szrsu

230

19页

4次

2023-04-07

5墨值下载

武汉誉天教育官网 www.yutianedu.cn

誉天 IT 认证下载站：www.51chongdian.net 华中 Oracle 技术交流群 95720285

oracle 日志分析工具 LogMiner 使用

1.设置日期格式

alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

startup force ; 重启后你的日志文件记录的时间格式：yyyy-mm-dd hh24:mi:ss

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual ;

2.添加补充日志

Redo log files are generally used for instance recovery and media recovery. The

data needed for such operations is automatically recorded in the redo log files.

However, a redo-based application may require that additional columns be

logged in the redo log files. The process of logging these additional columns is

called supplemental logging.

By default, Oracle Database does not provide any supplemental logging, which

means that by default LogMiner is not usable. Therefore, you must enable at

least minimal supplemental logging before generating log files which will be

analyzed by LogMiner.

如果数据库需要使用 logminer，就应该添加,只有添加这个日志之后的才能捕获 DML

查询了 metalink，Oracle 在文档 Doc ID: Note:291574.1 中对这个问题进行了详细说明，

如果希望 LOGMNR 可以得到记录，应该设置 SUPPLEMENTAL LOG DATA PRIMARY

KEY 和 UNIQUE INDEX，这样 Oracle 才能确保 LOGMNR 可以获取 SQL 语句：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI

FROM V$DATABASE;

SUP SUP

--- ---

NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE

INDEX) COLUMNS;

数据库已更改。

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI

FROM V$DATABASE;

武汉誉天教育官网 www.yutianedu.cn

誉天 IT 认证下载站：www.51chongdian.net 华中 Oracle 技术交流群 95720285

SUP SUP

--- ---

YES YES

3.开启归档 ,如果没有归档，只能看 online log 的信息

检查是否归档

SQL> archive log list ;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Current log sequence 6

SQL> show parameter recovery 默认归档路径

NAME TYPE VALUE

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

db_recovery_file_dest string +FRA

db_recovery_file_dest_size big integer 3882M 估计 4G

开启归档模式

shutdown immediate

startup mount

alter database archivelog; 启用归档

alter database open; 打开数据库

在次检查

SQL> archive log list ;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

归档文件在哪里？

of 19

5墨值下载

oracle

关注

评论