Database Tech Blog

sample rman_unregister_db.ksh file

2010-03-08T19:57:00.002-08:00

#!/bin/ksh
##
## Script Name: rman_unregister_db.ksh
## Author : Ramakrishna Nemani
##
## Unregister a given database with rman catalog
##
##

. ~/.profile
. ./rmanids.sh

##
## Make sure ORACLE_SID is passed as an argument
##

if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi

export ORACLE_SID=$1
rman << EOF
connect target ${rmanid}/${rmanpw} ;
connect catalog ${catlid}/${catlpw}@${ORACLE_CATL} ;
unregister database noprompt ;
EOF

export rman_rtnval=$?
echo $rman_rtnval
if [[ $rman_rtnval = 0 ]]
then
echo $ORACLE_SID successfully registered with rman catalog
else
echo $ORACLE_SID could not be registered with rman catalog
fi

## -------------------------------------------------------------------------------
## End of rman_unregister_db.ksh
## -------------------------------------------------------------------------------

sample rman_del.ksh file

2010-03-08T19:57:00.000-08:00

#!/bin/ksh

##
## script name : rman_del.ksh
## Author : Ramakrishna Nemani
## This script deletes obsolete backups
## for the specified ORACLE_SID
##
. ~/.profile
. /apps/oracle/admin/rman/rmanids.sh

##
## Make sure ORACLE_SID is passed as an argument
##

if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi

export ORACLE_SID=$1

LOG_DIR="/apps/oracle/admin/rman"
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
EMAIL_INFO_FILE="${LOG_DIR}/${ORACLE_SID}_email.cfg"
SENDER="rman@`hostname"
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one for keeping the logs
## The LOGFILE will have log from just the latest run
## The LOGARCFILE will have the cumulative log
##

## LOGFILE="${LOG_DIR}/rman_del_$CURR_DATE.log"

LOGFILE="${LOG_DIR}/rman_del.$ORACLE_SID.log"
LOGARCFILE="${LOG_DIR}/rman_del.$ORACLE_SID.log.arc"

##
## using two seperate files instead of one for keeping the errors
## The EMAILFILE will have errors if any from just the latest run
## The EMAILARCFILE will have the cumulative errors
##

EMAILFILE="${LOG_DIR}/rman_del.$ORACLE_SID.email"
EMAILARCFILE="${LOG_DIR}/rman_del.$ORACLE_SID.email.arc"

##
## Make sure no other rman process is running for this ORACLE_SID
##

echo --------------------------------------------------------- > $LOGFILE
date >> $LOGFILE
echo ---------------- rman_del.ksh started ------------------ >> $LOGFILE

while [ 1 ]
do

if [ -e $LOCKFILE ]
then

##
## Some rman process is running. Check again after 3 minutes
##
echo "$LOCKFILE exists. Will check again after 3 minutes" >> $LOGFILE
echo >> $LOGFILE
sleep 180

else

##
## No rman process is running. Create a lock file
##

echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
break
fi

done

rman <<EOF >> $LOGFILE
connect target ${rmanid}/${rmanpw} ;

CONFIGURE DEVICE TYPE sbt PARALLELISM 4 ;
CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;

SHOW ALL ;
DELETE NOPROMPT OBSOLETE;

CONFIGURE DEVICE TYPE sbt clear ;
CONFIGURE CHANNEL DEVICE TYPE sbt clear ;
REPORT SCHEMA ;

EXIT ;

EOF

rmanstatus=$?

if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed in the backup step for $ORACLE_SID !!!" >> $EMAILFILE

echo >> $EMAILFILE
date >> $EMAILFILE
echo "rman error!!! ..." >> $EMAILFILE
echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
echo "Please fix the error and remove the lock file" >> $EMAILFILE
echo "before re-running the script again!!!" >> $EMAILFILE
echo >> $EMAILFILE

grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-' $LOGFILE >> $EMAILFILE
grep -i 'sbt' $LOGFILE >> $EMAILFILE
grep -i 'tsm' $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

exit 12
fi
##
## sync up the rman catalog
##
rman << EOF >> $LOGFILE
CONNECT TARGET ${rmanid}/${rmanpw} ;
CONNECT CATALOG ${catlid}/${catlpw}@${ORACLE_CATL} ;
RESYNC CATALOG;
EOF

rmanstatus=$?

if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed in the resync step for $ORACLE_SID !!!" >> $EMAILFILE

echo >> $EMAILFILE
date >> $EMAILFILE
echo "rman error!!! ..." >> $EMAILFILE
echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
echo "Please fix the error and remove the lock file" >> $EMAILFILE
echo "before re-running the script again!!!" >> $EMAILFILE
echo >> $EMAILFILE

grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-' $LOGFILE >> $EMAILFILE
grep -i 'sbt' $LOGFILE >> $EMAILFILE
grep -i 'tsm' $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

exit 12
fi

echo -------------------------------------------------------------- >> $LOGFILE
date >> $LOGFILE
echo ---------------- rman_del.ksh completed ---------------- >> $LOGFILE
echo >> $LOGFILE

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script completed successfully for $ORACLE_SID !!!" >> $EMAILFILE
cat $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

cat $LOGFILE >> $LOGARCFILE

##
## Remove lock file to allow other rman processs for
## this ORACLE_SID to run
##

rm $LOCKFILE

## ---------------------------------------------------------------------
## End of rman_del.ksh
## ---------------------------------------------------------------------

sample rman_bkup_arch.ksh file

2010-03-08T19:55:00.002-08:00

#!/bin/ksh

##
## script name : rman_bkup_arch.ksh
## Author : Ramakrishna Nemani
##
## This script backsup archived log files. It takes ORACLE_SID as an
## argument and backs up the archived log files for the specified
## ORACLE_SID
##
. ~/.profile
. /apps/oracle/admin/rman/rmanids.sh

##
## Make sure ORACLE_SID is passed as an argument
##

if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi

export ORACLE_SID=$1

LOG_DIR="/apps/oracle/admin/rman"
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
EMAIL_INFO_FILE="${LOG_DIR}/${ORACLE_SID}_email.cfg"
SENDER="rman@`hostname"
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one for keeping the logs
## The LOGFILE will have log from just the latest run
## The LOGARCFILE will have the cumulative log
##

## LOGFILE="${LOG_DIR}/rman_bkup_arch_$CURR_DATE.log"

LOGFILE="${LOG_DIR}/rman_bkup_arch.$ORACLE_SID.log"
LOGARCFILE="${LOG_DIR}/rman_bkup_arch.$ORACLE_SID.log.arc"

##
## using two seperate files instead of one for keeping the errors
## The EMAILFILE will have errors if any from just the latest run
## The EMAILARCFILE will have the cumulative errors
##

EMAILFILE="${LOG_DIR}/rman_bkup_arch.$ORACLE_SID.email"
EMAILARCFILE="${LOG_DIR}/rman_bkup_arch.$ORACLE_SID.email.arc"

##
## Make sure no other rman process is running for this ORACLE_SID
##

echo --------------------------------------------------------- > $LOGFILE
date >> $LOGFILE
echo ---------------- rman_bkup_arch.ksh started ------------------ >> $LOGFILE

while [ 1 ]
do

if [ -e $LOCKFILE ]
then

##
## Some rman process is running. Check again after 3 minutes
##
echo "$LOCKFILE exists. Will check again after 3 minutes" >> $LOGFILE
echo >> $LOGFILE
sleep 180

else

##
## No rman process is running. Create a lock file
##

echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
break
fi

done

rman <<EOF >> $LOGFILE
CONNECT TARGET ${rmanid}/${rmanpw} ;

SHOW ALL ;

BACKUP ARCHIVELOG ALL DELETE INPUT;

CONFIGURE DEVICE TYPE sbt PARALLELISM 4 ;
CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO '%F';

BACKUP DEVICE TYPE SBT BACKUPSET ALL ;

CONFIGURE DEVICE TYPE sbt clear ;
CONFIGURE CHANNEL DEVICE TYPE sbt clear ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT CLEAR ;

REPORT SCHEMA ;
REPORT UNRECOVERABLE;

EOF

rmanstatus=$?

if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed in the backup step for $ORACLE_SID !!!" >> $EMAILFILE

echo >> $EMAILFILE
date >> $EMAILFILE
echo "rman error!!! ..." >> $EMAILFILE
echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
echo "Please fix the error and remove the lock file" >> $EMAILFILE
echo "before re-running the script again!!!" >> $EMAILFILE
echo >> $EMAILFILE

grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-' $LOGFILE >> $EMAILFILE
grep -i 'sbt' $LOGFILE >> $EMAILFILE
grep -i 'tsm' $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

exit 12
fi
##
## sync up the rman catalog
##
rman << EOF >> $LOGFILE
CONNECT TARGET ${rmanid}/${rmanpw} ;
CONNECT CATALOG ${catlid}/${catlpw}@${ORACLE_CATL} ;
RESYNC CATALOG;
EOF

rmanstatus=$?

if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed in the resync step for $ORACLE_SID !!!" >> $EMAILFILE

echo >> $EMAILFILE
date >> $EMAILFILE
echo "rman error!!! ..." >> $EMAILFILE
echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
echo "Please fix the error and remove the lock file" >> $EMAILFILE
echo "before re-running the script again!!!" >> $EMAILFILE
echo >> $EMAILFILE

grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-' $LOGFILE >> $EMAILFILE
grep -i 'sbt' $LOGFILE >> $EMAILFILE
grep -i 'tsm' $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

exit 12
fi

echo -------------------------------------------------------------- >> $LOGFILE
date >> $LOGFILE
echo ---------------- rman_bkup_arch.ksh completed ---------------- >> $LOGFILE
echo >> $LOGFILE

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script completed successfully for $ORACLE_SID !!!" >> $EMAILFILE
cat $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

cat $LOGFILE >> $LOGARCFILE

##
## Remove lock file to allow other rman processs for
## this ORACLE_SID to run
##

rm $LOCKFILE

## ---------------------------------------------------------------------
## End of rman_bkup_arch.ksh
## ---------------------------------------------------------------------

Sample rman backup script that uses catalog

2010-03-08T19:55:00.000-08:00

#!/bin/ksh

## ---------------------------------------------------------------------
## script name : rman_bkup.ksh
## Author : Ramakrishna Nemani
##
##
## This script backsup the data files, archived log files and control
## file and spfile. It takes ORACLE_SID as an argument and backs up the
## files for the specified ORACLE_SID
## This script uses rman catalog.
##
##
## ---------------------------------------------------------------------

. ~/.profile
##
## Source the rman user ids and passwords from rmanids.sh
##
. /apps/oracle/admin/rman/rmanids.sh

##
## Make sure ORACLE_SID is passed as an argument
##

if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi

export ORACLE_SID=$1

LOG_DIR="/apps/oracle/admin/rman"
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
EMAIL_INFO_FILE="${LOG_DIR}/${ORACLE_SID}_email.cfg"
SENDER="rman@`hostname"
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one for keeping the logs
## The LOGFILE will have log from just the latest run
## The LOGARCFILE will have the cumulative log
##

LOGFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.log"
LOGARCFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.log.arc"

##
## using two seperate files instead of one for keeping the errors
## The EMAILFILE will have errors if any from just the latest run
## The EMAILARCFILE will have the cumulative errors
##

EMAILFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.email"
EMAILARCFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.email.arc"

##
## Make sure no other rman process is running for this ORACLE_SID
##

echo --------------------------------------------------------- > $LOGFILE
date >> $LOGFILE
echo ---------------- rman_bkup.ksh started ------------------ >> $LOGFILE

while [ 1 ]
do

if [ -e $LOCKFILE ]
then

##
## Some rman process is running. Check again after 3 minutes
##
echo "$LOCKFILE exists. Will check again after 3 minutes" >> $LOGFILE
echo >> $LOGFILE
sleep 180

else

##
## No rman process is running. Create a lock file
##

echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
break
fi

done

echo $ORACLE_SID >> $LOGFILE
whoami >> $LOGFILE
which rman >> $LOGFILE

##
## Backup database and archive logs
##

rman <<EOF >> $LOGFILE

CONNECT TARGET ${rmanid}/${rmanpw} ;

SHOW ALL ;

BACKUP DATABASE FILESPERSET 1 ;
BACKUP ARCHIVELOG ALL DELETE INPUT;

CONFIGURE DEVICE TYPE sbt PARALLELISM 4 ;
CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO '%F';

BACKUP DEVICE TYPE SBT BACKUPSET ALL ;
DELETE NOPROMPT OBSOLETE
RECOVERY WINDOW OF 15 DAYS
DEVICE TYPE SBT ;

CONFIGURE DEVICE TYPE sbt clear ;
CONFIGURE CHANNEL DEVICE TYPE sbt clear ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT CLEAR ;

DELETE NOPROMPT OBSOLETE REDUNDANCY = 3 DEVICE TYPE = DISK ;

REPORT SCHEMA ;
REPORT UNRECOVERABLE ;

EOF

rmanstatus=$?

##
## Check rman exit status and email incase of a failure
##

if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed in the backup step for $ORACLE_SID !!!" >> $EMAILFILE

echo >> $EMAILFILE
date >> $EMAILFILE
echo "rman error!!! ..." >> $EMAILFILE
echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
echo "Please fix the error and remove the lock file" >> $EMAILFILE
echo "before re-running the script again!!!" >> $EMAILFILE
echo >> $EMAILFILE

grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-' $LOGFILE >> $EMAILFILE
grep -i 'sbt' $LOGFILE >> $EMAILFILE
grep -i 'tsm' $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

exit 12
fi

##
## sync up the rman catalog
##

rman << EOF >> $LOGFILE
CONNECT TARGET ${rmanid}/${rmanpw} ;
CONNECT CATALOG ${catlid}/${catlpw}@${ORACLE_CATL} ;
RESYNC CATALOG;
EOF

rmanstatus=$?

##
## Check rman exit status and email in case of a failure
##

if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed in the resync step for $ORACLE_SID !!!" >> $EMAILFILE

echo >> $EMAILFILE
date >> $EMAILFILE
echo "rman error!!! ..." >> $EMAILFILE
echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
echo "Please fix the error and remove the lock file" >> $EMAILFILE
echo "before re-running the script again!!!" >> $EMAILFILE
echo >> $EMAILFILE

grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-' $LOGFILE >> $EMAILFILE
grep -i 'sbt' $LOGFILE >> $EMAILFILE
grep -i 'tsm' $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

exit 12
fi
echo --------------------------------------------------------- >> $LOGFILE
date >> $LOGFILE
echo ---------------- rman_bkup.ksh completed ---------------- >> $LOGFILE
echo >> $LOGFILE

cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script completed successfully for $ORACLE_SID !!!" >> $EMAILFILE
cat $LOGFILE >> $EMAILFILE

sendmail -F "$SENDER" -t < $EMAILFILE

cat $EMAILFILE >> $EMAILARCFILE

cat $LOGFILE >> $LOGARCFILE

##
## Remove lock file to allow other rman processs for
## this ORACLE_SID to run
##

rm $LOCKFILE

## --------------------------------------------------------------------
## End of rman_bkup.ksh
## --------------------------------------------------------------------

sample rmanids.sh file

2010-03-08T19:54:00.001-08:00

#!/bin/ksh

##
## Script Name: rmanids.sh
## Author : Ramakrishna Nemani
##
## This script is sourced from in other KSH scripts
##
##

##
## set your rman id and password below
##

export rmanid=yourrmanid
export rmanpw=yourrmanpassword

##
## set your rman catalog id and password below
##

export catlid=yourcatalogid
export catlpw=yourcatalogpassword

##
## set your rman catalog database sid
##
export ORACLE_CATL=oem10gx.world
export NLS_LANG="american_america.us7ascii"
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"

## ------------------------------------------------------------------------------
## End of rmanids.sh
## ------------------------------------------------------------------------------

sample rman_config.ksh file

2010-03-08T19:53:00.000-08:00

#!/bin/ksh

##
## Script Name: rman_config.ksh
## Author : Ramakrishna Nemani
##
## Set your rman & shell environment using this script
##
##

##

## Make sure ORACLE_SID is passed as an argument
##

if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi

export ORACLE_SID=$1
LOG_DIR="."

##
## Make sure no other rman process is running for this ORACLE_SID
##

LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"

while [ 1 ]
do

if [ -e $LOCKFILE ]
then

##
## Some rman process is running. Check again after 3 minutes
##
sleep 80
else

##
## No rman process is running. Create the lock file
##
echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
break
fi

done

CURR_DATE=`date +"%Y%m%d_%H%M%S"`

##
## using two seperate files instead of one
## The LOGFILE will have log from just the latest run
## The ARCFILE will have cumulative log
##

## LOGFILE="${LOG_DIR}/rman_config_$CURR_DATE.$ORACLE_SID.log"

LOGFILE="${LOG_DIR}/rman_config.$ORACLE_SID.log"
ARCFILE="${LOG_DIR}/rman_config.$ORACLE_SID.arc"

##
## Run rman config commands
##

rman <<EOF > $LOGFILE
connect target / ;

CONFIGURE RETENTION POLICY TO REDUNDANCY 7 ;
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP
FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F';

CONFIGURE DEFAULT DEVICE TYPE TO DISK ;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET ;

CONFIGURE DEVICE TYPE sbt PARALLELISM 4 BACKUP TYPE TO BACKUPSET ;

EOF

cat $LOGFILE >> $ARCFILE

##
## Remove lock file to allow other rman processs for
## this ORACLE_SID to run
##

rm $LOCKFILE

