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=statisticsand 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 LOCALLYbut 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 LOCALLYIf 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 SYSThis 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:36Connecting 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-13That's all.