2 Replies Latest reply: Dec 29, 2013 8:04 PM by AKPT RSS

Not getting SCN details in Log Miner

AKPT Newbie
Currently Being Moderated

Oracle 11g

Windows 7

Hi DBA's,

I am not getting the SCN details in log miner. Below are steps for the same:-

 

SQL> show parameter utl_file_dir

 

NAME                                 TYPE        VALUE                         

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

utl_file_dir                         string                                    

SQL> select name,issys_modifiable from v$parameter where name ='utl_file_dir';

 

NAME               ISSYS_MOD                                                                      

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

utl_file_dir    FALSE                                                          

SQL>  alter system set utl_file_dir='G:\oracle11g' scope=spfile;

 

System altered.

 

 

 

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 1071333376 bytes                                                          

Fixed Size                  1334380 bytes                                                          

Variable Size             436208532 bytes                                                          

Database Buffers          629145600 bytes                                                          

Redo Buffers                4644864 bytes                                                          

Database mounted.

Database opened.

 

SQL> show parameter utl_file_dir

 

NAME                                 TYPE        VALUE                                             

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

utl_file_dir                         string      G:\oracle11g\logminer_dir 

                       

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

 

SUPPLEME                                                                                           

--------                                                                                           

NO                                                                                                 

 

SQL>  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

Database altered.

 

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

 

SUPPLEME                                                                                           

--------                                                                                           

YES                                                                                                

 

SQL> /* Minimum supplemental logging is now enabled. */

SQL>

SQL> alter system switch logfile;

 

System altered.

 

SQL> select g.group# , g.status , m.member

  2       from v$log g, v$logfile m

  3       where g.group# = m.group#

  4       and g.status = 'CURRENT';

 

    GROUP# STATUS                                                                                 

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

MEMBER                                                                                             

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

         1 CURRENT                                                                                 

G:\ORACLE11G\ORADATA\MY11G\REDO01.LOG                                                              

                                                                                                   

 

SQL> /* start fresh with a new log file which is the group 1.*/

 

SQL> create table scott.test_logmnr

  2  (id  number,

  3  name varchar2(10)

  4  );

 

Table created.

 

 

SQL> BEGIN

  2    DBMS_LOGMNR_D.build (

  3      dictionary_filename => 'logminer_dic.ora',

  4      dictionary_location => 'G:\oracle11g');

  5  END;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> /*

SQL>   This has recorded the dictionary information into the file

SQL>   "G:\oracle11g\logminer_dic.ora".

SQL> */

SQL> conn scott/

Connected.

SQL> insert into test_logmnr values (1,'TEST1');

 

1 row created.

 

SQL> insert into test_logmnr values (2,'TEST2');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from test_logmnr;

 

        ID NAME                                                                                    

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

         1 TEST1                                                                                   

         2 TEST2                                                                                   

 

SQL> update test_logmnr set name = 'TEST';

 

2 rows updated.

 

SQL> select * from test_logmnr;

 

        ID NAME                                                                                    

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

         1 TEST                                                                                    

         2 TEST                                                                                    

 

SQL> commit;

 

Commit complete.

 

SQL> delete from test_logmnr;

 

2 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from test_logmnr;

 

no rows selected

 

SQL> conn / as sysdba

Connected.

SQL> select g.group# , g.status , m.member

  2       from v$log g, v$logfile m

  3       where g.group# = m.group#

  4       and g.status = 'CURRENT';

 

    GROUP#         STATUS                                         MEMBER                                                                                             

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

         1             CURRENT                           G:\ORACLE11G\ORADATA\MY11G\REDO01.LOG                                                              

                                                                                                   

 

SQL> begin

  2        dbms_logmnr.add_logfile

  3        (

  4         logfilename => 'G:\oracle11g\oradata\my11g\REDO01.LOG',

  5         options     => dbms_logmnr.new

  6        );

  7      

  8       /

 

PL/SQL procedure successfully completed.

 

