patching super cluster

we patched our X4 cells for to the latest and greatest version 12.1.2.1.0 last weekend 

we had some issues with a cell that wasn’t reachable anymore during patching luckily after an init 6 of that cell the patching  continued, 

unfortunately the default 3,6 h asm disk time out was reached and the disks where removed from the disk group…

the next day we needed to upgrade DEV with GI upgrade from 11.2.0.4 to 12.1.0.2 and afterwards the 12.1.0.2.4 upgrade of january… the we upgrade the dbs from 11.2.0.4.12 to BP15 11.2.0.4.15

all went ok just one big caveat (it is in the patch notes ), once you upgrade to GI 12c the opatch  auto doesn’t work anymore for the db lower the 12d, so we had to apply those patches manually without opatch auto

so after a long day all database where patched ( mostly because i could start late, it take a while to stop all applications)…

the next day however asm had generated 64GBytes for core dumps, 

support found quickly that this was related to 

  Bug 20313024 - Exadata Solaris: ORA-7445 [ossdisk_ioctl_compl] on XDMG startup with 12.1.0.2.4 DBBP ( Doc ID 20313024.8 ) 

but then the worst still needed to follow

the unlocking of the GI and patch apply of the patch went fine, however 

 /u01/app/grid/product/12.1.0/grid/crs/install/roothas.pl -patch  didn’t complete…….

not knowing if I could stop this i opened an sr where they told me that it could be done, reexectuting didn’t change anything.

After a while I found info in the logs that point to evm issues i gave this observation  to support 

and also told them that i found old-er files in /var/tmp/.oracle and asked if i could remove them…

they said no several times. (however apparently i blogged already about this years ago here;)

In the mean time i had to increase severity to 1 because of coming dev deadline the system was already 1 day longer down than expected ...

Had some phone calls with support in US, one guy didn't know the logging changed from $GRID_HOME/log to $ORACLE_BASE/diag ....

asked them again if i could remove the .oracle files, but again no was the answer because i could break things.

next morning got contacted by support pointed again to .oracle files and there he said that it was ok to remove them 

I did and could execetute roothas.pl -patch after rebooting with inittab entries about ohas disabled  removing those entries in /var/tmp/.oracle 

the point was that i mentioned this very early in the SR and nobody listened....

anyway was happy that everything was running back again, and i hope that by blogging about this it will help other people and  that I will remember the next time i have this issue ;-)

Rac One Node and Data Guard Broker some thoughts on bugs, notes.....

Currently we are moving user acceptance testing db's from regular single instances with ZFS cross-site mirroring to Super Cluster with RAC One and Data Guard.

The initial setup of this was finished a couple of months ago

and apart from Martin Bach (@MartinDBA) suggestions here and here and in real life together with Marcin Przepiorowski (@pioro) in Ireland in march last year.

 I also used following metalink note

Data Guard Physical Standby 11.2 RAC Primary to RAC Standby using a second network (Doc ID 1349977.1) However due to some client connection issue I realized that the local vip listener wasn't used due to this : Bug 17781373 : ORAAGENT DOES NOT SET LOCAL_LISTENER WHEN LISTENER_NETWORKS IS SET IN SPFILE This exactly looked like what happened I could easily reproduce this by removing listener_networks The workaround suggested was to put the nodeVIP as local listener, but that wasn't what the dgbroker wanted because it's static connect identifier was set to the this VIP ...and I want to avoid at all cost to modify it after role changes or relocates of the Rac One instances. so I finally changed the LISTENER_NETWORKS parameter from ((NAME=network1)(LOCAL_LISTENER=RACONE)(REMOTE_LISTENER=scan01-uat:1521)), ((NAME=network_dg)(LOCAL_LISTENER=DG_VIP)(REMOTE_LISTENER=REMOTE_NET2)) To this : ((NAME=network1)(LOCAL_LISTENER=NODE_VIP,RACONE)(REMOTE_LISTENER=scan01-ora:1521)), ((NAME=network_dg)(LOCAL_LISTENER=DG_VIP)(REMOTE_LISTENER=REMOTE_NET2))

Note that NODE_VIP , RACONE , DG_VIP and REMOTE_NET2 resolve differently on each node in the RAC One Cluster.

eg.

on node1 one this maps to :

