Skip to Main Content
Return to Navigation

Implementing Oracle GoldenGate for PeopleSoft Off-Load Reporting

This section contains an overview and discusses:

Understanding GoldenGate Within PeopleSoft

Oracle GoldenGate, with Oracle Database 11g, enables you to off-load resource-intensive activities from a production database to a synchronized standby database. Oracle GoldenGate enables access to a physical standby database for queries, sorting, reporting, web-based access, and so on, while continuously applying changes received from the production database. If you use Oracle GoldenGate at your site, PeopleTools provides the infrastructure to use GoldenGate with your PeopleSoft application databases.

Image: Oracle GoldenGate synchronizing data on the primary and standby databases

The following diagram depicts how Oracle GoldenGate enables you to incorporate the use of a standby database for you to offload designated read-only transactions, freeing up more resources to handle the read-write transactions on your primary (production) database.

Oracle GoldenGate synchronizing data on the primary and standby databases

The following table describes the elements within the diagram:

Element

Description

Primary Database (source)

Your production database, handling the read-write requests of your transactional system. For example, this database fills orders, updates employee information, adds new product offerings, and so on.

Standby Database (target or reporting database)

Your clone of the primary database designed to handle read-only, or mostly-read-only (MRO), requests so that those transactions can be off-loaded from your primary database, conserving resources on the production system. Examples of MRO requests include, PSQUERY Viewer, Tree Viewer, components that only submit SELECT SQL to display lists of employees, products, and so on.

Oracle GoldenGate

Synchronizes the data stored in the primary and standby databases so that they remain exact duplicates. This is achieved using a combination of Oracle GoldenGate features, DBLINKS, and Remote Synonyms defined by scripts delivered with PeopleTools.

Primary Access ID

The PeopleSoft access ID used for connecting to the primary production database as well as the standby database.

Note: Only one access ID is required, unlike Oracle Active Data Guard, where the PeopleSoft implementation requires two access IDs (a primary and a secondary access ID).

Read Only components

When Oracle GoldenGate is enabled and PeopleSoft is configured with a standby database, these components are RO enabled:

  • Query Manager: PSQUERY Viewer queries will be run on the Standby database.

    Note: The PSQUERY Viewer does not need to be explicitly marked RO. Units of work utilizing ICQueryService are redirected to the Standby DB. Because PSQUERY Viewer uses the ICQueryService, its SQL will be redirected to the standby database.

  • Tree Manager: Tree Viewer

  • BI Publisher: Query Report Viewer

  • Pivot Grid: Pivot Grid Viewer and Pivot Grid viewed as a pagelet

  • SES Feeds Crawler

  • Feeds Reader

  • QAS (Query as a Service)

  • Component Interfaces: Component Interfaces work on any component marked as MRO.

Other components can also be made to run against the standby database, by setting the Read Only option in the component properties dialog box in Application Designer.

See Configuring Read-Only Components.

Limitations:

  • BI Publisher: If Query Report Scheduler is RO enabled, you cannot Schedule a Bursted report.

  • Application Engine: An Application Engine process cannot be marked as RO if it makes any publish call to Integration Broker. There is a limited set of Integration Broker service operations that are enabled, and those operations are specifically related to Feeds and QAS.

Read Only processes

When PeopleSoft is configured for Oracle GoldenGate the following processes are enabled, as delivered, to run against the standby database:

PeopleSoft Query:

  • PSCONQRS: Run Scheduled Connected Queries

  • PSQUERY: Scheduled Query

PS/nVision:

  • Report

  • Report drilldown

  • Report book

SQRs:

  • XRFAPFL

  • XRFFLPN

  • XRFFLRC

  • XRFIELDS

  • XRFMENU

  • XRFPANEL

  • XRFPCFL

  • XRFPNPC

  • XRFRCFL

  • XRFRCPN

  • XRFWIN

  • XRFFLPC

Audit Utilities:

  • SWPAUDIT

  • SYSAUDIT

  • DDDAUDIT

Other processes can also be enabled to run against the standby database by setting the Read Only option on the Process Definition properties page in Process Scheduler.

Note: For Scheduled Query, if a user attempts to schedule a query to run against the standby database, and selects output type Feeds on the Process Scheduler Request page, that process will be redirected to the primary database. This overrides the RO enabled Run Scheduled Query process.

Note: The use of Oracle GoldenGate with PeopleSoft batch processing only applies to the following: Application Engine processes run through the Process Scheduler with PSAESRV configured and SQR processes.

Note: To enable SQR processes to run against the standby database, refer to Configuring Read-Only Processes. SQR Processes that are generally considered reports are ideal candidates for redirection to the standby database.

Oracle GoldenGate needs to be licensed, installed, and enabled for your server before you can begin setting up your PeopleSoft system to take advantage of this feature. The primary and standby databases need to be set up according to the Oracle GoldenGate documentation.

The basic configuration for PeopleSoft and Oracle GoldenGate (OGG) consists of two databases, your Primary database and the Standby database. Characteristics of the server connections to these databases are:

Server

Connection Characteristics

Application Server

  • Primary connection and Standby connection both use the same access ID.

  • Each server process (PSAPPSRV, PSSAMSRV, and so on) can connect to either database, as needed.

Process Scheduler Server

  • Primary connection and Standby connection both use the same access ID.

  • Each batch server element (PSAESRV, PSAE, COBOL, SQR) can connect to the Primary database as needed.

  • Only PSAESRV connects to the Standby database as needed.

GoldenGate operates between the two databases, sits between the two databases, and manages a set of components associated with each of the databases. Depending on your implementation, one to many of these components may be in use. GoldenGate transaction replication software consists of several key components, including:

  • GoldenGate Manager process: controls background process behavior.

  • GoldenGate Extract or Capture component process: extracts data from the online or archive redo log files within an Oracle database.

  • GoldenGate trail files: store the extracted data as part of the replication processing.

  • GoldenGate Pump process: moves the data in the trail files from the primary database server to the reporting database server.

  • GoldenGate Replicate or delivery process: applies the captured data (stored in the trail files) to the target database.

Note: For a GoldenGate environment, the secondary connection to the Standby database utilizes the same login credentials used for the Primary connection.

Note: Whether both databases reside on the same or different servers, GoldenGate binaries need to be installed twice, in two separate directories. One installation is for the Primary database, and the other is for the Standby database.

See your Oracle Golden Gate documentation for more information.

Installing Oracle GoldenGate for a PeopleSoft Implementation

The installation described here assumes an Oracle installation where the Primary and Standby database instances are on the same machine. If the Primary and Standby database instances are on separate machines, then many of these steps are duplicated on the database server hosting the standby database.

