Implementing Oracle GoldenGate for PeopleSoft Off-Load Reporting

This section contains an overview and discusses steps to configure Oracle Golden Gate for PeopleSoft Off-Load reporting.

Note: The scripts given below are for Oracle 12c container database.

Note: For non container database replace C##OGGUSER with OGGUSER.

Oracle GoldenGate, with Oracle Database 11g or higher, 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.

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). The access ID and access ID passwords must be the same on the primary and standby databases For example, if you use EMDBO as your access ID on the primary database, you must also use EMDBO as the access ID on the standby database, and you must keep the passwords for EMDBO the same on both databases.

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

  • 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.

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).

    <@hostname:>$ cd \data1\ogg\primary
<@hostname:>$ ./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 (<@hostname>) 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:

    <@hostname:>$ cd \data1\ogg\primary
<@hostname:>$ ./ggsci

GGSCI (<@hostname>) 2> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

GGSCI (<@hostname>) 3>

This section explains the 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:

On Primary DB
SQL> create user C##OGGUSER identified by OGGUSER;

User created.

-------------------------------------------------------------

SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGGUSER',container=>'all');

PL/SQL procedure successfully completed.

--####Change the pluggable database#####
SQL> alter session set container=PT855GA;

Session altered.

GRANT CONNECT, RESOURCE to C##OGGUSER;
GRANT CREATE SESSION to C##OGGUSER;
GRANT ALTER SESSION to C##OGGUSER;
GRANT SELECT ANY DICTIONARY to C##OGGUSER;
GRANT FLASHBACK ANY TABLE to C##OGGUSER;
GRANT ALTER ANY TABLE to C##OGGUSER;
GRANT SELECT ANY TABLE to C##OGGUSER;
GRANT INSERT ANY TABLE to C##OGGUSER;
GRANT DELETE ANY TABLE to C##OGGUSER;
GRANT UPDATE ANY TABLE to C##OGGUSER;
GRANT CREATE TABLE to C##OGGUSER;
GRANT UNLIMITED TABLESPACE to C##OGGUSER;
GRANT EXECUTE on DBMS_FLASHBACK to C##OGGUSER;
GRANT SELECT ON dba_clusters to C##OGGUSER

Grant succeeded.
On Standby DB
SQL> create user C##OGGUSER identified by OGGUSER;
User created.

SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGGUSER',container=>'all');

PL/SQL procedure successfully completed.

SQL> alter session set container=STD855GA;

Session altered.

SQL> GRANT CONNECT, RESOURCE to C##OGGUSER;
GRANT CREATE SESSION to C##OGGUSER;
GRANT ALTER SESSION to C##OGGUSER; 
GRANT SELECT ANY DICTIONARY to C##OGGUSER;
GRANT FLASHBACK ANY TABLE to C##OGGUSER;
GRANT ALTER ANY TABLE to C##OGGUSER;
GRANT SELECT ANY TABLE to C##OGGUSER;
GRANT INSERT ANY TABLE to C##OGGUSER;
GRANT DELETE ANY TABLE to C##OGGUSER;
GRANT UPDATE ANY TABLE to C##OGGUSER;
GRANT CREATE TABLE to C##OGGUSER;
GRANT UNLIMITED TABLESPACE to C##OGGUSER;
GRANT EXECUTE on DBMS_FLASHBACK to C##OGGUSER;
GRANT SELECT ON dba_clusters to C##OGGUSER;

Note: For non container database replace C##OGGUSER with OGGUSER.

Listing the Privileges Granted to the GoldenGate User

To list the privileges granted to the OGG User, you can run the following script for a container database:

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('C##OGGUSER')
 /* THE ROLES TO ROLES RELATIONS */ 
union
   select 
  grantee,
granted_role
 from
dba_role_privs
)
start with grantee is null
connect by grantee = prior granted_role;

Append the following script to the script above, if you are using non-container database:

  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs

The above script generates following result:

C##OGGUSER
ALTER ANY TABLE
ALTER SESSION
CREATE ANY EDITION
CREATE EVALUATION CONTEXT
CREATE JOB
CREATE RULE
CREATE RULE SET
DEQUEUE ANY QUEUE
DROP ANY EDITION
EXECUTE ANY RULE SET
FLASHBACK ANY TABLE
LOGMINING
SELECT ANY TABLE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE

