Schema Refresh on Same Database

Edward Ramamoorthy
13 years ago
Categories: Tech

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

---