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
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
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
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
No comments:
Post a Comment