10.2.0.4 available on solaris sparc 64bit

Just noticed on www.dba-village.com that the 10.2.0.4 patch is finally available for sparc 64bit.

Just to let you know…

open source or freeware

Say you have put in a lot of effort in developing a pl/sql package that helps you in your job.
You think it does a reasonable decent job and you are thinking about releasing it to the rest of the oracle community.
However you are affraid to lose control over your application. I mean, you have put so much work in it, can you then just give it out hand or would you still want to control it?

Of course you could wrap it and distribute it as freeware, but who would install a wrapped package from the internet into their production databases?
You could try to work arround that by splitting of part of the package needing a higher level of privileges into separate packages which are left unwrapped and can be installed into a different schema. But would this be sufficient?

You would also be the sole responsible for support and bug fixing as you are the only one with access to the code. That is the only one, excluding those who have access to an unwrapper, with which they can revert your code back into its original state (including variable names).

Another option would be to share the source code, but what would happen then? Probably people would start modifying your code.
Is this a bad thing? Maybe that depends if they afterwards would start bothering you with questions as “why it doesn’t work” or not.
Would you allow people to send in their modifications or would you only maintain your own version? Could these modified “versions” then still use the same name as your original package or not?

Then there is the question of licensing. Would you just publish the code, or would you put a licensing on it? If so, which one?

Many questions and few answers.
So tell me, what would you do and how would you do it?

fun with frequenty based histograms

Recently I was reading in Jonathan Lewis’ book “cost-based oracle fundamentals” ( I’m reading this small peaces at a time, otherwise my brain gets overloaded and my head feels like it’s going to explode) about histograms, when I got to page 168 where Jonathan shows the output of tests with frequenty based histograms. In these tests Jonathan modifys some CBO statistics by using the set_table_stats and the set_column_stats procedures of the dbms_stats package.

In the first test Jonathan decreases the num_rows of the table, resulting in a decrease of the cardinality in the execution plan. I was surprised by this result, as with a frequenty based histogram available, Oracle knows the exact number of records for the given predicate. Why then would changing the number of rows of the table change the cardinality? So, I did what I always do when I don’t understand something: I try to work out an example myself.

First I created a table with 10 different values, 1 of the values appearing in 50% of the records.

fdh@LOKI> drop table t;

Table dropped.

fdh@LOKI> create table t (veld1 number(10,0));

Table created.

fdh@LOKI> insert into t
  2  select mod(rownum, 9) + 1
  3  from all_objects, all_objects
  4  where rownum  insert into t
  2  select 10
  3  from all_objects, all_objects
  4  where rownum  commit;

Commit complete.

fdh@LOKI> select veld1, count(*) counted
  2  from t
  3  group by veld1
  4  order by veld1;

     VELD1    COUNTED
---------- ----------
         1       5555
         2       5556
         3       5556
         4       5556
         5       5556
         6       5556
         7       5555
         8       5555
         9       5555
        10      50000

10 rows selected.

After creating the data I gathered the statistics, asking for a histogram on the field “VELD1″ with 10 buckets ( which would generate a frequenty based histogram ).

fdh@LOKI> BEGIN
2
3      dbms_stats.gather_table_stats
4          ( ownname           =>  NULL,
5            tabname           =>  'T',
6            method_opt        =>  'FOR COLUMNS VELD1 SIZE 10'
7          );
8
9  END;
10  /

PL/SQL procedure successfully completed.

fdh@LOKI> select num_distinct, num_nulls, num_buckets, histogram, density from user_tab_col_statistics where table_name = 'T' and column_name = 'VELD1';

NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM            DENSITY
------------ ---------- ----------- --------------- ------------
          10          0          10 FREQUENCY         ,000004918

fdh@LOKI> select endpoint_number, endpoint_value from user_tab_histograms where table_name = 'T' and column_name = 'VELD1';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
            314              1
            635              2
            943              3
           1269              4
           1573              5
           1911              6
           2200              7
           2512              8
           2802              9
           5592             10

10 rows selected.

Apparently the CBO is thinking that the value 10 appeared 2.790 times (5592 - 2802), which is far less then the 50.000 I would have expected. Also the other values where much lower then the actual counts. The reason for this became clear after querying the user_tables view:

