Creating Database Links and Remote Synonyms

Understanding Remote Synonyms with DBLINKS for PeopleSoft and Oracle GoldenGate

In the default Oracle GoldenGate configuration, all changes that occur on the source get replicated to the target. For the PeopleSoft reporting infrastructure to run correctly we would also need to synchronize the following tables back to the source from the target 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 Oracle GoldenGate configuration you use Oracle GoldenGate to replicate changes made on these tables from the target to the source database. PeopleSoft cannot take advantage of Oracle 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 Oracle GoldenGate, PeopleSoft needs to utilize Remote Synonyms and Database Links to redirect all data manipulation language (DML) requested on the target database to the source database for a subset of the tables that make up the PeopleSoft reporting infrastructure.

Creating Database Links to the Source Database

To create database links to the source database:

Drop ‘Like’ named redirected tables in the target database.

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

PS_HOME/scripts/dropredirectedtables.sql

Run this script against the target database connected as the primary access ID in SQLPlus.
Edit the createdblinktoprimary.sql script.

This script creates a fixed database link between the primary access ID on the target database to the primary access ID on the source database. Open the createdblinktoprimary.sql script in PS_HOME\scripts, and modify it as follows:
- Replace <DBNAME> with the source database TNSALIAS.
- <EMDBO> with the primary access ID.
- <PASSWORD> with the primary access ID password.
Run the createdblinktoprimary.sql script.

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

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 target 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 target database connected as the primary access ID.