Full database export & import using datapump in Oracle

Full database export & import using datapump in Oracle

Step.1

Create a directory for export in source side.
create directory EXP_DIR as '/u01/dpdump_prod/EXP_DIR';

Step.2

Get the tablespace DDL metadata in source side.

set heading off;
set echo off;
set lines 1000 pages 40000
set long 999999
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

Step.3

Run expdp command.

expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=INDPROD_$(date +"%Y%m%d")exp.dmp logfile=INDPROD$(date +"%Y%m%d")_exp.log FULL=Y exclude=statistics

Step.4

Install oracle database binaries and create database.

Step.5

Create the directory for impdp in target side.
create or replace directory IMP_DIR as '/u01/dpdump_prod/IMP_DIR';

Step.6

Create the missing tablespaces in target side.

Please refer to spool ddl_tablespace.sql and create the tablespaces.

Step.7

Run impdp command.

impdp \'/ as sysdba\' dumpfile=INDPROD_20220823exp.dmp logfile=-INDPROD$(date +"%Y%m%d")_imp.log directory=DATA_PUMP_DIR full=y

Step.8

Run utlrp.sql to compile invalid objects in target side.
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql