Sunday, October 29, 2017

Step-by-Step Setup of Oracle GoldenGate 12.2 for Oracle


Oracle GoldenGate for Oracle DB Setup

Goal: Real-time data replication
·         From Solaris (Sparc 5.11) to Red Hat Enterprise Linux (7.4)
·         From Oracle DB 11.2.0.4 to Oracle DB 12.1.0.2
·         From Oracle TDE (Transparent Data Encryption) to non-TDE
OGG (Oracle GoldenGate):           12.2.0.2.2   (Integrated EXTRACT and REPLICAT)
Source:      host, srcsvr DB, SRCDB    schema, SRCSCHM
Target:      host, tgtsvr DB, TGTDB    schema, SRCSCHM

Source and Target
Steps 1-8 need to be completed first on both source and target servers
1.     Install OGG
Ø  Source
srcsvr:oracle> mkdir -p /u01/app/oracle/ogg122
srcsvr:oracle> unzip -d /u01/app/oracle/ogg122 122022_fbo_ggs_Solaris_sparc_shiphome.zip
srcsvr:oracle> export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
srcsvr:oracle> export ORACLE_SID=SRCDB
srcsvr:oracle> export TNS_ADMIN=$ORACLE_HOME/network/admin
srcsvr:oracle> export LD_LIBRARY_PATH=$ORACLE_HOME/lib
srcsvr:oracle> /u01/app/oracle/ogg122/fbo_ggs_Solaris_sparc_shiphome/Disk1/runInstaller
  Oracle Goldengate for Oracle Database 11g (558.0MB)
  Software Location: /u01/app/oracle/ogg122
  Start Manager: Uncheck
  Log: /u01/oraInventory/logs/installActions2017-10-03_09-22-00AM.log
srcsvr:oracle> vi /home/oracle/.bash_profile
export GGS_HOME=/u01/app/oracle/ogg122
  export PATH=$ORACLE_HOME/bin:$GGS_HOME:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$GGS_HOME

Ø  Target
[tgtsvr:oracle]$  mkdir -p /u01/app/oracle/ogg122
[tgtsvr:oracle]$  unzip -d /u01/app/oracle/ogg122 122022_fbo_ggs_Linux_x64_shiphome.zip
[tgtsvr:oracle]$  export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[tgtsvr:oracle]$  export ORACLE_SID=TGTDB
[tgtsvr:oracle]$  export TNS_ADMIN=$ORACLE_HOME/network/admin
[tgtsvr:oracle]$  export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[tgtsvr:oracle]$  /u01/app/oracle/ogg122/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller
  Oracle Goldengate for Oracle Database 12c (1009.0MB)
  Software Location: /u01/app/oracle/ogg122
  Start Manager: Uncheck
  Log: /u01/app/oraInventory/logs/installActions2017-10-03_09-53-31AM.log
[tgtsvr:oracle]$  vi /home/oracle/.bash_profile
export GGS_HOME=/u01/app/oracle/ogg122
   export PATH=$ORACLE_HOME/bin:$GGS_HOME:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$GGS_HOME
2.     Create subdirs
Ø  Source
srcsvr:oracle> ggsci
GGSCI (srcsvr) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg122
Parameter files                /u01/app/oracle/ogg122/dirprm: created
Report files                   /u01/app/oracle/ogg122/dirrpt: created
Checkpoint files               /u01/app/oracle/ogg122/dirchk: created
Process status files           /u01/app/oracle/ogg122/dirpcs: created
SQL script files               /u01/app/oracle/ogg122/dirsql: created
Database definitions files     /u01/app/oracle/ogg122/dirdef: created
Extract data files             /u01/app/oracle/ogg122/dirdat: created
Temporary files                /u01/app/oracle/ogg122/dirtmp: created
Credential store files         /u01/app/oracle/ogg122/dircrd: created
Masterkey wallet files         /u01/app/oracle/ogg122/dirwlt: created
Dump files                     /u01/app/oracle/ogg122/dirdmp: created

