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.

Resource Manager in CDB & PDB

Resource Manager in CDB & PDB

In CDB RM can manage resource on two levels.

CDB Level :  Can manage the workloads for multiple PDB’s that are contending for system and CDB resources.

PDB Level : Manage resource within the PDB.

PDB compete for resources: - CPU / Exadata I/O and Parallel Servers.

This is done by granting different shares according to the priority of the PDB’s. More shares can be

allocated for the important PDB’s.

No Consumer group nor shares can be defined for root container.

Eg:-

CDB running with two PDB , each pdb is allocated with 1 share.

PDB1 -> (Share = 1/2 ( 50% Guaranteed CPU), limited to 100% CPU )

PDB2 -> (Share = 1/2 ( 50% Guaranteed CPU), limited to 100% CPU )

Now another 2 PDBS are added then

PDB1 -> (Share = 1/4 ( 25% Guaranteed CPU), limited to 100% CPU )

PDB2 -> (Share = 1/4 ( 25% Guaranteed CPU), limited to 100% CPU )

PDB3 -> (Share = 1/4 ( 25% Guaranteed CPU), limited to 100% CPU )

PDB4 -> (Share = 1/4 ( 25% Guaranteed CPU), limited to 100% CPU )

Say PDB1 is very important and we have allocated 2 shares then total share = (2+1+1+1 =5)

PDB1 -> (Share = 2/5 ( 40% Guaranteed CPU), limited to 100% CPU )

PDB2 -> (Share = 1/5 ( 20% Guaranteed CPU), limited to 100% CPU )

PDB3 -> (Share = 1/5 ( 20% Guaranteed CPU), limited to 100% CPU )

PDB4 -> (Share = 1/5 ( 20% Guaranteed CPU), limited to 100% CPU )

The Maximum limits can be controlled using the below parameters

Utilization_limit     ( To control the maximum utilization of resource of a PDB)

Parallel_server_limit ( To override utilization_limit only for parallel servers)

Eg.

Utilization_limit     = 30  ( Then the maximum system resource used by this PDB will be 30%)

Parallel_server_limit = 50  ( If the parallel_server_target is set = 200 then this limit says

     that 50% of parallel servers to be used i.e. 100 )

These are the values corresponding to the default directives that is automatically added to your plan.

It can be changed using dbms_resource_manager package using update_cdb_default_directive procedure.

Eg.

