Golden Gate – Part 2 (Configuration)

Continuing my previous post, now we will configure the environment to replicate
data from WINDOWS  to LINUX.
Here we will create the environment on Windows and configure GG for
the first load.

==> SOURCE Environment:

 Windows: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 SID: DB01
 schema: appuser
 
- Objects:

create table appuser.t1 
(num number,
 text varchar2(100),
 dt date)
tablespace ts1;

create table appuser.t2 
(num number,
 text varchar2(100),
 dt date)
tablespace ts1;

create table appuser.t3 
(num number,
 text varchar2(100),
 dt date)
tablespace ts1;

ALTER TABLE appuser.t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (num);
ALTER TABLE appuser.t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (num);
ALTER TABLE appuser.t3 ADD CONSTRAINT pk_t3 PRIMARY KEY (num);

create sequence appuser.seq_t1 
minvalue 1 maxvalue 999999999999 
nocache order increment by 1;

create sequence appuser.seq_t2 
minvalue 1 maxvalue 999999999999 
nocache order increment by 1;

create sequence appuser.seq_t3 
minvalue 1 maxvalue 999999999999 
nocache order increment by 1;

declare 
i number;
begin
 for i in 1..100 loop
 insert into appuser.t1
 values
 (appuser.seq_t1.nextval, 'GOLDEN GATE TEST:' || TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),SYSDATE);

 insert into appuser.t2
 values
 (appuser.seq_t2.nextval, 'GOLDEN GATE TEST:' || TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),SYSDATE);

 insert into appuser.t3
 values
 (appuser.seq_t3.nextval, 'GOLDEN GATE TEST:' || TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),SYSDATE); 
 end loop;
 commit;
end;
/

exec dbms_stats.gather_schema_stats('APPUSER');
select owner, table_name, num_rows 
from dba_tables where owner='APPUSER';

OWNER TABLE_NAME NUM_ROWS
--------------- -------------- ------------
APPUSER T1 100
APPUSER T2 100
APPUSER T3 100

- Archivelog Mode

 Changing Windows Database to Archivelog mode:
 dest: D:\app\candrade\oradata\DB01\ARCH
 
 SQL> alter system set log_archive_dest_1='location=D:\app\candrade\oradata\DB01\ARCH' scope=both;

 System altered.

 SQL> shut immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount;
 ORACLE instance started.

 Total System Global Area 2147483648 bytes
 Fixed Size 3834152 bytes
 Variable Size 671092440 bytes
 Database Buffers 1459617792 bytes
 Redo Buffers 12939264 bytes
 Database mounted.
 SQL> alter database archivelog;

 Database altered.

 SQL> alter database open;

 Database altered.

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination D:\app\candrade\oradata\DB01\ARCH
 Oldest online log sequence 50
 Next log sequence to archive 52
 Current log sequence 52

- Checking TNSNAMES.ora and LISTENER:

 * TNS entry from DB11(linux 11g instance) on Windows
 
 C:\Scripts>tnsping db11

 TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-APR-2016 14:28:05

 Copyright (c) 1997, 2014, Oracle. All rights reserved.

 Used parameter files:
 d:\app\candrade\product\12.1.0\dbhome_1\network\admin\sqlnet.ora


 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.1.36)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db11)))
 OK (0 msec)

==> TARGET Environment:

Linux: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SID: db11
schema:
create tablespace ts_app datafile '/data03/db11/ts_app_01.dbf' size 10m autoextend on next 10m maxsize 8g;
create user appuser identified by appuser default tablespace ts_app;
grant connect, resource to appuser;

- Archivelog Mode

 Changing lunux database 11g to archivelog mode
 dest: /data03/db11/arch

 SQL> alter system set log_archive_dest_1='location=/data03/db11/arch' scope=both;

 System altered.

 SQL> shut immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount;
 ORACLE instance started.

 Total System Global Area 835104768 bytes
 Fixed Size 2217952 bytes
 Variable Size 230688800 bytes
 Database Buffers 595591168 bytes
 Redo Buffers 6606848 bytes
 Database mounted.
 SQL> alter database archivelog;

 Database altered.

 SQL> alter database open;

 Database altered.

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination /data03/db11/arch
 Oldest online log sequence 4
 Next log sequence to archive 6
 Current log sequence 6

 SQL> select owner, table_name, num_rows
 from dba_tables where owner='APPUSER'; 2

 no rows selected

- Checking TNSNAMES.ora
 
 * TNS entry for DB01(windows instance) into TNSNAMES.ORA on WINDOWS and LINUX (11g home)
 
 [oracle@lnx01 admin]$ tnsping DB01IP

 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-APR-2016 08:19:50

 Copyright (c) 1997, 2009, Oracle. All rights reserved.

 Used parameter files:


 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.1.19)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB01)))
 OK (0 msec) 
 
