Perform the following steps to migrate data from an on-premises database for Oracle Identity Governance (OIG) using Data Pump.

1. Start SQL*Plus.

   sqlplus

2. Connect to the database as SYS user with the SYSDBA privilege.

   CONNECT sys/<password> AS SYSDBA

3. Create a new directory on the database server.
   For example, create the directory, /scratch/OIM/exportdb, using the following command:

   CREATE DIRECTORY exportdb AS '/scratch/OIM/exportdb';

4. Verify if the directory you created in step 3 is created.

   CONNECT SYS/<password>
   SELECT directory_name, directory_path FROM dba_directories;

   For example:

   SELECT exportdb, '/scratch/OIM/exportdb' FROM dba_directories;

5. Stop OIM and SOA managed servers.
   See Starting and Stopping Managed Servers in Administering Oracle Fusion Middleware.
6. Stop the SOA queues.

   Connect SOAINFRA_user/<password>;
   #get the list of all queues
   SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type ='NORMAL_QUEUE';

   To stop all queues, use PL/SQL advance queuing operation.

   Example command:

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

7. Identify the DBMS_SCHEDULER jobs that are running.

   #Connect as OIM User
   /sqlplus /nolog
   SQL> connect OIM_OIM /<password>@<SID> 
   # Identify the DBMS_SCHEDULER jobs that are running.
   SELECT job_name,session_id,running_instance,elapsed_time FROM user_scheduler_running_jobs;

8. If there are any running jobs, wait for the jobs to complete or stop the jobs (Catalog sync and ADF related jobs) gracefully using the following command:

   BEGIN
      DBMS_SCHEDULER.stop_job('REBUILD_OPTIMIZE_CAT_TAGS');
      END;
      /
   ===
      BEGIN
      DBMS_SCHEDULER.stop_job('FAST_OPTIMIZE_CAT_TAGS');
      END;
     /
   ===
      BEGIN
      DBMS_SCHEDULER.stop_job('PURGE_ADF_BC_TXN_TABLE');
      END;
      /

9. For MDS, grant the exempt access policy privilege.

   Note:

   MDS has Virtual Private Database (VPD) based access policies. So, when you use Data Pump export for MDS, the ORA-39181 error is displayed in the log output. If you grant exempt access policy, this error is not displayed.

   CONNECT sys/<password> AS SYSDBA
   GRANT EXEMPT ACCESS POLICY TO SYSTEM;

10. Exit SQL using the exit command.
11. (Optional): Shutdown and start the database server.

    shutdown immediate
    startup

12. Set the environment variables on the on-premises database host.

    setenv ORACLE_HOME <DB_$OH>
    setenv ORACLE_SID <SID>
    setenv PATH $ORACLE_HOME/bin:$PATH

    where DB_$OH is the Oracle_Home of the database and SID is the service ID of the database.

13. Export all OIM schemas at once using the expdp command.

    expdp sys/<password>@orcl DIRECTORY=exportdb
    SCHEMAS=DEV_STB, DEV_OIM, DEV_UMS, DEV_SOAINFRA, DEV_MDS, DEV_OPSS, DEV_IAU, DEV_WLS, DEV_IAU_APPEND, DEV_IAU_VIEWER, DEV_WLS_RUNTIME
    DUMPFILE=exportdbfull.dmp PARALLEL=4 LOGFILE=exportdbfull.log JOB_NAME= exportjobfull EXCLUDE=STATISTICS

    If the schema size is large, you can run the expdp command to export each schema individually.

    For example, you can export the schema DEV_OIM as follows:

    expdp sys/<password>@orcl DIRECTORY=exportdb SCHEMAS=DEV_OIM 
    DUMPFILE=DEV_OIM.dmp PARALLEL=4 LOGFILE=exortdevoim.log JOB_NAME= exportdevoim EXCLUDE=STATISTICS