PDB1 (2 Shares / utilization set to 50%,  parallel_server_limit set to 50% (parallel target set as 200) then

     -> (Share = 2/5 (40% Guaranteed CPU), limited to 50% CPU / ExaI/O, parallel server limited to 100)

PDB2 (1 Shares / utilization set to 30%,  parallel_server_limit set to 30% (parallel target set as 200) then

-> (Share = 1/5 (20% Guaranteed CPU), limited to 30% CPU / ExaI/O, parallel server limited to 60)

PDB3 (1 Shares / utilization set to 20%,  parallel_server_limit set to 20% (parallel target set as 200) then

-> (Share = 1/5 (20% Guaranteed CPU), limited to 20% CPU / ExaI/O, parallel server limited to 40)

PDB4 2 Shares / utilization set to 20%,  parallel_server_limit set to 20% (parallel target set as 200) then

-> (Share = 1/5 (20% Guaranteed CPU), limited to 20% CPU / ExaI/O, parallel server limited to 40)

Autotask allocation default is (-1 share) which is 20% of system resource ,utilization limit is 90

And parallel limit is 100%. We can change it accordingly.

Eg.

SQL> show con_name;

CON_NAME

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

CDB$ROOT

SQL> select name, open_mode from V$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB_PURCHASE                   READ WRITE

PDB_HR                         READ WRITE

SQL> exec dbms_resource_manager.CREATE_PENDING_AREA;

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.CREATE_CDB_PLAN (plan => 'PLAN_FOR_PDBS', comment => 'RM PLANS for PDBS');

PL/SQL procedure successfully completed.

SQL> begin

  2     dbms_resource_manager.CREATE_CDB_PLAN_DIRECTIVE

  3     (

  4     PLAN                    =>      'PLAN_FOR_PDBS',

  5     PLUGGABLE_DATABASE      =>      'PDB_PURCHASE',

  6     SHARES                  =>      4,

  7     UTILIZATION_LIMIT       =>      NULL,

  8     PARALLEL_SERVER_LIMIT   =>      NULL

  9     );

 10  end;

 11  /

PL/SQL procedure successfully completed.

SQL> begin

  2     dbms_resource_manager.CREATE_CDB_PLAN_DIRECTIVE

  3     (

  4     PLAN                    =>      'PLAN_FOR_PDBS',

  5     PLUGGABLE_DATABASE      =>      'PDB_HR',

  6     SHARES                  =>      1,

  7     UTILIZATION_LIMIT       =>      20,

  8     PARALLEL_SERVER_LIMIT   =>      20

  9     );

 10  end;

 11  /

PL/SQL procedure successfully completed.

           

SQL> exec dbms_resource_manager.VALIDATE_PENDING_AREA;

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.SUBMIT_PENDING_AREA;

PL/SQL procedure successfully completed.

SQL> alter system set resource_manager_plan = 'PLAN_FOR_PDBS';

System altered.

  

SQL> select PLAN, PLUGGABLE_DATABASE, SHARES, UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT from dba_cdb_rsrc_plan_directives;

PLAN                                     PLUGGABLE_DATABASE                           SHARES UTILIZATION_LIMIT PARALLEL_SERVER_LIMIT

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

DEFAULT_CDB_PLAN                         ORA$DEFAULT_PDB_DIRECTIVE                         1               100                   100

DEFAULT_CDB_PLAN                         ORA$AUTOTASK                                                       90                   100

DEFAULT_MAINTENANCE_PLAN                 ORA$DEFAULT_PDB_DIRECTIVE                         1               100                   100

DEFAULT_MAINTENANCE_PLAN                 ORA$AUTOTASK                                                       90                   100

ORA$INTERNAL_CDB_PLAN                    ORA$DEFAULT_PDB_DIRECTIVE

ORA$INTERNAL_CDB_PLAN                    ORA$AUTOTASK

ORA$QOS_CDB_PLAN                         ORA$DEFAULT_PDB_DIRECTIVE                         1               100                   100

ORA$QOS_CDB_PLAN                         ORA$AUTOTASK                                                       90                   100

PLAN_FOR_PDBS                            ORA$DEFAULT_PDB_DIRECTIVE                         1               100                   100

PLAN_FOR_PDBS                            ORA$AUTOTASK                                                       90                   100

PLAN_FOR_PDBS                            PDB_PURCHASE                                      4

PLAN_FOR_PDBS                            PDB_HR                                            1                20                    20

12 rows selected.

Lets test it..

Vi insert_pdb_purchase.sh

export ORACLE_HOME=/oracle/product/12.1.0.2/db_1

export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s manzoor/ahamed@pdb_purchase <<EOF > pdb_purchase_test.log

set timing on;

set time on;

begin

        for i in 1..100000 loop

                insert into emp values (i, dbms_random.string('U',30), 10000+i);

        end loop;

commit;

end;

/

exit

EOF

Save and close above script.

Vi insert_pdb_hr_test.sh

export ORACLE_HOME=/oracle/product/12.1.0.2/db_1

export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s manzoor/ahamed@pdb_purchase <<EOF > pdb_purchase_test.log

set timing on;

set time on;

begin

        for i in 1..100000 loop

                insert into emp values (i, dbms_random.string('U',30), 10000+i);

        end loop;

commit;

end;

/

exit

EOF

Save and close above script.

[oracle@rhel11gr2rac1 ~]$ cat insert_pdb_hr_test.sh

nohup /home/oracle/insert_pdb_purchase.sh &

nohup /home/oracle/insert_pdb_hr.sh &

nohup ./insert_pdb_hr_test.sh &

[oracle@rhel11gr2rac1 ~]$ cat pdb_purchase_test.log

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.06

[oracle@rhel11gr2rac1 ~]$ cat pdb_hr_test.log

PL/SQL procedure successfully completed.

Elapsed: 00:02:04.27

We could see that script is completed in 46 sec in pdb_purchase db, whereas in pdb_hr it took 2 mins and 4 secs

SQL> select con_id, sql_id, sql_text, (cpu_time/1000000)/executions "Avg CPU Time", (ELAPSED_TIME/1000000)/executions "Execution Time",   executions from V$sql where PARSING_SCHEMA_NAME = 'MANZOOR'

    CON_ID SQL_ID        SQL_TEXT                                           Avg CPU Time Execution Time EXECUTIONS

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

     3 049v557vkq39j begin  for i in 1..100000 loop   insert into emp v    25.007198      46.049448          1

                         alues (i, dbms_random.string('U',30), 10000+i);  e

                         nd loop; commit; end;

     4 049v557vkq39j begin  for i in 1..100000 loop   insert into emp v    24.877219     124.257585          1

                         alues (i, dbms_random.string('U',30), 10000+i);  e

                         nd loop; commit; end;

As from the query you can see that the execution time take much longer in container 4 which is pdb_hr.

    CON_ID        SID    SERIAL# PROGRAM                                          EVENT

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

         4         59       2970 sqlplus@rhel11gr2rac1.manzoor.com (TNS V1-V3)    resmgr:cpu quantum

         3         85        143 sqlplus@rhel11gr2rac1.manzoor.com (TNS V1-V3)    db file sequential read

SQL> select con_id, plan_name, to_char(begin_time, 'HH:MI') time,

60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,

60 * (select value from v$parameter where name = 'cpu_count') db_total,

sum(cpu_consumed_time) / 1000 consumed,

sum(cpu_wait_time) / 1000 throttled

from gv$rsrcmgrmetric_history

group by con_id, plan_name, begin_time order by begin_time;

    CON_ID PLAN_NAME                      TIME       TOTAL   DB_TOTAL   CONSUMED  THROTTLED

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

        1 PLAN_FOR_PDBS                  09:10         60         60       .081          0

         2                                09:10         60         60          0          0

         3                                09:10         60         60     13.291       .987

         4                                09:10         60         60     11.069     48.875

         1 PLAN_FOR_PDBS                  09:11         60         60      1.322          0

         2                                09:11         60         60          0          0

         3                                09:11         60         60          0          0

         4                                09:11         60         60     10.159     38.797

         1 PLAN_FOR_PDBS                  09:12         60         60       .134          0

         2                                09:12         60         60          0          0

         3                                09:12         60         60          0          0

         4                                09:12         60         60          0          0

From the above metric we could see that container 4 is throttled and container 3 is not during the execution time.

Managing Resouce within PDB.

Its is same like managing resource in non-cdb except below.

Non-CBD                                         PDB

Multilevel resource plans                       Single level resource plan only

Upto 32 consumer groups                         upto 8 consumer groups

Subplans                                        No subplans