16 rows selected.

Check if the parameter enable_goldengate_replication is set to True.

SQL> show parameter enable_goldengate_replication
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     FALSE

On both the Primary and Standby Database Login as Sysdba and set GoldenGate replication to True.

SQL> alter system set enable_goldengate_replication=TRUE;
System altered.

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:

Login to Primary DB
set ORACLE_SID=CDBPSFT1
sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount pfile='C:\PDBATTACH\admin\CDBPSFT1\initCDBPSFT1.ora'
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  3047720 bytes
Variable Size            1207963352 bytes
Database Buffers          922746880 bytes
Redo Buffers               13725696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

The following step is only for container database.

--#####Lists the pluggable databases in a container####
SQL> show pdbs 
CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED                  READ ONLY     NO
 3 PT855GA                   READ WRITE    NO

Viewing Archive Logging

To view the archive logging status:

SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle12c\product\12.1.0\dbhome_1\RDBMS
Oldest online log sequence     25
Next log sequence to archive   27
Current log sequence           27

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;
Database altered.

ALTER SYSTEM SWITCH LOGFILE;
System altered.

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
Database altered.

SQL>
System altered.

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

SQL> exit

Verifying the PSDBOWNER Table on the Standby Database

On your standby database, verify the table contents for the PSDBOWNER table. If you've cloned your standby database from the primary database, you may need to update the PSDBOWNER.DBNAME field to reflect the standby database's tnsnames alias. For example, if your newly cloned standby database's name is PSFTSTBY, then the PSDBOWNER table's DBNAME field should be PSFTSTBY:

SQL> SELECT * FROM PS.PSDBOWNER;

DBNAME   	OWNERID
-------- 	--------
PSFTSTBY 	EMDBO

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.

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.

##########################################################################################
-- 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 EMDBO.* 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 EMDBO.* statement
-- ##########################################################################################
-- 
-- Edit and modify 'OGGUSER' to Oracle GoldenGate Admin Userid and PW
--  
-- Edit and modify 'EMDBO' to PSACCESSID
-- 
-- Append the output from the PSGGgeneratetrandatadeletes.txt (eg. just the generated 
-- DELETE TRANDATA       -- statements) after the ADD TRANDATA EMDBO .* statement
--
dblogin userid C##OGGUSER@PT855GA password OGGUSER
ADD TRANDATA EMDBO.*
-- ##########################################################################################
-- This section lists the generated DELETE TRANDATA  statements 
-- ##########################################################################################
--<Copy in the generated DELETE TRANDATA statements after the preceding ADD TRANDDATA statement.>
DELETE TRANDATA emdbo.PS_MENU_LANG_TMP
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM1
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM10
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM2
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM3
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM4
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM5
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM6
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM7
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM8
DELETE TRANDATA emdbo.PS_PSMSFTMPCOM9
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS1
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS10
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS2
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS3
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS4
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS5
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS6
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS7
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS8
DELETE TRANDATA emdbo.PS_PSMSFTMPIDS9
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL1
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL10
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL2
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL3
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL4
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL5
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL6
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL7
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL8
DELETE TRANDATA emdbo.PS_PSMSFTMPTBL9
DELETE TRANDATA emdbo.PS_PSPMCSOAETEMP
DELETE TRANDATA emdbo.PS_PTPP_CPKP_TMP
DELETE TRANDATA emdbo.PS_PTPP_CPKP_TMP1
 
-- ##########################################################################################
-- This section lists the required static DELETE TRANDATA statements 
-- ##########################################################################################

