Automate Process of LogMiner Implementation

>>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:oracleUtilDir”

DEF StartlogName= &StartlogName — EXAMPLE “C:oracleadminora9ArchARC00014.001”

DEF AddLogname1= &AddLogname1 — EXAMPLE “C:oracleadminora9ArchARC00015.001”

DEF AddLogname2= &AddLogname2 — EXAMPLE “C:oracleadminora9ArchARC00016.001”

DEF AddLogname3= &AddLogname3 — EXAMPLE “C:oracleadminora9ArchARC00017.001”

DEF AddLogname4= &AddLogname4 — EXAMPLE “C:oracleadminora9ArchARC00018.001”

DEF FullDictFilename=&FullDictFilename — EXAMPLE “C:oracleUtilDirl_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