Schema Refresh on Same Database

In my environment I have worked on schema refresh frequently in different situation. In production I have done schema refresh during the database downtime.Mostly schema refresh is 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 the below script.

Script 1

******************************************************************************************

SCRIPT NAME : exppipemail.sh

DESCRIPTION : Script details

STEPS:

  1. Exported the schema using export utility.(also using mknod)
  2. Validate the export log file.
  3. Trigger the mail to user about export status.(Success/failure)
  4. If export dump is valid go to step 5, otherwise script exited.
  5. Check any TESTPIPE1 user connected to the database. ( Refer SCRIPT 3 & 4)
  6. If any session connected to TESTPIPE1 schema,Kill the TESTPIPE1 sessions in OS level.
  7. Dropped the TESTPIPE1 user and recreated. (Refer SCRIPT 5 & 6)
  8. Imported the schema using import utility. (Refer SCRIPT 7)
  9. Validate the import log file & trigger the mail to user about import status.(If any error during the import,send the error to 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 " rajabaskar.thangaraj@abcde.com < $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 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 generate 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/dbatest/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 " rajabaskar.thangaraj@abcde.com < $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 :http://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.


Discuss Here


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 articles or provide a link back to this page.

Subscribe to our mailing list