Thinking Out Loud

March 23, 2017

PURGEOLDEXTRACTS Not Purging Trail Files

Filed under: GoldenGate — mdinh @ 1:16 am

The best part of playing poker is misreading the hand and won.

Well, today this applied to help troubleshoot a Goldengate issue.
Granted it might not be the root cause but it did solve the issue for the time being.

Given:

1. Goldengate version 10.4

2. mgr process started with uid vs username

$ id -a
uid=19208(ggsuser) gid=1601(dba) groups=1601(dba)

$ ps -ef|grep ./mgr
19208    18576     1  0 16:05 ?        00:00:00 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. mgr.prm contains PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2

5. ggserr.log - trail files not being purged
2017-03-22 16:09:35  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 16:09:35  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 (MINKEEPFILES option not used.).
2017-03-22 16:09:35  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

6. send mgr, getpurgeoldextracts
GGSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...
PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              24        0        0        0   Y
OK	
No extract trails

GGSCI> 

Comparison:

1. Goldengate version 12.2

2. mgr process started with username vs uid
ggsuser  73929     1  0 Mar21 ?        00:00:46 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. send mgr, getpurgeoldextracts
GSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              48        0        1        0   Y
OK	
Extract Trails
Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit
/ggs/dirdat/aa                              45

GGSCI>

Research:

1. Did not apply
Goldengate Manager Not Purging Trail Files (Doc ID 1460097.1)

2. Did not apply
Trail Files Not Automatically Purged by 11.1 When PurgeOldExtracts for MGR Has Been Configured (Doc ID 1943702.1)

3. Helped resolve the problem by misreading.
PURGEOLDEXTRACTS Not Purging Trail Files (Doc ID 967934.1)

The use of a UNIX symbolic link to a process can prevent MANAGER from being able to find the appropriate trail files that need to be purged.

There was not symbolic link to a process, that's just insane and who would do such a thing!

Initially read this incorrectly as symbolic link and check dirdat (symbolic link) which lead to the solution.

Solution:

Replace PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
With    PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1

2017-03-22 19:49:10  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 19:49:10  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1 (MINKEEPFILES option not used.).
2017-03-22 19:49:10  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000150, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 150.
2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000151, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 151.
.........
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000183, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 183.
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000184, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 184.

February 28, 2017

Goldengate 12c OGG-01117 core dumped

Filed under: 12c,GoldenGate — mdinh @ 11:47 pm

This probably applies to other versions as well.

Don’t you love it when there’s a simple solution?

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> create subdirs

Creating subdirectories under current directory /oracle/12.2.0/ggs01

Parameter files                /oracle/12.2.0/ggs01/dirprm: created
Report files                   /oracle/12.2.0/ggs01/dirrpt: created
Checkpoint files               /oracle/12.2.0/ggs01/dirchk: created
Process status files           /oracle/12.2.0/ggs01/dirpcs: created
SQL script files               /oracle/12.2.0/ggs01/dirsql: created
Database definitions files     /oracle/12.2.0/ggs01/dirdef: created
Extract data files             /oracle/12.2.0/ggs01/dirdat: created
Temporary files                /oracle/12.2.0/ggs01/dirtmp: created
Credential store files         /oracle/12.2.0/ggs01/dircrd: created
Masterkey wallet files         /oracle/12.2.0/ggs01/dirwlt: created
Dump files                     /oracle/12.2.0/ggs01/dirdmp: created


GGSCI (test1) 2> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (test1) 3> add credentialstore

Credential store created in ./dircrd/.

GGSCI (test1) 4> alter credentialstore add user ggs_user alias ggs_user
Password:

Credential store in ./dircrd/ altered.

GGSCI (test1) 5> dblogin useridalias ggs_user