Before beginning the installation, make sure these items are in place:

  • To install on Windows, the user installing GoldenGate must log in as Administrator. Review the Oracle Installation and Setup Guide for Oracle GoldenGate on Windows for any additional requirements.

  • To install on UNIX, the user installing GoldenGate must have read and write privileges on the GoldenGate installation directory.

  • The GoldenGate processes require an operating system user that has privileges to read, write, and delete files and subdirectories in the GoldenGate directory. In addition, the user for the Manager process requires privileges to control GoldenGate processes.

  • The Extract process requires an operating system user that has read access to the log files, both online and archived. On UNIX systems, that user must be a member of the group that owns the Oracle instance.

  • It is recommended that these operating system users be dedicated to GoldenGate. Sensitive information might be available to anyone running a GoldenGate process, depending on how database authentication is configured.

To install Oracle GoldenGate for a PeopleSoft Implementation:

  1. Download the latest media from E-Delivery: https://edelivery.oracle.com/.

  2. FTP or copy the media to database server.

  3. Login on the database host as the ‘oracle’ user.

  4. On your database server, create a directory that the ‘oracle’ user has read/write/execute access to, such as:

    /data1/ogg

  5. Copy the media to the directory you created.

  6. Logged in as the ‘oracle’ user, unzip the media file.

    For example:

    unzip V22230-01.zip
    
    rtdc68005spdb:$ unzip V22230-01.zip
    Archive:  V22230-01.zip
      inflating: ggs_Linux_x64_ora11g_64bit_v11_1_1_0_0_078.tar
      inflating: OGG_WinUnix_Rel_Notes_11.1.1.0.0_078.pdf
      inflating: README.txt
    
  7. Expand the tarball twice: once to a primary directory and once again to a standby directory.

    Note: The following examples assume the primary and standby directories already exist.

    The primary database will use the primary directory installation and the standby database will use the other installation. For example:

    tar -xvf  <tar ball> -C primary/
    
    tar -xvf  <tar ball> -C standby/
    
  8. View the primary and standby directory to verify the install directory and object creation.

    For example:

    rtdc68005spdb:$ ls
    ggs_Linux_x64_ora11g_64bit_v11_1_1_0_0_078.tar  primary
    rtdc68005spdb:$
    
    rtdc68005spdb:$ cd primary
    rtdc68005spdb:$ ls
    UserExitExamples           ddl_remove.sql                   extract
    bcpfmt.tpl                 ddl_session.sql                  freeBSD.txt
    bcrypt.txt                 ddl_session1.sql                 ggMessage.dat
    chkpt_ora_create.sql       ddl_setup.sql                    ggsci
    cobgen                     ddl_sqlmx.tpl                    help.txt
    convchk                    ddl_status.sql                   keygen
    db2cntl.tpl                ddl_staymetadata_off.sql         libicudata.so.38
    ddl_access.tpl             ddl_staymetadata_on.sql          libicui18n.so.38
    ddl_cleartrace.sql         ddl_sybase.tpl                   libicuuc.so.38
    ddl_db2.tpl                ddl_tandem.tpl                   libxerces-c.so.28
    ddl_db2_os390.tpl          ddl_trace_off.sql                libxml2.txt
    ddl_ddl2file.sql           ddl_trace_on.sql                 logdump
    ddl_disable.sql            ddl_tracelevel.sql               marker_remove.sql
    ddl_enable.sql             ddlcob                           marker_setup.sql
    ddl_informix.tpl           ddlgen                           marker_status.sql
    ddl_mss.tpl                defgen                           mgr
    ddl_mysql.tpl              demo_more_ora_create.sql         notices.txt
    ddl_nopurgeRecyclebin.sql  demo_more_ora_insert.sql         params.sql
    ddl_nssql.tpl              demo_ora_create.sql              replicat
    ddl_ora10.sql              demo_ora_insert.sql              reverse
    ddl_ora10upCommon.sql      demo_ora_lob_create.sql          role_setup.sql
    ddl_ora11.sql              demo_ora_misc.sql                server
    ddl_ora9.sql               demo_ora_pk_befores_create.sql   sqlldr.tpl
    ddl_oracle.tpl             demo_ora_pk_befores_insert.sql   tcperrs
    ddl_pin.sql                demo_ora_pk_befores_updates.sql  usrdecs.h
    ddl_purgeRecyclebin.sql    emsclnt                          zlib.txt
    rtdc68005spdb:$ cd ..
    

Creating Subdirectories for Primary and Standby GoldenGate Installations

In each installation directory (primary and standby) you must create these required subdirectories.

Subdirectory

Description

dirchk

GoldenGate checkpoint files.

dirdat

GoldenGate extract and trail files.

dirdef

Source data definitions generated by the DEFGEN utility. (Used to translate heterogeneous data.)

dirpcs

Process status files.

dirout

Directory no longer used.

dirprm

GoldenGate parameter files (run time configuration files).

dirrpt

Process report files.

dirsql

SQL files.

dirtmp

Temporary storage for transactions.

dirver

GoldenGate Veridata directory. (Only used if Veridata is also installed in this GoldenGate instance.)

To install Oracle GoldenGate subdirectories:

  1. Change directories to your \primary directory.

  2. Launch the GoldenGate command line interface (GGSCI).

    rtdc68005spdb:$ cd \data1\ogg\primary
    rtdc68005spdb:$ ./ggsci
    
    Oracle GoldenGate Command Interpreter for Oracle
    Version 11.1.1.0.0 Build 078
    Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42
    
    Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
    
  3. Use the create subdirs command to create the required subdirectories.

    GGSCI (rtdc68005spdb) 1> create subdirs
    
    Creating subdirectories under current directory /data1/ogg/primary
    
    Parameter files                /data1/ogg/primary/dirprm: created
    Report files                   /data1/ogg/primary/dirrpt: created
    Checkpoint files               /data1/ogg/primary/dirchk: created
    Process status files           /data1/ogg/primary/dirpcs: created
    SQL script files               /data1/ogg/primary/dirsql: created
    Database definitions files     /data1/ogg/primary/dirdef: created
    Extract data files             /data1/ogg/primary/dirdat: created
    Temporary files                /data1/ogg/primary/dirtmp: created
    Veridata files                 /data1/ogg/primary/dirver: created
    Veridata Lock files            /data1/ogg/primary/dirver/lock: created
    Veridata Out-Of-Sync files     /data1/ogg/primary/dirver/oos: created
    Veridata Out-Of-Sync XML files /data1/ogg/primary/dirver/oosxml: created
    Veridata Parameter files       /data1/ogg/primary/dirver/params: created
    Veridata Report files          /data1/ogg/primary/dirver/report: created
    Veridata Status files          /data1/ogg/primary/dirver/status: created
    Veridata Trace files           /data1/ogg/primary/dirver/trace: created
    Stdout files                   /data1/ogg/primary/dirout: created
    
  4. Exit the command line interface.

    GGSCI (rtdc68005spdb) 2> exit
  5. View the primary directory to verify the additional subdirectories were created.

  6. Repeat these steps for your standby directory.

  7. Verify for each installation that the GoldenGate manager is stopped prior to continuing with further configuration instructions.

    For example:

    rtdc68005spdb:$ cd \data1\ogg\primary
    rtdc68005spdb:$ ./ggsci
    
    GGSCI (rtdc68005spdb) 2> info all
    
    Program     Status      Group       Lag           Time Since Chkpt
    
    MANAGER     STOPPED
    
    GGSCI (rtdc68005spdb) 3>
    