14. Export the SCHEMA_VERSION_REGISTRY view and its underlying tables from the SYSTEM schema using the Upgrade Assistant.
    1. Navigate to $OH/oracle_common/upgrade/bin.
    2. Export ORACLE_HOME=<Oracle_home>.
    3. Execute ua -backupRegistry on your on-premises database, to backup the existing schema version registry.

       ./ua -backupRegistry
       Oracle Fusion Middleware Upgrade Assistant 12.2.1.4.0
       Enter the Database Connect String(host:port/service or host:port:SID or TNS connect string):
       <databaseHost>:<listenerPort>/<OIG DB Service Name>
       Enter the DBA User Name: sys as sysdba
       Enter the DBA Password: <DBA_Password>

       The schema version registry is saved to ./registry.xml location.

15. After the export is successful, perform the following steps:
    1. Get the list of OIM Schema and the dependent schemas.
    2. Get the list of default, temporary tablespace name of OIM Schema, and the names of any other tablespace involved in OIM Schema objects.

       SELECT DISTINCT tablespace_name,owner FROM dba_segments 
       WHERE owner IN ('DEV_STB','DEV_OIM','DEV_UMS','DEV_SOAINFRA','DEV_MDS','DEV_OPSS','DEV_IAU','DEV_WLS', ‘DEV_IAU_APPEND’, ‘DEV_IAU_VIEWER’)

    3. Retrieve system grants for all schemas.

       SELECT DBMS_METADATA.GET_GRANTED_DDL ('SYSTEM_GRANT','DEV_OIM') FROM DUAL;

    4. Retrieve object grants for all schemas.

       SELECT DBMS_METADATA.GET_GRANTED_DDL ('OBJECT_GRANT','DEV_OIM') FROM DUAL;

16. Upload the data dump file to Oracle Cloud Infrastructure (OCI) Object Storage. See Upload the Export Files to Cloud Object Storage in Oracle Autonomous Database on Dedicated Exadata Infrastructure.
17. Create the credentials for OCI authentication.

    
    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'xx@oracle.com',
        password => '<OCI_Auth_Token>'
      );
    END;
    /

18. Import the data dump file to the ATP-S database for your schemas.

    Note:

    If the dump file is not accessible, then create a pre-auth request for the dump file. See To create a pre-authenticated request for a specific object in Oracle Cloud Infrastructure documentation.

    Example command:

    impdp ADMIN/<password>@<SID> credential=DEF_CRED_NAME dumpfile=https://objectstorage.us-ashburn-1.oraclecloud.com/n/idmocicloudacct/b/OIG/o/exportdbfull.dmp 
    REMAP_TABLESPACE=DEV_OIM:DATA REMAP_TABLESPACE=DEV_IAS_UMS:DATA REMAP_TABLESPACE=DEV_IAS_OPSS:DATA
    REMAP_TABLESPACE=DEV_IAU:DATA REMAP_TABLESPACE=DEV_MDS:DATA REMAP_TABLESPACE=DEV_SOAINFRA:DATA
    REMAP_TABLESPACE=DEV_STB:DATA REMAP_TABLESPACE=DEV_WLS:DATA REMAP_TABLESPACE=DEV_WLS_RUNTIME:DATA 
    REMAP_TABLESPACE=DEV_IAS_TEMP:TEMP REMAP_TABLESPACE=DEV_OIM_TEMP:TEMP

    If the schema size is large, you can run the impdp command to import each schema individually.

    For example, you can import the schema DEV_OIM_TEMP as follows:

    impdp ADMIN/<password>@<SID> credential=DEF_CRED_NAME 
    dumpfile=https://objectstorage.us-ashburn-1.oraclecloud.com/n/idmocicloudacct/b/OIG/o/exportdevoim.dmp REMAP_TABLESPACE=DEV_OIM:DATA REMAP_TABLESPACE=DEV_OIM_TEMP:TEMP

