Full Transportable Export 12c

Full Transportable export in 12c

Full transportable export/import used in many scenarios.

1)  Upgrading a database from 11g (11.2.0.3 & up) to 12c. Create a empty database in 12c and Export the 11g database and import to 12c.

2)  Transporting database to another server.

3)  Transporting non-cdb into non-cdb or cdb.

New parameter TRANSPORTABLE=ALWAYS , if exporting in 11g then need to use VERSION=12 while exporting.

Data movement methods.

1)  Meta data of objects residing in transportable tablespace are unloaded into dumpfile.

2)  Acutal data (datafile) will be copied from source to target system.

3)  Objects residing in non-transportable tablespace (system & sysaux) will be unloaded to dumpfile set.

Advantages of full transportable over transportable tablespace.

1.  Transportable tablespaces are usually the fastest fast way to move user and application data between databases, because tablespace data files are moved all together from the source database to the target.

   Moving an entire data file is generally much faster than exporting and importing individual rows or even blocks of data. However, traditional transportable tablespaces can require a fairly complicated set of steps to move user and application metadata needed to effectively use these tablespace data files inthe destination database. A migration using transportable tablespaces can therefore be characterized as being very fast but more complex.

2. At the same time, full transportable export/import uses the transportable tablespaces mechanism to move user and application data. This results in a migration that is very fast, even for very large volumes of data. Most important, full transportable export/import moves all of the system, user, and

application metadata needed for a database migration, without the complex set of steps required for a traditional transportable tablespaces operation.

Thus, full transportable export/import combines the ease of use of Oracle Data Pump with the performance of transportable tablespaces, resulting in a feature that makes database migration faster and easier.

Example.

Transporting full database using transportable option in expdp.

DBname : source

Source :-  11.2.0.3 (RHEL x86 64 bit)          - Little Endian

Target :-  12.1.0.2 (Solaris 10 x86 64 bit)    - Little Endian

If it is different endian then you need to use dbms_file_transfer or rman

convert to convert the endian format.

a)  Create a Skeleton database in 12c (Make sure to create with same character set as source db).

b)  Changed the default permanent tablespace name in the skelton database, because we alreay have users tablespace in the source db.

SQL> alter tablespace users rename to users1;

Tablespace altered.

SQL> alter tablespace users1 offline;

Tablespace altered.

SQL> ! mv users01.dbf users1_01.dbf

SQL> ! ls -lrt

total 3411040

-rw-r-----   1 oracle   oinstall 52429312 Oct 29 08:46 redo02.log

-rw-r-----   1 oracle   oinstall 52429312 Oct 29 08:46 redo03.log

-rw-r-----   1 oracle   oinstall 62922752 Oct 29 08:47 temp01.dbf

-rw-r-----   1 oracle   oinstall 629153792 Oct 29 09:42 sysaux01.dbf

-rw-r-----   1 oracle   oinstall 62922752 Oct 29 09:42 undotbs01.dbf

-rw-r-----   1 oracle   oinstall 817897472 Oct 29 09:42 system01.dbf

-rw-r-----   1 oracle   oinstall 5251072 Oct 29 09:44 users1_01.dbf

-rw-r-----   1 oracle   oinstall 52429312 Oct 29 09:44 redo01.log

-rw-r-----   1 oracle   oinstall 10043392 Oct 29 09:44 control01.ctl

SQL> alter database rename file '/opt/oracle/oradata/source/users01.dbf' to '/opt/oracle/oradata/source/users1_01.dbf';

Database altered.

SQL> alter tablespace users1 online;

Tablespace altered.

c)  Check the tablespace in the source db (11g)

SQL> select name from V$tablespace;

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

ACTIVE_DATA

IN_ACTIVE_DATA

User tablespace are (USERS / ACTIVE_DATA / IN_ACTIVE_DATA)

SQL> select name from V$datafile;

NAME

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

/opt/oracle/oradata/source/system01.dbf

/opt/oracle/oradata/source/sysaux01.dbf

/opt/oracle/oradata/source/undotbs01.dbf

/opt/oracle/oradata/source/users01.dbf

/opt/oracle/oradata/source/active_Data01.dbf

/opt/oracle/oradata/source/in_active_data01.dbf

6 rows selected.

Check whether the tablespace are self contained.

SQL> exec dbms_tts.transport_set_check('USERS,IN_ACTIVE_DATA,ACTIVE_DATA',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

Put these tablespace in read only mode.

SQL> alter tablespace USERS read only;

Tablespace altered.

SQL> alter tablespace ACTIVE_DATA read only;

Tablespace altered.

SQL> alter tablespace IN_ACTIVE_DATA read only;

Tablespace altered.

d)  Export the database using transportable parameter.

