Oracle 7 Apero

Today we definitely shutdown our last productive Oracle7 database. Quite good news actually, I can now use set newp none, sqlplus “/ as sysdba” in all my scripts, svrmgrl is no longer needed (well, sometimes in 8i to do abort, but this is rare), and we garbage a hudge bin of reference manuals and cds!

And it is a good atmosphere, we talk about the good old time and this long life with no support and no change.

We have about 20% Oracle8i, 70% Oracle9i and 10% Oracle10g right now.


The seminar yesterday focused on SOA, Service Oriented Architecture. Teacher Michel Hascoët gave us an impressive demo with his notebook and JDeveloper; the SOA Workshop is usually taking four days, and for us he did it in one day.

Ok, first ADF. With JDeveloper, he created with just a few clicks a connection to a database, dropped two screens, one for browsing customers, one for editing a customer, and just by clicking “run”, the application started in a browser.

Later, we saw the BPEL process manager in JDeveloper. I have been quite impressed. Well, I am a vi man after all, and I am typically quite sceptical about click-click-click and it works. What is really sexy in the BPEL approach is that the application is built in a natural way. You just define process, like you would do “if (y==2)”, or “x=1” in vi, but in a good-looking fashion. At the end, the application is self-documented, and this is a real bonus, because the next developer in task for your project will understand your work immediatly.

As Michel pointed out, the key point to success is the data format. If you can exchange Data from one application to another, than building a new process is just a matter of a few clicks.

In the afternoon, we had a look at BAM, the Oracle Business Activity Monitor. This Windows tool (needs a windows server and an internet explorer client) let you build graphs and send alerts according to rules and sensors you can define in JDeveloper. Michel believes this tool will be rewritten by Oracle in a near future to comply with the OS strategy of Oracle, understand Java.

At the end of the day, we have been watching a OWSM demo. Oracle Web Service Manager is a security product for your application. Instead of connecting to your OC4J component directly (with http), you actually access a Proxy server, where you can eventually add authentication with a directory server like OID and authorization, than access the OC4J url, which can be behind a firewall.

Learn more :

dba workshop

Last week I gave an internal 2-days dba course. All three students had good experience of filesystem backup and unix, but hardly any oracle experience.

I figured out recovery is more complex than I thought! By explaining to other, how often do you realise do you do not know the answer yourself? It has been very educative to me too…

First inconsistency I discovered :
I tried to simulate a disaster by removing all controlfiles, redologs, datafiles. I expected the students to notice “something”. But, O Surprise, the db kept running, nothing written in alert logs, it was possible to create and drop tables, nothing went wrong (but they were no files). Probably the OS has not notified the file were no more there, and Oracle opened the files already. Strange. Even SHUTDOWN has been successful! But of course STARTUP did not work.

Next time I prepare a B&R workshop, I must invest time to check that a disaster is effective!

Second inconsistency:

Report unrecoverable datafiles and validate backups. I wrote this in the rman technology forum this morning: REPORT NEED BACKUP is not suffisant to have consistent backups, nor RESTORE VALIDATE DATABASE!

More inconsistencies :
somehow, when users are doing backups with nocatalog and try to restore with the recovery manager catalog, it is not working. Well, it is not that surprising, but it really increases the difficulty of recovery procedures!

However, those “inconsitencies” were good in this informal training, feedback from students was positive, we did “survive” all those problems and they realised RMAN recovery is not as simple as RMAN backup…

oracle days

I was yesterday at Oracle Days. I have met a dozen of people I know, and was pleased to see Troy, who gave me one more tip for installing EM grid on a high availability cluster. I also saw Lutz, which announced me the workshop with Jonathan Lewis in a few weeks

I took two photos with my new handy, one with my collegue Zarko and one with the Oracle Partner Chocolate I gave to my daughter 🙂

Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite

Day 3 concludes the Tom workshop in Switzerland. We started with read consistency and write consistency, with an interesting example were a single row update could make a big job run thrice slower, because of the write consistency.

