1. Migrating Data for Oracle Fusion Middleware Products from On-Premises to ATP-S Database
  2. User Privileges, Parameter File Contents, and Expdp and Impdp Commands for Excluded Objects

A User Privileges, Parameter File Contents, and Expdp and Impdp Commands for Excluded Objects

For objects that are excluded from the online DMS migration job and need to be exported and imported manually using offline expdp and impdp commands, you must know the user privileges and the contents of the parameter file of the excluded objects.

The following tables lists the privileges to be provided to users and the contents of the parameter file for excluded objects that need to be migrated manually outside of the online DMS tool, and the expdp and theimpdp commands that must be used for this manual migration.

User Privileges for Excluded Objects

Table A-1 lists the privileges to be provided to users when you need to manually migrate the excluded objects.

Note:

The following table lists the sample privileges to be provided to users for excluded objects. Based on your setup, provide the privileges to users for any additional schemas, if required.

Table A-1 User Privileges for Excluded Objects

Product User privileges for Excluded Objects

SOA/Oracle Business Process Management (BPM)

NA

SOA/Oracle Business Activity Monitoring (BAM)

A user that has been granted privileges, which are not supported by Oracle Autonomous Transaction Processing-Shared (ATP-S) database, should be revoked before migration, and equivalent privileges should be granted in the target database post migration.

Oracle Enterprise Scheduler (ESS) 

grant execute on DBMS_LOCK to <SCHEMA_OWNER>;
grant execute on UTL_FILE to <SCHEMA_OWNER>;
grant execute on UTL_RAW to <SCHEMA_OWNER>;
grant execute on DBMS_LOB to <SCHEMA_OWNER>;
grant execute on DBMS_SCHEDULER to <SCHEMA_OWNER>;
grant execute on DBMS_XMLDOM to <SCHEMA_OWNER>;
grant execute on DBMS_APPLICATION_INFO to <SCHEMA_OWNER>;
grant execute on DBMS_UTILITY to <SCHEMA_OWNER>;
grant execute on DBMS_SESSION to <SCHEMA_OWNER>;
grant execute on DBMS_OUTPUT to <SCHEMA_OWNER>;
grant execute on SYS.DBMS_ASSERT to <SCHEMA_OWNER>;
grant select on sys.v_$instance to <SCHEMA_OWNER>;
grant select on sys.gv_$instance to <SCHEMA_OWNER>;
grant select on sys.v_$session to <SCHEMA_OWNER>;
grant select on sys.gv_$session to <SCHEMA_OWNER>;
grant select on sys.v_$parameter to <SCHEMA_OWNER>;
grant create any job to <SCHEMA_OWNER>;
grant create job to <SCHEMA_OWNER>;
grant manage scheduler to <SCHEMA_OWNER>;
grant select on dba_scheduler_jobs to <SCHEMA_OWNER>;
grant select on dba_scheduler_job_run_details to <SCHEMA_OWNER>;
grant select on dba_scheduler_running_jobs to <SCHEMA_OWNER>;
grant select on dba_scheduler_job_classes to <SCHEMA_OWNER>;

Oracle Managed File Transfer (MFT)

NA

Oracle WebCenter Portal (WCP) 

grant read,write on directory <DPDIR> to <schema_prefix>_WEBCENTER <schema_prefix>_MDS;

Oracle WebCenter Content (WCC)

Excluded objects get generated dynamically

Oracle WebCenter Sites (WCS)

NA

Parameter File Contents for Excluded Objects

Table A-2 lists the tables that must be excluded from the online DMS migration job and migrated outside of DMS using Data pump commands. These tables are listed in the parameter file (parfile), which is passed as a parameter to the expdp command.

The INCLUDE parameter specifies the tables to be included in the expdp command.

Note:

The following table provides sample tables listed in the parameter file. Based on your setup, provide any additional tables, if required.

Table A-2 Parameter File Contents for Excluded Objects

