Automate Process of LogMiner Implementation

February 28, 2005

>>Script Language and Platform: MS SQL
This scripts helps implement LogMiner. It takes some examples from Unix naming convention and some for Windows.

Just copy this script in a file and run that file on OS level. Otherwise cut and paste portions.

Author: Satish Mehta

#!/bin/bash

#Create a pfile from the existing spfile and then shutdown the database

sqlplus "/as sysdba" >> EOF1
create pfile from spfile;

shutdown immediate;

quit
EOF1

# Adding the archivelog paramters 
#Comment these lines if the database is already in the archivelog mode
export UTLDIR=$ORACLE_BASE/UtilDir
export ARCHIVEPATH=$ORACLE_BASE/admin/$ORACLE_SID/ARCH
echo LOG_ARCHIVE_START=TRUE >> $ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo LOG_ARCHIVE_DEST=$ARCHIVEPATH >> $ORACLE_HOME/dbs/init$ORACLE_SID.ora

#Adding the UTL_FILE_DIR parameter
#Comment the following line if the parameter is already specified

echo UTL_FILE_DIR=$UTILDIR >> $ORACLE_HOME/dbs/init$ORACLE_SID.ora

mkdir $ORACLE_BASE/UtilDir


#starting the database in archivelog mode using pfile
sqlplus "/as sysdba" >> EOF2

startup mount pfile=?/dbs/init?.ora
alter database archivelog;
archive log list;
alter database open;
alter system switch logfile
/
alter system switch logfile
/
create spfile from pfile
/

quit
EOF2



sqlplus "/as sysdba" >> EOF3

PROMPT Please set the following parameters before running this script

DEF Dictfilename=&DICTIONARYFILENAME -- EXAMPLE "l_dictionary.ora"
DEF DictfileDirLocation=DictfileDirLocation -- EXAMPLE "C:\oracle\UtilDir"
DEF StartlogName= &StartlogName -- EXAMPLE "C:\oracle\admin\ora9\Arch\ARC00014.001" 
DEF AddLogname1= &AddLogname1 -- EXAMPLE "C:\oracle\admin\ora9\Arch\ARC00015.001" 
DEF AddLogname2= &AddLogname2 -- EXAMPLE "C:\oracle\admin\ora9\Arch\ARC00016.001" 
DEF AddLogname3= &AddLogname3 -- EXAMPLE "C:\oracle\admin\ora9\Arch\ARC00017.001" 
DEF AddLogname4= &AddLogname4 -- EXAMPLE "C:\oracle\admin\ora9\Arch\ARC00018.001" 
DEF FullDictFilename=&FullDictFilename -- EXAMPLE "C:\oracle\UtilDir\l_dictionary.ora"


@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql

EXECUTE DBMS_LOGMNR_D.BUILD( dictionary_filename => '&&Dictfilename', 
  dictionary_location => '&&DictfileDirLocation')

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 
  '&&StartLogname',Options => dbms_logmnr.NEW); 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 
  '&&AddLogname1',Options => dbms_logmnr.ADDFILE); 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 
  '&&AddLogname2',Options => dbms_logmnr.ADDFILE); 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 
  '&&AddLogname3',Options => dbms_logmnr.ADDFILE); 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 
  '&&AddLogname4',Options => dbms_logmnr.ADDFILE); 


# Start logminer 

EXECUTE DBMS_LOGMNR.START_LOGMNR( DictFileName =>'&&FullDictfilename'); 


# Run queries against the logminer views 

SELECT sql_redo FROM V$LOGMNR_CONTENTS; 

quit
EOF3

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home