Tuesday, October 21, 2014

QFSDP October 2014 new stuff in BP12

something I noticed today while patching the first database node in the super cluster with BP12 since this will be probably the last patch we will implement before going into UAT in december this year I decided to patch an isolated environment, from a Solaris side , no need to patch anything, since we were already on QFSDP july 2013.


the readme says following
"
This bundle includes fixes which can cause a plan change. These are installed in a disabled state by default.

To display such fixes included in the bundle , execute the following to get the list of bugs included in the current bundle
SQL> connect / as sysdba
SQL> set serveroutput on;
SQL> exec dbms_optim_bundle.getBugsforBundle


To display such fixes included in all bundles till now say BP12, execute the following 
SQL> connect / as sysdba
SQL> set serveroutput on;
SQL> exec dbms_optim_bundle.getBugsforBundle(12)


To switch ON all fixes with plan changes included in bundles till now
SQL> exec dbms_optim_bundle.enable_optim_fixes('ON','Scope');
Where Scope can be either of MEMORY or SPFILE or BOTH, the default is MEMORY.

To get the information on what needs to be added to init.ora to enables these fixes
SQL> set serveroutput on;
SQL> exec dbms_optim_bundle.enable_optim_fixes('ON','INITORA');

These can be disabled by following the steps mentioned below
% sqlplus /nolog
SQL> connect / as sysdba
SQL> exec dbms_optim_bundle.enable_optim_fixes('OFF','Scope');

Where Scope can be either of MEMORY or SPFILE or BOTH, the default is MEMORY.

To disable the fixes enabled using init.ora , please remove the earlier added entries.

If you are rolling back then the above steps that were done to enable/disable fixes needs be undone manually

"

I am not sure if I am happy with the fact that BP can change the execution plans but I am happy that by default those changes are not put and Oracle gives you the choice

anyway on our environment this gave following 

exec dbms_optim_bundle.getBugsforBundle

BP12:
   16825679
   19174639
   16015637
   18798414
   18365267
   18304693


But what are those fixes/bugs about 


V$system_fix_control to the rescue ...


select bugno,value ,sql_feature,description from v$system_fix_control where bugno in (16825679,19174639,16015637,18798414,18365267,18304693)
SQL> /

     BUGNO      VALUE SQL_FEATURE                    DESCRIPTION
---------- ---------- ------------------------------ ----------------------------------------------------------------
  16825679          0 QKSFM_ACCESS_PATH_16825679     Remember opn value is out of range or with in range
  19174639          0 QKSFM_OLD_PUSH_PRED_19174639   allow OJPPD with valid cases of multiple columns in predicate
  16015637          0 QKSFM_CBO_16015637             consider col bind for pred intersection
  18798414          0 QKSFM_JPPD_18798414            Unnest distinct view in IN/ANY subquery if candidate for JPPD
  18365267          0 QKSFM_JPPD_18365267            Allow JPPD into SPJ view with NO_MERGE hint
  18304693          0 QKSFM_STATS_18304693           only reinitialize table stats for column if table is analyzed



Sunday, October 19, 2014

CVE-2012-1675 listener poisoning

Our team  was asked to see how CVE_2012_1675 affected us?


On our old infrastructure we use static registration and no default 1521 port nor dynamic registration, on a new machine SSC we make use of dynamic registration, ...



On a dev machine with a DB called HP_TEST  in 11.2.0.4 PSU 2 I did following


alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=SSC_DEV)(PORT=1521))'



this generates in the listener.log on the SSC_DEV 

TNS-01184: Listener rejected registration or update of service handler "DEDICATED"
TNS-01185: Registration attempted from a remote node




which makes sense it is a different machine after all, how could it be local 


on the DEV machine  

alter system set remote_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=SSC_DEV)(PORT=1521))'


On the SSC_DEV


LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 15-OCT-2014 16:14:59

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date                01-SEP-2014 16:44:30
Uptime                    43 days 23 hr. 30 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ssc_dev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ssc_dev)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "HPTEST" has 1 instance(s).
  Instance "HPTEST", status READY, has 1 handler(s) for this service...
The command completed successfully


 the instance registered with that listener ....


So yes the exploit is possible but requires to put remote_listener parameter


There are a couple of workaround one is to use COST (Class of Secure Transport) see MOS note Doc ID 1453883.1
But this solution requires to setup a wallet etc when using RAC which we do in our new SSC infrastructure so we decided to go for the >= 11.2.0.4 solution 


Byputting following in the listener. ora this is called Valid Node Checking for Registration (Doc ID 1600630.1)



After setting following in listener.ora and reloading the config

# CVE-2012-1675
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
REGISTRATION_INVITED_NODES_LISTENER=(SSC_DEV)



 lsnrctl reload LISTENER

LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 15-OCT-2014 16:16:03

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 15-OCT-2014 16:16:09

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date                01-SEP-2014 16:44:30
Uptime                    43 days 23 hr. 31 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ssc_dev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SSC_DEV)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
The command completed successfully
/u01/app/grid/product/11.2.0.4/grid/network/admin$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 15-OCT-2014 16:16:18

Copyright (c) 1991, 2013, Oracle.  All rights reserved.



this isn't possible anymore and following error is encountered while trying to do

15-OCT-2014 16:16:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=SSC_DEV)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0
Listener(VNCR option 1) rejected Registration request from destination
15-OCT-2014 16:16:14 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""


15-OCT-2014 16:16:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=SSC_DEV)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0
Listener(VNCR option 1) rejected Registration request from destination
15-OCT-2014 16:16:14 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""


voila, hope this helps