Product/Component Parameter File Contents

SOA/Oracle Business Process Management (BPM) 

INCLUDE=TABLE:"IN('MDS_PURGE_PATHS','ORASDPMAPPDEFRCVT1',
'ORASDPMENGINECMDT','ORASDPMENGINESNDT1','ORASDPMENGINERCVT1',
'ORASDPMWSRCVT1','ORASDPMDRIVERDEFSNDT1',
'ORASDPMENGINEPENDRCVQT','AIA_CAVSCALLBACKJMSQTAB',
'IP_QTAB','AQ$_IP_QTAB_S','AQ$_IP_QTAB_T',
'AQ$_IP_QTAB_H','AQ$_IP_QTAB_L','AQ$_IP_QTAB_G',
'AQ$_IP_QTAB_I','AQ$_EDN_EVENT_QUEUE_TABLE_S',
'AQ$_EDN_EVENT_QUEUE_TABLE_T','AQ$_EDN_EVENT_QUEUE_TABLE_H',
'AQ$_EDN_EVENT_QUEUE_TABLE_L','AQ$_EDN_EVENT_QUEUE_TABLE_G',
'AQ$_EDN_EVENT_QUEUE_TABLE_I','AQ$_EDN_OAOO_DELIVERY_TABLE_S',
'AQ$_EDN_OAOO_DELIVERY_TABLE_T',
'AQ$_EDN_OAOO_DELIVERY_TABLE_H',
'AQ$_EDN_OAOO_DELIVERY_TABLE_L','AQ$_EDN_OAOO_DELIVERY_TABLE_G',
'AQ$_EDN_OAOO_DELIVERY_TABLE_I','AQ$_EDN_AQJMS_TOPIC_TABLE_S',
'AQ$_EDN_AQJMS_TOPIC_TABLE_T','AQ$_EDN_AQJMS_TOPIC_TABLE_H',
'AQ$_EDN_AQJMS_TOPIC_TABLE_L','AQ$_EDN_AQJMS_TOPIC_TABLE_G',
'AQ$_EDN_AQJMS_TOPIC_TABLE_I','SYS_IOT_OVER_78024',
'SYS_IOT_OVER_77858','SYS_IOT_OVER_77962','EDN_EVENT_QUEUE_TABLE',
'EDN_OAOO_DELIVERY_TABLE','SYS_IOT_OVER_77991',
'EDN_AQJMS_TOPIC_TABLE','TASK_NOTIFICATION_Q_T',
'RUPD$_MFT_SOURCE_MESSAGE','RUPD$_MFT_TRANSFER_INSTANCE',
'RUPD$_MFT_TARGET_INSTANCE','RUPD$_MFT_TARGET_MESSAGE',
'RUPD$_MFT_DATA_STORAGE','OSB_FTP_TRANSPORT_TBL',
'OSB_SFTP_TRANSPORT_TBL','OSB_EMAIL_TRANSPORT_TBL',
'OSB_FILE_TRANSPORT_TBL','OSB_REPORTING_TBL',
'OSB_REPORTING_ERROR_TBL','OSB_PURGE_TBL','TEMP_FLOWID_PURGE_GLB',
'TEMP_CUBE_INSTANCE_GLB','TEMP_DOCUMENT_CI_REF_GLB',
'TEMP_DOCUMENT_DLV_MSG_REF_GLB','TEMP_BRDECISION_INSTANCE_GLB',
'TEMP_WFTASK_PURGE_GLB','TEMP_MEDIATOR_DEFERRED_GLB',
'TEMP_MEDIATOR_RESEQUENCER_GLB','UPGRADE_CURRENT_SEQUENCE_TEMP',
'TEMP_UPGRADE_ECID','TEMP_UPGRADE_CI','TEMP_UPGRADE_DLV_MSG',
'TEMP_UPGRADE_DLV_ECID_MAP','TEMP_UPGRADE_WKITM_CI',
'TEMP_UPGRADE_WFTASK','TEMP_UPGRADE_BRDECISION',
'TEMP_UPGRADE_MI','TEMP_UPGRADE_MCI','TEMP_UPGRADE_MCDV',
'TEMP_UPGRADE_CPST_INST','B2B_BAM_QTAB','CLUSTER_NODE')",PROCOBJ

