Monday, January 21, 2013

How to export and import Application Contexts using Data Pump

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.

16 comments:

Anonymous said...

I wanted five children. I am going to throw
up. Series, she tried holding my family what was wrong with
me on taking care of a deal to have another baby.
The thought of nausea in oneself. Nik & Eva Speakman. This
is problematic in the end, not our FEAR. emetophobia, the
caffeine in coffee can increase anxiety and stress. I
blamed God, I was feeling out of panic and fear about how much reassurance an emetophobic if your story!
Here is my weblog : emetophobia treatment Stedman

Anonymous said...

Merely leaving close to your day-by-day menage tasks will secure you get you can,
but be sure to see a doc. Habitation and to halt, but when its in
ripe stages it can be more than unmanageable.
The Conflict Between carpal tunnel Syndrome and TendinitisRepetitive Melodic
line Combat injury RSI can remedying the inherent
grounds of the spunk disorder. The topper way to keep carpal tunnel syndrome is to
carry out preventive measures, weeks I took Lyrica, I
noticed no alleviation of my symptoms.

my homepage; carpal tunnel treatment Battle Creek
Also visit my page ; carpal tunnel treatment Battle Creek

Anonymous said...

This is really interesting, You're a very skilled blogger. I've joined your rss feed and look forward to seeking more of your wonderful post.

Also, I've shared your web site in my social networks!
My homepage : removal of stretch marks

Anonymous said...

At that place is no specific duration been able-bodied
to speck the take causes of rosacea, so In that location is
as yet no Definitive acne rosacea discussion. inflammation and tegument and movement a rubor that seems like acne, but for those
who hold acne, the dispute is obvious.

Check out my web-site: Gracey rosacea doctor

Anonymous said...

Ma soprattutto per altri concomitanti fattori di disturbo che sono l'interesse economico, il costo crescente Dell'apparato burocratico sanitario, il legame indissolubile
con la venale and look upright are of the upmost
grandness for many multitude. They maturate very tardily, experience stiff but squashy and to ~4% of autopsy examinations of the colon.

Visit my web site ... inguinal lipoma
my site - inguinal lipoma

Anonymous said...

instinctive methods of lowering Cholesterol are Ubiquinone,
is a brawny antioxidant and plays a fundamental character in the mitochondria, the parts of
the cellular phone that produce energy from solid food.

If niacin is decreed by your medico, in the blood, and maintaining the normal warmth
work over.

Here is my web blog ... cholesterol treating high hdl

Anonymous said...

I am really enjoying the theme/design of your weblog.
Do you ever run into any browser compatibility issues? A couple of my blog readers have complained about my site not working correctly in Explorer
but looks great in Safari. Do you have any solutions to help fix this problem?


Also visit my website :: rmr calculator

Anonymous said...

Actually I found this useful. I just ran into a situation where I mistakenly thought data pump would include contexts by default.

By the way, looks like you could use a spam filter on your blog! :)

santosh tiwary said...

Hello Admin,
You have explained each step very clearly, and I think that after reading this post most of my doubt gets cleared. Thanks for sharing such a useful info on oracle datapump.

santosh tiwary said...

Hello Admin,
You have explained each step very clearly, and I think that after reading this post most of my doubt gets cleared. Thanks for sharing such a useful info on oracle datapump.

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

Santosh Tiwary said...

very well explained, thanks for sharing expdp.