How to export and import Application Contexts using Data Pump

Tweet

You have to export some schemas used by your latest application:
this new application restricts access by a customer to its own user's data based on a set of name-value pairs that Oracle Database stores in memory, the so called "Application Context".

You have already completed the export and import operations, but you do not find on production database any application context.
This is the export command you executed...

[oracle@vsi03dev log]$ expdp system dumpfile=3schema.dmp schemas=schema_q,schema_t,schema_v exclude=statistics

and the following is the import command:

[oracle@qdb01frm dump]$ impdp system dumpfile=3schema.dmp remap_tablespace=tsd_schema:tsd_schema_new,tsi_schema:tsi_schema_new

... but looking at the export log you discover the application contexts are not exported by Data Pump.
Why ?
On my development database I can see the following application contexts:

[oracle@vsi03dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 19 21:45:58 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 999 
SQL> set linesize 180
SQL> select * from dba_context;

NAMESPACE         SCHEMA         PACKAGE        TYPE
------------------------------ ------------------------------ ------------------------------ ----------------------
REGISTRY$CTX         SYS         DBMS_REGISTRY_SYS       ACCESSED LOCALLY
LT_CTX          WMSYS         LT_CTX_PKG       ACCESSED LOCALLY
DR$APPCTX         CTXSYS         DRIXMD        ACCESSED LOCALLY
EM_GLOBAL_CONTEXT        SYSMAN         SETEMUSERCONTEXT       ACCESSED GLOBALLY
EM_USER_CONTEXT         SYSMAN         SETEMUSERCONTEXT       ACCESSED LOCALLY
SCHEMA_T_USER_CONTEXT        SCHEMA_T               PKG_SCHEMA_T_CONTEXT       ACCESSED LOCALLY

but after the import on my production database I'm not able to locate them:

[oracle@qdb01frm ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 19 21:44:18 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 180
SQL> set pagesize 999
SQL> select * from dba_context;

NAMESPACE         SCHEMA         PACKAGE        TYPE
------------------------------ ------------------------------ ------------------------------ ----------------------
REGISTRY$CTX         SYS         DBMS_REGISTRY_SYS       ACCESSED LOCALLY
LT_CTX          WMSYS         LT_CTX_PKG       ACCESSED LOCALLY
DR$APPCTX         CTXSYS         DRIXMD        ACCESSED LOCALLY
EM_GLOBAL_CONTEXT        SYSMAN         SETEMUSERCONTEXT       ACCESSED GLOBALLY
EM_USER_CONTEXT         SYSMAN         SETEMUSERCONTEXT       ACCESSED LOCALLY

If you query the dba_object view you can discover SCHEMA_T_USER_CONTEXT is not owned by those schema, but it is owned by SYS

SQL> set linesize 180
SQL> set pagesize 999
SQL> column object_name format a30
SQL> select object_name, owner from dba_objects where object_name = 'SCHEMA_T_USER_CONTEXT';

OBJECT_NAME         OWNER
------------------------------ ------------------------------
SCHEMA_T_USER_CONTEXT          SYS

This is a good starting point to understand why the application context are not exported by DataPump.
SCHEMA_T_USER_CONTEXT is not part of exported schemas so it won't be considered during our export and import SCHEMA operations.
To successfully import an application context you have to create and specify a different data pump job using the full database option.
In my case I specify the following command during export operation ...

[oracle@vsi03dev log]$ expdp system dumpfile=context.dmp full=y include=context
;;;
Export: Release 10.2.0.5.0 - 64bit Production on Friday, 07 December, 2012 15:31:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** dumpfile=context.dmp full=y include=context
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/CONTEXT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /opt/app/oracle/product/10.2.0/db_1/rdbms/log/context.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:31:53

... and this command to complete import data pump job of application context (after you have copied context.dmp file from development to production machine):

[oracle@qdb01frm dump]$ impdp system dumpfile=context.dmp include=context full=y
;;;
Import: Release 10.2.0.4.0 - 64bit Production on Monday, 21 January, 2013 8:32:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=context.dmp include=context full=y
Processing object type DATABASE_EXPORT/CONTEXT
ORA-31684: Object type CONTEXT:"REGISTRY$CTX" already exists
ORA-31684: Object type CONTEXT:"LT_CTX" already exists
ORA-31684: Object type CONTEXT:"DR$APPCTX" already exists
ORA-31684: Object type CONTEXT:"EM_GLOBAL_CONTEXT" already exists
ORA-31684: Object type CONTEXT:"EM_USER_CONTEXT" already exists
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 08:32:36

Connecting to the database you can view your application context is now ready to be used:

[oracle@qdb01frm dump]$ sqlplus / as sysdba
SQL> set linesize 180
SQL> col namespace format a25
SQL> col schema format a10
SQL> col package format a25
SQL> SELECT C.*, O.LAST_DDL_TIME, O.CREATED
  2  FROM   SYS.DBA_CONTEXT C, SYS.DBA_OBJECTS O
  3  WHERE  C.NAMESPACE = O.OBJECT_NAME
  4  AND    O.OWNER = 'SYS'
  5  AND    O.OBJECT_TYPE = 'CONTEXT'
  6  AND    O.OBJECT_NAME = 'SCHEMA_T_USER_CONTEXT'
  7  AND    C.SCHEMA = 'SCHEMA_T';

NAMESPACE           SCHEMA     PACKAGE         TYPE        LAST_DDL_ CREATED
------------------------- ---------- ------------------------- ---------------------- --------- ---------
SCHEMA_T_USER_CONTEXT     SCHEMA_T   PKG_SCHEMA_T_CONTEXT      ACCESSED LOCALLY       21-JAN-13 21-JAN-13

That's all.