NNP NAHAR BLOG: Examples

SELECT log_mode FROM gv$database;

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

SQL> col SQL_REDO format a30
SQL> col SQL_UNDO format a30
SQL> column USERNAME format a15
you must have the SELECT ANY TRANSACTION privilege to query V$LOGMNR_CONTENTS.

Examples Using LogMiner:

Example 1: Finding All Modifications in the Last Archived Redo Log File

Step 1:
Determine which redo log file was most recently archived.

This example assumes that you know that you want to mine the redo log file that was most recently archived.

SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME                         
-------------------------------------------
/oracle/DATABASE_NAME/archivelog/2008_08_31/o1_mf_1_8554_4cngdomq_.arc

Step 2:

Specify the list of redo log files to be analyzed.

Specify the redo log file that was returned by the query in Step 1. The list will consist of one redo log file.

BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oracle/database_name/archivelog/2008_08_31/o1_mf_1_8554_4cngdomq_.arc');
END;
/

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
OPTIONS => DBMS_LOGMNR.NEW);

Step 3:

Start LogMiner.

SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

Step 4:

Query the V$LOGMNR_CONTENTS view.

Note that there are four transactions (two of them were committed within the redo log file being analyzed, and two were not).

The output shows the DML statements in the order in which they were executed; thus transactions interleave among themselves.

SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID,
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');

USR    XID                     SQL_REDO                                                 SQL_UNDO
----       ---------                ---------------------                                           -------------------------------
HR     1.11.1476           set transaction read write;

HR     1.11.1476  insert into "HR"."EMPLOYEES"(             delete from "HR"."EMPLOYEES"
               "EMPLOYEE_ID","FIRST_NAME",                where "EMPLOYEE_ID" = '306'
               "LAST_NAME","EMAIL",                             and "FIRST_NAME" = 'Nandini'
               "PHONE_NUMBER","HIRE_DATE",                and "LAST_NAME" = 'Shastry'
               "JOB_ID","SALARY",                                       and "EMAIL" = 'NSHASTRY'
               "COMMISSION_PCT","MANAGER_ID",    and "PHONE_NUMBER" = '1234567890'
               "DEPARTMENT_ID") values                           and "HIRE_DATE" = TO_DATE('10-JAN-2003
               ('306','Nandini','Shastry',                         13:34:43', 'dd-mon-yyyy hh24:mi:ss')
               'NSHASTRY', '1234567890',                     and "JOB_ID" = 'HR_REP' and
               TO_DATE('10-jan-2003 13:34:43',                 "SALARY" = '120000' and
               'dd-mon-yyyy hh24:mi:ss'),              "COMMISSION_PCT" = '.05' and
               'HR_REP','120000', '.05',                           "DEPARTMENT_ID" = '10' and
               '105','10');                                                    ROWID = 'AAAHSkAABAAAY6rAAO';


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

SQL> col SQL_REDO format a30
SQL> col SQL_UNDO format a30
SQL> column USERNAME format a15

SELECT username,session# SID, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID,
SQL_REDO, SQL_UNDO,to_char(timestamp,'mm/dd/yy hh24:mi:ss')timestamp
FROM V$LOGMNR_CONTENTS WHERE username IN ('PROD7');

Step 5 End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();


-------------------------------------------------
Example 2: Grouping DML Statements into Committed Transactions

Step 1:

SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME                         
-------------------------------------------
/oracle/database_name/archivelog/2008_08_31/o1_mf_1_8554_4cngdomq_.arc

Step 2:

BEGIN DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/oracle/database_name/archivelog/2008_08_31/o1_mf_1_8554_4cngdomq_.arc',
OPTIONS => DBMS_LOGMNR.NEW);
END;
/

Step 3:

Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY);

Step 4:
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_REDO,
SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');
;

Example 4: Using the LogMiner Dictionary in the Redo Log Files

This example shows how to use the dictionary that has been extracted to the redo log files.

When you use the dictionary in the online catalog, you must mine the redo log files in the same database that generated them.

Using the dictionary contained in the redo log files enables you to mine redo log files in a different database.

This example assumes that you know that you want to mine the redo log file that was most recently archived.

SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME                                                                                                        SEQUENCE#
------------------------------------------------------------------------------------------------------            ----------------
/oracle/database_name/archivelog/2008_08_31/o1_mf_1_8568_4cpdcgcw_.arc                    8568


SELECT NAME, SEQUENCE#, DICTIONARY_BEGIN d_beg, DICTIONARY_END d_end
FROM V$ARCHIVED_LOG
WHERE SEQUENCE# = (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE DICTIONARY_END = 'YES' and SEQUENCE# <= 8568);

Example 6: Filtering Output by Time Range

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

To filter out redo records based on timestamp values is by specifying the time range in the DBMS_LOGMNR.START_LOGMNR procedure call.

If you want to see the archive log file name from V$ARCHIVED_LOG

SELECT NAME,to_char(FIRST_TIME,'mm/dd/yy hh24:mi:ss') Time FROM V$ARCHIVED_LOG
WHERE FIRST_TIME like ('01-SEP-08%');

Step 1: Create a list of redo log files to mine.

The subsequent SQL EXECUTE statement calls the procedure and specifies the starting date 01-SEP-2008:

-- my_add_logfiles
-- Add all archived logs generated after a specified start_time.

CREATE OR REPLACE PROCEDURE my_add_logfiles (in_start_time  IN DATE) AS
CURSOR  c_log IS 
SELECT NAME FROM V$ARCHIVED_LOG 
WHERE FIRST_TIME >= in_start_time;

count      pls_integer := 0;
my_option  pls_integer := DBMS_LOGMNR.NEW;

BEGIN
  FOR c_log_rec IN c_log
  LOOP
    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => c_log_rec.name, 
                            OPTIONS => my_option);
    my_option := DBMS_LOGMNR.ADDFILE;
    DBMS_OUTPUT.PUT_LINE('Added logfile ' || c_log_rec.name);
  END LOOP;
END;
/

EXECUTE my_add_logfiles(in_start_time => '01-sep-2008');

Step 2:

Query the V$LOGMNR_LOGS to see the list of redo log files.

SQL> col NAME format a70

SQL> SELECT FILENAME name, LOW_TIME start_time, FILESIZE bytes FROM V$LOGMNR_LOGS;

Query the V$LOGMNR_LOGS to see the list of redo log files.

i.
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);

Or

ii.

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

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   STARTTIME => '01-sep-2008 15:00:00', -
   ENDTIME   => '02-sep-2008 16:00:00', -
   OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
                DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                DBMS_LOGMNR.PRINT_PRETTY_SQL);


To avoid the need to specify the date format in the call to the PL/SQL DBMS_LOGMNR.START_LOGMNR procedure, you can use the SQL ALTER SESSION SET NLS_DATE_FORMAT statement first, as shown in the following example.

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

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME => '/oracle/database/dictionary.ora', -
STARTTIME => '01-Jan-1998 08:30:00', -
ENDTIME => '01-Jan-1998 08:45:00'-
OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE);

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

select username, timestamp,scn_to_timestamp(SCN),
seg_type_name, seg_name, table_space, session# SID, serial# , operation
from v$logmnr_contents
where table_name = 'LOGIN' and
(scn_to_timestamp(SCN))> TO_date ('27-AUG-08 00:22:39', 'dd-mon-yyyy hh24:mi:ss');--run saturn

select username,scn_to_timestamp(SCN) as DataTime ,
seg_type_name, seg_name, session# SID, serial# , operation
from v$logmnr_contents
where table_name = 'LOGIN' and
(scn_to_timestamp(SCN))> TO_date ('27-AUG-08 00:22:39', 'dd-mon-yyyy hh24:mi:ss');

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

SQL> set lines 200
SQL> column USERNAME format a20
SQL> column SEG_NAME format a15
SQL> column SEG_TYPE_NAME format a15
SQL> column TABLE_SPACE format a15

select username,scn_to_timestamp(SCN)as DataTime,
seg_type_name, seg_name, session# SID, operation
from v$logmnr_contents
where table_name = 'LOGIN'
and (scn_to_timestamp(SCN))> TO_date ('27-AUG-08 01:00:00', 'dd-mon-yyyy hh24:mi:ss')
and (scn_to_timestamp(SCN))<>


select username,scn_to_timestamp(SCN)as DataTime,timestamp,
seg_type_name, seg_name, session# SID, operation
from v$logmnr_contents
where  username in ('prod7');

select username,timestamp,seg_type_name, seg_name, session# SID, operation
from v$logmnr_contents
where table_name = 'LOGIN';

----test

select username,timestamp,seg_type_name, seg_name, session# SID, operation
from v$logmnr_contents
where table_name = 'LOGIN'
and timestamp > TO_date ('01-SEP-2008 20:00:00', 'dd-mon-yyyy hh24:mi:ss')
and timestamp <>

9.10.2008

Examples

Examples Using LogMiner:

Example 4: Using the LogMiner Dictionary in the Redo Log Files

Example 6: Filtering Output by Time Range