DELETE TRANDATA EMDBO.PSLOCK
DELETE TRANDATA EMDBO.PSRECDEFN 
DELETE TRANDATA EMDBO.PS_SERVERMONITOR
DELETE TRANDATA EMDBO.PS_SERVERACTVTY
DELETE TRANDATA EMDBO.PS_PRCSSEQUENCE
DELETE TRANDATA EMDBO.PS_MESSAGE_LOGPARM
DELETE TRANDATA EMDBO.PS_MESSAGE_LOG
DELETE TRANDATA EMDBO.PS_AETEMPTBLMGR
DELETE TRANDATA EMDBO.PS_AERUNCONTROLPC
DELETE TRANDATA EMDBO.PS_AERUNCONTROL
DELETE TRANDATA EMDBO.PS_AELOCKMGR
DELETE TRANDATA EMDBO.PSWEBPROFHIST
DELETE TRANDATA EMDBO.PSSERVERSTAT
DELETE TRANDATA EMDBO.PSQRYTRANS
DELETE TRANDATA EMDBO.PSPRCSJOBSTATUS
DELETE TRANDATA EMDBO.PSOPRDEFN
DELETE TRANDATA EMDBO.PSIBSUBSLAVE
DELETE TRANDATA EMDBO.PSIBPUBSLAVE
DELETE TRANDATA EMDBO.PSIBFOLOCK
DELETE TRANDATA EMDBO.PSIBFAILOVER
DELETE TRANDATA EMDBO.PSIBBRKSLAVE
DELETE TRANDATA EMDBO.PSACCESSLOG
DELETE TRANDATA EMDBO.PS_PTFP_ACCESS_LOG
DELETE TRANDATA EMDBO.PS_PTFP_OPTIONS
DELETE TRANDATA EMDBO.PSIBPROFILESYNC
DELETE TRANDATA EMDBO.PSIBLOGHDR
DELETE TRANDATA EMDBO.PSIBLOGERR
DELETE TRANDATA EMDBO.PSIBLOGERRP
DELETE TRANDATA EMDBO.PSIBLOGDATA
DELETE TRANDATA EMDBO.PSIBLOGIBINFO
DELETE TRANDATA EMDBO.PSQASRUN
DELETE TRANDATA EMDBO.PSPRCSRQST
DELETE TRANDATA EMDBO.PSPRCSQUE
DELETE TRANDATA EMDBO.PSPRCSRQSTFILE
DELETE TRANDATA EMDBO.PSPRCSPARMS
DELETE TRANDATA EMDBO.PSPRCSRQSTTEXT
DELETE TRANDATA EMDBO.PSPRCSRQSTTEXT2
DELETE TRANDATA EMDBO.PS_CDM_LIST
DELETE TRANDATA EMDBO.PS_CDM_TRANSFER
DELETE TRANDATA EMDBO.PS_CDM_AUTH
DELETE TRANDATA EMDBO.PS_BAT_TIMINGS_LOG
DELETE TRANDATA EMDBO.PS_BAT_TIMINGS_DTL
DELETE TRANDATA EMDBO.PS_AE_TIMINGS_LG
DELETE TRANDATA EMDBO.PS_AE_TIMINGS_DT
DELETE TRANDATA EMDBO.PS_BAT_TIMINGS_FN
DELETE TRANDATA EMDBO.PSQRYFAVORITES
DELETE TRANDATA EMDBO.PSQRYSTATS
DELETE TRANDATA EMDBO.PSFILE_ATTDET
DELETE TRANDATA EMDBO.PSPTFILE_REF
DELETE TRANDATA EMDBO.PSPTFILE_WART
DELETE TRANDATA EMDBO.PS_PTSF_SCHED_STAT
DELETE TRANDATA EMDBO.PSPGVIEWOPT
DELETE TRANDATA EMDBO.PSPGCHARTOPT
DELETE TRANDATA EMDBO.PSPGCHRTFLRSOPT
DELETE TRANDATA EMDBO.PSPGCHTFLRSLANG
DELETE TRANDATA EMDBO.PSPGDISPOPT
DELETE TRANDATA EMDBO.PSPGGRIDOPT
DELETE TRANDATA EMDBO.PSPGQRYPROMPT
DELETE TRANDATA EMDBO.PSPGQRYPROMPLNG
DELETE TRANDATA EMDBO.PSPGCHARTOPTLNG
DELETE TRANDATA EMDBO.PSPGVIEWOPTLANG
DELETE TRANDATA EMDBO.PSPGVIEWOPTPERS
DELETE TRANDATA EMDBO.PSPGAXISPERS
DELETE TRANDATA EMDBO.PSPGAXISPERSLNG
DELETE TRANDATA EMDBO.PSPGCHARTOPTPER
DELETE TRANDATA EMDBO.PSPGQRYPRMPTPER
DELETE TRANDATA EMDBO.PSPGGRIDOPTPERS
DELETE TRANDATA EMDBO.PSPGCHTOPTPERLN
DELETE TRANDATA EMDBO.PSPGQRYPRMPTPLN
DELETE TRANDATA EMDBO.PSPGVWOPTPERLN
DELETE TRANDATA EMDBO.PSPGCORE
DELETE TRANDATA EMDBO.PSPGCORELANG
DELETE TRANDATA EMDBO.PSPGMODEL
DELETE TRANDATA EMDBO.PSPGMODELLANG
DELETE TRANDATA EMDBO.PSPGSETTINGS
DELETE TRANDATA EMDBO.PSPGAXIS
DELETE TRANDATA EMDBO.PSPGTHRESHOLDLN
DELETE TRANDATA EMDBO.PSPGNUIOPT
DELETE TRANDATA EMDBO.PSPGNUIDIMOPT
DELETE TRANDATA EMDBO.PSPGSAVEHIST 
DELETE TRANDATA EMDBO.PSPTFILE_PRCS
DELETE TRANDATA EMDBO.PSUSEROBJTYPE
DELETE TRANDATA EMDBO.PSUSERSRCHDEFN
DELETE TRANDATA EMDBO.PSUSERPRSNLOPTN
DELETE TRANDATA EMDBO.PSVERSION
DELETE TRANDATA EMDBO.PSUSRTAPAGECUST
DELETE TRANDATA EMDBO.PSBATCHAUTH
DELETE TRANDATA EMDBO.PSBATCHAUTHLONG
DELETE TRANDATA EMDBO.PSPRCSCHLDINFO
DELETE TRANDATA EMDBO.PS_PTNVSLYTQRY
DELETE TRANDATA EMDBO.PSTREESELNUM
DELETE TRANDATA EMDBO.PSTREESELCTL
DELETE TRANDATA EMDBO.PSNVSBATCHRSTRT
DELETE TRANDATA EMDBO.PSNVSDRILLQRY
DELETE TRANDATA EMDBO.PSNVSDRLPROMPTS
DELETE TRANDATA EMDBO.PS_CDM_TEXT
DELETE TRANDATA EMDBO.PS_PRCSRQSTDIST
DELETE TRANDATA EMDBO.PSTREESELECT01
DELETE TRANDATA EMDBO.PSTREESELECT02
DELETE TRANDATA EMDBO.PSTREESELECT03
DELETE TRANDATA EMDBO.PSTREESELECT04
DELETE TRANDATA EMDBO.PSTREESELECT05
DELETE TRANDATA EMDBO.PSTREESELECT06
DELETE TRANDATA EMDBO.PSTREESELECT07
DELETE TRANDATA EMDBO.PSTREESELECT08
DELETE TRANDATA EMDBO.PSTREESELECT09
DELETE TRANDATA EMDBO.PSTREESELECT10
DELETE TRANDATA EMDBO.PSTREESELECT11
DELETE TRANDATA EMDBO.PSTREESELECT12
DELETE TRANDATA EMDBO.PSTREESELECT13
DELETE TRANDATA EMDBO.PSTREESELECT14
DELETE TRANDATA EMDBO.PSTREESELECT15
DELETE TRANDATA EMDBO.PSTREESELECT16
DELETE TRANDATA EMDBO.PSTREESELECT17
DELETE TRANDATA EMDBO.PSTREESELECT18
DELETE TRANDATA EMDBO.PSTREESELECT19
DELETE TRANDATA EMDBO.PSTREESELECT20
DELETE TRANDATA EMDBO.PSTREESELECT21
DELETE TRANDATA EMDBO.PSTREESELECT22
DELETE TRANDATA EMDBO.PSTREESELECT23
DELETE TRANDATA EMDBO.PSTREESELECT24
DELETE TRANDATA EMDBO.PSTREESELECT25
DELETE TRANDATA EMDBO.PSTREESELECT26
DELETE TRANDATA EMDBO.PSTREESELECT27
DELETE TRANDATA EMDBO.PSTREESELECT28
DELETE TRANDATA EMDBO.PSTREESELECT29
DELETE TRANDATA EMDBO.PSTREESELECT30
DELETE TRANDATA EMDBO.PSFP_FEED
DELETE TRANDATA EMDBO.PSFP_FEED_LANG
DELETE TRANDATA EMDBO.PSFP_SETTINGS
DELETE TRANDATA EMDBO.PSFP_ADMN_PREF
DELETE TRANDATA EMDBO.PSFP_USER_PREF
DELETE TRANDATA EMDBO.PSFP_SECURITY
DELETE TRANDATA EMDBO.PSFP_PUB_SITES
DELETE TRANDATA EMDBO.PSFP_PARMS
DELETE TRANDATA EMDBO.PSFP_PARMS_LANG
DELETE TRANDATA EMDBO.PSFP_PVALS
DELETE TRANDATA EMDBO.PSFP_PVALS_LANG
DELETE TRANDATA EMDBO.PSFP_ATTRS
DELETE TRANDATA EMDBO.PSFP_ATTRS_LANG
DELETE TRANDATA EMDBO.PSFP_FEED_DEL
DELETE TRANDATA EMDBO.PSAPMSGPUBHDR
DELETE TRANDATA EMDBO.PSAPMSGPUBDATA
DELETE TRANDATA EMDBO.PSAPMSGIBATTR
DELETE TRANDATA EMDBO.PSAPMSGSEGATTR
DELETE TRANDATA EMDBO.PSAPMSGPUBSYNC

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 C##OGGUSER password OGGUSER
REGISTER EXTRACT primecap DATABASE  CONTAINER (PT855GA)
ADD EXTRACT primecap INTEGRATED TRANLOG, BEGIN NOW
add exttrail ./dirdat/pt, extract primecap
ADD EXTRACT primepmp EXTTRAILSOURCE ./dirdat/pt BEGIN NOW
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 'EMDBO' to PSACCESSID
--  
-- Copy the output from the PSGGgeneratetableexcludes.txt (eg. just the generated TABLEEXCLUDE  
-- statements) between the WILDCARDRESOLVE DYNAMIC statement and before the TABLE EMDBO.*
-- statement
--
-- ##########################################################################################
EXTRACT primecap

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "C:\oracle12c\product\12.1.0\dbhome_1")
SETENV (ORACLE_SID = CDBPSFT1)