Configuring PeopleSoft Databases for Oracle GoldenGate

This section explains these setup steps that need to be performed on the primary and standby databases:

Creating the Oracle GoldenGate User

GoldenGate requires a separate Oracle database user that is dedicated to GoldenGate installation defined in both the Primary and Standby databases. It can be the same user for all of the GoldenGate processes that must connect to a database, such as:

  • Extract (source/primary database)

  • Replicat (target/standby database)

  • Manager (source/primary database, if using DDL support)

  • DEFGEN (source or target database)

Note: For the purposes of this document, the same GoldenGate Oracle user is defined on both databases.

Note: To preserve the security of your data, and to monitor GoldenGate processing accurately, do not permit other users, applications, or processes to log on or operate as the GoldenGate database user.

Note: Keep a record of the application database user (PeopleSoft Access ID). It is required in the GoldenGate parameter files, as in, the USERID parameter for the database.

The following table outlines the required database user privileges.

User Privilege

Extract

Replicat

Create Session, Alter Session

X

X

Note: If RESOURCE cannot be granted to Replicat, use ALTER USER <user> QUOTA {<size> | UNLIMITED} ON <tablespace>, where <tablespace> represents all tablespaces that contain target objects.

Resource

X

X

Note: Required only if Replicat owns target objects or any PL/SQL procedures. If CONNECT cannot be granted, grant CREATE <object>for any object Replicat will need to create.

Connect

X

X

Select Any Dictionary

X

X

Flashback Any Table Or Flashback On <owner.table>

X

 

Select Any Table Or Select On <owner.table>

X

X

Select on DBA Clusters

X

 

Insert, Update, Delete on <target tables>

 

X

Create Table

Note: Required if using ADD CHECKPOINTTABLE in GGSCI to use the database checkpoint feature.

 

X

Execute on DBMS_FLSHBACK package (4)

Note: GoldenGate must make a call to DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.

X

 

Note: Be sure to check the most recent Oracle Installation and Setup Guide for GoldenGate based on the GoldenGate version you are using as permission requirements may change or be appended.

You can create the Oracle GoldenGate user by creating a script similar to the following:

set echo on
spool createogguser.log
-- Create the OGG User
GRANT CONNECT, RESOURCE to OGGUSER identified by OGGUSER;
--Grant OGG required privileges
GRANT CREATE SESSION to OGGUSER;
GRANT ALTER SESSION to OGGUSER; 
GRANT SELECT ANY DICTIONARY to OGGUSER;
GRANT FLASHBACK ANY TABLE to OGGUSER;
GRANT ALTER ANY TABLE to OGGUSER;
GRANT SELECT ANY TABLE to OGGUSER;
GRANT INSERT ANY TABLE to OGGUSER;
GRANT DELETE ANY TABLE to OGGUSER;
GRANT UPDATE ANY TABLE to OGGUSER;
GRANT CREATE TABLE to OGGUSER;
GRANT UNLIMITED TABLESPACE to OGGUSER;
GRANT EXECUTE on DBMS_FLASHBACK to OGGUSER;
GRANT SELECT ON dba_clusters to OGGUSER;
spool off;

To list the privileges granted to the OGG User, you can run the following script:

set echo on
set heading off
spool showogguserprivileges.log
-- Show all privileges associated with the OGG User
select
  lpad(' ', 2*level) || granted_role "USER, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('OGGUSER')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;
spool off; 

When the previous script is run, you should see results similar to:

  OGGUSER
    ALTER ANY TABLE 
    ALTER SESSION
    CONNECT 
      CREATE SESSION
    CREATE TABLE  
    DELETE ANY TABLE 
    FLASHBACK ANY TABLE 
    INSERT ANY TABLE  
    RESOURCE  
      CREATE CLUSTER  
      CREATE INDEXTYPE
      CREATE OPERATOR
      CREATE PROCEDURE 
      CREATE SEQUENCE
      CREATE TABLE 
      CREATE TRIGGER 
      CREATE TYPE
    SELECT ANY DICTIONARY 
    SELECT ANY TABLE 
    UNLIMITED TABLESPACE 
    UPDATE ANY TABLE 

22 rows selected.

Enable Archive Logging

Archive logging needs to be enabled in the primary databases.

To enable archive logging for Oracle GoldenGate, use these commands in SQLPlus:

  • ALTER DATABASE ARCHIVELOG;

  • ALTER DATABASE OPEN;

For example:

SQL> startup mount
ORACLE instance started.

Total System Global Area  805933056 bytes
Fixed Size                  2230680 bytes
Variable Size             469763688 bytes
Database Buffers          327155712 bytes
Redo Buffers                6782976 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

To view the archive logging status:

SQL>ARCHIVE LOG LIST;

SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /products/oracle/11.2.0.2.0-64bit/dbs/arch
Oldest online log sequence     201
Next log sequence to archive   203
Current log sequence           203
SQL>

Enabling Supplemental Logging

Once the Oracle GoldenGate user is created on both databases, you need to enable supplemental logging on the primary database. You can enable supplemental logging using the following SQL on the primary database while logged in as SYSDBA in SQLPlus:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SWITCH LOGFILE;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
Database altered.

SQL>
System altered.

SQL>
SUPPLEME
--------
YES

SQL> exit

Generating PeopleSoft Parameter File Input

Prior to creating and editing the Oracle GoldenGate configuration files, you need to generate PeopleSoft-specific input parameters for the GoldenGate parameter files.

To generate PeopleSoft-specific GoldenGate parameter files:

  1. Open the following SQL script in your SQL editor or text editor: PS_HOME/scripts/psggconfiggenerateparmfilelists.sql.

  2. Modify the following variables:

    • Modify all occurrences of <OWNER> to reflect the access ID for the database.

    • Modify all occurrences of <PATH> to reflect the script output directory path (as in, /data1/PT852/scripts/ or c:\temp\).

      Note: The ending slash is mandatory for the path.

  3. Save your changes to the SQL file.

  4. Log into SQLPlus using your PeopleSoft access ID.

  5. Run the psggconfiggenerateparmfilelists.sql script.

  6. Verify that these files appear in the output directory:

    • PSGGconfiggenerateparmfilelists.log

    • PSGGgeneratetableexcludes.txt

    • PSGGgeneratetrandatadeletes.txt

      Note: You will add the output from the .txt files to the appropriate GoldenGate parameter files manually.