Source Context :
  SourceModule            : [ggapp.util.pcs]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150330/oggcore/OpenSys/src/gglib/ggapp/pcsutl.c]
  SourceFunction          : [AbendHandler]
  SourceLine              : [1036]
  ThreadBacktrace         : [22] elements
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f746dc0066b]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7f746dbfa734]]
                          : [/oracle/12.2.0/ggs01/libgglog.so(_MSG_ERR_SIGNAL_RECEIVED(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition)+0x3a) [0x7f746dbde41d]]
                          : [ggsci() [0x52a977]]
                          : [/lib64/libpthread.so.0(+0xf850) [0x7f7469f72850]]
                          : [/lib64/libpthread.so.0(pthread_mutex_lock+0x4) [0x7f7469f6c444]]
                          : [ggsci(ggs::gglib::MultiThreading::Mutex::Lock()+0x9) [0x556bb9]]
                          : [ggsci(CContextItem::operator char const*() const+0x1d) [0x576b03]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::initConnectionLogon(ggs::gglib::ggdbapi::DBAuthParams const&)+0x3d0) [0x5d13b0]]
                          : [ggsci(ggs::gglib::ggdbora::OraConnUtil::openDataSource(ggs::gglib::ggdbapi::DBAuthParams const&)+0x28) [0x5d1578]]
                          : [ggsci() [0x57c20c]]
                          : [ggsci(gl_db_login(char const*, char const*, ggs::gglib::ggapp::CDBObjName<(DBObjType)11>&, ggs::gglib::ggapp::CDBObjName<(DBObjType)12>&, short, char (&) [2048])+0x61) [0x57c7a1]]
                          : [ggsci() [0x6437a6]]
                          : [ggsci(GGSCIDB_get_command(char const*, char const*, char const*, short, short, char (&) [2048])+0x1a0) [0x63ef00]]
                          : [ggsci(do_cmd(char*, unsigned long, char*, unsigned long)+0xe05) [0x6632a5]]
                          : [ggsci() [0x66746b]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x5550c0]]
                          : [ggsci(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x555e0d]]
                          : [ggsci(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x555ef1]]
                          : [ggsci(main+0x3b) [0x667a7b]]
                          : [/lib64/libc.so.6(__libc_start_main+0xe6) [0x7f7465cdfc36]]
                          : [ggsci() [0x4f5b89]]

2017-02-28 20:52:47  ERROR   OGG-01117  Received signal: Segmentation violation (11).

2017-02-28 20:52:47  ERROR   OGG-01668  PROCESS ABENDING.
Segmentation fault (core dumped)

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

Opatch Apply Instructions: Ensure that you set the ORACLE_HOME environment variable to the Oracle GoldenGate home.

I had just completed patching Goldengate and forgot to set ORACLE_HOME back to DB_HOME.

oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/12.2.0/ggs01
ACS_HOME=/oracle/acsprod

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

oracle@test1:/opt/oracle/12.2.0/ggs01$ source ~/.orcl
The Oracle base remains unchanged with value /opt/oracle
oracle@test1:/opt/oracle/12.2.0/ggs01$ env|grep HOME
GG_HOME=/opt/oracle/12.2.0/ggs01
HOME=/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
ACS_HOME=/oracle/acsprod

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

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> dblogin useridalias ggs_user
Successfully logged into database.

GGSCI (test1 as ggs_user@orcl) 2> exit
oracle@test1:/opt/oracle/12.2.0/ggs01$

Goldengate 12c PREPARECSN BUG RESOLUTION

Filed under: 12c,GoldenGate — mdinh @ 2:56 am

It looks like I have encountered BUG which applies to any platform but Oracle only has patch for Solaris Sparc.

Anyone working for Oracle able to assist here please?

ADD SCHEMATRANDATA Throws OGG-01780 Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command. (Doc ID 2188988.1)

Oracle GoldenGate – Version 12.2.0.1.0 and later
Information in this document applies to any platform.

Patch 24601324: Patch FOR MLR 24590215: Solaris Sparc: Oracle12c: OGG 12.2.0.1.160517

Only 3 options accepted after ADD SCHEMATRANDATA causing the issue.

The current patch will change the code to accept 5 options after ADD SCHEMATRANDATA

DEMO:

WAIT
Wait for any in-flight transactions and prepare table instantiation.

LOCK
Put a lock on the table (to prepare for table instantiation).

NOWAIT
Default behavior, preparing for instantiation is done immediately.

NONE
No instantiation preparation occurs.

++++++++++++++++++++++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ cat /etc/oracle-release
Oracle Linux Server release 6.6
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ ggsci

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.



