oracle GoldenGate Microservices Architecture 12.3

oracle GoldenGate Microservices Architecture 12.3 

Download goldengate software for microservices file (Oracle GoldenGate 12.3.0.1.2 Microservices for Oracle on Linux x86-64 (443 MB))  in the link 

Database Preparation:

SQL> create user oggadminuser identified by oracle;

User created.

SQL> grant connect,resource to oggadminuser;

Grant succeeded.

SQL> exec dbms_goldengate_auth.grant_admin_privilege('OGGADMINUSER');

PL/SQL procedure successfully completed.

purge sysaux tbs

1. Check the size of the sysaux tablespace items.

SQL>  set linesize 120
set pagesize 100

COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40

SELECT  occupant_name "Item",
    space_usage_kbytes/1048576 "Space Used (GB)",
    schema_name "Schema",
    move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7

Item                      Space Used (GB) Schema                    Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO                                    .04 SYS                       DBMS_AW.MOVE_AWMETA
AUDIT_TABLES                          .00 SYS                       DBMS_AUDIT_MGMT.move_dbaudit_tables
AUDSYS                                .00 AUDSYS
AUTO_TASK                             .00 SYS
EM                                    .00 SYSMAN                    emd_maintenance.move_em_tblspc
EM_MONITORING_USER                    .00 DBSNMP
EXPRESSION_FILTER                     .00 EXFSYS
JOB_SCHEDULER                         .00 SYS
LOGMNR                                .01 SYSTEM                    SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                              .00 SYSTEM                    SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM                                 .00 ORDSYS                    ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA                         .02 ORDDATA                   ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS                      .00 ORDPLUGINS                ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA              .00 SI_INFORMTN_SCHEMA        ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE                              .00 SYS
SDO                                   .08 MDSYS                     MDSYS.MOVE_SDO
SM/ADVISOR                            .01 SYS
SM/AWR                                .04 SYS
SM/OPTSTAT                            .01 SYS
SM/OTHER                              .10 SYS
SMON_SCN_TIME                         .00 SYS
SQL_MANAGEMENT_BASE                   .00 SYS
STATSPACK                             .00 PERFSTAT
STREAMS                               .00 SYS
TEXT                                  .00 CTXSYS                    DRI_MOVE_CTXSYS
TSM                                   .00 TSMSYS
ULTRASEARCH                           .00 WKSYS                     MOVE_WK
ULTRASEARCH_DEMO_USER                 .00 WK_TEST                   MOVE_WK
WM                                    .01 WMSYS                     DBMS_WM.move_proc
XDB                                   .07 XDB                       XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD                                 .00 OLAPSYS                   DBMS_AMD.Move_OLAP_Catalog
XSOQHIST                              .04 SYS                       DBMS_XSOQ.OlapiMoveProc

32 rows selected.

2. To check the retention time.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

3. To change the retention for stats history.

SQL> exec dbms_stats.alter_stats_history_retention(10);

PL/SQL procedure successfully completed.

4. Check the retions changed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         10

5. Purge the stats older than 10days.

SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

PL/SQL procedure successfully completed.

6. check the optstats tables size after purging.

SQL> col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 ascSQL> SQL> SQL> SQL>   2    3    4    5
  6  /

        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRI$_OPTSTAT_SYNOPSIS_PARTGRP            TABLE
         0 WRI$_OPTSTAT_SYNOPSIS_HEAD$              TABLE
         0 WRI$_OPTSTAT_AUX_HISTORY                 TABLE
         0 WRI$_OPTSTAT_IND_HISTORY                 TABLE
         0 WRI$_OPTSTAT_TAB_HISTORY                 TABLE
         0 WRI$_OPTSTAT_OPR                         TABLE
         1 WRI$_OPTSTAT_OPR_TASKS                   TABLE

7 rows selected.

7. check the index size belogs to OPT tables.