DG_VIP, NODE01_LOCAL_NET2=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node01dg-vip)(PORT = 1522))

  )

on node2 

DG_VIP, NODE02_LOCAL_NET2=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node02dg-vip)(PORT = 1522))

  )

...

also if you use alter system to change this don't forget the quotes

eg

alter system set listener_networks='(NAME=network1)(LOCAL_LISTENER=NODE_VIP,RACONE)(REMOTE_LISTENER=scan01-ora:1521))', '((NAME=network_dg)(LOCAL_LISTENER=DG_VIP)(REMOTE_LISTENER=REMOTE_NET2))';

While discussing some other things with my ex colleague Freek D'Hooge you can follow him on twitter and on his blog he pointed me to a note

How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure (Doc ID 1063571.1)

which says following

" Listeners specified by the LISTENER_NETWORKS parameter should not be used in the LOCAL_LISTENER and REMOTE_LISTENER parameters. Otherwise, cross registration will happen and connections will be redirected cross networks."

however this is not explicitly mentioned in the first note mentioned in this post.
For the time being I left the remote_listener as is and will test on newly created databases in order to remove it from already migrated uat databases...
Thanks Freek , Martin and Marcin for your insights ;-)  you guys rock !


PS I the host names in this blogpost are made up for privacy reasons .

fun facts of the day : SPFILE vs PFILE

Nothing spectacular 

some random observations made today

PFILE vs INIT.ora

when you startup with init.ora with deprecated parameters you get this 

 SQL>  startup nomount pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initU041P15.ora'ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecatedORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecatedORA-32006: USER_DUMP_DEST initialization parameter has been deprecatedORACLE instance started. 

same thing with spfile

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 8554127360 bytes

Fixed Size                  2198680 bytes

Variable Size            1006637928 bytes

Database Buffers         7532969984 bytes

Redo Buffers               12320768 bytes

opatch more powerfull then you think

This is very basic but nevertheless handy so decided to share it ;-)

with the new QFSDP out for Super Cluster I was wondering if they added the super cluster critical issues DB16 to the patch and couldn't find it in the documentation


If you unpack the patch and you do for example it returns you all the bugs solved by this patch, which can be very handy.....

opatch lspatches -bugs 20059375

 patch_id:20059375

sub-patches:17628006,17741631,17943261,18006299,18136151,18293775,18552960,18642122,18825509,19061859,19307276,19495869,19698715,19893780

unique_patch_id:18363959

date_of_patch:29 Dec 2014, 03:27:46 hrs PST8PDT

patch_description:DATABASE PATCH FOR EXADATA (JAN2015 - 11.2.0.4.15) : (20059375)

component:oracle.rdbms.crs,11.2.0.4.0,optional; oracle.ovm,11.2.0.4.0,optional; oracle.xdk.rsf,11.2.0.4.0,optional; oracle.rdbms.rsf,11.2.0.4.0,optional; oracle.rdbms.dbscripts,11.2.0.4.0,optional; oracle.rdbms.util,11.2.0.4.0,optional; oracle.oraolap,11.2.0.4.0,optional; oracle.precomp.common,11.2.0.4.0,optional; oracle.rdbms.rman,11.2.0.4.0,optional; oracle.xdk.parser.java,11.2.0.4.0,optional; oracle.ctx,11.2.0.4.0,optional; oracle.rdbms,11.2.0.4.0,optional

platform:23,Sun SPARC Solaris (64-BIT)

executable:ORACLE_HOME/bin/proc; ORACLE_HOME/bin/rman; ORACLE_HOME/bin/renamedg; ORACLE_HOME/bin/oracle; ORACLE_HOME/lib/liborasdk.so.11.1; ORACLE_HOME/lib/libclntsh.so.11.1

prereq_oneoffs:17628006; 17741631; 17943261; 18006299; 18136151; 18293775; 18552960; 18642122; 18825509; 19061859; 19307276; 19495869; 19698715; 19893780

instance_shutdown:true

online_rac_installable:true

overlay_oneoffs:17628006; 17741631; 17943261; 18006299; 18136151; 18293775; 18552960; 18642122; 18825509; 19061859; 19307276; 19495869; 19698715; 19893780

patch_type:singleton

product_family:db

auto:false

