1. Administering Oracle SOA Suite and Oracle Business Process Management Suite
  2. Migrating from a Non-Container Database (Non-CDB) to a Container Database (CDB)
  3. Exporting from the Source Database

Exporting from the Source Database

To export from the source non-container database (non-CDB), complete the following steps:

Export INFRA Schemas

To export INFRA schemas from the source database:

  1. Sign in to the source environment. For example, soa-host01.example.com.
  2. Change to the oracle user:
    sudo su - oracle
  3. Create a directory to store the exported files. For example:
    mkdir -p /u01/db_exp/INFRA_EXPORTS
cd /u01/db_exp/INFRA_EXPORTS
  4. Set the ORACLE_HOME, ORACLE, and PATH environment variables. For example:
    export ORACLE_HOME=/opt/oracle/product/12.1.0.2/db_1
export ORACLE_SID=orclsoa
export PATH=$PATH:$ORACLE_HOME/bin
  5. Change to the sys user, or any user with sysdba privileges, and connect to the source database:
    sqlplus "/ as sysdba"
  6. Create the export directory. For example:
    CREATE DIRECTORY DUMP_INFRA AS '/u01/db_exp/INFRA_EXPORTS';
  7. Check if the export directory was properly created:
    SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_INFRA';
  8. Grant READ and WRITE permissions to the respective schemas:
    GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_IAU;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_IAU_APPEND;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_IAU_VIEWER;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_MDS;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_OPSS;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_STB;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_WLS;
GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_WLS_RUNTIME;
  9. Verify that there are no connections from the INFRA users above in the database:
    SELECT username FROM v$session WHERE username IS NOT NULL ORDER BY username ASC;
quit
  10. Change to the oracle user.
  11. In the INFRA_EXPORTS directory, use a text editor to create a bash script to run the Data Pump Export utility (expdp) on all schemas. For example, create a script named export_infra_schema.sh with the following contents:
    expdp DEV_IAU/password schemas=DEV_IAU directory=DUMP_INFRA dumpfile=DEV_IAU_export.dmp logfile=DEV_IAU_export.log PARALLEL=4
expdp DEV_IAU_APPEND/password schemas=DEV_IAU_APPEND directory=DUMP_INFRA dumpfile=DEV_IAU_APPEND_export.dmp logfile=DEV_IAU_APPEND_export.log PARALLEL=4
expdp DEV_IAU_VIEWER/password schemas=DEV_IAU_VIEWER directory=DUMP_INFRA dumpfile=DEV_IAU_VIEWER_export.dmp logfile=DEV_IAU_VIEWER_export.log PARALLEL=4
expdp DEV_MDS/password schemas=DEV_MDS directory=DUMP_INFRA dumpfile=DEV_MDS_export.dmp logfile=DEV_MDS_export.log PARALLEL=4
expdp DEV_OPSS/password schemas=DEV_OPSS directory=DUMP_INFRA dumpfile=DEV_OPSS_export.dmp logfile=DEV_OPSS_export.log PARALLEL=4
expdp DEV_STB/password schemas=DEV_STB directory=DUMP_INFRA dumpfile=DEV_STB_export.dmp logfile=DEV_STB_export.log PARALLEL=4
expdp DEV_WLS/password schemas=DEV_WLS directory=DUMP_INFRA dumpfile=DEV_WLS_export.dmp logfile=DEV_WLS_export.log PARALLEL=4
expdp DEV_WLS_RUNTIME/password schemas=DEV_WLS_RUNTIME directory=DUMP_INFRA dumpfile=DEV_WLS_RUNTIME_export.dmp logfile=DEV_WLS_RUNTIME_export.log PARALLEL=4
  12. As the sys user, run the script in the source environment:
    @export_infra_schema.sh
quit
  13. Export the SCHEMA_VERSION_REGISTRY view and its underlying tables from the SYSTEM schema:
    expdp \"/ as sysdba\" SCHEMAS=system INCLUDE=VIEW:"IN('SCHEMA_VERSION_REGISTRY')" TABLE:"IN('SCHEMA_VERSION_REGISTRY$')" directory=DUMP_INFRA 
