x

To extract a LogMiner dictionary to the redo log files, the Oracle database must be open and set to Archive Log mode. Archiving must also be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed.

Enter the following command at the sql command prompt to issue a list of archive logs:

sql>archive log list

Enter the following commands to set the source Oracle database to Archive Log mode:

sql> alter system set log_archive_dest_1 = 'LOCATION=/usr/tmp/arch';
sql> shutdown immedaite;
sql> startup mount;
sql> alter database archivelog;

Note: If the Oracle database has multiple archive log destinations, you can only configure one archive log destination and defer all others.

Enter the following command to defer one archive log destination:

sql>alter system set log_archive_dest_state_1=defer scope=both;

Or you can add one log_archive_dest filter when query related archive log files later, like

sql>SELECT NAME FROM V$ARCHIVED_LOG

where:

to_char(COMPLETION_TIME,'DD-MON-YYYY HH24:MI:SS')

Is between '07-feb-2010 10:00:00' and '08-feb-2010 10:00:00'.

NAME

Can be something like '/rdbms/ora102/archive2%';

x

Enter a command using the following format at the sql command prompt to grant a role to your application core schema:

sql>grant execute_catalog_role to scott;

x

You must also set the initialization parameter (UTL_FILE_DIR) in the initialization parameter file. For example, enter the following commands at the sql command prompt:

sql>alter system set utl_file_dir='/usr/tmp/logmine' scope=spfile; 
sql>Shutdown immediate
sql>startup
sql>CREATE OR REPLACE DIRECTORY public_access AS '/usr/tmp/logmine';
sql>GRANT read, write ON DIRECTORY public_access TO public;

x

You can enable supplemental logging at the database level or table level by using the commands that are described in this section.

Database Level:

sql>alter database add supplemental log data;

Table Level:

sql>alter table xxx add supplemental log data (ALL) columns;

All tables in a schema:

sql>set lines 90 pages 0 termout off feedback off verify off
sql>select 'alter table '|| owner ||'.' ||table_name|| ' add supplemental log data (ALL) columns;'from dba_tables where owner = 'SCOTT';

x

Create a dictionary file on the source database using the following command:

begin
       dbms_logmnr_d.build( dictionary_filename =>'dictionary.ora',
                            dictionary_location => '/usr/tmp/logmine');
end;
/

Or:

exec dbms_logmnr_d.build('dictionary.ora','/usr/tmp/logmine');