Sunday, December 28, 2014

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 


UPDATE :

31 th october 2016 it seems there is a note about this now





Wednesday, December 10, 2014

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.





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 



Wednesday, September 17, 2014

blog posts

I am way behind with blog posts for the moment bit too busy ;(

more to come


Philippe

Thursday, July 31, 2014

QFSDP July 2014 new : confusion version numbering

this week I applied the QFSDP of july on the SSC T4 at the customer

One thing that struck me was following :


before on the oracle db home

ssc01dbdat01z01:~$ opatch lspatches
18497417;CRS PATCH FOR EXADATA (APR2014 - 11.2.0.4.6) : (18497417)
18293775;DATABASE PATCH FOR EXADATA (APR2014 - 11.2.0.4.6) : (18293775)


Quite obvious that this is BP6

after

18522515;OCW Patch Set Update : 11.2.0.4.3 (18522515)
18825509;DATABASE PATCH FOR EXADATA (JUL2014 - 11.2.0.4.9) : (18825509)




Very confusion not ?  first of all OCW (Oracle Cluster Ware ) instead of CRS patch but also a different version number





But apparently according to  

Wednesday, July 9, 2014

UKOUG TECH 14 here I come

Just received a mail from UKOUG today


I am very exited I will do a joint presentation with Bjoern Rost name : The Renaissance of SPARC.

and will do another presentation :

What we had to unlearn and learn when moving from M9000 to Super Cluster. 

the title says it all ;-)


hope to see you in Liverpool


Sunday, June 15, 2014

ougf more impressions day 2

After the marvellous social eating fest and some after party beer

Day 2 of OUGF was there I attended Graham Woods session

 The I went to the next building to see Ann Sjökvist's presentations about SE 12c, which I really enjoyed and I was not alone ....













Then time was there to go to the SE World Round Table in a ....Lapish Kota, which was great and better then Ann, Jan and I could have imagined.
Juha Hellman from Oracle Finland was the first to present and interact with the audience and highlighted that the Sparc T5 processors are really a bargain for SE





















We had different approaches on the subject Jans take was more "needs analysis" while mine was more "spoiled kid working with EE all his life what do I loose and how can I try to add those missing things". Two different approaches that the audience really liked. 

Jan also had a great take on the TCL ( Total Cost of Loss) what is your data worth and how the protect you ...


Then My friend Oyvind was on talking about his first experience with exadata, he had a packed room ... and a room laughing when he explained what PM's are named after by dba's ... "proxies" ;-)








ilmar kerm playing with my camera ;-)




everything unfortunately has an end and the end was near, OUGF 2014 was Finnish

so how can we close this one better then with a jump picture ...


Helli, Ursula, Cooper, Ann, Petri, Marko and all of the others I forgot
thanks you very much for having me it was a great event !! well organised and great to see all those familiar (f)aces again.
Hopefully see you next year...or sooner on another Usergroup Conference 


Wednesday, June 11, 2014

Oracle Ace Associate

Yesterday I received some great news I was given the Ace Associate Acreditation, I am really honoured thank you Osama Mustafa and Brendan Tierney for submitting my candidature.


ougf some impressions part 1 (Day 0 and Day 1

I was invited to Finland last week to speak, to participate in RAC Attack and to co-host a round table on Oracle Database Standard Edition together with my friends Jan Karremans and Ann Sjökvist  (which I knew from twitter but never met in person) and Juha Hellman from Oracle Finland.


It were two busy but fantastic days, there organisation was really top notch, great food, great atmosphere, great people; I love the oracle community such a strong and nice group of technology eager nerds ;-)

It was also the occasion to meetup with my fellow pragma's : Iloon Ellen Wolff, Joel Goodman and Jan Karremans ( and not this one although jan also is arty ;-)


you can read a lot about OUGF here and here and here and here of course on twitter searching on hash tag #ougf14 so I will try to tell the story by these pictures.

The speakers dinner was in a great setting Harolds Viking Restaurant


Some Pictures to encourage you to go there the Finnish cuisine is really very very nice I loved the tar-ice-cream very very special taste.




very nice atmosphere


sometimes very fearsome vikings







did i mention the service was fast ? ;-)


The day next day we were of to Haltia a wonderful natural resort






Then this person was "keynoting about 12 more things about 12c" do you recognise him ?



or is this better

 Learned a lot about 12c which I realise should start digging into .....




Then it was James Morle's turn, for many of us this was the best keynote in years, he made some really good observations and I really enjoyed it a lot !!! I hope the slides will come online, it talked about best practices, windows of opportunity, the lack of professionalism and the copy cats implementing  best practices without thinking as a day job

The quote "Corporate IT is not IT was marvellous' and had the whole conference room smiling.





First Bjoern Rost (@brost) and Kamran Agayev (@KamranAgayev) and I were there for Rac Attack

Lappish Kota edition





Bjoern Rost having a great time in our sauna


then our first attendee Jussi showed up


It appeared that Mike Dietrich aka as Mr Upgrade had a seminar next doors so he came to say hi !!! 
thanks for dropping by Mike !!!


We were out of joy when we saw jussi tweet 

julminen
Up and running! Now I needs to br34k it.. @pfierens @brost @KamranAgayev #RACAttackNinja #ougf14 http://t.co/Yc6embQdPm
05/06/14 15:34


so we decided to make a special jump picture for him,





Then it was show time for me, talking about" LDOMS and Oracle Database what to Expect"




I had a limited but great audiance with great questions really enjoyed it.


I also liked Kamran s session about Clusterware failure scenarios





After this I went to my friend Joel Goodmans session about Global Resource Management in Rac a session I saw already before but which was really nice to see again; Joel really knows his stuff and is extremely good at explaining things



Then the last session of the day Bryn Llewellyn
 was there with some relief  (thanks Oyvind and Brost for the catering )





and we thought we could call it a wrap, but ho no time for goodies !!!!





We are in Finland right so time after this for Sauna ;-)





to our surprise there was a small live band of (ex) Oracle Finland employees bringing nice music encouraging people to dance.
They were so nice to get Oyvind on the "dance flour" for his birthday dance ...

HAPPY B-DAY Oyvind 






















I didn't take pictures from the food but that was really marvellous, freshly smoked 'salmon' Finland style ... great.


The first conference day was really great, the next day the world premier SE round table was on the agenda ... I will post info about this shortly...