SQL> col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/SQL> SQL> SQL> SQL>   2    3    4    5    6

        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 I_OPT_SQLSTAT_LGT                        INDEX
         0 I_OPT_DIRECTIVE_DIROWN#                  INDEX
         0 I_OPT_DIRECTIVE_OWN#                     INDEX
         0 I_WRI$_OPTSTAT_OPR_ID                    INDEX
         0 I_WRI$_OPTSTAT_SYNOPHEAD                 INDEX
         0 I_OPT_FINDING_F_ID                       INDEX
         0 I_OPT_DIRECTIVE_DIRID                    INDEX
         0 I_OPT_FINDING_OBJ_ID_OBJ_TYPE            INDEX
         0 WRH$_PLAN_OPTION_NAME_PK                 INDEX
         0 I_WRI$_OPTSTAT_OPR_STIME                 INDEX
         0 I_WRI$_OPTSTAT_SYNOPPARTGRP              INDEX
         0 WRH$_OPTIMIZER_ENV_PK                    INDEX
         0 PK_OPT_SQLSTAT                           INDEX
         0 I_WRI$_OPTSTAT_AUX_ST                    INDEX
         0 I_WRI$_OPTSTAT_IND_ST                    INDEX
         0 I_WRI$_OPTSTAT_TAB_ST                    INDEX
         0 I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
         0 I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
         0 I_WRI$_OPTSTAT_OPR_TASKS_OPID            INDEX
         0 I_WRI$_OPTSTAT_OPR_TASKS_STIME           INDEX
         0 I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ           INDEX
         1 I_WRI$_OPTSTAT_H_ST                      INDEX
         1 I_WRI$_OPTSTAT_OPR_TASKS_TGST            INDEX
         1 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
         1 I_WRI$_OPTSTAT_HH_ST                     INDEX
         2 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX

26 rows selected.

9. To reclaim the space in sysaux tablespace, we need reorg OPT tables.

SQL> select 'alter table '||segment_name||'  move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'  2  ;

'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACESYSAUX;'
------------------------------------------------------------------------------------------------------------------------
alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR  move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR_TASKS  move tablespace SYSAUX;
alter table OPT_FINDING$  move tablespace SYSAUX;
alter table OPT_FINDING_OBJ$  move tablespace SYSAUX;
alter table OPT_DIRECTIVE_OWN$  move tablespace SYSAUX;
alter table OPT_DIRECTIVE$  move tablespace SYSAUX;
alter table WRH$_OPTIMIZER_ENV  move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME  move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP  move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$  move tablespace SYSAUX;

13 rows selected.

10. To reclaim the space in sysaux tablespace, we need reorg OPT Index.

SQL> select 'alter index '||segment_name||'  rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'  2
  3  ;

'ALTERINDEX'||SEGMENT_NAME||'REBUILDONLINEPARALLEL(DEGREE14);'
------------------------------------------------------------------------------------------------------------------------
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_AUX_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_STIME  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_ID  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_TGST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPID  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_STIME  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ  rebuild online parallel (degree 14);
alter index I_OPT_FINDING_F_ID  rebuild online parallel (degree 14);
alter index I_OPT_FINDING_OBJ_ID_OBJ_TYPE  rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_OWN#  rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIRID  rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIROWN#  rebuild online parallel (degree 14);
alter index PK_OPT_SQLSTAT  rebuild online parallel (degree 14);
alter index I_OPT_SQLSTAT_LGT  rebuild online parallel (degree 14);
alter index WRH$_OPTIMIZER_ENV_PK  rebuild online parallel (degree 14);
alter index WRH$_PLAN_OPTION_NAME_PK  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPPARTGRP  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPHEAD  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST  rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_ST  rebuild online parallel (degree 14);

26 rows selected.

11. Finally check the items size in the sysaux occupents.

SQL>  set linesize 120
set pagesize 100

COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40

SELECT  occupant_name "Item",
    space_usage_kbytes/1048576 "Space Used (GB)",
    schema_name "Schema",
    move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7

