Configure ETL Source for OUAW

This page describes the integration flow between source data and target data, detailing its preparation process and the configuration steps for the ETL process.

On this page:

Prerequisites

  • Ensure that Oracle GoldenGate is installed and configured on source and target database servers.
  • Ensure that Oracle GoldenGate Monitor Agent is installed and create an instance on source and target database servers.
  • Ensure that Oracle GoldenGate Monitor Agent instance is created and set Wallet on the source database server.
  • Ensure that installing the Oracle Data Integrator (ODI)-based ETL component is completed.
  • Ensure that the Oracle Data Integrator domain is created on the target application server.

See Perform Pre-Installation Steps for OUAW for more information.

Back to Top

Set Up Oracle GoldenGate on Source Database Server

To set up each source instance:

  1. Make sure that the source database is enabled in the ARCCHIVELOG mode.
  2. Connect to the source database as a sys user and execute the following statements.

    Copy
    alter database add supplemental log data (primary key) columns;
    alter system set enable_goldengate_replication=TRUE scope=both;

  3. On the command prompt, navigate to the Oracle GoldenGate Home (OGG_Home) directory, open the GLOBALS file in the editor, and add the following command.

    4. Copy 
    EnableMonitoring

  4. Navigate to the OGG_Home directory and run the sqlplus to connect to the database using SYSDBA sys user.

    5. Copy 
    cd <OGG Home>
    sqlplus sys/<sys password>@<source db service name>

  5. Create a new user. This will be the Oracle GoldenGate Owner. Make sure to assign to the new user a tablespace that is not assigned to any other.
    • For a standalone database: CCB01SRC
    • For a multi-tenant database: C##GGOWNER   

      • Note: Keep track of the GoldenGate Owner sername. It will be used in the Oracle Utilities Analytics Warehouse installation.

  6. Connect to the source database with the sys user and run the following commands.
    • For a standalone database:

      • Note: Replace the GoldenGate Owner placeholder with the actual name for the database user that was created in the previous step.

      Copy
      grant CREATE SESSION,CONNECT,RESOURCE,ALTER SYSTEM to <GoldenGate Owner>;
      grant unlimited tablespace to <GoldenGate owner> with admin option;
      EXECUTE dbms_goldengate_auth.grant_admin_privilege('<GoldenGate Owner>','capture');
      grant unlimited tablespace to <GoldenGate Owner> with admin option;    
      Revoke dba from <GoldenGate Owner>;    
      grant create any view to <GoldenGate Owner>;    
      grant select any dictionary to <GoldenGate Owner>;    
      GRANT CREATE ANY DIRECTORY TO <GoldenGate Owner>;    
      GRANT DROP ANY DIRECTORY TO <GoldenGate Owner>;    
      GRANT EXP_FULL_DATABASE TO <GoldenGate Owner>;    
      GRANT IMP_FULL_DATABASE TO <GoldenGate Owner>;   

    • For a multi-tenant database:

      • Note: Modify the common user used for the GoldenGate owner placeholder <Common user for GG> with the actual name of the container common user created on your multi-tenant database.

      Copy
      grant CONNECT to <Common user for GG> container=all;    
      grant IMP_FULL_DATABASE to <Common user for GG> container=all;    
      grant RESOURCE to <Common user for GG> container=all;    
      grant EXP_FULL_DATABASE to <Common user for GG> container=all;    
      grant DROP ANY DIRECTORY to <Common user for GG> container=all;    
      grant UNLIMITED TABLESPACE to <Common user for GG> container=all;    
      grant CREATE ANY DIRECTORY to <Common user for GG> container=all;    
      grant ALTER SYSTEM to <Common user for GG> container=all;    
      grant SELECT ANY DICTIONARY to <Common user for GG> container=all;    
      grant CREATE ANY VIEW to <Common user for GG> container=all;    
      EXECUTE dbms_goldengate_auth.grant_admin_privilege('<GoldenGate Owner>','capture');

  7. Connect to the <source application> ADM schema (for example, CISADM) and run the following commands.
    • For the Oracle Utilities Customer Care and Billing source database, run the following grants for the USER created in step 5:

      • Copy
      Grant select on F1_MST_CONFIG to <GoldenGate owner>
      Grant select on F1_EXT_LOOKUP_VAL to <GoldenGate owner>
      Grant select on F1_BKT_CONFIG to <GoldenGate owner>
      Grant select on F1_BKT_CONFIG_VAL to <GoldenGate owner>

    • For the Oracle Utilities Operational Device Management source database, run the following grants:

      • Copy
      Grant select on F1_MST_CONFIG to <GoldenGate owner>
      Grant select on F1_EXT_LOOKUP_VAL to <GoldenGate owner>

    • For the Oracle Utilities Meter Data Management source database, run the following grants:

      • Copy
      Grant select on <application ADM Schema>.F1_MST_CONFIG to <GoldenGate Owner>;
      Grant select on <application ADM Schema>.F1_EXT_LOOKUP_VAL to <GoldenGate Owner>;

    • For the Oracle Utilities Work and Asset Management source database, run the following grants:

      • Copy
      Grant select on <application ADM Schema>. F1_EXT_LOOKUP_VAL to <GoldenGate Owner>;
      Grant select on <application ADM Schema>.F1_MST_CONFIG to <GoldenGate Owner>;
      Grant select on <application ADM Schema>. F1_BKT_CONFIG to <GoldenGate Owner>;
      Grant select on <application ADM Schema>. F1_BKT_CONFIG_VAL to <GoldenGate Owner>;