USERID C##OGGUSER PASSWORD OGGUSER

DISCARDFILE ./dirrpt/primecap.dsc, purge

EXTTRAIL ./dirdat/pt
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
SOURCECATALOG PT855GA 
WILDCARDRESOLVE DYNAMIC
TRANLOGOPTIONS USE_ROOT_CONTAINER_TIMEZONE
-- ##########################################################################################
-- This section lists the generated TABLEEXCLUDE statements 
-- ##########################################################################################

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

TABLEEXCLUDE emdbo.PS_MENU_LANG_TMP;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM1;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM10;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM2;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM3;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM4;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM5;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM6;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM7;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM8;
TABLEEXCLUDE emdbo.PS_PSMSFTMPCOM9;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS1;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS10;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS2;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS3;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS4;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS5;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS6;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS7;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS8;
TABLEEXCLUDE emdbo.PS_PSMSFTMPIDS9;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL1;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL10;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL2;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL3;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL4;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL5;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL6;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL7;
ABLEEXCLUDE emdbo.PS_PSMSFTMPTBL8;
TABLEEXCLUDE emdbo.PS_PSMSFTMPTBL9;
TABLEEXCLUDE emdbo.PS_PSPMCSOAETEMP;
TABLEEXCLUDE emdbo.PS_PTPP_CPKP_TMP;
TABLEEXCLUDE emdbo.PS_PTPP_CPKP_TMP1;
 
