Thinking Out Loud

June 3, 2020

GoldenGate – Supplemental Logging Is A Mess

Filed under: 19c,GoldenGate — mdinh @ 3:22 am

I was tasked to find supplemental logging details for Oracle database used with GoldenGate.

Note: this is not a pluggable database.

With ADD TRANDATA, use dba_log_groups and dba_log_group_columns.

With ADD SCHEMATRANDATA, use select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Basically, one would need to run the query with logmnr pipeline function for all the tables in the schema.

Here is one process I used.

Create info_schematrandata.prm

$ cat info_schematrandata.prm
dblogin USERID ggs, PASSWORD *
info schematrandata *

Run ggsci using info_schematrandata.prm (full path is required)

$ ggsci paramfile /home/oracle/working/dinh/info_schematrandata.prm > info_schematrandata.log

Here is example for results (actual contains 12 schemas)

$ grep -i enable info_schematrandata.log
2020-06-01 05:19:35  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCOTT".
2020-06-01 05:19:35  INFO    OGG-01981  Schema level supplemental logging is enabled on schema "SCOTT" for all columns.

After finding the schemas, use logmnr pipeline function to find all the details.

select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;

You can find demo with logmnr$always_suplog_columns at: GoldenGate 12c Features Found in 11.2.1.0.21 ???

References:

OGG: How To Log All Columns With Add Schematrandata To Get NOCOMPRESSUPDATES (Doc ID 1413142.1)

ADD SCHEMATRANDATA does not allow columns to be specified.
This enables logging of Primary Key columns only.
By default, updates are compressed.
In order to log all columns ADD TRANDATA would have to be used.
The ADD TRANDATA can be used in conjunction with ADD SCHEMATRANDATA to specify the non-primary key columns.

How to Check Supplemental Logging When ADD SCHEMATRANDATA is Enabled (Doc ID 1537837.1)

It is not listed in dba_log_groups or dba_log_group_columns.
select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Effects of ADD TRANDATA and ADD SCHEMATRANDATA on an Oracle databases’ Supplemental Logging (Doc ID 2070331.1)

Some useful commands from ggsci:

INFO TRANDATA [container.]owner.table (info trandata *) did not work
INFO SCHEMATRANDATA schema            (info schematrandata *)
LIST TABLES table                     (list tables SCOTT.*)

Note to self:

$ cat list_table.prm
dblogin USERID ggs, PASSWORD *
list tables SCOTT.*

$ ggsci paramfile /home/oracle/working/dinh/list_table.prm > list_table.log

$ grep '\.' list_table.log | egrep -iv 'found|ggsci'| grep -A 10000 "Successfully logged into database."|grep -v database > table.log

$ cat table.log
SCOTT.EMP
SCOTT.BONUS
SCOTT.DEPT

$ cat read.sh
#!/bin/bash
IFS="."
while read f1 f3
do
echo "select * from table(logmnr\$always_suplog_columns('$f1','$f3')) order by intcol;"
done < /home/oracle/working/dinh/table.log
exit

$ ./read.sh > /tmp/suplog.sql

$ head /tmp/suplog.sql
select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;

$ cat suplog.sql
set numw 8 lines 200 timing off echo off pages 10000 trimsp on tab off
column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a65 wrap
col owner for a20
col table_name for a20
col column_name for a30
col log_group_type for a20
col column_list for a80
col log_group_name for a30
col table_name for a30
spool Database_Supplemental_Logging_Details.log
pro ******** Database ********
SELECT
name,db_unique_name,open_mode,database_role,remote_archive,switchover_status,dataguard_broker,primary_db_unique_name
FROM v$database
;
pro ******** Database Supplemental Logging ********
SELECT
supplemental_log_data_min MIN,
supplemental_log_data_pk PK,
supplemental_log_data_ui UI,
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL"
FROM v$database
;
pro ******** Table Supplemental Logging ********
pro
pro ******** GoldenGate: ADD TRANDATA ********
SELECT
g.owner, g.table_name, g.log_group_name, g.log_group_type,
DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional') always,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g, dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND g.log_group_name = c.log_group_name(+)
AND g.table_name = c.table_name(+)
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type, DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional')
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type
;
pro ******** Schema Supplemental Logging ********
pro
pro ******** GoldenGate: ADD SCHEMATRANDATA ********
@/tmp/suplog.sql
exit