GGSCI (srcsvr) 2> show all
Parameter settings:
SET SUBDIRS    ON
SET DEBUG      OFF
Current directory: /u01/app/oracle/ogg122
Using subdirectories for all process files
Editor:  vi
Reports (.rpt)                 /u01/app/oracle/ogg122/dirrpt
Parameters (.prm)              /u01/app/oracle/ogg122/dirprm
Replicat Checkpoints (.cpr)    /u01/app/oracle/ogg122/dirchk
Extract Checkpoints (.cpe)     /u01/app/oracle/ogg122/dirchk
Process Status (.pcs)          /u01/app/oracle/ogg122/dirpcs
SQL Scripts (.sql)             /u01/app/oracle/ogg122/dirsql
Database Definitions (.def)    /u01/app/oracle/ogg122/dirdef
Dump files (.dmp)              /u01/app/oracle/ogg122/dirdmp
Masterkey wallet files (.wlt)  /u01/app/oracle/ogg122/dirwlt
Credential store files (.crd)  /u01/app/oracle/ogg122/dircrd

Ø  Target
[tgtsvr:oracle]$ ggsci
GGSCI (tgtsvr) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg122
Parameter files                /u01/app/oracle/ogg122/dirprm: created
Report files                   /u01/app/oracle/ogg122/dirrpt: created
Checkpoint files               /u01/app/oracle/ogg122/dirchk: created
Process status files           /u01/app/oracle/ogg122/dirpcs: created
SQL script files               /u01/app/oracle/ogg122/dirsql: created
Database definitions files     /u01/app/oracle/ogg122/dirdef: created
Extract data files             /u01/app/oracle/ogg122/dirdat: created
Temporary files                /u01/app/oracle/ogg122/dirtmp: created
Credential store files         /u01/app/oracle/ogg122/dircrd: created
Masterkey wallet files         /u01/app/oracle/ogg122/dirwlt: created
Dump files                     /u01/app/oracle/ogg122/dirdmp: created
GGSCI (tgtsvr) 2> show all
Parameter settings:
SET SUBDIRS    ON
SET DEBUG      OFF
Current directory: /u01/app/oracle/ogg122
Using subdirectories for all process files
Editor:  vi
Reports (.rpt)                 /u01/app/oracle/ogg122/dirrpt
Parameters (.prm)              /u01/app/oracle/ogg122/dirprm
Replicat Checkpoints (.cpr)    /u01/app/oracle/ogg122/dirchk
Extract Checkpoints (.cpe)     /u01/app/oracle/ogg122/dirchk
Process Status (.pcs)          /u01/app/oracle/ogg122/dirpcs
SQL Scripts (.sql)             /u01/app/oracle/ogg122/dirsql
Database Definitions (.def)    /u01/app/oracle/ogg122/dirdef
Dump files (.dmp)              /u01/app/oracle/ogg122/dirdmp
Masterkey wallet files (.wlt)  /u01/app/oracle/ogg122/dirwlt
Credential store files (.crd)  /u01/app/oracle/ogg122/dircrd

3.     Create parameter file for OGG manager, mgr.prm, on both source and target hosts
GGSCI (srcsvr) 1> edit param mgr
PORT 15000
dynamicportlist 15010-15014
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MINKEEPFILES 3
AUTOSTART ER *
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 1, RESETMINUTES 30
LAGREPORTMINUTES 5
LAGCRITICALMINUTES 15
       GGSCI (tgtsvr) 1> view param mgr
PORT 15000
dynamicportlist 15010-15014
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MINKEEPFILES 3
AUTOSTART ER *
AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 1, RESETMINUTES 30
LAGREPORTMINUTES 5
LAGCRITICALMINUTES 15
  