==> Golden Gate configuration

On this moment we have:
 1.) Golden gate installation on Windows 12c, linux DB 11g and linux DB 12c;
 2.) All database instances on ARCHIVELOG mode;
 3.) All tnsnames.ora "pinging" the DBs;

First i would like to make easier our setup creating some alias on Linux and seting up some variables on (Linux and Windows).

- Windows:

 Configure PATH to have GG HOME: C:\Magno\Techs\GoldenGate\home:

 C:\Scripts>ggsci

 Oracle GoldenGate Command Interpreter for Oracle
 Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
 Windows x64 (optimized), Oracle 12c on Dec 18 2015 20:34:51
 Operating system character set identified as windows-1252.

 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

- LINUX
 
 On this case we have 2 databases on Linux (11g and 12c)
 
 [oracle@lnx01 ~]$ alias
 alias db='. /home/oracle/dba/scripts/db_env.sh'
 alias db11='. /home/oracle/dba/scripts/db11_env.sh'
 alias gg11='/oracle/product/GG/11g/ggsci'
 alias gg12='/oracle/product/GG/12c/ggsci'
 alias grid='. /home/oracle/dba/scripts/grid_env.sh'
 
 [oracle@lnx01 scripts]$ cat grid_env.sh
 echo --------------------------------------
 echo GRID INFRASTRUCTURE
 echo --------------------------------------

 export ORACLE_BASE=/oracle
 export ORACLE_SID=+ASM
 export GRID_HOME=/oracle/grid
 export ORACLE_HOME=/oracle/grid

 export PATH=/usr/sbin:$PATH
 export PATH=$ORACLE_HOME/bin:$PATH

 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
 export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
 export DISPLAY=10.130.1.73:0.0
 
 [oracle@lnx01 scripts]$ cat db11_env.sh
 echo ---------------------------
 echo ORACLE DATABASE 11g
 echo --------------------------
 cat /etc/oratab | grep ":/"
 export ORACLE_SID=db11

 echo ""
 echo "ORACLE_SID: " $ORACLE_SID

 export ORACLE_BASE=/oracle
 export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
 export GRID_HOME=/oracle/grid

 export PATH=/usr/sbin:$PATH
 export PATH=$ORACLE_HOME/bin:$PATH:/oracle/product/GG/11g

 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$GG_HOME_11g
 export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
 
 [oracle@lnx01 scripts]$ cat db_env.sh
 echo ---------------------------
 echo ORACLE DATABASE 12c
 echo --------------------------
 cat /etc/oratab | grep ":/"
 export ORACLE_SID=CDB

 echo ""
 echo "ORACLE_SID: " $ORACLE_SID

 export ORACLE_BASE=/oracle
 export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
 export GRID_HOME=/oracle/grid

 export PATH=/usr/sbin:$PATH
 export PATH=$ORACLE_HOME/bin:$PATH:$GG_HOME_12c

 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$GG_HOME_12c
 export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
 
 - Testing:
 
 [oracle@lnx01 scripts]$ db11
 ---------------------------
 ORACLE DATABASE 11g
 --------------------------
 db01:/oracle/product/12.1.0.2/db_1:N
 CDB:/oracle/product/12.1.0.2/db_1:N
 +ASM:/oracle/grid:N # line added by Agent
 db11:/oracle/product/11.2/db_1:N # line added by Agent

 ORACLE_SID: db11
 [oracle@lnx01 scripts]$ gg11

 Oracle GoldenGate Command Interpreter for Oracle
 Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
 Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
 Operating system character set identified as UTF-8.

 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.


 [oracle@lnx01 scripts]$ db
 ---------------------------
 ORACLE DATABASE 12c
 --------------------------
 db01:/oracle/product/12.1.0.2/db_1:N
 CDB:/oracle/product/12.1.0.2/db_1:N
 +ASM:/oracle/grid:N # line added by Agent
 db11:/oracle/product/11.2/db_1:N # line added by Agent

 ORACLE_SID: CDB
 [oracle@lnx01 scripts]$ gg12

 Oracle GoldenGate Command Interpreter for Oracle
 Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
 Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
 Operating system character set identified as UTF-8.

 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
 