Back to Top

Configure Data Encryption Using ENCKEYS Method

Note: Complete this task before running the Oracle Utilities Analytics Warehouse installer (config.sh). Moreover, note that all the scripts mentioned below are located under the <OGG Home> directory and must be run after connecting to the database through the sys user.

  1. Change the directory to the Oracle GoldenGate Home directory.

    2. Copy 
    cd <Oracle GoldenGate HOME>
    keygen <encryption key length (in bits)> [number of keys to generate]

    Example:

    Copy 
    cd $OGG_HOME
    ./keygen 128 1
    0x8CE55035DD6893205A7BD6773FA8E670

  2. Copy the key value to the ENCKEYS file against a logical name such as <CCB1KEY>. You must provide a unique logical name for each key-value generated above. The logical name should have this structure: logical name/space/key-value.
    Example:

    3. Copy 
    <CCB1KEY> 0x8CE55035DD6893205A7BD6773FA8E670

  3. Repeat the step above for each key-value.
  4. Save the file with the name ENCKEYS (upper case only) without any extension.
  5. Create an ASCII ENCKEYS file, or open an existing ENCKEYS text file and append the generated key.

The source ENCKEYS file entry has to be appended to the intermediate server or target server, which would be configured with the source system where the target Oracle GoldenGate is being configured.

Generate Shared Secret Password

Note: Complete this task before running the Oracle Utilities Analytics Warehouse installer (config.sh) and note that all the scripts mentioned below are located under the <OGG Home> directory and must be run after connecting to the database through the sys user.

  1. Ensure that a key specified with KEYNAME is present in the ENCKEYS file.
  2. Change the directory to the Oracle GoldenGate Home directory.
  3. Set ORACLE_HOME and PATH variables.
  4. Run the following command.

    5. Copy 
    ./ggsci
    ENCRYPT PASSWORD <GG Owner password> <algorithm> ENCRYPTKEY <key_name>

    Example:

    Copy 
    cd $<GoldenGate Home>
    ./ggsci
    GGSCI (hostname) 1> ENCRYPT PASSWORD <CCB01SRC> AES128 ENCRYPTKEY <CCB1KEY>

    Example of Encrypted password:

    Copy 
    AADAAAAAAAAAAAIAVGNHBFLGFCHBTCVBFJFHUEJGNCFAOAFEOFBALELISFOEIFWGRBZHCCMCYGYBDAZH

    Algorithm used:

    Copy 
    AES128

    Note: Keep track of the generated ENCKEYS file logical name, key-value, algorithm name used, and the generated encrypted password.

Back to Top

Configure Buckets and Extract Parameters

Before attaching a specific source system to Oracle Utilities Analytics Warehouse, it is necessary to complete some source application configurations as detailed on Configure ETL Parameters & Buckets.

Back to Top