dumpfile=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp logfile=SYSTEM_SCHEMA_VERSION_REGISTRY.log

    Note:

    If you encounter an error, make sure you are using a Bash shell to run this command
  14. In the INFRA_EXPORTS directory, use a text editor to create a script to export users. For example, create a script named export_users.sql with the following contents:
    set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
spool create_users.sql

SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_IAU') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_IAU_APPEND') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_IAU_VIEWER') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_MDS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_OPSS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_STB') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_WLS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_WLS_RUNTIME') FROM dual;

spool off
  15. As the sys user, run the script in the source environment:
    sqlplus sys/password as sysdba
@export_users.sql
quit
  16. Edit the output file (in this example, create_users.sql), and verify that it contains only lines with CREATE USER statements, then add a semicolon at the end of the CREATE statement. For example:
    CREATE USER "DEV_IAU" IDENTIFIED BY VALUES 
'S:7C9B0ECE4641D7409A0CBF67505591DD4E437FDDF7524FDBE61051FC3663;H:4A0E99B6CA492BF37AA2177A0D0043F3;T:093C0D3F6BA909A2B2A9077F4AC1B92B6CF59A5C6C6404B7E07CDAA652947C0593FB2EB3D7AC886D9F3164A5F98D25004E6C7A1F7277E2491F1AB7AC26AF4FEAA58561E489AB3CD17BACE090E17A2A52;38D2C7CEB79AC568'
   DEFAULT TABLESPACE "DEV_IAU"
   TEMPORARY TABLESPACE "DEV_IAS_TEMP";
  17. Enter the following commands to identify the tablespaces in the INFRA schemas:
    set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
select DISTINCT tablespace_name,owner from dba_segments WHERE OWNER like 'DEV\_%' ESCAPE '\';

    The command output should look similar to the following:

    Command output showing tablespaces.
  18. Enter the following command to identify the temporary tablespaces in the INFRA schemas:
    select DISTINCT temporary_tablespace from dba_users WHERE username like 'DEV\_%' ESCAPE '\';

    The command output should look similar to the following:

    Command output showing temporary tablespaces.
  19. In the INFRA_EXPORTS directory, use a text editor to create a script to export the tablespaces identified in the prior steps. For example, create a script named export_tablespaces.sql with the following contents:
    set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
spool create_tablespaces.sql

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_IAU') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_STB') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_MDS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_IAS_OPSS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_WLS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_IAS_TEMP') FROM DUAL;

spool off
  20. As the sys user, run the script in the source environment:
    sqlplus sys/password as sysdba
@export_tablespaces.sql
quit
  21. Edit the output file (in this example, create_tablespaces.sql), and verify that it contains only lines with CREATE TABLESPACE statements, then add a semicolon at the end of the CREATE statement. For example:
    CREATE USER "DEV_IAU" IDENTIFIED BY VALUES CREATE TABLESPACE "DEV_IAU" DATAFILE
 '/u01/app/oracle/oradata/CDB1/datafile/DEV_iau.dbf' SIZE 62914560
 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
  22. In the INFRA_EXPORTS directory, use a text editor to create a script to export roles and grants. For example, create a script named export_role_grant.sql with the following contents:
    set long 6000;
set longchunksize 6000;
set linesize 120;
spool create_role_grant.sql

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_IAU') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_IAU') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_IAU') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_IAU_APPEND') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_IAU_APPEND') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_IAU_APPEND') FROM dual;


SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_IAU_VIEWER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_IAU_VIEWER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_IAU_VIEWER') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_MDS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_MDS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_MDS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_OPSS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_OPSS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_OPSS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_STB') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_STB') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_STB') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_WLS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_WLS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_WLS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_WLS_RUNTIME') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_WLS_RUNTIME') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_WLS_RUNTIME') FROM dual;

spool off
  23. As the sys user, run the script in the source environment:
    sqlplus sys/password as sysdba
@export_role_grant.sql
quit
  24. Edit the output file (in this example, create_role_grant.sql), and verify that it contains only lines with GRANT statements. For example:
    GRANT "CONNECT" TO "DEV_IAU";
