Pythian Group

After attending John King’s XML Publisher presentation, Peter Koletzke and I took a “short cut” outside to get to the hotel lobby from the conference area. It is only about a 10 mile walk from the Conference rooms to the hotels. EVERYthing is bigger in Vegas! I was surprised when Peter told me it was his first time in THREE DAYS that he was outside. I guess a lot of people are either staying in the hotels here, or taking the tunnel between hotels. The hotels are set up like mini-mazes and the casinos are almost totally unavoidable. So far I have avoided all gambling temptation….other than the free slot machines in the vendor hall.

This morning I attended Tony Jambu’s session on Cryptography and Digital Signatures. Although I am used to working with VPN and ssh and generating and using public/private keys, I never really had the overview of the principles of security before. The entire concept of A.P.A.I.N was quite interesting.

My recent session was Tim Quinlan’s session on Implementing and Managing Logical Standby databases. I have been using physical standby databases for many years and have not yet had the opportunity to do a lot of work on logical standby databases.

Well….I am off for lunch.

AAAArrrgh… I hate it when using the internet and the posts get lost and I have to retype everything !!! Very tired today because we had a false alarm go off in the hotel last night at 2:30 am. It was hard to get back to sleep after the alarm and then the continuous loud speaker announcements to wait until they investigated the problem.

This morning I attended a session on Fusion from a database perspective by Dr. Paul Dorsey. It was very interesting perspective on moving the logic FROM the middle tier TO the database. Yes… that is right. Instead of moving logic to the middle tier, moving processing closer to the data, even for web pages.
(I missed the end of the session as I really wanted to go to the RAC Internals course. Sadly, it was full and I didn’t get in. Hopefully they will put on a repeat session).

Attended several very interesting other sessions this afternoon…but about to get booted off the Internet Cafe. Really enjoyed Sandep Patel’s session and demo on the Interested Transaction List. Attended Arup’s RAC Performance and John King’s XML Publisher. Both very good. And I could not do justice to try and go into details of any of them here….and I need to rush off to party :-) :-)

First day at IOUG. The opening session was an informative sneak peak at some of the new 11g features coming out. Probably all old hat to Christo, our Pythian 11g “expert”. He has been very active in the 11g Beta program.

I enjoyed Kirti Desphande’s session on Flashback Technologies. He did an excellent job covering many of the flashback features. One thing I want to check out is whether our UNDO tablespaces are autoextensible or not. Kirti described a bit in Oracle 10g release 2 (associated with the automatic undo retention tuning), where the database uses more UNDO in release 2 than release 1 if you are using fixed size files. The workaround is to set the files as autoextensibe but set the max size to the current size. Will have to check it out. Thanks Kirt.

I had a very large turnout for my session “Flash Recovery Area: Friend or Foe?”. The session was quite interactive, with lots of questions from the attendees. And gratefully, all of the live demos worked !!!

As usual, Oracle introduces new features, and we read the documentation about how they work and we start using them… but what about what is not in the documentation? That we find out by practice, experience, or accident. And that is exactly what happened to me today.

Whenever I write a procedure to be called from the scheduler (and previously via dbms_job), if I expect the changes I make to the data to be permanent, I explictly add a commit to the code. This makes the intention obvious to myself and others reviewing my code in the future. Today, I was reviewing someone else’s code. It was a packaged procedure that was called by the scheduler and it did not have a commit.

I was curious to determine how the scheduler handles this. Does the scheduler issue an implicit commit?

I scanned the online documentation and did not find any reference to whether the Oracle scheduler does implicit commits. I googled and did not find any articles that covered it. So I did what every other DBA would do — I created a test case to prove it one way or the other. Here are the steps I took.
Read the rest of this entry . . .

Yesterday I attended Julian Dyke’s session on “Inside Rac” and had the good fortune to be node 2 in a 4way node cluster. And Julian clearly demonstrated the importance of a good network in RAC, particularly for the internode interconnect.

