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 know IP address and manager port details of the trusted target system. And the trusted target is allowed to know VIP or Host IP of the untrusted source.

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

Manually Apply a One-Off Patch to the JDBC Thin Driver

In this example we will apply patch 17284368 on JDBC thin driver 11g.

1. Download p17284368_112040_Generic.zip from My Oracle Support.

2. Extract downloaded patch into /u01/swtmp directory.

3. Make a backup copy of the existing “ojdbc5.jar” file.

4. Copy the file “ojdbc5.jar” used by your application to the directory /u01/swtmp/17284368/files/jdbc/lib/ojdbc5.jar/.

5. Patch ojdbc5.jar

$ cd /u01/swtmp/17284368/files/jdbc/lib/ojdbc5.jar/  
$ jar uvf ojdbc5.jar *

6. Copy the patched “ojdbc5.jar” file to your test environment.

Please note that, Opatch is the recommended path to apply JDBC patches on production environments. Using this manual method is for testing purposes only.

JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516

Problem:

JDBC 11g thin clients are not able to connect to the database over SCAN and NAT. JDBC OCI and SQLPLUS connections work fine. JDBC connections to local listener work fine, only connections to SCAN listener fail.

For visibility, I will use sql developer with 11g JDBC thin driver. Actually, the problem happens on all applications using that version of JDBC.

Cause:

SQL*Plus and OCI clients use a redirect count to inform the SCAN listener about a redirected connection, but JDBC 11g and below use a lower version of NS , which does not keep a redirect count and connections are rejected.

Solution:

There are several solutions:

1.  Upgrade to JDBC 12C
2. Apply Patch 17284368 to the JDBC Thin Driver

The first option may not be acceptable for you, because it requires some changes on application side. Let’s describe how to apply patch to the JDBC thin driver:

1. Download and unzip patch file on database node(s):

$ unzip p17284368_112040_Generic.zip

2. Apply patch on db node(s):

$ cd 17284368/ 
$ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.20
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2019-02-22_20-26-57PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 17284368
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Problem with accessing response file of "/u01/app/oracle/product/11.2.0/dbhome_1/ccr/bin/setupCCR".
Backing up files…
Applying interim patch '17284368' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching component oracle.dbjava.ic, 11.2.0.4.0…
Patching component oracle.dbjava.jdbc, 11.2.0.4.0…
Patch 17284368 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2019-02-22_20-26-57PM_1.log
OPatch succeeded.

3. Copy patched $ORACLE_HOME/jdbc/lib/ojdbc5.jar to client side.

4. Restart the application:

Instead of patching database side and then copying patched ojdbc to client side, you can manually patch ojdbc thin driver without touching database.

Please consider that, manual method is for testing purposes only and is explained in the next post.

Oracle Database 18c: why Oracle skipped 6 releases?

“If you’re confused as to why Oracle skipped 6 releases, it may be simpler to regard Oracle Database 18c  as  Oracle Database 12c Release 2 12.2.0.2, where Oracle simply changed the naming to reflect the year in which the product is released.

Oracle Database 18c is the first version of the product to follow a yearly release pattern. “

https://blogs.oracle.com/database/oracle-database-18c-:-now-available-on-the-oracle-cloud-and-oracle-engineered-systems

Oracle DB Support Roadmap 2019