Item                      Space Used (GB) Schema                    Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO                                    .04 SYS                       DBMS_AW.MOVE_AWMETA
AUDIT_TABLES                          .00 SYS                       DBMS_AUDIT_MGMT.move_dbaudit_tables
AUDSYS                                .00 AUDSYS
AUTO_TASK                             .00 SYS
EM                                    .00 SYSMAN                    emd_maintenance.move_em_tblspc
EM_MONITORING_USER                    .00 DBSNMP
EXPRESSION_FILTER                     .00 EXFSYS
JOB_SCHEDULER                         .00 SYS
LOGMNR                                .01 SYSTEM                    SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                              .00 SYSTEM                    SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM                                 .00 ORDSYS                    ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA                         .02 ORDDATA                   ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS                      .00 ORDPLUGINS                ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA              .00 SI_INFORMTN_SCHEMA        ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE                              .00 SYS
SDO                                   .08 MDSYS                     MDSYS.MOVE_SDO
SM/ADVISOR                            .01 SYS
SM/AWR                                .04 SYS
SM/OPTSTAT                            .01 SYS
SM/OTHER                              .10 SYS
SMON_SCN_TIME                         .00 SYS
SQL_MANAGEMENT_BASE                   .00 SYS
STATSPACK                             .00 PERFSTAT
STREAMS                               .00 SYS
TEXT                                  .00 CTXSYS                    DRI_MOVE_CTXSYS
TSM                                   .00 TSMSYS
ULTRASEARCH                           .00 WKSYS                     MOVE_WK
ULTRASEARCH_DEMO_USER                 .00 WK_TEST                   MOVE_WK
WM                                    .01 WMSYS                     DBMS_WM.move_proc
XDB                                   .07 XDB                       XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD                                 .00 OLAPSYS                   DBMS_AMD.Move_OLAP_Catalog
XSOQHIST                              .04 SYS                       DBMS_XSOQ.OlapiMoveProc

32 rows selected.

Listener Tracing

To find the issues with the listener connections you may not get complete info in listener log, you need to trace the the listener to monitor the connections, below steps will show you how to enable and disable trace.


[oracle@localhost trace]$ lsnrctl

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-DEC-2017 08:38:21

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener LISTENER

Current Listener is LISTENER

LSNRCTL> set trc_level ADMIN

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
LISTENER parameter "trc_level" set to admin
The command completed successfully

LSNRCTL> show trc_directory

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
LISTENER parameter "trc_directory" set to /u01/app/oracle/diag/tnslsnr/localhost/listener/trace
The command completed successfully

LSNRCTL> show trc_file

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
LISTENER parameter "trc_file" set to ora_9516_140216592200064.trc
The command completed successfully

LSNRCTL> set trc_level off

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
LISTENER parameter "trc_level" set to off
The command completed successfully

LSNRCTL> exit

[oracle@localhost trace]$ ls -lrth
total 212K
-rw-r-----. 1 oracle oinstall 27K Dec 14 08:41 ora_9516_140216592200064.trm
-rw-r-----. 1 oracle oinstall 93K Dec 14 08:41 ora_9516_140216592200064.trc
-rw-r-----. 1 oracle oinstall 19K Dec 14 08:41 listener.log

statistics lock in oracle

To lock statistics for a tables:

exec dbms_stats.lock_table_stats('<schema_name>', '<tablename>');

Ex:

exec dbms_stats.lock_table_stats('scott', 'test');

when stats is locked the value of stattype_locked is ALL in dba_tab_statistics;

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null; 

To unlock table stats:

exec dbms_stats.unlock_table_stats('table_owner','table_name');

ex:

exec dbms_stats.unlock_table_stats('scott', 'test')

To lock statistics for a schema:

exec dbms_stats.lock_schema_stats('schema_owner'); 
exec dbms_stats.unlock_schema_stats('schema_owner'); 

goldengate 12.3 installation in linux

Download software from the link

unzip the file 123010_fbo_ggs_Linux_x64_shiphome

root@localhost opt]# unzip 123010_fbo_ggs_Linux_x64_shiphome

root@localhost opt]# su - oracle

[oracle@localhost Disk1]$ pwd

/opt/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@localhost Disk1]$ ls

install  response  runInstaller  stage

[oracle@localhost Disk1]$ ./runInstaller

[oracle@localhost ogg]$ ./ggsci

./ggsci: error while loading shared libraries: libnnz12.so: cannot open shared object file: No such file or directory

[oracle@localhost oracle]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/lib

[oracle@localhost lib]$ ls libnnz12.so

libnnz12.so

[oracle@localhost lib]$ cd /u01/app/oracle/ogg/

[oracle@localhost ogg]$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so .