At the focus area pubs last night, I shared with David Kurtz just how impressed I was with this conference and in particular how well the speakers are treated. I had always wanted a laser pointer and almost purchased one to bring with me…how fortuitous we received one with a slide switcher for our speaker gifts. And the speaker lounge is very well set-up and run. Having coffee and laps tops available in a quiet relaxing setting is a very nice benefit.

Sadly, Doug Burns did not bring his drinking companion to the bar last night, so I could not get a photo of the three of us together. And Lisa Dobson has been an extremely good sport as the last two nights out drinking I was telling every one “my name is Lisa Dobson so she gets blamed for anything I do”. But apparently “Lisa” was quite well behaved

The crowd is much smaller and more intimate than at UKOUG and so I have had a lot of opportunity to network with many technical gurus and ask them a question or two about some of the technical issues I have encountered recently. And although I say the crowd is more intimate, there is definetly at lot less hugging than at a North American conference. I have been told it is a British thing.

As usually I stayed up WAAAaaaaay too late socializing last night. It was quite an honour to be have invited to join the Oak Table members for dinner… sadly I was not able to stay. I headed back to my hotel to make an early night of it and got distracted by my fellow attendees in the bar…. Oh well, I did have good intentions of getting to bed early (I don’t know how these people party EVERY night!)

It’s lunchtime andhave attended three excellent sessions today. Jonathan Lewis’s ”Moving from RBO to CBO”, James Morle’s “Storage Internals for the Oracle Architect”, and Joze Senegacnik’s “Tuning with SQL Profiles”. And although it was difficult to get up so early this morning, it was definitely worth it!

And I have been meaning to mention a few of the interesting, quirky little differences in the British hotels from North America…. Firstly there are no wash clothes in any of the hotel rooms BUT there are these wonderful towel warmers. So after a shower you have this wonderful warm towel. And there is no little clock radio with annoyingly bad reception. Instead the radio channels are on the television. And the voices in the elevator have a lovely English accent. And instead of powdered milk, they have real little milkers in the rooms with the complimentary coffee services…..MUCH better than that powdered stuff.

And I never really quite understood this blogging thing before…but it is fascinating how many people here blog and have told me they mention myself (apparently Dan Fink has a photo of me showing the British how to use chairs) or The Pythian Group ( thanks Jonathan) on their blog. Others have said they have cross-posted in the past to our site ( Lisa Dobson and Andrew Clarke). It is quite an interesting phenomenom and almost makes me want to start my own blog!

Well I am quite hungry and have to run and grab a bite…. now …

Ah day two … and I thought I would have a lot more time and energy to blog today …how foolish… I totally under-estimated how busy we would be today and of course… a little tired today after partying quite late and doing laundry after the “brown beer incident”. It was great meeting several of the various people I had been corresponding with on the various Oracle related lists. And nice to meet up and spend time with old friends.

I have attended several excellent sessions today. I started the morning with Julian Dyke’s session on library cache. It was quite informative. I never really looked at relating parent and children and what caused additional children in the library cache for a parent. He did a great job of explaining the causes and how to relate the two.

I just attended both of Tanel Poder’s sessions and he had some very radical Oracle research techniques…definite “don’t do this at home” material. I am looking forward to getting back to the office and trying some of the safe tracing techniques he shared and sharing the techniques with my teammates.

Have to run off to the RAC round-table session now… until tomorrow.

Tom Kyte was the opening speaker giving us a quick peek at some of the new Oracle 11g. Two new features that really caught my eye were :the ability to apply application upgrades while the database is up and active through the use of “editions” AND the connection pooling IN the database. The second feature is enough to make me want to join the 11g Beta program alone!!

I had two presentations today so did not get to too many sessions. I can only imagine how tired Alex Gorbachev is…he did THREE !!

I did attend a session from BetFair.com that had an interesting way of using the Oracle RAC Fusion Cache as a means of implementing AQs.

The other presentation was Anjo Kolk’s presentation on SAN stories. Interesting and informative!