fdh@LOKI> select num_rows, sample_size from user_tables where table_name = 'T';  

  NUM_ROWS SAMPLE_SIZE
---------- -----------
    101673        5592

Oracle generated the statistics based upon a sample, not the entire table.
A quick check on the dbms_stats.gather_table_stats procedure indeed revealed that I had not included the estimate_percent parameter and that this parameter in 10g did not have the default value of “NULL” (compute) anymore. The values showed where the values occuring in the sample. It took me a while to realise that this was also the reason why lowering the num_rows would change the cardinality, but eventually it seeped into my numbed brain: oracle always extrapolates the values it finds in the histograms. It is just that when you don’t use a sample, the num_rows equals the sample_size, giving a fraction of 1. When you later on then lower the value of num_rows the fraction changes and thus does the cardinality. Therefor the cardinality for a predicate of the form column = constant (with a frequenty based histogram in place) would be:

           row_count * (user_tables.num_rows / user_tables.sample_size)

where row_count is the number derived from the histogram.

A second example given by Jonathan however showed that the num_nulls also played a role in the calculation, changing the formula to:

           row_count * ((user_tables.num_rows - user_tab_columns.num_nulls) / user_tables.sample_size)

But this striked me as odd. It would only make sense if the sample size did not include the record for which the value was NULL. Something which is not possible for the sample_size column in user_tables as the number of nulls would differ per column. A quick look on the columns of the user_tab_columns view revealed that there was indeed a sample_size column in this view as well ( the cbo can use a different sample size for the histograms then for the table). To test this further, I updated the table - setting veld1=NULL where veld1 = 1 - and gathered the statistics again (using compute this time).

fdh@LOKI> select sample_size, num_distinct, num_nulls, num_buckets, histogram, density from user_tab_columns where table_name = 'T' and column_name ='VELD1';

SAMPLE_SIZE NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM             DENSITY
----------- ------------ ---------- ----------- --------------- -------------
      94445            9       5555           9 FREQUENCY         ,0000052941

fdh@LOKI> set autotrace traceonly explain
fdh@LOKI> select veld1 from t where veld1 = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |    97K|    36   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 50000 |    97K|    36   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("VELD1"=10)

Which shows that the sample_size was indeed excluding the num_nulls and that the cardinality of the query would still be correct. So the formulla actually becomes:

           row_count * ((user_tables.num_rows - user_tab_columns.num_nulls) / user_tab_columns.sample_size)

Although I now understand why changing the number of rows or the number of nulls will change the cardinality I’m left with the question of why oracle is not just storing the extrapolated numbers, instead of making the calculation each time it needs to now the cardinality? Maybe I’m overestimating the cost of this calculation, but it just seems to me as a more efficient thing to do.

Oh well, 1 question answered, 99 to go… .

bugs introduced via patches

“Sometimes” you have to apply patches to your oracle system to fix bugs. This is however not without risk, as patches are just code and thus can contain bugs themself. The same is true for the scripts used to apply the patches, as I found out first hand.
Recently I needed to perform a drop node / add node procedure on an oracle 10gR2 rac (on solaris 10).
During this procedure I ran into the following problems:

When using ocrconfig to check the backups of the cluster registry, I got the following error:

ld.so.1: ocrconfig: fatal: libocr10.so: open failed: No such file or directory
Killed

After some searching on metalink I found the following bug: 6342492 - ld.so.1: ocrcheck: fatal: libocr10.so: open failed after patch 6000740.
Patch 6000740 is bundle patch MLR7, and the problem with this patch is that the path to make has been hardcoded to a value which is not correct on solaris 10. The solution is to create a symbolic link from /usr/ccs/bin/make to /usr/bin/make, and perform a relink all.

Yep, a hardcoded path. The same problem which requires you to create symbolic links for scp and ssh into the /usr/local/bin directory on solaris 10 when installing a rac system. You would think they learn from their mistakes… .

The second problem I ran into, appeared during the addnode procedure. During the copy of the cluster home to the new node, oracle complained that not all files could be copied.
When I checked the logfile I found the following messages:

WARNING: Error while copying directory /opt/oracle/crs with exclude file list '/tmp/OraInstall2008-02-09_04-20-28PM/installExcludeFile.lst' to no
des 'eocpc-rc01'. [PRKC-1073 : Failed to transfer directory "/opt/oracle/crs" to any of the given nodes "eocpc-rc01 ".
Error on node eocpc-rc01:tar: ./lib/prod/lib: Permission denied
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory
tar: ./lib/prod/lib: Permission denied
tar: cannot open ./lib/prod/lib/v9 No such file or directory]