1.) Windows Setup:

 - Logging:
 
 sqlplus / as sysdba
 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 ALTER DATABASE FORCE LOGGING;

 SQL> SELECT supplemental_log_data_min, force_logging 
 FROM v$database;

 SUPPLEME FORCE_LOGGING
 -------- ---------------------------------------
 YES YES

 alter system switch logfile;
 
 - GG user:
 https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/user_assignment.htm#GIORA552
 
 create tablespace ts_ggate datafile 'D:\APP\CANDRADE\ORADATA\DB01\ts_ggate_01.dbf' size 8m autoextend on next 8m maxsize 8g;
 create user ggate identified by ggate default tablespace ts_ggate;
 grant connect , resource to ggate;
 grant select any dictionary, select any table to ggate;
 grant create table to ggate;
 grant flashback any table to ggate;
 grant execute on dbms_flashback to ggate;
 grant execute on utl_file to ggate;
 grant dba to ggate;
 
 exec dbms_goldengate_auth.grant_admin_privilege('ggate');
 
 - Enabling GG on Database:
 
 ENABLE_GOLDENGATE_REPLICATION=true
 
 SQL> show parameter golden

 NAME TYPE VALUE
 ------------------------------------ ----------- --------------
 enable_goldengate_replication boolean FALSE
 
 SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;

 System altered.

 SQL> show parameter golden

 NAME TYPE VALUE
 ------------------------------------ ----------- -------------------
 enable_goldengate_replication boolean TRUE
 
 - UNDO:
 By default, Oracle GoldenGate uses Flashback Query to fetch the values from the undo (rollback) tablespaces. 
 That way, Oracle GoldenGate can reconstruct a read-consistent row image as of a 
 specific time or SCN to match the redo record.

 For best fetch results, configure the source database as follows:

 UNDO_MANAGEMENT=AUTO
 UNDO_RETENTION=86400 
 UNDO_RETENTION can be adjusted upward in high-volume environments.
 
 - MEMORY
 https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/setup.htm#GIORA234
 
 set STREAMS_POOL_SIZE at least with 1 GB;
 
 - DBMS_GOLDENGATE
 
 SQL> exec dbms_goldengate_auth.grant_admin_privilege('ggate');

 PL/SQL procedure successfully completed.
 
 - EXTRACT
 
 cd %GG_HOME% 
 # ggsci
 
 DBLOGIN USERID ggate, PASSWORD ggate
 
 ADD CHECKPOINTTABLE ggate.chktable
 EDIT PARAMS ./GLOBALS
 CHECKPOINTTABLE ggate.chktable
 
 edit params EXTWS12C <== EXTRACT for WINDOWS - Oracle 12c
 
 EXTRACT EXTWS12C
 USERID ggate, PASSWORD ggate
 LOGALLSUPCOLS
 UPDATERECORDFORMAT COMPACT
 TRANLOGOPTIONS EXCLUDEUSER ggate
 DDL INCLUDE MAPPED
 DDLOPTIONS ADDTRANDATA, GETREPLICATES, GETAPPLOPS
 BR BROFF
 EOFDELAY 1
 FLUSHSECS 1
 ENCRYPTTRAIL AES192
 EXTTRAIL C:\Magno\Techs\GoldenGate\home\dirdat\lt <== "Create this directory before start"
 SEQUENCE appuser.seq_t1;
 SEQUENCE appuser.seq_t2;
 SEQUENCE appuser.seq_t3;
 TABLE appuser.*;
 
 - DATA PUMP EXTRACT:
 Note: 
 - This port 7810 is not the default, because i have 2 DBs.
 - this directory must be created before start: /oracle/product/GG/11g/dirdat/rt on TARGET system
 - 10.130.1.36 My IP on TARGET system, the same that i have on LISTENER.
 
 EXTRACT PMPWS12C
 -- Specify database login information as needed for the database:
 USERID ggate, PASSWORD ggate
 RMTHOST 10.130.1.36, MGRPORT 7810
 -- Specify the remote trail on the secondary system:
 RMTTRAIL C:\Magno\Techs\GoldenGate\home\dirdat\pn
 PASSTHRU
 -- Specify tables to be captured:
 SEQUENCE appuser.seq_t1;
 SEQUENCE appuser.seq_t2;
 SEQUENCE appuser.seq_t3;
 TABLE appuser.*;
 
 - MGR on Windows:
 
 EDIT PARAMS MGR
 
 PORT 7809
 USERID ggate, PASSWORD ggate
 PURGEOLDEXTRACTS C:\Magno\Techs\GoldenGate\home\dirdat\ex, USECHECKPOINTS
 Autostart Extract E*
 AUTORESTART Extract *, WaitMinutes 1, Retries 3
 Autostart Replicat R*
 AUTORESTART Replicat *, WaitMinutes 1, Retries 3
 
