XAG Integration mode global setting of true does not match command line setting of false

Problem:

Not able to start Service Manager in Oracle Golden Gate Microservices architecture:

$ export OGG_CONF_HOME=/GG_HOME/sm/etc/conf 
$ /GG_HOME/ma/bin/ServiceManager

Errors in ServiceManager.log:

2019-03-23T21:59:50.237+0000 INFO | Read SharedContext from store for length 1372 of file '/GG_HOME/deploy/var/lib/conf/ServiceManager-config.dat'. (main)
2019-03-23T21:59:50.242+0000 INFO | Read SharedContext from store for length 245 of file '/GG_HOME/deploy/var/lib/conf/global.dat'. (main)
2019-03-23T21:59:50.245+0000 INFO | Read SharedContext from store for length 19 of file '/GG_HOME/deploy/var/lib/conf/ServiceManager-resources.dat'. (main)
2019-03-23T21:59:50.247+0000 ERROR| XAG Integration mode global setting of true does not match command line setting of false. (main)
2019-03-23T21:59:50.249+0000 ERROR| XAG Configuration failed (main)

Solution:

Pass `--xagEnabled` option when starting ServiceManager:

$ export OGG_CONF_HOME=/GG_HOME/sm/etc/conf
$ /GG_HOME/ma/bin/ServiceManager --xagEnabled

Oracle GoldenGate Service Manager for Oracle
Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432

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

Linux, x64, 64bit (optimized) on Sep 28 2018 17:31:51
Operating system character set identified as UTF-8.

Oracle Golden Gate: Passive Extract Configuration

When do we need passive extract?

When you have untrusted source and trusted target. Or in simple words, your untrusted source should not have IP address and manager port details of the trusted target system in the configuration. And the trusted target is allowed to know VIP or Host IP of the untrusted source.

How does it work?

1) Start command must be issued on the Trusted Target for the Alias Extract, that is intercepted by Manager.
2) Start command is forwarded to the Manager on the Untrusted Source.
3) Manager on the Untrusted Source starts Passive Extract.
4) Passive Extract retrieves data from source database.
5) After security validation, Manager on the Untrusted Source knows where to send Trails (initiator server IP). And sends captured data.
6) Trail files are read by Replicat.
7) Data from the Trails are applied to the database at the Trusted Target.

Environment Details:

Source host: primrac1.example.com
Target host: stbyrac1.example.com

Create Manager

On the source:

GGSCI (primrac1.example.com)> edit params mgr
PORT 7809
ACCESSRULE, PROG *, IPADDR *, PRI 1, ALLOW
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 60
AUTOSTART ER *

On the target:

GGSCI (stbyrac1.example.com)> edit params mgr
PORT 7809
ACCESSRULE, PROG *, IPADDR *, PRI 1, ALLOW
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 60
AUTOSTART ER *

ACCESSRULE is used to control connection access to the Manager process and the processes under its control. By default there is only deny rule.

For testing purposes I created a rule that grants access to all programs and all IP addresses. In production environment you have to filter incomming IPs. For more information about ACCESSRULE, please click here.

Modify Globals

To allow a remote Director server connection, add the following parameter in ./GLOBALS:

GGSCI (primrac1.example.com)> edit params ./GLOBALS
_DISABLEFIX21427144

Start Manager

GGSCI (primrac1.example.com)> start mgr
Manager started.
GGSCI (stbyrac1.example.com)> start mgr
Manager started.

Create Passive Extract

On the source:

# su - oracle
$ cd /GG_HOME/home_1
$ export ORACLE_HOME=/u01/app/oracle/product/18.3.0/dbhome_1
$ export ORACLE_SID=orclgg
$ ./ggsci

GGSCI (primrac1.example.com as ggcw@orclgg)> DBLOGIN USERID ggcw@ORCLGG, PASSWORD ggcw
Successfully logged into database.

GGSCI (primrac1.example.com as ggcw@orclgg)> edit param PassExtr
extract PassExtr
USERID ggcw@ORCLGG, PASSWORD ggcw
rmttrail ./dirdat/pe
table HR.SALARY;

