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:
- 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 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 ” [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 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/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.