Creating Oracle GoldenGate Parameter Files for the Primary Database

This section describes the parameter files that you need to create and modify manually for the primary database. These files need to be created in the dirprm directory of your primary Oracle GoldenGate installation. For example, C:\OGG\primary\dirprm. The files you need to create for the primary installation are:

  • mgr.prm

  • priaddtrndata.oby

  • configure_primary.oby

  • primecap.prm

  • primepmp.prm

Creating mgr.prm

Create a file named mgr.prm and add the following:

--
--  mgr.prm file 
--
PORT 7809
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3

Note: The port number defaults to 7809 or 7810.

Creating priaddtrndata.oby

Create a file named priaddtrndata.oby and add the following.

--
--  PRIADDTRNDATA.oby file
--
-- ##########################################################################################
-- ADD Trandata Obey file for Primary
-- This file defines the tables which we are interested in having OGG capture changes from the Transaction logs.
-- We initially specify an add TRANDATA SYSADM.* with wildcard to capture all tables.
-- We then direct OGG to ignore specific table trandata.  In our case all of the PeopleSoft type ‘7’ temp tables.
-- This is done by appending the output from the PSGGgeneratetrandatadeletes.txt (eg. just the generated 
-- DELETE -- TRANDATA statements) after the ADD TRANDATA SYSADM.* statement
-- ##########################################################################################
-- 
-- Edit and modify 'OGGUSER' to Oracle GoldenGate Admin Userid and PW
--  
-- Edit and modify 'SYSADM' to PSACCESSID
-- 
-- Append the output from the PSGGgeneratetrandatadeletes.txt (eg. just the generated 
-- DELETE TRANDATA       -- statements) after the ADD TRANDATA SYSADM.* statement
--
-- ##########################################################################################
DBLOGIN USERID OGGUSER PASSWORD OGGUSER

ADD TRANDATA SYSADM.*

-- ##########################################################################################
-- This section lists the generated DELETE TRANDATA  statements 
-- ##########################################################################################

<Copy in the generated DELETE TRANDATA statements after the preceding ADD TRANDDATA statement.>

-- Example:
-- DELETE TRANDATA SYSADM.PS_AR_CMANRT_TAO3
-- DELETE TRANDATA SYSADM.PS_AR_CMANRT_TAO4
-- DELETE TRANDATA SYSADM.PS_AR_CMANRT_TAO5
-- <more>

 
-- ##########################################################################################
-- This section lists the required static DELETE TRANDATA statements 
-- ##########################################################################################

DELETE TRANDATA SYSADM.PSLOCK 
DELETE TRANDATA SYSADM.PS_SERVERMONITOR 
DELETE TRANDATA SYSADM.PS_SERVERACTVTY 
DELETE TRANDATA SYSADM.PS_PRCSSEQUENCE 
DELETE TRANDATA SYSADM.PS_MESSAGE_LOGPARM 
DELETE TRANDATA SYSADM.PS_MESSAGE_LOG 
DELETE TRANDATA SYSADM.PS_AETEMPTBLMGR 
DELETE TRANDATA SYSADM.PS_AERUNCONTROLPC 
DELETE TRANDATA SYSADM.PS_AERUNCONTROL 
DELETE TRANDATA SYSADM.PS_AELOCKMGR 
DELETE TRANDATA SYSADM.PSWEBPROFHIST 
DELETE TRANDATA SYSADM.PSSERVERSTAT 
DELETE TRANDATA SYSADM.PSQRYTRANS 
DELETE TRANDATA SYSADM.PSPRCSJOBSTATUS 
DELETE TRANDATA SYSADM.PSOPRDEFN 
DELETE TRANDATA SYSADM.PSIBSUBSLAVE 
DELETE TRANDATA SYSADM.PSIBPUBSLAVE 
DELETE TRANDATA SYSADM.PSIBFOLOCK 
DELETE TRANDATA SYSADM.PSIBFAILOVER 
DELETE TRANDATA SYSADM.PSIBBRKSLAVE 
DELETE TRANDATA SYSADM.PSACCESSLOG 
DELETE TRANDATA SYSADM.PS_PTFP_ACCESS_LOG 
DELETE TRANDATA SYSADM.PS_PTFP_OPTIONS 
DELETE TRANDATA SYSADM.PSIBPROFILESYNC 
DELETE TRANDATA SYSADM.PSIBLOGHDR 
DELETE TRANDATA SYSADM.PSIBLOGERR 
DELETE TRANDATA SYSADM.PSIBLOGERRP 
DELETE TRANDATA SYSADM.PSIBLOGDATA 
DELETE TRANDATA SYSADM.PSIBLOGIBINFO 
DELETE TRANDATA SYSADM.PSQASRUN 
DELETE TRANDATA SYSADM.PSPRCSRQST 
DELETE TRANDATA SYSADM.PSPRCSQUE 
DELETE TRANDATA SYSADM.PSPRCSRQSTFILE 
DELETE TRANDATA SYSADM.PSPRCSPARMS 
DELETE TRANDATA SYSADM.PSPRCSRQSTTEXT 
DELETE TRANDATA SYSADM.PSPRCSRQSTTEXT2 
DELETE TRANDATA SYSADM.PS_CDM_LIST 
DELETE TRANDATA SYSADM.PS_CDM_TRANSFER 
DELETE TRANDATA SYSADM.PS_CDM_AUTH 
DELETE TRANDATA SYSADM.PS_BAT_TIMINGS_LOG 
DELETE TRANDATA SYSADM.PS_BAT_TIMINGS_DTL 
DELETE TRANDATA SYSADM.PS_AE_TIMINGS_LG 
DELETE TRANDATA SYSADM.PS_AE_TIMINGS_DT 
DELETE TRANDATA SYSADM.PS_BAT_TIMINGS_FN 
DELETE TRANDATA SYSADM.PSQRYFAVORITES 
DELETE TRANDATA SYSADM.PSQRYSTATS 
DELETE TRANDATA SYSADM.PSFILE_ATTDET 
DELETE TRANDATA SYSADM.PSPTFILE_REF 
DELETE TRANDATA SYSADM.PSPTFILE_WART 
DELETE TRANDATA SYSADM.PS_PTSF_SCHED_STAT 
DELETE TRANDATA SYSADM.PSPGVIEWOPT 
DELETE TRANDATA SYSADM.PSPGCHARTOPT 
DELETE TRANDATA SYSADM.PSPGCHRTFLRSOPT
DELETE TRANDATA SYSADM.PSPGCHTFLRSLANG 
DELETE TRANDATA SYSADM.PSPGDISPOPT 
DELETE TRANDATA SYSADM.PSPGGRIDOPT 
DELETE TRANDATA SYSADM.PSPGQRYPROMPT 
DELETE TRANDATA SYSADM.PSPGCHARTOPTLNG 
DELETE TRANDATA SYSADM.PSPGVIEWOPTLANG 
DELETE TRANDATA SYSADM.PSPTFILE_PRCS 
DELETE TRANDATA SYSADM.PSUSEROBJTYPE 
DELETE TRANDATA SYSADM.PSUSERSRCHDEFN 
DELETE TRANDATA SYSADM.PSUSERPRSNLOPTN 
DELETE TRANDATA SYSADM.PSVERSION 
DELETE TRANDATA SYSADM.PSUSRTAPAGECUST 
DELETE TRANDATA SYSADM.PSBATCHAUTH 
DELETE TRANDATA SYSADM.PSBATCHAUTHLONG 
DELETE TRANDATA SYSADM.PSPRCSCHLDINFO 
DELETE TRANDATA SYSADM.PS_PTNVSLYTQRY 
DELETE TRANDATA SYSADM.PSTREESELNUM 
DELETE TRANDATA SYSADM.PSTREESELCTL 
DELETE TRANDATA SYSADM.PSNVSBATCHRSTRT 
DELETE TRANDATA SYSADM.PSNVSDRILLQRY 
DELETE TRANDATA SYSADM.PSNVSDRLPROMPTS 
DELETE TRANDATA SYSADM.PS_CDM_TEXT 
DELETE TRANDATA SYSADM.PS_PRCSRQSTDIST 
DELETE TRANDATA SYSADM.PSTREESELECT01 
DELETE TRANDATA SYSADM.PSTREESELECT02 
DELETE TRANDATA SYSADM.PSTREESELECT03 
DELETE TRANDATA SYSADM.PSTREESELECT04 
DELETE TRANDATA SYSADM.PSTREESELECT05 
DELETE TRANDATA SYSADM.PSTREESELECT06 
DELETE TRANDATA SYSADM.PSTREESELECT07 
DELETE TRANDATA SYSADM.PSTREESELECT08 
DELETE TRANDATA SYSADM.PSTREESELECT09 
DELETE TRANDATA SYSADM.PSTREESELECT10 
DELETE TRANDATA SYSADM.PSTREESELECT11 
DELETE TRANDATA SYSADM.PSTREESELECT12 
DELETE TRANDATA SYSADM.PSTREESELECT13 
DELETE TRANDATA SYSADM.PSTREESELECT14 
DELETE TRANDATA SYSADM.PSTREESELECT15 
DELETE TRANDATA SYSADM.PSTREESELECT16 
DELETE TRANDATA SYSADM.PSTREESELECT17 
DELETE TRANDATA SYSADM.PSTREESELECT18 
DELETE TRANDATA SYSADM.PSTREESELECT19 
DELETE TRANDATA SYSADM.PSTREESELECT20 
DELETE TRANDATA SYSADM.PSTREESELECT21 
DELETE TRANDATA SYSADM.PSTREESELECT22 
DELETE TRANDATA SYSADM.PSTREESELECT23 
DELETE TRANDATA SYSADM.PSTREESELECT24 
DELETE TRANDATA SYSADM.PSTREESELECT25 
DELETE TRANDATA SYSADM.PSTREESELECT26 
DELETE TRANDATA SYSADM.PSTREESELECT27 
DELETE TRANDATA SYSADM.PSTREESELECT28 
DELETE TRANDATA SYSADM.PSTREESELECT29 
DELETE TRANDATA SYSADM.PSTREESELECT30