4.     Create checkpoint table in both source and target DBs
srcsvr:oracle> cd $GGS_HOME
srcsvr:oracle> sqlplus ogguser/OGG#admin#2017@srcdb
SQL> @chkpt_ora_create.sql
5.     Create parameter file, GLOBALS, in $GGS_HOME on both source and target host
srcsvr:oracle> cd $GGS_HOME
srcsvr:oracle> vi GLOBALS         (With the following contents)
GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.GGS_CHECKPOINT
6. Start OGG manager on both source and target hosts
GGSCI (srcsvr) 1> info mgr
Manager is DOWN!
GGSCI (srcsvr) 2> start mgr
Manager started.
GGSCI (srcsvr) 3> info mgr
 Manager is running (IP port srcsvr.15000, Process ID 32297).
7.      Enable OGG replication at DB level in both source and target DBs
SQL> alter system set enable_goldengate_replication=TRUE sid='*' scope=both;
SQL> sho parameter goldengate
NAME                          TYPE    VALUE
----------------------------- ------- -----
enable_goldengate_replication boolean TRUE
8.     Create credentialstore and add the OGG user
Ø  Source
GGSCI (srcsvr) 1> info credentialstore
ERROR: Unable to open credential store in ./dircrd/.
GGSCI (srcsvr) 2> add credentialstore
Credential store created in ./dircrd/.
GGSCI (srcsvr) 3> info credentialstore
Reading from ./dircrd/:
No information found in credential store.
GGSCI (srcsvr) 4> alter credentialstore add user ogguser@ogg_ext password OGG#admin#2017 alias ogguser
Credential store in ./dircrd/ altered.
GGSCI (srcsvr) 5> info credentialstore
Reading from ./dircrd/:
Default domain: OracleGoldenGate
  Alias: ogguser
  Userid: ogguser@ogg_ext
GGSCI (srcsvr) 6> dblogin useridalias ogguser
Successfully logged into database.
srcsvr:oracle> ls -lh $GGS_HOME/dircrd/
   -rw-r-----   1 oracle   dba  533 Oct  3 10:18 cwallet.sso

Ø  Target
GGSCI (tgtsvr) 1> info credentialstore
ERROR: Unable to open credential store in ./dircrd/.
GGSCI (tgtsvr) 2> add credentialstore
Credential store created in ./dircrd/.
GGSCI (tgtsvr) 3> info credentialstore
Reading from ./dircrd/:
No information found in credential store.
GGSCI (tgtsvr) 4> alter credentialstore add user ogguser@ogg_rep password OGG#admin#2017 alias ogguser
Credential store in ./dircrd/ altered.
GGSCI (tgtsvr) 5> info credentialstore
Reading from ./dircrd/:
Default domain: OracleGoldenGate
  Alias: ogguser
  Userid: ogguser@ogg_rep
GGSCI (tgtsvr) 6> dblogin useridalias ogguser
Successfully logged into database.
[oracle@tgtsvr]$ ls -lh $GGS_HOME/dircrd
   -rw-rw-r--+ 1 oracle oinstall 533 Oct  3 10:27 cwallet.sso


Source OGG Server
9.     Confirm source host and DB
srcsvr:oracle> uname -a
       SunOS waivoem01 5.11 11.3 sun4v sparc sun4v
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
10.  Add a dedicated TNS entry for OGG to tnsnames.ora
OGG_EXT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srcsvr)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRCDB)
    )
  )
11.  Enable Supplemental Logging for EXTRACT in source DB
        SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
        SQL> ALTER DATABASE FORCE LOGGING;
        SQL> ALTER SYSTEM SWITCH LOGFILE;
        SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
              SUPPLEME FORCE_LOGGING
              -------- -------------
            YES          YES
12.  Create tablespace and OGG user
SQL> create tablespace ogguser_dat datafile size 100M autoextend on next 10M maxsize 1G blocksize 8K;
SQL> create user ogguser identified by OGG#admin#2017 default tablespace ogguser_dat quota        unlimited on ogguser_dat;
 SQL> exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'ogguser',privilege_type=>'capture',grant_select_privileges=>true,do_grants=>TRUE);
