Archive for August, 2013

Oracle RAC SCAN is not compatibile with NAT (and thus with PCI DSS)

Tuesday, August 6th, 2013

Oracle Single Client Access Name (SCAN) – a single access point name for Oracle RAC instance VIPs – seems often to be problematic in environments that use NAT or have to be able with compliance regulations such as PCI DSS:

  1. section 1.3.3 – “Do not allow any direct connections inbound or outbound for traffic between the Internet and the cardholder data environment”
  2. section 1.3.8 – “Do not disclose private IP addresses and routing information to unauthorized parties. Note: Methods to obscure IP addressing may include, but are not limited to: Network Address Translation (NAT) [..]“

The problem with giving external/internal parties access to Oracle RAC database clusters basically could be defined as “Oracle SCAN incompatibility with Destination NAT/TCP proxies that are network brokers”, here’s simple successful connection example to TNS_ENTRY1 alias (served via single 1xSCAN raccluster-scan VIP and racnode1/racnode2 Oracle VIPs)

[someone@racnode1 ~]$ sqlplus doesnotexists@TNS_ENTRY1
[..]
[someone@racnode1 ~]$ grep -e nnftrne: -e niotns: -e nsc2addr: sqlnet_1696.trc
(1002018528) [06-AUG-2013 09:58:30:471] nnftrne: entry
(1002018528) [06-AUG-2013 09:58:30:471] nnftrne: Original name: TNS_ENTRY1
(1002018528) [06-AUG-2013 09:58:30:472] nnftrne: Using tnsnames.ora address (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raccluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TNS_ENTRY1))) for name TNS_ENTRY1
(1002018528) [06-AUG-2013 09:58:30:472] nnftrne: exit
(1002018528) [06-AUG-2013 09:58:30:512] nnftrne: entry
(1002018528) [06-AUG-2013 09:58:30:512] nnftrne: Original name: TNS_ENTRY1
(1002018528) [06-AUG-2013 09:58:30:512] nnftrne: Using tnsnames.ora address (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = raccluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TNS_ENTRY1))) for name TNS_ENTRY1
(1002018528) [06-AUG-2013 09:58:30:512] nnftrne: exit
(1002018528) [06-AUG-2013 09:58:30:527] niotns: entry
(1002018528) [06-AUG-2013 09:58:30:527] niotns: niotns: setting up interrupt handler...
(1002018528) [06-AUG-2013 09:58:30:527] niotns: Not trying to enable dead connection detection.
(1002018528) [06-AUG-2013 09:58:30:528] niotns: Calling address: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=raccluster-scan)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TNS_ENTRY1)(CID=(PROGRAM=sqlplus)(HOST=racnode1)(USER=someone))))
(1002018528) [06-AUG-2013 09:58:30:529] nsc2addr: (DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TNS_ENTRY1)(CID=(PROGRAM=sqlplus)(HOST=racnode1)(USER=someone)))(ADDRESS=(PROTOCOL=TCP)(HOST=10.xx.yy.43)(PORT=1521)))
(1002018528) [06-AUG-2013 09:58:30:537] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.xx.yy.42)(PORT=1521)))
(1002018528) [06-AUG-2013 09:58:30:572] niotns: niotns: passing ns handle back up...
(1002018528) [06-AUG-2013 09:58:30:572] niotns: exit
[someone@racnode1 ~]$

As you can see from the above example the following is happening:
a) client is connecting via TCP/1521 to raccluster-scan
b) SCAN listener on raccluster-scan redirects – via TCP data – to hostname of the least loaded Oracle VIP (!)
c) client spawns a new connection directly to the Oracle VIP on TCP/1521 (!!)

The main problem is that when you have DNAT defined with single TCP port redirection that looks like this some Oracle client –TCP port 33333 –> DNAT/proxy_redirection –> Oracle SCAN VIP, this is not going to work because Oracle SCAN listener will redirect “some Oracle client” to Oracle VIP TCP/1521. This will fail in 1 out of 3 ways:

  1. failure scenario1: client might fail to resolve hostname using it’s own DNS
  2. failure scenario2: client might succeed DNS hostname resolution of OracleVIPX resolution but is going to be unable to reach the private VIP address via IP routing because routers/default gateway won’t know how to route to the destination (e..g client is coming from different organization, internet, etc)
  3. failure scenario3: client might succeed DNS hostname resolution, might even be able to route to the the OracleVIPX IP is, but there is firewall somewhere in between (e.g. on the same box that is doing DNAT/TCP proxy) blocking “some client” to reach directly OracleVIPX on TCP/1521

There are several solutions to this problem:
1) don’t use Oracle SCAN simple name but provide long TNS conneciton string with a number of NAT-ted/proxied TCP ports defined (every Oracle RAC node VIP should have its own NATTed/proxied TCP port for failover purposes – Oracle client is responsible for load balancing and failovering Oracle connections)
- or -
2) implement some kind of highly available TCP proxy that intelligently routes Oracle traffic to healthy RAC instances (e.g. HAproxy if you really have clients that support only short Oracle hostnames)
- or -
3) do tricks on external-facing DNS name servers (or locally on clients by placing entries on /etc/hosts) by spoofing internal host names as proxy one, but you will need 1:1 ratio of external to internal IPs to meet this goal (remember each Oracle VIP serves connections on port 1521!)
- or -
4) THE SIMPLEST ONE: do not use NAT and allow direct traffic on firewall, as NAT is evil :)

-J.

gv$dataguard_stats is buggy even on most recent 11.2.0.3.x PSUs

Tuesday, August 6th, 2013

Oracle introduced a new RAC-enabled view for DataGuard named gv$dataguard_stats probably starting with 11.2. However this view returns false information when queried on RAC standby node where MRP0 is not running, sample:

1st instance of standby RAC with MRP0 running:

SQL> select name,value from v$dataguard_stats;

NAME                           VALUE
------------------------------ --------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         18

SQL> select name,value from gv$dataguard_stats;

NAME                           VALUE
------------------------------ --------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         18
transport lag                  +00 00:00:00
apply lag                      +00 00:21:56
apply finish time              +00 00:00:41.526
estimated startup time         29

8 rows selected.

SQL>

According to this gv$dataguard_stats output there lag is 0s on 1st node (itself) and 21mins (time since when MRP0 has been relocated from 2nd node to 1st one) – this is wrong, because when you ask 2nd RAC instance for its local view of DataGuard apply lag you will see 0s.

SQL> select name,value from v$dataguard_stats;

NAME                             VALUE
-------------------------------- ------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:00:00
apply finish time                +00 00:00:00.000
estimated startup time           29

SQL>

This inconsistency has been reported to Oracle in SR 3-6005743051: inconsistent gv$dataguard_stats on RAC standby. They did not register bug (even for docs) but returned with the following clarification:

the rows “apply lag time”, “apply finish time” and “estimated startup time” are relevant only for the applying instance. You are allowed to send log files to several instances – in such a case “transport lag” is relevant for all receiving instances.

This has certain repercussions as if you want to monitor standby lag properly – you will need to locate first the MRP0 process and then query this instance via dedicated connection for v$dataguard_stats. Doing shortcuts by using gv$dataguard_stats might return false results. Another question is why such (buggy in my opinion) view has been introduced at all if it represents no value.

-J.