bug:19972566, DB-11.2.0.4-MOLECULE-015-CPUJAN2015

bug:18641461, Fix for bug 18641461

bug:19972568, DB-11.2.0.4-MOLECULE-016-CPUJAN2015

bug:19972569, DB-11.2.0.4-MOLECULE-017-CPUJAN2015

bug:19487147, CREATE EDITION ERRORS OUT WITH ORA-38807

bug:19402853, ORA-600 [17182] SIGNALED ON A PARALLEL SLAVE

bug:18430495, HEAVY CONTENTION FOR GROUP TABLE LATCHES

bug:20074391, PSU 11.2.0.3.13 POST-DEINSTALLATION  CATBUNDLE RETURNS ORA-20000: ORU-10027

bug:17957017, ADD MORE DIAGNOSTICS FOR ORA-600 [QESMAGETTBLSEG1]

bug:19197175, Fix for bug 19197175

bug:19488514, DEADLOCK ON CONCURRENT APPEND INSERTS AND PARTITION DDL

bug:19291380, DBUA FAILS WITH ORA-38804 WHEN THERE IS NO ORA$BASE EDITION IN SOURCE DATABASE

bug:13640676, CANNOT EXCHANGE TABLE PARTITION WITH TEXT INDEX CREATED BEFORE 11.2.0.2 UPGRADE

bug:16667538, POSSIBLE FREED MEMORY ACCESS IN KKSMAPCURSOR

bug:18938517, SINGLE ROW INSERT INTO A CLUSTER TABLE DOES A LOT OF DB FILE SEQUENTIAL READ

bug:18863094, SKGXP NOT RETURNING COMPLETED RDMA WHEN ONE IS PENDING AT THE HEAD OF THE QUEUE

snip ...

patch_id:17628006

unique_patch_id:18263291

date_of_patch:6 Nov 2013, 11:39:51 hrs UTC

patch_description:DATABASE PATCH FOR EXADATA (NOV 2013 - 11.2.0.4.1) : (17628006)

component:oracle.rdbms.rsf,11.2.0.4.0,optional; oracle.rdbms.dbscripts,11.2.0.4.0,optional; oracle.rdbms,11.2.0.4.0,optional

platform:23,Sun SPARC Solaris (64-BIT)

executable:ORACLE_HOME/bin/oracle; ORACLE_HOME/bin/orion

instance_shutdown:true

online_rac_installable:true

patch_type:singleton

product_family:db

auto:false

bug:17313525, ORION WRITE HAS CORE DUMP ON X4-2 SOLARIS RACK

bug:17546761, GRIDDISK  RESILVERING TAKES FOREVER.

bug:17265217, X2-8; INCONSISTENT ASMDEACTIVATIONOUTCOME DURING SCRUB RESILVERING

bug:17080436, DUMP MAP ELEMENTS OF COMPLETED BUFFERS IN KCFIS

bug:14852021, SPAWRIO COMPUTES INCORRECT %READS AND %OPT

bug:13364795, COLUMNAR SMART SCAN CAN FAIL TO RECOVER FROM A CORRUPT BLOCK

bug:16863422, ORA-600 [KXDAM_NOTIFY_CELL;IOCTLFAILED] DURING RPM UPGRADE

bug:16837842, OUT OF MEMORY ERROR (ORA-4030) WITH KCFIS REQUESTS

bug:17446237, KCFIS BLOCK REPAIR SHOULD BE SUPPRESSED UPON GETTING 'BLOCK TO BE RESILVERED'

bug:17465741, ORA-600 KCFIS_DECR_OVERHEADMEM_LEN_1 WITH LARGE FILE CREATION / HIGH REDUNDANCY

bug:17443671, EXADATA ERROR;'OUTPUT BUFFER FOR READ CONTROL MESSAGE PAYLOAD TOO SMALL'

Btw DB16 is related to optimisations in speed on SPARC ... (altough some of them also available for linux ;-)

9308965 RAW HAZARDS SEEN WITH RDBMS CODE ON SOLARIS T5 - Patch is NOT yet available for 11.2.0.4.0 
17391636 EVAL OF UTF8 LIKE PREDICATE IS ORDERS OF MAGNITUDE SLOWER THAN ASCII LIKE - Patch available for 11.2.0.4. 
13846337 QESASIMPLEMULTICOLKEYCOMPARE NOT OPTMIZED FOR SOLARIS SPARC64 - Patch available for 11.2.0.4. 
12660972 CHECKSUM CODE NEEDS REVISTING IN LIGHT OF NEW PROCESSORS - Patch available for 11.2.0.4.

