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. Importing to the Target Database

Importing to the Target Database

To import to the target container database (CDB), complete the following steps:

Import INFRA Schemas

To import INFRA schemas to the target CDB:

  1. Sign in to the target environment. For example, new-target-db12c.example.com.
  2. Change to the oracle user:
    sudo su - oracle
  3. Make sure the source environment (soa-host01.example.com in this example) is in the /etc/hosts file.
  4. Copy all the scripts you created and dump files from the source environment (soa-host01.example.com in this example) to the target environment (new-target-db12c.example.com:/u01/db_exp/INFRA_EXPORTS in this example):
    mkdir -p /u01/db_exp/INFRA_EXPORTS
cd /u01/db_exp/INFRA_EXPORTS
scp -r oracle@soa-host01.example.com:/u01/db_exp/INFRA_EXPORTS/* .
  5. Set the ORACLE_HOME, ORACLE, and PATH environment variables. For example:
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=CDB1 
export PATH=$PATH:$ORACLE_HOME/bin
  6. Change to the sys user, or any user with sysdba privileges, and connect to the target database, then determine the existing data file location using the following commands:
    sqlplus sys/password as sysdba
set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
set wrap off;
COLUMN FILE_NAME FORMAT A90;
COLUMN name FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A20;
SELECT name, pdb FROM  v$services ORDER BY name;

NAME                      PDB
------------------------- ------------------------------
CDB1.example.com          CDB$ROOT
SYS$BACKGROUND            CDB$ROOT
SYS$USERS                 CDB$ROOT
pdb1_orclsoa.example.com  PDB1_ORCLSOA
pdb2_orclsoa.example.com  PDB2_ORCLSOA

ALTER SESSION SET CONTAINER=PDB1_ORCLSOA;
SELECT TABLESPACE_NAME, FILE_NAME FROM dba_data_files;

TABLESPACE_NAME      FILE_NAME
-------------------- -------------------------------------------------------------------
SYSTEM               /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_hkj1b8h3_.dbf
SYSAUX               /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_hkj1b8gy_.dbf
USERS                /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_hkj1b8h7_.dbf
EXAMPLE              /u01/app/oracle/oradata/CDB1/datafile/o1_mf_example_hkj1b8dt_.dbf

    In this example, the data location of PDB1_ORCLSOA is in /u01/app/oracle/oradata/CDB1/datafile.

  7. Edit the script create_tablespaces.sql and update the value of the DATAFILE variable to /u01/app/oracle/oradata/CDB1/datafile
  8. In order for the import to succeed, edit the script create_role_grant.sql and add the following commands:
    GRANT UNLIMITED TABLESPACE TO "DEV_MDS";
GRANT UNLIMITED TABLESPACE TO "DEV_OPSS";     
GRANT UNLIMITED TABLESPACE TO "DEV_STB";     
GRANT UNLIMITED TABLESPACE TO "DEV_WLS";
  9. As the sys user, connect to the target database and run the scripts create_tablespaces.sql, create_users.sql, and create_role_grant.sql:
    sqlplus sys/password as sysdba
ALTER SESSION SET CONTAINER=PDB1_ORCLSOA;
show con_name;

CON_NAME
------------------------------
PDB1_ORCLSOA

@create_tablespaces.sql
@create_users.sql
@create_role_grant.sql

    Note:

    Some statements in create_role_grant.sql will fail as some objects are not yet created. You will run this script again later to succeed.
  10. As the sys user, connect to the target database and create the export directory:
    sqlplus "/ as sysdba"
ALTER SESSION SET CONTAINER=PDB1_ORCLSOA;
CREATE DIRECTORY DUMP_INFRA AS '/u01/db_exp/INFRA_EXPORTS';
  11. Check if the export directory was successfully created:
    SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_INFRA;
  12. 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;
  13. Enter the following commands to avoid an error that STBROLE does not exist during the import of DEV_STB:
    sqlplus sys/password@PDB1 as sysdba
BEGIN     EXECUTE IMMEDIATE 'CREATE ROLE STBROLE'; END;
/
  14. Change to the oracle user.
  15. In the INFRA_EXPORTS directory, use a text editor to create a bash script to run the Data Pump Import utility (impdp) on all schemas. For example, create a script named import_infra_schema.sh with the following contents:
    impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_IAU directory=DUMP_INFRA dumpfile=DEV_IAU_export.dmp logfile=DEV_IAU_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_IAU_APPEND directory=DUMP_INFRA dumpfile=DEV_IAU_APPEND_export.dmp logfile=DEV_IAU_APPEND_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_IAU_VIEWER directory=DUMP_INFRA dumpfile=DEV_IAU_VIEWER_export.dmp logfile=DEV_IAU_VIEWER_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_MDS directory=DUMP_INFRA dumpfile=DEV_MDS_export.dmp logfile=DEV_MDS_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_OPSS directory=DUMP_INFRA dumpfile=DEV_OPSS_export.dmp logfile=DEV_OPSS_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_STB directory=DUMP_INFRA dumpfile=DEV_STB_export.dmp logfile=DEV_STB_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_WLS directory=DUMP_INFRA dumpfile=DEV_WLS_export.dmp logfile=DEV_WLS_import.log parallel=4
impdp \"sys/password@PDB1 as sysdba\" schemas=DEV_WLS_RUNTIME directory=DUMP_INFRA dumpfile=DEV_WLS_RUNTIME_export.dmp logfile=DEV_WLS_RUNTIME_import.log parallel=4
  16. As the sys user, run the script in the target environment:
    @import_infra_schema.sh
quit
  17. Connect to the target database and run the script create_role_grant.sql again to succeed without errors:
    sqlplus sys/password@PDB1 as sysdba
@create_role_grant.sql
  18. Before importing the SCHEMA_VERSION_REGISTRY, enter the following commands as the database administrator to create a role to allow registry access:
    sqlplus sys/password@PDB1 as sysdba
BEGIN EXECUTE IMMEDIATE 'CREATE ROLE REGISTRYACCESS'; END;
/
  19. As the oracle user, enter the following commands to run the Data Pump Import utility (impdp):
    impdp \"sys/password@PDB1 as sysdba\" SCHEMAS=SYSTEM directory=DUMP_INFRA DUMPFILE=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp LOGFILE=SYSTEM_SCHEMA_VERSION_REGISTRY_import2.log parallel=4 TABLE_EXISTS_ACTION=APPEND

impdp \"sys/password@PDB1 as sysdba\" SCHEMAS=DEV_IAU,DEV_IAU_APPEND,DEV_IAU_VIEWER,DEV_MDS,DEV_OPSS,DEV_STB,DEV_WLS,DEV_WLS_RUNTIME directory=DUMP_INFRA DUMPFILE=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp LOGFILE=SYSTEM_SCHEMA_VERSION_REGISTRY_import.log parallel=4 TABLE_EXISTS_ACTION=APPEND

    Note:

    The first impdp command will return some errors on DEV2* schemas, which is a normal error as there are no DEV2 schemas in PDB1. The second impdp returns some errors on DEV_* schemas, which can also be ignored.
  20. Enter the following commands and review the SELECT statement output to ensure the data is valid:
    sqlplus sys/password@PDB1 as sysdba
@create_select_grant_system.sql
CREATE PUBLIC SYNONYM schema_version_registry FOR system.schema_version_registry;

set lines 210;
set pages 40;
COLUMN MRC_NAME FORMAT A10;
COLUMN COMP_ID FORMAT A12;
COLUMN COMP_NAME FORMAT A35;
COLUMN OWNER FORMAT A17;
COLUMN VERSION FORMAT A12;
COLUMN STATUS FORMAT A8;
COLUMN UPGRADED FORMAT A8;
COLUMN OBJECT_NAME FORMAT A25;
COLUMN TABLE_NAME FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A15;

SELECT MRC_NAME, COMP_ID, COMP_NAME, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID;

MRC_NAME   COMP_ID      COMP_NAME                           OWNER             VERSION      STATUS   UPGRADED
---------- ------------ ----------------------------------- ----------------- ------------ -------- --------
DEV        IAU          Audit Service                       DEV_IAU           12.2.1.2.0   VALID    N
DEV        IAU_APPEND   Audit Service Append                DEV_IAU_APPEND    12.2.1.2.0   VALID    N
DEV        IAU_VIEWER   Audit Service Viewer                DEV_IAU_VIEWER    12.2.1.2.0   VALID    N
DEV        MDS          Metadata Services                   DEV_MDS           12.2.1.3.0   VALID    N
DEV        OPSS         Oracle Platform Security Services   DEV_OPSS          12.2.1.0.0   VALID    N
DEV        STB          Service Table                       DEV_STB           12.2.1.3.0   VALID    N
DEV        WLS          WebLogic Services                   DEV_WLS           12.2.1.0.0   VALID    N
DEV2       IAU          Audit Service                       DEV2_IAU          12.2.1.2.0   VALID    N
DEV2       IAU_APPEND   Audit Service Append                DEV2_IAU_APPEND   12.2.1.2.0   VALID    N
DEV2       IAU_VIEWER   Audit Service Viewer                DEV2_IAU_VIEWER   12.2.1.2.0   VALID    N
DEV2       MDS          Metadata Services                   DEV2_MDS          12.2.1.3.0   VALID    N
DEV2       OPSS         Oracle Platform Security Services   DEV2_OPSS         12.2.1.0.0   VALID    N
DEV2       SOAINFRA     SOA Infrastructure Services         DEV2_SOAINFRA     12.2.1.3.0   VALID    N
DEV2       STB          Service Table                       DEV2_STB          12.2.1.3.0   VALID    N
DEV2       UCSUMS       User Messaging Service              DEV2_UMS          12.2.1.0.0   VALID    N
DEV2       WLS          WebLogic Services                   DEV2_WLS          12.2.1.0.0   VALID    N

select rowid, mrc_name, owner from system.schema_version_registry$;

ROWID              MRC_NAME   OWNER
------------------ ---------- -----------------
AAAXDtAABAAAIWxAAA DEV        DEV_STB
AAAXDtAABAAAIWxAAB DEV        DEV_IAU_APPEND
AAAXDtAABAAAIWxAAC DEV        DEV_IAU_VIEWER
AAAXDtAABAAAIWxAAD DEV        DEV_MDS
AAAXDtAABAAAIWxAAE DEV        DEV_WLS
AAAXDtAABAAAIWxAAF DEV        DEV_IAU
AAAXDtAABAAAIWxAAG DEV        DEV_OPSS
AAAXDtAABAAAIWxAAH DEV2       DEV2_STB
AAAXDtAABAAAIWxAAI DEV2       DEV2_IAU_APPEND
AAAXDtAABAAAIWxAAJ DEV2       DEV2_IAU_VIEWER
AAAXDtAABAAAIWxAAK DEV2       DEV2_MDS
AAAXDtAABAAAIWxAAL DEV2       DEV2_WLS
AAAXDtAABAAAIWxAAM DEV2       DEV2_UMS
AAAXDtAABAAAIWxAAN DEV2       DEV2_IAU
AAAXDtAABAAAIWxAAO DEV2       DEV2_OPSS
AAAXDtAABAAAIWxAAP DEV2       DEV2_SOAINFRA

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS from all_tables where table_name = 'SCHEMA_VERSION_REGISTRY$' and owner in ('SYSTEM', 'FMWREGISTRY');

OWNER             TABLE_NAME                TABLESPACE_NAME STATUS
----------------- ------------------------- --------------- --------
SYSTEM            SCHEMA_VERSION_REGISTRY$  SYSTEM          VALID

select owner, object_name, object_type from all_objects where object_name like '%SCHEMA_VERSION_REGISTRY%';

OWNER             OBJECT_NAME               OBJECT_TYPE
----------------- ------------------------- -----------------------
PUBLIC            SCHEMA_VERSION_REGISTRY   SYNONYM
SYSTEM            SCHEMA_VERSION_REGISTRY$  TABLE
SYSTEM            SCHEMA_VERSION_REGISTRY   VIEW
  21. Delete lines that do not belong to DEV, and review the SELECT statement output to ensure the data is valid:
    sqlplus sys/password@PDB1 as sysdba
DELETE FROM SCHEMA_VERSION_REGISTRY WHERE MRC_NAME != 'DEV';

set lines 210;
set pages 40;
COLUMN MRC_NAME FORMAT A10;
COLUMN COMP_ID FORMAT A12;
COLUMN COMP_NAME FORMAT A35;
COLUMN OWNER FORMAT A17;
COLUMN VERSION FORMAT A12;
COLUMN STATUS FORMAT A8;
COLUMN UPGRADED FORMAT A8;
COLUMN OBJECT_NAME FORMAT A25;
COLUMN TABLE_NAME FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A15;

SELECT MRC_NAME, COMP_ID, COMP_NAME, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID;

MRC_NAME   COMP_ID      COMP_NAME                           OWNER             VERSION      STATUS   UPGRADED
---------- ------------ ----------------------------------- ----------------- ------------ -------- --------
DEV        IAU          Audit Service                       DEV_IAU           12.2.1.2.0   VALID    N
DEV        IAU_APPEND   Audit Service Append                DEV_IAU_APPEND    12.2.1.2.0   VALID    N
DEV        IAU_VIEWER   Audit Service Viewer                DEV_IAU_VIEWER    12.2.1.2.0   VALID    N
DEV        MDS          Metadata Services                   DEV_MDS           12.2.1.3.0   VALID    N
DEV        OPSS         Oracle Platform Security Services   DEV_OPSS          12.2.1.0.0   VALID    N
DEV        STB          Service Table                       DEV_STB           12.2.1.3.0   VALID    N
DEV        WLS          WebLogic Services                   DEV_WLS           12.2.1.0.0   VALID    N

select rowid, mrc_name, owner from system.schema_version_registry$;

ROWID              MRC_NAME   OWNER
------------------ ---------- -----------------
AAAXDtAABAAAIWxAAA DEV        DEV_STB
AAAXDtAABAAAIWxAAB DEV        DEV_IAU_APPEND
AAAXDtAABAAAIWxAAC DEV        DEV_IAU_VIEWER
AAAXDtAABAAAIWxAAD DEV        DEV_MDS
AAAXDtAABAAAIWxAAE DEV        DEV_WLS
AAAXDtAABAAAIWxAAF DEV        DEV_IAU
AAAXDtAABAAAIWxAAG DEV        DEV_OPSS
  22. Identify and recompile schema objects with status INVALID, and review the SELECT statement output:
    sqlplus sys/password@PDB1 as sysdba
set wrap off;
column owner format a20;
column object_type format a13;
column object_name format a10;

SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID' AND owner in ('DEV_IAU','DEV_IAU_APPEND','DEV_IAU_VIEWER','DEV_MDS','DEV_OPSS','DEV_STB', 'DEV_WLS', 'DEV_WLS_RUNTIME') ORDER BY owner, object_type, object_name;

OWNER                OBJECT_TYPE   OBJECT_NAM
-------------------- ------------- ----------
DEV_IAU              SYNONYM       OHS
DEV_IAU              SYNONYM       OID
DEV_IAU              SYNONYM       OVD
DEV_IAU              SYNONYM       REPORTSSER
DEV_IAU              SYNONYM       WEBCACHE

BEGIN UTL_RECOMP.recomp_serial('DEV_IAU'); END;
/
PL/SQL procedure successfully completed.

SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID' AND owner in ('DEV_IAU','DEV_IAU_APPEND','DEV_IAU_VIEWER','DEV_MDS','DEV_OPSS','DEV_STB', 'DEV_WLS', 'DEV_WLS_RUNTIME') ORDER BY owner, object_type, object_name;

no rows selected

Import SOA Schemas

To import SOA schemas to the target CDB:

  1. Sign in to the target environment. For example, new-target-db12c.example.com.
  2. Change to the oracle user:
    sudo su - oracle
  3. Make sure the source environment (soa-host01.example.com in this example) is in the /etc/hosts file.
  4. Copy all the scripts you created and dump files from the source environment (soa-host01.example.com in this example) to the target environment (new-target-db12c.example.com:/u01/db_exp/SOA_EXPORTS in this example):
    mkdir -p /u01/db_exp/SOA_EXPORTS
cd /u01/db_exp/SOA_EXPORTS
scp -r oracle@soa-host01.example.com:/u01/db_exp/SOA_EXPORTS/* .
  5. Set the ORACLE_HOME, ORACLE, and PATH environment variables. For example:
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=CDB1 
export PATH=$PATH:$ORACLE_HOME/bin
  6. Change to the sys user, or any user with sysdba privileges, and connect to the target database, then determine the existing data file location using the following commands:
    sqlplus sys/password as sysdba
set long 10000;
set lines 200;
set pages 400;
set longchunksize 10000;
set wrap off;
COLUMN FILE_NAME FORMAT A90;
COLUMN name FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A20;
SELECT name, pdb FROM  v$services ORDER BY name;

NAME                      PDB
------------------------- ------------------------------
CDB1.example.com          CDB$ROOT
SYS$BACKGROUND            CDB$ROOT
SYS$USERS                 CDB$ROOT
pdb1_orclsoa.example.com  PDB1_ORCLSOA
pdb2_orclsoa.example.com  PDB2_ORCLSOA

ALTER SESSION SET CONTAINER=PDB2_ORCLSOA;
SELECT TABLESPACE_NAME, FILE_NAME FROM dba_data_files;

TABLESPACE_NAME   FILE_NAME
----------------- -------------------------------------------------------------------------------
SYSTEM      /u01/app/oracle/oradata/CDB1/datafile/pdb2_orclsoa/pdb2_orclsoa_o1_mf_system_hkj0jm6k_.dbf
SYSAUX      /u01/app/oracle/oradata/CDB1/datafile/pdb2_orclsoa/pdb2_orclsoa_o1_mf_sysaux_hkj0jm47_.dbf
USERS       /u01/app/oracle/oradata/CDB1/datafile/pdb2_orclsoa/pdb2_orclsoa_users01.dbf

    In this example, the data location of PDB2_ORCLSOA is in /u01/app/oracle/oradata/CDB1/datafile/pdb2_orclsoa.

  7. Edit the script create_tablespaces.sql and update the value of the DATAFILE variable to /u01/app/oracle/oradata/CDB1/datafile/pdb2_orclsoa
  8. In order for the import to succeed, edit the script create_role_grant.sql and add the following commands:
    GRANT UNLIMITED TABLESPACE TO "DEV2_MDS";
GRANT UNLIMITED TABLESPACE TO "DEV2_OPSS";
GRANT UNLIMITED TABLESPACE TO "DEV2_SOAINFRA";     
GRANT UNLIMITED TABLESPACE TO "DEV2_STB";
GRANT UNLIMITED TABLESPACE TO "DEV2_UMS";      
GRANT UNLIMITED TABLESPACE TO "DEV2_WLS";
  9. As the sys user, connect to the target database and run the scripts create_tablespaces.sql, create_users.sql, and create_role_grant.sql:
    sqlplus sys/password as sysdba
ALTER SESSION SET CONTAINER=PDB2_ORCLSOA;
show con_name;

CON_NAME
------------------------------
PDB2_ORCLSOA

@create_tablespaces.sql
@create_users.sql
@create_role_grant.sql

    Note:

    Some statements in create_role_grant.sql will fail as some objects are not yet created. You will run this script again later to succeed.
  10. As the sys user, connect to the target database and create the export directory:
    sqlplus "/ as sysdba"
ALTER SESSION SET CONTAINER=PDB2_ORCLSOA;
CREATE DIRECTORY DUMP_SOA AS '/u01/db_exp/SOA_EXPORTS';
  11. Check if the export directory was successfully created:
    SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_SOA';
  12. 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;
  13. Enter the following commands to avoid an error that STBROLE does not exist during the import of DEV_STB:
    sqlplus sys/password@PDB2 as sysdba
BEGIN     EXECUTE IMMEDIATE 'CREATE ROLE STBROLE'; END;
/
  14. Change to the oracle user.
  15. In the SOA_EXPORTS directory, use a text editor to create a bash script to run the Data Pump Import utility (impdp) on all schemas. For example, create a script named import_soa_schema.sh with the following contents:
    impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_IAU directory=DUMP_SOA dumpfile=DEV2_IAU_export.dmp logfile=DEV2_IAU_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_IAU_APPEND directory=DUMP_SOA dumpfile=DEV2_IAU_APPEND_export.dmp logfile=DEV2_IAU_APPEND_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_IAU_VIEWER directory=DUMP_SOA dumpfile=DEV2_IAU_VIEWER_export.dmp logfile=DEV2_IAU_VIEWER_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_MDS directory=DUMP_SOA dumpfile=DEV2_MDS_export.dmp logfile=DEV2_MDS_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_OPSS directory=DUMP_SOA dumpfile=DEV2_OPSS_export.dmp logfile=DEV2_OPSS_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_SOAINFRA directory=DUMP_SOA dumpfile=DEV2_SOAINFRA_export.dmp logfile=DEV2_SOAINFRA_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_STB directory=DUMP_SOA dumpfile=DEV2_STB_export.dmp logfile=DEV2_STB_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_UMS directory=DUMP_SOA dumpfile=DEV2_UMS_export.dmp logfile=DEV2_UMS_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_WLS directory=DUMP_SOA dumpfile=DEV2_WLS_export.dmp logfile=DEV2_WLS_import.log parallel=4
impdp \"sys/password@PDB2 as sysdba\" schemas=DEV2_WLS_RUNTIME directory=DUMP_SOA dumpfile=DEV2_WLS_RUNTIME_export.dmp logfile=DEV2_WLS_RUNTIME_import.log parallel=4
  16. As the sys user, run the script in the target environment:
    @import_soa_schema.sh
quit
  17. Connect to the target database and run the script create_role_grant.sql again to succeed without errors:
    sqlplus sys/password@PDB1 as sysdba
@create_role_grant.sql
  18. Before importing the SCHEMA_VERSION_REGISTRY, enter the following commands as the database administrator to create a role to allow registry access:
    sqlplus sys/password@PDB2 as sysdba
BEGIN EXECUTE IMMEDIATE 'CREATE ROLE REGISTRYACCESS'; END;
/
  19. As the oracle user, enter the following commands to run the Data Pump Import utility (impdp):
    impdp \"sys/password@PDB2 as sysdba\" SCHEMAS=SYSTEM directory=DUMP_SOA DUMPFILE=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp LOGFILE=SYSTEM_SCHEMA_VERSION_REGISTRY_import.log parallel=4 TABLE_EXISTS_ACTION=APPEND

impdp \"sys/password@PDB2 as sysdba\" SCHEMAS=DEV2_IAU,DEV2_IAU_APPEND,DEV2_IAU_VIEWER,DEV2_MDS,DEV2_OPSS,DEV2_SOAINFRA,DEV2_STB,DEV2_UMS,DEV2_WLS,DEV2_WLS_RUNTIME directory=DUMP_SOA DUMPFILE=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp LOGFILE=SYSTEM_SCHEMA_VERSION_REGISTRY_import2.log parallel=4 TABLE_EXISTS_ACTION=APPEND

    Note:

    The first impdp command will return some errors on DEV_* schemas, which is a normal error as we don’t have DEV schemas in PDB2. The second impdp returns some errors on DEV2_* schemas, which can also be ignored.
  20. Enter the following commands and review the SELECT statement output to ensure the data is valid:
    sqlplus sys/password@PDB2 as sysdba
@create_select_grant_system.sql
CREATE PUBLIC SYNONYM schema_version_registry FOR system.schema_version_registry;

set lines 210;
set pages 40;
COLUMN MRC_NAME FORMAT A10;
COLUMN COMP_ID FORMAT A12;
COLUMN COMP_NAME FORMAT A35;
COLUMN OWNER FORMAT A17;
COLUMN VERSION FORMAT A12;
COLUMN STATUS FORMAT A8;
COLUMN UPGRADED FORMAT A8;
COLUMN OBJECT_NAME FORMAT A25;
COLUMN TABLE_NAME FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A15;

SELECT MRC_NAME, COMP_ID, COMP_NAME, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID;

MRC_NAME   COMP_ID      COMP_NAME                           OWNER             VERSION      STATUS   UPGRADED
---------- ------------ ----------------------------------- ----------------- ------------ -------- --------
DEV        IAU          Audit Service                       DEV_IAU           12.2.1.2.0   VALID    N
DEV        IAU_APPEND   Audit Service Append                DEV_IAU_APPEND    12.2.1.2.0   VALID    N
DEV        IAU_VIEWER   Audit Service Viewer                DEV_IAU_VIEWER    12.2.1.2.0   VALID    N
DEV        MDS          Metadata Services                   DEV_MDS           12.2.1.3.0   VALID    N
DEV        OPSS         Oracle Platform Security Services   DEV_OPSS          12.2.1.0.0   VALID    N
DEV        STB          Service Table                       DEV_STB           12.2.1.3.0   VALID    N
DEV        WLS          WebLogic Services                   DEV_WLS           12.2.1.0.0   VALID    N
DEV2       IAU          Audit Service                       DEV2_IAU          12.2.1.2.0   VALID    N
DEV2       IAU_APPEND   Audit Service Append                DEV2_IAU_APPEND   12.2.1.2.0   VALID    N
DEV2       IAU_VIEWER   Audit Service Viewer                DEV2_IAU_VIEWER   12.2.1.2.0   VALID    N
DEV2       MDS          Metadata Services                   DEV2_MDS          12.2.1.3.0   VALID    N
DEV2       OPSS         Oracle Platform Security Services   DEV2_OPSS         12.2.1.0.0   VALID    N
DEV2       SOAINFRA     SOA Infrastructure Services         DEV2_SOAINFRA     12.2.1.3.0   VALID    N
DEV2       STB          Service Table                       DEV2_STB          12.2.1.3.0   VALID    N
DEV2       UCSUMS       User Messaging Service              DEV2_UMS          12.2.1.0.0   VALID    N
DEV2       WLS          WebLogic Services                   DEV2_WLS          12.2.1.0.0   VALID    N

select rowid, mrc_name, owner from system.schema_version_registry$;

ROWID              MRC_NAME   OWNER
------------------ ---------- -----------------
AAAXl0AABAAAIIBAAA DEV        DEV_STB
AAAXl0AABAAAIIBAAB DEV        DEV_IAU_APPEND
AAAXl0AABAAAIIBAAC DEV        DEV_IAU_VIEWER
AAAXl0AABAAAIIBAAD DEV        DEV_MDS
AAAXl0AABAAAIIBAAE DEV        DEV_WLS
AAAXl0AABAAAIIBAAF DEV        DEV_IAU
AAAXl0AABAAAIIBAAG DEV        DEV_OPSS
AAAXl0AABAAAIIBAAH DEV2       DEV2_STB
AAAXl0AABAAAIIBAAI DEV2       DEV2_IAU_APPEND
AAAXl0AABAAAIIBAAJ DEV2       DEV2_IAU_VIEWER
AAAXl0AABAAAIIBAAK DEV2       DEV2_MDS
AAAXl0AABAAAIIBAAL DEV2       DEV2_WLS
AAAXl0AABAAAIIBAAM DEV2       DEV2_UMS
AAAXl0AABAAAIIBAAN DEV2       DEV2_IAU
AAAXl0AABAAAIIBAAO DEV2       DEV2_OPSS
AAAXl0AABAAAIIBAAP DEV2       DEV2_SOAINFRA

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS from all_tables where table_name = 'SCHEMA_VERSION_REGISTRY$' and owner in ('SYSTEM', 'FMWREGISTRY');

OWNER             TABLE_NAME                TABLESPACE_NAME STATUS
----------------- ------------------------- --------------- --------
SYSTEM            SCHEMA_VERSION_REGISTRY$  SYSTEM          VALID

select owner, object_name, object_type from all_objects where object_name like '%SCHEMA_VERSION_REGISTRY%';

OWNER             OBJECT_NAME               OBJECT_TYPE
----------------- ------------------------- -----------------------
PUBLIC            SCHEMA_VERSION_REGISTRY   SYNONYM
SYSTEM            SCHEMA_VERSION_REGISTRY$  TABLE
SYSTEM            SCHEMA_VERSION_REGISTRY   VIEW
  21. Delete lines that do not belong to DEV2, and review the SELECT statement output to ensure the data is valid:
    sqlplus sys/password@PDB2 as sysdba
DELETE FROM SCHEMA_VERSION_REGISTRY WHERE MRC_NAME != 'DEV2';

set lines 210;
set pages 40;
COLUMN MRC_NAME FORMAT A10;
COLUMN COMP_ID FORMAT A12;
COLUMN COMP_NAME FORMAT A35;
COLUMN OWNER FORMAT A17;
COLUMN VERSION FORMAT A12;
COLUMN STATUS FORMAT A8;
COLUMN UPGRADED FORMAT A8;
COLUMN OBJECT_NAME FORMAT A25;
COLUMN TABLE_NAME FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A15;

SELECT MRC_NAME, COMP_ID, COMP_NAME, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID;

MRC_NAME   COMP_ID      COMP_NAME                           OWNER             VERSION      STATUS   UPGRADED
---------- ------------ ----------------------------------- ----------------- ------------ -------- --------
DEV2       IAU          Audit Service                       DEV2_IAU          12.2.1.2.0   VALID    N
DEV2       IAU_APPEND   Audit Service Append                DEV2_IAU_APPEND   12.2.1.2.0   VALID    N
DEV2       IAU_VIEWER   Audit Service Viewer                DEV2_IAU_VIEWER   12.2.1.2.0   VALID    N
DEV2       MDS          Metadata Services                   DEV2_MDS          12.2.1.3.0   VALID    N
DEV2       OPSS         Oracle Platform Security Services   DEV2_OPSS         12.2.1.0.0   VALID    N
DEV2       SOAINFRA     SOA Infrastructure Services         DEV2_SOAINFRA     12.2.1.3.0   VALID    N
DEV2       STB          Service Table                       DEV2_STB          12.2.1.3.0   VALID    N
DEV2       UCSUMS       User Messaging Service              DEV2_UMS          12.2.1.0.0   VALID    N
DEV2       WLS          WebLogic Services                   DEV2_WLS          12.2.1.0.0   VALID    N

select rowid, mrc_name, owner from system.schema_version_registry$;

ROWID              MRC_NAME   OWNER
------------------ ---------- -----------------
AAAXl0AABAAAIIBAAH DEV2       DEV2_STB
AAAXl0AABAAAIIBAAI DEV2       DEV2_IAU_APPEND

set lines 210;
set pages 40;
COLUMN MRC_NAME FORMAT A10;
COLUMN COMP_ID FORMAT A12;
COLUMN COMP_NAME FORMAT A35;
COLUMN OWNER FORMAT A17;
COLUMN VERSION FORMAT A12;
COLUMN STATUS FORMAT A8;
COLUMN UPGRADED FORMAT A8;
COLUMN OBJECT_NAME FORMAT A25;
COLUMN TABLE_NAME FORMAT A25;
COLUMN TABLESPACE_NAME FORMAT A15;

SELECT MRC_NAME, COMP_ID, COMP_NAME, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID;

MRC_NAME   COMP_ID      COMP_NAME                           OWNER             VERSION      STATUS   UPGRADED
---------- ------------ ----------------------------------- ----------------- ------------ -------- --------
DEV2       IAU          Audit Service                       DEV2_IAU          12.2.1.2.0   VALID    N
DEV2       IAU_APPEND   Audit Service Append                DEV2_IAU_APPEND   12.2.1.2.0   VALID    N
DEV2       IAU_VIEWER   Audit Service Viewer                DEV2_IAU_VIEWER   12.2.1.2.0   VALID    N
DEV2       MDS          Metadata Services                   DEV2_MDS          12.2.1.3.0   VALID    N
DEV2       OPSS         Oracle Platform Security Services   DEV2_OPSS         12.2.1.0.0   VALID    N
DEV2       SOAINFRA     SOA Infrastructure Services         DEV2_SOAINFRA     12.2.1.3.0   VALID    N
DEV2       STB          Service Table                       DEV2_STB          12.2.1.3.0   VALID    N
DEV2       UCSUMS       User Messaging Service              DEV2_UMS          12.2.1.0.0   VALID    N
DEV2       WLS          WebLogic Services                   DEV2_WLS          12.2.1.0.0   VALID    N

select rowid, mrc_name, owner from system.schema_version_registry$;

ROWID              MRC_NAME   OWNER
------------------ ---------- -----------------
AAAXl0AABAAAIIBAAH DEV2       DEV2_STB
AAAXl0AABAAAIIBAAI DEV2       DEV2_IAU_APPEND
AAAXl0AABAAAIIBAAJ DEV2       DEV2_IAU_VIEWER
AAAXl0AABAAAIIBAAK DEV2       DEV2_MDS
AAAXl0AABAAAIIBAAL DEV2       DEV2_WLS
AAAXl0AABAAAIIBAAM DEV2       DEV2_UMS
AAAXl0AABAAAIIBAAN DEV2       DEV2_IAU
AAAXl0AABAAAIIBAAO DEV2       DEV2_OPSS
AAAXl0AABAAAIIBAAP DEV2       DEV2_SOAINFRA


AAAXl0AABAAAIIBAAJ DEV2       DEV2_IAU_VIEWER
AAAXl0AABAAAIIBAAK DEV2       DEV2_MDS
AAAXl0AABAAAIIBAAL DEV2       DEV2_WLS
AAAXl0AABAAAIIBAAM DEV2       DEV2_UMS
AAAXl0AABAAAIIBAAN DEV2       DEV2_IAU
AAAXl0AABAAAIIBAAO DEV2       DEV2_OPSS
AAAXl0AABAAAIIBAAP DEV2       DEV2_SOAINFRA
  22. Identify and recompile schema objects with status INVALID, and review the SELECT statement output.

    Note:

    In this example, the 6 rows of DEV2_SOAINFRA are already INVALID in the source database. 
    sqlplus sys/password@PDB2 as sysdba
set wrap off;
column owner format a20;
column object_type format a13;
column object_name format a20;

SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID' AND owner in ('DEV2_IAU','DEV2_IAU_APPEND','DEV2_IAU_VIEWER','DEV2_MDS','DEV2_OPSS', 'DEV2_SOAINFRA','DEV2_STB','DEV2_UMS','DEV2_WLS','DEV2_WLS_RUNTIME') ORDER BY owner, object_type, object_name;

OWNER                OBJECT_TYPE   OBJECT_NAM
-------------------- ------------- ----------
DEV2_IAU             SYNONYM       OHS
DEV2_IAU             SYNONYM       OID
DEV2_IAU             SYNONYM       OVD
DEV2_IAU             SYNONYM       REPORTSSER
DEV2_IAU             SYNONYM       WEBCACHE
DEV2_SOAINFRA        PACKAGE BODY  TRS_BPEL
DEV2_SOAINFRA        PACKAGE BODY  TRS_DEC
DEV2_SOAINFRA        PACKAGE BODY  TRS_FAB
DEV2_SOAINFRA        PACKAGE BODY  TRS_HWF
DEV2_SOAINFRA        PACKAGE BODY  TRS_MED
DEV2_SOAINFRA        PACKAGE BODY  TRS_SOA

BEGIN UTL_RECOMP.recomp_serial('DEV2_IAU'); END;
/
PL/SQL procedure successfully completed.
BEGIN UTL_RECOMP.recomp_serial('DEV2_SOAINFRA'); END;
/
PL/SQL procedure successfully completed.

SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID' AND owner in ('DEV2_IAU','DEV2_IAU_APPEND','DEV2_IAU_VIEWER','DEV2_MDS','DEV2_OPSS', 'DEV2_SOAINFRA','DEV2_STB','DEV2_UMS','DEV2_WLS','DEV2_WLS_RUNTIME') ORDER BY owner, object_type, object_name;

OWNER                OBJECT_TYPE   OBJECT_NAME
-------------------- ------------- --------------------
DEV2_SOAINFRA        PACKAGE BODY  TRS_BPEL
DEV2_SOAINFRA        PACKAGE BODY  TRS_DEC
DEV2_SOAINFRA        PACKAGE BODY  TRS_FAB
DEV2_SOAINFRA        PACKAGE BODY  TRS_HWF
DEV2_SOAINFRA        PACKAGE BODY  TRS_MED
DEV2_SOAINFRA        PACKAGE BODY  TRS_SOA
  23. Start the SOA queues, and review the SELECT statement output:
    sqlplus DEV2_SOAINFRA/password@PDB2 

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               NO              NO
OSB_REPORTING_PURGE         NO              NO
OSB_REPORTING_ERROR         NO              NO
OSB_REPORTING_PROVIDER      NO              NO
OSB_FILE_TRANSPORT          NO              NO
OSB_EMAIL_TRANSPORT         NO              NO
OSB_SFTP_TRANSPORT          NO              NO
OSB_FTP_TRANSPORT           NO              NO
TASK_NOTIFICATION_Q         NO              NO
EDN_AQJMS_TOPIC             NO              NO
EDN_OAOO_QUEUE              NO              NO
EDN_EVENT_QUEUE             NO              NO
IP_IN_QUEUE                 NO              NO
IP_OUT_QUEUE                NO              NO
AIA_CAVSCALLBACKJMSQ        NO              NO

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