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).

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

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

OPATCHAUTO-72043: Failed to create bundle patch object.

Problem:

Applying Patch Set Update (PSU) to Grid Infrastructure Home results in OPATCHAUTO-72043 error.

# /u01/app/18.3.0/grid/OPatch/opatchauto apply /u01/swtmp/28659165/ -nonrolling

OPatchauto session is initiated at Wed Feb 6 00:44:30 2019
System initialization log file is /u01/app/18.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-02-06_12-44-32AM.log.

Session log file is /u01/app/18.3.0/grid/cfgtoollogs/opatchauto/opatchauto2019-02-06_12-49-13AM.log

The id for this session is WRHH
OPATCHAUTO-72043: Patch collection failed.
OPATCHAUTO-72043: Failed to create bundle patch object.
OPATCHAUTO-72043: Please verify the patch supplied.
OPatchAuto failed.

Solution:

Ensure unzip of the patch as grid home owner before running opatchauto from root user.

As a workaround, execute the below command as the <grid home owner> to fix the file permissions on unzipped patch location and re-run opatchauto from root user.

chmod -R 775 <unzipped patch location>

Steps that are necessary to run DBCA via Xming (Flashgrid-enabled clusters)

1. Remove immutable flag from /etc/ssh/sshd_config file:

# chattr -i /etc/ssh/sshd_config

2. Change the following in /etc/ssh/sshd_config:

From:

#X11UseLocalhost yes

To:

X11UseLocalhost no

4. Readd immutable flag to /etc/ssh/sshd_config file:

# chattr +i /etc/ssh/sshd_config

4. Restart sshd service:

# systemctl restart sshd

5. Start Xming on your computer and reconnect to the server via putty with X11 enabled.

6. Check current DISPLAY settings:

$ echo $DISPLAY
192.168.1.2:10.0

7. Copy Xauthority file from fg to oracle

$ sudo cp /home/fg/.Xauthority /home/oracle/.Xauthority
$ sudo chown oracle:oinstall /home/oracle/.Xauthority

8. Switch to oracle user and export DISPLAY variable, assign the same value that was returned from fg user (from where you run sudo su – oracle):

$ sudo su – oracle
$ export DISPLAY=192.168.1.2:10.0

9. Run DBCA:

$ dbca

GGSCI: error while loading shared libraries: libclntshcore.so.18.1: cannot open shared object file: No such file or directory

Problem:

Oracle GoldenGate Software Command Interface (GGSCI) 18c raises error:

$ /GG_HOME/home_1/ggsci
/GG_HOME/home_1/ggsci: error while loading shared libraries: libclntshcore.so.18.1: cannot open shared object file: No such file or directory

Cause: 

GoldenGate put all necessary shared objects (.so files) under its own home directory, but several shared objects may not be found there. They should be borrowed from RDBMS or GI home.

Solution:

# find / -name libclntshcore.so.18.1

/u01/app/18.3.0/grid/.patch_storage/28090523_Jul_14_2018_00_03_50/files/lib/libclntshcore.so.18.1
/u01/app/18.3.0/grid/.patch_storage/28655784_Oct_8_2018_21_27_28/files/lib/libclntshcore.so.18.1
/u01/app/18.3.0/grid/lib/libclntshcore.so.18.1
/u01/app/18.3.0/grid/inventory/Scripts/ext/lib/libclntshcore.so.18.1

Create symbolic link:

$ cd /GG_HOME/home_1
$ ln -s /u01/app/18.3.0/grid/lib/libclntshcore.so.18.1 libclntshcore.so.18.1

Rerun ggsci.