Creating configure_primary.oby

Create a file named configure_primary.oby and add the following:

-- ##########################################################################################
-- 
-- Edit and modify local and remote Trail Directory
-- 
-- ##########################################################################################
DBLOGIN USERID OGGUSER PASSWORD OGGUSER
add extract primecap, tranlog, begin now
add exttrail ./dirdat/pt, extract primecap
add extract primepmp, exttrailsource ./dirdat/pt
add rmttrail ./dirdat/pr, extract primepmp

Creating primecap.prm

Create a file named primecap.prm and add the following:

-- ########################################################################################## 
-- Edit and modify NLS_LANG parameter as required (eg. language.territory.character)
-- Edit and modify ORACLE_HOME
-- Edit and modify Primary ORACLE_SID
-- 
-- Edit and modify 'OGGUSER' to Oracle GoldenGate Admin Userid and PW
-- 
-- Edit and modify 'SYSADM' to PSACCESSID
--  
-- Copy the output from the PSGGgeneratetableexcludes.txt (eg. just the generated TABLEEXCLUDE  
-- statements) between the WILDCARDRESOLVE DYNAMIC statement and before the TABLE SYSADM.*
-- statement
--
-- ##########################################################################################
EXTRACT primecap

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/products/oracle/11.2.0.2.0-64bit")
SETENV (ORACLE_SID = pg112064")

USERID OGGUSER PASSWORD OGGUSER

DISCARDFILE ./dirrpt/primecap.dsc, purge

EXTTRAIL ./dirdat/pt

WILDCARDRESOLVE DYNAMIC

-- ##########################################################################################
-- This section lists the generated TABLEEXCLUDE statements 
-- ##########################################################################################

-- <Copy in the generated TABLEEXCLUDE statements after the preceding WILDCARDRESOLVE DYNAMIC 
-- statement.>

-- Example:

-- TABLEEXCLUDE SYSADM.PS_AR_CMANRT_TAO3;
-- TABLEEXCLUDE SYSADM.PS_AR_CMANRT_TAO4;
-- TABLEEXCLUDE SYSADM.PS_AR_CMANRT_TAO5;
-- TABLEEXCLUDE SYSADM.PS_AR_CMANRT_TAO6;
-- TABLEEXCLUDE SYSADM.PS_AR_CMANRT_TAO7;
-- TABLEEXCLUDE SYSADM.PS_AR_CMCOLPRC_I;
-- TABLEEXCLUDE SYSADM.PS_AR_CMCOLPRC_I1;

 
-- ##########################################################################################
-- This section lists the required static TABLEEXCLUDE statements 
-- ##########################################################################################


TABLEEXCLUDE SYSADM.PSLOCK; 
TABLEEXCLUDE SYSADM.PS_SERVERMONITOR; 
TABLEEXCLUDE SYSADM.PS_SERVERACTVTY; 
TABLEEXCLUDE SYSADM.PS_PRCSSEQUENCE; 
TABLEEXCLUDE SYSADM.PS_MESSAGE_LOGPARM; 
TABLEEXCLUDE SYSADM.PS_MESSAGE_LOG; 
TABLEEXCLUDE SYSADM.PS_AETEMPTBLMGR; 
TABLEEXCLUDE SYSADM.PS_AERUNCONTROLPC; 
TABLEEXCLUDE SYSADM.PS_AERUNCONTROL; 
TABLEEXCLUDE SYSADM.PS_AELOCKMGR; 
TABLEEXCLUDE SYSADM.PSWEBPROFHIST; 
TABLEEXCLUDE SYSADM.PSSERVERSTAT; 
TABLEEXCLUDE SYSADM.PSQRYTRANS; 
TABLEEXCLUDE SYSADM.PSPRCSJOBSTATUS; 
TABLEEXCLUDE SYSADM.PSOPRDEFN; 
TABLEEXCLUDE SYSADM.PSIBSUBSLAVE; 
TABLEEXCLUDE SYSADM.PSIBPUBSLAVE; 
TABLEEXCLUDE SYSADM.PSIBFOLOCK; 
TABLEEXCLUDE SYSADM.PSIBFAILOVER; 
TABLEEXCLUDE SYSADM.PSIBBRKSLAVE; 
TABLEEXCLUDE SYSADM.PSACCESSLOG; 
TABLEEXCLUDE SYSADM.PS_PTFP_ACCESS_LOG; 
TABLEEXCLUDE SYSADM.PS_PTFP_OPTIONS; 
TABLEEXCLUDE SYSADM.PSIBPROFILESYNC; 
TABLEEXCLUDE SYSADM.PSIBLOGHDR; 
TABLEEXCLUDE SYSADM.PSIBLOGERR; 
TABLEEXCLUDE SYSADM.PSIBLOGERRP; 
TABLEEXCLUDE SYSADM.PSIBLOGDATA; 
TABLEEXCLUDE SYSADM.PSIBLOGIBINFO; 
TABLEEXCLUDE SYSADM.PSQASRUN; 
TABLEEXCLUDE SYSADM.PSPRCSRQST; 
TABLEEXCLUDE SYSADM.PSPRCSQUE; 
TABLEEXCLUDE SYSADM.PSPRCSRQSTFILE; 
TABLEEXCLUDE SYSADM.PSPRCSPARMS; 
TABLEEXCLUDE SYSADM.PSPRCSRQSTTEXT; 
TABLEEXCLUDE SYSADM.PSPRCSRQSTTEXT2; 
TABLEEXCLUDE SYSADM.PS_CDM_LIST; 
TABLEEXCLUDE SYSADM.PS_CDM_TRANSFER; 
TABLEEXCLUDE SYSADM.PS_CDM_AUTH; 
TABLEEXCLUDE SYSADM.PS_BAT_TIMINGS_LOG; 
TABLEEXCLUDE SYSADM.PS_BAT_TIMINGS_DTL; 
TABLEEXCLUDE SYSADM.PS_AE_TIMINGS_LG; 
TABLEEXCLUDE SYSADM.PS_AE_TIMINGS_DT; 
TABLEEXCLUDE SYSADM.PS_BAT_TIMINGS_FN; 
TABLEEXCLUDE SYSADM.PSQRYFAVORITES; 
TABLEEXCLUDE SYSADM.PSQRYSTATS; 
TABLEEXCLUDE SYSADM.PSFILE_ATTDET; 
TABLEEXCLUDE SYSADM.PSPTFILE_REF; 
TABLEEXCLUDE SYSADM.PSPTFILE_WART; 
TABLEEXCLUDE SYSADM.PS_PTSF_SCHED_STAT; 
TABLEEXCLUDE SYSADM.PSPGVIEWOPT; 
TABLEEXCLUDE SYSADM.PSPGCHARTOPT; 
TABLEEXCLUDE SYSADM.PSPGCHRTFLRSOPT;
TABLEEXCLUDE SYSADM.PSPGCHTFLRSLANG;
TABLEEXCLUDE SYSADM.PSPGDISPOPT; 
TABLEEXCLUDE SYSADM.PSPGGRIDOPT; 
TABLEEXCLUDE SYSADM.PSPGQRYPROMPT; 
TABLEEXCLUDE SYSADM.PSPGCHARTOPTLNG; 
TABLEEXCLUDE SYSADM.PSPGVIEWOPTLANG; 
TABLEEXCLUDE SYSADM.PSPTFILE_PRCS; 
TABLEEXCLUDE SYSADM.PSUSEROBJTYPE; 
TABLEEXCLUDE SYSADM.PSUSERSRCHDEFN; 
TABLEEXCLUDE SYSADM.PSUSERPRSNLOPTN; 
TABLEEXCLUDE SYSADM.PSVERSION; 
TABLEEXCLUDE SYSADM.PSUSRTAPAGECUST; 
TABLEEXCLUDE SYSADM.PSBATCHAUTH; 
TABLEEXCLUDE SYSADM.PSBATCHAUTHLONG; 
TABLEEXCLUDE SYSADM.PSPRCSCHLDINFO; 
TABLEEXCLUDE SYSADM.PS_PTNVSLYTQRY; 
TABLEEXCLUDE SYSADM.PSTREESELNUM; 
TABLEEXCLUDE SYSADM.PSTREESELCTL; 
TABLEEXCLUDE SYSADM.PSNVSBATCHRSTRT; 
TABLEEXCLUDE SYSADM.PSNVSDRILLQRY; 
TABLEEXCLUDE SYSADM.PSNVSDRLPROMPTS; 
TABLEEXCLUDE SYSADM.PS_CDM_TEXT; 
TABLEEXCLUDE SYSADM.PS_PRCSRQSTDIST; 
TABLEEXCLUDE SYSADM.PSTREESELECT01; 
TABLEEXCLUDE SYSADM.PSTREESELECT02; 
TABLEEXCLUDE SYSADM.PSTREESELECT03; 
TABLEEXCLUDE SYSADM.PSTREESELECT04; 
TABLEEXCLUDE SYSADM.PSTREESELECT05; 
TABLEEXCLUDE SYSADM.PSTREESELECT06; 
TABLEEXCLUDE SYSADM.PSTREESELECT07; 
TABLEEXCLUDE SYSADM.PSTREESELECT08; 
TABLEEXCLUDE SYSADM.PSTREESELECT09; 
TABLEEXCLUDE SYSADM.PSTREESELECT10; 
TABLEEXCLUDE SYSADM.PSTREESELECT11; 
TABLEEXCLUDE SYSADM.PSTREESELECT12; 
TABLEEXCLUDE SYSADM.PSTREESELECT13; 
TABLEEXCLUDE SYSADM.PSTREESELECT14; 
TABLEEXCLUDE SYSADM.PSTREESELECT15; 
TABLEEXCLUDE SYSADM.PSTREESELECT16; 
TABLEEXCLUDE SYSADM.PSTREESELECT17; 
TABLEEXCLUDE SYSADM.PSTREESELECT18; 
TABLEEXCLUDE SYSADM.PSTREESELECT19; 
TABLEEXCLUDE SYSADM.PSTREESELECT20; 
TABLEEXCLUDE SYSADM.PSTREESELECT21; 
TABLEEXCLUDE SYSADM.PSTREESELECT22; 
TABLEEXCLUDE SYSADM.PSTREESELECT23; 
TABLEEXCLUDE SYSADM.PSTREESELECT24; 
TABLEEXCLUDE SYSADM.PSTREESELECT25; 
TABLEEXCLUDE SYSADM.PSTREESELECT26; 
TABLEEXCLUDE SYSADM.PSTREESELECT27; 
TABLEEXCLUDE SYSADM.PSTREESELECT28; 
TABLEEXCLUDE SYSADM.PSTREESELECT29; 
TABLEEXCLUDE SYSADM.PSTREESELECT30;


TABLE SYSADM.*;

Creating primepmp.prm

Create a file named primepmp.prm and add the following:

-- ##########################################################################################
-- Pump for Extract primecap
-- 
-- Edit and modify REMOTE Host, Port and Trail Directory
-- Edit and modify 'SYSADM' to PSACCESSID
-- ##########################################################################################
EXTRACT primepmp

PASSTHRU
PASSTHRUMESSAGES

-- Remote Host and Trail Information
RMTHOST rtdc68005spdb.us.oracle.com MGRPORT 7810
RMTTRAIL ./dirdat/pr

-- Table Mapping Parameters
WILDCARDRESOLVE DYNAMIC

TABLE SYSADM.*;

Creating Oracle GoldenGate Parameter Files for the Standby Database

This section describes the parameter files that you need to create and modify manually for the standby database. These files need to be created in the dirprm directory of your standby Oracle GoldenGate installation. For example, C:\OGG\standby\dirprm. The files you need to create for the standby installation are:

  • mgr.prm

  • configure_standby.oby

  • trgtrep.prm

Creating mgr.prm

Create a file named mgr.prm, and add the following:

--
--  mgr.prm file 
--
PORT 7810
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3

Note: The default GoldenGate port is 7809. When installing twice on the same host, 7810 is the other default port.

Creating configure_standby.oby

Create a file named configure_standby.oby, and add the following”

-- ##########################################################################################
-- Edit and modify 'OGGUSER' to Oracle GoldenGate Admin Userid and PW
-- Edit and modify REMOTE Host and Trail 
-- ##########################################################################################
dblogin, userid OGGUSER, password OGGUSER
add checkpointtable OGGUSER.GGSCHKPT
add replicat trgtrep, exttrail ./dirdat/pr, checkpointtable OGGUSER.GGSCHKPT

Creating trgtrep.prm

Create a file named trgtrep.prm, and add the following:

-- ##########################################################################################
-- Edit and modify NLS_LANG parameter as required (eg. language.territory.character)
-- Edit and modify ORACLE_HOME
-- Edit and modify Primary ORACLE_SID
-- Edit and modify 'OGGUSER' to Oracle GoldenGate Admin Userid and PW
-- Edit and modify 'SYSADM' to PSACCESSID
-- ##########################################################################################
REPLICAT trgtrep

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/products/oracle/11.2.0.2.0-64bit")
SETENV (ORACLE_SID = "sg112064")

USERID OGGUSER PASSWORD OGGUSER

ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/trgtrep.dsc, APPEND

DISCARDROLLOVER ON SUNDAY
ALLOWNOOPUPDATES

MAP SYSADM.*, TARGET SYSADM.*;

Creating Database Links and Remote Synonyms

This section contains an overview and discusses:

  • creating database links

  • creating remote synonyms.

Understanding Remote Synonyms with DBLINKS for PeopleSoft and GoldenGate

The default GoldenGate configuration is all changes that occur on the Primary get replicated to the Standby. For the PeopleSoft reporting infrastructure to run correctly we would also need to synchronize the following tables back to the Primary from the Standby should any update be made to the following tables:

SYSADM.PSLOCK 
SYSADM.PS_SERVERMONITOR 
SYSADM.PS_SERVERACTVTY 
SYSADM.PS_PRCSSEQUENCE 
SYSADM.PS_MESSAGE_LOGPARM 
SYSADM.PS_MESSAGE_LOG 
SYSADM.PS_AETEMPTBLMGR 
SYSADM.PS_AERUNCONTROLPC 
SYSADM.PS_AERUNCONTROL 
SYSADM.PS_AELOCKMGR 
SYSADM.PSWEBPROFHIST 
SYSADM.PSSERVERSTAT 
SYSADM.PSQRYTRANS 
SYSADM.PSPRCSJOBSTATUS 
SYSADM.PSOPRDEFN 
SYSADM.PSIBSUBSLAVE 
SYSADM.PSIBPUBSLAVE 
SYSADM.PSIBFOLOCK 
SYSADM.PSIBFAILOVER 
SYSADM.PSIBBRKSLAVE 
SYSADM.PSACCESSLOG 
SYSADM.PS_PTFP_ACCESS_LOG 
SYSADM.PS_PTFP_OPTIONS 
SYSADM.PSIBPROFILESYNC 
SYSADM.PSIBLOGHDR 
SYSADM.PSIBLOGERR 
SYSADM.PSIBLOGERRP 
SYSADM.PSIBLOGDATA 
SYSADM.PSIBLOGIBINFO 
SYSADM.PSQASRUN 
SYSADM.PSPRCSRQST 
SYSADM.PSPRCSQUE 
SYSADM.PSPRCSRQSTFILE 
SYSADM.PSPRCSPARMS 
SYSADM.PSPRCSRQSTTEXT 
SYSADM.PSPRCSRQSTTEXT2 
SYSADM.PS_CDM_LIST 
SYSADM.PS_CDM_TRANSFER 
SYSADM.PS_CDM_AUTH 
SYSADM.PS_BAT_TIMINGS_LOG 
SYSADM.PS_BAT_TIMINGS_DTL 
SYSADM.PS_AE_TIMINGS_LG 
SYSADM.PS_AE_TIMINGS_DT 
SYSADM.PS_BAT_TIMINGS_FN 
SYSADM.PSQRYFAVORITES 
SYSADM.PSQRYSTATS 
SYSADM.PSFILE_ATTDET 
SYSADM.PSPTFILE_REF 
SYSADM.PSPTFILE_WART 
SYSADM.PS_PTSF_SCHED_STAT 
SYSADM.PSPGVIEWOPT 
SYSADM.PSPGCHARTOPT 
SYSADM.PSPGCHRTFLRSOPT 
SYSADM.PSPGDISPOPT 
SYSADM.PSPGGRIDOPT 
SYSADM.PSPGQRYPROMPT 
SYSADM.PSPGCHARTOPTLNG 
SYSADM.PSPGVIEWOPTLANG 
SYSADM.PSPTFILE_PRCS 
SYSADM.PSUSEROBJTYPE 
SYSADM.PSUSERSRCHDEFN 
SYSADM.PSUSERPRSNLOPTN 
SYSADM.PSVERSION 
SYSADM.PSUSRTAPAGECUST 
SYSADM.PSBATCHAUTH 
SYSADM.PSBATCHAUTHLONG 
SYSADM.PSPRCSCHLDINFO 
SYSADM.PS_PTNVSLYTQRY 
SYSADM.PSTREESELNUM 
SYSADM.PSTREESELCTL 
SYSADM.PSNVSBATCHRSTRT 
SYSADM.PSNVSDRILLQRY 
SYSADM.PSNVSDRLPROMPTS 
SYSADM.PS_CDM_TEXT 
SYSADM.PS_PRCSRQSTDIST 
SYSADM.PSTREESELECT01 
SYSADM.PSTREESELECT02 
SYSADM.PSTREESELECT03 
SYSADM.PSTREESELECT04 
SYSADM.PSTREESELECT05 
SYSADM.PSTREESELECT06 
SYSADM.PSTREESELECT07 
SYSADM.PSTREESELECT08 
SYSADM.PSTREESELECT09 
SYSADM.PSTREESELECT10 
SYSADM.PSTREESELECT11 
SYSADM.PSTREESELECT12 
SYSADM.PSTREESELECT13 
SYSADM.PSTREESELECT14 
SYSADM.PSTREESELECT15 
SYSADM.PSTREESELECT16 
SYSADM.PSTREESELECT17 
SYSADM.PSTREESELECT18 
SYSADM.PSTREESELECT19 
SYSADM.PSTREESELECT20 
SYSADM.PSTREESELECT21 
SYSADM.PSTREESELECT22 
SYSADM.PSTREESELECT23 
SYSADM.PSTREESELECT24 
SYSADM.PSTREESELECT25 
SYSADM.PSTREESELECT26 
SYSADM.PSTREESELECT27 
SYSADM.PSTREESELECT28 
SYSADM.PSTREESELECT29 
SYSADM.PSTREESELECT30

Normally in a GoldenGate configuration you use GoldenGate to replicate changes made on these tables from the standby to the primary database. PeopleSoft cannot take advantage of GoldenGate's bi-directional replication mechanism due to possible issues in the PeopleSoft reporting infrastructure in an environment where both databases are active at all times. The PeopleSoft reporting infrastructure (Process Scheduler, PSAESRV, PSPRCSRV, DISTSRV, and so on) will not accommodate database synchronization lag time with sequence numbers and instance numbers. To address this issue when using GoldenGate, PeopleSoft needs to utilize Remote Synonyms and Database Links to redirect all DML requested on the standby database to the primary database for a subset of the tables that make up the PeopleSoft reporting infrastructure.

Creating Database Links to the Primary Database

To create database links to the primary database:

  1. Drop ‘Like’ named redirected tables in the standby database.

    Prior to creating the database links and the remote synonyms, you need to drop ‘like’ named redirected tables on the standby database by running the following script.

    PS_HOME/scripts/dropredirectedtables.sql

    Run this script against the standby database connected as the primary access ID in SQLPlus.

  2. Edit the createdblinktoprimary.sql script.

    This script creates a fixed database link between the primary access ID on the standby database to the primary access ID on the primary database. Open the createdblinktoprimary.sql script in PS_HOME\scripts, and modify it as follows:

    • Replace <DBNAME> with the primary database TNSALIAS.

    • <SYSADM> with the primary access ID.

    • <PASSWORD> with the primary access ID password.

  3. Run the createdblinktoprimary.sql script.

    Run this script against the standby database connected as the primary access ID.

Creating Remote Synonyms

Remote synonyms are required for the tables identified as requiring DML access. If you decide to set a component to "read-only" after your analysis, then you need to include the underlying tables that require DML access to this script manually.

To create remote synonyms, you run the PS_HOME/scripts/createremotesynonyms.sql script. You must run this script against the standby database connected as the primary access ID.

Configuring Oracle GoldenGate for PeopleSoft

This section describes:

  • Configuring the primary and standby databases.

  • Starting the GoldenGate processes.

Configuring the Primary and Standby Databases

When working with each database, ensure that the ORACLE_HOME and ORACLE_SID environment variables are set before invoking the GoldenGate command line interpreter (GGSCI). Make sure you invoke GGSCI from $OGG_HOME or add $OGG_HOME to the $PATH environment variable.

To configure the primary and standby databases:

  1. Add supplemental log groups to the primary database.

    GGSCI > obey ./priaddtrndata.
  2. Configure the GoldenGate processes on the primary database.

    GGSCI > obey ./configure_primary.oby
  3. Configure the GoldenGate processes on the standby database.

    GGSCI > obey ./configure_standby.oby

Starting the GoldenGate Processes on the Primary and Standby Databases

To start the GoldenGate processes on the primary and standby databases:

  1. Issue the following commands on the primary database server to start the GoldenGate processes:

    GGSCI > start manager
    GGSCI> start primecap
    GGSCI > info all
    
  2. Issue the following commands on the standby database server to start the GoldenGate processes:

    GGSCI > start manager
    GGSCI > info all
    
  3. Issue the following command on the primary database server to start the GoldenGate pump processes.

    GGSCI> start primepmp
    GGSCI> info all
    
  4. Issue the following command on the standby database server to start the GoldenGate repository processes.

    GGSCI> start trgtrep
    GGSCI> info all

Configuring PeopleSoft to Work with GoldenGate

To set up your PeopleSoft system to recognize both the primary and standby databases, as well to recognize the components and processes that will use the GoldenGate implementation, you need to:

  • Specify the standby database information in the application server and Process Scheduler configuration files.

  • Configure read-only components.

  • Configure read-only processes.

The procedures for performing these tasks are identical to those for setting up Oracle ADG.

Note: Its important to understand the inherited behavior for marking a component as RO. If an initial component is marked as RO and this component calls other components, the called components will inherit the RO flag. That is, not only will the initial components SQL be redirected to the STANDBY database, all the SQL from the called components will also be redirected to the STANDBY database. Oracle has accounted for tables that may be involved with DML SQL operations from the supported components and processes listed in Understanding GoldenGate Within PeopleSoft, by including those tables in the CREATEREMOTESYNONYM.SQL script. If additional components are marked RO and perform DML SQL operations on any tables not previously accounted for, then REMOTE SYNONYMS must be created for those tables as well.