SOA/Oracle Business Activity Monitoring (BAM) 

INCLUDE=TABLE:"IN('MDS_PURGE_PATHS','ORASDPMAPPDEFRCVT1',
'ORASDPMENGINECMDT','ORASDPMENGINESNDT1','ORASDPMENGINERCVT1',
'ORASDPMWSRCVT1','ORASDPMDRIVERDEFSNDT1',
'ORASDPMENGINEPENDRCVQT','AIA_CAVSCALLBACKJMSQTAB',
'IP_QTAB','AQ$_IP_QTAB_S','AQ$_IP_QTAB_T',
'AQ$_IP_QTAB_H','AQ$_IP_QTAB_L','AQ$_IP_QTAB_G',
'AQ$_IP_QTAB_I','AQ$_EDN_EVENT_QUEUE_TABLE_S',
'AQ$_EDN_EVENT_QUEUE_TABLE_T','AQ$_EDN_EVENT_QUEUE_TABLE_H',
'AQ$_EDN_EVENT_QUEUE_TABLE_L','AQ$_EDN_EVENT_QUEUE_TABLE_G',
'AQ$_EDN_EVENT_QUEUE_TABLE_I','AQ$_EDN_OAOO_DELIVERY_TABLE_S',
'AQ$_EDN_OAOO_DELIVERY_TABLE_T',
'AQ$_EDN_OAOO_DELIVERY_TABLE_H',
'AQ$_EDN_OAOO_DELIVERY_TABLE_L','AQ$_EDN_OAOO_DELIVERY_TABLE_G',
'AQ$_EDN_OAOO_DELIVERY_TABLE_I','AQ$_EDN_AQJMS_TOPIC_TABLE_S',
'AQ$_EDN_AQJMS_TOPIC_TABLE_T','AQ$_EDN_AQJMS_TOPIC_TABLE_H',
'AQ$_EDN_AQJMS_TOPIC_TABLE_L','AQ$_EDN_AQJMS_TOPIC_TABLE_G',
'AQ$_EDN_AQJMS_TOPIC_TABLE_I','SYS_IOT_OVER_78024',
'SYS_IOT_OVER_77858','SYS_IOT_OVER_77962','EDN_EVENT_QUEUE_TABLE',
'EDN_OAOO_DELIVERY_TABLE','SYS_IOT_OVER_77991',
'EDN_AQJMS_TOPIC_TABLE','TASK_NOTIFICATION_Q_T',
'RUPD$_MFT_SOURCE_MESSAGE','RUPD$_MFT_TRANSFER_INSTANCE',
'RUPD$_MFT_TARGET_INSTANCE','RUPD$_MFT_TARGET_MESSAGE',
'RUPD$_MFT_DATA_STORAGE','OSB_FTP_TRANSPORT_TBL',
'OSB_SFTP_TRANSPORT_TBL','OSB_EMAIL_TRANSPORT_TBL',
'OSB_FILE_TRANSPORT_TBL','OSB_REPORTING_TBL',
'OSB_REPORTING_ERROR_TBL','OSB_PURGE_TBL','TEMP_FLOWID_PURGE_GLB',
'TEMP_CUBE_INSTANCE_GLB','TEMP_DOCUMENT_CI_REF_GLB',
'TEMP_DOCUMENT_DLV_MSG_REF_GLB','TEMP_BRDECISION_INSTANCE_GLB',
'TEMP_WFTASK_PURGE_GLB','TEMP_MEDIATOR_DEFERRED_GLB',
'TEMP_MEDIATOR_RESEQUENCER_GLB','UPGRADE_CURRENT_SEQUENCE_TEMP',
'TEMP_UPGRADE_ECID','TEMP_UPGRADE_CI','TEMP_UPGRADE_DLV_MSG',
'TEMP_UPGRADE_DLV_ECID_MAP','TEMP_UPGRADE_WKITM_CI',
'TEMP_UPGRADE_WFTASK','TEMP_UPGRADE_BRDECISION',
'TEMP_UPGRADE_MI','TEMP_UPGRADE_MCI','TEMP_UPGRADE_MCDV',
'TEMP_UPGRADE_CPST_INST','B2B_BAM_QTAB','CLUSTER_NODE')",PROCOBJ

