Prepare Source Database for Replication

Oracle GoldenGate is a database replication tool that provides a secure, flexible and scalable replication platform to send data to the data warehouse. Before you can replicate data, you must prepare the source database to support Oracle GoldenGate.

Set up your source database to send data to the data warehouse:

1. Connect to your source database with SQL*Plus as a user with admin access.
2. Create the Oracle GoldenGate schema (tablespace/user) and grant the necessary privileges where the database is named pdb1:

   create tablespace ogg_tbs datafile 'ogg_tbs01.dbf' size 100m;
   alter session set container=pdb1;
   create tablespace ogg_tbs datafile 'ogg_tbs02.dbf' size 100m;
   connect to CDB$ROOT
   create user c##ggadmin identified by ADWpass123_# default tablespace ogg_tbs temporary tablespace temp;
   grant dba TO c##ggadmin CONTAINER=all;
   exec dbms_goldengate_auth.grant_admin_privilege('c##ggadmin',container=>'all');

3. Create the source user/schema in the source database pdb1:

   alter session set container=pdb1;
   CREATE user adwsrc IDENTIFIED BY ADWpass123_#;
   grant dba, connect, resource to adwsrc;

4. Make sure that the database is using Archivelog.

   Oracle GoldenGate relies on the redo logs to capture the data that it needs to replicate source transactions. The redo logs on the source system must be configured properly before you start Oracle GoldenGate processing.

   select LOG_MODE from v$database;
   LOG_MODE
   ------------
   ARCHIVELOG
   ALTER DATABASE FORCE LOGGING;
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
   ALTER SYSTEM SET recyclebin=off SCOPE=SPFILE;
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER SYSTEM SET streams_pool_size=1024M scope=both;

5. To create a source data table, connect to your source database with SQL*Plus as ADWSRC and create table rec_data. For example:

   CREATE TABLE "REC_DATA" 
   	("REQ_ID" NUMBER, 
   		"HIS_REF_STATUS" VARCHAR2(50 BYTE), 
   		"REQ_NUMBER" NUMBER, 
   		"TOTAL_OPENING" NUMBER, 
   		"OPENINGS_LEFT_TO_FILL_ACCEPTED" NUMBER, 
   		"OPENINGS_LEFT_TO_FILL_HIRED" NUMBER, 
   		"REQ_BY_RECRUITER" NUMBER, 
   		"REQ_APPROVED" NUMBER, 
   		"REQ_CANCELLED" NUMBER, 
   		"REQ_FILLED" NUMBER, 
   		"REQ_HOLD" NUMBER, 
   		"REQ_SOURCE" VARCHAR2(40 BYTE), 
   		"REQ_SOURCE_TYPE" VARCHAR2(80 BYTE), 
   		"CANDIDATE_FEMALE" NUMBER, 
   		"CANDIDATE_AGE40" NUMBER, 
   		"SUBMISSION" NUMBER, 
   		"SUBMISSION_ACE" NUMBER, 
   		"SUBMISSION_HIRED" NUMBER, 
   		"SUBMISSION_OFFER_EXTENDED" NUMBER, 
   		"SUBMISSION_OFFER_REFUSED" NUMBER, 
   		"SUBMISSION_OFFER_ACCEPTED" NUMBER, 
   		"CANDIDATE" NUMBER, 
   		"COUNTRY" VARCHAR2(60 BYTE), 
   		"STATE" VARCHAR2(60 BYTE), 
   		"REGION" VARCHAR2(60 BYTE), 
   		"CITY" VARCHAR2(60 BYTE), 
   		"ETHNICITY" VARCHAR2(60 BYTE), 
   		"GENDER" VARCHAR2(30 BYTE), 
   		"RACE" VARCHAR2(30 BYTE), 
   		"CURRENT_STATUS_ID" NUMBER, 
   		"CANDIDATE_STATUS" VARCHAR2(40 BYTE), 
   		"STATUS" VARCHAR2(40 BYTE), 
   		"REQ_DIRECT_SOURCE" VARCHAR2(30 BYTE), 
   		"BU_NAME" VARCHAR2(50 BYTE), 
   		"PERFORMANCE" VARCHAR2(40 BYTE), 
   		"HIRING_MANAGER" VARCHAR2(60 BYTE), 
   		"RECRUITER" VARCHAR2(60 BYTE), 
   		"DAYS_OPEN" NUMBER, 
   		"REVENUE" NUMBER(8,2), 
   		"CALLS_VOLUME" NUMBER, 
   		"COST" NUMBER(8,2), 
   		"SERVICE_CENTER" VARCHAR2(40 BYTE), 
   		"OPEN_DATE" DATE, 
   		"OPEN_YEAR" NUMBER, 
   		"OPEN_MONTH_N" NUMBER, 
   		"OPEN_MONTH_W" VARCHAR2(14 BYTE), 
   		"OPEN_QUARTER" VARCHAR2(10 BYTE), 
   		"GEO_REGION" VARCHAR2(10 BYTE), 
   		"REVENUE_PER_TARGET" NUMBER, 
   		"REVENUE_PERCENTAGE_PER_TARGET" NUMBER(5,2), 
   		"SEQUENCE" NUMBER,
   		CONSTRAINT REC_DATA_PK PRIMARY KEY ("REQ_ID")
    		  );