13.  Create replication source tablespace, schema and table
SQL> create tablespace srcschm_dat datafile size 200M autoextend on next 10M maxsize unlimited encryption using ‘AES128’ default storage (encrypt) blocksize 8K;
SQL> create user srcschm identified by OGG#source#2017 default tablespace srcschm_dat quota unlimited on srcschm_dat;
SQL> grant connect, resource, create table to srcschm;
SQL> alter user srcschm default role all;
SQL> CREATE TABLE SRCSCHM.OGG_TEST_TB
 (OPS_NUM  NUMBER               NOT NULL,
  OPS_DT   TIMESTAMP(9)         DEFAULT CURRENT_TIMESTAMP,
  OPS      VARCHAR2(10 BYTE),
  OPS_MSG  VARCHAR2(100 BYTE));
SQL> CREATE UNIQUE INDEX SRCSCHM.OGG_TEST_TB_PK ON SRCSCHM.OGG_TEST_TB (OPS_NUM);
SQL> ALTER TABLE SRCSCHM.OGG_TEST_TB ADD (
  CONSTRAINT OGG_TEST_TB_PK
  PRIMARY KEY
  (OPS_NUM)
  USING INDEX SRCSCHM.OGG_TEST_TB_PK
  ENABLE VALIDATE);

SQL> GRANT DELETE, INSERT, SELECT, UPDATE ON SRCSCHM.OGG_TEST_TB TO OGGUSER;

14.  Set up TranData option
By default, Oracle DB logs only those column values that change. Before starting capturing real-time data, the source DB must be set to log the table key values whenever it logs row changes, by enabling supplemental logging for the specified tables, so that they are available to OGG in Redo, and hence OGG can locate the correct rows on the target for update and delete operations.

GGSCI (srcsvr as ogguser@SRCDB) 1> add TranData SRCSCHM.* ALLCOLS
GGSCI (srcsvr as ogguser@SRCDB) 2> info TranData SRCSCHM.*

2017-10-03 11:20:51  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema SRCSCHM.

2017-10-03 11:20:52  INFO    OGG-01980  Schema level supplemental logging is enabled on schema SRCSCHM for all scheduling columns.

Logging of supplemental redo log data is enabled for table SRCSCHM.OGG_TEST_TB.

Columns supplementally logged for table SRCSCHM.OGG_TEST_TB: OPS, OPS_DT, OPS_MSG, OPS_NUM.

Prepared CSN for table SRCSCHM.OGG_TEST_TB: 2869575
Logging of supplemental redo log data is enabled for table SRCSCHM.RS_CLAIM_HEADER.

15.  Create parameter file for integrated EXTRACT
GGSCI (srcsvr as ogguser@SRCDB) 1> edit param SRCSCHMext
extract SRCSCHMext
useridalias ogguser
TranlogOptions IntegratedParams (max_sga_size 256)
exttrail ./dirdat/ex
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE SRCSCHM.*;

       GGSCI (srcsvr as ogguser@SRCDB) 2> view param SRCSCHMext
extract SRCSCHMext
useridalias ogguser
TranlogOptions IntegratedParams (max_sga_size 256)
exttrail ./dirdat/ex
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE SRCSCHM.*;

16.  Register the integrated EXTRACT, create EXTRACT group and local EXTRACT trail file
GGSCI (srcsvr)1> dblogin useridalias ogguser
       Successfully logged into database.
GGSCI (srcsvr as ogguser@SRCDB) 2> register extract SRCSCHMext database
       2017-10-03 11:22:43 INFO OGG-02003 Extract SRCSCHMEXT successfully registered with database at SCN 2868073.
GGSCI (srcsvr as ogguser@SRCDB) 3> add extract SRCSCHMext, integrated tranlog, begin now       EXTRACT (Integrated) added.
GGSCI (srcsvr as ogguser@SRCDB) 4> add exttrail ./dirdat/ex, extract SRCSCHMext, megabytes 50     EXTTRAIL added.