GGSCI (primrac1.example.com as ggcw@orclgg)> add extract PassExtr, passive, integrated tranlog, begin now
EXTRACT (Integrated) added.

GGSCI (primrac1.example.com as ggcw@orclgg)> register extract PassExtr database
2019-03-21 21:45:23 INFO OGG-02003 Extract PASSEXTR successfully registered with database at SCN 8228334.

GGSCI (primrac1.example.com as ggcw@orclgg)> add rmttrail ./dirdat/pe, extract PassExtr, megabytes 1000
RMTTRAIL added.

Please note that in add extract command I have indicated keyword passive.

Create Alias Extract

On the target:

GGSCI (stbyrac1.example.com  as ggcw@orclgg)> DBLOGIN USERID ggcw@ORCLGG, PASSWORD ggcw
Successfully logged into database.

GGSCI (stbyrac1.example.com as ggcw@orclgg)> add extract AlExtr, rmthost primrac1.example.com, mgrport 7809, rmtname PassExtr
EXTRACT added.

Start Extract

START and STOP commands must be run on Alias Extract. Any GGSCI command executed against the Alias Extract is forwarded to the untrusted source for execution against the Passive Extract.

If you try to STOP or START Passive Extract, you will get the following error:

GGSCI (primrac1.example.com as ggcw@orclgg)> start PASSEXTR
ERROR: START is not allowed against a passive group.
GGSCI (primrac1.example.com as ggcw@orclgg)> stop PASSEXTR
ERROR: STOP is not allowed against a passive group.

Run start on only Alias Extract:

GGSCI (stbyrac1.example.com as ggcw@orclgg)> start AlExtr
Sending START request to MANAGER@PRIMRAC1.EXAMPLE.COM:7809 …
Sending START request to MANAGER@stbyrac1.example.com:7809 …

Check Status

GGSCI (stbyrac1.example.com as ggcw@orclgg)> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ALEXTR
GGSCI (primrac1.example.com as ggcw@orclgg)> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING PASSEXTR 00:00:00 00:18:14

For any possible problems you can check ggserr.log file under GG home.

Understanding LOAD_BALANCE parameter in TNSNAMES.ORA

This parameter can be entered under DESCRIPTION_LIST, DESCRIPTION or ADDRESS_LIST. After setting LOAD_BALANCE to a positive value, the list of addresses is processed in a random sequence.

Values: on, yes, true, off, no, false.

Default: ON for DESCRIPTION_LIST. Please note that for DESCRIPTION  and ADDRESS_LIST it is OFF by default.

Don’t be confused with the keyword balance. Oracle client does not know which database node is least loaded, this parameter only chooses addresses randomly there is no real balancing here.

The real balancing is a server-side task, when you connect to the SCAN listener it finds a least loaded node and redirects the connection to that node. LOAD_BALANCE=ON will help you to distribute the load between SCAN listeners but not evenly.

In the following test scenario, we will see how behaves client connection when using LOAD_BALANCE parameter.

Client side TNS:

  CLIENT_CON =
  (DESCRIPTION =
   (LOAD_BALANCE=ON)
    (TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.10)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST =11.11.11.11)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST =12.12.12.12)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST =13.13.13.13)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST =14.14.14.14)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST =15.15.15.15)(PORT = 1522))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclgg)
    ))

In the above connection string, I have used two other parameters. If you are not faimiliar with them, please see a brief explanation bellow:

TRANSPORT_CONNECT_TIMEOUT:

“The TRANSPORT_CONNECT_TIMEOUT parameter specifies the time, in seconds, for a client to establish a TCP connection to the database server. The default value is 60 seconds.” For more information, click here

RETRY_COUNT:

“To specify the number of times an ADDRESS list is traversed before the connection attempt is terminated.” For more information, click here         

Enable client tracing by specifying the following parameters in client sqlnet.ora file:

TRACE_LEVEL_CLIENT = USER
TRACE_FILE_CLIENT = MY_SQLNET.TRC
TRACE_DIRECTORY_CLIENT = /SQLTRACE_FOLDER
TRACE_TIMESTAMP_CLIENT = on
TRACE_UNIQUE_CLIENT = ON
DIAG_ADR_ENABLED = OFF