-- ##########################################################################################
-- This section lists the required static TABLEEXCLUDE statements 
-- ##########################################################################################


TABLEEXCLUDE EMDBO.PSLOCK;
TABLEEXCLUDE EMDBO.PSRECDEFN;
TABLEEXCLUDE EMDBO.PS_SERVERMONITOR;
TABLEEXCLUDE EMDBO.PS_SERVERACTVTY;
TABLEEXCLUDE EMDBO.PS_PRCSSEQUENCE;
TABLEEXCLUDE EMDBO.PS_MESSAGE_LOGPARM;
TABLEEXCLUDE EMDBO.PS_MESSAGE_LOG;
TABLEEXCLUDE EMDBO.PS_AETEMPTBLMGR;
TABLEEXCLUDE EMDBO.PS_AERUNCONTROLPC;
TABLEEXCLUDE EMDBO.PS_AERUNCONTROL;
TABLEEXCLUDE EMDBO.PS_AELOCKMGR;
TABLEEXCLUDE EMDBO.PSWEBPROFHIST;
TABLEEXCLUDE EMDBO.PSSERVERSTAT;
TABLEEXCLUDE EMDBO.PSQRYTRANS;
TABLEEXCLUDE EMDBO.PSPRCSJOBSTATUS;
TABLEEXCLUDE EMDBO.PSOPRDEFN;
TABLEEXCLUDE EMDBO.PSIBSUBSLAVE;
TABLEEXCLUDE EMDBO.PSIBPUBSLAVE;
TABLEEXCLUDE EMDBO.PSIBFOLOCK;
TABLEEXCLUDE EMDBO.PSIBFAILOVER;
TABLEEXCLUDE EMDBO.PSIBBRKSLAVE;
TABLEEXCLUDE EMDBO.PSACCESSLOG;
TABLEEXCLUDE EMDBO.PS_PTFP_ACCESS_LOG;
TABLEEXCLUDE EMDBO.PS_PTFP_OPTIONS;
TABLEEXCLUDE EMDBO.PSIBPROFILESYNC;
TABLEEXCLUDE EMDBO.PSIBLOGHDR;
TABLEEXCLUDE EMDBO.PSIBLOGERR;
TABLEEXCLUDE EMDBO.PSIBLOGERRP;
TABLEEXCLUDE EMDBO.PSIBLOGDATA;
TABLEEXCLUDE EMDBO.PSIBLOGIBINFO;
TABLEEXCLUDE EMDBO.PSQASRUN;
TABLEEXCLUDE EMDBO.PSPRCSRQST;
TABLEEXCLUDE EMDBO.PSPRCSQUE;
TABLEEXCLUDE EMDBO.PSPRCSRQSTFILE;
TABLEEXCLUDE EMDBO.PSPRCSPARMS;
TABLEEXCLUDE EMDBO.PSPRCSRQSTTEXT;
TABLEEXCLUDE EMDBO.PSPRCSRQSTTEXT2;
TABLEEXCLUDE EMDBO.PS_CDM_LIST;
TABLEEXCLUDE EMDBO.PS_CDM_TRANSFER;
TABLEEXCLUDE EMDBO.PS_CDM_AUTH;
TABLEEXCLUDE EMDBO.PS_BAT_TIMINGS_LOG;
TABLEEXCLUDE EMDBO.PS_BAT_TIMINGS_DTL;
TABLEEXCLUDE EMDBO.PS_AE_TIMINGS_LG;
TABLEEXCLUDE EMDBO.PS_AE_TIMINGS_DT;
TABLEEXCLUDE EMDBO.PS_BAT_TIMINGS_FN;
TABLEEXCLUDE EMDBO.PSQRYFAVORITES;
TABLEEXCLUDE EMDBO.PSQRYSTATS;
TABLEEXCLUDE EMDBO.PSFILE_ATTDET;
TABLEEXCLUDE EMDBO.PSPTFILE_REF;
TABLEEXCLUDE EMDBO.PSPTFILE_WART;
TABLEEXCLUDE EMDBO.PS_PTSF_SCHED_STAT;
TABLEEXCLUDE EMDBO.PSPGVIEWOPT;
TABLEEXCLUDE EMDBO.PSPGCHARTOPT;
TABLEEXCLUDE EMDBO.PSPGCHRTFLRSOPT;
TABLEEXCLUDE EMDBO.PSPGCHTFLRSLANG;
TABLEEXCLUDE EMDBO.PSPGDISPOPT;
TABLEEXCLUDE EMDBO.PSPGGRIDOPT;
TABLEEXCLUDE EMDBO.PSPGQRYPROMPT;
TABLEEXCLUDE EMDBO.PSPGQRYPROMPLNG;
TABLEEXCLUDE EMDBO.PSPGCHARTOPTLNG;
TABLEEXCLUDE EMDBO.PSPGVIEWOPTLANG;
TABLEEXCLUDE EMDBO.PSPGVIEWOPTPERS;
TABLEEXCLUDE EMDBO.PSPGAXISPERS;
TABLEEXCLUDE EMDBO.PSPGAXISPERSLNG;
TABLEEXCLUDE EMDBO.PSPGCHARTOPTPER;
TABLEEXCLUDE EMDBO.PSPGQRYPRMPTPER;
TABLEEXCLUDE EMDBO.PSPGGRIDOPTPERS;
TABLEEXCLUDE EMDBO.PSPGCHTOPTPERLN;
TABLEEXCLUDE EMDBO.PSPGQRYPRMPTPLN;
TABLEEXCLUDE EMDBO.PSPGVWOPTPERLN;
TABLEEXCLUDE EMDBO.PSPGCORE;
TABLEEXCLUDE EMDBO.PSPGCORELANG;
TABLEEXCLUDE EMDBO.PSPGMODEL;
TABLEEXCLUDE EMDBO.PSPGMODELLANG;
TABLEEXCLUDE EMDBO.PSPGSETTINGS;
TABLEEXCLUDE EMDBO.PSPGAXIS;
TABLEEXCLUDE EMDBO.PSPGTHRESHOLDLN;
TABLEEXCLUDE EMDBO.PSPGNUIOPT;
TABLEEXCLUDE EMDBO.PSPGNUIDIMOPT;
TABLEEXCLUDE EMDBO.PSPGSAVEHIST;
TABLEEXCLUDE EMDBO.PSPTFILE_PRCS;
TABLEEXCLUDE EMDBO.PSUSEROBJTYPE;
TABLEEXCLUDE EMDBO.PSUSERSRCHDEFN;
TABLEEXCLUDE EMDBO.PSUSERPRSNLOPTN;
TABLEEXCLUDE EMDBO.PSVERSION;
TABLEEXCLUDE EMDBO.PSUSRTAPAGECUST;
TABLEEXCLUDE EMDBO.PSBATCHAUTH;
TABLEEXCLUDE EMDBO.PSBATCHAUTHLONG;
TABLEEXCLUDE EMDBO.PSPRCSCHLDINFO;
TABLEEXCLUDE EMDBO.PS_PTNVSLYTQRY;
TABLEEXCLUDE EMDBO.PSTREESELNUM;
TABLEEXCLUDE EMDBO.PSTREESELCTL;
TABLEEXCLUDE EMDBO.PSNVSBATCHRSTRT;
TABLEEXCLUDE EMDBO.PSNVSDRILLQRY;
TABLEEXCLUDE EMDBO.PSNVSDRLPROMPTS;
TABLEEXCLUDE EMDBO.PS_CDM_TEXT;
TABLEEXCLUDE EMDBO.PS_PRCSRQSTDIST;
TABLEEXCLUDE EMDBO.PSTREESELECT01;
TABLEEXCLUDE EMDBO.PSTREESELECT02;
TABLEEXCLUDE EMDBO.PSTREESELECT03;
TABLEEXCLUDE EMDBO.PSTREESELECT04;
TABLEEXCLUDE EMDBO.PSTREESELECT05;
TABLEEXCLUDE EMDBO.PSTREESELECT06;
TABLEEXCLUDE EMDBO.PSTREESELECT07;
TABLEEXCLUDE EMDBO.PSTREESELECT08;
TABLEEXCLUDE EMDBO.PSTREESELECT09;
TABLEEXCLUDE EMDBO.PSTREESELECT10;
TABLEEXCLUDE EMDBO.PSTREESELECT11;
TABLEEXCLUDE EMDBO.PSTREESELECT12;
TABLEEXCLUDE EMDBO.PSTREESELECT13;
TABLEEXCLUDE EMDBO.PSTREESELECT14;
TABLEEXCLUDE EMDBO.PSTREESELECT15;
TABLEEXCLUDE EMDBO.PSTREESELECT16;
TABLEEXCLUDE EMDBO.PSTREESELECT17;
TABLEEXCLUDE EMDBO.PSTREESELECT18;
TABLEEXCLUDE EMDBO.PSTREESELECT19;
TABLEEXCLUDE EMDBO.PSTREESELECT20;
TABLEEXCLUDE EMDBO.PSTREESELECT21;
TABLEEXCLUDE EMDBO.PSTREESELECT22;
TABLEEXCLUDE EMDBO.PSTREESELECT23;
TABLEEXCLUDE EMDBO.PSTREESELECT24;
TABLEEXCLUDE EMDBO.PSTREESELECT25;
TABLEEXCLUDE EMDBO.PSTREESELECT26;
TABLEEXCLUDE EMDBO.PSTREESELECT27;
TABLEEXCLUDE EMDBO.PSTREESELECT28;
TABLEEXCLUDE EMDBO.PSTREESELECT29;
TABLEEXCLUDE EMDBO.PSTREESELECT30;
TABLEEXCLUDE EMDBO.PSFP_FEED;
TABLEEXCLUDE EMDBO.PSFP_FEED_LANG;
TABLEEXCLUDE EMDBO.PSFP_SETTINGS;
TABLEEXCLUDE EMDBO.PSFP_ADMN_PREF;
TABLEEXCLUDE EMDBO.PSFP_USER_PREF;
TABLEEXCLUDE EMDBO.PSFP_SECURITY;
TABLEEXCLUDE EMDBO.PSFP_PUB_SITES;
TABLEEXCLUDE EMDBO.PSFP_PARMS;
TABLEEXCLUDE EMDBO.PSFP_PARMS_LANG;
TABLEEXCLUDE EMDBO.PSFP_PVALS;
TABLEEXCLUDE EMDBO.PSFP_PVALS_LANG;
TABLEEXCLUDE EMDBO.PSFP_ATTRS;
TABLEEXCLUDE EMDBO.PSFP_ATTRS_LANG;
TABLEEXCLUDE EMDBO.PSFP_FEED_DEL;
TABLEEXCLUDE EMDBO.PSAPMSGPUBHDR;
TABLEEXCLUDE EMDBO.PSAPMSGPUBDATA;
TABLEEXCLUDE EMDBO.PSAPMSGIBATTR;
TABLEEXCLUDE EMDBO.PSAPMSGSEGATTR;
TABLEEXCLUDE EMDBO.PSAPMSGPUBSYNC;