17. Set up OGG Data Pump
-    Create data pump parameter file SRCSCHMdp
GGSCI (srcsvr as ogguser@SRCDB) 4> edit param SRCSCHMdp
extract SRCSCHMdp
UserIdAlias ogguser
rmthost tgtsvr, mgrport 15000, compress, timeout 30
rmttrail ./dirdat/rp
passthru
TABLE SRCSCHM.*;

GGSCI (srcsvr as ogguser@SRCDB) 5> view param SRCSCHMdp
extract SRCSCHMdp
UserIdAlias ogguser
rmthost tgtsvr, mgrport 15000, compress, timeout 30
rmttrail ./dirdat/rp
passthru
TABLE SRCSCHM.*;
-    Add data pump process and associate it with source local extract trail file
   GGSCI (srcsvr as ogguser@SRCDB) 6> add extract SRCSCHMdp, exttrailsource ./dirdat/ex
EXTRACT added.
-    Associate data pump process with remote trail file on target
GGSCI (srcsvr as ogguser@SRCDB) 7> add rmttrail ./dirdat/rp, extract SRCSCHMdp, megabytes 50
RMTTRAIL added.
GGSCI (srcsvr as ogguser@SRCDB) 8> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     SRCSCHMDP     00:00:00      00:00:17
EXTRACT     STOPPED     SRCSCHMEXT    00:00:00      00:01:53

18.  Start the primary EXTRACT and DataPump EXTRACT
GGSCI (srcsvr as ogguser@SRCDB) 10> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     SRCSCHMDP     00:00:00      00:19:00
EXTRACT     STOPPED     SRCSCHMEXT    00:46:23      00:02:54
GGSCI (srcsvr as ogguser@SRCDB) 11> start SRCSCHMext
Sending START request to MANAGER ...
EXTRACT SRCSCHMEXT starting

GGSCI (srcsvr as ogguser@SRCDB) 12> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     SRCSCHMDP     00:00:00      00:22:16
EXTRACT     RUNNING     SRCSCHMEXT    00:00:00      00:00:09

-          Ensure OGG manager is running on target.

GGSCI (srcsvr as ogguser@SRCDB) 13> start SRCSCHMdp
Sending START request to MANAGER ...
EXTRACT SRCSCHMDP starting
GGSCI (srcsvr as ogguser@SRCDB) 14> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     SRCSCHMDP     00:00:00      00:00:00
EXTRACT     RUNNING     SRCSCHMEXT    00:00:00      00:00:01
GGSCI (srcsvr as ogguser@SRCDB) 15> info SRCSCHMext
EXTRACT    SRCSCHMEXT  Last Started 2017-10-03 11:30   Status RUNNING
Checkpoint Lag       00:00:09 (updated 00:00:06 ago)
Process ID           44571
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-10-03 11:31:24
                     SCN 0.2890057 (2890057) Check tail file on target
srcsvr:oracle:/u01/app/oracle/ogg122>ls -lh dirdat/
       -rw-r-----  1 oracle  dba   1.3K Oct  3 11:30 ex000000000



  Target OGG Server

19. Confirm target host and DB
[tgtsvr:oracle]$  uname -a
                Linux tgtsvr 3.10.0-693.2.2.el7.x86_64 #1 SMP Sat Sep 9 03:55:24 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux
SQL> select banner from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - ProductionCORE 12.1.0.2.0  Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 – Production

20.  Add a dedicate TNS entry for OGG to tnsnames.ora
OGG_REP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tgtsvr)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TGTDB)
    )
  ) 
21.  Create replication tablespace, schema and table