April 25, 2020

GoldenGate 19.1.0.0.4 Silent Install

Filed under: 19c,GoldenGate — mdinh @ 12:22 am

GoldenGate install is so easy; don’t bother with GUI install.

Download Oracle GoldenGate 19.1.0.0.4 for Oracle on Linux x86-64 : 191004_fbo_ggs_Linux_x64_shiphome.zip

Create copy of response file:

[ggs@db-fs-1 response]$ pwd
/u01/app/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response

[ggs@db-fs-1 response]$ ls -l
total 12
-rwxr-xr-x 1 ggs oinstall  209 Apr 24 14:07 oggcore_11g_db.rsp
-rwxrwxr-x 1 ggs oinstall 4439 Jan 23  2019 oggcore.rsp
[ggs@db-fs-1 response]$

Create response file for install: just 5 lines and doesn’t get any easier than this.

[ggs@db-fs-1 response]$ cat oggcore_11g_db.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u01/app/ggs
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall
[ggs@db-fs-1 response]$

Install Goldengate: (Note: relative path is not supported for response file]

[ggs@db-fs-1 response]$ cd ../
[ggs@db-fs-1 Disk1]$ ls
install  response  runInstaller  stage

[ggs@db-fs-1 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile response/oggcore_11g_db.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 44288 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2047 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-04-24_02-08-58PM. Please wait ...
[FATAL] [INS-10101] The given response file response/oggcore_11g_db.rsp is not found.
   CAUSE: The given response file is either not accessible or do not exists.
   ACTION: Give a correct response file location. (Note: relative path is not supported)
[ggs@db-fs-1 Disk1]$


[ggs@db-fs-1 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /tmp/oggcore_11g_db.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 28928 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17407 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-04-24_02-23-29PM. Please wait ...

You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2020-04-24_02-23-29PM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   36% Done.
..................................................   54% Done.
..................................................   77% Done.
..................................................   82% Done.
..................................................   88% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   93% Done.
..................................................   95% Done.
..................................................   96% Done.
..................................................   98% Done.
..................................................   99% Done.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.
..................................................   95% Done.
Successfully Setup Software.
..................................................   100% Done.

Finish Setup successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-04-24_02-23-29PM.log' for more details.
[ggs@db-fs-1 Disk1]$

April 24, 2020

GoldenGate Monitoring With User Separation

Filed under: 19c,GoldenGate — mdinh @ 10:10 pm

Typically, GoldenGate monitoring is performed by GoldenGate user.

However, there may be circumstances where monitoring is performed by another user and this is test case for such scenario.

I did not want to create yet another user; hence, GoldenGate will be monitored by user oracle.

GoldenGate 19.1.0.0.4 with Database 11.2.0.4.

Both users (ggs and oracle) belong to the same primary group oinstall:

[ggs@db-fs-1 ggs]$ id ggs
uid=54322(ggs) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

[ggs@db-fs-1 ggs]$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54318(asmdba),54320(asmadmin),54322(dba),54323(backupdba),54324(oper),54325(dgdba),54326(kmdba)
[ggs@db-fs-1 ggs]$

Permission for GoldenGate directories are 755:

[ggs@db-fs-1 ggs]$ ls -ld dir*
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:21 dirchk
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:12 dircrd
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirdat
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirdef
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:13 dirdmp
drwxr-xr-x 3 ggs oinstall 4096 Apr 24 14:25 diretc
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:25 dirout
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirpcs
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:20 dirprm
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirrpt
drwxr-xr-x 4 ggs oinstall 4096 Apr 24 14:25 dirsca
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirsql
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirtmp
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirwlt
[ggs@db-fs-1 ggs]$

Check lag as user ggs – no issues:

[ggs@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

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



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02


GGSCI (db-fs-1) 2> info ext1

EXTRACT    EXT1      Last Started 2020-04-24 16:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           18886
Log Read Checkpoint  Oracle Redo Logs
                     2020-04-24 17:12:08  Seqno 374, RBA 14901248
                     SCN 0.1622637 (1622637)


GGSCI (db-fs-1) 3> lag ext1

2020-04-24 17:12:26  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1) 4> dblogin useridalias gguser
Successfully logged into database.

GGSCI (db-fs-1 as gguser@hawk) 5> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1 as gguser@hawk) 6> exit
[ggs@db-fs-1 ggs]$

Check lag as user oracle – issue with permissions and extract is not visible:

[oracle@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

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



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (db-fs-1) 2> sh ls -l /u01/app/ggs/dirchk/*

-rwxr-xr-x 1 ggs oinstall  2048 Apr 24 15:21 /u01/app/ggs/dirchk/EXT1.cpb
-rwxr-xr-x 1 ggs oinstall 20480 Apr 24 17:14 /u01/app/ggs/dirchk/EXT1.cpe


GGSCI (db-fs-1) 3> sh chmod 775 /u01/app/ggs/dirchk/*

chmod: changing permissions of ‘/u01/app/ggs/dirchk/EXT1.cpb’: Operation not permitted
chmod: changing permissions of ‘/u01/app/ggs/dirchk/EXT1.cpe’: Operation not permitted


GGSCI (db-fs-1) 4> sh ls -l /u01/app/ggs/dirchk/*

-rwxrwxr-x 1 ggs oinstall  2048 Apr 24 15:21 /u01/app/ggs/dirchk/EXT1.cpb
-rwxrwxr-x 1 ggs oinstall 20480 Apr 24 17:14 /u01/app/ggs/dirchk/EXT1.cpe

GGSCI (db-fs-1) 5>

Change permission for dirchk to 775:

ggs@db-fs-1 ggs]$ chmod 775 /u01/app/ggs/dirchk/*

Check lag as user oracle – issue with permissions, extract appears, lag check failed:

GGSCI (db-fs-1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:07


GGSCI (db-fs-1) 6> lag ext1

2020-04-24 17:14:58  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...

2020-04-24 17:14:58  ERROR   OGG-15161  Could not initialize the connection with EXTRACT EXT1 (Permission denied).


GGSCI (db-fs-1) 7> dblogin useridalias gguser
Successfully logged into database.

GGSCI (db-fs-1 as gguser@hawk) 8> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...

******************************************************************************************************************************
***** 2020-04-24 17:16:34  ERROR   OGG-15161  Could not initialize the connection with EXTRACT EXT1 (Permission denied). *****
******************************************************************************************************************************

GGSCI (db-fs-1 as gguser@hawk) 9>

Change permissions for all GoldenGate directories to 775:

[ggs@db-fs-1 ggs]$ chmod 775 -R dir*

Check lag using as user oracle – works perfectly:

GGSCI (db-fs-1 as gguser@hawk) 10> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1 as gguser@hawk) 11>

You might be thinking about least privileges principle.

Change permissions for GoldenGate directories (dirchk and dirtmp) to 775:

[ggs@db-fs-1 ggs]$ chmod 775 -R /u01/app/ggs/dirchk
[ggs@db-fs-1 ggs]$ chmod 775 -R /u01/app/ggs/dirtmp

[ggs@db-fs-1 ggs]$ ls -ld dir*
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:21 dirchk
drwxrwxr-x 2 ggs oinstall 4096 Apr 24 15:12 dircrd
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirdat
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirdef
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:13 dirdmp
drwxr-xr-x 3 ggs oinstall 4096 Apr 24 14:25 diretc
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:25 dirout
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirpcs
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:20 dirprm
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirrpt
drwxr-xr-x 4 ggs oinstall 4096 Apr 24 14:25 dirsca
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirsql
drwxrwxr-x 2 ggs oinstall 4096 Apr 24 16:24 dirtmp
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirwlt
[ggs@db-fs-1 ggs]$

Check lag as user oracle – SUCCESS:

[oracle@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

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



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02


GGSCI (db-fs-1) 2> lag *

2020-04-25 00:02:13  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 1 seconds.
At EOF, no more records to process


GGSCI (db-fs-1) 3> sh ls -l /u01/app/ggs/dirchk

total 28
-rwxrwxr-x 1 ggs oinstall  4096 Apr 24 20:24 EXT1.cpb
-rwxrwxr-x 1 ggs oinstall 20480 Apr 25 00:02 EXT1.cpe


GGSCI (db-fs-1) 4> sh ls -l /u01/app/ggs/dirtmp

total 0
srwxrwxr-x 1 ggs oinstall 0 Apr 24 16:24 EXT1.s


GGSCI (db-fs-1) 5> exit
[oracle@db-fs-1 ggs]$

Q.E.D.

July 24, 2019

Rsync DBFS To ACFS For GoldenGate Trail Migration

Filed under: GoldenGate,shell scripting — mdinh @ 2:25 pm

Planning to move GoldenGate trail files from DBFS to ACFS.

This is pre-work before actual migration to stress IO for ACFS.

Learned some cron along the way.

# Run every 2 hours at even hours
0 */2 * * * /home/oracle/working/dinh/acfs_ggdata02_rsync.sh > /tmp/rsync_acfs_ggdata_to_ggdata02.log 2>&1

# Run every 2 hours at odd hours
0 1-23/2 * * * /home/oracle/working/dinh/acfs_ggdata02_rsync.sh > /tmp/rsync_acfs_ggdata_to_ggdata02.log 2>&1

Syntax and ouptput.

+ /bin/rsync -vrpogt --delete-after /DBFS/ggdata/ /ACFS/ggdata
building file list ... done

dirchk/E_SOURCE.cpe
dirchk/P_TARGET.cpe

dirdat/
dirdat/aa000307647
dirdat/aa000307648
.....
dirdat/aa000307726
dirdat/aa000307727

deleting dirdat/aa000306741
deleting dirdat/aa000306740
.....
deleting dirdat/aa000306662
deleting dirdat/aa000306661

sent 16,205,328,959 bytes  received 1,743 bytes  140,305,893.52 bytes/sec
total size is 203,021,110,174  speedup is 12.53

real	1m56.671s
user	1m24.643s
sys	0m45.875s

+ '[' 0 '!=' 0 ']'

+ /bin/diff -rq /DBFS/ggdata /ACFS/ggdata

Files /DBFS/ggdata/dirchk/E_SOURCE.cpe and /ACFS/ggdata/dirchk/E_SOURCE.cpe differ
Files /DBFS/ggdata/dirchk/P_TARGET.cpe and /ACFS/ggdata/dirchk/P_TARGET.cpe differ

Only in /ACFS/ggdata/dirdat: aa000306742
Only in /ACFS/ggdata/dirdat: aa000306743
Only in /ACFS/ggdata/dirdat: aa000306744
Only in /ACFS/ggdata/dirdat: aa000306745

Only in /DBFS/ggdata/dirdat: aa000307728
Only in /DBFS/ggdata/dirdat: aa000307729

real	69m15.207s
user	2m9.242s
sys	17m3.846s

+ ls /DBFS/ggdata/dirdat/
+ wc -l
975

+ ls -alrt /DBFS/ggdata/dirdat/
+ head
total 190631492
drwxrwxrwx 24 root    root             0 Feb  9  2018 ..
-rw-r-----  1 ggsuser oinstall 199999285 Mar  8  2018 .fuse_hidden001a3c47000001c5
-rw-r-----  1 ggsuser oinstall 199999896 May 23 00:23 .fuse_hidden000002b500000001
-rw-r-----  1 ggsuser oinstall 199999934 Jul 23 06:11 aa000306798
-rw-r-----  1 ggsuser oinstall 199999194 Jul 23 06:13 aa000306799
-rw-r-----  1 ggsuser oinstall 199999387 Jul 23 06:14 aa000306800
-rw-r-----  1 ggsuser oinstall 199999122 Jul 23 06:16 aa000306801
-rw-r-----  1 ggsuser oinstall 199999172 Jul 23 06:19 aa000306802
-rw-r-----  1 ggsuser oinstall 199999288 Jul 23 06:19 aa000306803

+ ls -alrt /DBFS/ggdata/dirdat/
+ tail
-rw-r-----  1 ggsuser oinstall 199999671 Jul 24 07:59 aa000307764
-rw-r-----  1 ggsuser oinstall 199999645 Jul 24 08:01 aa000307765
-rw-r-----  1 ggsuser oinstall 199998829 Jul 24 08:02 aa000307766
-rw-r-----  1 ggsuser oinstall 199998895 Jul 24 08:04 aa000307767
-rw-r-----  1 ggsuser oinstall 199999655 Jul 24 08:05 aa000307768
-rw-r-----  1 ggsuser oinstall 199999930 Jul 24 08:07 aa000307769
-rw-r-----  1 ggsuser oinstall 199999761 Jul 24 08:09 aa000307770
-rw-r-----  1 ggsuser oinstall 199999421 Jul 24 08:11 aa000307771
-rw-r-----  1 ggsuser oinstall   7109055 Jul 24 08:11 aa000307772

+ ls /ACFS/ggdata/dirdat/
+ wc -l
986

+ ls -alrt /ACFS/ggdata/dirdat/
+ head
total 194779104
drwxrwxrwx 24 root    root          8192 Feb  9  2018 ..
-rw-r-----  1 ggsuser oinstall 199999285 Mar  8  2018 .fuse_hidden001a3c47000001c5
-rw-r-----  1 ggsuser oinstall 199999896 May 23 00:23 .fuse_hidden000002b500000001
-rw-r-----  1 ggsuser oinstall 199998453 Jul 23 04:55 aa000306742
-rw-r-----  1 ggsuser oinstall 199999657 Jul 23 04:56 aa000306743
-rw-r-----  1 ggsuser oinstall 199999227 Jul 23 04:57 aa000306744
-rw-r-----  1 ggsuser oinstall 199999389 Jul 23 04:59 aa000306745
-rw-r-----  1 ggsuser oinstall 199999392 Jul 23 05:00 aa000306746
-rw-r-----  1 ggsuser oinstall 199999116 Jul 23 05:01 aa000306747

+ ls -alrt /ACFS/ggdata/dirdat/
+ tail
-rw-r-----  1 ggsuser oinstall 199999876 Jul 24 06:48 aa000307719
-rw-r-----  1 ggsuser oinstall 199999751 Jul 24 06:50 aa000307720
-rw-r-----  1 ggsuser oinstall 199999918 Jul 24 06:51 aa000307721
-rw-r-----  1 ggsuser oinstall 199999404 Jul 24 06:52 aa000307722
-rw-r-----  1 ggsuser oinstall 199999964 Jul 24 06:54 aa000307723
-rw-r-----  1 ggsuser oinstall 199999384 Jul 24 06:56 aa000307724
-rw-r-----  1 ggsuser oinstall 199999283 Jul 24 06:57 aa000307725
-rw-r-----  1 ggsuser oinstall 199998033 Jul 24 06:59 aa000307726
-rw-r-----  1 ggsuser oinstall 199999199 Jul 24 07:00 aa000307727

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"

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.

Next Page »

Blog at WordPress.com.