Source Configuration Using OUAW Wizard

  1. Log in to the source database server and execute the following commands.

    2. Copy 
    set ORACLE_HOME, ORACLE_SID and PATH
    cd <GG Home>
    ./ggsci
    dblogin userid <GG Owner schema name>@<source db name>:<source db port>/<source db SID>,password <source shared secret>, encryptkey <encrypt key>
    add schematrandata <Source app schema name> ALLCOLS

  2. Create a directory under the Goldengate home with the context code name (<GG Home>/dirdat/<PRODUCT NAME WITH CONTEXT CODE>) in both the source and the target servers. Example:

    Copy 
    <GG HOME>/dirdat/CCB1
  3. Optional: For the Network Management System (NMS) source application, perform in ODI Studio the steps from the sectionNetwork Management System (NMS) Source Applicationbefore continuing with this process.
  4. Log in to the application server.
  5. Navigate to <ETL Home>/bin and run the following: ./config.sh. The configuration wizard opens.
  6. On the Welcome page, click Next.

    Operation Engine Welcome screen.

  7. On the Configuration Type page, select Configure Source and click Next.

    Operation Engine page, Configuration Type screen. Configure Source is the third and last option.

  8. On the Source Configuration Type page, select Register Source and click Next.

    Operation Engine page, Source Configuration Type screen. Register Source is the first of two options.

  9. On the Source Context page, select the appropriate source product and instance number from the drop-down menu and click Next.

    Operation Engine page, Source Context screen. Choose the appropriate option from the dropdown menus.

  10. On the Source DB Connection Details page, enter the information as in the table below and click Next.

    Operation Engine page, Source DB Connection Details screen, where there are eight text fields to be populated by the user.

    Field Name and Description

    Value

    Host: Enter the source database host name.

    Example: <dbserver.hostname>

    Port: Enter the source database server port.

    		Example: <1521>

    Service Name: Enter the source database service name.

    <Source database service name>

    DB Home Path: Enter the source Database installed path.

    Example: <ORACLE HOME>

    Drillback URL: Enter the Edge product application URL.

    Example: <http://source.application.host:port/ouaf>

    Schema Name: Enter the source database schema name.

    Example: <CISADM>

    Extract Start Date (YYYYMMDD): This is the date from which data should be
    extracted from the source database.

    20000101

    Socks Proxy: This is the socks proxy host and port separated by a (:). Provide the value only if
    a socks proxy has been set up. Otherwise, leave the field blank.

    [ BLANK ]

  11. On the Source GoldenGate Details page, enter the details as in the table below and click Next.

    Operation Engine page, Source Goldenate Details screen. This screen contains ten text fields to be populated by the user.

    Field Name and Description

    Value

    GoldenGate Host: Enter the source database host where GoldenGate is installed.

    Example: <dbserver.hostname>

    GoldenGate Home: Enter the source database server where Oracle GoldenGate is installed.

    Example: <GOLDENGATE HOME>

    Source Database Home: Enter the source database home location.

    Example: <ORACLE HOME>

    GoldenGate Manager Port : Enter the source database server GoldenGate manager port.

    Example: <7830>

    GoldenGate Algorithm:This is the algorithm configured in GoldenGate on the source database server.

    AES128

    GoldenGate Encryptkey:The GoldenGate Encryptkey configured the source database server.

    Generate ENCKEY. See Configure Data Encryption Using ENCKEYS Method.

    GoldenGate Shared Secret:This is the shared secret key configured in Oracle GoldenGate on the source database server.

    Shared Secret Generation. See Generate Shared Secret Password.

    GoldenGate Owner User:This is the user name of the GoldenGate Owner user.

    Example: <C##GGOWNER>

    GoldenGate Owner Password: This is the password of the GoldenGate Owner user.

    <GoldenGate Owner user password>

    Confirm GoldenGate Owner Password: Re-enter the password of the GoldenGate Owner user.

    <GoldenGate Owner user password>

  12. On the Source JAgent Details page, enter the details as in the table below and click Next.

    Operation Engine page, Source JAgent Details screen. This screen shows six text fields to be populated by the user.

    Field name and Description

    Value Examples

    JAgent Host: Enter the source database server host where JAgent is running.

    Example: <dbserver.hostname>

    JAgent GoldenGate: Enter the source database server Oracle GoldenGate installed location where GoldenGate JAgent is running.

    Example: <GoldenGate home>

    JAgent Port: Enter the port number on which Oracle GoldenGate JAgent is running on the source database server GoldenGate host.

    Example: <5557>

    JAgent User: Enter the user used for running JAgent.

    Example: <oracle>

    JAgent Wallet Password: Enter the JAgent Wallet Password.

    <JAGENT wallet password>

    Confirm JAgent Wallet Password: Enter the same JAgent Wallet Password as above.

    <JAGENT wallet password>

  13. On the Configuration Summary page, review the summary to verify that the entered information is correct, and click Configure.
  14. On the Configuration Progress page, review the progress of the configuration. Click Next when the progress bar is 100%.

    Operation Engine page, Configuration Progress screen. It shows the percentage of completion of the configuration.

  15. On the Complete summary page, review the log file path and the source database connection details. Click Finish. Upon completion, the status of the source registration is shown in a prompt.

    Completion Summary screen showing the details of the configuration.

    Note: The detailed logs of the operation will be available in the logs/system/deployodi.log  file in the Oracle Utilities Analytics Warehouse home.

    Example:

    Copy
    <ETL Home>/logs/system/deployodi.log

