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

Advertisements