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.1.0.2

FULL DATABASE EXPORT STEPS:

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

SQL> create directory EXPDIR as '/export/home/oracle/EXPDIR';

Directory created.

2. Run expdp with full=y

expdp dumpfile=FULLDB_SRCDB_%U.dmp logfile=FULLDB_SRCDB.log parallel=8 full=Y DIRECTORY=EXPDIR

expdp dumpfile=FULLDB_SRCDB_%U.dmp logfile=FULLDB_SRCDB.log parallel=8 full=Y DIRECTORY=EXPDIR

Export: Release 12.1.0.2.0 - Production on Mon Nov 5 13:50:23 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA dumpfile=FULLDB_SRCDB_%U.dmp logfile=FULLDB_SRCDB.log parallel=8 full=Y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.07 MB
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
. . exported "APP_LOGGER"."TABLE2"                       1.985 MB   19996 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows

.
.
.
.
.
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /export/home/oracle/product/12c/admin/SRCDB/dpdump/FULLDB_SRCDB_01.dmp
  /export/home/oracle/product/12c/admin/SRCDB/dpdump/FULLDB_SRCDB_02.dmp
  /export/home/oracle/product/12c/admin/SRCDB/dpdump/FULLDB_SRCDB_03.dmp
  /export/home/oracle/product/12c/admin/SRCDB/dpdump/FULLDB_SRCDB_04.dmp
  /export/home/oracle/product/12c/admin/SRCDB/dpdump/FULLDB_SRCDB_05.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Mon Nov 5 13:52:22 2018 elapsed 0 00:01:54

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. In this article, we will create a blank database also.

1. Install oracle database binaries. ( on TRGDB server)

Install oracle database binary

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 EXPDIR as '/export/home/oracle/EXPDIR';

Directory created.

5. Create missing tablespaces in target db.

-- Tablespaces present on SRCDB
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
APP_TS -- >> 

— Tablespaces present on TRGDB

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

If we compare, the tablespace APP_TS is not present in TRGDB.So lets create that.

SQL> create tablespace APP_TS datafile '/oracle/app/oracle/oradata/TRGDB/TRGDB/app_ts01.dbf' size 5G;

Tablespace created.


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.

impdp dumpfile=FULLDB_SRCDB_%U.dmp logfile=imp_FULLDB_SRCDB.log directory=EXPDIR PARALLEL=8 full=y

Import: Release 12.1.0.2.0 - Production on Mon Nov 5 13:56:16 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=FULLDB_SRCDB_%U.dmp logfile=imp_FULLDB_SRCDB.log directory=EXPDIR PARALLEL=8 full=y
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"APP_TS" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
ORA-31684: Object type DIRECTORY:"EXPDIR" already exists
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP"                 5.796 KB      20 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$"                  0 KB       0 rows
. . imported "SYS"."AMGT$DP$AUD$"                            0 KB       0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$"                      0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$"                        0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.531 KB      14 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$"                    0 KB       0 rows
. . imported "SYS"."DP$TSDP_CONDITION$"                      0 KB       0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$"                 0 KB       0 rows
. . imported "SYS"."DP$TSDP_PARAMETER$"                  5.953 KB       1 rows
. . imported "SYS"."DP$TSDP_POLICY$"                     5.921 KB       1 rows
. . imported "SYS"."DP$TSDP_PROTECTION$"                     0 KB       0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$"                 0 KB       0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$"                 0 KB       0 rows
. . imported "SYS"."DP$TSDP_SOURCE$"                         0 KB       0 rows
. . imported "SYS"."DP$TSDP_SUBPOL$"                     6.328 KB       1 rows
. . imported "SYSTEM"."REDO_DB_TMP"                      25.59 KB       1 rows
. . imported "SYSTEM"."REDO_LOG_TMP"                         0 KB       0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$"          0 KB       0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$"                       0 KB       0 rows
. . imported "WMSYS"."E$ENV_VARS$"                       6.015 KB       3 rows
. . imported "WMSYS"."E$EVENTS_INFO$"                    5.812 KB      12 rows
. . imported "WMSYS"."E$HINT_TABLE$"                     9.453 KB      75 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$"                    0 KB       0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$"          0 KB       0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$"         0 KB       0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$"               0 KB       0 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$"                  6.375 KB       1 rows
. . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE$"           0 KB       0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$"           0 KB       0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$"                  0 KB       0 rows
. . imported "WMSYS"."E$RIC_TABLE$"                          0 KB       0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$"                 0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$"              0 KB       0 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$"        5.984 KB       1 rows
. . imported "WMSYS"."E$VERSION_TABLE$"                      0 KB       0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$"                    0 KB       0 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$"               12.10 KB       1 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$"           7.054 KB      10 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$"         0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.953 KB       2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA"                   0 KB       0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION"           0 KB       0 rows
. . imported "SYS"."NACL$_ACE_IMP"                       9.929 KB       1 rows
. . imported "SYS"."NACL$_HOST_IMP"                      6.914 KB       1 rows
. . imported "SYS"."NACL$_WALLET_IMP"                        0 KB       0 rows
. . imported "WMSYS"."E$EXP_MAP"                         7.718 KB       3 rows

. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP"           8.671 KB       4 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP"       10.21 KB      22 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "APP_LOGGER"."TABLE4"                       1.986 MB   20010 rows
. . imported "APP_DB"."DB_TAB1"                          1.986 MB   20011 rows
. . imported "APP_DB"."DB_TAB2"                          1.987 MB   20012 rows
. . imported "APP_LOGGER"."TABLE1"                       1.985 MB   19995 rows
. . imported "APP_LOGGER"."TABLE2"                       1.985 MB   19996 rows
. . imported "APP_LOGGER"."TABLE3"                       1.986 MB   20009 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" completed with 5 error(s) at Mon Nov 5 13:57:39 2018 elapsed 0 00:01:21





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
--------------------------------------------------------------------------------
APP_DB
APP_LOGGER

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

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql