Thinking Out Loud

April 24, 2019

Solving DBFS UnMounting Issue

Filed under: DBFS,GoldenGate,RAC — mdinh @ 11:34 pm

Often, I am quite baffle with Oracle’s implementations and documentations.

RAC GoldenGate DBFS implementation has been a nightmare and here is one example DBFS Nightmare

I am about to show you another.

In general, I find any implementation using ACTION_SCRIPT is good in theory, bad in practice, but I digress.

Getting ready to shutdown CRS for system patching to find out CRS failed to shutdown.

# crsctl stop crs
CRS-2675: Stop of 'dbfs_mount' on 'host02' failed
CRS-2675: Stop of 'dbfs_mount' on 'host02' failed
CRS-2673: Attempting to stop 'dbfs_mount' on 'host02'
CRS-2675: Stop of 'dbfs_mount' on 'host02' failed
CRS-2673: Attempting to stop 'dbfs_mount' on 'host02'
CRS-2675: Stop of 'dbfs_mount' on 'host02' failed
CRS-2799: Failed to shut down resource 'dbfs_mount' on 'host02'
CRS-2799: Failed to shut down resource 'ora.GG_PROD.dg' on 'host02'
CRS-2799: Failed to shut down resource 'ora.asm' on 'host02'
CRS-2799: Failed to shut down resource 'ora.dbfs.db' on 'host02'
CRS-2799: Failed to shut down resource 'ora.host02.ASM2.asm' on 'host02'
CRS-2794: Shutdown of Cluster Ready Services-managed resources on 'host02' has failed
CRS-2675: Stop of 'ora.crsd' on 'host02' failed
CRS-2799: Failed to shut down resource 'ora.crsd' on 'host02'
CRS-2795: Shutdown of Oracle High Availability Services-managed resources on 'host02' has failed
CRS-4687: Shutdown command has completed with errors.
CRS-4000: Command Stop failed, or completed with errors.

Check /var/log/messages to find errors and there’s no clue for resolution.

# grep -i dbfs /var/log/messages
Apr 17 19:42:26 host02 DBFS_/ggdata: unmounting DBFS from /ggdata
Apr 17 19:42:26 host02 DBFS_/ggdata: umounting the filesystem using '/bin/fusermount -u /ggdata'
Apr 17 19:42:26 host02 DBFS_/ggdata: Stop - stopped, but still mounted, error

Apr 17 20:45:59 host02 DBFS_/ggdata: mount-dbfs.sh mounting DBFS at /ggdata from database DBFS
Apr 17 20:45:59 host02 DBFS_/ggdata: /ggdata already mounted, use mount-dbfs.sh stop before attempting to start

Apr 17 21:01:29 host02 DBFS_/ggdata: unmounting DBFS from /ggdata
Apr 17 21:01:29 host02 DBFS_/ggdata: umounting the filesystem using '/bin/fusermount -u /ggdata'
Apr 17 21:01:29 host02 DBFS_/ggdata: Stop - stopped, but still mounted, error
Apr 17 21:01:36 host02 dbfs_client[71957]: OCI_ERROR 3114 - ORA-03114: not connected to ORACLE
Apr 17 21:01:41 host02 dbfs_client[71957]: /FS1/dirdat/ih000247982 Block error RC:-5

Apr 17 21:03:06 host02 DBFS_/ggdata: unmounting DBFS from /ggdata
Apr 17 21:03:06 host02 DBFS_/ggdata: umounting the filesystem using '/bin/fusermount -u /ggdata'
Apr 17 21:03:06 host02 DBFS_/ggdata: Stop - stopped, now not mounted
Apr 17 21:09:19 host02 DBFS_/ggdata: filesystem /ggdata not currently mounted, no need to stop

Apr 17 22:06:16 host02 DBFS_/ggdata: mount-dbfs.sh mounting DBFS at /ggdata from database DBFS
Apr 17 22:06:17 host02 DBFS_/ggdata: ORACLE_SID is DBFS2
Apr 17 22:06:17 host02 DBFS_/ggdata: doing mount /ggdata using SID DBFS2 with wallet now
Apr 17 22:06:18 host02 DBFS_/ggdata: Start -- ONLINE

The messages in log of script agent show below error (MOS documentation).
Anyone know where script agent is located at?

2019-04-17 20:56:02.793903 :    AGFW:3274315520: {1:53477:37077} Agent received the message: AGENT_HB[Engine] ID 12293:16017523
2019-04-17 20:56:19.124667 :CLSDYNAM:3276416768: [dbfs_mount]{1:53477:37077} [check] Executing action script: /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh[check]
2019-04-17 20:56:19.176927 :CLSDYNAM:3276416768: [dbfs_mount]{1:53477:37077} [check] Checking status now
2019-04-17 20:56:19.176973 :CLSDYNAM:3276416768: [dbfs_mount]{1:53477:37077} [check] Check -- ONLINE
2019-04-17 20:56:32.794287 :    AGFW:3274315520: {1:53477:37077} Agent received the message: AGENT_HB[Engine] ID 12293:16017529
2019-04-17 20:56:43.312534 :    AGFW:3274315520: {2:37893:29307} Agent received the message: RESOURCE_STOP[dbfs_mount host02 1] ID 4099:16017535
2019-04-17 20:56:43.312574 :    AGFW:3274315520: {2:37893:29307} Preparing STOP command for: dbfs_mount host02 1
2019-04-17 20:56:43.312584 :    AGFW:3274315520: {2:37893:29307} dbfs_mount host02 1 state changed from: ONLINE to: STOPPING
2019-04-17 20:56:43.313088 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [stop] Executing action script: /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh[stop]
2019-04-17 20:56:43.365201 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [stop] unmounting DBFS from /ggdata
2019-04-17 20:56:43.415516 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [stop] umounting the filesystem using '/bin/fusermount -u /ggdata'
2019-04-17 20:56:43.415541 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [stop] /bin/fusermount: failed to unmount /ggdata: Device or resource busy
2019-04-17 20:56:43.415552 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [stop] Stop - stopped, but still mounted, error
2019-04-17 20:56:43.415611 :    AGFW:3276416768: {2:37893:29307} Command: stop for resource: dbfs_mount host02 1 completed with status: FAIL
2019-04-17 20:56:43.415929 :CLSFRAME:3449863744:  TM [MultiThread] is changing desired thread # to 3. Current # is 2
2019-04-17 20:56:43.415970 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [check] Executing action script: /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh[check]
2019-04-17 20:56:43.416033 :    AGFW:3274315520: {2:37893:29307} Agent sending reply for: RESOURCE_STOP[dbfs_mount host02 1] ID 4099:16017535
2019-04-17 20:56:43.467939 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [check] Checking status now
2019-04-17 20:56:43.467964 :CLSDYNAM:3276416768: [dbfs_mount]{2:37893:29307} [check] Check -- ONLINE

ACTION_SCRIPT can be found using crsctl as shown if you have not found where log of script agent is at.

oracle@host02 ~ $ $GRID_HOME/bin/crsctl stat res -w "TYPE = local_resource" -p | grep mount-dbfs.sh
ACTION_SCRIPT=/u02/app/12.1.0/grid/crs/script/mount-dbfs.sh
ACTION_SCRIPT=/u02/app/12.1.0/grid/crs/script/mount-dbfs.sh

Here’s a test case to resolve “failed to unmount /ggdata: Device or resource busy”.

First thought was to use fuser and kill the process.

# fuser -vmM /ggdata/
                     USER        PID ACCESS COMMAND
/ggdata:             root     kernel mount /ggdata
                     mdinh     85368 ..c.. bash
                     mdinh     86702 ..c.. vim
# 

Second thought, might not be a good idea and better idea is to let the script handle this if it can.
Let’s see what options are available for mount-dbfs.sh:

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh -h
Usage: mount-dbfs.sh { start | stop | check | status | restart | clean | abort | version }

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh version
20160215
oracle@host02 ~ 

Stop DBFS failed as expected.

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh status
Checking status now
Check -- ONLINE

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh stop
unmounting DBFS from /ggdata
umounting the filesystem using '/bin/fusermount -u /ggdata'
/bin/fusermount: failed to unmount /ggdata: Device or resource busy
Stop - stopped, but still mounted, error
oracle@host02 ~ $

Stop DBFS using clean option. Notice PID killed is 40047 and not the same as (mdinh 86702 ..c.. vim)
Note: not all output displayed for brevity.

oracle@host02 ~ $ /bin/bash -x /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh clean
+ msg='cleaning up DBFS nicely using (fusermount -u|umount)'
+ '[' info = info ']'
+ /bin/echo cleaning up DBFS nicely using '(fusermount' '-u|umount)'
cleaning up DBFS nicely using (fusermount -u|umount)
+ /bin/logger -t DBFS_/ggdata -p user.info 'cleaning up DBFS nicely using (fusermount -u|umount)'
+ '[' 1 -eq 1 ']'
+ /bin/fusermount -u /ggdata
/bin/fusermount: failed to unmount /ggdata: Device or resource busy
+ /bin/sleep 1
+ FORCE_CLEANUP=0
+ '[' 0 -gt 1 ']'
+ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh status
+ '[' 0 -eq 0 ']'
+ FORCE_CLEANUP=1
+ '[' 1 -eq 1 ']'
+ logit error 'tried (fusermount -u|umount), still mounted, now cleaning with (fusermount -u -z|umount -f) and kill'
+ type=error
+ msg='tried (fusermount -u|umount), still mounted, now cleaning with (fusermount -u -z|umount -f) and kill'
+ '[' error = info ']'
+ '[' error = error ']'
+ /bin/echo tried '(fusermount' '-u|umount),' still mounted, now cleaning with '(fusermount' -u '-z|umount' '-f)' and kill
tried (fusermount -u|umount), still mounted, now cleaning with (fusermount -u -z|umount -f) and kill
+ /bin/logger -t DBFS_/ggdata -p user.error 'tried (fusermount -u|umount), still mounted, now cleaning with (fusermount -u -z|umount -f) and kill'
+ '[' 1 -eq 1 ']'
================================================================================
+ /bin/fusermount -u -z /ggdata
================================================================================
+ '[' 1 -eq 1 ']'
++ /bin/ps -ef
++ /bin/grep -w /ggdata
++ /bin/grep dbfs_client
++ /bin/grep -v grep
++ /bin/awk '{print $2}'
+ PIDS=40047
+ '[' -n 40047 ']'
================================================================================
+ /bin/kill -9 40047
================================================================================
++ /bin/ps -ef
++ /bin/grep -w /ggdata
++ /bin/grep mount.dbfs
++ /bin/grep -v grep
++ /bin/awk '{print $2}'
+ PIDS=
+ '[' -n '' ']'
+ exit 1

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh status
Checking status now
Check -- OFFLINE

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh status
Checking status now
Check -- OFFLINE

oracle@host02 ~ $ df -h|grep /ggdata
/dev/asm/acfs_vol-177                                  299G  2.8G  297G   1% /ggdata1
dbfs-@DBFS:/                                            60G  1.4G   59G   3% /ggdata

oracle@host02 ~ $ /u02/app/12.1.0/grid/crs/script/mount-dbfs.sh status
Checking status now
Check -- ONLINE
oracle@host02 ~ $ 

What PID was killed when running mount-dbfs.sh clean?
It’s for dbfs_client.

mdinh@host02 ~ $ ps -ef|grep dbfs
oracle   34865     1  0 Mar29 ?        00:02:43 oracle+ASM1_asmb_dbfs1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   40047     1  0 Apr22 ?        00:00:10 /u01/app/oracle/product/12.1.0/db_1/bin/dbfs_client /@DBFS -o allow_other,direct_io,wallet /ggdata
oracle   40081     1  0 Apr22 ?        00:00:27 oracle+ASM1_user40069_dbfs1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
mdinh    88748 87565  0 13:30 pts/1    00:00:00 grep --color=auto dbfs
mdinh@host02 ~ $ 

It would have been so much better for mount-dbfs.sh to provide the info as part of the kill versus having user go debug script and trace process.

If you have read this far, then it’s only fair to provide log of script agent.

$ grep mount-dbfs.sh $ORACLE_BASE/diag/crs/$(hostname -s)/crs/trace/crsd_scriptagent_oracle.trc | grep "2019-04-17 20:5"
Advertisements

February 21, 2019

Simplest Automation: Use Environment Variables

Filed under: automation,GoldenGate — mdinh @ 1:25 pm

Copy the last five Goldengate trail files from source to destination.

Here are high level steps:

Copy trail with prefix (aa*) to new destination:
1. export OLD_DIRDAT=/media/patch/dirdat
2. export NEW_DIRDAT=/media/swrepo/dirdat
3. export TRAIL_PREFIX=rt*
5. ls -l $NEW_DIRDAT
6. ls $OLD_DIRDAT/$TRAIL_PREFIX | head -5
7. ls $OLD_DIRDAT/$TRAIL_PREFIX | tail -5
8. cp -fv $(ls $OLD_DIRDAT/$TRAIL_PREFIX | tail -5) $NEW_DIRDAT
9. ls -l $NEW_DIRDAT/*

Copy trail with prefix (ab*) to new destination:
export TRAIL_PREFIX=ab*
Repeat steps 5-9

February 16, 2019

GoldenGate XAG APP VIP Revisited

Filed under: GoldenGate — mdinh @ 2:17 pm

For unknown reasons, XAG integration for GoldenGate target was eradicated without any trace (I was not able to find any).

When running crsctl at target, no resources were available.

crsctl stat res -t -w 'TYPE = xag.goldengate.type'
crsctl stat res -t|egrep -A2 'dbfs|xag'

Here is an example from source:

$ crsctl stat res -t -w 'TYPE = xag.goldengate.type'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
xag.gg_xx.goldengate
      1        ONLINE  ONLINE       host_source02           STABLE
--------------------------------------------------------------------------------

$ crsctl stat res -t|egrep -A2 'dbfs|xag'
dbfs_mount
               ONLINE  ONLINE       host_source01            STABLE
               ONLINE  ONLINE       host_source02            STABLE
--
ora.dbfs.db
      1        ONLINE  ONLINE       host_source01            Open,STABLE
      2        ONLINE  ONLINE       host_source02            Open,STABLE
--
xag.gg_xx-vip.vip
      1        ONLINE  ONLINE       host_source01            STABLE
xag.gg_xx.goldengate
      1        ONLINE  ONLINE       host_source02            STABLE

Now, I need to setup XAG for target RAC Cluster.

FYI: XAG Bundled Agent was not downloaded, instead used the one available from GRID_HOME.

$ agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 3.1.0

$ agctl query deployment
The Oracle Grid Infrastructure Agents deployment is bundled

Creating XAG using 2 commands provided different metadata vs 1 command.

The difference between FILESYSTEMS is expected due to change from DBFS to ACFS.

Currently, the change is being implemented at target.

Here is an example using 2 commands:

As Root:
appvipcfg create -network=1 -ip=10.30.91.158 -vipname=xag.gg_xx-vip.vip -user=ggsuser -group=oinstall

As Oracle:
agctl add goldengate gg_xx \
--gg_home /u03/gg/12.2.0 \
--instance_type target \
--nodes target04,target02 \
--vip_name xag.gg_target-vip.vip \
--filesystems ora.acfs_data.acfs_vol.acfs \
--oracle_home /u01/app/oracle/product/12.1.0/client_2

Create source_xag_goldengate.txt target_xag_goldengate.txt using:
crsctl stat res -w "TYPE = xag.goldengate.type" -p
$ diff source_xag_goldengate.txt target_xag_goldengate.txt
< ACL=owner:ggsuser:rwx,pgrp:dba:r-x,other::r--
> ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
---
< AUTO_START=restore
> AUTO_START=never
---
< FILESYSTEMS=dbfs_mount
< GG_HOME=/u03/app/gg/12.2.0
---
> FILESYSTEMS=ora.acfs_data.acfs_vol.acfs
> GG_HOME=/u03/gg/12.2.0
---
< HOSTING_MEMBERS=source01 source02
> HOSTING_MEMBERS=target01 target02
---
< ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
> ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_2
---
< START_DEPENDENCIES=hard(xag.gg_target-vip.vip,dbfs_mount) pullup(xag.gg_target-vip.vip,dbfs_mount)
> START_DEPENDENCIES=
---
< STOP_DEPENDENCIES=hard(xag.gg_target-vip.vip,intermediate:dbfs_mount)
> STOP_DEPENDENCIES=
---
< VIP_CREATED=1
> VIP_CREATED=0

Here is an example using 1 command:

As Root:
agctl add goldengate gg_target
--gg_home /u03/gg/12.2.0
--instance_type target
--nodes target01,target02
-- filesystems ora.acfs_data.acfs_vol.acfs
--oracle_home /u01/app/oracle/product/12.1.0/client_2
--network 1 --ip 10.30.91.158
--user ggsuser
--group dba

$ diff source_xag_goldengate.txt target_xag_goldengate2.txt
< ACL=owner:ggsuser:rwx,pgrp:dba:r-x,other::r--
> ACL=owner:ggsuser:rwx,pgrp:dba:r-x,other::r--
---
< FILESYSTEMS=dbfs_mount
< GG_HOME=/u03/app/gg/12.2.0
---
> FILESYSTEMS=ora.acfs_data.acfs_vol.acfs
> GG_HOME=/u03/gg/12.2.0
---
< HOSTING_MEMBERS=source01 source02
> HOSTING_MEMBERS=target01 target02
---
< ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
> ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_2
---
< START_DEPENDENCIES=hard(xag.gg_target-vip.vip,dbfs_mount) pullup(xag.gg_target-vip.vip,dbfs_mount)
> START_DEPENDENCIES=hard(xag.gg_target-vip.vip,ora.acfs_data.acfs_vol.acfs) pullup(xag.gg_target-vip.vip,ora.acfs_data.acfs_vol.acfs)
---
< STOP_DEPENDENCIES=hard(xag.gg_target-vip.vip,intermediate:dbfs_mount)
> STOP_DEPENDENCIES=hard(xag.gg_target-vip.vip,intermediate:ora.acfs_data.acfs_vol.acfs)

In conclusion, I will be creating XAG using 1 command from now on to provide more metadata info.

Error (CLSD|CLSU-00100|CLSU-00103: error location: sclsdgcwd2|CLSD00183) Running ggsci

Filed under: GoldenGate — mdinh @ 12:25 am

Rant: Any application requiring strace for a simple problem to determine root cause is poorly written.

Oracle blog – Amardeep Sidhu January 12, 2019 Error while running ggsci

The blog above was a great help.

$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170919 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_171030.0908_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 30 2017 20:49:22
Operating system character set identified as UTF-8.

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


2019-02-15 18:04:14.827 
CLSD: An error occurred while attempting to generate a full name. Logging may not be active for this process
Additional diagnostics: CLSU-00100: operating system function: sclsdgcwd failed with error data: -1
CLSU-00103: error location: sclsdgcwd2
(:CLSD00183:)

Results from strace.

strace ./ggsci 
mkdir("/u01/app/oracle/product/12.1.0/client_2/log", 01777) = -1 EACCES (Permission denied)

I did take a different path for resolution.

gid is different for ggsuser and oracle

uid=1521(ggsuser) gid=1500(dba)      groups=1500(dba),1501(oinstall)
uid=1500(oracle)  gid=1501(oinstall) groups=1501(oinstall),1500(dba)

As root, chmod 775 -R /u01/app resolved the issue.

# cd /u01/
# chmod 775 -R app/

However, this does not explain why it was working before adding GoldenGate to CRS.

# agctl add goldengate GoldenGate_instance \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/client_2 \
--nodes node1,node2 \
--network 1 --ip 10.30.91.158 \
--user ggsuser \
--group dba \
--filesystems ora.acfs_data.acfs_vol.acfs \
--gg_home /u03/gg/12.2.0

October 27, 2018

Troubleshooting GoldenGate OGG-00303: Unable to open credential store. Error code 43,490

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

After applying Oracle GoldenGate V12.2.0.1.170919 for Oracle Database 12c OPTIMIZER Patch for Bug# 26849949, starting GoldenGate extract failed with OGG-00303: Unable to open credential store. Error code 43,490.

Here is what the report looks like.

$ head -50 E_LAX6.rpt
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
 Version 12.2.0.1.170919 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_171030.0908_FBO
   Linux, x64, 64bit (optimized), Oracle 12c on Oct 30 2017 20:59:41
 
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2018-10-25 15:08:33
***********************************************************************

Operating System Version:
Linux
Version #2 SMP Wed Jul 11 12:11:36 PDT 2018, Release 4.1.12-94.8.5.el7uek.x86_64
Node: localhost
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 23154

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2018-10-25 15:08:33  INFO    OGG-03059  Operating system character set identified as UTF-8.

2018-10-25 15:08:33  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT e_lax
USERIDALIAS gguser

Source Context :
  SourceModule            : [er.init]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150431/oggcore/OpenSys/src/app/er/init.cpp]
  SourceFunction          : [get_infile_params]
  SourceLine              : [5554]
  ThreadBacktrace         : [11] elements
                          : [/u01/gg/12.2.0/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f714024709b]]
                          : [/u01/gg/12.2.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x135) [0x7f7140241165]]
                          : [/u01/gg/12.2.0/libgglog.so(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x30) [0x7f71402308d0]]
                          : [/u01/gg/12.2.0/extract(get_infile_params(time_elt_def*, time_elt_def*, char**, ggs::gglib::ggdatasource::DataSourceParams&, ggs::Heartbeat::MapGeneratorParams&)+0x5da1) [0x5c4c91]]
                          : [/u01/gg/12.2.0/extract() [0x5f036a]]
                          : [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6cea60]]
                          : [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x6cfcdd]]

 

Since I did not have the password for database gguser, I modified the password from the database and recreated credentialstore using ggsci.

info credentialstore
delete credentialstore
create wallet.
add credentialstore
alter credentialstore add user gguser alias gguser.
alter credentialstore add user GGUSER alias GGUSER
info credentialstore

Reason check credentialstore before deleting is to determine aliases.

GGSCI 1> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: gguser
  Userid: gguser

  Alias: GGUSER
  Userid: GGUSER

Here are the steps from ggserr.log.

2018-10-25 15:44:00  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): create wallet.
2018-10-25 15:44:00  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Created wallet at location 'dirwlt'.
2018-10-25 15:44:00  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Opened wallet at location 'dirwlt'.
2018-10-25 15:44:10  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): add credentialstore.
2018-10-25 15:44:10  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store created in ./dircrd/.
2018-10-25 15:44:22  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): alter credentialstore add user gguser alias gguser.
2018-10-25 15:44:31  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store in ./dircrd/ altered.
2018-10-25 15:44:55  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): alter credentialstore add user GGUSER alias GGUSER.
2018-10-25 15:45:06  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store in ./dircrd/ altered.
2018-10-25 15:45:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): info credentialstore.
2018-10-25 15:45:15  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Reading from ./dircrd/:.

Here are the permissions for the files.

$ chmod 775 -R dirwlt/ dircrd/
$ ls -l dirwlt/ dircrd/
dircrd/:
total 4
-rwxrwxr-x 1 gguser oinstall 701 Oct 25 15:45 cwallet.sso

dirwlt/:
total 4
-rwxrwxr-x 1 gguser oinstall 290 Oct 25 15:44 cwallet.sso

Starting extract failed again!

2018-10-25 15:45:30  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): dblogin useridalias gguser.
2018-10-25 15:45:35  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): start e*.
2018-10-25 15:45:35  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.80.27.191:39060 (START EXTRACT E_LAX ).
2018-10-25 15:45:35  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #5).
2018-10-25 15:45:35  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E_LAX starting.
2018-10-25 15:45:35  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX starting.
2018-10-25 15:45:35  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Operating system character set identified as UTF-8.
2018-10-25 15:45:35  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, e_lax.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:45:35  ERROR   OGG-00303  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Unable to open credential store. Error code 43,490.
2018-10-25 15:45:35  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_lax.prm:  PROCESS ABENDING.

Set environment variables for ORACLE_HOME and ORACLE_SID for extract, and restart extract solved the issue.

FYI – environment variables already exist from OS.

2018-10-25 15:45:38  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): info all.
2018-10-25 15:52:44  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start e*.
2018-10-25 15:52:44  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.80.27.191:39169 (START EXTRACT E_LAX ).
2018-10-25 15:52:44  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #5).
2018-10-25 15:52:44  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E_LAX starting.
2018-10-25 15:52:44  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX starting.
2018-10-25 15:52:44  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Operating system character set identified as UTF-8.
2018-10-25 15:52:44  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, e_lax.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:56  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX started.

Currently, I don’t know if patching caused the issue or if it is a pre-existing condition.

What’s interesting is the same issue happened for another system where environment variables for ORACLE_HOME and ORACLE_SID were not set for extract.

Instead of restarting just the extract, all the processes were stopped, and restarted.

It would be a good idea to stop and start Goldengate processes before patching in order to identify any pre-existing conditions.

July 22, 2018

Cluster Resource To Check When Patching RAC DBFS OGG

Filed under: GoldenGate,Grid Infrastructure,RAC — mdinh @ 2:41 pm

crsctl stat res|grep -i type|sort -u

TYPE=app.appvipx.type
TYPE=local_resource
TYPE=ora.asm.type
TYPE=ora.cluster_vip_net1.type
TYPE=ora.cvu.type
TYPE=ora.database.type
TYPE=ora.diskgroup.type
TYPE=ora.listener.type
TYPE=ora.mgmtdb.type
TYPE=ora.mgmtlsnr.type
TYPE=ora.network.type
TYPE=ora.oc4j.type
TYPE=ora.ons.type
TYPE=ora.scan_listener.type
TYPE=ora.scan_vip.type
TYPE=xag.goldengate.type


crsctl stat res -p -w 'TYPE = ora.database.type' | egrep '^NAME|AUTO_START'

crsctl stat res -t -w '((TARGET != ONLINE) or (STATE != ONLINE)'

crsctl stat res -t -w 'TYPE = xag.goldengate.type' -- OGG Resource
crsctl stat res -t -w 'TYPE = app.appvipx.type'    -- OGG VIP
crsctl stat res -t -w 'TYPE = local_resource'      -- DBFS Mount
crsctl stat res -t -w 'TYPE = ora.database.type'   -- DB resource (including DBFS)

You might ask, why not use crsctl stat res -t?

For this specific environment, there are 190 lines of output and needed to focus on what’s important.

July 20, 2018

Patching GoldenGate with DBFS

Filed under: GoldenGate,Grid Infrastructure,RAC — mdinh @ 11:41 pm

There seems to be no consistency as to what directories should be on DBFS for when GoldenGate is implemented with RAC.

Here I will share my thoughts based on issues encountered.

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> 


$ ls -ld dir*
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirchk -> /dbfs_client/ggs01/dirchk
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dircrd -> /dbfs_client/ggs01/dircrd
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirdat -> /dbfs_client/ggs01/dirdat
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirdef -> /dbfs_client/ggs01/dirdef
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirdmp -> /dbfs_client/ggs01/dirdmp
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirout -> /dbfs_client/ggs01/dirout
drwxr-x--- 2 ggsuser oinstall 4096 Mar 20  2017 dirpcs
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirprm -> /dbfs_client/ggs01/dirprm
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirrpt -> /dbfs_client/ggs01/dirrpt
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirsql -> /dbfs_client/ggs01/dirsql

GoldenGate maintains data that it swaps to disk in dirtmp.
With all the issues for DBFS, might be better on local.
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirtmp -> /dbfs_client/ggs01/dirtmp

lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirwlt -> /dbfs_client/ggs01/dirwlt
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirwww -> /dbfs_client/ggs01/dirwww
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 BR -> /dbfs_client/ggs01/BR

Here are errors when applying GoldenGate Patchset.

The errors were due to the stack being down from after running opatchauto apply -norestart which results in DBFS offline for the instance.

Errors can be avoided if directories are local as they should be.

The following actions have failed:
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirout
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style

Use Oracle RAC database as a baseline.
Are alert logs, trace files, etc… on shared volume if Oracle software is installed locally?

July 14, 2018

How To Delete Integrated Extract Replicat

Filed under: 12c,GoldenGate — mdinh @ 12:46 am

Why is this important?

If processes are not unregistered from database, they are orphaned.

Hence:
unregister replicat $replicat_name DATABASE
unregister extract $extract_name DATABASE

$ ./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 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     E_DB        00:00:05      00:00:00    
EXTRACT     RUNNING     P_DB        00:00:00      00:00:00    
REPLICAT    RUNNING     R_OWL       00:00:00      00:00:03    
REPLICAT    STOPPED     R_JAY       00:00:00      00:24:15   

GGSCI 2> info E_DB debug

EXTRACT    E_DB      Last Started 2018-06-18 08:48   Status RUNNING
Checkpoint Lag       00:00:05 (updated 00:00:00 ago)
Process ID           365696
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2018-07-13 20:20:45  Seqno 151864, RBA 31183128
                     SCN 188.1068581841 (808522433489)


GGSCI 3> info r_jay debug

REPLICAT   R_JAY     Last Started 2018-06-16 11:42   Status STOPPED
INTEGRATED
Checkpoint Lag       00:00:00 (updated 578:24:33 ago)
Log Read Checkpoint  File ./dirdat/
                     2018-06-19 17:55:08.604509  RBA 409140739


GGSCI 4> info r_owl debug

REPLICAT   R_OWL     Last Started 2018-06-16 11:42   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           284878
Log Read Checkpoint  File ./dirdat/
                     2018-07-13 20:21:09.000628  RBA 132791008

GGSCI 5> exit


SQL> @pr "select * from DBA_APPLY"
APPLY_NAME                    : OGG$R_JAY
QUEUE_NAME                    : OGGQ$R_JAY
APPLY_CAPTURED                : YES
APPLY_USER                    : GGSUSER13
APPLY_TAG                     : 00
STATUS                        : DISABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 19-jun-2018 17:56:28
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Apply
LCRID_VERSION                 : 2
-------------------------
APPLY_NAME                    : OGG$R_MIG - ORPHANED REPLICAT AS PROCESS DOES NOT EXISTS
QUEUE_NAME                    : OGGQ$R_MIG
QUEUE_OWNER                   : GGSUSER12
APPLY_CAPTURED                : YES
APPLY_USER                    : GGSUSER12
APPLY_TAG                     : 00
STATUS                        : DISABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 24-feb-2018 07:12:24
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Apply
LCRID_VERSION                 : 2
-------------------------
APPLY_NAME                    : OGG$E_DB
QUEUE_NAME                    : OGG$Q_E_DB
QUEUE_OWNER                   : GGSUSER13
APPLY_CAPTURED                : YES
RULE_SET_NAME                 : RULESET$_9
APPLY_TAG                     : 00
STATUS                        : ENABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 18-jun-2018 08:48:02
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Capture
LCRID_VERSION                 : 2
-------------------------

PL/SQL procedure successfully completed.

SQL> @pr "select * from DBA_CAPTURE"
CAPTURE_NAME                  : OGG$CAP_E_DB
QUEUE_NAME                    : OGG$Q_E_DB
START_SCN                     : 776572270090
STATUS                        : ENABLED
CAPTURED_SCN                  : 808484653323
APPLIED_SCN                   : 808484649784
USE_DATABASE_LINK             : NO
FIRST_SCN                     : 776572270090
SOURCE_DATABASE               : DB01
SOURCE_DBID                   : 689358028
SOURCE_RESETLOGS_SCN          : 279476595350
SOURCE_RESETLOGS_TIME         : 826720979
LOGMINER_ID                   : 1
MAX_CHECKPOINT_SCN            : 808510343793
REQUIRED_CHECKPOINT_SCN       : 808484621637
LOGFILE_ASSIGNMENT            : IMPLICIT
STATUS_CHANGE_TIME            : 18-jun-2018 08:48:04
VERSION                       : 12.2.0.1.0
CAPTURE_TYPE                  : LOCAL
CHECKPOINT_RETENTION_TIME     : 7
PURPOSE                       : GoldenGate Capture
SOURCE_ROOT_NAME              : DB01
CLIENT_NAME                   : E_DB
CLIENT_STATUS                 : ATTACHED
OLDEST_SCN                    : 776572270090
FILTERED_SCN                  : 773378341423
-------------------------

PL/SQL procedure successfully completed.

May 18, 2018

Using GoldenGate LogDump To Find Bad Data

Filed under: GoldenGate — mdinh @ 4:38 am

GoldenGate Primary Extract from source database captured data without any issues;

however, target was not able to consume the data since GoldenGate process would ABEND.

Unfortunately, I cannot provide all the details but high level.

Logdump 2433 >pos 0
+++ Starting with GoldenGate 12.2 TDR – Table Definition Record is in trail
+++ This will provide metadata for the table

Reading forward from RBA 0 
Logdump 2434 >SCANFORMETADATA
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :    86  (x0056)   IO Time    : 2018/03/25 18:24:23.307.797   
IOType     :   170  (xaa)     OrigNode   :     1  (x01) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
DDR/TDR Idx:   (001, 000)     AuditPos   : 3277290592 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/03/25 18:24:23.307.797 Metadata             Len 86 RBA 1689 
Name:  
*
DDR Version: 1
Database type: ORACLE
Character set ID: CESU-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT-05:00
Global name: DBNAME
* 
Logdump 2435 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   561  (x0231)   IO Time    : 2018/03/25 18:28:16.317.879   
IOType     :   170  (xaa)     OrigNode   :     2  (x02) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
DDR/TDR Idx:   (001, 001)     AuditPos   : 3352410292 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/03/25 18:28:16.317.879 Metadata             Len 561 RBA 1826 
Name: SCHEMA.TABLE 
*
 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale         6)Level
 7)Null          8)Bump if Odd      9)Internal Length 10)Binary Length    11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision  15)Low Precision   16)Elementary Item  17)Occurs       18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set   22)Character Length 23)LOB Type     24)Partial Type
*
TDR version: 1
Definition for table SCHEMA.TABLE
Record Length: 4298
Columns: 7
ID           64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
A            B      C         D  E  F G H     I      J      K  L M N O P    Q R

Position 18)Key Column identify column is Primary Key; hence, ID a primary key column.
I labeled each column using the alphabet for reference and R is the 18th letter of the alphabet.

Even though ID is the first column of the table, GoldenGate offset starts with 0

SQL> desc SCHEMA.TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
0 ID                                        NOT NULL NUMBER
5 TEXT                                               VARCHAR2(4000)

SQL> r
  1  select b.column_name,a.constraint_type
  2  from dba_constraints a, dba_cons_columns b
  3  where a.table_name = b.table_name
  4  and a.constraint_name=b.constraint_name
  5  and a.constraint_type = 'P'
  6  and a.table_name='TABLE'
  7  and a.owner='SCHEMA'
  8*

COLUMN_NAME                    C
------------------------------ -
ID                             P

Logdump 2500 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   885  (x0375)   IO Time    : 2018/05/10 14:16:43.000.514   
IOType     :    15  (x0f)     OrigNode   :   255  (xff) 
TransInd   :     .  (x02)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :      36261       AuditPos   : 2145515112 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/05/10 14:16:43.000.514 FieldComp            Len   885 RBA 88598006 
Name: SCHEMA.TABLE  (TDR Index: 1) 
After  Image:                                        ....................
 0000 000c 0000 0008 3239 3533 3432 3936 0001 0009 | ........29534296....  
 0000 0005 3937 3932 3600 0200 0500 0000 0131 0003 | ....................
 0005 0000 0001 3600 0400 0b00 0000 0731 3738 3337 | ....................
 3939 0005 0325 0000 0321 456e 6a6f 7920 796f 7572 | ....................  
 2073 7461 7920 696e 206f 7572 2068 6f74 656c 7320 | ....................
 7769 7468 2074 6865 206d 6f73 7420 6469 7363 6f75 | ....................  
 6e74 6564 2072 6174                               | ....................
Column     0 (x0000), Len    12 (x000c)  
 0000 0008 3239 3533 3432 3936                     | ....29534296 --- PRIMARY KEY VALUE
Column     1 (x0001), Len     9 (x0009)  
 0000 0005 3937 3932 36                            | ....97926  
Column     2 (x0002), Len     5 (x0005)  
 0000 0001 31                                      | ....1  
Column     3 (x0003), Len     5 (x0005)  
 0000 0001 36                                      | ....6  

SQL> select id, substr(TEXT,1,30) txt, vsize(TEXT), length(TEXT) from SCHEMA.TABLE where ID in (29534296);

        ID LTXT                           VSIZE(LTEXT) LENGTH(LTEXT)
---------- ------------------------------ ------------ -------------
  29534296 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx          801           800

SQL>

For database, NLS_LENGTH_SEMANTICS=BYTE; hence, 800 characters should equal 800 bytes. 
However, one character is most likely a multi-byte character which target cannot consume 
as it is not able to handle multi-byte.

	

May 15, 2018

Goldengate REPORTING P2

Filed under: GoldenGate — mdinh @ 2:35 am

Previous post for Goldengate REPORTING

Goldengate reporting has to be the least implemented functionality until it is needed and often in hindsight.

Here is an example of what I would normally implement.

STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 15 MINUTES, RATE
DISCARDROLLOVER AT 00:01 ON SUNDAY

If business only cares about monthly data, then would not make sense to collect daily nor would it make sense for business to request such.

Next Page »

Blog at WordPress.com.