2.) LINUX Setup:

 [oracle@lnx01 rt]$ db11
 ---------------------------
 ORACLE DATABASE 11g
 --------------------------
 db01:/oracle/product/12.1.0.2/db_1:N
 CDB:/oracle/product/12.1.0.2/db_1:N
 +ASM:/oracle/grid:N # line added by Agent
 db11:/oracle/product/11.2/db_1:N # line added by Agent

 ORACLE_SID: db11

 - Logging:
 
 sqlplus / as sysdba
 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 ALTER DATABASE FORCE LOGGING;

 SQL> SELECT supplemental_log_data_min, force_logging 
 FROM v$database;

 SUPPLEME FORCE_LOGGING
 -------- ---------------------------------------
 YES YES

 alter system switch logfile;
 
 - GG user:
 https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/user_assignment.htm#GIORA552
 
 create tablespace ts_ggate datafile '/data03/db11/ts_ggate_01.dbf' size 8m autoextend on next 8m maxsize 8g;
 create user ggate identified by ggate default tablespace ts_ggate;
 grant connect , resource to ggate;
 grant select any dictionary, select any table to ggate;
 grant create table to ggate;
 grant flashback any table to ggate;
 grant execute on dbms_flashback to ggate;
 grant execute on utl_file to ggate;
 grant dba to ggate;
 
 # ggsci
 
 DBLOGIN USERID ggate, PASSWORD ggate
 
 ADD CHECKPOINTTABLE ggate.chktable
 EDIT PARAMS ./GLOBALS
 CHECKPOINTTABLE ggate.chktable
 
 edit params rep11g
 
 REPLICAT rep11g
 ASSUMETARGETDEFS
 DISCARDFILE /oracle/product/GG/11g/discard/rep11g.dsc, PURGE
 USERID ggate, PASSWORD ggate
 REPORTCOUNT EVERY 1 MINUTES, RATE
 DDLOPTIONS UPDATEMETADATA
 DDLERROR DEFAULT IGNORE RETRYOP
 EOFDELAY 1
 MAP appuser.*, TARGET appuser.*;
 
 GGSCI (lnx01 as ggate@db11) 32> info all

 Program Status Group Lag at Chkpt Time Since Chkpt

 MANAGER RUNNING
 
 - MGR on Linux:
 
 EDIT PARAMS MGR
 
 PORT 7810
 USERID ggate, PASSWORD ggate
 PURGEOLDEXTRACTS /oracle/product/GG/11g/dirdat/ex, USECHECKPOINTS
 Autostart Extract E*
 AUTORESTART Extract *, WaitMinutes 1, Retries 3
 Autostart Replicat R*
 AUTORESTART Replicat *, WaitMinutes 1, Retries 3
 ACCESSRULE, PROG REPLICAT, IPADDR 10.130.1.19, ALLOW
 
 - restart
 
 GGSCI (lnx01) 7> stop manager
 Manager process is required by other GGS processes.
 Are you sure you want to stop it (y/n)?y

 Sending STOP request to MANAGER ...
 Request processed.
 Manager stopped.

 GGSCI (lnx01) 8> start manager
 Manager started.

 GGSCI (lnx01) 9> info manager

 Manager is running (IP port lnx01.7810, Process ID 7500).
 
3.) First Load:
 
 - SOURCE:
 
 exp cmagno/pwd file=appuser.dmp rows=n indexes=y grants=y constraints=y owner=appuser
 imp cmagno/pwd@db11 file=appuser.dmp fromuser=appuser touser=appuser ignore=y grants=y constraints=y indexes=y
 
 #GGSCI
 ADD EXTRACT load1, SOURCEISTABLE

 EDIT PARAMS load1

 EXTRACT load1
 USERID ggate, PASSWORD ggate
 RMTHOST 10.130.1.36, MGRPORT 7810
 RMTTASK replicat, GROUP load2
 TABLE appuser.*;

 - Target
 
 #GGSCI
 ADD REPLICAT load2, SPECIALRUN

 EDIT PARAMS load2

 REPLICAT load2
 USERID ggate, PASSWORD ggate
 ASSUMETARGETDEFS
 WILDCARDRESOLVE IMMEDIATE
 MAP appuser.*, TARGET appuser.*;
 
 - Source:
 
 START EXTRACT load1
 
 - Target:
 info replicat load2
 
 sqlplus / as sysdba
 exec dbms_stats.gather_schema_stats('APPUSER');
 SQL> select owner, table_name, num_rows
 from dba_tables where owner='APPUSER';
 2
 OWNER TABLE_NAME NUM_ROWS
 ------------------------------ ------------------------------ ----------
 APPUSER T3 100
 APPUSER T2 100
 APPUSER T1 100
-------------------------------------------------------------------------------
Next.. Configuration for ONGOING replication on GG.. to be continued
Advertisements

One thought on “Golden Gate – Part 2 (Configuration)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s