TABLE EMDBO.*;

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 'EMDBO' to PSACCESSID
-- ##########################################################################################
EXTRACT primepmp
SETENV (ORACLE_SID='CDBPSFT1') 

PASSTHRU
PASSTHRUMESSAGES
USERID C##OGGUSER PASSWORD OGGUSER 
-- Remote Host and Trail Information
RMTHOST <hostname> MGRPORT 7810
RMTTRAIL ./dirdat/pr

-- Table Mapping Parameters
WILDCARDRESOLVE DYNAMIC
SOURCECATALOG PT855GA
TABLE EMDBO.*;

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 C##OGGUSER@STD855GA password OGGUSER 
ADD REPLICAT trgtrep INTEGRATED EXTTRAIL ./dirdat/pr

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 'EMDBO' to PSACCESSID
-- ##########################################################################################
REPLICAT trgtrep

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "C:\oracle12c\product\12.1.0\dbhome_1") 
SETENV (ORACLE_SID = "CDBPSFT1")
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
USERID C##OGGUSER@STD855GA, PASSWORD OGGUSER 

ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/trgtrep.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
ALLOWNOOPUPDATES

MAP PT855GA.EMDBO.*, TARGET STD855GA.EMDBO.*;

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:

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

    • <EMDBO> 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.

    Please make sure that the access ID has "create public database link" privileges or else the above create database link will fail with ORA-01031: insufficient privileges . Grant the CREATE PUBLIC DATABASE LINK privilege to the access id on the standby database:

    $ sqlplus / as sysdba 
SQL> Alter session set container=PT855GA;
Session Altered.
SQL> Grant create public database link to EMDBO;

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 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

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.