SQL> create tablespace tgtschm_dat datafile size 200M autoextend on next 10M maxsize unlimited blocksize 8K;
SQL> create user tgtschm identified by OGG#source#2017 default tablespace tgtschm_dat quota unlimited on tgtschm_dat;
SQL> grant connect, resource, create table to tgtschm;
SQL> alter user tgtschm default role all;
SQL> CREATE TABLE TGTSCHM.OGG_TEST_TB
 (OPS_NUM  NUMBER               NOT NULL,
  OPS_DT   TIMESTAMP(9)         DEFAULT CURRENT_TIMESTAMP,
  OPS      VARCHAR2(10 BYTE),
  OPS_MSG  VARCHAR2(100 BYTE));
SQL> CREATE UNIQUE INDEX TGTSCHM.OGG_TEST_TB_PK ON TGTSCHM.OGG_TEST_TB (OPS_NUM);
SQL> ALTER TABLE TGTSCHM.OGG_TEST_TB ADD (
  CONSTRAINT OGG_TEST_TB_PK
  PRIMARY KEY
  (OPS_NUM)
  USING INDEX TGTSCHM.OGG_TEST_TB_PK
  ENABLE VALIDATE);

22. Create REPLICAT parameter file, TGTSCHMrep
GGSCI (tgtsvr) 1> edit param TGTSCHMrep
replicat TGTSCHMREP
DBOPTIONS INTEGRATEDPARAMS(parallelism 2)
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/TGTSCHM.dsc, append, megabytes 10
useridalias ogguser
Map SRCSCHM.*, Target TGTSCHM.*;

GGSCI (tgtsvr) 2> view param TGTSCHMrep
replicat TGTSCHMREP
DBOPTIONS INTEGRATEDPARAMS(parallelism 2)
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/SRCSCHM.dsc, append, megabytes 10
useridalias ogguser
Map SRCSCHM.*, Target TGTSCHM.*;


23.  Add and start the REPLICAT

GGSCI (tgtsvr) 3> dblogin useridalias ogguser
Successfully logged into database.
GGSCI (tgtsvr as ogguser@TGTDB) 4> add replicat TGTSCHMrep Integrated, exttrail ./dirdat/rp
REPLICAT (Integrated) added.
GGSCI (tgtsvr as ogguser@TGTDB) 5> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     TGTSCHMREP    00:00:00      00:00:10
GGSCI (tgtsvr as ogguser@TGTDB) 6> start TGTSCHMrep
Sending START request to MANAGER ...
REPLICAT TGTSCHMREP starting
GGSCI (tgtsvr as ogguser@TGTDB) 7> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TGTSCHMREP    00:00:00      00:00:04

GGSCI (tgtsvr as ogguser@TGTDB) 8> info TGTSCHMrep
REPLICAT   TGTSCHMREP  Last Started 2017-10-03 11:39   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           2782
Log Read Checkpoint  File ./dirdat/rp000000000
2017-10-0311:44:12.534426  RBA 0

-          Note: The Status should be RUNNING, and the time since the last update or checkpoint should be under 10 seconds.

oracle@tgtsvr> ls -lh dirdat/
   -rw-rw-r--+ 1 oracle oinstall 0 Oct  3 11:35 rp000000000

24. Test data replication
Ø  Source
SQL> select * from SRCSCHM.OGG_TEST_TB;
       no rows selected
Ø  Target
SQL> select * from TGTSCHM.OGG_TEST_TB;
no rows selected
Ø  Source
SQL> insert into SRCSCHM.OGG_TEST_TB values (1,current_timestamp,'Insert','The first insert');
SQL> insert into SRCSCHM.OGG_TEST_TB values (2,current_timestamp,'Insert','The 2nd insert');
SQL> insert into SRCSCHM.OGG_TEST_TB values (3,current_timestamp,'Insert','The 3rd test insert by Kyle');
SQL> commit;
SQL> select * from SRCSCHM.OGG_TEST_TB;
OPS_NUM OPS_DT                           OPS     OPS_MSG
-------- -------------------------------- ------- -------------------
       1 03-OCT-17 11.49.43.083448000 PM  Insert  The first insert
       2 03-OCT-17 11.50.29.401382000 PM  Insert  The 2nd insert here
       3 03-OCT-17 11.51.36.319982000 PM  Insert  3rd insert by Kyle