GGSCI (arrow1.localdomain) 1> DBLOGIN USERIDALIAS ggs_user
Successfully logged into database.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 2> DELETE SCHEMATRANDATA demo

2017-02-27 18:45:16  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:45:16  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 3> ADD SCHEMATRANDATA demo PREPARECSN WAIT

2017-02-27 18:45:57  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-10154  Schema level PREPARECSN set to mode WAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 4> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:00  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:00  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 5> ADD SCHEMATRANDATA demo PREPARECSN NOWAIT

2017-02-27 18:46:10  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 6> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:18  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:18  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 7> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN NOWAIT

2017-02-27 18:46:29  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 8> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN WAIT

2017-02-27 18:46:38  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 9> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN

2017-02-27 18:46:46  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 10> info SCHEMATRANDATA demo

2017-02-27 18:46:56  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEMO.

2017-02-27 18:46:56  INFO    OGG-01981  Schema level supplemental logging is enabled on schema DEMO for all columns.

2017-02-27 18:46:56  INFO    OGG-10462  Schema DEMO have 2 prepared tables for instantiation.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 11> exit
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$

UPDATE:

Download and Apply following patch for the appropriate DB version:
Patch 25445840: ORACLE GOLDENGATE V12.2.0.1.170221 FOR Oracle 11g

p25445840_12201170221_Linux-x86-64.zip
p6880880_112000_Linux-x86-64.zip

oracle@test1:/opt/oracle/12.2.0/ggs01$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

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



GGSCI (test1) 1> DBLOGIN USERIDALIAS ggs_user
Successfully logged into database.

GGSCI (test1 as ggs_user@orcl) 2> ADD SCHEMATRANDATA ggs_test ALLCOLS PREPARECSN WAIT

2017-02-28 20:59:06  INFO    OGG-01788  SCHEMATRANDATA has been added on schema ggs_test.

2017-02-28 20:59:06  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema ggs_test.

2017-02-28 20:59:06  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema ggs_test.

2017-02-28 20:59:06  INFO    OGG-10154  Schema level PREPARECSN set to mode WAIT on schema ggs_test.

GGSCI (test1 as ggs_user@orcl) 3> versions
Operating System:
Linux
Version #1 SMP Tue Jun 23 16:02:31 UTC 2015 (4b89d0c), Release 3.0.101-63-default
Node: test1
Machine: x86_64

Database:
Oracle Database 11g 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 Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

GGSCI (test1 as ggs_user@orcl) 4> exit
oracle@test1:/opt/oracle/12.2.0/ggs01$

February 14, 2017

Goldengate 12c runInstaller UNIX_GROUP_NAME | INVENTORY_LOCATION

Filed under: 12c,GoldenGate,oracle — mdinh @ 2:20 pm

I keep finding ways to break things because the environments are not consistent.

UNIX_GROUP_NAME
This parameter is the UNIX group name to be set for the inventory on UNIX platforms.
Note: The UNIX group name is used for first-time installations only.

Details for oraInventory

$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

Notice the group is dba while /etc/oraInst.loc has oinstall.
Probably because I specified UNIX_GROUP_NAME=dba?
Don’t remembered since this environment is really old.

$ ls -ld /u01/app/oracle/oraInventory
drwxrwx---. 6 oracle dba 4096 Feb 14 04:50 /u01/app/oracle/oraInventory

What happens when INVENTORY_LOCATION is not the same as /etc/oraInst.loc ?

oracle@arrow1:HAWKA:/media/sf_OracleSoftware/GoldenGate_12.2.0.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion INSTALL_OPTION=ORA11g SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs03 UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oraInventory

++++++++++

oracle@arrow1:HAWKA:/home/oracle
$ ls -ld /u01/app/oraInventory
drwxrwx---. 5 oracle oinstall 4096 Feb 14 05:18 /u01/app/oraInventory
oracle@arrow1:HAWKA:/home/oracle
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03
$ cd /u01/app/12.2.0.1/ggs03/OPatch/

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ env|grep HOME
GG_HOME=/u01/app/12.2.0.1/ggs01
HOME=/home/oracle

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ export GG_HOME=/u01/app/12.2.0.1/ggs03

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_05-21-32AM.log

