--On the source

set lines 200

column object_name format a30

select owner, object_name, object_type, status from dba_objects

where status!='VALID';

 
Full Database Export Import Using Datapump Utility

 
The datapump utility can be used for full database export import. This can be one of the database migration or database cloning method.

 
SOURCE DB -SRCDB

TARGET DB – TRGDB

 
DATABASE VERSION – 12.2


FULL DATABASE EXPORT STEPS:

 
Set you path and ORACLE_SID

sqlplus / as sysdba

set ORACLE_SID=TRAVQC

 

1. create a directory for export. (on SRCDB)

SQL> create or replace directory DATA_PUMP_DIR as 'F:\oracle\admin\travqc\dpdump\';

 

2. Capture tablespace DDL

set pagesize

set long 100000

set long 20000

column ddl format a1000

exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

select   DBMS_METADATA.GET_DDL('TABLESPACE',tablespace_name) ddl from dba_tablespaces

where tablespace_name like 'TRAVE%';

 

  CREATE TABLESPACE "TRAVEL_SMART_DATA" DATAFILE

  'F:\ORACLE\ORADATA\TRAVQC\TRAVEL_SMART_DATA01.DBF' SIZE 52428800

  AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

 

  CREATE TABLESPACE "TRAVEL_SMART_IX" DATAFILE

  'F:\ORACLE\ORADATA\TRAVQC\TRAVEL_SMART_IX01.DBF' SIZE 52428800

  AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

 

3. Run expdp with full=y add parallelism with: parallel=8

 

expdp '/ as sysdba' dumpfile=FULLDB_TRAVQC_%U.dmp logfile=FULLDB_TRAVQC.log full=Y DIRECTORY=DATA_PUMP_DIR

 

Now our full export is done. Next step is to prepare the target database.

 

Before doing the import, make sure a blank database is present.

 

1. Install oracle database binaries. (TRGDB server)

 

Install oracle database binary

 

Drop current database

 

2. Create a database using dbca (TRGDB)

 

cd $ORACLE_HOME/bin

 

dbca

 

Now the TRGDB is ready.

 


4. Create the directory for impdp( on TRGDB)

 

SQL> create or replace directory DATA_PUMP_DIR as 'F:\oracle/admin/travqc/dpdump';

 

Directory created.

 

5. Create missing tablespaces in target db.

 

-- Create application Tablespaces present on SRCDB

  CREATE TABLESPACE "TRAVEL_SMART_DATA" DATAFILE

  'F:\ORACLE\ORADATA\TRAVQC\TRAVEL_SMART_DATA01.DBF' SIZE 52428800

  AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

 

  CREATE TABLESPACE "TRAVEL_SMART_IX" DATAFILE

  'F:\ORACLE\ORADATA\TRAVQC\TRAVEL_SMART_IX01.DBF' SIZE 52428800

  AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

 

 

Now copy the dumps from source DB to target DB (If on a different server)

 

— On srcdb server

 

scp FULLDB_SRCDB*dmp oracle@trgdb-host:/export/home/oracle/EXPDIR/

 

Now dumps are available on target db server.

 

6. Start the import process on TRGDB. Use if required: PARALLEL=8 full=y

 

impdp '/ as sysdba' dumpfile=FULLDB_TRAVQC_%U.dmp logfile=FULLDB_TRAVQC-imp.log full=Y DIRECTORY=DATA_PUMP_DIR

 

 

Import completed successfully. If any error reported in the

 

Check whether non-default schemas/users were migrated or not.

 

SQL> select username from dba_users where ORACLE_MAINTAINED='N';

 

USERNAME

--------------------------------------------------------------------------------

TRAVEL_SMART_BASIC_USER

TRAVEL_SMART_ADVANCED_USER

TRAVEL_SMART_ADMIN

 

Run utlrp.sql to compile invalid objects (ON TRGDB)

 
SQL>@?/rdbms/admin/utlrp.sql