SQL> select filename from v$logmnr_logs;

 

FILENAME                                                                                           

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

G:\oracle11g\oradata\my11g\REDO01.LOG                                                              

 

PL/SQL procedure successfully completed.

 

 

SQL> BEGIN

  2    -- Start using all logs

  3    DBMS_LOGMNR.start_logmnr (

  4      dictfilename => 'G:\oracle11g\logminer_dic.ora');

  5 

  6   END;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> DROP TABLE myLogAnalysis;

 

Table dropped.

 

SQL> create table myLogAnalysis

  2       as

  3       select * from v$logmnr_contents;

 

Table created.

 

SQL> begin

  2         DBMS_LOGMNR.END_LOGMNR();

  3       end;

  4       /

 

PL/SQL procedure successfully completed.

 

SQL> set lines 1000

SQL> set pages 500

SQL> column scn format a6

SQL> column username format a8

SQL> column seg_name format a11

SQL> column sql_redo format a33

SQL> column sql_undo format a33

SQL> select scn , seg_name , sql_redo , sql_undo

  2  from   myLogAnalysis

  3  where username = 'SCOTT'

  4  AND (seg_owner is null OR seg_owner = 'SCOTT');

 

SCN SEG_NAMESQL_REDO                      SQL_UNDO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
---------- ----------- --------------------------------- ---------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
##########         set transaction read write;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
##########         commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
##########         set transaction read write;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
########## TEST_LOGMNR insert into "SCOTT"."TEST_LOGMNR" delete from "SCOTT"."TEST_LOGMNR"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
("ID","NAME") values ('1','TEST1'  where "ID" = '1' and "NAME" = 'T                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                 );                            EST1' and ROWID = 'AAARjeAAEAAAAD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     PAAA';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
########## TEST_LOGMNR insert into "SCOTT"."TEST_LOGMNR" delete from "SCOTT"."TEST_LOGMNR"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   ("ID","NAME") values ('2','TEST2'  where "ID" = '2' and "NAME" = 'T                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   );                            EST2' and ROWID = 'AAARjeAAEAAAAD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                                     PAAB';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
##########         commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
##########         set transaction read write;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
########## TEST_LOGMNR update "SCOTT"."TEST_LOGMNR" set  update "SCOTT"."TEST_LOGMNR" set                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                   "NAME" = 'TEST' where "NAME" = 'T "NAME" = 'TEST1' where "NAME" = '                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   EST1' and ROWID = 'AAARjeAAEAAAAD TEST' and ROWID = 'AAARjeAAEAAAAD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   PAAA';                        PAAA';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
########## TEST_LOGMNR update "SCOTT"."TEST_LOGMNR" set  update "SCOTT"."TEST_LOGMNR" set                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                   "NAME" = 'TEST' where "NAME" = 'T "NAME" = 'TEST2' where "NAME" = '                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   EST2' and ROWID = 'AAARjeAAEAAAAD TEST' and ROWID = 'AAARjeAAEAAAAD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   PAAB';                        PAAB';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
##########         commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
##########         set transaction read write;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
########## TEST_LOGMNR delete from "SCOTT"."TEST_LOGMNR" insert into "SCOTT"."TEST_LOGMNR"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                    where "ID" = '1' and "NAME" = 'T ("ID","NAME") values ('1','TEST')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   EST' and ROWID = 'AAARjeAAEAAAADP ;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   AAA';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
########## TEST_LOGMNR delete from "SCOTT"."TEST_LOGMNR" insert into "SCOTT"."TEST_LOGMNR"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                    where "ID" = '2' and "NAME" = 'T ("ID","NAME") values ('2','TEST')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   EST' and ROWID = 'AAARjeAAEAAAADP ;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                   AAB';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
##########         commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
##########         set transaction read write;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
##########         commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

 

Kindly correct me,why am i not getting SCN details.

 

Thanks,

AKPT

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points