Sunday, September 28, 2008

OOW'08 : Real Life Best Practices for DBAs

Once more, it was a full session with 278 attendees and 251 waitlisted due to lack of space! I wish the organizers simply moved to a bigger room. Oh, well!

For those who attended, I truly wish to express my gratitude. As a speaker, I feel honored when people choose to attend my session over others. I wish you found something useful here.

If you haven't already downloaded it from Open World site, here is the presentation:

Here is the abstract:

This session covers best practices for excelling at being a DBA in the field from someone who has been managing Oracle Database instances for 14 years; was named the DBA of the Year in 2003; and has been through it all, from modeling to performance tuning, disaster recovery, security, and beyond. Best practices should be justifiable and situation-aware, not just because someone said they were good. Hear several tips and tricks for surviving and succeeding in the part art/part wizardry of database administration. The session covers Oracle 11g.

I will highly appreciate if you post your comments.


Tonguç said...

Dear Mr.Nanda,

As usual another fantastic publication, it is always a pleasure to read your articles and now you have time for blogging also, thank you again and again. :)

I tried to meet you at OOW by the help of Reiner, but couldn't make it unfortunately, you are one of my all time heroes sir, please forgive me if I caused any trouble that day.

Warm regards from Istanbul,

Marco Gralike said...

Positive criticism/remarks as requested:

- Sheets sometimes crowded due to detail.

- Don't enable what you don't use (e.g. XMLDB Protocol server is almost always enabled, DISPATCHER)

- Contra dictionary remarks about log files (keep RMAN logging, get rid of trace and other database logfiles, no remarks on the listener).

Good suggestion would be to alter DUMP file size from the default UNLIMITED value to a small (<< 1Mb) file size. I would hate to loose the log and trace files, because they are an indicator of what went on before something actually happened. Definitely do not delete the alert.log after 2 days...

- Under 11g it would maybe also advise able to alter DIAG destination and Listener trace home to reflect the new patch/version Oracle Home ("Moving the DIAG directory": This can be done online (the old directory keeps intact / is not touched)

Anonymous said...

Hi, I attended your Best Practice presentation at OOW. It was very good . Thx !
Still a bit unclear on this "new" Patching approach though.
First you need to install a Separate NEW oracle home (feks ../db2, and then APPLY the patch
on that home . So this new (unpatched) Oracle home should be identical to your first feks ../db1
home ? - before you can apply the patch ?
It seems a bit tedious


Arup said...

Hi Kevin,

Thank you very much for the comments and compliments. I am glad you liked it.

About your question: no, you don't *need* to install on a new home. You can copy it from the existing home and update the inventory later. I find it not that difficult to install a new home; but that's left to your choice.


Anonymous said...


I attended most of your presentation and found some really good tips especially from your 'Best Practices' presentation.

I had a question regarding the ASSM or not slide.

You mentioned in the slide

But bitmap is only for 25, 50 and 75% free
• Potentially lose up to 25% space on each block.

What I understood from this, and I'd appreciate a feedback if I understood correctly, is that with ASSM your pctfree can be 25% and not lower hence you can only fill upto 75% of the block.

And I did a test on

1 select tablespace_name,EXTENT_MANAGEMENT,SEGment_SPACE_MANAGEMENT from dba_tablespaces
2* where tablespace_name in ('USAGE_TEST','USAGE_DATA')
SQL> /

-------------------- ---------- ------

Usage_data is ASSM and usage_test is MSSM.

Then I created one table in each tablespace

SQL> create table test_auto tablespace USAGE_DATA as select * from postpaid_usage_detail where rown
um <1;

Table created.

SQL> create table test_manual tablespace USAGE_test as select * from postpaid_usage_detail where row
num <1;

Table created.

The pct_free for both tables is by default 10 as shown below.

SQL> select table_name,pct_free from dba_tables where table_name like 'TEST_%';

-------------------- ----------

Also I loaded data in both of them.

SQL> insert into TEST_MANUAL select * from postpaid_usage_detail where rownum < 1000000;

999999 rows created.

SQL> commit;

Commit complete.

SQL> insert into TEST_auto select * from postpaid_usage_detail where rownum < 1000000;

999999 rows created.

SQL> commit;

Commit complete.

and as you can see below space occupied by both these tables is almost the same

1 select segment_name,(bytes)/1024/1204 M,blocks
2* from dba_segments where segment_name like 'TEST_%'
3 /

-------------------- ---------- ----------
TEST_AUTO 510,299003 19200
TEST_MANUAL 509,448505 19168

Anonymous said...

Hi Great Arup...
I really would like to understand what you suggested to Kevin about installing a "separate" OH.
One way is to use runInstaller coming from the same Oracle software distribution and then choose another different OH
Another way is to copy current OH to the new one (you mean something like: cp -r OH1 OH2) ?
And what about "update oinventory later" ?

Arup said...

For the post on ASSM space from "Anonymous" - the space usage actually applies to the blocks being linked and unlinked to freelists; not just during the initial loading. In ASSM, there are simply 5 states a block could be in - completely empty (never used), 25% full, 50% full, 75% full and completely full. Therefore, there is nothing between 100% full and 75% full. When a data block was 100% full and now it is back to 76% full, it is *not* put in the free buffers list. So, a block may potentially have 24% of its space not used. It may not be that relevant in a small OLTP database; but in DW 24% may end up being a large number.

Arup said...

For "Anonymous" on separate Oracle Home - yes, I was referring to cp -R comamnd.

Willo said...

Hi Mr. Nanda,

First of all thanks for your humanized continous collaboration for the Oracle DBA world communtities.

Mr. Nanda I have a question about patching best practices on RAC environments. It is possible to apply patching best practices recommended by you on a clusterware home?. Could I create a second clusterware home without affecting the first one?.