Transporting FULL Database from 11g Database to 12c Database

Upgrading to a New Release of Oracle Database

This article covers the Transporting Data: Scenarios of “full transportable export/import feature”  https://community.oracle.com/docs/DOC-991606

Introduction:
This is the new feature of Oracle12c R1

You can use full transportable export/import to upgrade a database from an Oracle Database 11g Release 2 (11.2.0.3) or later to Oracle Database 12c.
To do so, install Oracle Database 12c and create an empty database. Next, use full transportable export/import to transport the Oracle Database 11g Release 2 (11.2.0.3) database into the Oracle Database 12c database.

Transporting data is much faster than performing either an export/import or unload/load of the same data. It is faster because, for user-defined tablespaces, the data files containing all of the actual data are copied to the target location, and you use Data Pump to transfer only the metadata of the database objects to the new database.

The Procedure:
Scenario having Source Database on Standalone EE 11g DBv11.2.0.3.0 – RHEL 4 32bit and Target Database on Standalone EE 12c DBv12.1.0.2.0 – RHEL 6.5 64bit

Step-1# It is assumed the following datafiles and tablespaces exist in source database:

set pagesize 0
col USERNAME format a25
col DEFAULT_TABLESPACE format a16
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS;

USERNAME DEFAULT_TABLESPACE
————————————————————————
APP1 APP1
APP2 APP2
APP3 USERS
APP4 USERS
APP5 APP5
APP6 USERS
APP7 USERS
APP8 USERS

Step-2# Determine if Platforms are Supported and Determine Endianness
This task is only necessary if you are transporting the tablespace set to a platform different from the source platform.

If you are transporting the tablespace set to a platform different from the source platform,
then determine if cross-platform tablespace transport is supported for both the source and
destination platforms, and determine the endianness of each platform. If both platforms have
the same endianness, no conversion is necessary. Otherwise you must do a conversion of the
tablespace set either at the source or destination database.

#Source
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 6 16:05:44 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
—————————————–
Linux IA (32-bit) Little

#Destination
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 6 16:38:38 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

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

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; 3

PLATFORM_NAME ENDIAN_FORMAT
——————————————
Linux x86 64-bit Little

Step-3# The following statement can be used to determine whether tablespaces are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘APP1,APP2,APP5,USERS’, TRUE);

PL/SQL procedure successfully completed.

#After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

Step-4# Make all tablespaces in the set read-only.
set pagesize 0
SELECT ‘ALTER TABLESPACE ‘||TABLESPACE_NAME ||’ READ ONLY;’ FROM USER_TABLESPACES;

ALTER TABLESPACE USERS READ ONLY;
ALTER TABLESPACE APP1 READ ONLY;
ALTER TABLESPACE APP2 READ ONLY;
ALTER TABLESPACE APP5 READ ONLY;

SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;
SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;

TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
SYSAUX ONLINE
SYS_UNDOTS ONLINE
TEMP ONLINE
USERS READ ONLY
APP1 READ ONLY
APP2 READ ONLY
APP5 READ ONLY

Stpe-5#Invoke the Data Pump export utility as a user with DATAPUMP_EXP_FULL_DATABASE role and specify the full transportable export/import options.

expdp system full=y dumpfile=expdat_full_tts.dmp directory=data_pump_dir
transportable=always version=12 logfile=expdat_full_tts.log

You must always specify TRANSPORTABLE=ALWAYS, which determines whether the transportable option is used.

This example specifies the following Data Pump parameters:

The FULL parameter specifies that the entire database is being exported.

The DUMPFILE parameter specifies the name of the structural information export dump file to be created, expdat.dmp.

The DIRECTORY parameter specifies the directory object that points to the operating system or Oracle Automatic Storage Management location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to the user running the Export utility. See Oracle Database SQL Language Reference for information on the CREATE DIRECTORY command.

In a non-CDB, the directory object DATA_PUMP_DIR is created automatically. Read and write access to this directory is automatically granted to the DBA role, and thus to users SYS and SYSTEM.

However, the directory object DATA_PUMP_DIR is not created automatically in a PDB. Therefore, when importing into a PDB, create a directory object in the PDB and specify the directory object when you run Data Pump.

The LOGFILE parameter specifies the file name of the log file to be written by the export utility. In this example, the log file is written to the same directory as the dump file, but it can be written to a different location.

-bash-3.2$ expdp system full=y dumpfile=expdat_full_tts.dmp directory=data_pump_dir transportable=always version=12 logfile=expdat_full_tts.log

