Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Feb 28, 2005

Automate Process of LogMiner Implementation

By DatabaseJournal.com Staff



>>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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date