Rebuild database from data pump export

Problem

A small, noarchivelog test database was lost.  Data pump exports of it exist.  To recover it, I re-built the database using dbca and then imported the data pump export.

 

Solution

To recreate ORADB using data pump exports, it will be necessary to do the following

  • If using a new server, go through all the requisites for installing a new oracle home.  See the Oracle documentation.
  • Install a new Oracle Home
  • Create a new database.
  • Move all the export files (.dmp) (or rather, mount the file system with the .dmp files to the new host, with read/write permissions.)
  • Import the .dmp file:
Run the script below to import the export.

#CREATE or REPLACE DIRECTORY EXPORT_DIR as ”;
#GRANT READ, WRITE ON DIRECTORY EXPORT_DIR to SYSTEM;
$ORACLE_HOME/bin/sqlplus -s ‘/ as sysdba’ <<EOF
set pages 0 echo off feed off
CREATE or REPLACE DIRECTORY EXPORT_DIR as ”;
GRANT READ, WRITE ON DIRECTORY EXPORT_DIR to SYSTEM;
quit;
EOF

impdp system/*** directory=export_dir dumpfile=expdp_oradb_full_prd1_%U.dmp logfile=imp4oradb_full.log full=y exclude=schema:\” IN \(\’SYS\’, \’SYSTEM\’, \’SCOTT\’, \’ADAMS\’, \’JONES\’, \’CLARK\’, \’BLAKE\’, \’HR\’, \’OE\’, \’SH\’, \’PM\’, \’DEMO\’, \’ANONYMOUS\’, \’CTXSYS\’, \’DBSNMP\’, \’DIP\’, \’DMSYS\’, \’DSSYS\’, \’EXFSYS\’, \’LBACSYS\’, \’MDSYS\’, \’ORACLE_OCM\’, \’ORDPLUGINS\’, \’ORDSYS\’, \’PERFSTAT\’, \’TRACESVR\’, \’TMSYS\’, \’XDB\’, \’APEX_030200\’, \’APEX_PUBLIC_USER\’, \’APPQOSSYS\’, \’FLOWS_FILES\’, \’MDDATA\’, \’MGMT_VIEW\’, \’OLAPSYS\’, \’ORDDATA\’, \’OUTLN\’, \’OWBSYS\’, \’OWBSYS_AUDIT\’, \’SI_INFORMTN_SCHEMA\’, \’SYSMAN\’, \’WMSYS\’, \’SPATIAL_CSW_ADMIN_USR\’, \’SPATIAL_WFS_ADMIN_USR\’, \’XS$NULL\’, \’BI\’, \’IX\’ \) \”

In this import, I’m excluding the default Oracle schemas because I only want to import my application schemas.
As a reference, here is the script that created the export:
############################################################
#
#  ORADB export
#
############################################################
# ORADB@server1
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=ORADB
#CREATE or REPLACE DIRECTORY dpump_dir1 as ‘/exports;
#GRANT READ, WRITE ON DIRECTORY dpump_dir1 to SYSTEM;
$ORACLE_HOME/bin/sqlplus -s ‘/ as sysdba’ <<EOF
set pages 0 echo off feed off
CREATE or REPLACE DIRECTORY dpump_dir1 as ‘/exports’;
GRANT READ, WRITE ON DIRECTORY dpump_dir1 to SYSTEM;
quit;
EOF

#Grab the SCN
dbscn=`
$ORACLE_HOME/bin/sqlplus -s ‘/ as sysdba’ <<!
set pages 0 echo off feed off
select to_char(current_scn) from v\\$database;
quit;
!
`

# Get the date YearMonthDay (ex: 20140925)
currentDate=`date +%Y%m%e`

# Export all the application schemas, excluding the default Oracle schemas.

expdp \’/ as sysdba\’ directory=dpump_dir1 dumpfile=ORADB`echo $currentDate`_%U.dmp logfile=ORADB`echo $currentDate`.log full=y parallel=2 filesize=2G flashback_scn=`echo $dbscn` exclude=schema:\” IN \(\’SYS\’, \’SYSTEM\’, \’SCOTT\’, \’ADAMS\’, \’JONES\’, \’CLARK\’, \’BLAKE\’, \’HR\’, \’OE\’, \’SH\’, \’PM\’, \’DEMO\’, \’ANONYMOUS\’, \’CTXSYS\’, \’DBSNMP\’, \’DIP\’, \’DMSYS\’, \’DSSYS\’, \’EXFSYS\’, \’LBACSYS\’, \’MDSYS\’, \’ORACLE_OCM\’, \’ORDPLUGINS\’, \’ORDSYS\’, \’PERFSTAT\’, \’TRACESVR\’, \’TMSYS\’, \’XDB\’, \’APEX_030200\’, \’APEX_PUBLIC_USER\’, \’APPQOSSYS\’, \’FLOWS_FILES\’, \’MDDATA\’, \’MGMT_VIEW\’, \’OLAPSYS\’, \’ORDDATA\’, \’OUTLN\’, \’OWBSYS\’, \’OWBSYS_AUDIT\’, \’SI_INFORMTN_SCHEMA\’, \’SYSMAN\’, \’WMSYS\’, \’SPATIAL_CSW_ADMIN_USR\’, \’SPATIAL_WFS_ADMIN_USR\’, \’XS$NULL\’, \’BI\’, \’IX\’
\) \”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s