## -------------------------------------------------------------------------------
## End of rman_config.ksh
## -------------------------------------------------------------------------------

sample rman_register_db.ksh file

2010-03-08T19:51:00.000-08:00

#!/bin/ksh
##
## Script Name: rman_register_db.ksh
## Author : Ramakrishna Nemani
##
## Register a given database with rman catalog
##
##

. ~/.profile
. ./rmanids.sh

##
## Make sure ORACLE_SID is passed as an argument
##

if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi

export ORACLE_SID=$1
rman << EOF
connect target ${rmanid}/${rmanpw} ;
connect catalog ${catlid}/${catlpw}@${ORACLE_CATL} ;
Register database;
EOF

export rman_rtnval=$?
echo $rman_rtnval
if [[ $rman_rtnval = 0 ]]
then
echo $ORACLE_SID successfully registered with rman catalog
else
echo $ORACLE_SID could not be registered with rman catalog
fi

## -------------------------------------------------------------------------------
## End of rman_register_db.ksh
## -------------------------------------------------------------------------------

Teradata Dignostics commands

2007-11-04T15:25:00.000-08:00

Diagnostic commands are undocumented features of Teradata. These commands come in handy to DBAs when dignosing performance problems. I use a couple of the Diagnostic commands regularly in troubleshooting performance issues.

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;
The above command makes Teradata to include additional info in the output of an explain command. The output from all subsequent explain commands

qualify Spool File on join columns in the explain output
specify hash distribution column names for spool files.

To turn off additional information in the output of the explain command just issue the following command.

DIAGNOSTIC VERBOSEEXPLAIN NOT ON FOR SESSION;

Below is another command that is quite helpful

DIAGNOSTIC HELPSTATS ON FOR SESSION;

The above command makes Teradata to include the collect statistics possibilities in the ouput of expplain command

To turn off collect statistics possibilities in the output of the explain command just issue the following command.

DIAGNOSTIC HELPSTATS NOT ON FOR SESSION;

An Introduction to Teradata components

2007-11-04T14:19:00.000-08:00

Teradata is a relational database management system. A Teradata system has many components (hardware and software). A Teradata system contains one or more nodes. A node is a term for a general-purpose processing unit under the control of a single operating system. The basic building block for a Teradata system, the node is where the processing occurs for the database.

A node contains a large number of hardware and software components as explained below…

PDE
The PDE (Parallel Database Extensions) software layer runs the operating system on each node. It was created by NCR to support the parallel environment.

System Disks
System disks are contained on the node used for the following:
• Operating system software
• Teradata software
• Application software
• System dump space
Teradata database tables are stored on disk arrays, not on the system disks.

Memory
Vprocs share a free memory pool within a node. A segment of memory is allocated to a vproc for its use, then returned to the memory pool for use by another vproc. The free memory pool is a collection of memory available to the node.

Application
An application is software that accesses the Teradata RDBMS. It can run on various platforms:
• Channel-attached client
• LAN-attached client
• Node

Vproc
A virtual processor or a vproc is a group of one or more software processes running under the operating system's multi-tasking environment:
• On the UNIX operating system, a vproc is a collection of software processes.
• On the Windows operating systems, a vproc is a single software process.

The two types of Teradata vprocs are:
• AMP (Access Module Processor)
• PE (Parsing Engine)

When vprocs communicate, they use BYNET hardware (on MPP systems), BYNET software, and PDE. The BYNET hardware and software carry vproc messages to and from a particular node. Within a node, the BYNET and PDE software deliver messages to and from the participating vprocs.

PE
PEs (Parsing Engines) are vprocs that receive SQL requests from the client and break the requests into steps. The PEs send the steps to the AMPs and subsequently return the answer to the client.

AMP
AMPs (Access Module Processors) are virtual processors (vprocs) that receive steps from PEs (Parsing Engines) and perform database functions to retrieve or update data. Each AMP is associated with one virtual disk (vdisk), where the data is stored. An AMP manages only its own vdisk, not the vdisk of any other AMP.

Vdisk (Virtual Disk)
A vdisk is the logical disk space that is managed by an AMP. Depending on the configuration, a vdisk may not be contained on the node; however, it is managed by an AMP, which is always a part of the node.
The vdisk is made up of 1 to 64 pdisks (user slices in UNIX or partitions in Windows NT, whose size and configuration vary based on RAID level). The pdisks logically combine to comprise the AMP's vdisk. Although an AMP can manage up to 64 pdisks, it controls only one vdisk. An AMP manages only its own vdisk, not the vdisk of any other AMP.

Channel Driver
Channel driver software is the means of communication between the PEs and applications running on channel-attached (mainframe) clients.

Gateway
The Teradata Gateway software is the means of communication between the PEs and applications running on a LAN-attached clients or A node in the system

BYNET
The BYNET (banyan network) is a combination of hardware and software that provides high performance networking between the nodes of a Teradata system. A dual-redundant, bi-directional, multi-staged network, the BYNET enables the nodes to communicate in a high speed, loosely-coupled fashion. It is based on banyan topology, a mathematically defined structure that has branches reminiscent of a banyan tree.
The BYNET is a high-speed interconnect (network) that enables multiple nodes in the system to communicate.

The BYNET hardware and software handle the communication between the vprocs.
• Hardware: The nodes of an MPP system are connected with the BYNET hardware, consisting of BYNET boards and cables.
• Software: The BYNET software is installed on every node. This BYNET driver is an interface between the
PDE software and the BYNET hardware.

SMP systems do not contain BYNET hardware. The PDE and BYNET software emulates BYNET activity in a single-node environment. The SMP implementation is sometimes called "boardless BYNET."

Teradata uses different types of messages for the highest efficiency:
• Broadcast: BYNET hardware delivers a message to all nodes in an MPP system. This is simulated in SMP systems using BYNET software. A single broadcast message consumes more BYNET bandwidth than a single point-to-point message, but sometimes a broadcast may be the most efficient for processing.
• Point-to-Point: A single message to a single vproc is carried over BYNET hardware to the destination node, and relayed to or from a particular vproc using PDE software. Point-to-point messages enable the BYNET to be scalable because as the system grows, the capacity to handle point-to-point messages also grows.
• Multicast: A message can be sent to several vprocs (multicast). The BYNET hardware first sends a broadcast message to all nodes. The PDE software on the receiving nodes determines which vprocs should receive the message and delivers it to those vprocs. Multicast messages give the system yet another method to deliver messages for the best performance

A Teradata node requires three distinct pieces of software.

Operating System :
UNIX or Windows. Or Linux

The Parallel Database Extensions (PDE) :
The Parallel Database Extensions (PDE) software layer was added to the operating system by NCR to support the parallel software environment.

Teradata RDBMS aka Trusted Parallel Application (TPA)
A Trusted Parallel Application (TPA) uses PDE to implement virtual processors (vprocs). The Teradata RDBMS is classified as a TPA. The four components of the Teradata TPA are:
• AMP
• PE
• Channel Driver
• Teradata Gateway

Access Module Processor (AMP)
AMPs (Access Module Processors) are virtual processors (vprocs) that receive steps from PEs (Parsing Engines) and perform database functions to retrieve or update data
AMP Worker Task Functions
The AWT functions in the AMP perform a number of operations, including:
• Locking tables to ensure data consistency.
• Executing AMP step operations such as select, insert, update, delete and sort.
• Joining tables as required.
• Executing end transaction steps as required to support multi-AMP operations.

AMP File System
The file system software accesses the data on the virtual disks. Each AMP uses the file system software to read from and write to the virtual disks.

AMP Console Utilities
The AMP software includes utilities to perform systems management functions such as:
• Configure and reconfigure the system
• Rebuild tables
• Reveal details about locks and space status

Parsing Engine (PE)
PEs (Parsing Engines) are vprocs that receive SQL requests from the client and break the requests into steps. The PEs send the steps to the AMPs and subsequently return the answer to the client.

PE Session Control
When you log on to the Teradata RDBMS through your application, the session control software
on the PE establishes that session. Session control also manages and terminates sessions on that PE.

PE Parser/Optimizer
The Parser interprets an incoming Teradata SQL request and checks the syntax. The Parser decomposes the request into AMP steps, using the Optimizer to determine the most efficient way to access the data on the virtual disks (vdisks).
The Optimizer develops the "least expensive" plan (in terms of time and system resources) to return the requested response set. Processing alternatives are evaluated, and the fastest alternative is chosen. The selected alternative is converted to executable steps that will performed by the AMPs. Then, the Parser sends the steps to the dispatcher.

PE Dispatcher
The dispatcher is responsible for a number of tasks, depending on the operation it is performing:
• Processing Requests: Controls the sequence in which the steps are executed and passes the steps to the AMPs through the BYNET.
• Processing Responses: After the AMPs process the steps, the dispatcher builds a response message and sends the response back to the user.

Channel Driver
Channel Driver software is the means of communication between an application and the PEs assigned to channel-attached clients. There is one Channel Driver per node.

Teradata Gateway
Teradata Gateway software is the means of communication between an application and the PEs assigned to network-attached clients. There is one Teradata Gateway per node.

User Access
Clients communicate with the Teradata RDBMS in three ways:
• Channel
• Network
• Node

Channel
Communication from applications on the mainframe goes through the Channel Driver.
Traffic and communications between client applications on the mainframe and the Teradata database are managed by the Teradata Director Program (TDP) software.

Network
Communication from applications on a network-attached client goes through the Teradata Gateway.
Traffic and communication between network-attached client applications and the Teradata database are managed by either: ODBC or CLIv2.

Node
If you install application software on a node, it will be treated like an application on a network-attached client. In other words, communications from applications on the node go through the Teradata Gateway.

To summarize, users can access data in the Teradata RDBMS through an application on both channel-attached and network-attached clients. Additionally, the node itself can act as a client. Teradata client software is installed on each client (channel-attached, network-attached, or node) and communicates with RDBMS software on the node.

Primary Indexes
The Teradata Database distributes the data in each table across all AMPs on a system. Since each amp is responsible for managing it’s own vdisk, it effectively means distributing data across disks automatically. This is one of the major differences between Teradata and other RDBMSs

Teradata assigns rows to AMPs based on the value of their primary index. The determination of which hash bucket, and hence which AMP the row is to be stored on, is made solely on the row hash value of its primary index. Each Teradata Database table must have a primary index.

RMAN Disaster Recovery script (rman_dr.ksh)

2007-07-03T12:17:00.000-07:00

This script is intended to provide a mechanism for disaster recovery. Output log of this script is given as another entry which you can check

#!/bin/ksh
. ~/.profile
##
## script name : rman_dr.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring the complete database from tape. This could be used
## after a disaster to do a disaster recovery. It assumes that a new unix box has
## been set up and Oracle software has been installed and all the directory structures
## have been restored by the UNIX admins. This script does not use rman recovery catalog.
##
## Note :
## You have to replace the DBID value with the DBID that you are attempting to restore and recover
## You need to login as a unix user id that belongs to the dba group and run this script
## I have tested this script only on Oracle 10g on an IBM AIX platform. Use it at your own risk.
##
## This is an example or sample script
##
rman << EOF > ./cmad_dr.log
CONNECT TARGET /
STARTUP FORCE NOMOUNT;
##
## set dbid because we are not using an rman recovery catalog
##
SET DBID 1522268517 ;
##
## Specify AUTOBACKUP Format
##
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F';
##
## Restore spfile from autobackup first
##
RUN {
ALLOCATE CHANNEL tape_1 DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
RESTORE SPFILE FROM AUTOBACKUP ;
}
##
## Restart the database with newly restored SPFILE
##
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
##
## Now restore controlfile from autobackup
##
RUN {
ALLOCATE CHANNEL tape_1 DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
RESTORE CONTROLFILE FROM AUTOBACKUP ;
}
##
## Mount the database, restore and recover
##
ALTER DATABASE MOUNT ;
RUN {
ALLOCATE CHANNEL tape_1 DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
RESTORE DATABASE ;
RECOVER DATABASE ;
}
##
## Open the database, if restore and recover are successful
##
EOF

SQL Tutorial for the beginners

2007-07-01T05:42:00.000-07:00

This tutorial is aimed at giving a beginner a brief introduction to the Structured Query Language or SQL and get you started with using sqlplus to log on to Oracle and access and manipulate data.

Give below is what you learn and can expect to be able to do after attending this tutorial…
• Brief history and overview of SQL
• Set-up environment variables to invoke sqlplus and log on to a Oracle database from a reflection session
• Execute basic SQL queries
• Get all the data in a table
• Get named columns from a table
• Get named columns from a table and order them
• Perform arithmetic on column values
• Prevent duplicate row selection
• Selecting only data that meets a criteria with the WHERE clause
• SQL operators in WHERE clauses
• Multiple condition WHERE clauses
• Group Functions
• Sub-queries
• Select data from multiple tables
• Execute basic SQL DML commands
• Insert
• Update
• Delete
• Use basic DDL commands
• create table
• alter table
• create index
• truncate table
• drop table
• Use basic access control commands
• grant
• revoke
• Modify sqlplus environment
• Write and execute small SQL scripts

Structured Query Language

Structured Query Language or SQL was originally developed by IBM for its System/R Project. IBM later used it in its products SQL/Data System (SQL/DS) and Database 2 (DB2). SQL today became a language of choice to access the data and structures within a relational database and is implemented and supported by most commercially available Relational Database Management System vendors.
SQL was designed to be an English like language using English phrases to manipulate the database. It is non procedural. You specify what you want done but not how to do it. The how part is taken care of by the RDBMS. Each RDBMS has an in-built query optimizer which parses your SQL statements and works out the best path to retrieve/manipulate the data.
SQL is designed to be used by a wide spectrum of users- DBA's, application developers, operators and end users.
It provides commands to do the following …
• finding (querying) data
• inserting, updating and deleting data
• creating, modifying and deleting database objects
• controlling access to the database and database objects
• Transaction Control
The different SQL statements could be grouped into 4 different categories as explained below…
DATA QUERY LANGUAGE (DQL) - SQL statements to retrieve data from the database and transform it.   SELECT statement falls into this category.
DATA MANIPULATION LANGUAGE (DML) - SQL statements to insert, change or delete data from the database. DELETE, INSERT, UPDATE   statements fall into this category.
DATA DEFINITION LANGUAGE (DDL) - SQL statements to define or alter database structures. DROP, TRUNCATE, CREATE, ALTER statements fall into this category.
Access Control - SQL statements to either grant or revoke privileges to access and manipulate database objects.
REVOKE, GRANT statements fall into this category.
Transaction Control - SQL statements that commit or rollback changes to data.
COMMIT, ROLLBACK statements fall into this category.
To connect to an oracle database and execute sql commands you can use an oracle supplied tool called sqlplus.

Environment set-up for invoking sqlplus and connect to an Oracle database
Add the following lines to your .profile after you login to your unix environment. After adding the following lines log out and log back in to make sure that the .profile is run.
# Replace with the path at your installation
export ORACLE_HOME=/oracle/product/8.1.7
# Replace with sid name at your installation
ORACLE_SID=xyz
export ORACLE_SID
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH
LIBPATH=$LIBPATH:/usr/lib:$ORACLE_HOME/lib
export LIBPATH
PATH=$PATH:$ORACLE_HOME/bin
export PATH
The above lines set-up paths for oracle binaries and libraries.
The ORACLE_SID environment variable identifies the database that you would connect to when you invoke sqlplus. In our case we would be connecting to a database named stgt.
After setting up the environment you can invoke sqlplus by entering sqlplus at your unix prompt as shown below. Enter your username and password when prompted. You will get SQL prompt if you login successfully. You can then enter and execute all your sql commands at the SQL prompt.
$ sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 26 11:33:41 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: rnemani
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL>
If you want to terminate your sqlplus session you simply enter exit at your SQL prompt as shown below…
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 – Production
$