GRANT "RESOURCE" TO "DEV_IAU";
GRANT "SELECT_CATALOG_ROLE" TO "DEV_IAU";
...
  25. In the INFRA_EXPORTS directory, use a text editor to create a script to grant SELECT privileges to the SCHEMA_VERSION_REGISTRY table to all users. For example, create a script named create_select_grant_system.sql with the following contents:
    GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_IAU";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_IAU_APPEND";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_IAU_VIEWER";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_MDS";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_OPSS";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_STB";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_WLS";

    You will use this script later in Importing to the Target Database to validate the data.

Export SOA Schemas

To export SOA schemas from the source database:

  1. Sign in to the source environment. For example, soa-host01.example.com.
  2. Change to the oracle user:
    sudo su - oracle
  3. Create a directory to store the exported files. For example:
    mkdir -p /u01/db_exp/SOA_EXPORTS
cd /u01/db_exp/SOA_EXPORTS
  4. Set the ORACLE_HOME, ORACLE, and PATH environment variables. For example:
    export ORACLE_HOME=/opt/oracle/product/12.1.0.2/db_1
export ORACLE_SID=orclsoa
export PATH=$PATH:$ORACLE_HOME/bin
  5. Change to the sys user, or any user with sysdba privileges, and connect to the source database:
    sqlplus "/ as sysdba"
  6. Create the export directory. For example:
    CREATE DIRECTORY DUMP_SOA AS '/u01/db_exp/SOA_EXPORTS';
  7. Check if the export directory was successfully created:
    SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_SOA;
  8. Grant READ and WRITE permissions to the respective schemas:
    GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_IAU;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_IAU_APPEND;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_IAU_VIEWER;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_MDS;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_OPSS;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_SOAINFRA;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_STB;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_UMS;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_WLS;
GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_WLS_RUNTIME;
  9. Enter the following commands to verify if any SOA queues are stopped:
    sqlplus / as sysdba
connect DEV2_SOAINFRA;

set lines 200;
set pages 400;
COLUMN name format A25;
COLUMN enqueue_enabled format A15;
COLUMN dequeue_enabled format A15;

SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE';

NAME                      ENQUEUE_ENABLED DEQUEUE_ENABLED
------------------------- --------------- ---------------
B2B_BAM_QUEUE               YES             YES
OSB_REPORTING_PURGE         YES             YES
OSB_REPORTING_ERROR         YES             YES
OSB_REPORTING_PROVIDER      YES             YES
OSB_FILE_TRANSPORT          YES             YES
OSB_EMAIL_TRANSPORT         YES             YES
OSB_SFTP_TRANSPORT          YES             YES
OSB_FTP_TRANSPORT           YES             YES
TASK_NOTIFICATION_Q         YES             YES
EDN_AQJMS_TOPIC             YES             YES
EDN_OAOO_QUEUE              YES             YES
EDN_EVENT_QUEUE             YES             YES
IP_OUT_QUEUE                YES             YES
IP_IN_QUEUE                 YES             YES
AIA_CAVSCALLBACKJMSQ        YES             YES
  10. Enter the following commands to stop the SOA database queues and confirm they are stopped:
    sqlplus / as sysdba
connect DEV2_SOAINFRA;

set lines 200;
set pages 400;
COLUMN name format A25;
COLUMN enqueue_enabled format A15;
COLUMN dequeue_enabled format A15;

--Stop the SOA database queues :
BEGIN
DBMS_AQADM.STOP_QUEUE ('AIA_CAVSCALLBACKJMSQ');
DBMS_AQADM.STOP_QUEUE ('B2B_BAM_QUEUE');
DBMS_AQADM.STOP_QUEUE ('EDN_AQJMS_TOPIC');
DBMS_AQADM.STOP_QUEUE ('EDN_EVENT_QUEUE');
DBMS_AQADM.STOP_QUEUE ('EDN_OAOO_QUEUE');
DBMS_AQADM.STOP_QUEUE ('IP_IN_QUEUE');
DBMS_AQADM.STOP_QUEUE ('IP_OUT_QUEUE');
DBMS_AQADM.STOP_QUEUE ('OSB_EMAIL_TRANSPORT');
DBMS_AQADM.STOP_QUEUE ('OSB_FILE_TRANSPORT');
DBMS_AQADM.STOP_QUEUE ('OSB_FTP_TRANSPORT');
DBMS_AQADM.STOP_QUEUE ('OSB_REPORTING_ERROR');
DBMS_AQADM.STOP_QUEUE ('OSB_REPORTING_PROVIDER');
DBMS_AQADM.STOP_QUEUE ('OSB_REPORTING_PURGE');
DBMS_AQADM.STOP_QUEUE ('OSB_SFTP_TRANSPORT');
DBMS_AQADM.STOP_QUEUE ('TASK_NOTIFICATION_Q');
END;
/

SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE' order by name asc;

NAME                      ENQUEUE_ENABLED DEQUEUE_ENABLED
------------------------- --------------- ---------------
AIA_CAVSCALLBACKJMSQ        NO              NO
B2B_BAM_QUEUE               NO              NO
EDN_AQJMS_TOPIC             NO              NO
EDN_EVENT_QUEUE             NO              NO
EDN_OAOO_QUEUE              NO              NO
IP_IN_QUEUE                 NO              NO
IP_OUT_QUEUE                NO              NO
OSB_EMAIL_TRANSPORT         NO              NO
OSB_FILE_TRANSPORT          NO              NO
OSB_FTP_TRANSPORT           NO              NO
OSB_REPORTING_ERROR         NO              NO
OSB_REPORTING_PROVIDER      NO              NO
OSB_REPORTING_PURGE         NO              NO
OSB_SFTP_TRANSPORT          NO              NO
TASK_NOTIFICATION_Q         NO              NO
  11. Verify that there are no connections from the SOA users above in the database:
    SELECT username FROM v$session WHERE username IS NOT NULL ORDER BY username ASC;
quit

    If there are any connections, stop the SOA domain.

  12. Change to the oracle user.
  13. In the SOA_EXPORTS directory, use a text editor to create a bash script to run the Data Pump Export utility (expdp) on all schemas. For example, create a script named export_soa_schema.sh with the following contents:
    expdp DEV2_IAU/password schemas=DEV2_IAU directory=DUMP_SOA dumpfile=DEV2_IAU_export.dmp logfile=DEV2_IAU_export.log PARALLEL=4
expdp DEV2_IAU_APPEND/password schemas=DEV2_IAU_APPEND directory=DUMP_SOA dumpfile=DEV2_IAU_APPEND_export.dmp logfile=DEV2_IAU_APPEND_export.log PARALLEL=4
expdp DEV2_IAU_VIEWER/password schemas=DEV2_IAU_VIEWER directory=DUMP_SOA dumpfile=DEV2_IAU_VIEWER_export.dmp logfile=DEV2_IAU_VIEWER_export.log PARALLEL=4
expdp DEV2_MDS/password schemas=DEV2_MDS directory=DUMP_SOA dumpfile=DEV2_MDS_export.dmp logfile=DEV2_MDS_export.log PARALLEL=4
expdp DEV2_OPSS/password schemas=DEV2_OPSS directory=DUMP_SOA dumpfile=DEV2_OPSS_export.dmp logfile=DEV2_OPSS_export.log PARALLEL=4
expdp DEV2_SOAINFRA/password schemas=DEV2_SOAINFRA directory=DUMP_SOA dumpfile=DEV2_SOAINFRA_export.dmp logfile=DEV2_SOAINFRA_export.log PARALLEL=4
expdp DEV2_STB/password schemas=DEV2_STB directory=DUMP_SOA dumpfile=DEV2_STB_export.dmp logfile=DEV2_STB_export.log PARALLEL=4
expdp DEV2_UMS/password schemas=DEV2_UMS directory=DUMP_SOA dumpfile=DEV2_UMS_export.dmp logfile=DEV2_UMS_export.log PARALLEL=4
expdp DEV2_WLS/password schemas=DEV2_WLS directory=DUMP_SOA dumpfile=DEV2_WLS_export.dmp logfile=DEV2_WLS_export.log PARALLEL=4
expdp DEV2_WLS_RUNTIME/password schemas=DEV2_WLS_RUNTIME directory=DUMP_SOA dumpfile=DEV2_WLS_RUNTIME_export.dmp logfile=DEV2_WLS_RUNTIME_export.log PARALLEL=4
  14. As the sys user, run the script in the source environment:
    @export_soa_schema.sh
