Transporting FULL Database from 11g Database to 12c Database

Version 9
Visibility: Open to anyone

    Upgrading to a New Release of Oracle Database

     

    Introduction:

     

    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.

     

    Documentation:

    Oracle® Database Administrator’s Guide have

    –> 14 Managing Tablespaces

    –> Transporting Tablespaces Between Databases

     

    This whitepaper covers the Transporting Data: Scenarios of “full transportable export/import feature”

     

    Source Database:

    Standalone EE 11g DBv11.2.0.3.0 - RHEL 4 32bit

     

    Target Database:

    Standalone EE 12c DBv12.1.0.2.0 - RHEL 6.5 64bit

     

    Hands On:

    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

     

     

    Summary:

    You can use Full TTS for Upgrading to a New Release of Oracle Database.

    Above whitepaper has shown that TTS 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:

    https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#i1007252

    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

     

    Author:

    Hitesh Gondalia

    Lead Database Administrator

    OCE RACv11g,OCP v12c/11g/10g/9i

    OTN Community - Master Tag

    https://hiteshgondalia.wordpress.com