Translate

Search This Blog

Showing posts with label log miner. Show all posts
Showing posts with label log miner. Show all posts

tracking DDL,while DB is in noarchivelog mode

You want to tack DDL and your DB is in noarchivelog , attempted as below,[no way but to use catalog in flat file]
SQL> conn / as as sysdba
Connected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '13-apr-2009 12:42:00', -
> ENDTIME => '15-apr-2009 11:55:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);
BEGIN DBMS_LOGMNR.START_LOGMNR( STARTTIME => '13-apr-2009 12:42:00', ENDTIME => '15-apr-2009 11:55:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); END;

*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream


And again below you get error when utry use redo logs for building dict
SQL> EXECUTE DBMS_LOGMNR_D.BUILD ( options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
BEGIN DBMS_LOGMNR_D.BUILD ( options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); END;
*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream

if DB bouce is affordable go ahead as follow:
SQL> alter system set utl_file_dir='c:\ora';
SQL> shutdown immediate
SQL> startup

SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', 'c:\dict', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);
BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING); END;
*
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner sessionORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

seems in much hurry!!

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO01.LOG', OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO02.LOG', OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO03.LOG', OPTIONS => DBMS_LOGMNR.NEW);

[file 3 was current log group file]

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);

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

SQL> create table t1 as select * from v$logmnr_contents;
Table created.

SQL> create table t2(c1 number);
Table created.

SQL> truncate table t2;
Table truncated.

SQL> create table t1_log as select * from v$logmnr_contents;
Table created.

query from, another session SELECT t.session_info,t.sql_redo, t.* FROM t1_log t WHERE UPPER(sql_redo) LIKE UPPER('%truncate%') OR operation LIKE 'DDL'
gives: DDL operations above in SQL>prompt are also tracked

extracting SQLS from redo logs 10g to tracks all/commited changes

I -to track all(commited+non commited changes)

since v$logmmr_contents data is only persistent to session in which log miner is run a table logmined_commited is created to store all extracted information.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

In below options parameters is saving the time of adding individual redo logs/archive logs.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-jan-2008 01:00:00', -
> ENDTIME => '03-jan-2008 21:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.

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

COUNT(*)
----------
98558

SQL> create table extlogs as select * from v$logmnr_contents;

Table created.
------

OR

II -to track commited changes only

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-JAN-2008 01:00:00', -
> ENDTIME => '03-JAN-2008 23:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.


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

COUNT(*)
----------
54994


SQL> create table extlogs as select * from v$logmnr_contents ;

Table created.

SQL> exec DBMS_LOGMNR.END_LOGMNR( );

PL/SQL procedure successfully completed.

sample query from extracted logs:
SELECT SEG_OWNER, SEG_NAME,operation, COUNT(*) AS Hits FROM
sys.logmined_commited
WHERE SEG_OWNER NOT IN('SYS','SYSTEM','WKSYS')
AND TRUNC(timestamp)='01-jan-07'
GROUP BY SEG_OWNER, SEG_NAME,operation
ORDER BY 1,2,3,4

above shows tables which had undergone DMLS

below shows DML type,corresponding DML statement and equivalent UNDO statement to undo DML for above affected objects.

SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM sys.logmined_commited
WHERE SEG_OWNER = 'SEGOWNER'
AND SEG_NAME = 'segment_name'
AND OPERATION = 'UPDATE'
AND USERNAME = 'username';