Once again, Tom insist on saying that you must understand Oracle to write applications.

Tom demonstrates a few 10g features. Even if I already attended his 10gR2 new features in OpenWorld, I learnt a few interesting stuff about 10gR1, and the demo on his notebook were impressing.

Ok, let’s reveal some of those 😉

You have surely heard of flashback database, but did you ever heard of flashforward ??? Well, if you flashback, open readonly, than you can flashback to the future afterwards !

Did you know about Case Insensitive? Did you try it? By writing the correct index, setting the correct parameters (NLS_COMP=ANSI, NLS_SORT=binary_ci), you will be using a regular index where doing where ENAME=’Scott’, the execution will actually reveal an index RANGE scan.

A life-saving package I have never heard of is DBMS_ADVANCED_REWRITE. This is simply a great way of rewriting a call you cannot rewrite in the application. Each call to the server could be rewritting by the dba. A great tuning capability. Maybe I will never use that, sounds a bit too much frightening to me, but I am so glad to know about it !

Finally, the TDE (transparent data encryption) demo was educative. On the one hand, if you do not have the wallet password, even if you are the DBA, than you cannot select an encrypted column!

Does it mean that you can hide data from the dba? Tom says NO WAY. The dba can always look your bind variable, look your package functions, and catch all your datas. What he think (and I do think that too), is that you can have a non-powerfull dba, which can reset your password, create a new user, drop a datafile, but which does not need to have the DBA role. Kind of pseudo-dba.

I believe that some systems contain data that no one is able to access. Not even the database administrator. In bank, it is not the case, the DBA is trusted, he signed some contracts to not reveal datas, etc… But in justice for example, I could imagine that the dba does not have the power to select any table (so will not be granted the dba role). I have been postings on forums quite a lot of time about it. For example thread 499144.995 in metalink in 2003. But I have no real experience about “secret” data which are hidded from the dba. Just thoughts.

One more great feature I learnt and will try on monday is datapump compression. We have always been compression regular export dumps with named pipe and “compress” in unix, there is finally a way to compress datapump exports.

Tom said: “I learn something about Oracle every single day”. Well, in Tom workshop, I learnt 42 new things in 3 days.

Tom Kyte Day 2

Today I asked : “Is it the responsability of the developper to create the table structure?”

The answer was something like that :
“You have four kind of persons.
– You have the Oracle6 DBA, who says always NO
– You have the Developer, who does not care about database
– You have the DBA/Developer, who understand the logical structures like IOT/Hash Cluster
– You have the Developer/DBA, who understand the database
In a perfect world, the Developer/DBA choose the table at design time and the DBA/Developer inform the developer about the database capabilities.
If you have a developer and a dba/developer, the dba/developer can reorganize the table as a tuning action.”

After, Tom talked about sql technics, show a few examples about “CONNECT BY” without prior generating rows, a pipelined table. Very interesting examples, with a few keyword like FIRST_VALUE and IGNORE NULLS that I have never used before.

Later in the afternoon, he starts talking about binding. Difficult topic, I was almost going to sys.dbms_lock.sleep at the end of the day.

We are a bit late on the program, probably too many questions, palindnilap and myself feel a bit bored about one person always asking a lot of question mostly off topic about experiences he had but nobody cares of…

Well, I am impatient about day three, it is going to be intense, read-write consistency among others themas.


Tom Kyte Day 1

Day one was quite interesting! We learnt a lot of staff about tuning approach, I have got confirmation that most of the “WE KNOW THAT, IT HAS ALWAYS BEEN SO” were maybe one day true, but are no longer, for example “separate index and tables”, or, delightfull, “you must periodically reorganise your tables”. I also learnt about DBMS_APPLICATION_INFO, which I may use in my dba-scripts too.

