Perform these detailed tasks to set up Oracle GoldenGate and SDS.
Note:
You must perform the tasks in this section in the listed sequence.Setup Step: Install Oracle GoldenGate on Source and Target Systems
Setup Step: Generate, Deploy, and Populate the Source Dependent Schema Tables on Target Database
Setup Step: Generate and Deploy Oracle GoldenGate Parameter Files to Source and Target Systems
Setup Step: Start Oracle GoldenGate on Source and Target Systems
In this step, you create Oracle GoldenGate database users on source and target databases. Unlike other database schemas used by Oracle 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 Oracle BI Applications Configuration Manager (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 Oracle BI Applications 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.
Download and install Oracle GoldenGate software first on the source and then on the target machines. The software is available from Oracle Technology Network.
See the Oracle GoldenGate Installation and Setup guides for your platform and database:
Oracle GoldenGate for Oracle Installation and Setup Guide
Oracle GoldenGate for DB2 LUW Installation and Setup Guide
Oracle GoldenGate for c-tree Installation and Setup Guide
Installation Recommendations
When installing and configuring the Oracle GoldenGate 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 Oracle GoldenGate 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 Oracle BI Applications-specific parameter files, as discussed in the following section. See 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
These example steps illustrate how to configure the OGG processes. Modify these steps 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
Configure Configuration Manager and ODI to support the Source Dependent Schema.
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.
Generate and run the Data Definition Language (DDL) to create the SDS tables on the SDS schema in the target database.
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.
Generate Oracle GoldenGateParameter Files
To generate the required parameter files, execute the 'GENERATE_SDS_OGG_PARAM_FILES' scenario found under BI Apps Project, Components, SDS, then 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: |
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 Oracle GoldenGate 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 Oracle GoldenGate 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: |
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: |
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 Oracle GoldenGate Manager Process on the source system. The default value when Oracle GoldenGate is installed is 7809. |
REPLICAT_NAME |
Name of the Replicat Process specified when installing Oracle GoldenGate 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 Oracle GoldenGate is stored on the source system, while SOURCE_DEFS includes the path where Oracle GoldenGate 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: |
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 Oracle GoldenGate Manager Process on the target system. The default value when Oracle GoldenGate 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 references. The reference that uses the table is identified in the parameter file. If you are not executing all ETL references, you may want to consider identifying the references 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.
About JD Edwards Support
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.
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.
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 |
Configure the Source System
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.
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.
Enable Table Level Logging
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 OGG 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 GoldenGate 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 GoldenGate 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. 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. 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.
Configure the Target System
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.
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; in which case, 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>