catcdb issues

I am currently studying for my 1Z0-060 exam  using the Oracle Press book.

Oracle apparently retires the OCP 10g title as of 1th march which means you cannot use it to upgrade to 12c OCP anymore...
So I had to get started to start learning 12c, my customer just finished upgrading to 11.2.0.4 and I am putting these db's on a Sparc Super Cluster for the moment.

To learn for Ocp for me it is not sufficient to cram the course material in you head and learn by heart but also to have some hands-on.

One of the first tasks was to create a CDB, I first did it with the dbca and then wanted to do it manually as I always did.

The book suggested to create it by manually executing

catalog.sql
catblock.sql
catproc.sql ....


but I saw in the manual that you also can use catcdb.sql which basically does everything for you ...

While doing this I faced some issues with following error.

Can't locate Term/ReadKey.pm in @INC (@INC contains: /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin /u01/app/oracle/product/12.1.0/dbhome_1/perl/lib//5.14.1/i86pc-solaris-thread-multi-64 /u01/app/oracle/product/12.1.0/dbhome_1/perl/lib//5.14.1 /u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/ ../lib/site_perl/5.14.1/i86pc-solaris-thread-multi-64 ../lib/site_perl/5.14.1 ../lib/5.14.1/i86pc-solaris-thread-multi-64 ../lib/5.14.1 . /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/) at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pm line 189.

BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pm line 189.

Compilation failed in require at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl line 94.

BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl line 94.

I am not a perl afficiando but after some googling I found that on my solaris 11.2 system following needed to be set

PATH=/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin:/usr/bin:/usr/sbin:/usr/local/bin:/u01/app/oracle/product/12.1.0/dbhome_1/bin

==> to use the perl from the oracle home

export PERL5LIB=/u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/site_perl/5.14.1/i86pc-solaris-thread-multi-64:/u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/:/u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/5.14.1/:/u01/app/oracle/product/12.1.0/dbhome_1/perl/lib/site_perl/5.14.1/

Hope this helps when you encounter this 

UKOUG 2014

My 2nd time at UKOUG, the conference which got me into talking at conferences last year.


This Year I had 2 talks one joint presentation with Bjoern Rost about the Renaissance of SPARC and one about moving from M9000 to Sparc Super Cluster (both presentations will be on slideshare shortly).

Both sessions went very well we had a good feedback and a good QA during and after the session

Apart from attending sessions the best part of going to conferences is the Social Side of it meeting people.... A non exhaustive list : .... I wanted to thank for making this conference so great for me ..


thank you

Martin Bach
Bjoern "Mierenneuker" Rost ;-) btw for the dutch here the honour goes to Frits who learned bjoern a new word :-)
Martin Paul Nash
Babak Tourani
Bart Sjerps
Marcin Przepiorowski
Richard "David" Foote
Frits Hoogland
Andy Colvin
Bryn Llewellyn
David Peake
Joel Kallman
Peter Scott
Maris Elsin
Simon Haslan
Uwe Hesse
Kellyn and Tim Gorman
Chris Antognini
Mark Fielding
Ludovico Caldara
Jonathan Lewis
Iloon Ellen - Wolff
Joel GoodMan
Veronica from Enkitec
Maja Veselica
Zoran Pavlovic
Brendan Tierney
Maria Colgan
Markus Michalewicz
Oyvind Isene
Kjell Ture
Bertrand Drouvot
Debra Lilley and Jennifer Nicholson from the Otn program

you Guys and Girls made my conference

some impressions 

A special thanks also to the organisation of UKOUG for making this possible.

my highlights were the sessions of

Andy Colvin about emergency migration to exadata, Richard Foote's presentation about Exadata and indexes, the excellent presentation of Martin Bach about HCC internals and Jonathan Lewis'  Fundamentals of Troubleshooting (without graphics)  and Maris and Marcs session about Oda as a service.

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 bundleSQL> 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 nowSQL> 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 fixesSQL> 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