Using Oracle8i and Oracle9i Log Miner

1
LA Oracle Users Group

• Using Oracle8i and Oracle9i Log Miner
• Tim Gorman
• (tim_at_sagelogix.com)
• Principal
• SageLogix, Inc.

2
Agenda

• Basic overview of Log Miner
• What is it?
• How to use it?
• Why use it?
• Oracle9i New Features
• Enhancements to support Log Miner as a major
  component of Data Guards Logical Standby
  Database feature

3
What is Log Miner

• Log Miner is a mechanism for examining redo log
  files (online or archived) from any Oracle8,
  Oracle8i, or Oracle9i database
• The database which generated the logs does not
  have to be the database used to examine them
• Oracle server processes perform the I/O on redo
  log files in a Log Miner session
• Session initiated by identifying the log files to
  be read
• Data is retrieved on demand by SQL queries within
  the session on the view VLOGMNR_CONTENTS
• Easy and safe
• Can examine logs in another database altogether
• Only need to generate snapshots of database data
  dictionary periodically

4
Uses for Log Miner

• Debugging or auditing DML or DDL actions
  performed within a specified time period
• Recovering dropped tables
• by finding the exact SCN of the DROP command
• to allow recovery of a CLONE database the precise
  SCN-1, instead of an approximate time of day
• Recovering deleted or updated data
• by finding the relevant REDO_SQL statement(s) and
  running the associated UNDO_SQL command(s)
• Database Replication
• Component of Oracle9i Data Guard Logical Standby
  Database feature

5
Using Log Miner

• PL/SQL packages supplied with the RDBMS to query
  information from (online or archived) redo log
  files
• Oracle data dictionary must have previously been
  exported
• using the DBMS_LOGMNR_D.BUILD procedure
• contents must be valid from when redo logs were
  generated
• exporting allows point-in-time snapshots of data
  dictionary
• Package DBMS_LOGMNR_D supplied with Oracle8i or
  Oracle9i
• Can be installed on lower version databases, such
  as Oracle8
• Filename OH/rdbms/admin/dbmslmd.sql

6
Using Log Miner

• Creating a Log Miner session
• Redo log files must first be added to a list
• DBMS_LOGMNR.ADD_LOGFILE(file-name, options)
• options include
• NEW (clear list and add new file)
• ADDFILE (add file to existing list)
• REMOVEFILE (remove file from existing list)

7
Using Log Miner

• Then, the Log Miner session must be started
• to populate the VLOGMNR_CONTENTS view with
  information from the files in the list
• Procedure DBMS_LOGMNR.START_LOGMNR
• Use parameters start-SCN/stop-SCN or
  start-time/stop-time to restrict analysis to
  certain redo records (default no restriction)
• The redo trail contains only numeric IDs for
  database objects (not symbolic names) so data
  dictionary info is necessary to translate to
  human-readable form
• Online data dictionary
• Flat-file text extract of data dictionary
• Redo stream contains data dictionary extracts
  (Oracle9i only on both source and mining side)

8
Using Log Miner

• OPTIONS parameter in START_LOGMNR procedure
• Oracle8i
• USE_COLMAP
• SKIP_CORRUPTION
• Oracle9i
• SKIP_CORRUPTION
• PRETTY_SQL
• DICT_FROM_ONLINE_CATALOG
• COMMITTED_DATA_ONLY
• DDL_DICT_TRACKING
• DICT_FROM_REDO_LOGS
• NO_SQL_DELIMITER
• CONTINUOUS_MINE

9
Using Log Miner

• Procedure END_LOGMNR
• Finishes a Log Miner session
• Function MINE_VALUE return VARCHAR2
• Parameter SQL_REDO_UNDO
• Parameter COLUMN_NAME
• Returns the value of the specified column in the
  REDO/UNDO SQL
• Returns NULL if column not present or has NULL
  value
• Function COLUMN_PRESENT return NUMBER
• Parameter SQL_REDO_UNDO
• Parameter COLUMN_NAME
• Returns 1 if column is present, 0 if not present

10
DBMS_LOGMNR_D

• BUILD
• New parameter OPTIONS
• STORE_IN_FLAT_FILE
• STORE_IN_REDO_LOGS (Oracle9i)
• Progress of BUILD now visible (via DBMS_OUTPUT)
  when SET SERVEROUTPUT ON enabled in SQLPlus
• SET_TABLESPACE procedure (Oracle9i)
• By default, internal tables used by Log Miner
  reside in the SYSTEM tablespace
