请教10G的alter database add supplemental log data;

autumn_leaf

原帖由 hhphaipeng 于 2009-12-10 11:59 发表
试验做了，如果不开DML没有。username 和 session_info 也没有

开了之后，DML 有了

补充：客户端需重新连接 。userna 和 session_info 才有。

重新连接？
1、如果以前数据库没开这个选项，
2、然后很多DML
3、现在开这个选项
4、重新连接
此时，能挖掘到2执行过的DML吗？

autumn_leaf

up

autumn_leaf

做了一下实验，好像执行alter database add supplemental log data;之前的DML都是挖不到的

autumn_leaf

做了几个实验，搞得稀里糊涂
我是想弄明白
在执行ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
之前的DML都不能挖掘到了？
假设
1、我先insert一条记录
2、执行ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
3、然后挖，能挖到1的insert吗？

还是必须ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
之后的DML才能挖倒？

opsc

你需要搞明白这个语句是干什么用的，这个官档有。

开启之后，会有一些额外的日志写进redo,所以很清楚你修改之后的dml才会有这些额外记录。

至于会否挖到DML, 记得都可以但是enable才全面，你不去enable那么一些操作会显示不出sql而是显示不支持，例如IMU, IOT操作。

对于session的information, 即使enable, 你也需要挖掘到session初始登陆的log因为只有那个里面有登陆信息，后面的都是根据这个来生成记录的。

hhphaipeng

挖不到

autumn_leaf

原帖由 opsc 于 2009-12-10 16:22 发表
你需要搞明白这个语句是干什么用的，这个官档有。

开启之后，会有一些额外的日志写进redo,所以很清楚你修改之后的dml才会有这些额外记录。

至于会否挖到DML, 记得都可以但是enable才全面，你不去enable那么一些操作会显示不出sql而是显示不支持，例如IMU, IOT操作。

对于session的information, 即使enable, 你也需要挖掘到session初始登陆的log因为只有那个里面有登陆信息，后面的都是根据这个来生成记录的。

谢谢，当然要搞明白了

我现在发现，似乎是这样
在执行alter database add supplemental log data;之前的DML都是挖不到的
只有执行了alter database add supplemental log data;之后的DML才能挖到

[ 本帖最后由 autumn_leaf 于 2009-12-10 17:03 编辑 ]

hhphaipeng

是add 吧

zhou0953

请看官方介绍：
Note: You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information
in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;


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 prior to generating log files which will be analyzed by
LogMiner.

There are two types of database-level supplemental logging: minimal supplemental
logging and identification key logging, as described in the following sections. Minimal
supplemental logging does not impose significant overhead on the database
generating the redo log files. However, enabling database-wide identification key
logging can impose overhead on the database generating the redo log files. Oracle
recommends that you at least enable minimal supplemental logging for LogMiner.

[ 本帖最后由 zhou0953 于 2010-7-27 14:36 编辑 ]

likgui

开了对性能有多大影响？v$logmnr_contents的信息存在哪里，并发高的话是不是影响很大？