Preparing the PeopleSoft Databases

To prepare the source and target databases to work with Oracle GoldenGate, define parameters and create the necessary users and privileges.

Enabling Oracle GoldenGate Replication

Using SQL*Plus or other utility on both the source and target databases, enter these SQL statements:

1. Check if the parameter enable_goldengate_replication is set to TRUE.

   In this example, it is set to False.

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

2. If it is set to False, log into the database as sysdba and change it to True.

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

Enabling Archive Logging

Enable archive logging in the source database using the following SQL statements in SQL*Plus.

1. Log in to the source database as sysdba.

   set ORACLE_SID=CDBPSFT
   sqlplus / as sysdba

2. Shut down the database.

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

3. Enter the alter commands.

   SQL> startup mount;
   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.

4. To view the archive logging status, enter ARCHIVE LOG LIST:

   SQL> ARCHIVE LOG LIST;
   Database log mode              Archive Mode
   Automatic archival             Enabled
   Archive destination            USE_DB_RECOVERY_FILE_DEST
   Oldest online log sequence     129
   Next log sequence to archive   131
   Current log sequence           131

Creating the Oracle GoldenGate Admin User

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

• Extract (source database)

• Replicat (target database)

• Manager (source database, if using DDL support)

• DEFGEN (source or target database)

Note:

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

Note:

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

Note:

Keep a record of the application database user (PeopleSoft Access ID). It is required in the Oracle 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
Run on DBMS_FLSHBACK package (4) Note: Oracle GoldenGate must make a call to DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.	X

Note:

Be sure to check the most recent installation and administration guides for Oracle GoldenGate based on the Oracle GoldenGate version you are using, as permission requirements may change or be appended.

Create and grant privileges to user oggadmin on the pluggable databases (PDB) for both the source and target with these SQL statements.

SQL> alter session set container=pdbsource;
SQL> create user oggadmin identified by oggadmin;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('oggadmin',container=>'<pdbname>');

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

Enabling Supplemental Logging

Enable supplemental logging on the source database. Using SQL*Plus or other utility, connect as sysdba and submit these SQL statements:

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