I also feel relaxed that designing a table to be IOT or Hash-Clustered is NOT a dba task, but a developer task. Wow, finally, the dba have to look at the database not at the table structure to gain performance…

In the evening, we had a dinner with my friend Fabrice, my ZKB-collegue Roman, my future ex-lc collegue Marc, a reader of tom blog and mine one called Leo, and also Lutz, who is working for Oracle University, and of course Tom, who enjoyed the fishes 😉

soon day 2, so i have to go to bed right now

Welcome to Switzerland

I will attend Tom Kyte 3-days session in Zurich in 2 weeks. I am immensely impatient to follow his workshop !

I hope palindnilap will be there too 😉 I invited him to come to my home to go to Tom Workshop, and to have a game of xiangqi on the brand new wooden board I received yesterday.

Last day

Very intense last day. In the morning I waked up late, and just went to OTN lounge. Well, I meet Puschitz, which speaks german too because he is austrian, and also Wim. I then ran to Moscone South for my XQuery session. Very interresting indeed…

In the afternoon, pure DBA staff :
1) shared memory
very interresting, difficult staff
2) dss
a lot of nice improvement, but highly soporific presentation
3) storage
great great great presentation from Amit Ganesh, Storage Director at oracle

Than I had a talk with Joel and Arup and a glass of wine in the garden. Than I came in the hotel, I have been kindly invited for a glass of red wine by Barbara and Lisa, two lawyers residing also in the argent hotels, thanks if you read me!

Well, I had to pack, reserve my shuttle, organise wake-up service for 05:00am

tom session + blogger dinner

What a queue for Tom Kyte session! 1000 persons have attended, and apparently a lot more had to wait outside. Very interresting. I am especially seduced by the Online Transportable Tablespace, it was SO BAD to make the tablespaces readonly before transport. I got my signed copy of his book 🙂
In the afternoon, I was too much dead to assist some session, so I visited the exhibitors and will come home with plenty of gifts for Dora and Loïc – what a pain to type the ï on an american keyboard, but alt-139 does the trick.
Than OTN/lounge, with a dozen of fans we had the opportunity to ask questions to tom.
On the evening, we had the blogger dinner. I particullary appreciated! Even if I have a terrible french accent, I have been able to chat with my neighbours. The food was good, apart from the sorbet, but the way I pronounced “sorbet” was apparently very amusing!

rac again

I remembered the time in 9iR2 when load/balancing was just dicing between the instance. In 10gR2, I noticed that load balancing is much better. I did a test, start 28 sessions from an external client, 12 went to node 1, 12 to node 2. Very impressive.

In 10gR2 RAC, the listener gets statistics collected by MMON, and balances load much more efficiently…

The Failover is also working correctly. I just powered off Doug PC (we work in team in this lab), and after second try it worked. The TNSNAMES must be configured correctly, with FAILOVER and virtual ip.

I also tried to install RAC/Linux/vmware on my notebook, but I stupidely remove the linux grub boot partition and I cannot boot anymore… I need a doctor soon 🙂

rac day 1

This first RAC day was intense! My neighbour and I just installed clusterware, configure iscsi to use LUN in SUSE and started ASM and a clustered Database!

all doc are on

timetable for tomorrow

I did my timetable in sqlplus. Quite surprised I have to travel over 15 hours to go there!!! what a trip! I have never made such a long flight in one day!

