Schema Refresh on Same Database

Mar 1, 2011

—

by

In my environment, I have worked on schema refresh frequently in different situations. In production, I have done schema refresh during the database downtime. Schema refresh is mostly used to avoid the database fragmentation.

Schema Refresh steps:

Exported the schema (TESTPIPE1) using exp utility in production server.
Dropped & Recreated TESTPIPE1 the schema in production server.
Imported the TESTPIPE1 schema using import utility in production server.
Compiled the invalid objects.
Ensure the Jobs are submitted.
Gather the statistics for TESTPIPE1 schema.

I have automated the above activity. Refer to the below script.

Script 1

SCRIPT NAME : exppipemail.sh

DESCRIPTION : Script details

STEPS:

Exported the schema using export utility.(also using mknod)
Validate the export log file.
Trigger the mail to user about export status.(Success/failure)
If export dump is valid go to step 5, otherwise script exited.
Check any TESTPIPE1 user connected to the database. (Refer SCRIPT 3 & 4)
If any session connected to TESTPIPE1 schema, Kill the TESTPIPE1 sessions in OS level.
Dropped the TESTPIPE1 user and recreated. (Refer SCRIPT 5 & 6)
Imported the schema using import utility. (Refer SCRIPT 7)
Validate the import log file & trigger the mail to the user about import status.(If any error during the import, send the error to the user through mail)


##############################################################################
# 
# SCHEMA REFRESH FOR TESTPIPE1 USER IN TEST DATABASE
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

#!/bin/sh

. $HOME/.bash_profile
cd /home/oracle/dbatest/raja/mknode/schemarefresh
RUNDATE=`date "+%d%m%y at %H:%M:%S"`
DBHOME=$ORACLE_HOME
PATH=/home/oracle/dbatest/raja/mknode/schemarefresh
mknod export_pipe p
gzip -cNf $ORACLE_SID.dmp.gz &
exp parfile=/home/oracle/dbatest/raja/mknode/schemarefresh/exp_param file=export_pipe log=$PATH/export_$ORACLE_SID.log
cat $PATH/export_$ORACLE_SID.log | grep "EXP-" > $PATH/exp_$ORACLE_SID.err
mail -s "Status of $ORACLE_SID export backup on $RUNDATE " [email protected] < $PATH/exp_$ORACLE_SID.err
rm -f export_pipe p
WORDCOUNT=`wc $PATH/exp_$ORACLE_SID.err | awk '{print $1}'`
if [ $WORDCOUNT -ne 0 ]; then
exit 16;
else
sh /home/oracle/dbatest/raja/mknode/schemarefresh/killuser.sh
sh /home/oracle/dbatest/raja/mknode/schemarefresh/killuser_script.sh
sh /home/oracle/dbatest/raja/mknode/schemarefresh/usercreation.sh
sh /home/oracle/dbatest/raja/mknode/schemarefresh/imppipemail.sh
fi

Script 2

SCRIPT NAME : exp_param

DESCRIPTION : This exp_param file contains export parameter.(SCRIPT 1 call this exp_param file during export)


##############################################################################
# 
# EXPORT PARAMETER PARFILE
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

userid=testpipe1/testpipe1 owner=testpipe1 buffer=10485667 statistics=none object_consistent=y

Script 3

SCRIPT NAME : killuser.sh

DESCRIPTION : This killuser.sh file generate SPID for TESTPIPE1 userconnected to the database .


##############################################################################
# 
# GENERATE SPID for TESTPIPE1 User
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

#!bin/ksh
. $HOME/.bash_profile
sqlplus /nolog << EOF
conn dba/dba123@test as sysdba
set feedback off
set heading off
@/home/oracle/dbatest/raja/mknode/schemarefresh/finduser.sql
Disconnect
Exit

Script 4

SCRIPT NAME : finduser.sql

DESCRIPTION : To find the which sessions connected to the database.


##############################################################################
# 
# To find the which sessions connected to database
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

spool /home/oracle/dbatest/raja/mknode/schemarefresh/killuser_script.sh
select 'kill -9 '|| V$P.SPID || ';' from v$session V$S,v$process V$P
where V$S.PADDR = V$P.ADDR and V$S.SID in (select distinct sid from v$session
where schemaname='TESTPIPE1') order by V$S.PROCESS,V$S.SID;
spool off
exit

Note: This script generates the syntax like this, “kill -9 142345”.

Script 5

SCRIPT NAME : dropandrecreateuser.sql

DESCRIPTION : Drop and recreate user script


##############################################################################
# 
# Drop and recreate user script
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

Drop user testpipe cascade;
create user testpipe
identified by testpipe
default tablespace users
quota unlimited on users;
grant connect,resource,imp_full_database to testpipe;

Script 6

SCRIPT NAME : usercreation.sh

DESCRIPTION : Execute the userrecreation script


##############################################################################
# 
# Execute the recreate user script
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

#!bin/ksh
. $HOME/.bash_profile
sqlplus /nolog <
conn dba/dba123@test
@/home/oracle/dbatest/raja/mknode/schemarefresh/dropandrecreateuser.sql
Disconnect
Exit

Script 7

SCRIPT NAME : imppipemail.sh

DESCRIPTION : Imported the testpipe1 schema


##############################################################################
# 
# SCHEMA REFRESH FOR TESTPIPE1 USER IN TEST DATABASE
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

#!/bin/sh
. $HOME/.bash_profile
cd /home/oracle/dbatest/raja/mknode/schemarefresh/
PATH=/home/oracle/dbates
t/raja/mknode/schemarefresh
mknod import_pipe p
gunzip -c $ORACLE_SID.dmp.gz > import_pipe &
imp parfile=/home/oracle/dbatest/raja/mknode/schemarefresh/imp_param file=import_pipe log=$PATH/import_$ORACLE_SID.log
cat $PATH/import_$ORACLE_SID.log | grep "IMP-" > $PATH/imp_$ORACLE_SID.err
mail -s "Status of $ORACLE_SID import done on $RUNDATE " [email protected] < $PATH/imp_$ORACLE_SID.err
rm -f import_pipe p

Script 8

SCRIPT NAME : imp_param

DESCRIPTION : This imp_param file contains import parameter.(SCRIPT 1 call this imp_param file during export)


##############################################################################
#
# import PARAMETER PARFILE
# CREATED BY RAJABASKAR THANGARAJ 25-AUG-2008
# 
##############################################################################

userid=testpipe/testpipe fromuser=demo touser=testpipe buffer=10485667 statistics=none commit=y

Note:

In this script, I have used the mknod option. No need to use this mknod for this activity. Because schema refresh on same server.

Compression & uncompression activity affect the system performance.

More detais about MKNOD : https://www.primeinspiration.com/programming/database/oracle/84-using-mknod-during-export.html

I hope this article helped you to understand the automated schema refresh. Suggestions are welcome.

Tags:

Edward Ramamoorthy

I work for one of the top 10 tech companies in India. In my spare time, I write for PrimeInspiration.com.

Help Us Grow

If you like this post, please share it with your friends.

You are free to copy and redistribute this article in any medium or format, as long as you keep the links in the article or provide a link back to this page.