DATA QUERY LANGUAGE (DQL)
The SELECT statement is the most widely used command of SQL. It allows you to retrieve the data from the database. Most SQL statements will need a semi-colon ";" to indicate the end of the statement.
Get all the data in a table
Select * from price_list ;
Gets all the columns and all the rows in the table.
Get named columns from a table
Select division_no, article_nbr, price_list_price, price_list_price
From price_list ;
Get named columns from a table and order them
Select division_no, article_nbr, price_list_price
From price_list
Order by division_no, article_nbr ;
Perform arithmetic on column values
Select division_no, article_nbr, price_list_price, price_list_price*0.05
From price_list ;
Preventing duplicate row selection
Select distinct division_no From price_list ;
Selecting only data that meets a criteria with the WHERE clause
Select division_no, article_nbr, price_list_price
From price_list
Where division_no = 72 ;
SQL operators in WHERE clauses
Select division_no, article_nbr, price_list_price
From price_list
Where division_no in (71, 72) ;
Select division_no, article_nbr, price_list_price
From price_list
Where price_list_price > 100 ;
Select division_no, article_nbr, price_list_price
From price_list
Where division_no not in (71, 72) ;
select * from division
where division_desc like 'T%' ;
Multiple condition WHERE clauses
Select * from price_list
Where division_no = 93
And basis_company_id = 66 ;
Select * from price_list
Where division_no = 93
or price_list_price > 100 ;
Group Functions
Select count(*) from price_list ;
Select max(price_list_price)
From price_list ;
Select avg(price_list_price)
From price_list ;
Select division_no, count(*)
from price_list
group by division_no ;
Select division_no, max(price_list_price), min(price_list_price)
from price_list
group by division_no ;
Select division_no, max(price_list_price), min(price_list_price)
from price_list
group by division_no
having min(price_list_price) > 10 ;
Select division_no, basis_company_id, count(*)
from price_list
group by division_no, basis_company_id ;
Selecting data from multiple tables (Join)
Select division_desc, outlet_name
From division a , outlet b
Where a.division_no = b.division_no ;
Select division_desc, outlet_name
From division a, outlet b
Where a.division_no = b.division_no
And a.division_no = 51 ;
Sub-queries
Select count(*) from price_list
Where division_no in (
select division_no from division where price_group_user = 'Y'
) ;
Select count(*) from price_list
Where division_no not in (
select division_no from division where price_group_user = 'Y'
)
;
Select count(*) from price_list a
Where exists in (
select division_no
from division b
where b.price_group_user = 'Y'
and b.division_no = a.division_no
)
;
Select count(*) from price_list a
Where not exists in (
select division_no
from division b
where b.price_group_user = 'Y'
and b.division_no = a.division_no
)
;
DATA MANIPULATION LANGUAGE (DML)
In order for us to be able to retrieve data from tables we need to first have them populated and properly maintained so that they contain the relevant data. This is exactly what the DML commands are used for.
INSERT
Insert command is used for inserting new data into a table.
insert into division
values (98, 'This is a New Dvsn','Y','Y','N')
;
The above statement will create a new row .
insert into division
(division_no,division_desc)
values (98, 'This is a New Dvsn') ;
The above statement will create a new row but with only division_no and division_desc columns populated with values and other columns set to NULL.
Insert into new_division_table
Select * from division ;
The above statement Inserts rows from division table into new_division_table table (provided the structure of new_division_table table is identical to the structure of division table).
You have to issue a commit statement if you want the data to be saved in the table permanently. Alternatively you can issue a rollback, if you do not want your changes to be saved to the database.
A commit or a rollback command should always be issued to commit or rollback a transaction. A transaction could be made up of one or more DML commands.
A commit statement looks as follows …
Commit ;
A rollback statement looks as follows …
Rollback ;
Make sure that you issue a commit or rollback before you exit your sqlplus session. Sqlplus will issue a commit by default when you exit.
UPDATE
The UPDATE command is used for updating the column values of an existing row in a table.
Following are some examples …
update division
set division_desc = 'My new division'
where division_no = 98 ;
update price_list
set price_list_price = price_list_price * 0.05
where price_list_price < 1 ;
You can use a sub-query to update a column as shown below …
update new_division_table a
set division_desc = (select division_desc from division b
                     where b.division = a.division
                     )
;
DELETE
Delete command is used for removing data (rows) from a table.
delete from new_division_table ;
The above statement will delete all rows from new_division_table table.
delete from new_division_table
where division_no = 98 ;
The above statement will delete the row corresponding to the division_no 98 from the new_division_table table.
Some more examples …
delete from new_division_table
where division_no >= 98 ;
delete from new_division_table
where division_no in (98, 99) ;
delete from new_division_table
where division_no != 98 ;
delete from new_division_table
where division_no not in (98, 99) ;
DATA DEFINITION LANGUAGE (DDL)
In order for us to be able to store and retrieve data from tables we need to first have them created. This is exactly what the DDL commands are used for.
Following is an example of "create table " command in its simplest form…
create table new_division_table
( DIVISION_NO NUMBER(2) NOT NULL,
DIVISION_DESC VARCHAR2(30),
PRICE_GROUP_USER VARCHAR2(1),
TRADE_GROUP_USER VARCHAR2(1),
SALES_TYPE_USER VARCHAR2(1)
) ;
The above command creates a table called new_division_table. It will be empty. The NOT NULL clause for division_no column is to make sure that division_no column must always have a value whenever data is inserted into this table and it can not be updated to a NULL value.
You can also create a table from an existing table as shown below…
create table new_division_table as
select * from division ;
The above command creates a table called new_division_table and populates it with data from division table.
create index x1_new_division_table
on new_division_table (division_no) ;
The above command creates an index called x1_new_division_table.
Alter table new_division_table
modify               DIVISION_DESC VARCHAR2(40) ;
The above command alters the width of division_desc column to 40 from 30.
truncate table new_division_table ;
The above command deletes all data in the table
drop table new_division_table ;
The above command drops the table from the database. All the data within the table is permanently lost. So do exercise caution when using this command. I have one suggestion to avoid mistakes with DROP command – DO NOT USE IT in the first place.
Note : DDL commands do not need a commit because they issue an implicit commit. This means that we can not rollback any DDL once it is executed.
There are a number of other DDL commands which I think are the beyond the scope of this brief introduction to SQL.

Access Control Commands
When a user creates a database object such as a table, it gets created under the ownership (schema) of the user creating it. The user who created the table owns the table. In order for others to be able to access or store data in this table the owner has to grant appropriate privileges to other users.
Following are some examples but first the disclaimer…
"Following examples are based on fictitious situations. Any resemblance to any names of any persons or projects or places is purely coincidental. " 
Example 1.
Tom and Harry work in the IT department for a major bottling company and are working on a new project (actually cleaning up an old project) which requires them to work on an Oracle database and deal with tables etc...
During the course working on this project, Tom creates a table called new_division_table and he wants Harry to be able to select data from this table. So he executes the following command…
grant select on new_division_table to Harry ;
Now Harry can access data from this table as follows..
select * from Tom.new_division_table ;
Note that the username of the owner of the table has to be prepended to the table name, if the table is not owned by the user trying to access the table.
Example 2.
Harry tells Tom that he wants to be able to do more than mere selecting of data from the table.
Tom then executes the following GRANT command to give Harry more privileges…
grant insert, update, delete on new_division_table to Harry ;
Harry then tests his newly granted privileges on the table as follows…
delete from Tom.new_division_table ;
commit ;

Example 3.
Now Tom is mad and wants to take away all but SELECT privilege from Harry on the new_division_table. He executes the following command …
revoke   insert,update, delete on new_division_table from Harry ;
I Hope the above examples clarify the use of Access control commands.
Changing sqlplus environment
You can change the default behaviour of how the results are displayed within sqlplus. You can do this by using the set command of sqlplus.
set pagesize 80
The above command makes sure columns headings are repeated after every 80 lines of the results.
set pagesize 0
The above command supresses printing of headings.
set linesize 70
Above command sets the line size to 70.
spool /home/rnemani/division.list
select * from division ;
spool off
The above commands create a text file called division.lst in /home/patuser directory. This file would contain the output of division table.
If you want see your current sqlplus environment settings give the following command
show all
There are a number of useful set commands that you could use and would use as you start using sqlplus regularly.
Using SQL scripts
You can save regularly used sql commands in a sql script using a text editor such as vi.
For example you can save the following commands in a script file called get_div.sql using vi.
spool /home/rnemani/division.list
select * from division ;
spool off
You can then start up a sql session and execute the script at the sql prompt as shown below.
SQL> @get_div.sql
The above command would execute all the commands in the script as if they were entered at SQL prompt.
You can know the structure (column names, data types etc…) of any table, by using the following command
DESC table_name
for example…
SQL> desc division
Name                                Null?    Type
----------------------------------- -------- --------------------
DIVISION_NO                         NOT NULL NUMBER(2)
DIVISION_DESC                                VARCHAR2(30)
PRICE_GROUP_USER                             VARCHAR2(1)
TRADE_GROUP_USER                             VARCHAR2(1)
SALES_TYPE_USER                              VARCHAR2(1)
SQL>
Oracle returns errors when you execute your sql statements, if there is a syntax error(s) or if it is unable to find the objects that are referred to in the SQL statement. Given below is an example…
SQL> select count(*) from rkn_branch_extension ;
select count(*) from rkn_branch_extension
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
If you want know more about this error then you can issue the following command at your UNIX prompt.
$ oerr ORA 942

Conclusion

As you can see SQL is very easy to use and intuitive. One other good thing about SQL is that it is portable for the most part across databases that support it. I hope this tutorial gave you enough information to get started with SQL.

RMAN tape backup script

2007-06-30T21:55:00.000-07:00

This script  does a backup of backupset to tape.