set head off feedb off
col z for a14
col y for a17
col x for a17 fold_a
alter session set nls_timestamp_tz_format=’DD. HH24:MI TZH:TZM’;

    ” z,
 ‘zurich-sf’ y, ‘sf-zurich’ x,
    ‘flight’ z,
 substr(b-a+d-c,12,5) y,substr(f-e+h-g,12,5) x,
    ‘travel’ z,
 substr(d-a,12,5) y,substr(h-f,12,5) x,
    ” z, ” y, ” x,
    ” z, ‘localtime’ y, ‘zurichtime’ x,
    ‘zurich dep. ‘ z,
 a y, a at time zone ‘Europe/Zurich’ x,
    ‘atlanta arr.’ z,
 b y, b at time zone ‘Europe/Zurich’ x,
    ‘atlanta dep.’ z,
 c y, c at time zone ‘Europe/Zurich’ x,
    ‘SF arr.     ‘ z,
 d y, d at time zone ‘Europe/Zurich’ x,
    ‘SF dep.     ‘ z,
 e y, e at time zone ‘Europe/Zurich’ x,
    ‘atlanta arr.’ z,
 f y, f at time zone ‘Europe/Zurich’ x,
    ‘atlanta dep.’ z,
 g y, g at time zone ‘Europe/Zurich’ x,
    ‘zurich arr. ‘ z,
 h y, h at time zone ‘Europe/Zurich’ x
( select
    timestamp ‘2005-02-16 10:30:00 Europe/Zurich’ a,
    timestamp ‘2005-02-16 14:55:00 US/East-Indiana’ b,
    timestamp ‘2005-02-16 16:40:00 US/East-Indiana’ c,
    timestamp ‘2005-02-16 18:29:00 US/Pacific-New’ d,
    timestamp ‘2005-02-23 08:21:00 US/Pacific-New’ e,
    timestamp ‘2005-02-23 15:44:00 US/East-Indiana’ f,
    timestamp ‘2005-02-23 17:25:00 US/East-Indiana’ g,
    timestamp ‘2005-02-24 08:30:00 Europe/Zurich’ h
  from dual)

               zurich-sf         sf-zurich
flight         15:14             13:28
travel         16:59             15:09

               localtime         zurichtime
zurich dep.    16. 10:30 +01:00  16. 10:30 +01:00
atlanta arr.   16. 14:55 -05:00  16. 20:55 +01:00
atlanta dep.   16. 16:40 -05:00  16. 22:40 +01:00
SF arr.        16. 18:29 -08:00  17. 03:29 +01:00
SF dep.        23. 08:21 -08:00  23. 17:21 +01:00
atlanta arr.   23. 15:44 -05:00  23. 21:44 +01:00
atlanta dep.   23. 17:25 -05:00  23. 23:25 +01:00
zurich arr.    24. 08:30 +01:00  24. 08:30 +01:00

on my return trip on leave sf at 8:21am and arrive at 8:30am in zurich !


What time is it? I just hope it is soon friday 🙂
Friday, I am flying to San Francisco for Oracle OpenWorld
I received this mail for the appreciation dinner : Please let me know if you are available to attend by Monday, September 12. well, I said “fine, see you on monday”. But of course it is on sunday, today is just the deadline, not the dinner.
Also on Mark Rittman Blog, I wrote the otn night is on tuesday, as written on, Tuesday, December 7, 6:00 p.m. – 10:00 p.m. Marriott Hotel, Yerba Buena Level. Of course, it was on December 7 last year, and the link is somewhat misleading… thanks for the reminder Mark, and I will attend to your dinner on tuesday.
Well, I almost had my appreciation dinner on monday, my otn night on tuesday and my blogger dinner on any other day… I definitely need to write down my appointements before going there !

OpenWorld SanFrancisco

Well, next month I am going to SF for OOW 2K5. I am invited by Oracle to participate to the Meet the expert session (otn underground). I cannot stand waiting to meet my oracle forums pals alive! I am also taking part to the xtreme sessions and I expect them to be xtreme!

It almost 20 years I have not been in the states. I am very excited !

I already installed DB 10gR2 on my notebook. It has been very easy. I first installed as a virtual machine (vmware) RedHat Entreprise Linux 3 update 3. Then Oracle. My collegues did a little bit complain about the 5 giga download, because the whole network was slow. Only 1 cd is necessary for DB installation. Rest is companion, client, clusterware, grid, etc…

I hope I can get a working 10gR2 grid in SF ! Even a beta release would be great 🙂