Preparing Environment for Oracle Golden Gate Extract and Replication Part – ||

In the previous article, we have seen installing Golden Gate software. In this article, we will see

1. Creation of user for  Golden Gate extract process configuration
2. Golden Gate replicat process configuration
3. Enable Archive logging
4. Enable Supplemental Logging

We will start with User Creation

• User Creation at extserver
• User Creation at repserver

First, we will create user extserver : Step 1: Create a tablespace for golden gate user

SQL> create tablespace tbs_ggate datafile size 50m;

Tablespace created.

Step 2: Create a user for Golden Gate configuration

SQL> create user c##ggadmin identified by oracle default tablespace tbs_ggate quota unlimited on tbs_ggate;

User created.

Here I have created a common user as I am using Golden Gate for a multitenant environment. Step 3: Grant necessary privileges for Golden Gate extract process

grant connect,resource to c##ggadmin;
grant alter session to c##ggadmin;
grant select any dictionary, select any table to c##ggadmin;
grant flashback any table to c##ggadmin;
grant execute on utl_file to c##ggadmin;

Now we will create a user at repserver Step 1: Create a tablespace

SQL> create tablespace tbs_ggate datafile size 50m;

Tablespace created.

Step 2: Create a user

SQL> create user c##ggadmin identified by oracle default tablespace tbs_ggate quota unlimited on tbs_ggate;

User created.

Step 3: Grant necessary privileges to replication configuration user

grant connect,resource to c##ggadmin;
grant alter session to c##ggadmin;
grant select any dictionary, select any table to c##ggadmin;
grant create any table to c##ggadmin;
grant insert any table to c##ggadmin;
grant update any table to c##ggadmin;
grant delete any table to c##ggadmin;
grant drop any table to c##ggadmin;

Note: We can see the difference between the privileges of extract and replicat process  Extract process does not need DML operation privileges whereas the replicat process does not need flashback any table privilege. 3. Enable Archivelog: Archivelog mode is must in golden gate configuration so if your database does not have archiving enabled, enable it Archivelog in Oracle Database 4. Enable Force logging

SQL> SELECT FORCE_LOGGING FROM V$DATABASE; 

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FORCE_LOGGING
---------------------------------------
YES

5. Enable supplemental logging.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

6. Set enable_goldengate_replication parameter to TRUE value.

SQL> SHOW PARAMETER ENABLE_GOLDENGATE

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication	     boolean	 FALSE

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;

System altered.

SQL> SHOW PARAMETER ENABLE_GOLDENGATE

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication	     boolean	 TRUE

Now we have set all necessary parameters at the database level. Step 7: Add trandata for objects to be replicated using Golden Gate with the ggsci prompt We can add trandata using ADD TRANDATA or ADD SCHEMATRANDATA ADD TRANDATA :  Add Trandata enables supplemental logging of key values in the transaction logs whenever a row change occurs. If we do not use Add Trandata for objects, DML [update and delete] will fail to be propagated to the target system and Replicat process may abend as by default only those columns are recorded in redo log which is updated. ADD SCHEMATRANDATA :  ADD SCHEMATRANDATA functions as Add Trandata only but using this will give extra functionality as it will also add trandata for future tables which will be created in that schema. Means, Add Trandata will add trandata for existing tables only, and if we create a new table in that schema we need to explicitly add trandata for those tables whereas adding schema trandata will help in this case we don’t need to add trandata for that table explicitly, it will happen implicitly. Note: Oracle strongly recommends using schema-level logging rather than table-level logging, because it ensures that any new tables added to a schema are captured if they satisfy wildcard specifications. So I am going to add SchemaTrandata for my configuration. Before adding SCHEMATRANDATA grant admin privileges to golden gate user :

SQL> exec dbms_streams_auth.grant_admin_privilege('GGS_OWNER');

PL/SQL procedure successfully completed.

GGSCI (localhost.localdomain as ggs_owner@orcl)3> add schematrandata hr

2018-09-27 22:32:25  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "hr".

2018-09-27 22:32:27  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "hr".

2018-09-27 22:32:33  INFO    OGG-10471  ***** Oracle Goldengate support information on table HR.CITY ***** 
Oracle Goldengate support native capture on table HR.CITY.
Oracle Goldengate marked following column as key columns on table HR.CITY: NO
No unique key is defined for table HR.CITY.

2018-09-27 22:32:33  INFO    OGG-10471  ***** Oracle Goldengate support information on table HR.DEMO ***** 
Oracle Goldengate support native capture on table HR.DEMO.
Oracle Goldengate marked following column as key columns on table HR.DEMO: NO
No unique key is defined for table HR.DEMO.

2018-09-27 22:32:33  INFO    OGG-10471  ***** Oracle Goldengate support information on table HR.STATE ***** 
Oracle Goldengate support native capture on table HR.STATE.
Oracle Goldengate marked following column as key columns on table HR.STATE: NO
No unique key is defined for table HR.STATE.

2018-09-27 22:32:33  INFO    OGG-10471  ***** Oracle Goldengate support information on table HR.TEST ***** 
Oracle Goldengate support native capture on table HR.TEST.
Oracle Goldengate marked following column as key columns on table HR.TEST: NO
No unique key is defined for table HR.TEST.

In the next lecture, we will see the configuring manager in Golden Gate If you want to be updated with all our articles send us an Invitation or Follow us: Telegram Channel: https://t.me/helporacle Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/ Joel Perez’s LinkedIn: Joel Perez’s Profile LinkedIn Group: Oracle Cloud DBAAS Facebook Page: OracleHelp