• SET_TABLESPACE changes to the specified
  tablespace and moves the tables (if already
  present somewhere else)

11
Using Log Miner

• VLOGMNR_CONTENTS view
• SCN NUMBER(15) system change number
• TIMESTAMP DATE timestamp of redo vector
• THREAD NUMBER redo log thread number
• LOG_ID NUMBER redo log sequence
• XIDUSN NUMBER XID (transaction ID) rollback
  segment
• XIDSLOT NUMBER XID transaction table slot
• XIDSQN NUMBER XID sequence of slot
• RBASQN NUMBER RBA (redo byte address) log seq
• RBABLK NUMBER RBA block within file
• RBABYTE NUMBER RBA byte offset within block
• UBAFIL NUMBER UBA (undo byte address) file
• UBABLK NUMBER UBA block within file
• UBAREC NUMBER UBA record within block
• UBASQN NUMBER UBA sequence of block
• ABS_FILE NUMBER absolute file
• REL_FILE NUMBER relative (to tablespace) file

12
Using Log Miner

• VLOGMNR_CONTENTS view (contd)
• DATA_BLK NUMBER data block address (block )
• DATA_OBJ NUMBER data object
• DATA_DOBJ NUMBER data block data object
• SEG_OWNER VARCHAR2(30) segment owner
• SEG_NAME VARCHAR2(81) segment name
• SEG_TYPE NUMBER type of segment
• SEG_TYPE_NAME VARCHAR2(32) name of type of
  segment
• TABLESPACE_NAME VARCHAR2(30) segments tablespace
• ROW_ID VARCHAR2(18) row ID
• SESSION NUMBER session ID
• SERIAL NUMBER serial of session
• USER_NAME VARCHAR2(30) Oracle account name
• SESSION_INFO VARCHAR2(4000) additional info
• ROLLBACK NUMBER 0commit, 1rollback
• OPERATION VARCHAR2(30) SQL command type
• SQL_REDO VARCHAR2(4000) SQL statement
• SQL_UNDO VARCHAR2(4000) reverse SQL stmt
• INFO VARCHAR2(32) informational msg

13
Enhanced data analysis

• In Oracle9i, LogMiner has been enhanced to
  provide comprehensive Log Analysis for (almost)
  all types of data
• Index-organized tables
• Clustered tables
• Chained and migrated rows
• LOBs and LONGs
• direct-path loaded data
• scalar object types
• All DDL commands
• Still missing
• Collection object types (i.e. VARRAYs and NESTED
  TABLES)

14
Enhanced data analysis

• To support redo logfile-based applications (such
  as logical standby databases), PK values and/or
  before-images may need to be added to the redo
  stream
• Database supplemental logging
• Minimal
• Allows Log Miner to group REDO logs for
  individual DML statements
• ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
• Primary key logging
• Allows identification of rows logically rather
  than using ROWIDS
• Requirement for using Logical Standby database
• ALTER DATABASE ADD SUPPLEMENTAL DATA (PRIMARY
  KEY, UNIQUE KEY) COLUMNS

15
Enhanced data analysis

• Supplement logging (contd)
• Table supplemental logging
• Logs column before-image values
• An application might require that the
  before-image of the entire row be logged
• Not just the columns being changed

16
New V views

• VLOGMNR_CONTENTS
• Very similar to Oracle8i version
• VLOGMNR_DICTIONARY
• New for Oracle9i, shows info about dictionary
  being used (flat-file or online)
• VLOGMNR_LOGS
• New for Oracle9i, shows info about logfiles being
  analyzed
• VLOGMNR_PARAMETERS
• Shows values for current Log Miner session,
  showing parameter values passed to START_LOGMNR

17
Documentation

• Oracle8i/9i Server Administration manual
• Chapter on Using LogMiner to Analyze Online and
  Archived Redo Logs
• Oracle8i/9i Supplied Packages Reference manual
• Chapters on DBMS_LOGMNR and DBMS_LOGMNR_D
• Oracle9i Server Administration manual
• Section on Database Supplemental Logging and
  Table Supplemental Logging
• Oracle9i Data Warehousing Guide manual
• Chapter 15 on Change Data Capture
• Oracle9i Supplied Packages Reference manual
• Chapters on DBMS_LOGMNR_CDC_PUBLISH and
  DBMS_LOGMNR_CDC_SUBSCRIBE

18
QA
Slides downloadable from http//www.EvDBT.com/pape
rs.htm