#!/bin/ksh
. ~/.profile
##
## script name : rman_tape_bkup.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script takes database sid as a command line argument and does a backup of
## backupset to tape. It backs up to the tape device using automatic channel
## allocation. Make sure you have configured the default device and channels. You
## can find the configuration that I have used in a script called rman_config.ksh
##
## Other Features :
## This script emails the backup log to the email ids set up in the email configuration
## file email configuration file is just a text file containing the email ids to where
## the backup log will be emailed to.
## This script creates a log file on the datbase server where this script is hosted.
##
## It also creates and uses a lockfile to make sure no other rman process is running for
## the database sid that was supplied through the command line. Should this script fail
## for any reason, it does not remove the lockfile so that no rman process can run for this
## database until the error is fixed and the lock file is removed manually
##
## Note :
## You need to login as a unix user id that belongs to the dba group and run this script
## There are a bunch of modifiable parameters in this script that you should modify to
## reflect your environment.
## I have tested this script on only Oracle 10g on an IBM AIX platform. Use it at your own risk.
##
## This is an example or sample script
##
##
## Make sure ORACLE_SID is passed as an argument
##
if (( $# < 1 ))
then
        echo
        echo Error Missing Arguement, Please supply Oracle Sid
        echo
        echo Usage: $0 ORACLE_SID
        echo
        exit
fi
export ORACLE_SID=$1
## ----------------------------------------------------------------------------------
##
## -------------------- Start of modifiable parameters ------------------------------
##
## Change the values for the following parameters to reflect your environment
##
##
LOG_DIR="/u01/oracle/admin/rman"
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
EMAIL_INFO_FILE="${LOG_DIR}/${ORACLE_SID}_email.cfg"
SENDER="mailto:rman@%60hostname"
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one for keeping the logs
## The LOGFILE will have log from just the latest run
## The LOGARCFILE will have the cumulative log
##
## LOGFILE="${LOG_DIR}/rman_tape_bkup_$CURR_DATE.log"
LOGFILE="${LOG_DIR}/rman_tape_bkup.$ORACLE_SID.log"
LOGARCFILE="${LOG_DIR}/rman_tape_bkup.$ORACLE_SID.log.arc"
##
## using two seperate files instead of one for keeping the errors
## The EMAILFILE will have errors if any from just the latest run
## The EMAILARCFILE will have the cumulative errors
##
EMAILFILE="${LOG_DIR}/rman_tape_bkup.$ORACLE_SID.email"
EMAILARCFILE="${LOG_DIR}/rman_tape_bkup.$ORACLE_SID.email.arc"
## --------------------------------------------------------------------------------
##
## -------------------- End of modifiable parameters ------------------------------
##
## --------------------------------------------------------------------------------
##
## Make sure no other rman process is running for this ORACLE_SID
##
echo -------------------------------------------------------------- > $LOGFILE
date >> $LOGFILE
echo ---------------- rman_tape_bkup.ksh started ------------------ >> $LOGFILE
while [ 1 ]
do

if [ -e $LOCKFILE ]
then
##
## Some rman process is running. Check again after 3 minutes
##
echo "$LOCKFILE exists. Will check again after 3 minutes" >> $LOGFILE
echo >> $LOGFILE
        sleep 180
else
##
## No rman process is running. Create a lock file
##
        echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
        break
fi
done
rman <<EOF   >> $LOGFILE
connect target / ;
SHOW ALL ;
## RUN
## {
## ALLOCATE CHANNEL t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' format '%u_%p_%c';
## BACKUP BACKUPSET ALL ;
## }
BACKUP DEVICE TYPE SBT BACKUPSET ALL ;
REPORT SCHEMA ;
EXIT ;
EOF
rmanstatus=$?
if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
        echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi
cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed for $ORACLE_SID !!!" >> $EMAILFILE
        echo >> $EMAILFILE
        date >> $EMAILFILE
        echo "rman error!!! ..." >> $EMAILFILE
        echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
        echo "Please fix the error and remove the lock file" >> $EMAILFILE
        echo "before re-running the script again!!!" >> $EMAILFILE
        echo >> $EMAILFILE
grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-'   $LOGFILE >> $EMAILFILE
grep -i 'sbt'    $LOGFILE >> $EMAILFILE
grep -i 'tsm'    $LOGFILE >> $EMAILFILE
sendmail -F "$SENDER" -t < $EMAILFILE
cat $EMAILFILE >> $EMAILARCFILE
        exit 12
fi
echo -------------------------------------------------------------- >> $LOGFILE
date >> $LOGFILE
echo ---------------- rman_tape_bkup.ksh completed ---------------- >> $LOGFILE
echo >> $LOGFILE
cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script completed successfully for $ORACLE_SID !!!" >> $EMAILFILE
cat $LOGFILE >> $EMAILFILE
sendmail -F "$SENDER" -t < $EMAILFILE
cat $EMAILFILE >> $EMAILARCFILE
cat $LOGFILE >> $LOGARCFILE
##
## Remove lock file to allow other rman processs for this ORACLE_SID to run, if any
##
rm $LOCKFILE
## --------------------------------------------------------------------------------
## End of rman_tape_bkup.ksh
## --------------------------------------------------------------------------------

RMAN configuration script

2007-06-30T21:19:00.000-07:00

This script configures persistent rman settings

#!/bin/ksh
. ~/.profile
##
## script name : rman_config.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script takes database sid as a command line argument and configures
## persistent rman settings
##
## Other Features :
## This script creates a log file on the datbase server where this script is hosted.
##
## It also creates and uses a lockfile to make sure no other rman process is running for
## the database sid that was supplied through the command line. Should this script fail
## for any reason, it does not remove the lockfile so that no rman process can run for this
## database until the error is fixed and the lock file is removed manually.
##
##
## Note :
## You need to login as a unix user id that belongs to dba group and run this script.
## For device type sbt , the setting that I have here is for our TSM setup. Your setting may differ.
## I have tested this script on only Oracle 10g on an IBM AIX platform. Use it at your own risk.
##
## This is an example or sample script
##
##
## Make sure ORACLE_SID is passed as an argument
##
if (( $# < 1 ))
then
echo
echo Error Missing Arguement, Please supply Oracle Sid
echo
echo Usage: $0 ORACLE_SID
echo
exit
fi
export ORACLE_SID=$1
LOG_DIR="."
##
## Make sure no other rman process is running for this ORACLE_SID
##
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
while [ 1 ]
do
if [ -e $LOCKFILE ]
then
##
## Some rman process is running. Check again after 3 minutes
##
        sleep 80
else
##
## No rman process is running. Create the lock file
##
        echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
        break
fi
done
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one
## The LOGFILE will have log from just the latest run
## The ARCFILE will have cumulative log
##

## LOGFILE="${LOG_DIR}/rman_config_$CURR_DATE.$ORACLE_SID.log"
LOGFILE="${LOG_DIR}/rman_config.$ORACLE_SID.log"
ARCFILE="${LOG_DIR}/rman_config.$ORACLE_SID.arc"
##
## Run rman config commands
##
rman <<EOF > $LOGFILE
connect target / ;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP
FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F';
CONFIGURE DEFAULT DEVICE TYPE TO DISK ;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET ;
CONFIGURE DEVICE TYPE sbt PARALLELISM 4 BACKUP TYPE TO BACKUPSET ;
CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
EOF
cat $LOGFILE >> $ARCFILE
##
## Remove lock file to allow other rman processs for this ORACLE_SID to run, if any
##
rm $LOCKFILE
## ---------------------------------------------------------------------------------
## End of rman_config.ksh
## ---------------------------------------------------------------------------------

RMAN archive logs backup script

2007-06-30T21:16:00.000-07:00

This script does archive logs backup using rman.

#!/bin/ksh
. ~/.profile
##
## script name : rman_arch_bkup.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script takes database sid as a command line argument and does archive logs
## backup. It backs up to the default device using automatic channel allocation.
## Make sure you have configured the default device and channels. You can find the
## configuration that I have used in a script called rman_config.ksh
##
## Other Features :
## This script emails the backup log to the email ids set up in the email configuration
## file email configuration file is just a text file containing the email ids to where
## the backup log will be emailed to.
## This script creates a log file on the datbase server where this script is hosted.
##
## It also creates and uses a lockfile to make sure no other rman process is running for
## the database sid that was supplied through the command line. Should this script fail
## for any reason, it does not remove the lockfile so that no rman process can run for this
## database until the error is fixed and the lock file is removed manually
##
##
## Note :
## You need to login as a unix user id that belongs to the dba group and run this script
## There are a bunch of modifiable parameters in this script that you should modify to
## reflect your environment.
## I have tested this script on only Oracle 10g on an IBM AIX platform. Use it at your own risk.
##
## This is an example or sample script
##
##
## Make sure ORACLE_SID is passed as an argument
##
if (( $# < 1 ))
then
        echo
        echo Error Missing Arguement, Please supply Oracle Sid
        echo
        echo Usage: $0 ORACLE_SID
        echo
        exit
fi
export ORACLE_SID=$1
## ----------------------------------------------------------------------------------
##
## -------------------- Start of modifiable parameters ------------------------------
##
## Change the values for the following parameters to reflect your environment
##
##
LOG_DIR="/u01/oracle/admin/rman"
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
EMAIL_INFO_FILE="${LOG_DIR}/${ORACLE_SID}_email.cfg"
SENDER="mailto:rman@%60hostname"
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one for keeping the logs
## The LOGFILE will have log from just the latest run
## The LOGARCFILE will have the cumulative log
##
## LOGFILE="${LOG_DIR}/rman_arch_bkup_$CURR_DATE.log"
LOGFILE="${LOG_DIR}/rman_arch_bkup.$ORACLE_SID.log"
LOGARCFILE="${LOG_DIR}/rman_arch_bkup.$ORACLE_SID.log.arc"
##
## using two seperate files instead of one for keeping the errors
## The EMAILFILE will have errors if any from just the latest run
## The EMAILARCFILE will have the cumulative errors
##
EMAILFILE="${LOG_DIR}/rman_arch_bkup.$ORACLE_SID.email"
EMAILARCFILE="${LOG_DIR}/rman_arch_bkup.$ORACLE_SID.email.arc"
## --------------------------------------------------------------------------------
##
## -------------------- End of modifiable parameters ------------------------------
##
## --------------------------------------------------------------------------------

##
## Make sure no other rman process is running for this ORACLE_SID
##
echo --------------------------------------------------------- > $LOGFILE
date >> $LOGFILE
echo ---------------- rman_arch_bkup.ksh started ------------------ >> $LOGFILE
while [ 1 ]
do

if [ -e $LOCKFILE ]
then
##
## Some rman process is running. Check again after 3 minutes
##
echo "$LOCKFILE exists. Will check again after 3 minutes" >> $LOGFILE
echo >> $LOGFILE
        sleep 180
else
##
## No rman process is running. Create a lock file
##
        echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
        break
fi
done
rman <<EOF   >> $LOGFILE
connect target / ;
SHOW ALL ;
## RUN
## {
## ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
## BACKUP DATABASE FILESPERSET 1 PLUS ARCHIVELOG DELETE INPUT;
## DELETE NOPROMPT OBSOLETE;
## }
BACKUP ARCHIVELOG ALL DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
REPORT SCHEMA ;
REPORT UNRECOVERABLE;
EXIT ;
EOF
rmanstatus=$?
if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
        echo "To:rnemani@na.cokecce.com" > $EMAIL_INFO_FILE
fi
cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed for $ORACLE_SID !!!" >> $EMAILFILE
        echo >> $EMAILFILE
        date >> $EMAILFILE
        echo "rman error!!! ..." >> $EMAILFILE
        echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
        echo "Please fix the error and remove the lock file" >> $EMAILFILE
        echo "before re-running the script again!!!" >> $EMAILFILE
        echo >> $EMAILFILE
grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-'   $LOGFILE >> $EMAILFILE
grep -i 'sbt'    $LOGFILE >> $EMAILFILE
grep -i 'tsm'    $LOGFILE >> $EMAILFILE
sendmail -F "$SENDER" -t < $EMAILFILE
cat $EMAILFILE >> $EMAILARCFILE
        exit 12
fi
echo --------------------------------------------------------- >> $LOGFILE
date >> $LOGFILE
echo ---------------- rman_arch_bkup.ksh completed ---------------- >> $LOGFILE
echo >> $LOGFILE
cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script completed successfully for $ORACLE_SID !!!" >> $EMAILFILE
cat $LOGFILE >> $EMAILFILE
sendmail -F "$SENDER" -t < $EMAILFILE
cat $EMAILFILE >> $EMAILARCFILE
cat $LOGFILE >> $LOGARCFILE
##
## Remove lock file to allow other rman processs for this ORACLE_SID to run, if any
##
rm $LOCKFILE
## ----------------------------------------------------------------------------------
## End of rman_arch_bkup.ksh
## ----------------------------------------------------------------------------------

RMAN complete database backup script

2007-06-30T21:11:00.000-07:00

This script does a full database backup including the archive logs.

#!/bin/ksh
. ~/.profile
##
## script name : rman_bkup.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script takes database sid as a command line argument and does a full database
## backup including the archive logs. It backs up to the default device using automatic
## channel allocation. Make sure you have configured the default device and channels.
## You can find the configuration that I have used in a script called rman_config.ksh
##
## Other Features :
## This script emails the backup log to the email ids set up in the email configuration
## file email configuration file is just a text file containing the email ids to where
## the backup log will be emailed to.
## This script creates a log file on the datbase server where this script is hosted.
##
## It also creates and uses a lockfile to make sure no other rman process is running for
## the database sid that was supplied through the command line. Should this script fail
## for any reason, it does not remove the lockfile so that no rman process can run for this
## database until the error is fixed and the lock file is removed manually
##
##
## Note :
## You need to login as a unix user id that belongs to the dba group and run this script
## There are a bunch of modifiable parameters in this script that you should modify to
## reflect your environment.
## I have tested this script on only Oracle 10g on an IBM AIX platform. Use it at your own risk.
##
## This is an example or sample script
##

##
## Make sure ORACLE_SID is passed as an argument
##
if (( $# < 1 ))
then
        echo
        echo Error Missing Arguement, Please supply database sid (Oracle Sid)
        echo
        echo Usage: $0 ORACLE_SID
        echo
        exit
fi
export ORACLE_SID=$1
## ----------------------------------------------------------------------------------
##
## -------------------- Start of modifiable parameters ------------------------------
##
## Change the values for the following parameters to reflect your environment
##
##
LOG_DIR="/u01/oracle/admin/rman"
LOCKFILE="${LOG_DIR}/$ORACLE_SID.lock"
EMAIL_INFO_FILE="${LOG_DIR}/${ORACLE_SID}_email.cfg"
SENDER="mailto:rman@%60hostname"
CURR_DATE=`date +"%Y%m%d_%H%M%S"`
##
## using two seperate files instead of one for keeping the logs
## The LOGFILE will have log from just the latest run
## The LOGARCFILE will have the cumulative log
##
LOGFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.log"
LOGARCFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.log.arc"
##
## using two seperate files instead of one for keeping the errors
## The EMAILFILE will have errors if any from just the latest run
## The EMAILARCFILE will have the cumulative errors
##
EMAILFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.email"
EMAILARCFILE="${LOG_DIR}/rman_bkup.$ORACLE_SID.email.arc"
## -----------------------------------------------------------------------------------
##
## -------------------- End of modifiable parameters -----------------------------
##
## ------------------------------------------------------------------------------------

##
## Make sure no other rman process is running for this ORACLE_SID
##
echo --------------------------------------------------------- > $LOGFILE
date >> $LOGFILE
echo ---------------- rman_bkup.ksh started ------------------ >> $LOGFILE
while [ 1 ]
do

if [ -e $LOCKFILE ]
then
##
## Some rman process is running. Check again after 3 minutes
##
echo "$LOCKFILE exists. Will check again after 3 minutes" >> $LOGFILE
echo >> $LOGFILE
        sleep 180
else
##
## No rman process is running. Create a lock file
##
        echo Do not remove this file. This is a lock file for $ORACLE_SID > $LOCKFILE
        break
fi
done
echo $ORACLE_SID >> $LOGFILE
whoami >> $LOGFILE
which rman >> $LOGFILE
rman <<EOF   >> $LOGFILE
CONNECT TARGET / ;
SHOW ALL ;
BACKUP DATABASE FILESPERSET 1 PLUS ARCHIVELOG DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
REPORT SCHEMA ;
REPORT UNRECOVERABLE ;
EXIT ;
EOF
rmanstatus=$?
if (( $rmanstatus > 0 ))
then
if [ ! -e $EMAIL_INFO_FILE ]
then
##
## The following line is executed if the Email config file is not set up.
## Replace emailid@defaultemail.com with the email id to where the email
## needs to be sent.
##
        echo "To:emailid@defaultemail.com" > $EMAIL_INFO_FILE
fi
cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script failed for $ORACLE_SID !!!" >> $EMAILFILE
        echo >> $EMAILFILE
        date >> $EMAILFILE
        echo "rman error!!! ..." >> $EMAILFILE
        echo "Not removed the lock file : $LOCKFILE " >> $EMAILFILE
        echo "Please fix the error and remove the lock file" >> $EMAILFILE
        echo "before re-running the script again!!!" >> $EMAILFILE
        echo >> $EMAILFILE
grep -i 'RMAN-' $LOGFILE >> $EMAILFILE
grep -i 'ORA-'   $LOGFILE >> $EMAILFILE
grep -i 'sbt'    $LOGFILE >> $EMAILFILE
grep -i 'tsm'    $LOGFILE >> $EMAILFILE
sendmail -F "$SENDER" -t < $EMAILFILE
cat $EMAILFILE >> $EMAILARCFILE
        exit 12
fi
echo --------------------------------------------------------- >> $LOGFILE
date >> $LOGFILE
echo ---------------- rman_bkup.ksh completed ---------------- >> $LOGFILE
echo >> $LOGFILE
cat $EMAIL_INFO_FILE > $EMAILFILE
echo "Subject: The $0 script completed successfully for $ORACLE_SID !!!" >> $EMAILFILE
cat $LOGFILE >> $EMAILFILE
sendmail -F "$SENDER" -t < $EMAILFILE
cat $EMAILFILE >> $EMAILARCFILE
cat $LOGFILE >> $LOGARCFILE
##
## Remove lock file to allow other rman processs for this ORACLE_SID to run, if any
##
rm $LOCKFILE
## ---------------------------------------------------------------------------------
## End of rman_bkup.ksh
## ---------------------------------------------------------------------------------

Writing Efficient SQL Code for Oracle - Part 2

2007-06-30T17:52:00.000-07:00

Introduction

In part 2 of this article I cover writing Efficient SQL Code for the OLTP environment.

Following topics are covered

Correlated Queries
Implicit data type conversions
Making sure indexes are considered by the Optimizer
Reduce the network traffic

Correlated Queries

Exercise caution while writing SQL statements using correlated queries. If you use lot of correlated queries in your code and if they get executed frequently then you might want to re-examine them. Correlated queries could get very expensive in terms of resources as explained in the next few slides with the help of an example.

The query shown next lists employees that have been hired between Jan 2002 and March 2002 and have been employed the longest in each division.

SQL> select emp_no, emp_division, (emp_term_dt - emp_hire_dt) emp_span

2 from emp o

3 where emp_hire_dt >= '01-JAN-2002'

4 and emp_hire_dt < '31-MAR-2002'

5 and (o.emp_term_dt - o.emp_hire_dt)

6 in

7 ( select max(i.emp_term_dt - i.emp_hire_dt)

8 from emp i

9 where i.emp_division = o.emp_division

10 and emp_hire_dt >= '01-JAN-2002'

11 and emp_hire_dt < '31-MAR-2002' ) ;

62 rows selected.

Elapsed: 00:00:43.58

Statistics

-------------------------------------------

977818 physical reads

The above sql is a correlated query. The output from the query is not shown here. Notice the execution time and physical reads.Next slide will show this query re-written without using the correlated sub query

The same sql re-written as shown below and it does not use correlated sub query. Output from the query is not shown here. Notice the drastic reduction in the execution time and the physical reads

SQL> select a.emp_division, a.emp_no, b.emp_span

2 from emp a

3 , ( select emp_division, max(i.emp_term_dt - i.emp_hire_dt) emp_span

4 from emp i

5 where emp_hire_dt >= '01-JAN-2002'

6 and emp_hire_dt < '31-MAR-2002'

7 group by emp_division ) b

8 where a.emp_division = b.emp_division

9 and (a.emp_term_dt - a.emp_hire_dt) = b.emp_span

10 and a.emp_hire_dt >= '01-JAN-2002'

11 and a.emp_hire_dt < '31-MAR-2002' ;

62 rows selected.

Elapsed: 00:00:01.82

Statistics

----------------------------------

28725 physical reads

Let us modify the query to consider the whole emp table.

Select statement with a correlated subquery

SQL> select a.emp_division, a.emp_no, (a.emp_term_dt - a.emp_hire_dt) emp_span
2 from emp a
3 , ( select emp_division, max(i.emp_term_dt - i.emp_hire_dt) max_span
4 from emp i
5 group by emp_division ) b
6 where a.emp_division = b. emp_division
7 and (a.emp_term_dt - a.emp_hire_dt) = b.max_span
8 order by emp_span ;

305 rows selected.
Elapsed: 00:29:08.75

Statistics
----------------------------------------------------------
39094846 physical reads

As can be seen above, the resource intensive nature of Correlated queries becomes very evident as the size of the driving table in the outer query increases. Now the above correlated sub query took more than 29 minutes and a huge number of physical reads.

The above query is re-written as shown below and does not use the correlated sub query and notice that the execution time and physical reads still are very low although now we are considering the entire emp table

SQL> select a.emp_division, a.emp_no, b.emp_span
2 from emp a
3 , ( select emp_division
4 , max(i.emp_term_dt - i.emp_hire_dt) emp_span
5 from emp i
6 group by emp_division ) b
7 where a.emp_division = b.emp_division
8 and (a.emp_term_dt - a.emp_hire_dt) = b.emp_span ;

305 rows selected.
Elapsed: 00:00:02.84

Statistics
-----------------------------------
28726 physical reads

Making sure indexes are considered by the Optimizer

Oracle optimizer may not consider the indexes when evaluating query plans if the columns in the where clause have any kind of transformation done on them.

For example in the following select statement notice how the ename column is transformed.

Select * from emp
Where upper(ename) = ‘&ename’

If an index exists on ename column Oracle optimizer will not consider it when evaluating the access paths for executing the above statement.

Next I explain with an example how we can make sure that indexes are considered by the Optimizer.
Consider the emp table that we used in the previous examples. This table has an index on hiredate column.
Some of the columns of emp table are shown below…

Name Type
-------------- ------------
EMPNO NUMBER(6)
ENAME VARCHAR2(10)
HIREDATE DATE
DEPTNO NUMBER(2)

Following select statement retrieves the data for all employees hired yesterday.

Select * from emp
where hiredate + 1 = sysdate ; *

When evaluating the access paths optimizer does not consider the hiredate index, instead it looks for an index on hiredate+10 and does not find one.

* Actually Oracle date columns have time component also, but I am ignoring that to keep this example simple.

If you want the optimizer to use the index then you have to rewrite the above select statement as shown below …

Select * from emp
where hiredate = sysdate -1 ;

Now the optimizer looks for an index on hiredate and finds one and uses it, if that gives a better access path.

To summarize, if you do any transformations on columns in the where clause of your SQL statement, then indexes (if any) on those columns would not be considered by Oracle optimizer while evaluating access paths.

Avoid implicit data type conversions

What is implicit data type conversion?

Implicit data type conversion occurs when the database converts the data types while applying a condition in the where clause during the execution of a query.

Consider the following table

SQL> describe SALESCENTER_CHANNEL_SUMMARY

Name Null? Type
----------------------------------- -------- -----------
SALES_CENTER_NUM NOT NULL VARCHAR2(6)
CHANNEL_NBR NOT NULL NUMBER(3)
DIVISION_NO NOT NULL NUMBER(2)
SALES NOT NULL NUMBER

SALES_CENTER_NUM column is defined as datatype VARCHAR2(6) in the above table.

select count(*)
from SALESCENTER_CHANNEL_SUMMARY
where SALES_CENTER_NUM = 213502 ;

COUNT(*)
----------
37

The above select statement worked because Oracle did the data type conversion for us when it executed the query. And because the sales_center_num data in the table happened to contain only numeric values.

If you looked at what Oracle did behind the scenes for this query, it would be

1. For each row of SALESCENTER_CHANNEL_SUMMARY

apply the where clause condition SALES_CENTER_NUM = 213502

by first converting the SALES_CENTER_NUM

into a number and then comparing against the value 213502

2. Return the filtered result set from step 1

In effect, it is as if your query is rewritten as follows…

select count(*)

from SALESCENTER_CHANNEL_SUMMARY

where to_number(SALES_CENTER_NUM) = 213502 ;

Now let us add another row to the table that has a non numeric values for sales_center num column as shown below.

SALES_ CHANNEL_NBR DIVISION_NO BASIS_COMPANY_ID BA

------ ----------- ----------- ---------------- --

4070ZP 14 40 70 ZP

Now when we execute the same query again we get the following error

SQL> select count(*)

2 from SALESCENTER_CHANNEL_SUMMARY

3 where SALES_CENTER_NUM = 213502 ;

where SALES_CENTER_NUM = 213502

ERROR at line 3:

ORA-01722: invalid number

We got an error because now there is a sales center 4070ZP in the data that could not be converted into a number. The query worked as long as the data values could be implicitly converted but failed when there is a data value that could not be converted.

Moral of the story : Do not depend on implicit data conversions.

There is a side effect to implicit conversions that needs to be mentioned also. Implicit conversions prevent the optimizer from choosing the index even if there is an index existing for that column. Implicit data conversions almost always result in full table scans. That is another reason why we should not depend on implicit data conversions.

Reduce the network traffic

High network traffic saturates the available network bandwidth in a WAN causing severe performance problems.

So we need to minimize the network traffic between

- the client application and database server if it is a two tier architecture

- the App server and the database server if it is a three tier architecture

Network traffic consists of

1. Sql statements sent from the application to the database server

2. Results sent from the database server to the application

Minimizing traffic from the application to the database server

To do this we need to minimize the amount of data going from from the application to the database server.

This can be done by moving the sql statements from the application to the database stored procedures. This means instead of sending lengthy sql statements across the network, application just invokes database stored procedures with appropriate parameters.

This results is far less traffic being generated from the application to the database server.

This approach has another benefit as well, it reduces the parsing activity to a minimum – because stored procedures are pre-parsed and can be pinned in the memory. Since parsing is a very CPU intensive activity, less parsing means less load on the CPU.

Minimizing traffic from the database server to the application

In this case, we can not minimize the amount of data sent from the database server to the application. But we can still reduce the traffic by packing more data in each of the tcp/ip packets which results in less number of packets going across the network.

This can be done by tweaking the sqlnet ARRAYSIZE. I am not going into details here but just know that it is possible to tweak it and take the help of your DBA to do that.

Conclusion

Many applications fail to scale because of the SQL code that is not efficient. Writing SQL code that works alone is not enough. It is important to make sure that your SQL code does what it does very efficiently.

I have used the techniques described here very successfully to solve many tuning problems over the last several years. I hope you find the information presented here useful.

Writing Efficient SQL Code for Oracle - Part 1

2007-06-30T15:22:00.000-07:00

Introduction

This article aims to provide you with information and tips that will help you write efficient SQL code for Oracle databases. Part 1 covers batch environment.

Why is writing efficient SQL code important?
If you would like your application to scale well, then you would need to have SQL code that executes efficiently.

How do you define efficient SQL code?
Any SQL code that does what it does, using minimum resources and in minimum possible time.

In part 1 of this article I cover the following areas where one can achieve big efficiency gains by writing efficient sql for batch environment.

Huge Inserts
Huge Updates
Huge Deletes
Efficient elimination of duplicates
Efficient verification of foreign keys

Oracle does the following when you issue a DML
After the normal syntax, semantics and security checking etc...
it would...
1. Insert/modify the data into database buffers
- This is the actual data that would subsequently be written to data files on the disks
2. Create redo log records
- These records are written to redo log files
- These are used to recover from an instance failure or crash
3. Create undo records (Rollback info)
- These records are written to rollback segments
- These records are used in case you decide to rollback

So, as you can see each DML, results in a flurry of activity which means batch jobs that deal with huge amounts of data would have unacceptable performance unless special measures are taken as described next.

Handling huge inserts
Normal insert
SQL> insert
2 into abc
3 select * from xyz ;

978816 rows created.

Elapsed: 00:08:18.08

Statistics
-------------------------------------------------
106586580 redo size

Obviously the above insert is not very efficient. It took about 8+ minutes to insert just under a million rows.

Now let us look at a turbo charged version of insert command called Direct Load Insert

SQL> insert /*+ append */
2 into abc
3 select * from xyz
4 nologging ;

978816 rows created.

Elapsed: 00:00:12.30

Statistics
----------------------------------------------------------
241716 redo size

“Direct load insert” works differently than a Normal insert. It completely by passes the 3 steps (mentioned previously) that occur for a normal DML and directly writes to the data files of the oracle database.

You should use “Direct load insert” whenever you do huge inserts.

Handling large Updates

Say you have a 30 million row table and your update affects 5 Millions rows - How would you do it ?
The normal approach would be to execute an update statement similar to the one shown in the example below

update aBigTable
set column1 = column1 + column1 * 0.10
where column3 = 'Y'
;

What do you think will happen if you executed the previous update?

Best case scenario
1. Your update finishes after running zillion hours without actually running out of rollback segment space
2. Because your update brought everything else to a grinding halt - Help desk gets inundated by calls from other users of the database wanting to know if the database has left the building!!!.

Worst case scenario
1. Your update takes zillion hours and then fails after running out of rollback segment space
2. Because your update brought everything else to a grinding halt - Help desk gets inundated by calls from other users of the database .
3. Your access privileges to the database may get revoked
As you can see even the best case scenario is not particularly good…
let us look at a better way of doing large updates
--
-- Create an empty table
--
create table aBigTable_temp
nologging
as select * from aBigTable where rownum < 1 ;
insert /*+ append */
into aBigTable_temp (column1,column2,column3)
select decode (column3, 'Y', (column1 + column1 * 0.10), column1)
, column2
, column3
from aBigTable ;
commit ;
rename aBigTable to aBigTable_old ;
rename aBigTable_temp to aBigTable ;
--
-- Notice how column1 is handled in the select clause of the insert ..select .. statement
--
--
-- create any needed indices on aBigTable
--
Your index creation statements go here.
--
-- Check if data in table aBigTable is OK and drop aBigTable_old
--
drop table aBigTable_old ;

The above steps would complete in far less time than it takes the normal update

Handling large Deletes
Say you have a 30 million row table and you need to delete 5 Millions rows - How would you do it
The normal approach would be to execute the following sql statement

Delete from xyz
where column3 = 'Y‘ ;

What do you think will happen ?
The best and worst case scenarios would be more or less the same as that for an update described previously.

Recommended approach
--
-- Create an empty table
--
create table temp_table
nologging
as select * from xyz where rownum < 1 ;
--
-- Load the rows that you want to keep in the temp table
--
insert /*+ append */
into temp_table (column1,column2,column3)
select column1 , column2 , column3
from xyz
where column3 != 'Y‘ ;
commit ;

rename xyz xyz_old ;
rename xyz_temp xyz ;
--
-- create any needed indices on xyz
--
Your index creation code goes here.
--
-- Check if data in table xyz is OK and drop xyz_old
--
drop table xyz_old ;

Efficient Duplicates elimination

Many times we are faced with situations that require us to design a load script that loads data with duplicates.

Normal method of eliminating duplicates
1.Define a unique index on the table and load data.
2.Load the data using sqlldr conventional path (direct path load can not be used because of the index)
sqlldr rejects Duplicate rows and writes them to badfile. But this unique key checking incurs a very big performance penalty. For example in my testing sqlldr took 28 minutes to load little over a million rows with one duplicate row. Using the normal method, the time it takes to eliminate duplicates grows exponentially with the size of data. So this method is not practical for bulk data loading.

Method to eliminate duplicates efficiently
Oracle assigns a unique rowid to each row in a table. So each row in a table has unique rowid even if the data within the row is duplicated.
In oracle the fastest path to data is the path that uses rowids. So if we can identify the rowids of duplicates then we can use those rowids to delete them from the table very fast.
The sample delete statement given next , identifies the duplicates and deletes them.

This sample delete statement is explained using an example in subsequent slides

delete from xyz
where rowid in
(
--
-- This select gets all the rowids
--
select a.rowid
from xyz
minus
--
-- This select gets rowids of the first row for each duplicated value for the
-- column in the group by clause
--
select min(rowid)
from xyz
group by unique_key_col
) ;

The minus operation gives us the row ids of all the duplicates that can be deleted from the table. Thus, we will have only one row for each set of duplicates in the table after the deletion th
us ridding the table of duplicates.

I use a table called emp_sal to explain the above delete statement whose contents are shown below
EMPID DEPTID SAL
---------- ---------- ----------
1 SALES 36000
2 SALES 38000
3 SALES 42000
4 ACCTS 36000
5 ACCTS 38000
5 ACCTS 42000
5 ACCTS 42000
5 ACCTS 38000
7 SALES 42000
7 SALES 35000
9 IT 42000
10 IT 45000
11 IT 52000
12 IT 55000
13 SALES 35000
15 rows selected.

This gives all rows including duplicates

SQL> select a.rowid , a.empid from emp_sal a
2 order by empid ;
ROWID EMPID
------------------ ----------
AAAYPOAAJAABio4AAA 1
AAAYPOAAJAABio4AAB 2
AAAYPOAAJAABio4AAC 3
AAAYPOAAJAABio4AAD 4
AAAYPOAAJAABio4AAE 5
AAAYPOAAJAABio4AAF 5
AAAYPOAAJAABio4AAO 5
AAAYPOAAJAABio4AAN 5
AAAYPOAAJAABio4AAG 7
AAAYPOAAJAABio4AAH 7
AAAYPOAAJAABio4AAI 9
AAAYPOAAJAABio4AAJ 10
AAAYPOAAJAABio4AAK 11
AAAYPOAAJAABio4AAL 12
AAAYPOAAJAABio4AAM 13
15 rows selected.

The following select statement gives all rows but with only one row per duplicated data

SQL> select min(a.rowid) min_rowid, a.empid
2 from emp_sal a
3 group by empid ;

MIN_ROWID EMPID
------------------ ----------
AAAYPOAAJAABio4AAA 1
AAAYPOAAJAABio4AAB 2
AAAYPOAAJAABio4AAC 3
AAAYPOAAJAABio4AAD 4
AAAYPOAAJAABio4AAE 5
AAAYPOAAJAABio4AAG 7
AAAYPOAAJAABio4AAI 9
AAAYPOAAJAABio4AAJ 10
AAAYPOAAJAABio4AAK 11
AAAYPOAAJAABio4AAL 12
AAAYPOAAJAABio4AAM 13
11 rows selected.

If we do a diff (minus operation) on the above two sets of data then we get the rowids of the duplicates which we use for deleting.

SQL> select a.rowid , a.empid from emp_sal a
2 minus
3 select min(a.rowid) , a.empid
4 from emp_sal a
5 group by empid
6 order by empid ;

ROWID EMPID
------------------ ----------
AAAYPOAAJAABio4AAF 5
AAAYPOAAJAABio4AAN 5
AAAYPOAAJAABio4AAO 5
AAAYPOAAJAABio4AAH 7
4 rows selected.

Here is the delete (With all components combined) …

SQL> delete from emp_sal
2 where rowid in
3 (
4 select a.rowid
5 from emp_sal a
6 minus
7 select min(a.rowid)
8 from emp_sal a
9 group by empid
10 ) ;
4 rows deleted.

So in this method you

1. Load the table using sqlldr direct path
2. Use a delete statement similar to the one shown on the previous slide to eliminate duplicates

Using this method I repeated the test of loading a little over million rows with one duplicate row

1.sqlldr took 58 seconds to load 1065393 rows
2.delete statement took 46 seconds.

So it took less than 2 minutes to load the table and eliminate the duplicates

Efficient Verification of Foreign Key

This is another area that causes performance problems in batch environment.
If you have a foreign key constraint defined for a column in a table, then all the inserts and updates cause Oracle to do a foreign Key checking to make sure that referential integrity is maintained. This means for each insert, Oracle would query the referenced table (parent table) to make sure FK constraint is satisfied. This does not pose a problem if you are dealing with low volumes of data. But this becomes a big issue if you do huge data loads.

So how do you load millions of rows into a table that has foreign keys defined ?

Following two methods are the most widely used but inefficient methods.
Do the load with foreign keys enabled and only rows that satisfy fk constraints get loaded
or
Do the load with foreign keys disabled.

Then try enabling the fk with a stat
ement similar to the one below

ALTER TABLE emp
ENABLE VALIDATE CONSTRAINT fk_deptno
EXCEPTIONS INTO exceptions_table

Both of the above methods are very inefficient and would require big batch windows.

The following method is very efficient for loading tables with FK constraints. It involves 3 steps…
1. Load the table with FK disabled (This will be quick since no FK checking is done)
2. Identify the rows that do not satisfy the FK constraint and delete them
3. Enable foreign key without validating (This will be quick since no FK checking is done)
Following example illustrates the use of the above method.

Desc emp
Name Type
-------------- ------------
EMPNO NUMBER(6)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(6)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Desc dept

Name Type
-------------- -------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Deptno in EMP table references Deptno in DEPT table.
So any new rows inserted into EMP table should have a DEPTNO value that exists in DEPT table.
Now to do a huge data load into EMP table
First Load the data into EMP table with FK disabled.
Then Delete from EMP table deptno values that do not exist in DEPT table as shown next
delete from emp
where rowid in
(-- This gets us the rowids that need to be deleted
select rowid
from emp
where dept_no in
( -- This identifes deptno values that do not exist in DEPT table
select distinct deptno
from emp
minus
select deptno
from dept
)
) ;

After the above step, You can re-enable the foreign key constraint on EMP table without validating. This is because we know that EMP table will have only valid deptno values after the above delete

ALTER TABLE emp
ENABLE NOVALIDATE CONSTRAINT fk_deptno ;

This concludes part 1. I have used all the techniques that were described here to make Oracle batch environment perform optimally. I hope you find these techniques useful.

Output log of restore_datafile_new.ksh

2007-06-17T15:19:00.000-07:00

Output log of restore_datafile_new.ksh. read this along with restore_datafile_new.ksh script

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 17 16:15:13 2007

RMAN>
RMAN>
connected to target database: slsd (DBID=3183102432)

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>
using target database control file instead of recovery catalog
allocated channel: disk_1
channel disk_1: sid=41 devtype=DISK

sql statement: alter database datafile 6 offline

executing command: SET NEWNAME

Starting restore at 17-JUL-07

channel disk_1: starting datafile backupset restore
channel disk_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u02/oradata/slsd/new_users01.dbf
channel disk_1: reading from backup piece /u06/flash_recovery_area/slsd/backupset/2007_07_13/o1_mf_nnndf_TAG20070713T142343_39hjzqbh_.bkp
channel disk_1: restored backup piece 1
piece handle=/u06/flash_recovery_area/slsd/backupset/2007_07_13/o1_mf_nnndf_TAG20070713T142343_39hjzqbh_.bkp tag=TAG20070713T142343
channel disk_1: restore complete, elapsed time: 00:00:08
Finished restore at 17-JUL-07

datafile 6 switched to datafile copy
input datafile copy recid=10 stamp=628186526 filename=/u02/oradata/slsd/new_users01.dbf

Starting recover at 17-JUL-07

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u06/flash_recovery_area/slsd/archivelog/2007_07_14/o1_mf_1_3_39kwb3tf_.arc
archive log thread 1 sequence 4 is already on disk as file /u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_4_39ogg8pf_.arc
archive log thread 1 sequence 5 is already on disk as file /u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_5_39ogtt12_.arc
archive log thread 1 sequence 6 is already on disk as file /u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_6_39ogtvj5_.arc
archive log thread 1 sequence 7 is already on disk as file /u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_7_39ogtxon_.arc
archive log thread 1 sequence 8 is already on disk as file /u06/flash_recovery_area/slsd/archivelog/2007_07_16/o1_mf_1_8_39rb0ylf_.arc
channel disk_1: starting archive log restore to default destination
channel disk_1: restoring archive log
archive log thread=1 sequence=2
channel disk_1: reading from backup piece /u06/flash_recovery_area/slsd/backupset/2007_07_13/o1_mf_annnn_TAG20070713T143007_39hk5kjq_.bkp
channel disk_1: restored backup piece 1
piece handle=/u06/flash_recovery_area/slsd/backupset/2007_07_13/o1_mf_annnn_TAG20070713T143007_39hk5kjq_.bkp tag=TAG20070713T143007
channel disk_1: restore complete, elapsed time: 00:00:02
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_17/o1_mf_1_2_39t8v19h_.arc thread=1 sequence=2
channel default: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_17/o1_mf_1_2_39t8v19h_.arc recid=60 stamp=628186529
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_14/o1_mf_1_3_39kwb3tf_.arc thread=1 sequence=3
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_4_39ogg8pf_.arc thread=1 sequence=4
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_5_39ogtt12_.arc thread=1 sequence=5
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1_6_39ogtvj5_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:04
Finished recover at 17-JUL-07

sql statement: alter database datafile 6 online
released channel: disk_1

RMAN>
RMAN>

Recovery Manager complete.

restore_datafile_new.ksh

2007-06-17T14:58:00.000-07:00

This script is for restoring datafile to a new location/name and recovering it.

#!/bin/ksh
. ~/.profile

##
## script name : restore_datafile_new.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring datafile to a new location/name
## and recovering it.
##
## Note :
## To execute this script You need login as a unix id that belongs to DBA group.
## Make sure you have configured the rman automatic channels etc..
## I have tested this script on UNIX/LINUX platform using Oracle 10g.
## Use it at your own risk.
##
## This is an example or sample script
##

export ORACLE_SID=slsd

rman << EOF > ./rstr_datafile_new.log

connect target /

##
## Specify the datafile# to be restored and recovered
## Specify new location/name
##

run
{
allocate channel disk_1 device type disk ;
sql "alter database datafile 6 offline" ;
set newname for datafile '/u02/oradata/slsd/users01.dbf' TO '/u02/oradata/slsd/new_users01.dbf' ;
restore datafile 6 ;
switch datafile 6 ;
recover datafile 6 ;
sql "alter database datafile 6 online" ;
}

EOF

Output log of restore_datafile.ksh

2007-06-16T19:00:00.000-07:00

This is the output log of restore_datafile.ksh. Read this along with restore_datafile.ksh

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 16 15:58:15 2007

RMAN>
RMAN>
connected to target database: slsd (DBID=3183102432)

RMAN>
RMAN> 2> 3> 4>
using target database control file instead of recovery catalog
sql statement: alter database datafile 6 offline

RMAN>
Starting restore at 16-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=39 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=58 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=40 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=41 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=55 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=42 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=63 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=56 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: Tivoli Data Protection for Oracle: version 5.2.0.0

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u02/oradata/slsd/users01.dbf
channel ORA_DISK_1: reading from backup piece /u06/flash_recovery_area/slsd/back
upset/2007_07_13/o1_mf_nnndf_TAG20070713T142343_39hjzqbh_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u06/flash_recovery_area/slsd/backupset/2007_07_13/o1_mf_nnndf_TAG2
0070713T142343_39hjzqbh_.bkp tag=TAG20070713T142343
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 16-JUL-07

RMAN>
Starting recover at 16-JUL-07
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u06/flash_recovery_a
rea/slsd/archivelog/2007_07_14/o1_mf_1_3_39kwb3tf_.arc
archive log thread 1 sequence 4 is already on disk as file /u06/flash_recovery_a
rea/slsd/archivelog/2007_07_15/o1_mf_1_4_39ogg8pf_.arc
archive log thread 1 sequence 5 is already on disk as file /u06/flash_recovery_a
rea/slsd/archivelog/2007_07_15/o1_mf_1_5_39ogtt12_.arc
archive log thread 1 sequence 6 is already on disk as file /u06/flash_recovery_a
rea/slsd/archivelog/2007_07_15/o1_mf_1_6_39ogtvj5_.arc
archive log thread 1 sequence 7 is already on disk as file /u06/flash_recovery_a
rea/slsd/archivelog/2007_07_15/o1_mf_1_7_39ogtxon_.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: reading from backup piece /u06/flash_recovery_area/slsd/back
upset/2007_07_13/o1_mf_annnn_TAG20070713T143007_39hk5kjq_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u06/flash_recovery_area/slsd/backupset/2007_07_13/o1_mf_annnn_TAG2
0070713T143007_39hk5kjq_.bkp tag=TAG20070713T143007
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_16/o1_mf_1
_2_39qmhxq7_.arc thread=1 sequence=2
channel default: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_16/o1_mf_1
_2_39qmhxq7_.arc recid=57 stamp=628099133
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_14/o1_mf_1
_3_39kwb3tf_.arc thread=1 sequence=3
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1
_4_39ogg8pf_.arc thread=1 sequence=4
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_15/o1_mf_1
_5_39ogtt12_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:06
Finished recover at 16-JUL-07

RMAN>
sql statement: alter database datafile 6 online

RMAN>
RMAN>
RMAN>

Recovery Manager complete.

Restore datafile from disk (restore_datafile.ksh)

2007-06-16T18:56:00.000-07:00

This script restores datafile from disk to the original location.

#!/bin/ksh
. ~/.profile

##
## script name : restore_datafile.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring and recovering a datafile from disk.
##
## Note :
## To execute this script You need login as a unix id that belongs to DBA group.
## Make sure you have configured the rman automatic channels etc..
## I have tested this script on UNIX/LINUX platform using Oracle 10g.
## Use it at your own risk.
##
## This is an example or sample script
##

export ORACLE_SID=stgd

rman << EOF > ./rstr_datafile.log

connect target /

##
## Specify the datafile# to be restored and recovered
##
sql "alter database datafile 6 offline" ;
restore datafile 6 ;
recover datafile 6 ;
sql "alter database datafile 6 online" ;

EOF

Output log of restore_controlfile_from_tape.ksh

2007-06-14T02:38:00.000-07:00

Output log of restore_controlfile_from_tape.ksh

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 13 14:21:58 2007

RMAN>
RMAN>
connected to target database (not started)

RMAN>
RMAN>
Oracle instance started

Total System Global Area 402653184 bytes

Fixed Size 2021192 bytes
Variable Size 146802872 bytes
Database Buffers 247463936 bytes
Redo Buffers 6365184 bytes

RMAN>
RMAN> 2> 3> 4>
executing command: SET DBID

RMAN>
RMAN> 2> 3> 4>
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: tape_1
channel tape_1: sid=62 devtype=SBT_TAPE
channel tape_1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 13-JUL-07

channel tape_1: looking for autobackup on day: 20070713
channel tape_1: autobackup found: c-3183102432-20070713-06
channel tape_1: control file restore from autobackup complete
output filename=/u01/oradata/slsd/control01.ctl
output filename=/u05/oradata/slsd/control02.ctl
output filename=/u07/oradata/slsd/control03.ctl
Finished restore at 13-JUL-07
released channel: tape_1

RMAN> 2> 3> 4>
database mounted

RMAN> 2> 3> 4> 5> 6> 7>
Starting recover at 13-JUL-07
Starting implicit crosscheck backup at 13-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=62 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=60 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=59 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=58 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 13-JUL-07

Starting implicit crosscheck copy at 13-JUL-07
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 13-JUL-07

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=57 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=56 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=55 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=54 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: Tivoli Data Protection for Oracle: version 5.2.0.0

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/oradata/slsd/red
o3b.rdo
archive log filename=/u02/oradata/slsd/redo3b.rdo thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-JUL-07

RMAN>
database opened

RMAN> 2> 3> 4> 5> 6>

Recovery Manager complete.

restore_controlfile_from_tape.ksh

2007-06-14T02:19:00.000-07:00

If you have lost all copies of the controlfile then use this script to restore controlfile from your rman tape backup

#!/bin/ksh
. ~/.profile

##
## script name : restore_controlfile_from_tape.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring the controlfile from tape (if you have lost all copies of control
## file). It assumes you have an rman backup (that was done with controlfile autobackup)
## availabale on tape
##
## Note :
## You have to replace DBID value with the DBID that you are attempting to restore the controlfile
## for. To execute this script You need login as a unix id that belongs to DBA group.
## You have to modify the tape or sbt settings to suit your environment
## Make sure you have configured the rman automatic channels etc..
## I have tested this script on UNIX/LINUX platform using Oracle 10g. Use it at your own risk.
##
## This is an example or sample script
##

export ORACLE_SID=slsd

rman << EOF > ./rstr_controlfile_from_tape.log

CONNECT TARGET /

STARTUP NOMOUNT;

##
## set dbid because we are not using an rman catalog
##
SET DBID 3183102432 ;

##
## Specify AUTOBACKUP Format
##
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F';
##
## Restore controlfile from autobackup first
##
RUN {
ALLOCATE CHANNEL tape_1 DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
RESTORE CONTROLFILE FROM AUTOBACKUP ;
}
##
## Mount the database with newly restored controlfile
##
alter database mount ;
##
## Issue a "recover database ;" command to make sure that we don't run into
## any "ORA-01152: file 1 was not restored from a sufficiently old backup"
## kind of errors.
## Open the database with "alter database open resetlogs ;"
##
recover database ;
alter database open resetlogs ;
##
## Make sure that you do a complete database backup after opening
## the database with resetlogs option
##

EOF

Outputlog of restore_controlfile.ksh

2007-06-14T02:16:00.000-07:00

This shows the screen output that gets produced when restore_controlfile.ksh is run

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 13 13:38:13 2007

RMAN>
RMAN>
connected to target database (not started)

RMAN>
RMAN>
Oracle instance started

Total System Global Area 402653184 bytes

Fixed Size 2021192 bytes
Variable Size 142608568 bytes
Database Buffers 251658240 bytes
Redo Buffers 6365184 bytes

RMAN>
RMAN> 2> 3> 4>
executing command: SET DBID

RMAN>
RMAN> 2> 3> 4>
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

RMAN> 2> 3> 4> 5> 6> 7>
allocated channel: disk_1
channel disk_1: sid=62 devtype=DISK

Starting restore at 13-JUL-07

recovery area destination: /u06/flash_recovery_area
database name (or database unique name) used for search: slsd
channel disk_1: no autobackups found in the recovery area
channel disk_1: looking for autobackup on day: 20070713
channel disk_1: autobackup found: controlfile_c-3183102432-20070713-03
channel disk_1: control file restore from autobackup complete
output filename=/u01/oradata/slsd/control01.ctl
output filename=/u05/oradata/slsd/control02.ctl
output filename=/u07/oradata/slsd/control03.ctl
Finished restore at 13-JUL-07
released channel: disk_1

RMAN> 2> 3> 4>
database mounted

RMAN> 2> 3> 4> 5> 6> 7>
Starting recover at 13-JUL-07
Starting implicit crosscheck backup at 13-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=62 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=60 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=59 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=58 devtype=DISK
Crosschecked 7 objects
Crosschecked 13 objects
Finished implicit crosscheck backup at 13-JUL-07

Starting implicit crosscheck copy at 13-JUL-07
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 13-JUL-07

searching for all files in the recovery area
cataloging files...
no files cataloged

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u04/oradata/slsd/red
o2a.rdo
archive log filename=/u04/oradata/slsd/redo2a.rdo thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-JUL-07

RMAN>
database opened

RMAN> 2> 3> 4> 5> 6>

Recovery Manager complete.

restore_controlfile.ksh

2007-06-14T02:09:00.000-07:00

If you have lost all copies of the controlfile then use this script to restore the controlfile from rman disk backup

#!/bin/ksh
. ~/.profile

##
## script name : restore_controlfile.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring the controlfile from disk (if you have lost all copies of
## the controlfile). It assumes you have an rman backup (that was done with controlfile
## autobackup option)
##
## Note :
## You have to replace DBID value with the DBID that you are attempting to restore the spfile
## for. To execute this script You need login as a unix id that belongs to DBA group.
## Make sure you have configured the rman automatic channels etc..
## I have tested this script on UNIX/LINUX platform using Oracle 10g. Use it at your own risk.
##
## This is an example or sample script
##

export ORACLE_SID=slsd

rman << EOF > ./rstr_controlfile.log

CONNECT TARGET /

STARTUP NOMOUNT;

##
## set dbid because we are not using an rman catalog
##
SET DBID 3183102432 ;

##
## Specify AUTOBACKUP Format
##
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F' ;
##
## Restore controlfile from autobackup
##
RUN {
ALLOCATE CHANNEL disk_1 DEVICE TYPE disk ;
RESTORE CONTROLFILE FROM AUTOBACKUP ;
}
##
## Mount the database with newly restored controlfile
##
alter database mount ;
##
## Issue a "recover database ;" command to make sure that we don't run into
## any "ORA-01152: file 1 was not restored from a sufficiently old backup"
## kind of errors.
## Open the database with "alter database open resetlogs ;"
##
recover database ;
alter database open resetlogs ;
##
## Make sure that you do a complete database backup after opening
## the database with resetlogs option
##

EOF

Output log of restore_spfile_from_tape.ksh

2007-06-13T04:08:00.000-07:00

Output log of restore_spfile_from_tape.ksh, See restore_spfile_from_tape.ksh

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 12 16:12:16 2007

RMAN>
RMAN>
connected to target database (not started)

RMAN>
RMAN>
startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/u01/oracle/product/10.2.0/dbs/spfilestgd.
ora'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 2019328 bytes
Variable Size 67108864 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes

RMAN>
RMAN> 2> 3> 4>
executing command: SET DBID

RMAN>
RMAN> 2> 3> 4>
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: tape_1
channel tape_1: sid=37 devtype=SBT_TAPE
channel tape_1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 12-JUL-07

channel tape_1: looking for autobackup on day: 20070712
channel tape_1: autobackup found: c-3183102432-20070712-02
channel tape_1: SPFILE restore from autobackup complete
Finished restore at 12-JUL-07
released channel: tape_1

RMAN> 2> 3> 4>
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area 402653184 bytes

Fixed Size 2021192 bytes
Variable Size 130025656 bytes
Database Buffers 264241152 bytes
Redo Buffers 6365184 bytes

RMAN>
RMAN>

Recovery Manager complete.

restore_spfile_from_tape.ksh

2007-06-13T04:05:00.000-07:00

This script is for restoring the spfile from tape to default location.

#!/bin/ksh
. ~/.profile

##
## script name : restore_spfile_from_tape.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring the spfile from tape to default location.
##
## Note :
## You have to replace DBID value with the DBID that you are attempting to restore the spfile for.
## You have to modify sbt settings to suit your environment.
## To execute this script You need login as a unix id that belongs to DBA group.
## I have tested this script on UNIX/LINUX platform using Oracle 10g. Use it at your own risk.
##
## This is an example or sample script
##

export ORACLE_SID=slsd

rman << EOF > ./rstr_spfile_from_tape.log

CONNECT TARGET /

STARTUP FORCE NOMOUNT;

##
## set dbid because we are not using an rman catalog
##
SET DBID 3183102432 ;

##
## Specify AUTOBACKUP Format
##
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F';
##
## Restore spfile from autobackup first
##
RUN {
ALLOCATE CHANNEL tape_1 DEVICE TYPE sbt
PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'
FORMAT '%u_%p_%c' ;
RESTORE SPFILE FROM AUTOBACKUP ;
}
##
## Restart the database with newly restored SPFILE
##
SHUTDOWN IMMEDIATE;
STARTUP ;

EOF

Output log of restore_spfile.ksh

2007-06-13T04:01:00.000-07:00

Output log of restore_spfile.ksh, see restore_spfile.ksh to make sense of this log

restore_spfile.ksh

2007-06-13T03:57:00.000-07:00

This script is for restoring the spfile from disk to default location

#!/bin/ksh
. ~/.profile

##
## script name : restore_spfile.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script is for restoring the spfile from disk to default location.
##
## Note :
## You have to replace DBID value with the DBID that you are attempting to restore the spfile for.
## To execute this script You need login as a unix id that belongs to DBA group.
## I have tested this script on UNIX/LINUX platform using Oracle 10g. Use it at your own risk.
##
## This is an example or sample script
##

export ORACLE_SID=stgd

rman << EOF > ./rstr_spfile.log

CONNECT TARGET /

STARTUP FORCE NOMOUNT;

##
## set dbid because we are not using an rman catalog
##
SET DBID 3183102432 ;

##
## Specify AUTOBACKUP Format
##
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F' ;
##
## Restore spfile from autobackup first
##
RUN {
ALLOCATE CHANNEL disk_1 DEVICE TYPE disk ;
RESTORE SPFILE FROM AUTOBACKUP ;
}
##
## Restart the database with newly restored SPFILE
##
SHUTDOWN IMMEDIATE;
STARTUP ;

EOF
##
## End of restore_spfile.ksh
##

Normalization process explained

2007-06-07T15:01:00.000-07:00

I will attempt to explain the normalization process by taking you through the process step by step using an example.

The purpose of normalization process , to state in layman's terms - is to eliminate data redundancy, data maintenance (insert, update and delete) anomalies and to establish relationships between the tables that make logical sense
Generally speaking, normalization process involves splitting of a non normalized table (or a lump of data) into multiple normalized tables.

Let us start by looking at the following fictitious invoices and try to design tables that are in 3rd normal form and that can capture the data in the fictitious invoices.

---------------------------------------------------------------------------------------

Inv No : 345987
Inv Dt : 08/08/2005

Customer Type : R Customer Type Desc : Retail

Custome Id : 621
PC Center Inc
Address : 123, Peach street, Atlanta, GA 30035

Item no Description Quantity Amount (USD)
123 Keyboard 50 500.00
987 IDE hard disk 80 GB 30 1200.00
---------------
Total 1700.00

---------------------------------------------------------------------------------------

Inv No : 355987
Inv Dt : 08/10/2006

Customer Type : W Customer Type Desc : Wholesale

Custome Id : 525
PC Mart Inc
Address : 56, Carrington Cove, Atlanta, GA 30029

Item no Description Quantity Amount (USD)
481 Optical Mouse 500 2500.00
987 IDE hard disk 80 GB 300 10000.00
----------------
Total 12500.00
---------------------------------------------------------------------------------------

As a first cut, we create a table that will capture the above data. Each invoice will be a record in this table.
Let us call this table invoice_lump and choose inv_no as the primary key.

shown below is this invoice_lump table with the above invoice data. Click on the table name if you want to see an unwrapped version of this table

Table_Name : invoice_lump Primary key : inv_no

Inv_no Inv_date CustId CustName CustAddr CustCity CustState CustZip CustType CustTypeDesc Item_1_No Item_1_Desc Item_1_Qty Item_1_Amt Item_2_No Item_2_Desc Item_2_Qty Item_2_Amt
345987 08/08/2005 621 PC Center Inc 123, Peach street Atlanta GA 30035 R Retail 123 Keyboard 50 500.00 987 IDE hard disk(80 GB) 30 1200.00
355987 08/10/2006 525 PC Mart Inc 56, Carrington Cove Atlanta GA 30029 W Wholesale 481 Optical Mouse 500 2500.00 987 IDE hard disk(80 GB) 300 10000.00

You can see straight away the problems with the above table as listed below...
If an invoce contains more than 2 items then we will need to add more columns to accomadate those items.
The customer and item information is redundently repeated in the table.
If we need to update a customer address or item desc then we will have to do a full table scan which is ineffecient.
You can not add a new customer that never placed an order or add a new item information that does not have sales.

The process of normalization is aimed at resolving the above issues by designing tables in such a way that the above
problems are eliminated. So let us start normalizing the invoice_lump table.

First Normal Form

A table is said to be in First Normal Form if it has no repeating elements or groups of elements.
The repeating groups should be moved into their own tables to conform to First Normal Form.

We can immediately see that Item information is repeating since each invoice can have multiple items. So we split the invoice_lump table into 2 tables as shown below.

Table : INV_HDR, Primary Key ; Inv_No

Inv_no Inv_date CustId CustName CustAddr CustCity CustState CustZi
p CustType CustTypeDesc
345987 08/08/2005 621 PC Center Inc 123, Peach street Atlanta GA 30035 R Retail
355987 08/10/2006 525 PC Mart Inc 56, Carrington Cove Atlanta GA 30029 W Wholesale

Table : INV_LINES, Primary Key ; Inv_No ,Item_No

Inv_no Item_No Item_Desc Item_Qty Item_Amt
345987 123 Keyboard 50 500.00
345987 987 IDE hard disk(80 GB) 30 1200.00
355987 481 Optical Mouse 500 2500.00
355987 987 IDE hard disk(80 GB) 300 10000.00

The above tables have no repeating elements or groups of elements. So they are in First Normal Form.

The above table still have the problem of data redundancy. To address this we will need to further normalize the tables.

Second Normal Form

A table is said to be in Second Normal Form if it meets the First Normal Form requirement and
if every non-key field in that table is functionally dependent on the entire PK. There should be no partial dependencies.

Now let us examine the above two tables (inv_hdr and inv_lines) and see if they conform to Second Normal Form.

These tables already conform to First Normal Form so they partially meet Second Normal Form requirement already.

We can see that in the inv_hdr table the Customer attributes are not functionally dependent on inv_no (which is the PK ).
Hence the Customer attributes need to be separated into their own table.

In the inv_lines table, the item attributes are only dependent on Item_no column of the PK which constitutes partially dependency.
Hence the item attributes need to be separated into their own table.

So We split inv_hdr table into inv_hdr and Customer_mstr tables and we split inv_lines into inv_lines and Item_mstr tables.

Table : INV_HDR, Primary Key : Inv_No

Inv_no Inv_date CustId
345987 08/08/2005 621
355987 08/10/2006 525

Table : CUSTOMER_MSTR, Primary Key ; CustId

CustId CustName CustAddr CustCity CustState CustZip CustType CustTypeDesc
621 PC Center Inc 123, Peach street Atlanta GA 30035 R Retail
525 PC Mart Inc 56, Carrington Cove Atlanta GA 30029 W Wholesale

Table : INV_LINES, Primary Key : Inv_No ,Item_No

Inv_no Item_No Item_Qty Item_Amt
345987 123 50 500.00
345987 987 30 1200.00
355987 481 500 2500.00
355987 987 300 10000.00

Table : ITEM_MSTR, Primary Key : Item_No

Item_No Item_Desc
123 Keyboard
481 Optical Mouse
987 IDE hard disk(80 GB)

The above tables are in Second Normal Form because
- they meet the requirement for First Normal Form
- all non-key fields are functionally dependent on the PK in each of these tables

Third Normal Form

A table is said to be in Third Normal Form if it meets the Second Normal Form requirement
and there are no transitive dependencies

If a non-key field is functionally dependent on another non-key field and is indirectly dependent on the primary key through that non-key field then that constitutes a transitive dependency .

Of the above 4 tables we can only see the transitive dependency in the customer_mstr table.
The rest of the tables have no transitive dependencies, hence they already conform to TNF.

The Customer_mstr table has a field called CustTypeDesc which is dependent on a non-key field called CustType.
The field CustTypeDesc is dependent on the CustId field (which is the PK) through the CustType field.
This constitutes transitive dependency.
Hence we have to split that relationship into another table in order to bring the Customer_Mstr table into TNF

Table : CUSTOMER_MSTR, Primary Key ; CustId

CustId CustName CustAddr CustCity CustState CustZip CustType
621 PC Center Inc 123, Peach street Atlanta GA 30035 R
525 PC Mart Inc 56, Carrington Cove Atlanta GA 30029 W

Table : CUSTTYPE_LKUP, Primary Key ; CustId

CustType CustTypeDesc
R Retail
W Wholesale

The above tables are in Third Normal Form because
- they meet the requirement for Second Normal Form
- there are no transitive dependencies

This completes the normalization process of the INVOICE_LUMP table.

We started with a single non normalized table and ended up with 5 separate tables that are in third normal form.
You can see that in each of the above normalized tables, every non-key field depends on the primary key, the complete primary key and nothing but the primary key.

I hope this article helps you understand the process of normalization.

Output log of rman_bkup.ksh

2007-06-03T13:19:00.000-07:00

This shows the output log of rman_bkup.ksh

---------------------------------------------------------
Tue Jun 26 14:26:50 EDT 2007
---------------- rman_bkup.ksh started ------------------
sls1
oracle
/u01/oracle/product/agent10g/bin/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 26 14:26:50 2007

RMAN>
RMAN>
connected to target database: sls1 (DBID=1522268517)

RMAN>
RMAN>
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' FORMAT '%u_%p_%c';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/product/10.2.0/dbs/snapcf_sls1.f'; # default

RMAN>
RMAN> 2> 3> 4> 5> 6> 7>

Starting backup at 26-JUN-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=133 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=132 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=141 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=16 recid=417 stamp=626279214
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142658_382pmmo0_.bkp tag=TAG20070626T142658 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/sls1/archivelog/2007_06_26/o1_mf_1_16_382pmgt9_.arc recid=417 stamp=626279214
Finished backup at 26-JUN-07

Starting backup at 26-JUN-07
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/u03/oradata/sls1/sls1_data_01.dbf
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00005 name=/u03/oradata/sls1/sls1_data_02.dbf
channel ORA_DISK_2: starting piece 1 at 26-JUN-07
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00007 name=/u04/oradata/sls1/sls1_index_01.dbf
channel ORA_DISK_3: starting piece 1 at 26-JUN-07
channel ORA_DISK_4: starting full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00009 name=/u05/oradata/sls1/sls1aud_arch_01.dbf
channel ORA_DISK_4: starting piece 1 at 26-JUN-07
channel ORA_DISK_4: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382pms0g_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:05:56
channel ORA_DISK_4: starting full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00012 name=/u05/oradata/sls1/sls1hist_arch_01.dbf
channel ORA_DISK_4: starting piece 1 at 26-JUN-07
channel ORA_DISK_3: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382pmpqw_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:06:33
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/sls1/system01.dbf
channel ORA_DISK_3: starting piece 1 at 26-JUN-07
channel ORA_DISK_3: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382q019s_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:03:35
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00006 name=/u03/oradata/sls1/sls1_data_03.dbf
channel ORA_DISK_3: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382pmpov_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:46
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/u04/oradata/sls1/sls1_index_02.dbf
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_2: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382pmppv_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:12:14
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00010 name=/u05/oradata/sls1/sls1aud_arch_02.dbf
channel ORA_DISK_2: starting piece 1 at 26-JUN-07
channel ORA_DISK_3: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382q6z41_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:04:02
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00002 name=/u05/oradata/sls1/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 26-JUN-07
channel ORA_DISK_2: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382qbwdp_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:02:14
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=/u02/oradata/sls1/sysaux01.dbf
channel ORA_DISK_2: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382q9wwh_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:38
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1:
specifying datafile(s) in backupset
input datafile fno=00011 name=/u02/oradata/sls1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382qjqpj_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/u05/oradata/sls1/FLOW_1.dbf
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_2: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382qh399_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:58
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382qkxlw_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:01
channel ORA_DISK_4: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382pyyjy_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:29:02
channel ORA_DISK_3: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_nnndf_TAG20070626T142701_382qgsmc_.bkp tag=TAG20070626T142701 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:22:51
Finished backup at 26-JUN-07

Starting backup at 26-JUN-07
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=17 recid=418 stamp=626281450
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T150411_382rsf32_.bkp tag=TAG20070626T150411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/sls1/archivelog/2007_06_26/o1_mf_1_17_382rsb9f_.arc recid=418 stamp=626281450
Finished backup at 26-JUN-07

Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=/u01/oracle/product/10.2.0/dbs/controlfile_c-1522268517-20070626-03 comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07

RMAN>
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=136 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=139 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=140 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=138 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: Tivoli Data Protection for Oracle: version 5.2.0.0
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 164 14-JUN-07
Backup Piece 230 14-JUN-07 54ik8t17_1_2
Backup Set 164 14-JUN-07
Backup Piece 205 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v7rf_.bkp
Backup Set 165 14-JUN-07
Backup Piece 231 14-JUN-07 55ik8t17_1_2
Backup Set 165 14-JUN-07
Backup Piece 206 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v7tr_.bkp
Backup Set 162 14-JUN-07
Backup Piece 229 14-JUN-07 56ik8t17_1_2
Backup Set 162 14-JUN-07
Backup Piece 203 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v7vs_.bkp
Backup Set 161 14-JUN-07
Backup Piece 202 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v9vs_.bkp
Backup Set 161 14-JUN-07
Backup Piece 217 14-JUN-07 57ik8t17_1_2
Backup Set 172 14-JUN-07
Backup Piece 225 14-JUN-07 58ik8tcl_1_2
Backup Set 172 14-JUN-07
Backup Piece 213 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_37336ohr_.bkp
Backup Set 163 14-JUN-07
Backup Piece 218 14-JUN-07 59ik8tdo_1_2
Backup Set 163 14-JUN-07&nb
sp;
Backup Piece 204 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_37337snk_.bkp
Backup Set 166 14-JUN-07
Backup Piece 219 14-JUN-07 5aik8tjs_1_2
Backup Set 166 14-JUN-07
Backup Piece 207 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733g0j5_.bkp
Backup Set 168 14-JUN-07
Backup Piece 209 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733kmdh_.bkp
Backup Set 168 14-JUN-07
Backup Piece 221 14-JUN-07 5bik8tng_1_2
Backup Set 167 14-JUN-07
Backup Piece 220 14-JUN-07 5cik8tol_1_2
Backup Set 167 14-JUN-07
Backup Piece 208 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733lswb_.bkp
Backup Set 173 14-JUN-07
Backup Piece 214 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733p33c_.bkp
Backup Set 173 14-JUN-07
Backup Piece 226 14-JUN-07 5dik8trv_1_2
Backup Set 170 14-JUN-07
Backup Piece 223 14-JUN-07 5eik8tsq_1_2
Backup Set 170 14-JUN-07
Backup Piece 211 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733pwvf_.bkp
Backup Set 169 14-JUN-07
Backup Piece 210 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733rlpk_.bkp
Backup Set 169 14-JUN-07
Backup Piece 222 14-JUN-07 5fik8tui_1_2
Backup Set 171 14-JUN-07
Backup Piece 224 14-JUN-07 5gik8tvm_1_2
Backup Set 171 14-JUN-07
Backup Piece 212 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733srm3_.bkp
Backup Set 174 14-JUN-07
Backup Piece 227 14-JUN-07 5hik8v8l_1_2
Backup Set 174 14-JUN-07
Backup Piece 215 14-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_annnn_TAG20070614T151741_37352pv5_.bkp
Backup Set 177 15-JUN-07
Backup Piece 236 15-JUN-07 5kikb9l5_1_2
Backup Set 177 15-JUN-07
Backup Piece 233 15-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_15/o1_mf_annnn_TAG20070615T122717_375hh73n_.bkp
Backup Set 182 26-JUN-07
Backup Piece 240 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbd8_.bkp
Backup Set 183 26-JUN-07
Backup Piece 241 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbgc_.bkp
Backup Set 184 26-JUN-07
Backup Piece 242 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbhd_.bkp
Backup Set 180 26-JUN-07
Backup Piece 238 26-JUN-0
7 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbf8_.bkp
Backup Set 181 26-JUN-07
Backup Piece 239 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382ofrxg_.bkp
Backup Set 186 26-JUN-07
Backup Piece 244 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142413_382pggv5_.bkp
Backup Set 188 26-JUN-07
Backup Piece 246 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142533_382pjytc_.bkp
Backup Set 189 26-JUN-07
Backup Piece 247 26-JUN-07 /u01/oracle/product/10.2.0/dbs/controlfile_c-1522268517-20070626-02
Backup Set 190 26-JUN-07
Backup Piece 248 26-JUN-07 /u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142658_382pmmo0_.bkp
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v7rf_.bkp recid=205 stamp=625243175
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v7tr_.bkp recid=206 stamp=625243175
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v7vs_.bkp recid=203 stamp=625243175
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3732v9vs_.bkp recid=202 stamp=625243177
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_37336ohr_.bkp recid=213 stamp=625243541
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_37337snk_.bkp recid=204 stamp=625243577
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733g0j5_.bkp recid=207 stamp=625243774
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733kmdh_.bkp recid=209 stamp=625243890
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733lswb_.bkp recid=208 stamp=625243928
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733p33c_.bkp recid=214 stamp=625244034
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733pwvf_.bkp recid=211 stamp=625244058
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733rlpk_.bkp recid=210 stamp=625244114
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_nnndf_TAG20070614T143933_3733srm3_.bkp recid=212 stamp=625244151
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_14/o1_mf_annnn_TAG20070614T151741_37352pv5_.bkp recid=215 stamp=625245462
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_15/o1_mf_annnn_TAG20070615T122717_375hh73n_.bkp recid=233 stamp=625321639
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbd8_.bkp recid=240 stamp=626277962
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbgc_.bkp recid=241 stamp=626277962
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbhd_.bkp recid=242 stamp=626277962
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382odbf8_.bkp recid=238 stamp=626277962
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T140558_382ofrxg_.bkp recid=239 stamp=626278008
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142413_382pggv5_.bkp recid=244 stamp=626279054
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142533_382pjytc_.bkp recid=246 stamp=626279134
deleted backup piece
backup piece handle=/u01/oracle/product/10.2.0/dbs/controlfile_c-1522268517-20070626-02 recid=247 stamp=626279136
deleted backup piece
backup piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142658_382pmmo0_.bkp recid=248 stamp=626279219
Deleted 24 objects

deleted backup piece
backup piece handle=54ik8t17_1_2 recid=230 stamp=625248833
deleted backup piece
backup piece handle=55ik8t17_1_2 recid=231 stamp=625249002
deleted backup piece
backup piece handle=56ik8t17_1_2 recid=229 stamp=625248832
deleted backup piece
backup piece handle=57ik8t17_1_2 recid=217 stamp=625248832
deleted backup piece
backup piece handle=58ik8tcl_1_2 recid=225 stamp=625249429
deleted backup piece
backup piece handle=59ik8tdo_1_2 recid=218 stamp=625248849
deleted backup piece
backup piece handle=5aik8tjs_1_2 recid=219 stamp=625249055
deleted backup piece
backup piece handle=5bik8tng_1_2 recid=221 stamp=625249186
deleted backup piece
backup piece handle=5cik8tol_1_2 recid=220 stamp=625249144
deleted backup piece
backup piece handle=5dik8trv_1_2 recid=226 stamp=625249433
deleted backup piece
backup piece handle=5eik8tsq_1_2 recid=223 stamp=625249316
deleted backup piece
backup piece handle=5fik8tui_1_2 recid=222 stamp=625249313
deleted backup piece
backup piece handle=5gik8tvm_1_2 recid=224 stamp=625249413
deleted backup piece
backup piece handle=5hik8v8l_1_2 recid=227 stamp=625249481
deleted backup piece
backup piece handle=5kikb9l5_1_2 recid=236 stamp=625330580
Deleted 15 objects

RMAN>
RMAN>
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1024 SYSTEM *** /u02/oradata/sls1/system01.dbf
2 &n
bsp; 512 UNDOTBS1 *** /u05/oradata/sls1/undotbs01.dbf
3 512 SYSAUX *** /u02/oradata/sls1/sysaux01.dbf
4 2040 SLS1_DATA *** /u03/oradata/sls1/sls1_data_01.dbf
5 2040 SLS1_DATA *** /u03/oradata/sls1/sls1_data_02.dbf
6 1024 SLS1_DATA *** /u03/oradata/sls1/sls1_data_03.dbf
7 2040 SLS1_INDEX *** /u04/oradata/sls1/sls1_index_01.dbf
8 1024 SLS1_INDEX *** /u04/oradata/sls1/sls1_index_02.dbf
9 2036 SLS1AUD_ARCH *** /u05/oradata/sls1/sls1aud_arch_01.dbf
10 1024 SLS1AUD_ARCH *** /u05/oradata/sls1/sls1aud_arch_02.dbf
11 128 USERS *** /u02/oradata/sls1/users01.dbf
12 1972 SLS1HIST_ARCH *** /u05/oradata/sls1/sls1hist_arch_01.dbf
13 100 FLOW_1 *** /u05/oradata/sls1/FLOW_1.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 528 TEMP 1024 /u07/oradata/sls1/temp01.dbf

RMAN>
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN>
RMAN>

Recovery Manager complete.
---------------------------------------------------------
Tue Jun 26 15:05:36 EDT 2007
---------------- rman_bkup.ksh completed ----------------

Output log of rman_dr.ksh

2007-06-03T12:15:00.000-07:00

This is the output log of rman_dr.ksh. Please see rman_dr.ksh also to make sense of this log

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 12 16:33:59 2007

RMAN>
RMAN>
connected to target database (not started)

RMAN>
RMAN>
startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/u01/oracle/product/10.2.0/dbs/spfileslsd.
ora'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 2019328 bytes
Variable Size 67108864 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes

RMAN>
RMAN> 2> 3> 4>
executing command: SET DBID

RMAN>
RMAN> 2> 3> 4>
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: tape_1
channel tape_1: sid=37 devtype=SBT_TAPE
channel tape_1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 12-JUL-07

RMAN> 2> 3> 4>
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started

Total System Global Area 402653184 bytes

Fixed Size 2021192 bytes
Variable Size 130025656 bytes
Database Buffers 264241152 bytes
Redo Buffers 6365184 bytes

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: tape_1
channel tape_1: sid=62 devtype=SBT_TAPE
channel tape_1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 12-JUL-07

channel tape_1: looking for autobackup on day: 20070712
channel tape_1: autobackup found: c-3183102432-20070712-02
channel tape_1: control file restore from autobackup complete
output filename=/u01/oradata/slsd/control01.ctl
output filename=/u05/oradata/slsd/control02.ctl
output filename=/u07/oradata/slsd/control03.ctl
Finished restore at 12-JUL-07
released channel: tape_1

RMAN>
RMAN> 2> 3> 4>
database mounted

RMAN>
RMAN> 2> 3> 4> 5> 6> 7>
allocated channel: tape_1
channel tape_1: sid=62 devtype=SBT_TAPE
channel tape_1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 12-JUL-07
Starting implicit crosscheck backup at 12-JUL-07
Crosschecked 11 objects
Finished implicit crosscheck backup at 12-JUL-07

Starting implicit crosscheck copy at 12-JUL-07
Finished implicit crosscheck copy at 12-JUL-07

searching for all files in the recovery area
cataloging files...
no files cataloged

channel tape_1: starting datafile backupset restore
channel tape_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/slsd/system01.dbf
channel tape_1: reading from backup piece 08imku8o_1_2
channel tape_1: restored backup piece 1
piece handle=08imku8o_1_2 tag=TAG20070712T104647
channel tape_1: restore complete, elapsed time: 00:01:26
channel tape_1: starting datafile backupset restore
channel tape_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u03/oradata/slsd/sa_data_01.dbf
channel tape_1: reading from backup piece 09imku8o_1_2
channel tape_1: restored backup piece 1
piece handle=09imku8o_1_2 tag=TAG20070712T104647
channel tape_1: restore complete, elapsed time: 00:01:25
channel tape_1: starting datafile backupset restore
channel tape_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u04/oradata/slsd/sa_index_01.dbf
channel tape_1: reading from backup piece 0aimku8o_1_2
channel tape_1: restored backup piece 1
piece handle=0aimku8o_1_2 tag=TAG20070712T104647
channel tape_1: restore complete, elapsed time: 00:01:15
channel tape_1: starting datafile backupset restore
channel tape_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u02/oradata/slsd/users01.dbf
channel tape_1: reading from backup piece 0dimkuem_1_2
channel tape_1: restored backup piece 1
piece handle=0dimkuem_1_2 tag=TAG20070712T104647
channel tape_1: restore complete, elapsed time: 00:00:07
channel tape_1: starting datafile backupset restore
channel tape_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u02/oradata/slsd/sysaux01.dbf
channel tape_1: reading from backup piece 0cimkuej_1_2
channel tape_1: restored backup piece 1
piece handle=0cimkuej_1_2 tag=TAG20070712T104647
channel tape_1: restore complete, elapsed time: 00:00:35
channel tape_1: starting datafile backupset restore
channel tape_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u05/oradata/slsd/undotbs01.dbf
channel tape_1: reading from backup piece 0bimku8o_1_2
channel tape_1: restored backup piece 1
piece handle=0bimku8o_1_2 tag=TAG20070712T104647
channel tape_1: restore complete, elapsed time: 00:00:45
Finished restore at 12-JUL-07

Starting recover at 12-JUL-07

starting media recovery

archive log thread 1 sequence 44 is already on disk as file /u04/oradata/slsd/re
do2a.rdo
archive log thread 1 sequence 45 is already on disk as file /u02/oradata/slsd/re
do3b.rdo
archive log thread 1 sequence 46 is already on disk as file /u02/oradata/slsd/re
do1a.rdo
channel tape_1: starting archive log restore to default destination
channel tape_1: restoring archive log
archive log thread=1 sequence=42
channel tape_1: reading from backup piece 0eimkul0_1_2
channel tape_1: restored backup piece 1
piece handle=0eimkul0_1_2 tag=TAG20070712T105320
channel tape_1: restore complete, elapsed time: 00:00:04
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_12/o1_mf_1
_42_39f4gskr_.arc thread=1 sequence=42
channel default: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_12/o1_mf_1
_42_39f4gskr_.arc recid=12 stamp=627756057
channel tape_1: starting archive log restore to default destination
channel tape_1: restoring archive log
archive log thread=1 sequence=43
channel tape_1: reading from backup piece 0gimldma_1_2
channel tape_1: restored backup piece 1
piece handle=0gimldma_1_2 tag=TAG20070712T151000
channel tape_1: restore complete, elapsed time: 00:00:02
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_12/o1_mf_1
_43_39f4gzy
r_.arc thread=1 sequence=43
channel default: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/slsd/archivelog/2007_07_12/o1_mf_1
_43_39f4gzyr_.arc recid=13 stamp=627756064
archive log filename=/u04/oradata/slsd/redo2a.rdo thread=1 sequence=44
archive log filename=/u02/oradata/slsd/redo3b.rdo thread=1 sequence=45
archive log filename=/u02/oradata/slsd/redo1a.rdo thread=1 sequence=46
media recovery complete, elapsed time: 00:00:10
Finished recover at 12-JUL-07
released channel: tape_1

RMAN> 2> 3> 4> 5>

Recovery Manager complete.

Output log of rman_arch_bkup.ksh

2007-06-03T12:13:00.000-07:00

This shows the output log of rman_arch_bkup.ksh

---------------------------------------------------------
Tue Jun 26 14:25:25 EDT 2007
---------------- rman_arch_bkup.ksh started ------------------

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 26 14:25:25 2007

RMAN>
connected to target database: sls1 (DBID=1522268517)

RMAN>
RMAN> 2> 3> 4> 5> 6> 7>
Starting backup at 26-JUN-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=134 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=133 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=416 stamp=626279130
channel ORA_DISK_1: starting piece 1 at 26-JUN-07
channel ORA_DISK_1: finished piece 1 at 26-JUN-07
piece handle=/u06/flash_recovery_area/sls1/backupset/2007_06_26/o1_mf_annnn_TAG20070626T142533_382pjytc_.bkp tag=TAG20070626T142533 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/flash_recovery_area/sls1/archivelog/2007_06_26/o1_mf_1_15_382pjsyy_.arc recid=416 stamp=626279130
Finished backup at 26-JUN-07

Starting Control File and SPFILE Autobackup at 26-JUN-07
piece handle=/u01/oracle/product/10.2.0/dbs/controlfile_c-1522268517-20070626-02 comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUN-07

RMAN>
RMAN>
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1024 SYSTEM *** /u02/oradata/sls1/system01.dbf
2 512 UNDOTBS1 *** /u05/oradata/sls1/undotbs01.dbf
3 512 SYSAUX *** /u02/oradata/sls1/sysaux01.dbf
4 2040 SLS1_DATA *** /u03/oradata/sls1/sls1_data_01.dbf
5 2040 SLS1_DATA *** /u03/oradata/sls1/sls1_data_02.dbf
6 1024 SLS1_DATA *** /u03/oradata/sls1/sls1_data_03.dbf
7 2040 SLS1_INDEX *** /u04/oradata/sls1/sls1_index_01.dbf
8 1024 SLS1_INDEX *** /u04/oradata/sls1/sls1_index_02.dbf
9 2036 SLS1AUD_ARCH *** /u05/oradata/sls1/sls1aud_arch_01.dbf
10 1024 SLS1AUD_ARCH *** /u05/oradata/sls1/sls1aud_arch_02.dbf
11 128 USERS *** /u02/oradata/sls1/users01.dbf
12 1972 SLS1HIST_ARCH *** /u05/oradata/sls1/sls1hist_arch_01.dbf
13 100 FLOW_1 *** /u05/oradata/sls1/FLOW_1.dbf

RMAN>
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN>
RMAN>

Recovery Manager complete.
---------------------------------------------------------
Tue Jun 26 14:25:48 EDT 2007
---------------- rman_arch_bkup.ksh completed ----------------

Output log of rman_tape_bkup.ksh

2007-06-03T12:10:00.000-07:00

This shows the output log of rman_tape_bkup.ksh

---------------------------------------------------------
Fri Jun 15 14:56:06 EDT 2007
---------------- rman_tape_bkup.ksh started ------------------

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 15 14:56:07 2007

RMAN>
connected to target database: sls1 (DBID=1522268517)

RMAN>
RMAN> 2> 3> 4> 5> 6> 7>
Starting backup at 15-JUN-07
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=146 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=141 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=144 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=139 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: Tivoli Data Protection for Oracle: version 5.2.0.0
skipping backupset key 161; already backed up 1 time(s)
skipping backupset key 162; already backed up 1 time(s)
skipping backupset key 163; already backed up 1 time(s)
skipping backupset key 164; already backed up 1 time(s)
skipping backupset key 165; already backed up 1 time(s)
skipping backupset key 166; already backed up 1 time(s)
skipping backupset key 167; already backed up 1 time(s)
skipping backupset key 168; already backed up 1 time(s)
skipping backupset key 169; already backed up 1 time(s)
skipping backupset key 170; already backed up 1 time(s)
skipping backupset key 171; already backed up 1 time(s)
skipping backupset key 172; already backed up 1 time(s)
skipping backupset key 173; already backed up 1 time(s)
skipping backupset key 174; already backed up 1 time(s)
input backupset count=183 stamp=625325470 creation_time=15-JUN-07
channel ORA_SBT_TAPE_2: starting piece 1 at 15-JUN-07
channel ORA_SBT_TAPE_2: backup piece /u01/oracle/product/10.2.0/dbs/controlfile_c-1522268517-20070615-00
input backupset count=180 stamp=625321637 creation_time=15-JUN-07
channel ORA_SBT_TAPE_1: starting piece 1 at 15-JUN-07
channel ORA_SBT_TAPE_1: backup piece /u06/flash_recovery_area/sls1/backupset/2007_06_15/o1_mf_annnn_TAG20070615T122717_375hh73n_.bkp
piece handle=5kikb9l5_1_2 comment=API Version 2.0,MMS Version 5.2.0.0
channel ORA_SBT_TAPE_1: finished piece 1 at 15-JUN-07
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
piece handle=c-1522268517-20070615-00 comment=API Version 2.0,MMS Version 5.2.0.0
channel ORA_SBT_TAPE_2: finished piece 1 at 15-JUN-07
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:03
Finished backup at 15-JUN-07

Starting Control File and SPFILE Autobackup at 15-JUN-07
piece handle=c-1522268517-20070615-01 comment=API Version 2.0,MMS Version 5.2.0.0
Finished Control File and SPFILE Autobackup at 15-JUN-07

RMAN>
RMAN>
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1024 SYSTEM *** /u02/oradata/sls1/system01.dbf
2 512 UNDOTBS1 *** /u05/oradata/sls1/undotbs01.dbf
3 512 SYSAUX *** /u02/oradata/sls1/sysaux01.dbf
4 2040 SLS1_DATA *** /u03/oradata/sls1/sls1_data_01.dbf
5 2040 SLS1_DATA *** /u03/oradata/sls1/sls1_data_02.dbf
6 1024 SLS1_DATA *** /u03/oradata/sls1/sls1_data_03.dbf
7 2040 SLS1_INDEX *** /u04/oradata/sls1/sls1_index_01.dbf
8 1024 SLS1_INDEX *** /u04/oradata/sls1/sls1_index_02.dbf
9 2036 SLS1AUD_ARCH *** /u05/oradata/sls1/sls1aud_arch_01.dbf
10 1024 SLS1AUD_ARCH *** /u05/oradata/sls1/sls1aud_arch_02.dbf
11 128 USERS *** /u02/oradata/sls1/users01.dbf
12 1972 SLS1HIST_ARCH *** /u05/oradata/sls1/sls1hist_arch_01.dbf
13 100 FLOW_1 *** /u05/oradata/sls1/FLOW_1.dbf

RMAN>
RMAN>

Recovery Manager complete.
---------------------------------------------------------
Fri Jun 15 14:56:31 EDT 2007
---------------- rman_tape_bkup.ksh completed ----------------

&nbsp
;

Output log of rman_config.ksh

2007-06-03T12:04:00.000-07:00

This shows the output log of rman_config.ksh

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jun 11 14:58:03 2007

RMAN>
connected to target database: sls1 (DBID=1522268517)

RMAN>
RMAN>
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored

RMAN>
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> 2>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'controlfile_%F';
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> 2> 3>
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' FORMAT '%u_%p_%c';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' FORMAT '%u_%p_%c';
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>

Recovery Manager complete.

Sample sqlnet.ora file

2007-06-03T12:02:00.000-07:00

This is a sample sqlnet.ora file

#
# Sample sqlnet.ora - Ramakrishna Nemani
#
NAMES.DEFAULT_DOMAIN=world
NAMES.DEFAULT_ZONE=world
automatic_ipc=off

Sample tnsnames.ora

2007-06-03T11:52:00.000-07:00

This is a sample tnsnames.ora file

#
# Sample tnsnames.ora - Ramakrishna Nemani
#
#
# The following is the general syntax for any entry in
# a tnsnames.ora file. There could be several such entries
# tailored to the user's needs.
#
sls1.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = us6atlp1)(PORT = 1521))
(CONNECT_DATA =
(SID = cmad)
(ORACLE_HOME = /apps/oracle/product/10.2.0)
(service_names = sls1)
)
)
sls2.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = us6atlp1)(PORT = 1521))
(CONNECT_DATA =
(SID = stgt)
(ORACLE_HOME = /apps/oracle/product/10.2.0)
(service_names = sls2)
)
)

Sample Listener.ora

2007-06-03T11:49:00.000-07:00

This is a sample listener.ora file

#
# Sample listener.ora - Ramakrishna Nemani
#
LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = us6atlp1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = sls1))
(ADDRESS = (PROTOCOL = IPC)(KEY = sls2))
(ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sls1.world)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(SID_NAME = sls1)
)
(SID_DESC =
(GLOBAL_DBNAME = sls2.world)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(SID_NAME = sls2)
)
)

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Sample .profile file