quit
  15. Export the SCHEMA_VERSION_REGISTRY view and its underlying tables from the SYSTEM schema:
    expdp \"/ as sysdba\" SCHEMAS=system INCLUDE=VIEW:"IN('SCHEMA_VERSION_REGISTRY')" TABLE:"IN('SCHEMA_VERSION_REGISTRY$')" directory=DUMP_SOA 
dumpfile=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp logfile=SYSTEM_SCHEMA_VERSION_REGISTRY.log

    Note:

    If you encounter an error, make sure you are using a Bash shell to run this command
  16. In the SOA_EXPORTS directory, use a text editor to create a script to export users. For example, create a script named export_users.sql with the following contents:
    set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
spool create_users.sql

SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_IAU') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_IAU_APPEND') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_IAU_VIEWER') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_MDS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_OPSS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_SOAINFRA') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_STB') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_UMS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_WLS') FROM dual;
SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_WLS_RUNTIME') FROM dual;

spool off
  17. Change to the sys user, and run the script in the source environment:
    sqlplus sys/password as sysdba
@export_users.sql
quit
  18. Edit the output file (in this example, create_users.sql), and verify that it contains only lines with CREATE USER statements, then add a semicolon at the end of the CREATE statement. For example:
    CREATE USER "DEV_IAU" IDENTIFIED BY VALUES 
'S:7C9B0ECE4641D7409A0CBF67505591DD4E437FDDF7524FDBE61051FC3663;H:4A0E99B6CA492BF37AA2177A0D0043F3;T:093C0D3F6BA909A2B2A9077F4AC1B92B6CF59A5C6C6404B7E07CDAA652947C0593FB2EB3D7AC886D9F3164A5F98D25004E6C7A1F7277E2491F1AB7AC26AF4FEAA58561E489AB3CD17BACE090E17A2A52;38D2C7CEB79AC568'
   DEFAULT TABLESPACE "DEV2_IAU"
   TEMPORARY TABLESPACE "DEV2_IAS_TEMP";
  19. Enter the following commands to identify the tablespaces in the SOA schema:
    set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
SELECT DISTINCT tablespace_name,owner from dba_segments WHERE OWNER like 'DEV2\_%' ESCAPE '\';

    The command output should look similar to the following:

    Command output showing tablespaces.
  20. Enter the following command to identify the temporary tablespaces in the SOA schemas:
    SELECT DISTINCT temporary_tablespace from dba_users WHERE username like 'DEV2\_%' ESCAPE '\';

    The command output should look similar to the following:

    Command output showing temporary tablespaces.
  21. In the SOA_EXPORTS directory, use a text editor to create to export the tablespaces identified in the prior steps. For example, create a script named export_tablespaces.sql with the following contents:
    set long 10000;
set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
spool create_tablespaces.sql

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAU') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_STB') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAS_UMS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_SOAINFRA') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_MDS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAS_OPSS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_WLS') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAS_TEMP') FROM DUAL;

spool off
  22. As the sys user, run the script in the source environment:
    sqlplus sys/password as sysdba
@export_tablespaces.sql
quit
  23. Edit the output file (in this example, create_tablespaces.sql), and verify that it contains only lines with CREATE TABLESPACE statements, then add a semicolon at the end of the CREATE statement. For example:
    CREATE TABLESPACE "DEV2_IAU" DATAFILE           
'/opt/oracle/oradata/DEV2_iau.dbf' SIZE 62914560
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M          
LOGGING ONLINE PERMANENT BLOCKSIZE 8192         
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT    
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
  24. In the SOA_EXPORTS directory, use a text editor to create a script to export roles and grants. For example, create a script named export_role_grant.sql with the following contents:
    set long 6000;
set longchunksize 6000;
set linesize 120;
spool create_role_grant.sql

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_IAU') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_IAU') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_IAU') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_IAU_APPEND') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_IAU_APPEND') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_IAU_APPEND') FROM dual;


SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_IAU_VIEWER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_IAU_VIEWER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_IAU_VIEWER') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_MDS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_MDS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_MDS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_OPSS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_OPSS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_OPSS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_SOAINFRA') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_SOAINFRA') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_SOAINFRA') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_STB') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_STB') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_STB') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_UMS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_UMS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_UMS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_WLS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_WLS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_WLS') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_WLS_RUNTIME') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_WLS_RUNTIME') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_WLS_RUNTIME') FROM dual;

spool off
  25. As the sys user, run the script in the source environment:
    sqlplus sys/password as sysdba
@export_role_grant.sql
quit
  26. Edit the output file (in this example, create_role_grant.sql), and verify that it contains only lines with GRANT statements. For example:
    GRANT "CONNECT" TO "DEV2_IAU";
GRANT "RESOURCE" TO "DEV2_IAU";
GRANT "SELECT_CATALOG_ROLE" TO "DEV2_IAU";
...
  27. In the SOA_EXPORTS directory, use a text editor to create a script to grant SELECT privileges to the SCHEMA_VERSION_REGISTRY table to all users. For example, create a script named create_select_grant_system.sql with the following contents:
    GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_IAU";
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_IAU_APPEND";    
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_IAU_VIEWER";        
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_MDS";        
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_OPSS";          
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_SOAINFRA";   
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_STB";     
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_UMS";             
GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_WLS";

    You will use this script later in Importing to the Target Database to validate the data.

  28. Enter the following commands to restart the SOA queues and confirm they are started:
    sqlplus / as sysdba
connect DEV2_SOAINFRA;

set lines 200;
set pages 400;
COLUMN name format A25;
COLUMN enqueue_enabled format A15;
COLUMN dequeue_enabled format A15;

BEGIN
DBMS_AQADM.START_QUEUE ('AIA_CAVSCALLBACKJMSQ');
DBMS_AQADM.START_QUEUE ('B2B_BAM_QUEUE');
DBMS_AQADM.START_QUEUE ('EDN_AQJMS_TOPIC');
DBMS_AQADM.START_QUEUE ('EDN_EVENT_QUEUE');
DBMS_AQADM.START_QUEUE ('EDN_OAOO_QUEUE');
DBMS_AQADM.START_QUEUE ('IP_IN_QUEUE');
DBMS_AQADM.START_QUEUE ('IP_OUT_QUEUE');
DBMS_AQADM.START_QUEUE ('OSB_EMAIL_TRANSPORT');
DBMS_AQADM.START_QUEUE ('OSB_FILE_TRANSPORT');
DBMS_AQADM.START_QUEUE ('OSB_FTP_TRANSPORT');
DBMS_AQADM.START_QUEUE ('OSB_REPORTING_ERROR');
DBMS_AQADM.START_QUEUE ('OSB_REPORTING_PROVIDER');
DBMS_AQADM.START_QUEUE ('OSB_REPORTING_PURGE');
DBMS_AQADM.START_QUEUE ('OSB_SFTP_TRANSPORT');
DBMS_AQADM.START_QUEUE ('TASK_NOTIFICATION_Q');
END;
/

SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE' order by name asc;

NAME                      ENQUEUE_ENABLED DEQUEUE_ENABLED
------------------------- --------------- ---------------
AIA_CAVSCALLBACKJMSQ        YES             YES
B2B_BAM_QUEUE               YES             YES
EDN_AQJMS_TOPIC             YES             YES
EDN_EVENT_QUEUE             YES             YES
EDN_OAOO_QUEUE              YES             YES
IP_IN_QUEUE                 YES             YES
IP_OUT_QUEUE                YES             YES
OSB_EMAIL_TRANSPORT         YES             YES
OSB_FILE_TRANSPORT          YES             YES
OSB_FTP_TRANSPORT           YES             YES
OSB_REPORTING_ERROR         YES             YES
OSB_REPORTING_PROVIDER      YES             YES
OSB_REPORTING_PURGE         YES             YES
OSB_SFTP_TRANSPORT          YES             YES
TASK_NOTIFICATION_Q         YES             YES