Configure the Source Product in Oracle Data Integrator

  1. Navigate to Oracle GoldenGate home.

    2. Copy 
    cd <GOLDENGATE HOME>

  2. Set ORACLE_HOME and PATH, connect the ggsci command prompt, and run the command to stop the respective context code replicat processes on the GoldenGate target database server side.

    3. Copy 
    export ORACLE_HOME=<ORACLE_HOME>
    export PATH=$ORACLE_HOME/bin:$PATH
    ./ggsci
    stop REPLICAT <product_contextcode>

    Example:

    Copy 
    stop REPLICAT <CCB1AA>

  3. Exit the ggsci prompt.
  4. Navigate to <FMW HOME>/odi/studio/bin, set JAVA HOME and PATH, and execute the ODI file. Oracle Data Integrator Studio opens.

    5. Copy 
    export JAVA_HOME=<JAVA HOME>
    export PATH=$JAVA_HOME/bin:$PATH
    ./odi

  5. Click Connect to Repository and provide the necessary inputs to execute the connection.

    Oracle Data Integrator Studio showing the connection to the repository in the left-hand pane, and the start page details on the main section.

    Field Name and Description

    Value

    ODI Connection 

     

    Login Name: Enter the name.

    Example: <ODIREPO2800>

    User Name: Enter the username as SUPERVISOR

    SUPERVISOR

    Password: Enter the password of user SUPERVISOR.

    <password of SUPERVISOR>

    Database connection(Master repository)

     

    User: Target database master repository user name

    Example: <OUA_MASTER>

    Password: Target database master repository user password

    Enter the password of Target database master repository user password

    Driver list: Select the drivers from the list

    Oracle JDBC Driver

    Driver Name: Select the driver name from the list

    oracle.jdbc.OracleDriver

    Credentials Details

     

    URL: Provide the target database connection url

    Example: <targetdatabase.host>:<port>/<target database servicename>

    Work Repository

     

    Work Repository: Select the work repository from the search

    OBIU

  6. Click the Designer tab and navigate to the Load Plans and Scenario folder.

    Oracle Data Integrator Studio with the Designer tab expanded on the left, and the Data Integrator start page on the main section.

  7. Expand the Framework and Scheduler folders.

    Oracle Data Integrator Studio with the Designer tab expanded on the left, and the Data Integrator start page on the main section.

  8. Right click on B1_SYNC_CONTEXT Version 001 and click Run. A popup window opens.

    Oracle Data Integrator Studio with the Designer tab expanded on the left, and the Data Integrator start page on the main section. This screenshot also shows the options menu that appears after right-clicking the element mentioned.

  9. In the Context menu, select the appropriate source product and instance number. In this example, Context is CCB1, which refers to instance 1 of the Customer Care and Billing (CCB) application.

  10. In the Logical Agent field, select the appropriate logical agent. In this example, Logical Agent is set to WLS_Agent (ODI Agent).

    Oracle Data Integrator Studio with the Designer tab expanded on the left, and the Data Integrator start page on the main section. This screenshot also shows the Run popup window.

  11. Click OK.

    12. Back to Top

Network Management System (NMS) Source Application

This section details the steps for the source application setup in different versions of Network Management System (NMS).

Notes:

  • Steps below are to be performed in ODI Studio.
  • Ignore the steps below in case the names for scenarios, dimensions, or facts are already set to the ones defined throughout this process.