List of Homes on this system:

Home name= OraGI12Home1, Location= "/u01/app/12.1.0.2/grid"
Home name= OraDB12Home1, Location= "/u01/app/oracle/product/12.1.0.2/db_1"
Home name= OraDb11g_home1, Location= "/u01/app/oracle/product/11.2.0.4/db_1"
Home name= OraHome1, Location= "/u01/app/12.2.0.1/ggs01"
Home name= OraHome2, Location= "/u01/app/12.2.0.1/ggs02"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

When INVENTORY_LOCATION is different from /etc/oraInst.loc, use -invPtrLoc.

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details -invPtrLoc /u01/app/oraInventory/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oraInventory/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_05-44-55AM.log

Lsinventory Output file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_05-44-55AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle GoldenGate Core                                               12.2.0.0.0
There are 1 products installed in this Oracle Home.


Installed Products (6):

Installer SDK Component                                              11.2.0.3.0
Java Development Kit                                                 1.6.0.65.0
Oracle GoldenGate Core                                               12.2.0.0.0
Oracle GoldenGate for Oracle 11g                                     12.2.0.0.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Universal Installer                                           11.2.0.3.0
There are 6 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

Just out of curiosity, what’s the difference between ORA11g|ORA12c?

oracle@arrow1:HAWKA:/media/sf_OracleSoftware/GoldenGate_12.2.0.1/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion INSTALL_OPTION=ORA12c SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs03 UNIX_GROUP_NAME=dba INVENTORY_LOCATION=/u01/app/oraInventory2
Starting Oracle Universal Installer...

++++++++++

oracle@arrow1:HAWKA:/home/oracle
$ cd /u01/app/12.2.0.1/ggs03/OPatch/

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ cat /u01/app/oraInventory2/oraInst.loc
inventory_loc=/u01/app/oraInventory2
inst_group=dba

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ls -ld /u01/app/oraInventory2/
drwxrwx---. 5 oracle dba 4096 Feb 14 06:08 /u01/app/oraInventory2/

++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$ ./opatch lsinventory -details -invPtrLoc /u01/app/oraInventory2/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/ggs03
Central Inventory : /u01/app/oraInventory2
   from           : /u01/app/oraInventory2/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/opatch2017-02-14_06-09-42AM.log

Lsinventory Output file location : /u01/app/12.2.0.1/ggs03/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_06-09-42AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle GoldenGate Core                                               12.2.0.0.0
There are 1 products installed in this Oracle Home.


Installed Products (6):

Installer SDK Component                                              11.2.0.3.0
Java Development Kit                                                 1.6.0.65.0
Oracle GoldenGate Core                                               12.2.0.0.0
Oracle GoldenGate for Oracle 12c                                     12.2.0.0.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Universal Installer                                           11.2.0.3.0
There are 6 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs03/OPatch
$

February 13, 2017

Goldengate 12c Find log sequence#, rba# for integrated extract (Doc ID 2006932.1)

Filed under: 12c,GoldenGate — mdinh @ 2:50 am

When using integrated extract, info command does not show Seqno, RBA, e.g. Seqno 3292, RBA 79236752.
Command send status will show Seqno, RBA; is not usable when process is stopped.
For Oracle GoldenGate – Version 12.1.2.1.2 and later, there is now debug option to retrieve Seqno, RBA.

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ ggsci

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.