[oracle@localhost ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13

Operating system character set identified as UTF-8.

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

GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

example for goldengate unidirectional replication through Datapump process

Steps for performing an Online change Synchronization through Datapump process

====================================================

Make sure to create a same table structure on SRC and TRGT and manager process must be running .

Sample Table structure:

===============

Create table sender.empl (empid number(10),empname varchar2(10),constraint emp_key primary key(empid));

Create table receiver.empl (empid number(10),empname varchar2(10),constraint emp_key primary key(empid));

Prerequisite:

========

 In SRC:

Create a directories  “ mkdir /u01/app/oracle/GG/dirdat/etrail/“

In TRGT:

                              “ mkdir /u01/app/oracle/GG/dirdat/ptrail/“  for trail file’s location.

 On SRC

1. GGSCI> add extract extdml, tranlog, Begin now

2. GGSCI> add exttrail /u01/app/oracle/GG/dirdat/etrail/et,extract extdml

     3.  GGSCI> add extract dpump, EXTTRAILSOURCE /u01/app/oracle/GG/dirdat/etrail/et

    4.   GGSCI> edit params extdml

                 extract extdml

                 userid gguser@SRC,password oracle

                 exttrail /u01/app/oracle/GG/dirdat/etrail/et

                 table sender.empl;

5.  GGSCI> add rmttrail /u01/app/oracle/GG/dirdat/ptrail/rt, extract dpump

6. GGSCI> edit params dpump

                      Extract dpump

                      userid gguser@SRC,password oracle

                      rmthost 192.168.162.13,mgrport 7810

                      rmttrail /u01/app/oracle/GG/dirdat/ptrail/rt

                      passthru

                      table sender.empl;

On TRGT

1.    GGSCI> add replicat repldml, exttrail /u01/app/oracle/ GG/dirdat/dptrail/rt,checkpointtable gguser.chkptab

2.    GGSCI> edit params repldml

                        replicat repldml

                        userid gguser@TRGT,password oracle

                        ASSUMETARGETDEFS

                        MAP sender.empl, TARGET receiver.empl;

    

    On SRC

1. GGSCI > start extract extdml

 2. GGSCI> start extract dpump

    

    On TRGT

     1.  GGSCI> start replicat repldml    

Do some insert and update on SRC table which should be reflected on TRGT automatically.

====================================================

Schema Replication through Datapump

Sample Table structure:

===============

Create table sender.empl (empid number(10),empname varchar2(10),constraint emp_key unique(empid));

Create table receiver.empl (empid number(10),empname varchar2(10),constraint emp_key unique(empid));

Create table sender.company (city varchar2(10),pincode number(10),constraint pin_key unique(pincode));

Create table receiver.company (city varchar2(10),pincode number(10),constraint pin_key unique(pincode));

Prerequisite:

========

Create a directories  “ mkdir /u01/app/oracle/GG/dirdat/schtrail/“  &

                          “mkdir /u01/app/oracle/GG/dirdat/schtrail/“ for trail file’s location.

On SRC

3. GGSCI> add extract extp_sch, tranlog, Begin now

4. GGSCI> add exttrail /u01/app/oracle/GG/dirdat/schtrail/lt,extract extp_sch

5.  GGSCI> add extract dp_sch, EXTTRAILSOURCE /u01/app/oracle/GG/dirdat/schtrail/lt

6. GGSCI> edit params extp_sch

                 extract extp_dml

                 userid gguser@SRC,password gguser

                 exttrail /u01/app/oracle/GG/dirdat/schtrail/lt

                 Table sender.*;

7. GGSCI> add rmttrail /u01/app/oracle/GG/dirdat/schtrail/rt,extract dp_sch

8. GGSCI> edit params dp_sch

                      extract dpump

                      userid gguser@SRC,password gguser

                      rmthost 192.168.162.13,mgrport 7810

                      rmttrail /u01/app/oracle/GG/dirdat/schtrail/rt

                      passthru

                      table sender.*;

On TRGT

9. GGSCI> add replicat repl_sch, exttrail /u01/app/oracle/GG/dirdat/schtrail/rt,checkpointtable gguser.chkptab

10. GGSCI> edit params repl_sch

                        replicat repl_dml

                        userid gguser@TRGT,password gguser

                        ASSUMETARGETDEFS

                        MAP sender.*, TARGET receiver.*;

On SRC

11. GGSCI > start extract extp_sch

12. GGSCI> start extract dp_sch

    

On TRGT

13. GGSCI> start replicat repl_sch    

Do some insert and update on both the SRC table which should be reflected on TRGT tables automatically.