2007-05-08T16:01:00.000-07:00

A sample .profile file

if [ "$DISPLAY" = "" ]; then
if [ "`tty`" != "/dev/console" -o "$TERM" != "console" ]; then
REMOTEHOSTNAME="`who am i cut -d'(' -f2,2 cut -d')' -f1,1`"
DISPLAY="$REMOTEHOSTNAME:0.0"
export DISPLAY
fi
fi

umask 022
export ORACLE_BASE=/apps/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export TNS_ADMIN=$ORACLE_HOME/network/admin
export AGENT_HOME=$ORACLE_BASE/product/agent10g
PATH=$PATH:$ORACLE_HOME/bin:/home/oracle/bin
export ORACLE_SID=slsp
PS1='$ORACLE_SID:'"`uname -n`:"'$PWD> '
alias slsp='export ORACLE_SID=slsp'
alias dird='ls -lrt grep -i "^d"'
alias cdslsp='cd /apps/oracle/admin/slsp/scripts'
alias rmandir='cd /apps/oracle/admin/rman'
export TMOUT=0
export LIBPATH=$ORACLE_HOME/lib:/usr/lib
export EXCLUDE=/usr/tivoli/tsm/client/ba/bin/inclexcl.oracle
lsattr -El sys0 grep -i REALMEM
AIXTHREAD_SCOPE=S; export AIXTHREAD_SCOPE
NUM_SPAREVP=1; export NUM_SPAREVP

