| Oracle® Fusion Middleware Administrator's Guide for Oracle Business Intelligence Applications 11g Release 1 (11.1.1.7) Part Number E37988-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Administrator's Guide for Oracle Business Intelligence Applications 11g Release 1 (11.1.1.7) Part Number E37988-01 |
|
|
PDF · Mobi · ePub |
This chapter explains how to set up and use Oracle GoldenGate (OGG) and source dependent schemas (SDS).
This chapter contains the following sections:
Section 5.3, "Setting Up Oracle GoldenGate and Source Dependent Schemas"
Section 5.4, "Tasks for Setting Up Oracle GoldenGate and the Source Dependent Schema"
In a conventional ETL scenario, data is loaded from source online transaction processing (OLTP) schemas, which in many cases support full-time transactional systems with constant ongoing updates. Contention can arise during complex extracts from these sources, particularly in cases where significant OLTP data changes have occurred which must be processed and loaded by ETL processes.
To relieve this contention, you can set up source dependent schemas which replicate OLTP schemas in the same database as the Oracle Business Analytics Warehouse schema. In addition to segregating extract processing on the analytical system and eliminating contention on transactional systems, physical architecture and ETL performance benefits accrue from maintaining source data in the same physical location as the warehouse tables, consolidating multiple sources, regions and timezones, and eliminating network bottlenecks and incremental change capture during extraction and load.
The SDS is a separate schema usually stored on the same database as the Oracle Business Analytics Warehouse, which contains data extracted from an OLTP schema on a separate machine. The OLTP schema is treated as the source and the SDS schema as the target of the Oracle GoldenGate processes which maintain the replicated SDS.
The SDS Architecture is an optional addition to the existing BI Applications Architecture that solves many problems associated with data transport from the source OLTP system to the data warehouse and change data capture required for incremental ETL. The architecture consists of these main components:
Source Dependent Data Store (SDS): A separate schema on the OBAW database that is a replication of the source OLTP systems tables. Also stores deletes and additional optimizations for incremental ETL.
Oracle GoldenGate: This replication system is deployed on both source and OBAW database systems. On the source database system, Oracle GoldenGate supports continuous asynchronous change data capture at a low level in the database, then compresses and ships the changed data across the network to the target SDS schema on the analytical warehouse database instance. On the target OBAW database instance, it receives the changed data from one or more source systems and loads them into the target database, specifically into the SDS schemas, one per ETL OLTP source.
ODI: ODI metadata stores definitions used to generate the SDS schemas and to support the Oracle GoldenGate replication processes.
BI Application SDS Components: Components used to support generation of the SDS schema and Oracle GoldenGate replication processes.
This section provides instructions for setting up and using Oracle GoldenGate and SDS.
List of Steps for Setting Up Oracle GoldenGate and Source Dependent Schemas
To install and set up Oracle GoldenGate and SDS, you must complete the following tasks, in order. Each high-level task breaks down into a list of detailed steps provided in the next section.
Configure the source and target databases, as described in Section 5.4.1, "Setup Step: Configure Source and Target Database."
Install Golden Gate software, as described in Section 5.4.2, "Setup Step: Install Oracle GoldenGate on Source and Target Systems."
Configure BI Applications Configuration Manager and ODI, as described in Section 5.4.3, "Setup Step: Configure BI Applications Configuration Manager and Oracle Data Integrator to Support the Source Dependent Schema."
Generate and run the Data Definition Language to create the SDS tables on the SDS schema in the target database, as described in Section 5.4.4, "Setup Step: Generate, Deploy, and Populate the Source Dependent Schema Tables on Target Database."
Generate and deploy OGG Parameter files, as described in Section 5.4.5, "Setup Step: Generate and Deploy Oracle GoldenGate Parameter Files to Source and Target Machines."
Start OGG, as described in Section 5.4.6, "Setup Step: Start Oracle GoldenGate on Source and Target Machines."
This section provides detailed tasks for setting up Oracle GoldenGate and SDS.
Note: You must perform the tasks in this section in the sequence described in Section 5.3, "Setting Up Oracle GoldenGate and Source Dependent Schemas".
In this step, you create Oracle GoldenGate database users on source and target databases. Unlike other database schemas used by BI Applications, the SDS and OGG schemas are not automatically created during installation. Only the installation process can automatically create database users; because datasources are defined in Configuration Manager after installation is complete, the required Source Dependent Schemas associated with these datasources must be manually created. For this reason, an SDS schema must be manually defined on the target database. Additionally, the BI Apps installer is not able to create the OGG database user on the source OLTP system. This section describes how to create the OGG database user on the source database system and the OGG and SDS database users on the target database system.
Create OLTP Oracle GoldenGate Database User
Each OGG process requires a dedicated database user. On the source system, the OGG user needs to be able to query various metadata. Secure database practice is to avoid granting privileges to tables not in use, so SELECT ANY TABLE is not granted to the OGG database user. Instead, as part of the SDS DDL, SELECT privileges are granted only to those tables in the OLTP schema being replicated.
The user creation scripts use the following parameters:
| Parameter | Description |
|---|---|
|
&BIAPPS_OGG |
Oracle GoldenGate Database User Name |
|
&BIAPPS_OGG_PW |
Oracle GoldenGate Database User Password |
Run the following script on the source database to create the source database OGG user.
-- Create OGG User CREATE USER &BIAPPS_OGG IDENTIFIED BY &BIAPPS_OGG_PW DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS; GRANT CREATE SESSION TO &BIAPPS_OGG; GRANT ALTER SESSION TO &BIAPPS_OGG; GRANT SELECT ANY DICTIONARY TO &BIAPPS_OGG; GRANT FLASHBACK ANY TABLE TO &BIAPPS_OGG; -- OGG user requires ALTER ANY table to set up supplemental logging for individual tables. Once accomplished, this privilege can be revoked: GRANT ALTER ANY TABLE TO &BIAPPS_OGG;
Prepare OLTP Database and Redo Logs
Oracle GoldenGate requires that the database be configured for supplemental logging. Execute the following statement in the source database with a user with sufficient privileges.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Create Target Oracle GoldenGate Database User
Each OGG process requires a dedicated database user. On the target system, the OGG user needs to be able to execute various DML operations on the SDS tables as well as optionally create a checkpoint table. Secure database practice is to avoid granting privileges to tables not in use, so SELECT ANY TABLE, INSERT ANY TABLE and so on are not granted to the OGG database user. Instead, as part of the SDS DDL, required privileges are granted only to those tables in the SDS schema for the OGG database user.
The user creation scripts use the following parameters:
| Parameter | Description |
|---|---|
|
&BIAPPS_OGG |
Oracle GoldenGate Database User Name |
|
&BIAPPS_OGG_PW |
Oracle GoldenGate Database User Password |
Run the following script on the target table to create the target database OGG user.
-- Create OGG User CREATE USER &BIAPPS_OGG IDENTIFIED BY &BIAPPS_OGG_PW DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS; GRANT CREATE SESSION TO &BIAPPS_OGG; GRANT ALTER SESSION TO &BIAPPS_OGG; GRANT SELECT ANY DICTIONARY TO &BIAPPS_OGG; -- Create Table privilege only required to create checkpoint table. Can be revoked once table is created. Not required if not creating this table GRANT CREATE TABLE TO &BIAPPS_OGG;
A separate SDS database user must be configured in the target database for each OLTP system that will leverage the SDS. Each supported source instance requires a separate SDS schema. The recommended naming convention for the schema owner is <BIAPPS>SDS<Model Code>_<DSN Number> where <BIAPPS> is a user defined code representing BI Applications content, <Model Code> is the unique code assigned to each datasource type and <DSN Number> is the unique datasource ID assigned to a specific datasource instance. For example, if you have the following two datasources defined as supported source systems in the BI Applications Configuration Manager you would have the corresponding SDS schemas defined in the data warehouse database :
| Source Instance Name | Model Code | Data Source Number | SDS |
|---|---|---|---|
|
Oracle EBS 11.5.10 |
EBS_11_5_10 |
310 |
BIAPPS_SDS_ EBS_11_5_10_310 |
|
Siebel CRM 8.1.1 |
SEBL_8_1_1 |
625 |
BIAPPS_SDS_ SEBL_8_1_1_625 |
Use the following DDL as a template for creating each SDS database user. The following only represents a bare minimum of required DDL statements; adjust for your environment as necessary. Rerun for each supported source instance.
| Parameter | Description |
|---|---|
|
&BIAPPS_SDS_DATA_TS |
Table space name |
|
&ORADATA |
Path where tablespace should be located |
|
&BIAPPS_SDS |
SDS User name |
|
&BIAPPS_SDS_PW |
SDS User password |
|
&BIAPPS_OGG |
Oracle GoldenGate Database User Name |
-- Create tablespace. Following is only an example and may not reflect PSR guidance: CREATE TABLESPACE &BIAPPS_SDS_DATA_TS DATAFILE '&ORADATA/&BIAPPS_SDS_DATA_TS..dbf' SIZE 100M AUTOEXTEND ON NEXT 10M LOGGING DEFAULT COMPRESS FOR OLTP; -- Create SDS User CREATE USER &BIAPPS_SDS IDENTIFIED BY &BIAPPS_SDS_PW DEFAULT TABLESPACE &BIAPPS_SDS_DATA_TS QUOTA UNLIMITED ON &BIAPPS_SDS_DATA_TS; -- Required Grants GRANT CREATE SESSION TO &BIAPPS_SDS; GRANT CREATE TABLE TO &BIAPPS_SDS; -- OGG user must be granted Quota to insert and update data ALTER USER &BIAPPS_OGG QUOTA UNLIMITED ON &BIAPPS_SDS_DATA_TS;
Download and install Oracle GoldenGate software first on the source and then on the target machines. The software is available from Oracle Technology Network. For information about installation of Oracle Golden Gate, refer to the Oracle GoldenGate Installation and Setup Guide for your platform and database.
When installing and configuring the OGG software, consider the following recommendations:
For each OLTP instance supported, install a separate Replicate process on the target machine. As each OLTP instance has its own separate SDS schema on the target database, the Replicate process is populating different targets so a separate Replicate process is required for each.
Install a Data Pump process on the source machine.
The name of the Extract, Data Pump and Replicat processes are limited to eight characters. The suggested naming convention is as follows:
| Process | Naming Convention | Example |
|---|---|---|
|
Extract |
EXT_<Datasource Num Id> |
EXT_310 |
|
Data Pump |
DP_<Datasource Num Id> |
DP_310 |
|
Replicate |
REP_<Datasource Num Id> |
REP_310 |
Follow the steps in the Oracle GoldenGate documentation to configure an instance of OGG on the source and target systems up to the point of starting the OGG processes.
Note that as part of the installation and configuration, a procedure is run to generate BI Applications-specific parameter files, as discussed in the following section. For more information, see Section 5.4.5, "Setup Step: Generate and Deploy Oracle GoldenGate Parameter Files to Source and Target Machines." The install and configuration of the OGG processes are completed at this point.
Example Steps to configure the Oracle GoldenGate processes
The following is a set of example steps to configure the OGG processes, modify as appropriate for your environment. For the source system, configure Extract and Data Pump processes. The initial steps in the example below effectively remove an existing instance of both processes. If none already exist, start with the START MGR command.
--Stop Manager on primary database dblogin USERID <GG User's DB ID, requirement depends on database>, PASSWORD <GG User's DB password, requirement depends on database > STOP MGR --Stop GG processes STOP <name of Extract process> DELETE EXTTRAIL <relative or fully qualified path where Extract Trail files are created on source system>/* DELETE <name of Extract process> STOP <name of Data Pump process> DELETE RMTTRAIL <relative or fully qualified path where Replicat Trail files are created on target system>/* DELETE <name of Data Pump process> --Delete Previous Trail Files SHELL rm <relative or fully qualified path where Extract Trail files are created on source system>/* --Start Manager on primary database START MGR --Primary database capture configuration ADD EXTRACT <name of Extract process>, TRANLOG, BEGIN NOW ADD EXTTRAIL <relative or fully qualified path where Extract Trail files are to be created on source system>, EXTRACT <name of Extract process>, MEGABYTES 50 --Primary database pump configuration: ADD EXTRACT<name of Data Pump process>, EXTTRAILSOURCE <relative or fully qualified path where Extract Trail files are to be created on source system>, ADD RMTTRAIL <relative or fully qualified path where Replicat Trail files are to be created on target system>, EXTRACT<name of Data Pump process>, MEGABYTES 50
Example:
--Stop Manager on primary database dblogin userid gg, password gg STOP MGR --Stop GG processes STOP EXT_310 DELETE EXTTRAIL ./dirdat/* DELETE EXT_310 STOP DP_310 DELETE RMTTRAIL ./dirdat/* DELETE DP_310 --Delete Previous Trail Files SHELL rm ./dirdat/* --Start Manager on primary database START MGR --Primary database capture configuration ADD EXTRACT EXT_310, TRANLOG, BEGIN NOW ADD EXTTRAIL ./dirdat/tr, EXTRACT EXT_310, MEGABYTES 50 --Primary database pump configuration: ADD EXTRACT DP_310, EXTTRAILSOURCE ./dirdat/tr ADD RMTTRAIL ./dirdat/tr, EXTRACT DP_310, MEGABYTES 50
Implement similar steps for the Replicate process in the target system. The initial steps effectively remove an existing instance of the Replicate process. If none already exist, start with the START MGR command.
--Stop Manager on target database dblogin USERID <GG User's DB ID, requirement depends on database>, PASSWORD <GG User's DB password, requirement depends on database > STOP MGR --Stop GG processes STOP <name of Replicat process> DELETE <name of Replicat process> --Delete CHECKPOINTTABLE DELETE CHECKPOINTTABLE <GG User's DB ID>.GGSCHKPT --Delete Previous Trail Files SHELL rm <relative or fully qualified path where Replicat Trail files are created on target system>/* --Start Manager on target database START MGR --Create CHECKPOINTTABLE in target database dblogin USERID <GG User's DB ID>, PASSWORD <GG User's DB password> ADD CHECKPOINTTABLE <GG User's DB ID>.GGSCHKPT --Target database delivery configuration ADD REPLICAT <name of Replicat process>, exttrail <relative or fully qualified path where Replicat Trail files are to be created on target system>
Example:
--Stop Manager on target database dblogin userid gg, password gg STOP MGR --Stop GG processes STOP REP_310 DELETE REP_310 --Delete CHECKPOINTTABLE DELETE CHECKPOINTTABLE --Delete Previous Trail Files SHELL rm ./dirdat/* --Start Manager on target database START MGR --Create CHECKPOINTTABLE in target database dblogin userid gg, password gg ADD CHECKPOINTTABLE --Target database delivery configuration ADD REPLICAT REP_310, exttrail ./dirdat/tr
Enable SDS in Configuration Manager
Enable the SDS option for each datasource defined in Configuration Manager. You can enable the SDS option for the entire datasource or for individual Fact Groups. The SDS option is enabled by setting the value for the IS_SDS_DEPLOYED parameter to 'Yes'.
In Configuration Manager, select the Source Instance.
Click Manage Data Load Parameters.
Locate the IS_SDS_DEPLOYED parameter in the list.
In the Global Parameter Value, replace <Edit Value> with 'Yes'.
A warning is displayed indicating that the parameter is being updated globally for all Fact and Dimension Groups. Click Yes to confirm or, if you prefer, set the global paramter to 'No', and then edit the parameter value at the Fact Group or Dimension Group level.
Add SDS Physical Schemas in ODI
For each source instance, you must manually add a corresponding physical schema under the 'BIAPPS_DW' physical server in ODI.
In ODI Studio's Topology Navigator, expand the Oracle technology in the Physical Architecture.
Right-click on Oracle_BI_Apps_DW and select New Physical Schema.
In the Definition, set Schema (Schema) and Schema (Work Schema) both to the SDS schema owner.
Select Flexfields.
For the DATASOURCE_NUM_ID flex field, uncheck the Default checkbox and assign the DSN value associated with that source as defined in Configuration Manager.
Save the physical schema definition. Ignore the message about the physical server not being assigned a context.
In this step, you generate and run the Data Definition Language to create the SDS tables on the SDS schema in the target database.
Procedures are provided to generate the required objects to enable the SDS. To generate the required DDL, in ODI Designer execute the 'Generate DDL - SDS' scenario found under 'BI Apps Project > Components > SDS > Oracle > Generate SDS DDL. Provide an appropriate context when prompted.As the procedure can only accept a single source type, this process needs to be repeated for each different type of Source system to be enabled.
To execute the scenario, right-click it and select Execute. When the scenario is executed, a prompt appears to provide values for the DDL execution options. Refer to the following table describing the options to provide appropriate values.
| Option | Description |
|---|---|
|
GG_USER_DW |
Golden Gate database user on the BI Applications database |
|
GG_USER_SOURCE |
Golden Gate database user on the OLTP database. |
|
SDS_MODEL |
The OLTP model to be used to generate the SDS schema. Each model is associated with a logical schema. The logical schema is associated with a physical schema. The logical and physical schema DSN flexfields must match an SDS physical schema's DSN flexfield defined under the BI Apps DW physical server in order for this utility to determine the appropriate schema name to be used for the SDS. The SDS physical schema with the matching DSN flexfield value is used to identify changes and execute the DDL against if the RUN_DDL option is set to Y. |
|
CREATE_SCRIPT_FILE |
Y or N. Set to Y if you want to review the DDL or manually execute the script. |
|
REFRESH_MODE |
FULL or INCREMENTAL. Full drops and creates all tables. Incremental compares the repository with the SDS schema and applies changes using |
|
CHAR_CLAUSE |
Y or N. For Unicode support, will include the |
|
RUN_DDL |
Y or N. Determines whether to execute the DDL commands. The script will be executed against the physical SDS schema associated with the SDS_MODEL. Note that this script will be executed via the user defined in the BIAPPS_DW physical server which is usually the owner of the BIAPPS_DW schema and which may not have appropriate privileges to create or alter tables in another schema. To have the utility execute the DDL script, you may need to grant It is recommended to set this option to N. If set to Y, both the tables and indexes will be created. Having the indexes on the tables will impact the performance of initially loading the tables. Rather, it is recommended that you set this option to N, manually execute the Table DDL, perform the initial load of the tables, then manually execute the Index DDL. Also, if an index or primary key constraint is not defined correctly in ODI, uniqueness or not null errors could be generated and a table could fail to be loaded. Indexes and primary keys are useful for Oracle GoldenGate but are not required. It is better to build the indexes and primary keys after the data is loaded and make any necessary corrections to the constraint's definition in ODI and attempt to build the index or primary key again. |
|
SCRIPT_LOCATION |
The location where the script should be created if the CREATE_SCRIPT_FILE option is true. |
|
TABLE_MASK |
To generate the DDL for all tables, use a wildcard (the default). To generate for only a subset of tables with names matching a particular pattern, use that pattern with a wildcard, such as PER_%. |
If you set CREATE_SCRIPT_FILE to Y, four files are generated by the Generate SDS DDL procedure in the location specified by SCRIPT_LOCATION. One is a .SQL script to creates the tables. Another is a .SQL script to create the indexes and analyze the tables. This allows you to create the tables, perform an initial load of the tables without any indexes that could hurt performance, and then create the indexes and analyze the tables after they are loaded. Another .SQL script is generated which grants SELECT privileges to the OGG database user only for those tables that need to be selected from. The final file is a log file.
Grant privileges to OLTP Tables
The OGG user must be able to select from the tables in the OLTP database. Rather than grant the SELECT ANY TABLE privilege to the OGG user, SELECT privileges are granted only to those tables that actually need to be replicated to the target system.
The SDS DDL generator procedure creates a script to grant SELECT privileges to the OGG user. Refer to the script 'BIA_SDS_Schema_Source_Grants_DDL_<unique ID>.sql' and execute the contents in the OLTP database with a user with sufficient privileges to grant SELECT privileges on the OLTP tables.
The SDS DDL generator procedure creates a .SQL script that follows the naming convention BIA_SDS_Schema_DDL_<unique ID>.sql which contains the CREATE or ALTER DDL statements to create or alter the tables in the SDS schema. Execute the SQL in this file against the SDS schema.
The ETL process must be able to select from the SDS tables. Typically, the ETL process uses the BI Applications data warehouse schema owner. This must be granted SELECT privileges on the SDS tables. In addition, the Oracle GoldenGate user needs read and write access to these same tables. The SDS Generate DDL procedure grants SELECT privileges to the BI Applications data warehouse schema owner and SELECT, INSERT, UPDATE and DELETE privileges to the OGG user.
Performing Initial Load of the SDS Tables: Create Database Link to OLTP Database
A variety of methods can be used to initially load the data from the source database to the target database. A procedure is provided to generate a script to perform an initial load as described in the steps below. Note however, that you may opt for other methods. The procedure generates a script that executes DML statements that extract data over a database link.
Note:
LOB and LONG datatype columns are created in the SDS, but the provided utilities to initially copy data from the source to target system cannot support these datatypes, so columns with these datatypes are specifically excluded by these utilities. If data in these columns are required, an alternate method for performing an initial load of the SDS will need to be implemented.
Note:
In Siebel implementations, a small number of tables in the Siebel database are created when installing the BI Applications. These tables must be manually created and always have S_ETL as a prefix. Be sure these tables have already been created prior to executing these steps. If these tables do not already exist, a "table or view does not exist" error can occur when executing the following commands.
First, create a database link to the OLTP database on the target database. The procedure to generate the DML script requires that a database link already exist named "DW_TO_OLTP" prior to being executed. The procedure executes using the BIAPPS_DW physical server so the database link has to either be defined in the same schema as used in the BIAPPS_DW physical server or else defined as a public database link. This database link must be manually created, it is not automatically created.
The procedure only populates a single SDS schema at a time. If creating multiple SDS schemas to accommodate multiple sources, this database link will need to be updated prior to each run to point to a different OLTP instance.
Note:
The JDE application spreads data across four databases and is tracked under four different submodels under a single JDE specific model. The DML option will need to be executed for each separate submodel and the "DW_TO_OLTP" database link will need to be updated prior to executing the DML script.
Performing Initial Load of the SDS Tables: Execute Procedure to generate DML script
This DML script generation procedure requires that the ODI topology is set up correctly, ensuring the OLTP model logical schema DSN matches with the desired target warehouse SDS physical schema DSN. The DSNs are set in the logical or physical schema flexfields.
In ODI Designer, execute the COPY_OLTP_TO_SDS scenario found under BI Apps Project > Components > SDS > Oracle > Copy OLTP to SDS.
To execute the scenario, right-click it and select Execute. Provide an appropriate context when prompted. When the scenario is executed, a prompt appears to provide values for the DML execution options. Refer to the following table describing the options to provide appropriate values.
| Option | Description |
|---|---|
|
TABLE_LIST |
A comma-separated list of tables. A wildcard match % may be used to match multiple tables. Do not include any line breaks. For example: PER_ALL_ASSIGNMENTS_F,PER%ORG%INFO%,HR%UNIT,FND_LOOKUP_TYPES |
|
CREATE_SCRIPT_FILE |
Y or N. Set to Y if you want to review the DDL or manually execute the script. |
|
RUN_DDL |
Y or N. Whether to execute the DML commands. The script will be executed against the physical SDS schema associated with the SDS_MODEL. Note that this script will be executed via the user defined in the BIAPPS_DW physical server which is usually the owner of the BIAPPS_DW schema and which may not have appropriate privileges to insert data into tables in another schema. To have the utility execute the DDL script, you may need to grant Alternatively, rather than have the procedure execute the script, create the script file and connect to the database as the SDS schema owner and execute the contents of the script file directly. |
|
SDS_MODEL |
The OLTP model to be used to generate the SDS schema. |
|
SCRIPT_LOCATION |
The location where the script should be created if the CREATE_SCRIPT_FILE option is true. |
Performing Initial Load of the SDS Tables: Execute DML Script on SDS Database
The resulting DML script can be executed using the SDS schema owner or the BIAPPS DW schema owner. If executed by the BIAPPS DW schema owner, this user must be granted the SELECT ANY TABLE and INSERT ANY TABLE privileges in order to populate data in another schema. If executed using the SDS schema owner, a private database link named "DW_TO_OLTP" must be created in the SDS schema (the SDS user must be granted the CREATE DATABASE LINK privilege to create this database link) or already created as a public database link.
The DML script that is generated includes all tables used by all ETL tasks. If you are not executing all ETL tasks, you may want to consider identifying the tasks you are not executing and removing the corresponding tables from this script so that they are not replicated, thus keeping the overall size of the SDS down. Refer to the parameter files to determine the tasks that use each table and edit this script to remove the tables you do not need to replicate.
Create SDS Indexes and Analyze the SDS schema
When the tables are populated, execute the 'BIA_SDS_Schema_Index_DDL_<unique ID>.sql' script to create indexes and analyze the SDS tables.
Parameter files are used to control how Oracle GoldenGate operates. These files are deployed to the source system, where the Extract and Data Pump processes are executed, and the target system, where the Replicat process is executed. An ODI procedure generates these parameter files based on the metadata defined in ODI. A scenario that executes this procedure is provided to generate the Oracle GoldenGate parameter files to populate the SDS.
To generate the required parameter files, execute the 'GENERATE_SDS_OGG_PARAM_FILES' scenario found under BI Apps Project > Components > SDS > Generate SDS OGG Param Files. When the scenario is executed, a prompt appears to provide values for the parameter file options. Refer to the following table describing the options to provide appropriate values to match your environment. As the procedure can only accept a single Source type, this process needs to be repeated for each different type of Source system to be enabled.
| Parameter | Description |
|---|---|
|
PARAM_FILE_LOCATION |
Location on machine where ODI client is running where parameter files will be created. Example: C:\temp\ |
|
DATASOURCE_NUM_ID |
Datasource Num Id value associated with the particular source for which parameter files are to be generated. Example: 310 |
|
DATAPUMP_NAME |
Name of the Datapump Process specified when installing OGG on the source machine. Limit is eight characters. Suggested naming convention is DP_<Datasource Num Id>, for example DP_310. |
|
EXTRACT_NAME |
Name of the Primary Extract Process specified when installing OGG on the source machine. Limit is eight characters. Suggested naming convention is EX_<Datasource Num Id>, for example EXT_310. |
|
EXTRACT_TRAIL |
Path and name of trail file on source system. Can be a relative or fully qualified path, though actual file name must be two characters. In the example below, 'tr' is the name of the trail file. Example: ./dirdat/tr |
|
DEFSFILE |
The relative or fully qualified path on the source system where the DEFGEN definition file should be created and file name. This value is included in the DEFGEN.prm parameter file that is generated by this procedure. The DEFGEN utility is executed on the source database, so the path provided must be a path available on the system the source database runs on. Suggested naming convention is DEF_<Datasource Num Id>.def. Example: ./dirdef/DEF_310.def |
|
SOURCE_GG_USER_ID |
Database user dedicated to the Oracle GoldenGate processes on the source database. Example: GG_USER |
|
SOURCE_GG_PASSWORD |
Password for the database user dedicated to the Oracle GoldenGate processes on the source database. By default, the password is stored as clear text in the generated parameter file. If an encrypted value is desired, use the ENCRYPT PASSWORD utility and edit the generated parameter files accordingly. Example: GG_PASSWORD |
|
SOURCE_PORT |
Port used by the OGG Manager Process on the source system. The default value when OGG is installed is 7809. |
|
REPLICAT_NAME |
Name of the Replicat Process specified when installing OGG on the target machine. Limit is eight characters. Suggested naming convention is REP_<Datasource Num Id>, for example REP_310 |
|
SOURCE_DEF |
This is the Source Definitions file created by executing the DEFGEN utility on the source database and copied over to the target machine. This can be either a relative or fully qualified path to this definition file on the target system. Include the /dirdef subfolder as part of the path. Suggested naming convention is DEF_<Datasource Num Id>.def, for example ./dirdef/DEF_310.def Note that the file name is usually the same as the one defined for DEFSFILE but the paths are usually different as DEFSFILE includes the path where OGG is stored on the source system, while SOURCE_DEFS includes the path where OGG is installed on the target system. |
|
REMOTE_HOST |
IP address or Host Name of the target machine where the Replicat process runs. |
|
REMOTE_TRAIL |
Path and name of the trail file on target system. Can be a relative or fully qualified path though the actual file name must be two characters. In the example below, 'tr' is the name of the trail file. Example: ./dirdat/tr |
|
BIA_GG_USER_ID |
Database user dedicated to the Oracle GoldenGate processes on the target database, for example GG_USER |
|
BIA_GG_PASSWORD |
Password for the database user dedicated to the Oracle GoldenGate processes on the target database. By default, the password is stored as clear text in the generated parameter file. If an encrypted value is desired, use the ENCRYPT PASSWORD utility and edit the generated parameter files accordingly. Example: GG_PASSWORD |
|
BIA_GG_PORT |
Port used by the OGG Manager Process on the target system. The default value when OGG is installed is 7809. |
The procedure automatically creates subfolders under a folder you specify. The naming convention is DSN_<DATASOURCE_NUM_ID> where <DATASOURCE_NUM_ID> is the value you specify when executing this procedure. For example, if you specify 310 as the value for DATASOURCE_NUM_ID, there will be a folder named DSN_310. Under this folder are two more subfolders, 'source' and 'target'. The 'source' folder contains all of the files that need to be copied to the source system, while 'target' contains all of the files that need to be copied to the target system.
Tip:
The parameter files that are generated include all tables used by all ETL tasks. The task that uses the table is identified in the parameter file. If you are not executing all ETL tasks, you may want to consider identifying the tasks you are not executing and removing the corresponding tables from the parameter files so that they are not replicated. This keeps the overall size of the SDS down.
The JDE application spreads data across up to four databases. Each database instance must be assigned its own extract/datapump processes and a separate corresponding replicat process. If the JDE components are on a single database, generate a single set of parameter files. If the JDE components are spread across two, three or four databases, generate a corresponding number of parameter files.
For more details on this configuration, refer to the "Configuring Oracle GoldenGate for real-time data warehousing" chapter in the Oracle GoldenGate Windows and UNIX Administrator's Guide, which discusses multiple source databases replicating data to a single target database.
Keep the following in mind when generating the parameter files.Execute the procedure for each database instance. The name of each process and trail file should be unique. The following example assumes all four components are on different databases:
| Component | Extract Name | Data Pump Name | Extract Trail | Defs File | Replicat Name | Replicat Trail | Source Defs |
|---|---|---|---|---|---|---|---|
|
Control |
EX_410A |
DP_410A |
./dirdat/ta |
./dirdef/DEF_310A.def |
REP_410A |
./dirdat/ta |
./dirdef/DEF_310A.def |
|
Data |
EX_410B |
DP_410B |
./dirdat/tb |
./dirdef/DEF_310B.def |
REP_410B |
./dirdat/tb |
./dirdef/DEF_310B.def |
|
Data Dictionary |
EX_410C |
DP_410C |
./dirdat/tc |
./dirdef/DEF_310C.def |
REP_410C |
./dirdat/tc |
./dirdef/DEF_310C.def |
|
System |
EX_410D |
DP_410D |
./dirdat/td |
./dirdef/DEF_310D.def |
REP_410D |
./dirdat/td |
./dirdef/DEF_310D.def |
About PeopleSoft Learning Management Support
PeopleSoft has a Learning Management pillar which is tightly integrated with the Human Capital Management pillar. HCM can be deployed without LM but LM cannot be deployed without HCM. When both are deployed, BI Applications treats the HCM with LM pillars in a similar fashion as it treats JDE: the data is spread across two databases but is treated as a single application. As with the JDE application, in this configuration each database instance must be assigned its own extract/datapump processes and a separate corresponding replicat process.
For more details on this configuration, refer to the "Configuring Oracle GoldenGate for real-time data warehousing" chapter in the Oracle GoldenGate Windows and UNIX Administrator's Guide, which discusses multiple source databases replicating data to a single target database.
Keep the following in mind when generating the parameter files.Execute the procedure for each database instance. The name of each process and trail file should be unique.
| Component | Extract Name | Data Pump Name | Extract Trail | Defs File | Replicat Name | Replicat Trail | Source Defs |
|---|---|---|---|---|---|---|---|
|
HCM Pillar |
EX_518A |
DP_518A |
./dirdat/ta |
./dirdef/DEF_518A.def |
REP_518A |
./dirdat/ta |
./dirdef/DEF_518A.def |
|
LM Pillar |
EX_518B |
DP_518B |
./dirdat/tb |
./dirdef/DEF_518B.def |
REP_518B |
./dirdat/tb |
./dirdef/DEF_518B.def |
Copy all of the files from the 'source' directory on the ODI client to the corresponding directories in the source system:
Copy the following file to the <ORACLE OGG HOME> directory:
ADD_TRANDATA.txt
Copy the following files to the <ORACLE OGG HOME>/dirprm directory:
DEFGEN.prm
<EXTRACT_NAME>.prm where <EXTRACT_NAME> is the value specified when generating the parameter files.
<DATAPUMP_NAME>.prm where <DATAPUMP_NAME> is the value specified when generating the parameter files.
Edit the Extract parameter file
By default, the procedure creates a basic set of parameter files that do not include support for a variety of features. For example, the parameter files do not include support for Transparent Data Encryption (TDE) or unused columns. The procedure also does not include the options to encrypt data.
If your source tables have unused columns, edit the Extract parameter file to include DBOPTIONS ALLOWUNUSEDCOLUMN. If encrypting the data is desired, edit the parameter files to add the ENCRYPTTRAIL and DECRYPTTRAIL options.
To support such features, edit the generated parameter files using the GGSCI EDIT PARAMS <parameter file> command. Also edit the generated param files to implement various tuning options that are specific to the environment. Refer to the Oracle GoldenGate Reference guide for details on implementing these options.
Start the GGSCI command utility from the <ORACLE OGG HOME> directory. Execute the following command to edit the Extract parameter file - this should open the Extract parameter file you copied to <ORACLE OGG HOME>/dirprm:
GGSCI>EDIT PARAMS <EXTRACT_NAME>
Save and close the file.
Oracle GoldenGate requires table-level supplemental logging. This level of logging is only enabled for those tables actually being replicated to the target system. The SDS Parameter file generator creates 'ADD_TRANDATA.txt' file to enable the table-level logging. This script is executed using the GGSCI command with the Oracle GoldenGate database user. This user must be granted the ALTER ANY TABLE privilege prior to executing this script. Once the script completes, this privilege can be removed. Alternatively, edit the script file to use a database user with this privilege. When the OGG database user is originally created, the ALTER ANY TABLE privilege is granted at that time. Once the script to enable table level supplemental logging completes, this privilege can be revoked from the OGG user.
Start the GGSCI command utility from the <ORACLE OGG HOME> directory and execute the following command:
GGSCI> obey ADD_TRANDATA.txt
Exit GGSCI, then connect to the database and revoke the ALTER ANY TABLE privilege.
Note:
If a table does not have a primary key or any unique indexes defined, you may see a warning message like the following. This is a warning that a 'pseudo' unique key is being constructed and used by Oracle Golden Gate to identify a record. Performance is better if a primary key or unique index is available to identify a record but as we generally cannot add such constraints to an OLTP table when they do not already exists, Oracle Golden Gate creates this pseudo unique key.
WARNING OGG-00869 No unique key is defined for table 'FA_ASSET_HISTORY'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Generate Data Definition File on the Source System
As the source and target tables do not match exactly, configure the Replicat process to use a data definition file which contains definitions of the tables on the source system required to map and convert data. The procedure generates a basic DEFGEN.prm file used to create a data definition file. If required, edit this file to reflect your environment. Refer to the Oracle GoldenGate documentation for more details. For example, the DEFGEN.prm file does not leverage the encryption option, so if this or other options are desired, edit the parameter file to enable them.
To edit the DEFGEN.prm file, start the GGSCI command utility from the Oracle GoldenGate home directory. Execute the following command to open and edit the DEFGEN.prm file you copied to <ORACLE OGG HOME>/dirprm:
GGSCI>EDIT PARAMS DEFGEN
Save and close the file and exit GGSCI, then run the DEFGEN utility. Refer to Oracle GoldenGate documentation for more information about this utility and its execution. The following is an example of executing this command on UNIX:
defgen paramfile dirprm/defgen.prm
A data definition file is created in the <ORACLE OGG HOME>/ folder with the path and name specified using the DEFSFILE parameter. FTP the data definition file to the <ORACLE OGG HOME>/dirdef folder on the remote system using ASCII mode. Use BINARY mode to FTP the data definitions file to the remote system if the local and remote operating systems are different and the definitions file is created for the remote operating system character set.
Copy all of the files from the 'target' directory on the ODI client to the corresponding directories in the target system.
Copy the following file to the <ORACLE OGG HOME>/dirprm directory in the target system:
<REPLICAT_NAME>.prm where <REPLICAT_NAME> is the value specified when generating the parameter files.
Edit the Replicat Parameter File
By default, the procedure creates a basic set of parameter files that do not include support for a variety of features. For example, the parameter files do not include support for Transparent Data Encryption (TDE) or unused columns. The procedure also does not include the options to encrypt data.If encrypting the data is desired, edit the generated parameter files to add the ENCRYPTTRAIL and DECRYPTTRAIL options. To support such features, edit the generated parameter files using the GGSCI EDIT PARAMS <parameter file> command. Also edit the generated param files to implement various tuning options that are specific to the environment. Refer to the Oracle GoldenGate Reference guide for details on implementing these options.
Start the GGSCI command utility from the <ORACLE OGG HOME> directory. Execute the following command to edit the Extract parameter file. This should open the Replicat parameter file - this should open the Replicat parameter file you copied to <ORACLE OGG HOME>/dirprm:
GGSCI>EDIT PARAMS <REPLICAT_NAME>
Save and close the file, and exit GGSCI.
Create a Checkpoint Table (Optional)
The procedure does not account for a checkpoint table in the target system. A checkpoint table is not required but is recommended. If a checkpoint table is desired, follow the steps detailed in Oracle GoldenGate documentation to create a checkpoint table and edit the GLOBALS param file to reference this table.
Start the GGSCI command utility
GGSCI> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE <OGG User>.<Table Name>
Save and close the file, then run the following commands:
GGSCI> DBLOGIN USERID <OGG User> PASSWORD <OGG Password>GGSCI> ADD CHECKPOINTTABLE <OGG User>.<Table Name>
Start Oracle GoldenGate on the Source Machine
Use the following command to start the Extract and Data Pump processes on the source system.
START MGR --Start capture on primary database START <name of Extract process> --Start pump on primary database START <name of Data Pump process>
Example:
START MGR --Start capture on primary database START EXT_310 --Start pump on primary database START DP_310
Start Oracle GoldenGate on the Target Machine
Use the following command to start the Replicat process in the target system.
START MGR
--Start delivery on target database
START <name of Replicat process>
Example:
START MGR --Start capture on primary database START REP_310
|
 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|