When you create a table in logging mode and the table has LOBs, the LOB columns will take the logging mode of the TABLESPACE. That is right, not the table but the TABLESPACE. So if you have a tablespace that is NOLOGGING and you create the LOB segments of a table in the NOLOGGING tablespace, the LOBs will be created NOLOGGING.

The changes to LOBs will not get recorded in your redo stream. But what is even worse is that you will be unaware of this until you attempt to use your standby and find the data out of sync. Normally, the database keeps track of NOLOGGING operations and you can query this. However, LOBs seem to be handled differently.

I have created the following test case :

1. Make sure the database is NOT force logging:

   select force_logging from v$database;
   

2. Create two tablespaces, one LOGGING, the other NOLOGGING:

   create tablespace nolog datafile '/opt/db/oracle/product/10.2.0/dbs/nologtest3.dbf' size 50M NOLOGGING;
   create tablespace yeslog datafile '/opt/db/oracle/product/10.2.0/dbs/yeslogtest3.dbf' size 50M LOGGING;

3. Create a table with a LOB segment in the NOLOGGING tablespace and add data:

   create table testlob
   ( col1 number, col2 varchar2(80),col3 clob)
   lob (col3)  store as ( disable storage in row tablespace nolog)
   tablespace yeslog;
   insert into testlob values ( 1, 'Test 1', 'SOME VALUE');
   insert into testlob values ( 2, 'Test 2', 'SOME VALUE');
   update testlob set col3 = lpad('X',  4000, 'X');
   commit;

4. Confirm segments in proper tablespaces:

   select table_name, column_name, segment_name, tablespace_name, index_name from user_lobs;
   select segment_name, segment_type, tablespace_name from user_segments;
   

5. And see if the database recognizes any NOLOGGING operations. (It does not.)

   select * from v$datafile;
   create table testnolog NOLOGGING tablespace yeslog as select * from dba_objects where 1=0;
   insert /*+ append */ into testnolog select * from dba_objects;
   drop table testnolog;
   select * from testlob;
   select * from dba_objects where object_name like '%REC%';
   

6. Check to see if NOLOGGING was recorded for the tablespace:

   select unrecoverable_change# unreccha
       , unrecoverable_time unrectime
       , first_nonlogged_scn firstscn
       , first_nonlogged_time firsttime
   from v$datafile;
   

   UNRECCHA	UNRECTIME	FIRSTSCN	FIRSTTIME
   0		0
   0		0
   0		0
   0		0
   0		0

   And this indicates that nothing has been identified as unrecoverable or NOLOGGING.

Typically, we monitor the v$datafile to ensure there are no NOLOGGING operations that will break our standby database. So it is important that we confirm that this mechanism actually works. We do this by creating a NOLOGGING table in a LOGGING tablespace as follows :

create table testnolog NOLOGGING tablespace yeslog as select * from dba_objects where 1=0;
insert /*+ append */ into testnolog select * from dba_objects;

But for some reason, it is showing nothing under v$datafile for the YESLOG tablespace. I know this operation will break the standby database (because of a break in archivelog activity). I am just not sure how to confirm in the data dictionary.

Well I have been back from Collaborate 2006 for over a week now and decided that I should also make my presentation available from the Pythian web site. While there I participated in three sessions. I joined Michael Abbey, Ian Abramson, and Carl Dudley on a panel for the Non-Oracle DBA.

My first presentation was PostgreSQL for the Oracle DBA. Although the crowd was small at the presentation itself, there was lots of interaction with the attendees. I was pleasantly surprised to find that almost everyone attending the session had experience in Oracle, PostgreSQL AND Mysql. It certainly attests to the growing popularity of non-proprietary databases.

My second solo-presentation was Oracle 10g Data Pump 101.  There were many many presentations on Oracle Data Pump at Collaborate 2006, from 30 minute mini-sessions to 2 hour in-depth sessions. My presentation was on day three of the conference and I was pleasantly surprised that the room was nearly full for my technical session. It was a good beginner overview of Data Pump followed by a live demo. As with most live demos, we hit a small snag but overall the presentation went well.

Enjoy the slides and if you have any questions on either of these topics, feel free to contact me via email.