All problem files where located in the same directory, and this directory was lacking write privileges for the oracle user.

[oracle@eocpc-rc02:~]$ find /opt/oracle/crs/lib/prod -exec ls -ald {} \;
dr-xrwx—   3 oracle   oinstall     512 May  5  2007 /opt/oracle/crs/lib/prod
drwxrwx—   3 oracle   oinstall     512 May  5  2007 /opt/oracle/crs/lib/prod/lib
drwxrwx—   2 oracle   oinstall     512 May  5  2007 /opt/oracle/crs/lib/prod/lib/v9
-rw-rw—-   1 oracle   oinstall    2824 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/CCrti.o
-rw-rw—-   1 oracle   oinstall    1232 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/CCrtn.o
-rw-rw—-   1 oracle   oinstall    3064 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/crt1.o
-rw-rw—-   1 oracle   oinstall     776 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/crti.o
-rw-rw—-   1 oracle   oinstall     712 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/crtn.o
-rw-rw—-   1 oracle   oinstall   14736 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/libCCexcept.so.1
-rw-rw—-   1 oracle   oinstall   19056 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/libldstab_ws.so

A quick check on a new, net yet patched installation confirmed that this was not a normal situation:

-[oracle@eocsp-rc51:~]$ find /opt/oracle/crs/lib/prod -exec ls -ald {} \;
drwxrwx—   3 oracle   oinstall     512 Feb  7 23:47 /opt/oracle/crs/lib/prod
drwxrwx—   3 oracle   oinstall     512 Feb  7 23:47 /opt/oracle/crs/lib/prod/lib
drwxrwx—   2 oracle   oinstall     512 Feb  7 23:48 /opt/oracle/crs/lib/prod/lib/v9
-rw-rw—-   1 oracle   oinstall    2824 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/CCrti.o
-rw-rw—-   1 oracle   oinstall    1232 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/CCrtn.o
-rw-rw—-   1 oracle   oinstall    3064 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/crt1.o
-rw-rw—-   1 oracle   oinstall     776 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/crti.o
-rw-rw—-   1 oracle   oinstall     712 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/crtn.o
-rw-rw—-   1 oracle   oinstall   14736 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/libCCexcept.so.1
-rw-rw—-   1 oracle   oinstall   19056 Mar 13  2003 /opt/oracle/crs/lib/prod/lib/v9/libldstab_ws.so

Making the lib/prod directory in the crs home writable again for the oinstall group did indeed solve the problem.
Sofar I have found 2 different patches which introduces this problem:

• 6000740 MLR7
• 5749953 ons sigbus error after install patchset 10.2.0.3 for crs

A quick search on metalink did not found this problem listed as a known bug. I will file one next week. (If I find the time)

Interesting to note is that these 2 problems are also not likely to be discovered by applying the patch on a test system.
I don’t think there are many dba’s who are performing an addnode procedure when testing a new patchset…

Gunnar

Hello World,

My name is Gunnar D’Hooge, and I’m Freek and Edda’s son.
I just dropped by to inform you all of my birth on 14, Februari.
Yes, I’m feeling well, thank you.

I’m looking forward to start working with Oracle, but my dad told me I will still have to wait a couple of years before I can start working on a pc.
I was really sad when I heard this, so dad bought me a special lamp. It has the shape of a penguin. Not sure what this means, but I’m sure it is important.

Anyway, here you have a picture of me. Ain’t I cute?

rman backup to tape when you don’t have a tape

Something I already knew for a couple of years, but recently rediscovered it: rman has the possibility to mimic a backup to tape.
You can do this by specifying the oracle test disk api when allocating the channel:

[oracle@ragna oracle]$ cd /opt/oracle
[oracle@ragna oracle]$ mkdir tape_backup
[oracle@ragna oracle]$ export ORACLE_SID=LOKI
[oracle@ragna oracle]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Feb 4 21:04:33 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: LOKI (DBID=2591136617)
using target database control file instead of recovery catalog