Export: Release 11.2.0.3.0 – Production on Wed Jan 6 16:03:36 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_FULL_05″: system/******** full=y dumpfile=expdat_full_tts.dmp directory=data_pump_dir transportable=always version=12 logfile=expdat_full_tts.log
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
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: 937.5 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
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/ROLE
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/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
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_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
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/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported “SYS”.”KU$_USER_MAPPING_VIEW” 6.492 KB 53 rows
. . exported “SYS”.”DAM_CONFIG_PARAM$” 6.367 KB 10 rows
. . exported “SYS”.”AUD$” 0 KB 0 rows
. . exported “SYS”.”DAM_CLEANUP_EVENTS$” 0 KB 0 rows
. . exported “SYS”.”DAM_CLEANUP_JOBS$” 0 KB 0 rows
. . exported “SYS”.”FGA_LOG$” 0 KB 0 rows
. . exported “SYSTEM”.”SCHEDULER_PROGRAM_ARGS” 21.37 KB 154 rows
. . exported “SYSTEM”.”SCHEDULER_JOB_ARGS” 0 KB 0 rows
. . exported “SYSTEM”.”SYS_EXPORT_FULL_02″ 389.5 MB 123398 rows
. . exported “SYSTEM”.”SYS_EXPORT_FULL_03″ 389.7 MB 123591 rows
. . exported “SYSTEM”.”SYS_EXPORT_FULL_01″ 3.622 MB 16660 rows
. . exported “SYSTEM”.”SYS_EXPORT_FULL_04″ 2.289 MB 10893 rows
. . exported “SYSTEM”.”REPCAT$_AUDIT_ATTRIBUTE” 6.328 KB 2 rows
. . exported “SYSTEM”.”REPCAT$_OBJECT_TYPES” 6.882 KB 28 rows
. . exported “SYSTEM”.”REPCAT$_RESOLUTION_METHOD” 5.835 KB 19 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_STATUS” 5.484 KB 3 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_TYPES” 6.289 KB 2 rows
. . exported “OUTLN”.”OL$” 0 KB 0 rows
. . exported “OUTLN”.”OL$HINTS” 0 KB 0 rows
. . exported “OUTLN”.”OL$NODES” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_AQCALL” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_AQERROR” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_CALLDEST” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_DEFAULTDEST” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_DESTINATION” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_ERROR” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_LOB” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_ORIGIN” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_PROPAGATOR” 0 KB 0 rows
. . exported “SYSTEM”.”DEF$_PUSHED_TRANSACTIONS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_AUDIT_COLUMN” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_COLUMN_GROUP” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_CONFLICT” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_DDL” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_EXCEPTIONS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_EXTENSION” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_FLAVORS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_FLAVOR_OBJECTS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_GENERATED” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_GROUPED_COLUMN” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_INSTANTIATION_DDL” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_KEY_COLUMNS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_OBJECT_PARMS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_PARAMETER_COLUMN” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_PRIORITY” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_PRIORITY_GROUP” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REFRESH_TEMPLATES” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPCAT” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPCATLOG” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPCOLUMN” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPGROUP_PRIVS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPOBJECT” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPPROP” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_REPSCHEMA” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_RESOLUTION” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_RESOLUTION_STATISTICS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_RESOL_STATS_CONTROL” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_RUNTIME_PARMS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_SITES_NEW” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_SITE_OBJECTS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_SNAPGROUP” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_OBJECTS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_PARMS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_REFGROUPS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_SITES” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_TEMPLATE_TARGETS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_USER_AUTHORIZATIONS” 0 KB 0 rows
. . exported “SYSTEM”.”REPCAT$_USER_PARM_VALUES” 0 KB 0 rows
. . exported “SYSTEM”.”SQLPLUS_PRODUCT_PROFILE” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_FULL_05″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_05 is:
/u03/app/oracle/product/11.2.0/db_1/rdbms/log/expdat_full_tts.dmp
******************************************************************************
Datafiles required for transportable tablespace APP1:
/u03/app/oracle/oradata/aaadb/APP1.dbf
Datafiles required for transportable tablespace APP2:
/u03/app/oracle/oradata/aaadb/APP2.dbf
Datafiles required for transportable tablespace APP5:
/u03/app/oracle/oradata/aaadb/APP5.dbf
Datafiles required for transportable tablespace USERS:
/u03/app/oracle/oradata/aaadb/datafiles/AAADB/datafile/o1_mf_users_9l0d9qq4_.dbf
Job “SYSTEM”.”SYS_EXPORT_FULL_05″ successfully completed at 16:17:44

-bash-3.2$

Step-6#Copy the datafile from source to target using OS Utility

cd /opt/u01/app/oracle/oradata/csmdb/
scp APP1.dbf oracle@destination_ip:/home/oracle/datafiles/
scp APP2.dbf oracle@destination_ip:/home/oracle/datafiles/
scp APP5.dbf oracle@destination_ip:/home/oracle/datafiles/
scp o1_mf_users_9l0d9qq4_.dbf oracle@destination_ip:/home/oracle/datafiles/

Step-7# The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:

#Convert datafile at destination before import
RMAN> CONVERT DATAFILE
‘/home/oracle/datafiles/APP1.dbf’,
‘/home/oracle/datafiles/APP2.dbf’,
‘/home/oracle/datafiles/APP5.dbf’,
‘/home/oracle/datafiles/o1_mf_users_9l0d9qq4_.dbf’
TO PLATFORM=”Linux x86 64-bit”
FROM PLATFORM=”Linux IA (32-bit)”
DB_FILE_NAME_CONVERT=
‘/home/oracle/datafiles/’, ‘/opt/u01/app/oracle/oradata/csmdb/’
PARALLELISM=2;

Starting conversion at target at 06-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=326 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=400 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/datafiles/o1_mf_users_9l0d9qq4_.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of conversion at target command on ORA_DISK_1 channel at 01/06/2016 17:07:09
ORA-01276: Cannot add file /opt/u01/app/oracle/oradata/csmdb/o1_mf_users_9l0d9qq4_.dbf. File has an Oracle Managed Files file name.

RMAN> exit

#Correct the Datafile name to resolve the ORA-1276
mv o1_mf_users_9l0d9qq4_.dbf users160.dbf

-bash-4.1$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Wed Jan 6 17:10:14 2016

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

connected to target database: CSMDB (DBID=3697841330)

RMAN>
CONVERT DATAFILE
‘/home/oracle/datafiles/APP1.dbf’,
‘/home/oracle/datafiles/APP2.dbf’,
‘/home/oracle/datafiles/APP5.dbf’
‘/home/oracle/datafiles/users160.dbf’
TO PLATFORM=”Linux x86 64-bit”
FROM PLATFORM=”Linux IA (32-bit)”
DB_FILE_NAME_CONVERT=
‘/home/oracle/datafiles/’, ‘/opt/u01/app/oracle/oradata/csmdb/’
PARALLELISM=2; ;

Starting conversion at target at 06-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=400 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=9 device type=DISK
channel ORA_DISK_1: starting datafile conversion
converted datafile=/opt/u01/app/oracle/oradata/csmdb/users160.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/datafiles/APP5.dbf
converted datafile=/opt/u01/app/oracle/oradata/csmdb/APP5.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile conversion
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/datafiles/APP1.dbf
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/datafiles/APP2.dbf
converted datafile=/opt/u01/app/oracle/oradata/csmdb/APP1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/datafiles/netvertex_aircel.dbf
converted datafile=/opt/u01/app/oracle/oradata/csmdb/APP2.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 06-JAN-16

RMAN>

Step-8#Import the tablespace metadata using the Data Pump Import utility, impdp:
#The REMAP_SCHEMA parameter changes the ownership of database objects.
If you do not specify REMAP_SCHEMA, all database objects (such as tables and indexes)
are created in the same user schema as in the source database, and those users must
already exist in the destination database. If they do not exist, then the import utility
returns an error.

#vi create_users.sql
create user &&username identified by &&username;
grant connect,resource,create any view to &&username;
grant create job to &&username;
grant read,write on directory data_pump_dir to &&username;

impdp system full=Y dumpfile=expdat_full_tts.dmp directory=data_pump_dir
transport_datafiles=’/opt/u01/app/oracle/oradata/csmdb/APP1.dbf’,
‘/opt/u01/app/oracle/oradata/csmdb/APP2.dbf’,
‘/opt/u01/app/oracle/oradata/csmdb/APP5.dbf’,
‘/opt/u01/app/oracle/oradata/csmdb/users160.dbf’ logfile=import.log

-bash-4.1$ impdp system full=Y dumpfile=expdat_full_tts.dmp directory=data_pump_dir
transport_datafiles=’/opt/u01/app/oracle/oradata/csmdb/APP1.dbf’,’/opt/u01/app/oracle/oradata/csmdb/APP2.dbf’,’/opt/u01/app/oracle/oradata/csmdb/APP5.dbf’,’/opt/u01/app/oracle/oradata/csmdb/users160.dbf’ logfile=import.log

Import: Release 12.1.0.2.0 – Production on Wed Jan 6 17:29:17 2016

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

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 “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Source time zone is +05:30 and target time zone is +00:00.
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** full=Y dumpfile=expdat_full_tts.dmp directory=data_pump_dir transport_datafiles=/opt/u01/app/oracle/oradata/csmdb/aaa.dbf,/opt/u01/app/oracle/oradata/csmdb/aaa6604.dbf,/opt/u01/app/oracle/oradata/csmdb/aaa66249.dbf,/opt/u01/app/oracle/oradata/csmdb/APP1.dbf,/opt/u01/app/oracle/oradata/csmdb/APP2.dbf,/opt/u01/app/oracle/oradata/csmdb/dhyani_APP5.dbf,/opt/u01/app/oracle/oradata/csmdb/eliteaaa.dbf,/opt/u01/app/oracle/oradata/csmdb/eliteaaan660.dbf,/opt/u01/app/oracle/oradata/csmdb/nayana.dbf,/opt/u01/app/oracle/oradata/csmdb/netvertex_646.dbf,/opt/u01/app/oracle/oradata/csmdb/netvertex_aircel.dbf,/opt/u01/app/oracle/oradata/csmdb/netvertex_telenor.dbf,/opt/u01/app/oracle/oradata/csmdb/APP5.dbf,/opt/u01/app/oracle/oradata/csmdb/nvsmx_audit.dbf,/opt/u01/app/oracle/oradata/csmdb/nvsmx_dhyani.dbf,/opt/u01/app/oracle/oradata/csmdb/nv662.dbf,/opt/u01/app/oracle/oradata/csmdb/test.dbf,/opt/u01/app/oracle/oradata/csmdb/test_nvsmx.dbf,/opt/u01/app/oracle/oradata/csmdb/training.dbf,/opt/u01/app/oracle/oradata/csmdb/user644.dbf,/opt/u01/app/oracle/oradata/csmdb/users160.dbf,/opt/u01/app/oracle/oradata/csmdb/ytl.dbf logfile=import.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace ‘USERS’ already exists

Job “SYSTEM”.”SYS_IMPORT_FULL_01″ stopped due to fatal error at Wed Jan 6 17:29:50 2016 elapsed 0 00:00:30

#Correct the Tablespace Name to resolve the ORA-29349
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 6 17:30:17 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

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

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> ALTER TABLESPACE users RENAME TO usersts;

Tablespace altered.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERSTS

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

#Try to Import
-bash-4.1$ impdp system full=Y dumpfile=expdat_full_tts.dmp directory=data_pump_dir transport_datafiles=’/opt/u01/app/oracle/oradata/csmdb/aaa.dbf’,’/opt/u01/app/oracle/oradata/csmdb/aaa6604.dbf’,’/opt/u01/app/oracle/oradata/csmdb/aaa66249.dbf’,’/opt/u01/app/oracle/oradata/csmdb/APP1.dbf’,’/opt/u01/app/oracle/oradata/csmdb/APP2.dbf’,’/opt/u01/app/oracle/oradata/csmdb/dhyani_APP5.dbf’,’/opt/u01/app/oracle/oradata/csmdb/eliteaaa.dbf’,’/opt/u01/app/oracle/oradata/csmdb/eliteaaan660.dbf’,’/opt/u01/app/oracle/oradata/csmdb/nayana.dbf’,’/opt/u01/app/oracle/oradata/csmdb/netvertex_646.dbf’,’/opt/u01/app/oracle/oradata/csmdb/netvertex_aircel.dbf’,’/opt/u01/app/oracle/oradata/csmdb/netvertex_telenor.dbf’,’/opt/u01/app/oracle/oradata/csmdb/APP5.dbf’,’/opt/u01/app/oracle/oradata/csmdb/nvsmx_audit.dbf’,’/opt/u01/app/oracle/oradata/csmdb/nvsmx_dhyani.dbf’,’/opt/u01/app/oracle/oradata/csmdb/nv662.dbf’,’/opt/u01/app/oracle/oradata/csmdb/test.dbf’,’/opt/u01/app/oracle/oradata/csmdb/test_nvsmx.dbf’,’/opt/u01/app/oracle/oradata/csmdb/training.dbf’,’/opt/u01/app/oracle/oradata/csmdb/user644.dbf’,’/opt/u01/app/oracle/oradata/csmdb/users160.dbf’,’/opt/u01/app/oracle/oradata/csmdb/ytl.dbf’ logfile=import.log

Import: Release 12.1.0.2.0 – Production on Wed Jan 6 17:31:43 2016

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

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 “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Source time zone is +05:30 and target time zone is +00:00.
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** full=Y dumpfile=expdat_full_tts.dmp directory=data_pump_dir transport_datafiles=/opt/u01/app/oracle/oradata/csmdb/aaa.dbf,/opt/u01/app/oracle/oradata/csmdb/aaa6604.dbf,/opt/u01/app/oracle/oradata/csmdb/aaa66249.dbf,/opt/u01/app/oracle/oradata/csmdb/APP1.dbf,/opt/u01/app/oracle/oradata/csmdb/APP2.dbf,/opt/u01/app/oracle/oradata/csmdb/dhyani_APP5.dbf,/opt/u01/app/oracle/oradata/csmdb/eliteaaa.dbf,/opt/u01/app/oracle/oradata/csmdb/eliteaaan660.dbf,/opt/u01/app/oracle/oradata/csmdb/nayana.dbf,/opt/u01/app/oracle/oradata/csmdb/netvertex_646.dbf,/opt/u01/app/oracle/oradata/csmdb/netvertex_aircel.dbf,/opt/u01/app/oracle/oradata/csmdb/netvertex_telenor.dbf,/opt/u01/app/oracle/oradata/csmdb/APP5.dbf,/opt/u01/app/oracle/oradata/csmdb/nvsmx_audit.dbf,/opt/u01/app/oracle/oradata/csmdb/nvsmx_dhyani.dbf,/opt/u01/app/oracle/oradata/csmdb/nv662.dbf,/opt/u01/app/oracle/oradata/csmdb/test.dbf,/opt/u01/app/oracle/oradata/csmdb/test_nvsmx.dbf,/opt/u01/app/oracle/oradata/csmdb/training.dbf,/opt/u01/app/oracle/oradata/csmdb/user644.dbf,/opt/u01/app/oracle/oradata/csmdb/users160.dbf,/opt/u01/app/oracle/oradata/csmdb/ytl.dbf logfile=import.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:”SYS_UNDOTS” failed to create with error:
ORA-01516: nonexistent log file, data file, or temporary file “/u03/app/oracle/oradata/aaadb/datafiles/AAADB/datafile/o1_mf_sys_undo_9l0d9lt4_.dbf”
Failing sql is:
ALTER DATABASE DATAFILE ‘/u03/app/oracle/oradata/aaadb/datafiles/AAADB/datafile/o1_mf_sys_undo_9l0d9lt4_.dbf’ RESIZE 692060160
ORA-31684: Object type TABLESPACE:”TEMP” already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:”OUTLN” already exists
ORA-39083: Object type USER:”ELITE64418″ failed to create with error:
ORA-00959: tablespace ‘ELITE64418’ does not exist
Failing sql is:
CREATE USER “ELITE64418” IDENTIFIED BY VALUES ‘S:DF2A0444ACC7BFC44BD8A300C30A794BE7991E01638B7EC0472653EB7028;93988CF2021D0742’ DEFAULT TABLESPACE “ELITE64418” TEMPORARY TABLESPACE “TEMP”
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:”SELECT_CATALOG_ROLE” already exists
ORA-31684: Object type ROLE:”EXECUTE_CATALOG_ROLE” already exists
ORA-31684: Object type ROLE:”DELETE_CATALOG_ROLE” already exists
ORA-31684: Object type ROLE:”DBFS_ROLE” already exists
ORA-31684: Object type ROLE:”AQ_ADMINISTRATOR_ROLE” already exists
ORA-31684: Object type ROLE:”AQ_USER_ROLE” already exists
ORA-31684: Object type ROLE:”ADM_PARALLEL_EXECUTE_TASK” already exists
ORA-31684: Object type ROLE:”GATHER_SYSTEM_STATISTICS” already exists
ORA-31684: Object type ROLE:”RECOVERY_CATALOG_OWNER” already exists
ORA-31684: Object type ROLE:”SCHEDULER_ADMIN” already exists
ORA-31684: Object type ROLE:”HS_ADMIN_SELECT_ROLE” already exists
ORA-31684: Object type ROLE:”HS_ADMIN_EXECUTE_ROLE” already exists
ORA-31684: Object type ROLE:”HS_ADMIN_ROLE” already exists
ORA-31684: Object type ROLE:”GLOBAL_AQ_USER_ROLE” already exists
ORA-31684: Object type ROLE:”OEM_ADVISOR” already exists
ORA-31684: Object type ROLE:”OEM_MONITOR” already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
ORA-31684: Object type DIRECTORY:”ORACLE_OCM_CONFIG_DIR” already exists
ORA-31684: Object type DIRECTORY:”DATA_PUMP_DIR” already exists
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
ORA-31684: Object type CONTEXT:”GLOBAL_AQCLNTDB_CTX” already exists
ORA-31684: Object type CONTEXT:”DBFS_CONTEXT” already exists
ORA-31684: Object type CONTEXT:”REGISTRY$CTX” 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
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’), export_db_name=>’AAADB’, inst_scn=>’165370149′);COMMIT; END;
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” 6.492 KB 53 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 “SYS”.”AMGT$DP$DAM_CONFIG_PARAM$” 6.367 KB 10 rows
. . imported “SYS”.”AMGT$DP$AUD$” 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$FGA_LOG$” 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
ORA-31693: Table data object “SYSTEM”.”SCHEDULER_PROGRAM_ARGS_TMP” failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-22303: type “SYS”.”JDM_STR_VALS” not found
ORA-21700: object does not exist or is marked for delete
. . imported “SYSTEM”.”SCHEDULER_JOB_ARGS_TMP” 0 KB 0 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 “SYSTEM”.”SYS_EXPORT_FULL_02″ 389.5 MB 123398 rows
. . imported “SYSTEM”.”SYS_EXPORT_FULL_03″ 389.7 MB 123591 rows
. . imported “SYSTEM”.”SYS_EXPORT_FULL_01″ 3.622 MB 16660 rows
. . imported “SYSTEM”.”SYS_EXPORT_FULL_04″ 2.289 MB 10893 rows
. . imported “OUTLN”.”OL$” 0 KB 0 rows
. . imported “OUTLN”.”OL$HINTS” 0 KB 0 rows
. . imported “OUTLN”.”OL$NODES” 0 KB 0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
ORA-39083: Object type PROCEDURE failed to create with error:
ORA-31625: Schema ELITE64418 is needed to import this object, but is unaccessible
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 9189
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
ORA-39083: Object type ALTER_PROCEDURE failed to create with error:
ORA-31625: Schema ELITE64418 is needed to import this object, but is unaccessible
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 9189
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

USERNAME DEFAULT_TABLESPA
———————————–
APP1 APP1
APP2 APP2
APP3 USERS
APP4 USERS
APP5 APP5
APP6 USERS
APP7 USERS
APP8 USERS

Step-9#Confirm data dictionaries of DBA_SEQUENCES/DBA_SOURCE/DBA_SCHEDULER_JOBS/DBA_SYNONYMS

col sequence_owner format a15
col CNT format 9999
SELECT SEQUENCE_OWNER,COUNT(SEQUENCE_NAME) CNT FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER IN (‘APP6’,
‘APP4’,
‘APP7’,
‘APP2’,
‘APP1’,
‘APP8’,
‘APP5’,
‘APP3’)
GROUP BY SEQUENCE_OWNER
ORDER BY SEQUENCE_OWNER;

SEQUENCE_OWNER CNT SEQUENCE_OWNER CNT
————— —– ————— —–
APP8 74 APP8 74

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(DISTINCT NAME)CNT FROM DBA_SOURCE
WHERE OWNER IN (‘APP6’,
‘APP4’,
‘APP7’,
‘APP2’,
‘APP1’,
‘APP8’,
‘APP5’,
‘APP3’)
GROUP BY OWNER
ORDER BY OWNER;

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(JOB_NAME) FROM DBA_SCHEDULER_JOBS
WHERE OWNER IN (‘APP6’,
‘APP4’,
‘APP7’,
‘APP2’,
‘APP1’,
‘APP8’,
‘APP5’,
‘APP3’)
GROUP BY OWNER
ORDER BY OWNER;

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(SYNONYM_NAME) FROM DBA_SYNONYMS
WHERE OWNER IN (‘APP6’,
‘APP4’,
‘APP7’,
‘APP2’,
‘APP1’,
‘APP8’,
‘APP5’,
‘APP3’)
GROUP BY OWNER
ORDER BY OWNER;

col owner format a20
col object_name format a25
col object_type format a15
select owner,object_name,object_type from dba_objects where status != ‘VALID’
and owner IN (
‘APP6’,
‘APP4’,
‘APP7’,
‘APP2’,
‘APP1’,
‘APP8’,
‘APP5’,
‘APP3’);

Step-10# Check Invalid objects

col owner format a20
col object_name format a25
col object_type format a15
select owner,object_name,object_type from dba_objects where status != ‘VALID’;

SQL> col owner format a20
SQL> col object_name format a25
SQL> col object_type format a15
SQL> select owner,object_name,object_type from dba_objects where status != ‘VALID’;

OWNER OBJECT_NAME OBJECT_TYPE
——————– ————————- —————
SYS DBA_REPGROUPED_COLUMN VIEW
SYS ALL_REPGROUPED_COLUMN VIEW
SYS USER_REPGROUPED_COLUMN VIEW
SYS _ALL_REPGROUPED_COLUMN VIEW
SYS _ALL_REPCOLUMN_GROUP VIEW
SYS _ALL_REPRESOLUTION VIEW
SYS DBA_REPPARAMETER_COLUMN VIEW
SYS _ALL_REPPARAMETER_COLUMN VIEW
SYS ALL_REPPARAMETER_COLUMN VIEW
SYS USER_REPPARAMETER_COLUMN VIEW
SYS _ALL_REPCONFLICT VIEW

OWNER OBJECT_NAME OBJECT_TYPE
——————– ————————- —————
SYS DBMS_REPCAT_MIGRATION PACKAGE BODY
SYS DBMS_REPCAT_UTL4 PACKAGE BODY
SYS DBMS_REPCAT_RGT_CUST PACKAGE BODY
PUBLIC DBA_REPGROUPED_COLUMN SYNONYM
PUBLIC ALL_REPGROUPED_COLUMN SYNONYM
PUBLIC USER_REPGROUPED_COLUMN SYNONYM
PUBLIC DBA_REPPARAMETER_COLUMN SYNONYM
PUBLIC ALL_REPPARAMETER_COLUMN SYNONYM
PUBLIC USER_REPPARAMETER_COLUMN SYNONYM
USER644 PROC_RESET_BOD_STATUS PROCEDURE
AAA FUNCTION2 FUNCTION
APP7 TRG_SUBSCRIPTION_HISTORY_ TRIGGER

30 rows selected.

#Compile the Invalid Objects
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2016-01-06 18:13:57

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_END 2016-01-06 18:14:04

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
——————-
0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
—————————
0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

…Database user “SYS”, database schema “APEX_040200”, user# “98” 18:14:26
…Compiled 0 out of 3014 objects considered, 0 failed compilation 18:14:26
…271 packages
…263 package bodies
…452 tables
…11 functions
…16 procedures
…3 sequences
…457 triggers
…1320 indexes
…211 views
…0 libraries
…6 types
…0 type bodies
…0 operators
…0 index types
…Begin key object existence check 18:14:26
…Completed key object existence check 18:14:27
…Setting DBMS Registry 18:14:27
…Setting DBMS Registry Complete 18:14:27
…Exiting validate 18:14:27

PL/SQL procedure successfully completed.

SQL>

SQL> select owner,object_name,object_type from dba_objects where status != ‘VALID’;

no row selected

Conclusion:
You can use full transportable export/import feature for Upgrading to a New Release of Oracle Database.
Above article has shown that full transportable export/import feature solution has been used as data migrate between cross platform (like Linux32bit (Little) to linux64bit (little) endian format) and cross database version (from source DB 11g v11.2.0.3.0 to target DB 12c v12.1.0.2.0.)

References:
Oracle12c Database Administrator’s Guide15 Transporting Data
https://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN13721
https://community.oracle.com/thread/958704?start=0&tstart=0
https://community.oracle.com/thread/2277648?tstart=0
https://community.oracle.com/thread/2277648?tstart=0

About Author:
Hitesh Gondalia
Working as Sr. Database Administrator having 8.6 years of experience with good exposure of handling the Production Database Challenges of Telecom, Banking and Finance Applications
OCE Certified in RACv11g
OCP Certified in v12c/11g/10g/9i
CCNA Certified
OTN Community – Master Tag
https://hiteshgondalia.wordpress.com

Transporting Tablespaces from 11g Database to 12c Database

This article covers the Transporting Data: Scenarios of “Transportable Tablespaces” https://community.oracle.com/docs/DOC-991671

Introduction:
This is existing feature onwards Oracle 8i.

You can transport data at any of the following levels:
Database
You can use the full transportable export/import feature to move an entire database to a different database instance.
 Tablespaces
You can use the transportable tablespaces feature to move a set of tablespaces between databases.
 Tables, partitions, and subpartitions
You can use the transportable tables feature to move a set of tables, partitions, and subpartitions between databases.

Transporting data is much faster than performing either an export/import or unload/load of the same data. It is faster because, for user-defined tablespaces, the data files containing all of the actual data are copied to the target location, and you use Data Pump to transfer only the metadata of the database objects to the new database.

The Procedure:
Scenario having Source Database on Standalone 11g DB v11.2.0.3.0 on Solaris 10 64bit, Data files stored on File system and Destination Database on 2-Node Oracle RAC 12c DB v12.1.0.2.0 on Linux 7.0 64bit, Data files will be stored on Oracle ASM

Step-1# It is assumed the following datafiles and tablespaces exist in source database:

set linesize 100 pagesize 100
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
WHERE USERNAME IN
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

USERNAME DEFAULT_TABLESPACE
————– ——————————
MYAPP5 MYAPP5
MYAPP4 MYAPP4
MYAPP1 MYAPP1
MYAPP3 MYAPP3
MYAPP6 MYAPP6
MYAPP7 MYAPP7
MYAPP2 MYAPP2

select tablespace_name,file_name from dba_data_files where tablespace_name in
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

TABLESPACE_NAME FILE_NAME
—————————— ———————————————
MYAPP7 /u01/app/oracle/oradata/mydb/MYAPP7.dbf
MYAPP2 /u01/app/oracle/oradata/mydb/MYAPP2.dbf
MYAPP6 /u01/app/oracle/oradata/mydb/MYAPP6.dbf
MYAPP3 /u01/app/oracle/oradata/mydb/MYAPP3.dbf
MYAPP1 /u01/app/oracle/oradata/mydb/MYAPP1.dbf
MYAPP4 /u01/app/oracle/oradata/mydb/MYAPP4.dbf
MYAPP5 /u01/app/oracle/oradata/mydb/MYAPP5.dbf

select tablespace_name,status from dba_tablespaces where tablespace_name in (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

Step-2# Determine if Platforms are Supported and Determine Endianness
This task is only necessary if you are transporting the tablespace set to a platform different from the source platform.

If you are transporting the tablespace set to a platform different from the source platform,
then determine if cross-platform tablespace transport is supported for both the source and
destination platforms, and determine the endianness of each platform. If both platforms have
the same endianness, no conversion is necessary. Otherwise you must do a conversion of the
tablespace set either at the source or destination database.

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; 2 3

Source
=========
PLATFORM_NAME ENDIAN_FORMAT
————————————————
Solaris[tm] OE (64-bit) Big

Destination
============
PLATFORM_NAME ENDIAN_FORMAT
————————————————-
Linux x86 64-bit Little
Step-3# The following statement can be used to determine whether tablespaces are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2’, TRUE);

PL/SQL procedure successfully completed.

#After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

Step-4# Make all tablespaces in the set read-only.

SQL> ALTER TABLESPACE MYAPP5 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP4 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP1 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP3 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP6 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP7 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP2 READ ONLY;

Tablespace altered.

TABLESPACE_NAME STATUS
—————————— ———
MYAPP4 READ ONLY
MYAPP5 READ ONLY
MYAPP6 READ ONLY
MYAPP7 READ ONLY
MYAPP1 READ ONLY
MYAPP2 READ ONLY
MYAPP3 READ ONLY

Step-5# Invoke the Data Pump export utility as user system and specify the tablespaces in the transportable set.

expdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_tablespaces=MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2 logfile=tts_export_05012016.log

-bash-3.00$ expdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_tablespaces=MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2 logfile=tts_export_05012016.log

Export: Release 11.2.0.3.0 – Production on Tue Jan 5 15:36:29 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″: system/******** dumpfile=expdat05012016.dmp directory=data_pump_dir transport_tablespaces=MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2 logfile=tts_export_05012016.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/product/11.2.0/db_1/rdbms/log/expdat05012016.dmp
******************************************************************************
Datafiles required for transportable tablespace MYAPP4:
/u01/app/oracle/oradata/mydb/MYAPP4.dbf
Datafiles required for transportable tablespace MYAPP5:
/u01/app/oracle/oradata/mydb/MYAPP5.dbf
Datafiles required for transportable tablespace MYAPP6:
/u01/app/oracle/oradata/mydb/MYAPP6.dbf
Datafiles required for transportable tablespace MYAPP7:
/u01/app/oracle/oradata/mydb/MYAPP7.dbf
Datafiles required for transportable tablespace MYAPP1:
/u01/app/oracle/oradata/mydb/MYAPP1.dbf
Datafiles required for transportable tablespace MYAPP2:
/u01/app/oracle/oradata/mydb/MYAPP2.dbf
Datafiles required for transportable tablespace MYAPP3:
/u01/app/oracle/oradata/mydb/MYAPP3.dbf
Job “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 15:46:31

Step-6# Prepare Source to transfer datafile and backup to target database
#tnsnames.ora
CSMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = csmdb)
)
)

-bash-3.00$ tnsping CSMDB
TNS Ping Utility for Solaris: Version 11.2.0.3.0 – Production on 05-JAN-2016 16:58:29
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.139)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = csmdb)))
OK (0 msec)

SQL> CREATE OR REPLACE DIRECTORY db_files_dir1 AS ‘/u01/app/oracle/oradata/mydb/’;

Directory created.

CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY eliteAAA123 USING ‘csmdb’;

Destination:
SQL> CREATE OR REPLACE DIRECTORY db_files_dir2 as ‘+DATA’;

Directory created.

Step-7# Copy the datafile from source to target using the Oracle Utility -DBMS_FILE_TRANSFER.PUT_FILE
Source:
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP4.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP4.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP5.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP5.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP6.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP6.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP7.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP7.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP1.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP1.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP2.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP2.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP3.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP3.dbf’,
destination_database => ‘REMOTE’);
END;
/

.oraenv
+ASM2

asmcmd
asmcmd> lsdg
confirm the location of copied datafile.

Step-8# The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:
CONVERT DATAFILE
‘+DATA/MYAPP4.dbf’,
‘+DATA/MYAPP5.dbf’,
‘+DATA/MYAPP6.dbf’,
‘+DATA/MYAPP7.dbf’,
‘+DATA/MYAPP1.dbf’,
‘+DATA/MYAPP2.dbf’,
‘+DATA/MYAPP3.dbf’
TO PLATFORM=”Linux x86 64-bit”
FROM PLATFORM=”Solaris[tm] OE (64-bit)”
DB_FILE_NAME_CONVERT=’/home/oracle/Demo_Db’,’+DATA’
PARALLELISM=2;

Above command failed with error
ORA-19563: cross-platform datafile header validation failed for file +RECO/soets_9.tf

MetaLink and OTN Community
—————————
Known issue Using DBMS_FILE_TRANSFER

=> Unpublished Bug 13636964- ORA-19563 from RMAN convert on datafile copy transferred with DBMS_FILE_TRANSFER (Doc ID 13636964.8)
Versions confirmed as being affected
11.2.0.3
This issue is fixed in
12.1.0.1 (Base Release)
11.2.0.4 (Future Patch Set)

Description

A file transferred using DBMS_FILE_TRANSFER fails during an RMAN convert
operation.
eg:
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of conversion at target command at 01/24/2012 16:22:23
ORA-19563: cross-platform datafile header validation failed for file +RECO/soets_9.tf

Rediscovery Notes:
If RMAN convert fails on a file transferred using DBMS_FILE_TRANSFER
then it may be due to this bug

Workaround
Transfer the file using OS facilities.

Step-9##Copy the datafile from source to target using OS Utility
scp MYAPP1.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP2.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP3.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP4.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP5.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP6.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP7.dbf oracle@target_server:/home/oracle/Demo_Db

Step-10# The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:
-bash-4.2$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 5 17:07:43 2016

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

connected to target database: CSMDB (DBID=3698469423)

RMAN> CONVERT DATAFILE
‘/home/oracle/Demo_Db/MYAPP4.dbf’,
‘/home/oracle/Demo_Db/MYAPP5.dbf’,
‘/home/oracle/Demo_Db/MYAPP6.dbf’,
‘/home/oracle/Demo_Db/MYAPP7.dbf’,
‘/home/oracle/Demo_Db/MYAPP1.dbf’,
‘/home/oracle/Demo_Db/MYAPP2.dbf’,
‘/home/oracle/Demo_Db/MYAPP3.dbf’
TO PLATFORM=”Linux x86 64-bit”
FROM PLATFORM=”Solaris[tm] OE (64-bit)”
DB_FILE_NAME_CONVERT=’/home/oracle/Demo_Db’,’+DATA’
PARALLELISM=2;

Starting conversion at target at 05-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=867 instance=csmdb2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1058 instance=csmdb2 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP2.dbf
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP3.dbf
converted datafile=+DATA/MYAPP3.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP1.dbf
converted datafile=+DATA/MYAPP1.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP4.dbf
converted datafile=+DATA/MYAPP4.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP5.dbf
converted datafile=+DATA/MYAPP5.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP6.dbf
converted datafile=+DATA/MYAPP6.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP7.dbf
converted datafile=+DATA/MYAPP2.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
converted datafile=+DATA/MYAPP7.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 05-JAN-16

Step-11# Import the tablespace metadata using the Data Pump Import utility, impdp:
impdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_datafiles=+DATA/MYAPP4.dbf,+DATA/MYAPP5.dbf,+DATA/MYAPP6.dbf,+DATA/MYAPP7.dbf,+DATA/MYAPP1.dbf,+DATA/MYAPP2.dbf,+DATA/MYAPP3.dbf logfile=tts_import.log

#The REMAP_SCHEMA parameter changes the ownership of database objects.
If you do not specify REMAP_SCHEMA, all database objects (such as tables and indexes)
are created in the same user schema as in the source database, and those users must
already exist in the destination database. If they do not exist, then the import utility
returns an error.

#vi create_users.sql
create user &&username identified by &&username;
grant connect,resource,create any view to &&username;
grant create job to &&username;
grant read,write on directory data_pump_dir to &&username;

#Import
-bash-4.2$ impdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_datafiles=+DATA/MYAPP4.dbf,+DATA/MYAPP5.dbf,+DATA/MYAPP6.dbf,+DATA/MYAPP7.dbf,+DATA/MYAPP1.dbf,+DATA/MYAPP2.dbf,+DATA/MYAPP3.dbf logfile=tts_import.log

Import: Release 12.1.0.2.0 – Production on Tue Jan 5 17:26:57 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Source time zone version is 14 and target time zone version is 18.
Source time zone is +05:30 and target time zone is +00:00.
Starting “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″: system/******** dumpfile=expdat05012016.dmp directory=data_pump_dir transport_datafiles=+DATA/MYAPP4.dbf,+DATA/MYAPP5.dbf,+DATA/MYAPP6.dbf,+DATA/MYAPP7.dbf,+DATA/MYAPP1.dbf,+DATA/MYAPP2.dbf,+DATA/MYAPP3.dbf logfile=tts_import.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:”MYAPP1″.”TBLRADIUSCUSTOMER_SEQ_TRIGGER” created with compilation warnings
ORA-39082: Object type TRIGGER:”MYAPP3″.”TBLRADIUSCUSTOMER_SEQ_TRIGGER” created with compilation warnings
Job “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ completed with 2 error(s) at Tue Jan 5 17:31:23 2016 elapsed 0 00:04:22

#Check Alert logs
Tue Jan 05 17:27:02 2016
Archived Log entry 132 added for thread 2 sequence 77 ID 0xdc71d92f dest 1:
Tue Jan 05 17:27:03 2016
Plug in tablespace MYAPP4 with datafile
‘+DATA/MYAPP4.dbf’
Plug in tablespace MYAPP5 with datafile
‘+DATA/MYAPP5.dbf’
Plug in tablespace MYAPP6 with datafile
‘+DATA/MYAPP6.dbf’
Plug in tablespace MYAPP7 with datafile
‘+DATA/MYAPP7.dbf’
Plug in tablespace MYAPP1 with datafile
‘+DATA/MYAPP1.dbf’
Plug in tablespace MYAPP2 with datafile
‘+DATA/MYAPP2.dbf’
Plug in tablespace MYAPP3 with datafile
‘+DATA/MYAPP3.dbf’
Tue Jan 05 17:27:04 2016
Thread 2 cannot allocate new log, sequence 79
Checkpoint not complete

heckpoint not complete
Current log# 3 seq# 87 mem# 0: +DATA/CSMDB/ONLINELOG/group_3.270.899316471
Current log# 3 seq# 87 mem# 1: +DATA/CSMDB/ONLINELOG/group_3.271.899316471
Tue Jan 05 17:31:08 2016
Thread 2 advanced to log sequence 88 (LGWR switch)
Current log# 4 seq# 88 mem# 0: +DATA/CSMDB/ONLINELOG/group_4.272.899316471
Current log# 4 seq# 88 mem# 1: +DATA/CSMDB/ONLINELOG/group_4.273.899316473
Tue Jan 05 17:31:08 2016
Archived Log entry 145 added for thread 2 sequence 87 ID 0xdc71d92f dest 1:
ALTER TABLESPACE “MYAPP4” READ WRITE
Completed: ALTER TABLESPACE “MYAPP4” READ WRITE
ALTER TABLESPACE “MYAPP4” READ ONLY
Tue Jan 05 17:31:21 2016
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP4” READ ONLY
ALTER TABLESPACE “MYAPP5” READ WRITE
Completed: ALTER TABLESPACE “MYAPP5” READ WRITE
ALTER TABLESPACE “MYAPP5” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP5” READ ONLY
ALTER TABLESPACE “MYAPP6” READ WRITE
Completed: ALTER TABLESPACE “MYAPP6” READ WRITE
ALTER TABLESPACE “MYAPP6” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP6” READ ONLY
ALTER TABLESPACE “MYAPP7” READ WRITE
Completed: ALTER TABLESPACE “MYAPP7” READ WRITE
ALTER TABLESPACE “MYAPP7” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP7” READ ONLY
ALTER TABLESPACE “MYAPP1” READ WRITE
Completed: ALTER TABLESPACE “MYAPP1” READ WRITE
ALTER TABLESPACE “MYAPP1” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP1” READ ONLY
ALTER TABLESPACE “MYAPP2” READ WRITE
Completed: ALTER TABLESPACE “MYAPP2” READ WRITE
ALTER TABLESPACE “MYAPP2” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP2” READ ONLY
ALTER TABLESPACE “MYAPP3” READ WRITE
Completed: ALTER TABLESPACE “MYAPP3” READ WRITE
ALTER TABLESPACE “MYAPP3” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP3” READ ONLY

Step-12#Make all tablespaces in the set read-write.

ALTER TABLESPACE MYAPP5 READ WRITE;

ALTER TABLESPACE MYAPP4 READ WRITE;

ALTER TABLESPACE MYAPP1 READ WRITE;

ALTER TABLESPACE MYAPP3 READ WRITE;

ALTER TABLESPACE MYAPP6 READ WRITE;

ALTER TABLESPACE MYAPP7 READ WRITE;

ALTER TABLESPACE MYAPP2 READ WRITE;
Step-13 #Confirm the data dictionaries of DBA_USERS/DBA_TABLESPACES
set linesize 100 pagesize 100
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
WHERE USERNAME IN
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

USERNAME DEFAULT_TABLESPACE
——————————
MYAPP2 USERS
MYAPP7 USERS
MYAPP6 USERS
MYAPP3 USERS
MYAPP1 USERS
MYAPP4 USERS
MYAPP5 USERS

TABLESPACE_NAME FILE_NAME
—————————— ——————-
MYAPP2 +DATA/MYAPP2.dbf
MYAPP7 +DATA/MYAPP7.dbf
MYAPP6 +DATA/MYAPP6.dbf
MYAPP3 +DATA/MYAPP3.dbf
MYAPP1 +DATA/MYAPP1.dbf
MYAPP4 +DATA/MYAPP4.dbf
MYAPP5 +DATA/MYAPP5.dbf

Step-14#Assign the user to his respective default tablespace
ALTER USER MYAPP2 DEFAULT TABLESPACE MYAPP2;
ALTER USER MYAPP7 DEFAULT TABLESPACE MYAPP7;
ALTER USER MYAPP6 DEFAULT TABLESPACE MYAPP6;
ALTER USER MYAPP3 DEFAULT TABLESPACE MYAPP3;
ALTER USER MYAPP1 DEFAULT TABLESPACE MYAPP1;
ALTER USER MYAPP4 DEFAULT TABLESPACE MYAPP4;
ALTER USER MYAPP5 DEFAULT TABLESPACE MYAPP5;

set linesize 100 pagesize 100
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
WHERE USERNAME IN
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

USERNAME DEFAULT_TABLESPACE
——————————
MYAPP4 MYAPP4
MYAPP5 MYAPP5
MYAPP1 MYAPP1
MYAPP3 MYAPP3
MYAPP6 MYAPP6
MYAPP7 MYAPP7
MYAPP2 MYAPP2

Step-15# Check Invalid objects

SQL> select object_name,owner from dba_objects where status != ‘VALID’;

OBJECT_NAME                                                                   OWNER
——————————————————————————–
TBLRADIUSCUSTOMER_SEQ_TRIGGER                            MYAPP1
TBLRADIUSCUSTOMER_SEQ_TRIGGER                            MYAPP3

SQL> select text from user_source where name=’TBLRADIUSCUSTOMER_SEQ_TRIGGER’;

TEXT
——————————————————————————–
TRIGGER TBLRADIUSCUSTOMER_SEQ_TRIGGER
BEFORE INSERT ON TBLRADIUSCUSTOMER
FOR EACH ROW

BEGIN

IF :new.ID IS NULL
THEN
SELECT SEQ_RADIUSCUSTOMER.nextval INTO :new.ID FROM DUAL;
END IF;
END;

17 rows selected.

SQL> select SEQ_RADIUSCUSTOMER.nextval from dual;
select SEQ_RADIUSCUSTOMER.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist

SQL> select sequence_name from user_sequences;

no rows selected

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
-bash-4.2$ sqlplus MYAPP3/MYAPP3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 5 17:51:45 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select sequence_name from user_sequences;

no rows selected

SQL>

I am trying to transport tablespaces from one DB Instance to another db instances on Solaris 64bit to Linux 64bit
It works fine for Tables/Index and constrains but unable to transport Functions and Sequences.

The only objects that are exported as part of transportable are objects associated with the
tablespaces that you list. If you want to see the complete list, you can query the view:

select unique full_path from SYS.datapump_paths where het_type = ‘TRANSPORTABLE_EXPORT’;

This will give you what gets exported as part of a datapump transportable export job.
If you need to export synonyms or packages, you can use datapump, but you will either
need to use the schema mode or full mode. You can add the include parameter to only get
what you want.

Step-16# export and import sequence,procedure,function,package,PROCOBJ,synonyms from source database to target database
First Method
——————–
Source:
alter tablespace MYAPP4 read write;
grant read,write on directory data_pump_dir to MYAPP4;

-bash-3.00$ expdp MYAPP5 dumpfile=expdat06012016_other.dmp directory=data_pump_dir schemas=MYAPP5 logfile=tts_export_06012016_other.log include=sequence,procedure,function,package,PROCOBJ,synonyms

Export: Release 11.2.0.3.0 – Production on Wed Jan 6 10:47:46 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “MYAPP5”.”SYS_EXPORT_SCHEMA_01″: MYAPP5/******** dumpfile=expdat06012016_other.dmp directory=data_pump_dir schemas=MYAPP5 logfile=tts_export_06012016_other.log include=sequence,procedure,function,package
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39168: Object path FUNCTION was not found.
ORA-39168: Object path PACKAGE was not found.
Master table “MYAPP5”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYAPP5.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/11.2.0/db_1/rdbms/log/expdat06012016_other.dmp
Job “MYAPP5”.”SYS_EXPORT_SCHEMA_01″ completed with 2 error(s) at 10:48:21

Destination:
alter user MYAPP4 quota unlimited on MYAPP4;
impdp MYAPP4 dumpfile=expdat06012016_MYAPP4.dmp directory=data_pump_dir schemas=MYAPP4 logfile=tts_export_06012016_MYAPP4.log include=sequence,procedure,function,package,PROCOBJ

select count(sequence_name) from user_sequences;
select count(distinct name) from user_source;
select count(job_name) from user_scheduler_jobs;

Second Method
————————-
expdp system dumpfile=expdat06012016_full.dmp directory=data_pump_dir full=y logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms

-bash-3.00$ expdp system dumpfile=expdat06012016_full.dmp directory=data_pump_dir full=y logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms

Export: Release 11.2.0.3.0 – Production on Wed Jan 6 11:43:49 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** dumpfile=expdat06012016_full.dmp directory=data_pump_dir full=y logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
ORA-39168: Object path SYNONYMS was not found.
Master table “SYSTEM”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/product/11.2.0/db_1/rdbms/log/expdat06012016_full.dmp
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ completed with 1 error(s) at 11:46:37

-bash-3.00$ cd /u01/app/oracle/product/11.2.0/db_1/rdbms/log/
-bash-3.00$ scp expdat06012016_full.dmp oracle@192.168.2.138:/u01/app/oracle/product/12.1.0.2/db_1/rdbms/log/
oracle@192.168.2.138’s password:
expdat06012016_full. 100% |*********************************************************************************| 3328 KB 00:00

Destination:
impdp system dumpfile=expdat06012016_full.dmp directory=data_pump_dir logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms
or
impdp &username/&password dumpfile=expdat06012016_full.dmp directory=data_pump_dir logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms

Step-17#Confirm data dictionaries of DBA_SEQUENCES/DBA_SOURCE/DBA_SCHEDULER_JOBS/DBA_SYNONYMS
col sequence_owner format a15
col CNT format 9999
SELECT SEQUENCE_OWNER,COUNT(SEQUENCE_NAME) CNT FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY SEQUENCE_OWNER
ORDER BY SEQUENCE_OWNER;

SEQUENCE_OWNER CNT
————— —–
MYAPP4 109
MYAPP5 112
MYAPP6 151
MYAPP7 152
MYAPP1 152
MYAPP2 152
MYAPP3 151

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(DISTINCT NAME)CNT FROM DBA_SOURCE
WHERE OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY OWNER
ORDER BY OWNER;

OWNER CNT
————— —–
MYAPP4 5
MYAPP5 1
MYAPP6 1
MYAPP7 1
MYAPP1 3
MYAPP2 1
MYAPP3 3

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(JOB_NAME) FROM DBA_SCHEDULER_JOBS
WHERE OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY OWNER
ORDER BY OWNER;

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(SYNONYM_NAME) FROM DBA_SYNONYMS
WHERE OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY OWNER
ORDER BY OWNER;

Step-18# Check Invalid objects

SQL> select object_name,owner from dba_objects where status != ‘VALID’;

OBJECT_NAME OWNER
——————————————————————————–
TBLRADIUSCUSTOMER_SEQ_TRIGGER MYAPP1
TBLRADIUSCUSTOMER_SEQ_TRIGGER MYAPP3

EXEC DBMS_UTILITY.COMPILE_SCHEMA(MYAPP1);
EXEC DBMS_UTILITY.COMPILE_SCHEMA(MYAPP2);

SQL> select object_name,owner from dba_objects where status != ‘VALID’;
no rows selected

Conclusion:
Above This article has showed that Oracle Transportable Tablespaces Solution has been used as data migrate between cross platform (like solari(big) to linux(little) endian format) and cross database version (from source DB 11g v11.2.0.3.0 to target DB 12c v12.1.0.2.0.)

References:
Oracle11g Oracle® Database Administrator’s Guide have–> 14 Managing Tablespaces–> Transporting Tablespaces Between Databases
Oracle12c Database Administrator’s Guide15 Transporting Data
https://community.oracle.com/thread/958704?start=0&tstart=0
https://community.oracle.com/thread/2277648?tstart=0
https://community.oracle.com/thread/2277648?tstart=0

About Author:
Hitesh Gondalia
Working as Sr. Database Administrator having 8.6 years of experience with good exposure of handling the Production Database Challenges of Telecom, Banking and Finance Applications
OCE Certified in RACv11g
OCP Certified in v12c/11g/10g/9i
CCNA Certified
OTN Community – Master Tag
https://hiteshgondalia.wordpress.com