Jephe Wu - http://linuxtechres.blogspot.com
Objective: restore backup some tables, procedures and packages from daily night backup
Environment: RHEL 5 64bit, Oracle 11g 64bit
Steps:
1. daily cronjob backup job
put the following inside your cronjob shell script to backup the schema1 and schema2 excluding table JEPHE1
export PATH=/usr/bin:/usr/sbin:/bin:/sbin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' # So time is shown in logs as well as date
export ORACLE_SID=XE
ORAENV_ASK=NO
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh # or whatever path of file
unset ORAENV_ASK
#===========program starts here
DAY=`date +%w`
for i in schema1 schema2
do
expdp userid=\"/ as sysdba\" directory=cronjob dumpfile=$i.dmpdp.$DAY logfile=$i.logdp.$DAY schemas=$i exclude=statistics parfile=/path/to/excludes.par
sync;sleep 3
rm -f $i.dmpdp.$DAY.gz $i.logdp.$DAY.gz
gzip /path/to/$i.dmpdp.$DAY /path/to/$i.logdp.$DAY
done
# more excludes.par
exclude=TABLE:"in ('JEPHE1')"
2. restore preparation
use sql script to delete those procedures and packages first before actual restoration, for tables, you can use table replace action parameter, no need to delete tables first.
# more drop.sql
drop procedure procedure1;
drop package package1;
# sqlplus schema1 @drop.sql
3. create database directory if necessary
I am going to conigure /home/oracle as database directory name 'oracle and copy those backup files under /home/oracle.
create [ or replace ] directory oracle as '/home/oracle’
grant read,write on directory oracle to system
select * from dba_directories
4. actual restoration process (from schema1 objects backup to schema2 schema)
stop listener - lsnrctl stop
impdp system parfile=schema1.par logfile=schema1.logdp remap_schema=schema1:schema2 remap_tablespace=schema1:schema2 transform=oid:n
# more schema1.par
directory=oracle
dumpfile=schema1.dmpdp
include=PROCEDURE:"in ('PROC1')"
include=PACKAGE:"in ('PAC1')"
include=TABLE:"in ('TABLE1')"
Table_exists_action=replace
Note:
a. if you need to restore to the objects from the same schema. (from schema1 backup to schema1 schema)
impdp system parfile=schema1.par logfile=schema1.logdp schemas=schema1
or
5. compile all schema objects then check invalid objects
sqlplus / as sysdba
exec dbms_utility.compile_schema('SCHEMA1');
For how to check invalid objects, refer to another article at http://linuxtechres.blogspot.com/2010/06/how-to-do-deployment-for-oracle.html
6. start listener and register services
lsnrctl start
sqlplus / as sysdba
alter system register;
exit
7. Appendix: how to restore a package from dumpfile and generate sql file without actual restoration.
impdp directory=cronjob dumpfile=jephe.dmpdp.6 schemas=JEPHE include=PACKAGE:\"=\'NAMEOFOBJECT\'\" sqlfile=NAMEOFOBJECT.sql
Posts