Versions 2.5.0.2 and Later

  1. Rename the scenario name for the CD_ALARM_LOG NMS Dimension.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Dimensions > Alarm Log Dimension FROM2502.

    2. On the Definition tab, double-click B1_D_ALARM_LOG_VW_FROM2502 Version 001, and rename it to B1_D_ALARM_LOG_VW Version 001.

    3. On the Definition tab, double-click B1_PKG_CD_ALARM_LOG_FROM2502 Version 001, and rename it to B1_PKG_CD_ALARM_LOG Version 001.

    4. Click Save.
  2. Rename the scenario for the CF_ABNORMAL_DEVICE_LOG NMS Fact.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Facts > Abnormal Device Log Fact FROM2502.

    2. On the Definition tab, double-click B1_F_ABNORMAL_DEVICE_LOG_VW_FROM2502 Version 001, and rename it to B1_F_ABNORMAL_DEVICE_LOG_VW Version 001.

    3. On the Definition tab, double-click B1_PKG_CF_ABNORMAL_DEVICE_LOG_FROM2502 Version 001, and rename it to B1_PKG_CF_ABNORMAL_DEVICE_LOG Version 001.

    4. Click Save.

  3. Rename the scenario for the New Damage Assessment Detail NMS Fact.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Facts > New Damage Assessment Detail Fact.

    2. Expand the Fact, Right click on B1_PKG_CF_DAMAGE_ASMT_NEW_DETAIL_FROM2401 scenario and select Open.

    3. Navigate to Definition tab and modify the name of the scenario to B1_PKG_CF_DAMAGE_ASMT_DETAIL.

    4. Click Save.

Note: Once you go through these steps, go back to step three in Source Configuration Using OUAW Wizard

Back to Top

Versions 2.4.0.1 to 2.5.0.2

  1. Rename the scenario for the CD_ALARM_LOG NMS Dimension.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Dimensions > Alarm Log Dimension.

    2. On the Definition tab, double-click B1_D_ALARM_LOG_VW_UPTO2502 Version 001 and rename it to B1_D_ALARM_LOG_VW Version 001.

    3. On the Definition tab, double-click B1_PKG_CD_ALARM_LOG_UPTO2502 Version 001 and rename it to B1_PKG_CD_ALARM_LOG Version 001.

    4. Click Save.
  2. Rename the scenario for the CF_ABNORMAL_DEVICE_LOG NMS Fact.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Facts > Abnormal Device Log Fact.

    2. On the Definition tab, double-click B1_F_ABNORMAL_DEVICE_LOG_VW_UPTO2502 Version 001 and rename it to B1_F_ABNORMAL_DEVICE_LOG_VW Version 001.

    3. On the Definition tab, double-click B1_PKG_CF_ABNORMAL_DEVICE_LOG_UPTO2502 Version 001 and rename it to B1_PKG_CF_ABNORMAL_DEVICE_LOG Version 001.

    4. Click Save.

  3. Rename the scenario for the New Damage Assessment Detail NMS Fact.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Facts > New Damage Assessment Detail Fact.

    2. Expand the Fact, right-click on B1_PKG_CF_DAMAGE_ASMT_NEW_DETAIL_FROM2401 scenario, and select Open.

    3. Navigate to the Definition tab and modify the name of the scenario to B1_PKG_CF_DAMAGE_ASMT_DETAIL.

    4. Click Save

Note: Once you go through these steps, go back to step three in Source Configuration Using OUAW Wizard

Back to Top

Versions below 2.4.0.1

  1. Rename the scenario for the Damage Assessment Detail NMS Fact.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Facts > Damage Assessment Detail Fact.

    2. Expand the Fact, Right click on B1_PKG_CF_DAMAGE_ASMT_DETAIL_UPTO_2401 scenario and select Open.

    3. Navigate to the Definition tab and rename the scenario to B1_PKG_CF_DAMAGE_ASMT_DETAIL.

    4. Click Save.
  2. Rename the scenario for the CD_ALARM_LOG NMS Dimension.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Dimensions > Alarm Log Dimension.

    2. On the Definition tab, double-click B1_D_ALARM_LOG_VW_UPTO2502 Version 001 and rename it to B1_D_ALARM_LOG_VW Version 001.

    3. On the Definition tab, double-click B1_PKG_CD_ALARM_LOG_UPTO2502 Version 001 and rename it to B1_PKG_CD_ALARM_LOG Version 001.

    4. Click Save.

  3. Rename the scenario for the New Damage Assessment Detail NMS Fact.

    1. Navigate to Load Plans and Scenarios > Oracle Utilities BI > NMS > Facts > Abnormal Device Log Fact.

    2. On the Definition tab, double-click B1_F_ABNORMAL_DEVICE_LOG_VW_UPTO2502 Version 001, and rename it to B1_F_ABNORMAL_DEVICE_LOG_VW Version 001.

    3. On the Definition tab, double-click B1_PKG_CF_ABNORMAL_DEVICE_LOG_UPTO2502 Version 001, and rename it to B1_PKG_CF_ABNORMAL_DEVICE_LOG Version 001.

    4. Click Save.

Note: Once you go through these steps, go back to step three in Source Configuration Using OUAW Wizard

Back to Top