3 rows selected.
SQL> select owner,table_name,tb.tablespace_name,encrypted from dba_tables tb, dba_tablespaces ts where table_name='OGG_TEST_TB' and tb.tablespace_name=ts.tablespace_name;
OWNER TABLE_NAME     TABLESPACE_NAME       ENC
----- -------------- --------------------- ---
SRCSCHM OGG_TEST_TB  ENCRYPT_SRCSCHM_D_NP2 YES
SQL> select apply_name,apply_captured,status, to_char(status_change_time,'HH24:MI mm-dd')        chg_dt,message_delivery_mode msg_del_mode,purpose from dba_apply;
APPLY_NAME      APP STATUS   CHG_DT      MSG_DEL_MO PURPOSE
--------------- --- -------- ----------- ---------- ------------------
OGG$SRCSCHMEXT  YES ENABLED  11:50 10-03 CAPTURED   GoldenGate Capture
GGSCI (srcsvr as ogguser@SRCDB) 1> info *
EXTRACT    SRCSCHMDP   Last Started 2017-10-03 11:45   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           45274
Log Read Checkpoint  File ./dirdat/ex000000000
                     2017-10-03 11:50:46.000000  RBA 2430

EXTRACT    SRCSCHMEXT  Last Started 2017-10-03 11:40   Status RUNNING
Checkpoint Lag       00:00:09 (updated 00:00:08 ago)
Process ID           44571
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-10-03 12:37:06
                     SCN 0.2892514 (2892514)
srcsvr:oracle:/u01/app/oracle/ogg122>ls -lh dirdat/
       -rw-r-----   1 oracle   dba         2.4K Oct  3 11:50 ex000000000

Ø  Target
oracle@tgtsvr> ls -lh dirdat/
      -rw-rw-r--+ 1 oracle oinstall 2.5K Oct  4 11:50 rp000000000
SQL> select * from TGTSCHM.OGG_TEST_TB;
OPS_NUM OPS_DT                              OPS        OPS_MSG
------- ----------------------------------- ---------- ------------------
      1 03-OCT-17 11.50.22.853063000 AM     Insert     The first insert
      2 03-OCT-17 11.50.30.565370000 AM     Insert     The 2nd insert here
      3 04-OCT-17 11.50.40.805242000 AM     Insert     3rd insert by Kyle
SQL> select replicat_name,server_name,status from dba_goldengate_inbound;
REPLICAT_N SERVER_NAME     STATUS
---------- --------------- --------
SRCSCHMREP   OGG$SRCSCHMREP    ATTACHED
SQL> select owner,table_name,tb.tablespace_name,encrypted from dba_tables tb, dba_tablespaces ts where     table_name='OGG_TEST_TB' and tb.tablespace_name=ts.tablespace_name;
OWNER   TABLE_NAME  TABLESPACE_NAME ENC
------- ----------- --------------- ---
TGTSCHM OGG_TEST_TB TGTSCHA_DAT     NO
SQL> select apply_name,apply_captured,status, to_char(status_change_time,'HH24:MI mm-dd')     chg_dt,message_delivery_mode msg_del_mode,purpose from dba_apply;
APPLY_NAME     APP STATUS   CHG_DT      MSG_DEL_MO PURPOSE
-------------- --- -------- ----------- ---------- ----------------
OGG$TGTSCHMREP YES ENABLED  11:51 10-03 CAPTURED   GoldenGate Apply


GGSCI (tgtsvr) 14> info TGTSCHMrep
REPLICAT   TGTSCHMREP  Last Started 2017-10-03 11:39   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           2782
Log Read Checkpoint  File ./dirdat/rp000000000
                     2017-10-03 12:36:15.965273  RBA 2486

posted by Kyle H @ October 29, 2017   0 Comments

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home