Export to UNIX pipe and Compress

2007-05-08T15:56:00.000-07:00

This script exports to a UNIX pipe which is then fed to gzip to create a compressed export dump file.

#!/bin/ksh
. $HOME/.profile
##
## script name : exp_pipe.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script exports to a UNIX pipe which is then fed to gzip to create
## a compressed export dump file
## This is useful when space is at a premium or when you are dealing with
## an export dump file that can grow larger than 2GB and you are working on
## a file system that can not handle files larger than 2GB in size
##
## Note :
## There are a bunch of modifiable parameters in this script that you
## should modify to reflect your environment.
## I have tested this script on UNIX/LINUX
## Use it at your own risk.
##
## This is an example or sample script
##

##
## Begining of modifiable parameters
##

export ORACLE_SID=slsd
EXP_DIR="./"

DUMPFILE=${EXP_DIR}slsd.pipe
GZIPDUMPFILE=${EXP_DIR}slsd_exp.dmp.gz

PARFILE=${EXP_DIR}slsd_exp.par

LOGFILE=${EXP_DIR}exp_slsd.log
ERRFILE=${EXP_DIR}full_exp.out

##
## End of modifiable parameters
##

##
## Create a pipe
##

rm -f $DUMPFILE 2>/dev/null
mknod $DUMPFILE p