Oracle Enterprise Scheduler (ESS) 

INCLUDE=TABLE:"IN('ESS_TEMP_REQID')"

If the domain contains SOA, SOA excluded tables must also be included

Oracle Managed File Transfer (MFT) 

INCLUDE=TABLE:"IN('RUPD$_MFT_SOURCE_MESSAGE',
'RUPD$_MFT_TRANSFER_INSTANCE',
'RUPD$_MFT_TARGET_INSTANCE',
'RUPD$_MFT_TARGET_MESSAGE',
'RUPD$_MFT_DATA_STORAGE')"

Oracle WebCenter Portal (WCP) 

INCLUDE=TABLE:"IN (select table_name from user_tables 
where table_name='WC_AS_ARCHIVE_TMP' and temporary = 'Y')"

Oracle WebCenter Content (WCC)

NA

Oracle WebCenter Sites (WCS)

NA

Metadata Services (MDS)

 
INCLUDE=TABLE:"IN (select table_name from user_tables where table_name='MDS_PURGE_PATHS' and temporary = 'Y')"

Expdp and Impdp Commands for Excluded Objects

Table A-3 lists the expdp and impdp commands to manually migrate the excluded objects listed in Table A-2

Note:

The following table lists the sample expdp and impdp commands to manually migrate the excluded objects. For any additional schemas in your setup, update the commands, if required.

Table A-3 Expdp and Impdp Commands for Excluded Objects

Product/Component Expdp and Impdp Commands

SOA/Oracle Business Process Management (BPM) 

./expdp ggadmin/<ggadmin_password>@<PDB_connect_string> dumpfile=dump_tables.dmp 
logfile=<logfilename>.log directory=<DPDIR> full=y parfile=<parfilename>.par
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=def_cred_name 
dumpfile=<dump_file_cloud_object_storage_location>.dmp 
REMAP_TABLESPACE=PKBPM1_IAS_OPSS:DATA 
REMAP_TABLESPACE=PKBPM1_IAS_UMS:DATA 
REMAP_TABLESPACE=PKBPM1_IAU:DATA 
REMAP_TABLESPACE=PKBPM1_MDS:DATA 
REMAP_TABLESPACE=PKBPM1_SOAINFRA:DATA 
REMAP_TABLESPACE=PKBPM1_STB:DATA 
REMAP_TABLESPACE=PKBPM1_WLS:DATA 
REMAP_TABLESPACE=PKBPM1_IAS_TEMP:TEMP

SOA/Oracle Business Activity Monitoring (BAM) 

./expdp ggadmin/<ggadmin_password>@<PDB_connect_string> dumpfile=<dumpfilename>.dmp 
logfile=<logfilename>.log directory=<DPDIR> full=y parfile=<parfilename>.par
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=def_cred_name 
dumpfile=<dump_file_cloud_object_storage_location>.dmp 
REMAP_TABLESPACE=PKBPM1_IAS_OPSS:DATA 
REMAP_TABLESPACE=PKBPM1_IAS_UMS:DATA 
REMAP_TABLESPACE=PKBPM1_IAU:DATA 
REMAP_TABLESPACE=PKBPM1_MDS:DATA 
REMAP_TABLESPACE=PKBPM1_SOAINFRA:DATA 
REMAP_TABLESPACE=PKBPM1_STB:DATA 
REMAP_TABLESPACE=PKBPM1_WLS:DATA 
REMAP_TABLESPACE=PKBPM1_IAS_TEMP:TEMP

