/*
|| Oracle 10g Availability Improvements - Listing 4
||
|| Contains examples of new Oracle 10g LogMiner and DataGuard Features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| new LogMiner and Dataguard features and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/
 
-----
-- Listing 4.1: Letting the database's control file establish which 
--              redo logs LogMiner needs to complete its work
-----
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI';

SPOOL C:\Listing_41.log

-- Start LogMiner, running from the database's online data dictionary and
-- preparing for several mining attempts
BEGIN
    DBMS_LOGMNR.START_LOGMNR(
         STARTTIME => '02/20/2005 06:00'
        ,ENDTIME => '02/20/2005 12:00'
        ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE
    );
END;
/

-- Find the desired data
SELECT 
     seg_owner
    ,seg_name 
    ,operation
    ,sql_redo
  FROM v$logmnr_contents
 WHERE operation = 'INSERT'
   AND seg_owner = 'HR';
     
-- Reissue the START_LOGMNR directive for a new starting and ending period,
-- but this time based on specified starting and ending SCNs
BEGIN
    DBMS_LOGMNR.START_LOGMNR(
         STARTSCN => 2266500
        ,ENDSCN => 2266679
        ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE
    );
END;
/

-- Find the desired data
SELECT 
     seg_owner
    ,seg_name 
    ,operation
    ,sql_redo
  FROM v$logmnr_contents
 WHERE operation = 'INSERT'
   AND seg_owner = 'HR';

-- End the LogMiner session
EXEC DBMS_LOGMNR.END_LOGMNR;

SPOOL OFF

----- 
-- Listing 4.2: Making LogMiner SQL output "prettier"
-----
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI';

SPOOL C:\Listing_42.log

BEGIN 
    -- Start LogMiner, running from the database's online data dictionary and
    -- preparing for several mining attempts
    DBMS_LOGMNR.START_LOGMNR(
         STARTTIME => '02/20/2005 06:05'
        ,ENDTIME => '02/20/2005 06:15'
        ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.NO_ROWID_IN_STMT + DBMS_LOGMNR.PRINT_PRETTY_SQL
    );
END;
/        
    -- Find the desired data
    SELECT 
         sql_redo
      FROM v$logmnr_contents
     WHERE seg_owner = 'HR';
     
    -- End the LogMiner session
BEGIN
    DBMS_LOGMNR.END_LOGMNR;
END;
/

SPOOL OFF    

----- 
-- Listing 4.3: Creating a LogMiner data dictionary and storing it within
--              the online redo log files
-----
BEGIN 
    DBMS_LOGMNR_D.BUILD(OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
END;
/

----- 
-- Listing 4.4: LOG_ARCHIVE_DEST_n and the VALID_FOR directive
-----
...
# Set up a log destination for use in any role
LOG_ARCHIVE_DEST_1 = 'LOCATION=c:\oracle\oradata\archive VALID_FOR=(ALL_LOGFILES, ALL_ROLES)' 
# Specify an  archive redo log destination for use in only the primary database role
LOG_ARCHIVE_DEST_2 = 'LOCATION=g:\oracle\oradata\archive VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLES)' 
# Set up a standby redo log destination for use in only a logcial standby database role
LOG_ARCHIVE_DEST_3 = 'LOCATION=h:\oracle\oradata\archive VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLES)' 
...