19. Import the SCHEMA_VERSION_REGISTRY view to the SYSTEM schema.
    1. Download and apply the OPatch 32089134 for ua restoreRegistry to your OIG Middleware Home location.
    2. Upload the registry.xml you created in step 14 to the <OIG_OH>/oracle_common/upgrade/bin directory in your OCI environment.
    3. Set the following environment variable:

       export UA_PROPERTIES="-Doracle.jdbc.fanEnabled=false"

    4. Navigate to the <OIG_OH>/oracle_common/upgrade/bin directory and run the ua -restoreRegistry on the ATP-S database.

       ./ua -restoreRegistry
       Oracle Fusion Middleware Upgrade Assistant 12.2.1.4.0
       Enter location of Schema Version Registry backup file:
       <OH>/oracle_common/upgrade/bin/registry.xml
       Restoring from <OH>/oracle_common/upgrade/bin/registry.xml
       Enter prefix or * for list:
       <schema_prefix>
       Enter the Database Connect String:
       (host:port/service or host:port:SID or TNS connect string)
       jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=<protocol_name)
       (port=<port_number>)(host=<host_name>))(connect_data=(service_name=<service_name>.adb.oraclecloud.com))
       (security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))
       Enter the DBA User Name:
       ADMIN
       Enter the DBA Password:
       <DBA_Password>
       Schema Version Registry restored from <OH>/oracle_common/upgrade/bin/registry.xml
       Rows removed: 0. Rows inserted: 6

       To view the definition of the public synonym for schema_version_registry, on the Workspace home page, click SQL Workshop and Object Browser, and then select schema_version_registry.

20. After the import is successful, perform the following steps:
    1. Get the list of invalid schema objects.

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

    2. Compile invalid schema objects.

       Example command:

       EXEC DBMS_UTILITY.compile_schema(schema => 'DEV_OIM', compile_all => false);

    3. Start the SOAINFRA DBMS Queues.

       Connect SOAINFRA_user/<password>;
       #get the list of all queues
       SELECT name FROM USER_QUEUES where queue_type='NORMAL_QUEUE';

       Start the queues one by one using the following command:

       BEGIN
       DBMS_AQADM.START_QUEUE ('OSB_FTP_TRANSPORT');
       END;
       /

• Rewiring the Domain with the ATP-S Database
  You must rewire the data sources in the Oracle Fusion Middleware domain with the newly created Oracle Autonomous Transaction Processing-Shared (ATP-S) database.

Perform the following steps to migrate data from an on-premises database for Oracle Access Manager (OAM) using Data Pump.

1. Start SQL*Plus.

   sqlplus

2. Connect to the database as SYS user with the SYSDBA privilege.

   CONNECT sys/<password> AS SYSDBA

3. Get the list of OAM schema users.

   Select owner,comp_id,version,status from schema_version_registry where owner like '<Schema_Prefix>%';

   Example command output:

   ATPS_IAU    IAU           12.2.1.2.0    VALID
   ATPS_IAU_APPEND      IAU_APPEND    12.2.1.2.0    VALID
   ATPS_IAU_VIEWER      IAU_VIEWER    12.2.1.2.0    VALID
   ATPS_MDS             MDS           12.2.1.3.0    VALID
   ATPS_OAM             OAM           12.2.1.3.0    VALID
   ATPS_OPSS            OPSS          12.2.1.0.0    VALID
   ATPS_STB             STB           12.2.1.3.0    VALID
   ATPS_WLS             WLS           12.2.1.0.0    VALID

4. Get the list of OAM schema users with its associated default and temporary tablespace.

   Select owner,comp_id,version,status from schema_version_registry where owner like '<Schema_Prefix>%';

   Example command output:

   ATPS_OPSS      ATPS_IAS_OPSS      ATPS_IAS_TEMP
   ATPS_IAU                ATPS_IAU           ATPS_IAS_TEMP
   ATPS_IAU_APPEND         ATPS_IAU           ATPS_IAS_TEMP
   ATPS_IAU_VIEWER         ATPS_IAU           ATPS_IAS_TEMP
   ATPS_MDS                ATPS_MDS           ATPS_IAS_TEMP
   ATPS_STB                ATPS_STB           ATPS_IAS_TEMP
   ATPS_WLS_RUNTIME        ATPS_WLS           ATPS_IAS_TEMP
   ATPS_WLS                ATPS_WLS           ATPS_IAS_TEMP
   ATPS_OAM                ATPS_OAM           ATPS_OAM_TEMP