GGSCI (arrow1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_HAWK      00:00:10      00:00:06
EXTRACT     STOPPED     P_HAWK      00:00:00      00:02:03


GGSCI (arrow1.localdomain) 2> info e*

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status RUNNING
Checkpoint Lag       00:00:10 (updated 00:00:08 ago)
Process ID           1665
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:36:06
                     SCN 0.4928929 (4928929)


GGSCI (arrow1.localdomain) 3> info e* debug

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status RUNNING
Checkpoint Lag       00:00:10 (updated 00:00:02 ago)
Process ID           1665
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:36:16  Seqno 3292, RBA 79236752
                     SCN 0.4928939 (4928939)


GGSCI (arrow1.localdomain) 4> send e* status

Sending STATUS request to EXTRACT E_HAWK ...


EXTRACT E_HAWK (PID 1665)
  Current status: Recovery complete: Processing data

  Current read position:
  Redo thread #: 1
  Sequence #: 3292
  RBA: 79263888
  Timestamp: 2017-02-12 18:36:47.000000
  SCN: 0.4928987 (4928987)
  Current write position:
  Sequence #: 0
  RBA: 1420
  Timestamp: 2017-02-12 18:36:56.251219
  Extract Trail: ./dirdat/aa



GGSCI (arrow1.localdomain) 5> stop e*

Sending STOP request to EXTRACT E_HAWK ...
Request processed.


GGSCI (arrow1.localdomain) 6> send e* status

Sending STATUS request to EXTRACT E_HAWK ...

ERROR: sending message to EXTRACT E_HAWK (Connection reset by peer).


GGSCI (arrow1.localdomain) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:07      00:00:13
EXTRACT     STOPPED     P_HAWK      00:00:00      00:03:09


GGSCI (arrow1.localdomain) 8> info e* debug

EXTRACT    E_HAWK    Last Started 2017-02-12 18:35   Status STOPPED
Checkpoint Lag       00:00:07 (updated 00:00:19 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-02-12 18:37:07  Seqno 3292, RBA 79275152
                     SCN 0.4929013 (4929013)


GGSCI (arrow1.localdomain) 9> exit
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$

January 28, 2017

Goldengate Importance of Commenting

Filed under: GoldenGate — mdinh @ 2:35 am

I was curious to determine effect for modifying ./dirdat/aa to dirdat/aa for Goldengate parameter.

While it may look to be the same, it is not treated the same.

$ ll dirdat/*
-rw-r-----. 1 oracle oinstall    3719 Jan 27 14:15 dirdat/aa000000000
-rw-r-----. 1 oracle oinstall 8086335 Jan 27 18:16 dirdat/aa000000001

$ ll ./dirdat/*
-rw-r-----. 1 oracle oinstall    3719 Jan 27 14:15 ./dirdat/aa000000000
-rw-r-----. 1 oracle oinstall 8086335 Jan 27 18:16 ./dirdat/aa000000001

Here is what happens when parameter is modified from ./dirdat/aa to dirdat/aa

$ grep EXTTRAIL ggserr.log
2017-01-27 10:45:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500.
2017-01-27 10:45:16  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): ADD EXTRACT p_hawk  EXTTRAILSOURCE ./dirdat/aa.
2017-01-27 18:18:26  ERROR   OGG-01044  Oracle GoldenGate Capture for Oracle, e_hawk.prm:  The trail 'dirdat/aa' is not assigned to extract 'E_HAWK'. 
Assign the trail to the extract with the command "ADD EXTTRAIL/RMTTRAIL dirdat/aa, EXTRACT E_HAWK".

How do you find how trail file as added?

GGSCI (arrow1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     E_HAWK      00:00:04      00:12:25
EXTRACT     STOPPED     P_HAWK      00:00:00      07:44:04


GGSCI (arrow1.localdomain) 2> info e*

EXTRACT    E_HAWK    Last Started 2017-01-27 18:18   Status ABENDED
Checkpoint Lag       00:00:04 (updated 00:12:27 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-01-27 18:16:52
                     SCN 0.3369629 (3369629)


GGSCI (arrow1.localdomain) 3> info e* showch

EXTRACT    E_HAWK    Last Started 2017-01-27 18:18   Status ABENDED
Checkpoint Lag       00:00:04 (updated 00:12:41 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2017-01-27 18:16:52
                     SCN 0.3369629 (3369629)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Integrated Redo Log

  Startup Checkpoint (starting position in the data source):
    Timestamp: 2017-01-27 10:45:14.000000
    SCN: Not available

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Timestamp: 2017-01-27 18:16:52.000000
    SCN: 0.3369628 (3369628)

  Current Checkpoint (position of last record read in the data source):
    Timestamp: 2017-01-27 18:16:52.000000
    SCN: 0.3369629 (3369629)

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 1
    RBA: 8086335
    Timestamp: 2017-01-27 18:16:56.302658
    Extract Trail: ./dirdat/aa
    Seqno Length: 9
    Flip Seqno Length: No
    Trail Type: EXTTRAIL

Header:
  Version = 2
  Record Source = A
  Type = 13
  # Input Checkpoints = 1
  # Output Checkpoints = 1

File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0

Configuration:
  Data Source = 3
  Transaction Integrity = 1
  Task Type = 0

Status:
  Start Time = 2017-01-27 18:18:26
  Last Update Time = 2017-01-27 18:16:56
  Stop Status = A
  Last Result = 520



GGSCI (arrow1.localdomain) 4>

Alternatively, make it easier by commenting parameter files.

$ head dirprm/e_hawk.prm
EXTRACT e_hawk
-- CHECKPARAMS
-- ADD EXTRACT e_hawk, INTEGRATED TRANLOG, BEGIN NOW
-- ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500
USERIDALIAS ggs_user
EXTTRAIL ./dirdat/aa
INCLUDE dirprm/global_ggenv.inc

January 20, 2017

Goldengate Network Troubleshooting

Filed under: GoldenGate — mdinh @ 2:50 pm

We encounter the following error:

GGS ERROR 150  Oracle GoldenGate Capture for Oracle, pump.prm:  TCP/IP error 9 (Bad file descriptor).

Note: server collectors at target will not be started and ports not opened until pump at source is started.
Using nc to test port.

$ nc -v -z -w 3 target.local 7960; echo $?
nc: connect to target.local port 7960 (tcp) failed: Connection refused
1

$ nc -v -z -w 3 target.local 7970; echo $?
Connection to target.local 7970 port [tcp/*] succeeded!
0

Not preferred and does not mean it’s wrong – Entry starting at 16.

DYNAMICPORTLIST 7960-7980 (20)

In hindsight, I should have started a few more pump extracts
to determine if Entry values cycle back to 0 and
if port assignment will start at 7960 or will fail.

This is what happens when dozen of pump extracts are stopped and started in groups while manager is RUNNING.

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 20
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
  16   7976     0       6260   2017/01/19 12:06:12  Server
  17   7977     0       6261   2017/01/19 12:06:12  Server
  18   7978     0       6262   2017/01/19 12:06:12  Server
  19   7979     0       6263   2017/01/19 12:06:12  Server

Houston, we have a problem.
Look at Error column.
Anything other than 0 is not good.
Look at the date assigned.
These look to be orphaned processes and manager still thinks port is assigned.

GGSCI> SEND MANAGER GETPORTINFO

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 18
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7841    98      31663   2016/12/30 08:03:18  Server
   1   7842    98      31664   2016/12/30 08:03:18  Server
   2   7843    98                
   3   7844    98                
   4   7845    98                
   5   7846    98       1243   2016/12/30 08:14:01  Server
   6   7847    98       4543   2016/12/30 08:34:28  Server
   7   7848    98       4815   2016/12/30 08:35:55  Server
   8   7849    98       5094   2016/12/30 08:37:07  Server
   9   7850    98       5151   2016/12/30 08:37:20  Server
  10   7851    98       5152   2016/12/30 08:37:25  Server
  11   7852    98      26856   2017/01/17 21:57:38  Server
  12   7853    98      32133   2017/01/17 22:30:35  Server
  13   7854    98      16390   2017/01/06 03:56:56  Server
  14   7855    98      32220   2017/01/17 22:30:41  Server
 
  15   7856     0       4774   2017/01/17 22:57:40  Server
  16   7857     0       4777   2017/01/17 22:57:52  Server
  17   7858     0       4779   2017/01/17 22:57:59  Server
  
  18   7859    98      26854   2017/01/17 21:57:38  Server
  19   7860    98      26855   2017/01/17 21:57:38  Server

This is what I like.
Notice the timestamp for Assigned are all the same.
This is because pump from source was started using wildcard, i.e. start p*
Oracle support does not recommend this and YMMV.

GGSCI> !
SEND MANAGER GETPORTINFO

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 14
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7960     0       7744   2017/01/19 12:15:13  Server
   1   7961     0       7745   2017/01/19 12:15:13  Server
   2   7962     0       7746   2017/01/19 12:15:13  Server
   3   7963     0       7747   2017/01/19 12:15:13  Server
   4   7964     0       7748   2017/01/19 12:15:13  Server
   5   7965     0       7749   2017/01/19 12:15:13  Server
   6   7966     0       7750   2017/01/19 12:15:13  Server
   7   7967     0       7751   2017/01/19 12:15:13  Server
   8   7968     0       7752   2017/01/19 12:15:13  Server
   9   7969     0       7753   2017/01/19 12:15:13  Server
  10   7970     0       7754   2017/01/19 12:15:13  Server
  11   7971     0       7755   2017/01/19 12:15:13  Server
  12   7972     0       7756   2017/01/19 12:15:13  Server
  13   7973     0       7757   2017/01/19 12:15:13  Server

GGSCI> sh ps -ef|grep ./server

512       7744  7742  0 12:15 ?        00:00:00 ./server -p 7960 -k -l /ggs1040/ggserr.log
512       7745  7742  0 12:15 ?        00:00:00 ./server -p 7961 -k -l /ggs1040/ggserr.log
512       7746  7742  0 12:15 ?        00:00:00 ./server -p 7962 -k -l /ggs1040/ggserr.log
512       7747  7742  0 12:15 ?        00:00:00 ./server -p 7963 -k -l /ggs1040/ggserr.log
512       7748  7742  0 12:15 ?        00:00:00 ./server -p 7964 -k -l /ggs1040/ggserr.log
512       7749  7742  0 12:15 ?        00:00:00 ./server -p 7965 -k -l /ggs1040/ggserr.log
512       7750  7742  0 12:15 ?        00:00:00 ./server -p 7966 -k -l /ggs1040/ggserr.log
512       7751  7742  0 12:15 ?        00:00:00 ./server -p 7967 -k -l /ggs1040/ggserr.log
512       7752  7742  0 12:15 ?        00:00:00 ./server -p 7968 -k -l /ggs1040/ggserr.log
512       7753  7742  0 12:15 ?        00:00:00 ./server -p 7969 -k -l /ggs1040/ggserr.log
512       7754  7742  0 12:15 ?        00:00:00 ./server -p 7970 -k -l /ggs1040/ggserr.log
512       7755  7742  0 12:15 ?        00:00:00 ./server -p 7971 -k -l /ggs1040/ggserr.log
512       7756  7742  0 12:15 ?        00:00:00 ./server -p 7972 -k -l /ggs1040/ggserr.log
512       7757  7742  0 12:15 ?        00:00:00 ./server -p 7973 -k -l /ggs1040/ggserr.log
512       7759  7741  0 12:16 pts/1    00:00:00 sh -c ps -ef|grep ./server
512       7761  7759  0 12:16 pts/1    00:00:00 grep ./server

GGSCI>

Good followup reading.
OGG GGS Error 150: No Dynamic Ports Available Orphan Ports Server Collector (Doc ID 965356.1)

A SERVER process is an "orphan" if netstat or lsof shows only a "listening" port, with no "ESTABLISHED" connections.

Updated Jan 25 2017
In TCP networking, what is a FIN_WAIT state?
https://kb.iu.edu/d/ajmi

Sending GETPORTINFO request to MANAGER ...

Dynamic Port List

Starting Index 32
Reassign Delay 30 seconds

Entry Port  Error  Process     Assigned             Program
----- ----- ----- ----------   -------------------  -------
   0   7841    98                
   1   7842    98                
   2   7843    98                
   3   7844    98                
   4   7845    98                
   5   7846     0       1397   2017/01/24 21:03:55  Server
   6   7847    98                
   7   7848     0       1398   2017/01/24 21:03:55  Server
   8   7849    98                
   9   7850     0       1399   2017/01/24 21:03:55  Server
  10   7851     0       1400   2017/01/24 21:03:55  Server
  11   7852     0       1547   2017/01/24 21:04:24  Server
  12   7853    98                
  13   7854    98                
  14   7855    98                
  15   7856    98                
  16   7857     0       1548   2017/01/24 21:04:24  Server
  17   7858     0       1549   2017/01/24 21:04:24  Server
  18   7859    98                
  19   7860    98                
  20   7861    98                
  21   7862    98                
  22   7863    98                
  23   7864    98                
  24   7865     0       1550   2017/01/24 21:04:24  Server
  25   7866     0       1587   2017/01/24 21:04:38  Server
  26   7867     0       1588   2017/01/24 21:04:38  Server
  27   7868     0       1589   2017/01/24 21:04:38  Server
  28   7869     0       1590   2017/01/24 21:04:38  Server
  29   7870     0       1596   2017/01/24 21:05:00  Server
  31   7872     0       6491   2017/01/24 21:31:14  Server
  

$ netstat -an|grep 7864
tcp        0      0 0.0.0.0:7864                0.0.0.0:*                   LISTEN      
tcp        1      0 10.80.25.64:7864            10.80.25.64:42242           CLOSE_WAIT  
tcp        0      0 10.80.25.64:42242           10.80.25.64:7864            FIN_WAIT2 

$ netstat -an|grep 7870
tcp        0      0 0.0.0.0:7870                0.0.0.0:*                   LISTEN      
tcp        0      0 10.80.25.64:7870            10.80.26.36:13810           ESTABLISHED

$ netstat -an|grep 7841
tcp        0      0 0.0.0.0:7841                0.0.0.0:*                   LISTEN      
tcp        0      0 10.80.25.64:39288           10.80.25.64:7841            TIME_WAIT

December 9, 2016

GoldenGate Capture using Active DataGuard

Filed under: Dataguard,GoldenGate — mdinh @ 3:38 am

How to Configure Extract on Standalone Active Data Guard System if Primary is RAC Multipe Nodes (Doc ID 1962336.1)

Configuring Classic Capture in Oracle Active Data Guard Only Mode
http://docs.oracle.com/goldengate/1212/gg-winux/GIORA/classic_capture.htm#GIORA997

November 15, 2016

OGG Activity Logging Tracing (Doc ID 1204284.1)

Filed under: GoldenGate — mdinh @ 1:54 am

I just came across MOS Doc for tracing OGG processes.

Just thought I would compare the old versus new.

You can find comparison and my preference here

October 30, 2016

GoldenGate 12.2 EXCLUDEUSER

Filed under: GoldenGate,oracle — mdinh @ 9:02 pm

Here’s a good reason to always use the latest version.

GoldenGate 12c Integrated EXTRACT: How To EXCLUDEUSER (Doc ID 2008420.1)

APPLIES TO:

Oracle GoldenGate – Version 12.1.2.0.2 to 12.1.2.1.9 [Release 12.1]
Information in this document applies to any platform.

GOAL

We are trying to exclude the REPLICAT user in OGG 12.1.2 capturing from Oracle 12.1.0.2 bi-directional. But using EXTRACT TRANLOGOPTIONS EXCLUDEUSER to exclude a user in one of the PDBs:

TRANLOGOPTIONS EXCLUDEUSER ZEPP01.ZE_GG

EXTRACT is returning the message:

2015-03-25 15:44:27 ERROR OGG-00303 Could not find USER_ID corresponding to USERNAME ‘ZEPP01.ZE_GG’.

SOLUTION

When using Integrated EXTRACT, EXCLUDEUSER is currently not supported in OGG V12.1.2. We have a enhancement request tracked via Bug 21891811 – Support EXCLUDEUSER in Integrated Extract (IE) for DDL records which has been implementd in OGG v12.2.0.1.

For excluding users in a 12c multitenant DB, OGG v12.1.2  has a new EXTRACT param TRANLOGOPTIONS EXCLUDETAG <nn>. This is typically used to exclude the REPLICAT user in bi-directional configurations.

Changes made by Integrated REP are tagged by default in redo as 00. So adding the EXTRACT param TRANLOGOPTIONS EXCLUDETAG 00

Would exclude those operations.

The tag can also be explicitly set in REPLICAT using:

DBOPTIONS SETTAG 0935

Then in EXTRACT param:

TRANLOGOPTIONS EXCLUDETAG 0935

With OGG v12.2.0.1+, you can use the earlier option of TRANLOGOPTIONS EXCLUDEUSER ZEPP01.ZE_GG

Reference:

(2016, October 18). Retrieved October 30, 2016, from https://support.oracle.com/epmos/faces/DocContentDisplay?id=2008420.1

Next Page »

Create a free website or blog at WordPress.com.