Oracle Enterprise Scheduler (ESS) 

./expdp ggadmin/<ggadmin_password>@<PDB_connect_string> dumpfile=<dumpfilename>.dmp 
logfile=<logfilename>.log directory=<DPDIR> full=y parfile=<parfilename>.par
For PROCOBJ objects, use the following expdp command
./expdp sys/<sys_password>@<PDB_connect_string> as sysdba dumpfile=<procobjObject_dumpfilename>.dmp 
logfile=<procobjObject_logfilename>.log directory=<DPDIR> full=y INCLUDE=PROCOBJ INCLUDE=GRANT INCLUDE=ROLE_GRANT
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=def_cred_name 
dumpfile=<dump_file_cloud_object_storage_location>.dmp 
REMAP_TABLESPACE=PKESS1_ESS:DATA 
REMAP_TABLESPACE=PKESS1_IAS_OPSS:DATA 
REMAP_TABLESPACE=PKESS1_IAS_UMS:DATA 
REMAP_TABLESPACE=PKESS1_IAU:DATA 
REMAP_TABLESPACE=PKESS1_MDS:DATA 
REMAP_TABLESPACE=PKESS1_SOAINFRA:DATA 
REMAP_TABLESPACE=PKESS1_STB:DATA 
REMAP_TABLESPACE=PKESS1_WLS:DATA 
REMAP_TABLESPACE=PKESS1_IAS_TEMP:TEMP
For PROCOBJ objects, use the following impdp command
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=def_cred_name 
dumpfile=<dump_file_cloud_object_storage_location_procobjObject>.dmp

Oracle Managed File Transfer (MFT) 

./expdp ggadmin/<ggadmin_password>@<PDB_connect_string> dumpfile=<dumpfilename>.dmp 
logfile=<logfilename>.log directory=<DPDIR> parfile=<parfilename>.par
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=def_cred_name 
dumpfile=<dump_file_cloud_object_storage_location>.dmp 
REMAP_TABLESPACE=PKMFT1_ESS:DATA 
REMAP_TABLESPACE=PKMFT1_IAS_OPSS:DATA 
REMAP_TABLESPACE=PKMFT1_IAS_UMS:DATA 
REMAP_TABLESPACE=PKMFT1_IAU:DATA 
REMAP_TABLESPACE=PKMFT1_MDS:DATA 
REMAP_TABLESPACE=PKMFT1_MFT:DATA 
REMAP_TABLESPACE=PKMFT1_STB:DATA 
REMAP_TABLESPACE=PKMFT1_WLS:DATA 
REMAP_TABLESPACE=PKMFT1_IAS_TEMP:TEMP

Oracle WebCenter Portal (WCP) 

./expdp <schema_prefix>_Webcenter/<Webcenter_schema_password>@<PDB_connect_string> directory=<DPDIR> 
dumpfile=<dumpfilename>.dmp logfile=<logfilename>.log parfile=<parfilename>.par
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=DEF_CRED_NAME 
dumpfile=<dump_file_cloud_object_storage_location>.dmp

Oracle WebCenter Content (WCC)

NA

Oracle WebCenter Sites (WCS)

NA

Metadata Services (MDS)

 
./expdp <schema_prefix>_MDS/<MDS_schema_password>@<PDB_connect_string> directory=<DPDIR>
dumpfile=<dumpfilename>.dmp logfile=<logfilename>.log parfile=<parfilename>.par
./impdp admin/<admin_password_for_ATP-S_DB>@<ATP_databasename> credential=DEF_CRED_NAME
dumpfile=<dump_file_cloud_object_storage_location>.dmp