1 / 23

IOUG-A Live 2003

www.SageLogix.com. Agenda. Basic overview of Log MinerWhat is it?How to use it?Why use it?Oracle9i New FeaturesEnhancements to support Log Miner as a major component of Data Guard's Logical Standby Database and Streams feature. www.SageLogix.com. What is Log Miner. Log Miner is a mechanism for

vittorio
Download Presentation

IOUG-A Live 2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. www.SageLogix.com Paper #536 Oracle Log Miner Too Clever For Words Tim Gorman (tim@sagelogix.com) Principal SageLogix, Inc. IOUG-A “Live 2003”

    2. www.SageLogix.com 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 Guard’s Logical Standby Database and Streams feature

    3. www.SageLogix.com 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 V$LOGMNR_CONTENTS Easy and safe Can examine logs in another database altogether Only need to generate snapshots of database data dictionary periodically

    4. www.SageLogix.com 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 Oracle9i Data Guard Logical Standby Database Oracle9i Streams

    5. www.SageLogix.com 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. www.SageLogix.com Using Log Miner Package DBMS_LOGMNR_D BUILD New parameter OPTIONS STORE_IN_FLAT_FILE Also requires parameters DICTIONARY_FILENAME and DICTIONARY_LOCATION STORE_IN_REDO_LOGS (Oracle9i) Progress of BUILD execution now visible (via DBMS_OUTPUT) when SET SERVEROUTPUT ON enabled in SQL*Plus 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)

    7. www.SageLogix.com Using Log Miner SET_TABLESPACE should be a one-time only occurrence Moving Log Miner tables from SYSTEM to TOOLS tablespace, for example BUILD should be used regularly to ensure that any future Log Miner sessions will have accurate data dictionary mapping information available Using Log Miner while viewing data without data dictionary translations is miserable! All schemas are named “UNKNOWN” All tables are named “OBJ #” All columns are named “COL #”

    8. www.SageLogix.com 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)

    9. www.SageLogix.com Using Log Miner Then, the Log Miner session must be started to populate the V$LOGMNR_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 to certain redo records default: no restrictions, use the full list of files 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)

    10. www.SageLogix.com 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 Procedure END_LOGMNR Finishes a Log Miner session

    11. www.SageLogix.com Using Log Miner USE_COLMAP functionality in Oracle8i Requires use of logmnr.opt file Must be located in the same location (directory) as the data dictionary flat-file Format of column-mapping entries: colmap = schema table (1, column [, 2, column [, …]] ) maps specified columns to five place-holder columns in the end of the V$LOGMNR_CONTENTS view PHn_NAME VARCHAR2(32) PHn_REDO VARCHAR2(4000) PHn_UNDO VARCHAR2(4000) select * from v$logmnr_contents where seg_owner = ‘SCOTT’ and seg_name = ‘EMP’ and ph1_name = ‘SAL’ and ph1_redo = ‘100000’ and ph1_undo = ‘75000’;

    12. www.SageLogix.com Using Log Miner Oracle9i does it differently, better… Function MINE_VALUE return VARCHAR2 Parameter SQL_REDO_UNDO Flag: constants REDO_VALUE or UNDO_VALUE Parameter COLUMN_NAME Fully-qualified column name Returns string with column value Dates always in format DD-MON-YYYY HH24:MI:SS.SS Returns NULL if column not present or column has NULL value How to tell the difference, when NULL is returned? Function COLUMN_PRESENT return NUMBER Parameter SQL_REDO_UNDO Parameter COLUMN_NAME Returns 1 if column is present, 0 if not present

    13. www.SageLogix.com Using Log Miner So, what would this Oracle9i query return? select dbms_logmnr.mine_value(redo_value, 'SCOTT.EMP.SAL') from v$logmnr_contents where dbms_logmnr.mine_value(redo_value, 'SCOTT.EMP.SAL') = ‘0’ or (dbms_logmnr.mine_value(redo_value, 'SCOTT.EMP.SAL') IS NULL and dbms_logmnr.column_present(redo_value, 'SCOTT.EMP.SAL') = 1)

    14. www.SageLogix.com Using Log Miner V$LOGMNR_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#

    15. www.SageLogix.com Using Log Miner V$LOGMNR_CONTENTS view (cont’d) 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) segment’s 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 0=commit, 1=rollback OPERATION VARCHAR2(30) SQL command type SQL_REDO VARCHAR2(4000) SQL statement SQL_UNDO VARCHAR2(4000) “reverse” SQL stmt INFO VARCHAR2(32) informational msg

    16. www.SageLogix.com 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)

    17. www.SageLogix.com 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;

    18. www.SageLogix.com Enhanced data analysis Supplement logging (cont’d) 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

    19. www.SageLogix.com New V$ views V$LOGMNR_DICTIONARY Shows info about dictionary being used (flat-file or online) V$LOGMNR_LOGS Shows info about log files being analyzed V$LOGMNR_LOGFILE Not documented! Shows info about logfiles being analyzed V$LOGMNR_PARAMETERS Shows options chosen for current Log Miner session Parameter values passed to START_LOGMNR V$LOGMNR_SESSION Not documented! Information about each Log Miner session, including SESSION_ID for joining back to the V$SESSION view. Can be queried from outside an active Log Miner session.

    20. www.SageLogix.com New V$ views V$LOGMNR_PROCESS Not documented! Information about the Oracle server process underlying the current Log Miner session. Cannot be queried from outside an active Log Miner session. V$LOGMNR_TRANSACTION Not documented! Information about the transaction listed within the current Log Miner session. Cannot be queried from outside an active Log Miner session V$LOGMNR_REGION Not documented! Information about internal memory structures used within the current Log Miner session. Cannot be queried from outside an active Log Miner session.

    21. www.SageLogix.com New V$ views V$LOGMNR_CALLBACK Not documented! Information about internal memory structures used within the current Log Miner session. Cannot be queried from outside an active Log Miner session. V$LOGMNR_STATS Not documented! Usage statistics about each Log Miner session, intended to be joined to V$LOGMNR_SESSION and/or V$SESSION.

    22. www.SageLogix.com 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” (or “Streams”) Oracle9i Supplied Packages Reference manual Chapters on “DBMS_LOGMNR_CDC_PUBLISH” and “DBMS_LOGMNR_CDC_SUBSCRIBE”

    23. www.SageLogix.com Documentation Metalink note #148616.1 – Oracle9i Log Miner New Features Good overview with examples PL/SQL source files located in $ORACLE_HOME/rdbms/admin File dbmslm.sql Package header source for DBMS_LOGMNR File dbmslmd.sql Package header source for DBMS_LOGMNR_D File dbmslms.sql Package header source for DBMS_LOGMNR_SESSION More streamlined repackaging of DBMS_LOGMNR Bug #2137007 filed to add some documentation this to standard doc set – still not visible (Feb 2003)

    24. www.SageLogix.com Q&A

More Related