SQL> create directory exp_full as '/opt/oracle/oradata/source';

Directory created.

[oracle@rhel11gr2rac1 source]$ expdp directory=exp_full dumpfile=full_transortable.dmp version=12 full=y transportable=always logfile=full_trans.log

Export: Release 11.2.0.3.0 - Production on Thu Oct 29 09:00:33 2015

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

Username: / as sysdba

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 "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA directory=exp_full dumpfile=full_transortable.dmp version=12 full=y transportable=always logfile=full_trans.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/TABLE_DATA

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: 63.43 MB

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . exported "XDB"."XDB$H_INDEX"                         1.089 MB     283 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 "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_FULL_01 is:

  /opt/oracle/oradata/source/full_transortable.dmp

******************************************************************************

Datafiles required for transportable tablespace ACTIVE_DATA:

  /opt/oracle/oradata/source/active_Data01.dbf

Datafiles required for transportable tablespace IN_ACTIVE_DATA:

  /opt/oracle/oradata/source/in_active_data01.dbf

Datafiles required for transportable tablespace USERS:

  /opt/oracle/oradata/source/users01.dbf

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 09:06:14

e)  Transfer the dumpfiles and datafiles to the target server.

[oracle@rhel11gr2rac1 source]$ scp / opt/oracle/oradata/source/full_transortable.dmp oracle@192.168.0.51:/opt/oracle/oradata/source/

Password:

full_transortable.dmp                                                                                                                 100%   98MB   7.0MB/s   00:14

[oracle@rhel11gr2rac1 source]$ scp /opt/oracle/oradata/source/active_Data01.dbf oracle@192.168.0.51:/opt/oracle/oradata/source/active_Data01.dbf                                                                                                         100%  100MB   6.3MB/s   00:16

[oracle@rhel11gr2rac1 source]$ scp /opt/oracle/oradata/source/in_active_data01.dbf oracle@192.168.0.51:/opt/oracle/oradata/source/

Password:

in_active_data01.dbf                                                                                                                  100%  100MB   6.3MB/s   00:16

[oracle@rhel11gr2rac1 source]$ scp /opt/oracle/oradata/source/users01.dbf oracle@192.168.0.51:/opt/oracle/oradata/source/

Password:

users01.dbf                                                                                                                           100% 5128KB   5.0MB/s   00:00

f)  Import on target side.

SQL> create directory imp_full as '/opt/oracle/oradata/source';

Directory created.

-bash-3.2$ impdp directory=imp_full dumpfile=full_transortable.dmp  full=y transport_datafiles='/opt/oracle/oradata/source/users01.dbf','/opt/oracle/oradata/source/active_Data01.dbf','/opt/oracle/oradata/source/in_active_data01.dbf' logfile=imp.log

Import: Release 12.1.0.2.0 - Production on Thu Oct 29 09:57:24 2015

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=imp_full dumpfile=full_transortable.dmp full=y transport_datafiles=/opt/oracle/oradata/source/users01.dbf,/opt/oracle/oradata/source/active_Data01.dbf,/opt/oracle/oradata/source/in_active_data01.dbf logfile=imp.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-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"TEMP" already exists

Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

----

----

....

Job "SYS"."SYS_IMPORT_FULL_01" completed with 10 error(s) at Thu Oct 29 10:12:50 2015 elapsed 0 00:15:20.

Verify the tablespace in target .

SQL> select TABLESPACE_NAME, status, CONTENTS from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS

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

SYSTEM                         ONLINE    PERMANENT

SYSAUX                         ONLINE    PERMANENT

UNDOTBS1                       ONLINE    UNDO

TEMP                           ONLINE    TEMPORARY

USERS1                         ONLINE    PERMANENT

ACTIVE_DATA                    ONLINE    PERMANENT

IN_ACTIVE_DATA                 ONLINE    PERMANENT

USERS                          ONLINE    PERMANENT

NAME                                                         STATUS

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

/opt/oracle/oradata/source/system01.dbf                      SYSTEM

/opt/oracle/oradata/source/in_active_data01.dbf              ONLINE

/opt/oracle/oradata/source/sysaux01.dbf                      ONLINE

/opt/oracle/oradata/source/undotbs01.dbf                     ONLINE

/opt/oracle/oradata/source/active_Data01.dbf                 ONLINE

/opt/oracle/oradata/source/users1_01.dbf                     ONLINE

/opt/oracle/oradata/source/users01.dbf                       ONLINE

Upgrade as well as migration is compelted.