##
## Compress using gzip the cat output from pipe. This is done in the background
##

cat $DUMPFILE gzip -c > $GZIPDUMPFILE &

exp parfile=$PARFILE log=$LOGFILE file=$DUMPFILE 2> $ERRFILE

export RETURN_CODE=$?
echo RETURN_CODE: $RETURN_CODE

exit $RETURN_CODE

Import through UNIX pipe

2007-05-08T14:13:00.000-07:00

This script imports through an UNIX pipe.This is useful when space is at a premium or when you are dealing with an export dump file that is larger than 2GB and you are working on a file system that can not handle files larger than 2GB in size.

#!/bin/ksh
. ~/.profile

##
## script name : imp_pipe.ksh
## Author : Ramakrishna Nemani
##
## Purpose :
## This script imports from an oracle export dump file through a UNIX pipe.
## This is useful when space is at a premium or when you are dealing with an export dump file
## that is larger than 2GB and you are working on a file system that can not handle files
## larger than 2GB in size
##
##
##
## Note :
## There are a bunch of modifiable parameters in this script that you should modify to
## reflect your environment.
## I have tested this script on UNIX/LINUX.
## Use it at your own risk.
##
## This is an example or sample script
##

## ----------------------------------------------------------------------------
## Begining of modifiable parameters
## ----------------------------------------------------------------------------