5. On your on-premises database, unlock the schemas for all the schema users from step 4 and commit the changes.

   ALTER USER <Schema_Prefix_User> IDENTIFIED BY <Schema_Password> account unlock;
   commit;

6. Create a new directory on the database server.
   For example, create the directory, /scratch/OAM/oamatps, using the following command:

   CREATE OR REPLACE DIRECTORY oamatps AS '/scratch/oam';

7. Grant access to all users you identified in step 4 to the database directory.

   GRANT read, write ON DIRECTORY oamatps TO <Schema_Prefix_User>;
   commit;

   For example:

   Grant read,write on directory oamatps to ATPS_WLS_RUNTIME,ATPS_IAU,ATPS_IAU_APPEND,ATPS_IAU_VIEWER,ATPS_OPSS,ATPS_MDS,ATPS_STB,ATPS_WLS,ATPS_OAM;
   commit;

8. Export all OAM schemas at once using the expdp command.
   Example command:

   expdp sys/<password>@orcl
   DIRECTORY=exportdb SCHEMAS=DEV_STB, DEV_OIM, DEV_UMS, DEV_SOAINFRA, DEV_MDS, DEV_OPSS, DEV_IAU, DEV_WLS, DEV_IAU_APPEND, DEV_IAU_VIEWER, DEV_WLS_RUNTIME
   DUMPFILE=exportdbfull.dmp PARALLEL=4 LOGFILE=exportdbfull.log

   If the schema size is large, you can run the expdp command to export each schema individually.

   For example, you can export the schema DEV_STB as follows:

   expdp sys/<password>@orcl DIRECTORY=exportdb SCHEMAS=DEV_STB DUMPFILE=DEV_STB.dmp PARALLEL=4 LOGFILE=exortdevoim.log

9. Upload the data dump file to Oracle Cloud Infrastructure Object Storage. See Upload the Export Files to Cloud Object Storage in Oracle Autonomous Database on Dedicated Exadata Infrastructure.
10. To access the dump files during import, create a pre-auth request for the dump file, and note down the URL. See To create a pre-authenticated request for a specific object in Oracle Cloud Infrastructure documentation.
11. Connect to the ATP-S database using on-premises database client.

    export ORACLE_HOME=<Oracle_Home>
    ./sqlplus /nolog
    SQL> connect ADMIN/<admin_password>@<ATP_databasename> 

12. Create the credentials for OCI authentication.

    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'xx@oracle.com',
        password => '<OCI_Auth_Token>'
      );
    END;
    /

13. Import the data dump file to the ATP-S database for your schemas.

    impdp ADMIN/<admin_password>@<ATP_databasename> credential=DEF_CRED_NAME
    dumpfile=<dump_file_cloud_object_storage_location>.dmp REMAP_TABLESPACE=<schema_prefix>_SCHEMA:DATA REMAP_TABLESPACE=<schema_prefix>_SCHEMA_TEMP:TEMP

    Example command:

    impdp ADMIN/<admin_password>@<ATP_databasename> credential=DEF_CRED_NAME dumpfile= https://objectstorage.us-ashburn-1.oraclecloud.com/p/izPjJpSvZP_LONAVxZ_sItknpaYv9BBg7mRsxC3slycpvoLL6UiLMuDwa7RVLSGi/n/idmocicloudacct/b/OIG/o/IAU_APPEND.dmp
    REMAP_TABLESPACE=ATPS_IAU:DATA REMAP_TABLESPACE=ATPS_IAS_TEMP:TEMP

14. Grant unlimited amount of disk space in the tablespaces to all users from step 4.
    For example, grant unlimited tablespace to ATPS_IAU user as follows:

    Grant unlimited tablespace to ATPS_IAU;
    commit;

• Rewiring the Domain with the ATP-S Database
  You must rewire the data sources in the Oracle Fusion Middleware domain with the newly created Oracle Autonomous Transaction Processing-Shared (ATP-S) database.