How to restore objects for Oracle database

Jephe Wu -

Objective: restore backup some tables, procedures and packages from daily night backup
Environment: RHEL 5 64bit, Oracle 11g 64bit


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

. /u01/app/oracle/product/11.2.0/xe/bin/  # or whatever path of file 

#===========program starts here

DAY=`date +%w`
for i in schema1 schema2
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

# 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
include=PROCEDURE:"in ('PROC1')"
include=PACKAGE:"in ('PAC1')"
include=TABLE:"in ('TABLE1')"

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

impdp system directory=backup schemas=jephe dumpfile=jephe.dmpdp.5 logfile=jephe.logdp.5 include=TABLE:\"=\'CSTB_BACKUP_REQUEST_DET\'\" table_exists_action=replace
impdp system directory=cronjob schemas=jephe dumpfile=jephe.dmpdp.5 logfile=jephe.logdp.5 include=TABLE  table_exists_action=replace

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

6. start listener and register services
lsnrctl start
sqlplus / as sysdba
alter system register;

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