RMAN> run {
 allocate channel ch00 device type ‘SBT_TAPE’
    PARMS=”SBT_LIBRARY=oracle.disksbt,
    ENV=(BACKUP_DIR=/opt/oracle/tape_backup)”;
 backup datafile 1 format=’%U’;
}
2> 3> 4> 5> 6>
allocated channel: ch00
channel ch00: sid=140 devtype=SBT_TAPE
channel ch00: WARNING: Oracle Test Disk API

Starting backup at 04-FEB-08
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/LOKI/system01.dbf
channel ch00: starting piece 1 at 04-FEB-08
channel ch00: finished piece 1 at 04-FEB-08
piece handle=1dj7t4rb_1_1 tag=TAG20080204T210443 comment=API Version 2.0,MMS Version 8.1.3.0
channel ch00: backup set complete, elapsed time: 00:00:25
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ch00: starting piece 1 at 04-FEB-08
channel ch00: finished piece 1 at 04-FEB-08
piece handle=1ej7t4s4_1_1 tag=TAG20080204T210443 comment=API Version 2.0,MMS Version 8.1.3.0
channel ch00: backup set complete, elapsed time: 00:00:06
Finished backup at 04-FEB-08
released channel: ch00

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
36      Full    346.25M    SBT_TAPE    00:00:23     04-FEB-08
        BP Key: 36   Status: AVAILABLE  Compressed: NO  Tag: TAG20080204T210443
        Handle: 1dj7t4rb_1_1   Media:
  List of Datafiles in backup set 36
  File LV Type Ckp SCN    Ckp Time  Name
  —- — —- ———- ——— —-
  1       Full 2854789    04-FEB-08 /opt/oracle/oradata/LOKI/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
37      Full    8.75M      SBT_TAPE    00:00:04     04-FEB-08
        BP Key: 37   Status: AVAILABLE  Compressed: NO  Tag: TAG20080204T210443
        Handle: 1ej7t4s4_1_1   Media:
  Control File Included: Ckp SCN: 2854804      Ckp time: 04-FEB-08
  SPFILE Included: Modification time: 04-FEB-08

RMAN> exit

Recovery Manager complete.
[oracle@ragna oracle]$ ls -lhd tape_backup/
drwxr-xr-x  2 oracle oinstall 4.0K Feb  4 21:05 tape_backup/
[oracle@ragna oracle]$ ls -lh tape_backup/
total 356M
-rw-rw-rw-  1 oracle oinstall 347M Feb  4 21:05 1dj7t4rb_1_1
-rw-rw-rw-  1 oracle oinstall 8.8M Feb  4 21:05 1ej7t4s4_1_1
-rw-r–r–  1 oracle oinstall 2.0K Feb  4 21:05 Oracle_Disk_SBT_Catalog

Neat isn’t it?
But what can you use it for, I hear you say?
Well, euh…..  Testing I suppose or maybe for learning purposes. I’m sure someone might know a good use for it.

oracle unbreakable linux with Wim Coekaerts

Today we had an Oracle partner meeting with Wim Coekaerts.
For those who don’t know who Wim Coekaerts is, he is vice president of Linux Engineering for Oracle (and originating from Belgium, but living in the US for more then 10 years now) and is also known as Oracle’s Mr. Linux.
Needless to say that when I received an inventation to attend a partner meeting with him, I was quickly to confirm my presence.

The presentation that Wim gave was divided into 2 parts: Oracle unbreakable linux and Oracle VM. Both parts where very interesting and at the end we had more then enough time to ask questions.

Some key points that I have written down:

Enterprise linux:

• Oracle did not launched oracle enterprise linux to bully Redhat or to push Redhat out of the market.
  They came with their own linux support because they felt that oracle customers where not helped sufficiently by Redhat support. As oracle software can be freely downloaded for testing and this is not possible with Redhat linux, oracle came with an own rebuild.

• Oracle Enterprise linux is not a separate fork and never will be. It is, and stays completely compatible with Redhat linux.
  In fact, when oracle tests its software, they don’t differentiate between Oracle Enterprise linux and Redhat linux as os platform.

• Oracle did not include an option to the installer to provide it with “preset” options suited to host an oracle database (needed rpm’s, kernel parameters, …), because they did not want to create the appearance that they are creating a fork.
  Instead oracle created the “oracle validated configuration” rpm. When installing this rpm, it will also install all rpm’s necessary, creates the oracle user and sets the kernel parameters and user os limites.

• Linux (32bit) is the reference platform for all development.
  It is also the platform for all internal servers.

Oracle VM:

• Wim claimed (and a paper about this would be appearing soon) that an oracle database running in oracle VM will perform at about 90% compared to running it on a real server. With VMware this would only be 70%.

• You no longer have to license your database for all physical processors on the oracle VM server, but only the number of cpu’s defined in the guest.
  According to Wim there should be a document about this on the oracle site, but the document I found stated that this was only true when using hard partitioning with Oracle VM (http://www.oracle.com/corporate/pricing/partitioning.pdf).
  I will check this further.

• The license policy for oracle on vmware was not going to change.

• Grid control 11g will have buildin functionality to manage Oracle VM servers (deploying guests, performing life migrations, …), but the VM-Manager will not disappear.

• The oracle supplied guest images will be certified for production use somewhere during the second half of this year.
  This would mean that you could download a database image from the oracle site and use it as a production database.

That was about it.
After the presentation I felt more assured that OEL would be here to stay and that the compatibility between Redhat linux and OEL would not disappear in the future.
Not to sure about Oracle VM though, I’m still a little bit anxious of running production databases in a virtualized environment (according to my shrink I have a problem with losing control).

Askimet spam filter

I have noticed that the spam counter has been going up without new spam messages being listed in the spam filter.  So it looks like the askimet spam filter is marking some posts as spam without giving me the possibility to moderate them.

If you notice that a posted comment is not published on the blog, then please send an email to the address listed in the “about” section.

Teaching

Part of my job is to occasionally give oracle trainings to customers.
Although this has never been my favorite part of my job, I find it most times an interesting experience. It forces you to realy take a good look into the material you are teaching and by explaining something to others, you ofter gain a better insight yourself.

The downsize of these kind of trainings is that you don’t have much time to actually learn the students something. I mean, you can guide them through the course material and learn them the facts, but that still is something different then giving a real understanding of things. You know, the level of understanding that you have reached when you are able to reason why something works a certain way.

A good example of this is the question (I’m currently giving a 9i backup and recovery course) : “do I have to backup my undo tablespace?”
The simple answer is that yes, you should backup your undo tablespace, but if you understand how oracle processes data changes and how oracle performs recovery (with the roll-forward and roll-back phases) then you just know that the undo tablespace has to be backed-up.

However, as I said, it takes time to gain understanding and I am afraid that a 4 days of training are only enough to scratch the surface. Therefore I find that these training courses are only suited as a kind of starting point and that you have to learn the details afterwards on yourself.
One way to do this is by organizing regular internal workshops or hand-on trainings, in which each time a fellow dba explains one specific part of oracle. Another option can be to hire somebody for a couple of days per week to coach the internal dba team.

I want to end this blog entry with a couple of questions: How did you learn oracle? Are there regular internal workshops at your company? What are your feelings about oracle courses? Do you find them usefull or not?

8 trivial things about me

Looks like, thanks to Chen Shapira, the tagging storm has hit me as well:

1. I was born on December 27, 1978 in Lokeren, Belgium and have 1 younger brother. He has also studied computer science and is currenlty working as a sysadmin.
   As you can imagine we had some animated discussions at the dinner table that where hard to follow by our parents.

1. My father is a lifeguard in a swimming pool ( I could swim before I could walk ) and my mother is a laboratory technician (yes, we had petri dishes stored in our fridge).

1. I got maried in 2003, and we are expecting our first child in about 6 weeks.

1. Every August I spend 10 days working as a barkeeper at a music festival ( lokerse feesten ).

1. I have the bad habbit of always arriving late. So if the baby looks like me, he/she will be born in about 8 weeks :)

1. I drive a Toyota Prius, but I would really need to use my bike more often.

1. My favorit beer is Hoegaarden (an unfiltered white beer) and I also like to drink whisky (single malt, that is).

1. I like reading, mostly fiction (sci-fi, thrillers, detectives, that kind of stuff). When I’m reading I tend to forget about time and other duties and keep on reading until late at night, which explains a bit the late arrivings.

My time to tag.
Hmm, I tag Geert De Paep, Arul Ramachandran and Howard Rogers (call it a flaw in my character)