Transporting Tablespaces from 11g Database to 12c Database

Version 11
Visibility: Open to anyone

    Transporting Tablespaces from 11g Database to 12c Database

     

    Introduction:

    Purpose of Transporting Data

     

    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.

     

    Documentation:

    Oracle® Database Administrator’s Guide have

    –> 14 Managing Tablespaces

    –> Transporting Tablespaces Between Databases

     

    This whitepaper covers the Transporting Data: Scenarios of “Transportable Tablespaces”

     

    Source Database:

    Standalone 11g DB v11.2.0.3.0 on Solaris 10 64bit

    Data files stored on File system

     

    Destination Database:

    2-Node Oracle RAC 12c DB v12.1.0.2.0 on Linux 7.0 64bit

    Data files will be stored on Oracle ASM

     

    Hands On:

    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

     

    Stpe-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 trasfer 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-12 #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-13#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-14# 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;

     

    TEXT

    --------------------------------------------------------------------------------

     

    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-15# 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-16#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-17# 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

     

     

    Summary:

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

    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,OCPv12c/11g/10g/9i

    OTN Community - Master Tag

    https://hiteshgondalia.wordpress.com