Try the connection using the above TNS alias and analyze generated trace file.

[10-MAR-2019 15:38:27:271] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:38:30:274] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:38:33:276] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:38:36:279] nttbnd2addr: using host IP address: 15.15.15.15 <- instead of 12 it chose 15
[10-MAR-2019 15:38:39:282] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:38:42:286] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:38:45:289] nttbnd2addr: using host IP address: 15.15.15.15
[10-MAR-2019 15:38:48:292] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:38:51:294] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:38:54:297] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:38:57:298] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:39:00:299] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:39:03:302] nttbnd2addr: using host IP address: 11.11.11.11 <- Here it used the same address again
[10-MAR-2019 15:39:06:303] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:39:09:306] nttbnd2addr: using host IP address: 15.15.15.15
[10-MAR-2019 15:39:12:309] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:39:15:312] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:39:18:314] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:39:21:315] nttbnd2addr: using host IP address: 15.15.15.15
[10-MAR-2019 15:39:24:318] nttbnd2addr: using host IP address: 13.13.13.13

From the above output we can conclude that addresses were chosen randomly.

Let’s comment LOAD_BALANCE parameter in connection string or explicitly specify LOAD_BALANCE=OFF. Increase RETRY_COUNT until 5 to see a better picture.

[10-MAR-2019 15:53:08:108] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:53:11:109] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:53:14:110] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:53:17:111] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:53:20:112] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:53:23:114] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:53:26:117] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:53:29:120] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:53:32:123] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:53:35:124] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:53:38:127] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:53:41:131] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:53:44:132] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:53:47:135] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:53:50:139] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:53:53:142] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:53:56:144] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:53:59:147] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:54:02:150] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:54:05:153] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:54:08:156] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:54:11:159] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:54:14:160] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:54:17:161] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:54:20:164] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:54:23:165] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:54:26:167] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:54:29:170] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:54:32:171] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:54:35:174] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:54:38:175] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:54:41:178] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:54:44:182] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:54:47:184] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:54:50:187] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:54:53:190] nttbnd2addr: using host IP address: 15.15.15.15

If you turn off LOAD_BALANCE then addresses are chosen using round-robin until one succeeds or until (RETRY_COUNT * #_of_addresses).

How to display certain line from a text file in Linux?

Sometimes script fails and error mentiones the line number, where there is a mistake. One option is to open a file and go to the mentioned line.

I am going to show you how to use SED to print only the certain line from the script file:

# sed -n ’80p’ /u01/app/18.3.0/grid/crs/script/mount-dbfs.sh

“My 80th line”

Where 80 is the line number and p “print[s] the current pattern space”

ORA-12578: TNS:wallet open failed while starting the database

Problem:

ORA-12578: TNS:wallet open failed encountered while starting the database using SRVCTL. Auto-login wallet doesn’t open during database startup when started using SRVCTL .

$ srvctl start database -db orcl
PRCD-1332 : failed to start database orcl
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5017: The resource action “ora.orcl.db start” encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DATA/ORCL/PARAMETERFILE/spfile.291.1001107969’
ORA-17503: ksfdopn:10 Failed to open file +DATA/ORCL/PARAMETERFILE/spfile.291.1001107969
ORA-12578: TNS:wallet open failed

There are two solutions:

Solution1:

Start the database in mount mode using sqlplus:

SQL> startup mount;

Open the wallet:

SQL> alter system set encryption wallet open identified by “wallet_password”;

Open the database:

SQL> alter database open;

Solution2:

Check the value of TNS_ADMIN value by executing:

$ srvctl getenv database -d orcl -t “TNS_ADMIN”
orcl:
PRKF-1128 : Environment variable TNS_ADMIN is not defined.

Set correct TNS_ADMIN value by executing:

$ srvctl setenv database -d orcl -T “TNS_ADMIN=/u01/app/oracle/product/18.3.0/dbhome_1/network/admin”

Change the following parameter in sqlnet.ora file:

From:

SQLNET.WALLET_OVERRIDE = TRUE

To:

SQLNET.WALLET_OVERRIDE = FALSE

Start the database:

$ srvctl start database -db orcl