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
