select * from v$log
select * from v$logfile
exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/aiki/redo03.log',dbms_logmnr.new);
select count(1) from v$logmnr_contents;
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
------------------------------------------
1:基本对象
源数据库
分析数据库
LogMiner字典
2:LonMiner配置需求
1):源数据库和分析数据库必须在相同的硬件平台上
2)分析数据库可以是独立数据库或者与源数据库在同一数据库中
3):分析数据库版本不能低于源数据库的版本
4):分析数据库与源数据库必须有相同的字符集
5):LogMiner字典必须在源数据库中生成
6):重做日志文件
默认情况下无法支持一下特征
索引簇,long和lob数据类型
sql>conn sys/oracle@deom as sysdba
sql>alter database add supplemental log data;
3:数据库配置
mkdir /logmnr
chown -R oracle:dba /logmnr
alter system set utl_file_dir='/logmnr' scope=spfile sid='*';
startup force
4:Use logmnr
create dictionary
1):
execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/logmnr');
这个过程可能会报错identifier 'DBMS_LOGMNR_D.STORE_IN_FLAT_FILE' must be declared
我的数据库版本是10g执行$ORACLE_HOME/rdbms/admin/dbmslmd.sql这个脚本,如果是早期的版本需要执dbmslogmnrd.sql
2):add logfile
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/db_1/dbs/arch1_7_668642272.dbf');
注:如果这个过程有问题,运行这个SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql脚本即可解决
select * from v$logmnr_logs;
3):start analyze
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/logmnr/dictionary.ora');
SQL> select count(*) from v$logmnr_contents; //证明该表中有数据
4):create table as select * from v$logmnr_contents
sql>create table test.logmnr as select * from v$log
desc v$logmnr_contents
username
seg_name
seg_owner
seg_type_name
timestamp
operation
5):end logmnr
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
599
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
SQL> select count(*) from v$logmnr_contents;
select count(*) from v$logmnr_contents
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from
v$logmnr_contents
6):find exact drop time