ORACLE_SID=slsd; export ORACLE_SID

##
## name the directory that has the import dump file
##

IMP_DIR="/u01/import/"

##
## name a directory where you want to create the export logs, errors and pipes
##

LOG_DIR="/u01/log/"

##
## name your export dump file here
##

EXPDUMPFILE=${IMP_DIR}slsp_exp.dmp.gz

##
## name your pipe - you can give any name, I prefer to give ORACLE_SID.pipe
##

PIPEFILE=${LOG_DIR}slsd.pipe

##
## name your import parameters file (parfile) here
##

PARFILE=${LOG_DIR}imp_slsd.par

##
## name your Error log files here
##

ERRFILE=${LOG_DIR}imp_slsd.out
LOGFILE=${LOG_DIR}imp_slsd.log

## ----------------------------------------------------------------------------
## End of modifiable parameters
## ----------------------------------------------------------------------------

##
## Create a pipe
##

rm $PIPEFILE
mknod $PIPEFILE p

##
## unzip and read the compressed dump file to the screen
## and redirect the screen output to the pipe . This process
## is run in the background
##

gunzip -c $EXPDUMPFILE > $PIPEFILE &

##
## Import from pipe
##

imp parfile=$PARFILE log=$LOGFILE file=$PIPEFILE 2> $ERRFILE

exit