Laurent Schneider

This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual…

There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit].

1) AUDIT_FILE_DEST is not writable

$ env
_=/usr/bin/env
ORACLE_SID=FOO
TERM=dtterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3
PWD=/u01/app/oracle/product/10.2.0/db_3
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -      
root's Password:
# mkdir /bar 
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.

2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable

$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump  
drwxr-xr-x   2 root   dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:29 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump 
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:48 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> quit

3) $ORACLE_HOME/rdbms/audit is not writable

$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:48:09 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit 
drwxr-x---   2 root     dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:49:12 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> quit

I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries

I did not know that. Do I need to use hints?

set autotrace traceonly explain
create table t as 
select 
  sign(rownum-1) r, rpad(rownum,4000,'.') t 
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

select /*+ INDEX(T,I) */ r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787

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

| Id  | Operation                   | Name | Rows  | Bytes | C
ost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |      |     1 |  4004 |
1979   (2)| 00:00:24 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |  4004 |
1979   (2)| 00:00:24 |

|*  2 |   INDEX FULL SCAN           | I    |     1 |       |
1979   (2)| 00:00:24 |

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

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

   1 - filter("R"<>1)

SQL> select r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)
| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |  4004 |   271K  (1)
| 00:54:18 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   271K  (1)
| 00:54:18 |

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

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

   1 - filter("R"<>1)

Online Patching : you can apply or roll back online patches while the RDBMS instance is running

1) download an interim patch for 11g, f.ex. dummy patch 6198642
2) unzip p6198642_111060_LINUX.zip
3) cd 6198642
4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql

Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/oracle/product/11/db_4
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.0
OUI version       : 11.1.0.6.0
OUI location      : /opt/oracle/product/11/db_4/oui
Log file location : /opt/oracle/product/11/db_4/cfgtoollogs/
    opatch/opatch2007-12-05_21-23-42PM.log

RollbackSession rolling back interim patch '6198642' from OH
    '/opt/oracle/product/11/db_4'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory 
    and will patch the local system only.

Please shutdown Oracle instances running out of this 
    ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/11/db_4')

Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files affected by the patch '6198642' for restore. 
    This might take a while...
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
    6198642_May_07_2007_00_50_36/original_patch/custom/
    scripts/pre -rollback 6198642 ':

Return Code = 0

Patching component oracle.rdbms, 11.1.0.6.0...
Copying file to "/opt/oracle/product/11/db_4/cpu/CPUDummy2007/
    catcpu.sql"
RollbackSession removing interim patch '6198642' from inventory

---------------------------------------------------------------
This is a dummy patch for testing only
---------------------------------------------------------------
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
    6198642_May_07_2007_00_50_36/original_patch/custom/scripts/
    post -rollback 6198642 ':

Return Code = 0

Running the "apply" sql script "/opt/oracle/product/11/db_4/cpu/
    CPUDummy2007/catcpu.sql" with reference to 'patchmd.xml' file 
    for the patch "6198642"...

The local system has been patched and can be restarted.

OPatch succeeded.

OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable

If I need to generate 3 rows called AAA, BBB, CCC, I could use dual and union all. Another method is to use Extensibility Types

select * from table
   (sys.ODCIVarchar2List('AAA','BBB','CCC'));

COLUMN_VALUE
------------
AAA
BBB
CCC

« Ask Tom Live » European Tour 2008

City	Date	Price	Register
Madrid 28 - 29 January € 924 ** Click here London 25 - 26 February £1,101 * Click here Berlin 6 - 7 May € 1,474 ** Click here * This price is valid until 31 January 2008 only ** This price is valid until 29 February 2008 only

Note that if you register after Feb 29th for the Madrid event (which is in January), the price is no longer valid

I am also surprised to see such a price difference for the same course between the different events.

One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel.

SQL> create bigfile tablespace big datafile size 1g;

Tablespace created.

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE 
  TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE 
  TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace big section size 100m;

Starting backup at 30.11.2007 07:23:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=42 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=41 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 30.11.2007 07:23:37
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_2: starting piece 2 at 30.11.2007 07:23:41
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_3: starting piece 3 at 30.11.2007 07:23:45
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_4: starting piece 4 at 30.11.2007 07:23:48
channel ORA_DISK_1: finished piece 1 at 30.11.2007 07:23:49
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_1_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 30.11.2007 07:23:53
channel ORA_DISK_2: finished piece 2 at 30.11.2007 07:23:53
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_2_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_2: starting piece 6 at 30.11.2007 07:23:57
channel ORA_DISK_3: finished piece 3 at 30.11.2007 07:23:57
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_3_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 76801 through 89600
channel ORA_DISK_3: starting piece 7 at 30.11.2007 07:24:01
channel ORA_DISK_1: finished piece 5 at 30.11.2007 07:24:01
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_5_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 89601 through 102400
channel ORA_DISK_1: starting piece 8 at 30.11.2007 07:24:05
channel ORA_DISK_2: finished piece 6 at 30.11.2007 07:24:05
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_6_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 102401 through 115200
channel ORA_DISK_2: starting piece 9 at 30.11.2007 07:24:09
channel ORA_DISK_4: finished piece 4 at 30.11.2007 07:24:09
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_4_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:21
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 115201 through 128000
channel ORA_DISK_4: starting piece 10 at 30.11.2007 07:24:13
channel ORA_DISK_1: finished piece 8 at 30.11.2007 07:24:13
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_8_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 128001 through 131072
channel ORA_DISK_1: starting piece 11 at 30.11.2007 07:24:18
channel ORA_DISK_2: finished piece 9 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_9_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_3: finished piece 7 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_7_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_4: finished piece 10 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_10_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: finished piece 11 at 30.11.2007 07:24:19
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_11_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30.11.2007 07:24:19

This works fine

SECTION SIZE requires 11gR1 or later

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage.

One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time.

Unfortunately, the feature has not been implemented in 11g Release 1.

As you can see, the CREATE EDITION command does not work :

SQL> create edition v1;
create edition v1
       *
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> alter session set edition=v1;
ERROR:
ORA-38802: edition does not exist

The error messages ORA-38801 to ORA-38816 refer to EDITIONS.

SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ------
ORA$BASE                         YES

SQL> alter session set edition=ora$base;

Session altered.

Apparently the feature is implemented, but not ready to be used. Be patient …

I blogged here that 10.2.0.4 was announced for 2007Q4. Well, I have rechecked metalink and the tentative date for Linux is now 2008Q1

Lutz posted about some 10.2.0.4 Beta tests, I did not even know that some customers got beta version of the metalink patchsets

I read the ultimate excuse database and checked if I can use model to do generate random statements:

with t as (
  select '%E and %E are travelling to %L.' fmt 
  from dual union all
  select 'About %N employees live in %L.' 
  from dual)
select str from t
model
reference
  dept on (
    select loc,count(*) over () c,rownum r 
    from dept)
  dimension by (r) measures (loc,c)
reference
  emp on (
    select ename,count(*) over () c, rownum r 
    from emp)
  dimension by (r) measures (ename, c) 
partition by (fmt)
dimension by (1 x) 
measures (cast(fmt as varchar2(4000)) str)
rules 
  iterate (1000) 
  until str[1] not like '%/%%' escape '/'
(str[1]=substr(str[1],1,
  decode(instr(str[1],'%'),0,4000,
    instr(str[1],'%')-1))||
  decode(substr(str[1],nullif(
    instr(str[1],'%'),0)+1,1),
    'N',to_char(trunc(
      dbms_random.value(0,1+emp.c[1]))),
    'E',initcap(emp.ename[trunc(
      dbms_random.value(1,1+emp.c[1]))]),
    'L',initcap(dept.loc[trunc(
      dbms_random.value(1,1+dept.c[1]))]))||
    substr(str[1],nullif(
      instr(str[1],'%'),0)+2))
/
STR
----------------------------------------
James and Ward are travelling to Dallas.
About 1 employees live in Chicago.
/
STR
--------------------------------------------
King and Blake are travelling to New York.
About 3 employees live in Dallas.

Is rownum=1 the first row returned? well, it depends :

select rownum, ename 
from emp 
order by ename;
    ROWNUM ENAME
---------- ----------
        11 ADAMS
         2 ALLEN
         6 BLAKE
         7 CLARK
        13 FORD
        12 JAMES
         4 JONES
         9 KING
         5 MARTIN
        14 MILLER
         8 SCOTT
         1 SMITH
        10 TURNER
         3 WARD
</pre>

The ROWNUM is evaluated before the order by

<code>select rownum 
from emp 
group by rownum 
having rownum between 3 and 6;
    ROWNUM
----------
         6
         5
         3
         4

the rownum is selected before the GROUP BY and before the HAVING.

select 
   rownum, 
   sal, 
   median(sal) over () 
from emp;
    ROWNUM        SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
         1        800              1550
        12        950              1550
        11       1100              1550
         3       1250              1550
         5       1250              1550
        14       1300              1550
        10       1500              1550
         2       1600              1550
         7       2450              1550
         6       2850              1550
         4       2975              1550
         8       3000              1550
        13       3000              1550
         9       5000              1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR

1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)

Let’s try

with t as (
  select 10 n from dual union all 
  select 12 from dual union all 
  select 6 from dual) 
select 
  utl_raw.cast_to_binary_integer(
    sys.mvaggrawbitor(
      utl_raw.cast_from_binary_integer(
        n
      )
    )
  ) N 
from t;
  N
---
 14

It is that easy !

disclaimer: mvaggrawbitor is not documented

I have enjoyed speaking at the unconference very much. I chose not to use anything else than a SQL file to demonstrate the capabilities.

Thanks to the audience for coming. Here is the SQL file I used for the demo

SQLMODEL.SQL

SQL Model - Oracle Wiki

My unconference is Thursday at 2pm

I just arrived at San Francisco and meet my colleague Daniel and Lutz Hartmann. Thanks Dani for driving us to SF and thanks Lutz for the Indian food in the restaurant where we ended last year during Blogger 2006 Meetup . By the way do not miss 2007 Meetup organized by Mark Rittman : oow 2007 blogger meetup

I took the cable car from Lutz hotel to the Fishermann’s Wharf. Here it is raining, but well, in Zurich it was snowing and the wind was so cold that they needed to unfreeze the aisles of the plane before take off!

In less than 8 hours the 11g exam cram is starting at Moscone South 104 ! My notebook clock is still set to Sun Nov 11, 10:10 am, I will try to sleep until 7am…

I exported to CSV, formated and printed my schedule

I will have to add the Unconferences too.

Saturday I have a long long day to flight to San Francisco

On Sunday you can assist to the 11g exam cram from 9am to 5pm. It may be too late to register, I tried anyway :
11g db Exam Cram

Then you may want to get your OCP credentials for a discount price.
on-site testing

The Oracle Database 11g New Features for Administrators exam will be available in beta version during the conference. Beta exams are not available at Oracle OpenWorld 2007.

If I read this correctly, it means 11g New Features is available during the conference but not at the conference

Dear Friends,

Planning to appear for Oracle DB 10g: Administration I exam, Would be very grateful if you send across braindumps/questions/tips related to that.

Pl mail to this id : ***@yahoo.com

Many Thanks,
***

---

When I appeared to my first multiple choice question exam by Prometrics back in 1997, there were no brain dumps, no free question except the 10 sample questions on the vendor site, and no book. I missed Sun JDK 1.0.2 exam because of the many questions related to java.media.Player and other classes totally unknown to me.

In 2000, I did OCP 8.0 and 8i. That was 5 exams. One of the exam was called Oracle 8.0 Network. I had and I still have never used connection pooling, Oracle Names, Oracle Connection Manager.
So I bought a book to prepare for the certification :

Oracle8 DBA Network Administration Exam Cram

Guess what, I mastered that exam. Those books are fairly useless, you do not really learn something, but you learn questions by heart and passing an exam on a topic you do not know is dramatically easy.

About brain dumps, I recommend you the following lecture

CertGuard’s definition of a Braindump

On the Internet, there are hundreds of vendors that sell exam questions. Oracle itself has a kind of partnership with SelfTestSoftware. SelfTestSoftware is an official vendor of test simulators. Well, why paying 99$ for a test? A new trend is to post on the official Oracle Technical Network Certification Forum your email address and beg for free questions. I have alerted ocpfraud_ww@oracle.com about this behaviour.

Is OCM better? Well, at least you must know how to use your fingers to type quickly. I received not less than 50 emails from people asking for the content of the exam.

I passed the OCM in early 2004. Later in 2004, former Oracle Certification Principal Jim DiIanni revealed the content of the Practicum in Oracle Magazine

Recently I did the Oracle Certification Experts beta exams for RAC and SQL. Those exams are also multiple choice tests and could also be a market for illegal certification sites.

If you want to pass the 11g New Features exam, you could also attend to a new feature course.
In OpenWorld next week there are also some mini-lessons from Oracle University like :
Oracle Database 11g: Can I Do That? Introducing 11g SQL and PL/SQL Enhancements
Oracle Ace Lutz Hartmann is giving 11g courses in Switzerland : http://sysdba.ch/aktuell.php

I am in the processing in adding logfiles to a 10gR2 database.

SQL> alter database add logfile group 10 size 1e7;

Database altered.
$ ls -l
-rw-r-----   1 oracle     10000896 Nov  1 15:00
    o1_mf_10_3lmq05ld_.log

The file size is 10,000,896 bytes.

What about this :

SQL> alter database drop logfile group 10;

Database altered.
SQL> alter database add logfile size 1e;
alter database add logfile size 1e
*
ERROR at line 1:
ORA-00741: logfile size of (2251799813685248) blocks 
exceeds maximum logfile size

No way! Oracle does not want me to add a logfile of 1 Exabyte !

Remember the logfile blocks are OS blocks of 512 bytes. Not Oracle blocks.

Ok, let’s try something else

SQL> alter database add logfile size 1p;
alter database add logfile size 1p
*
ERROR at line 1:
ORA-00741: logfile size of (2199023255552) blocks 
exceeds maximum logfile size

No, one Petabyte is not a realistic size for a log file.

I have one more try, but unfortunately it works

SQL> alter database add logfile size 1t;

...

It just takes ages…

$ ls -l
-rw-r-----   1 oracle   dba      1099511628288 Nov  1 14:49
    o1_mf_5_3lmpb6w6_.log
$ du -g o1_mf_5_3lmpb6w6_.log
6.09    o1_mf_5_3lmpb6w6_.log
$ df -gt .
Filesystem    GB blocks      Used      Free %Used Mounted on
/dev/u02_lv      140.00     19.32    120.68   14% /u02

The ls shows the file size has been set to 1T and 6 Gigabytes have been allocated yet. Since I do not want to fill my filesystem, I just shutdown-abort my instance and remove that file…

I posted about Unexpected results in June 2005. Here are more results from NOT IN and NULL

select * from dual
WHERE (1) NOT IN (SELECT NULL FROM DUAL);
no rows selected

select * from dual
WHERE (1,1) NOT IN (SELECT NULL,1 FROM DUAL);
no rows selected

However, and this surprised me,

select * from dual
WHERE (1,1) NOT IN (SELECT NULL,2 FROM DUAL);

D
-
X

Probably the expression
where (a,b) not in (select c,d from t)
is translated into
where (a!=c or b!=d) — first row of t
and (a!=c or b!=d) — second row of t
– and …

What is doing the MIN(DISTINCT X) call? Basically, every distinct value of X is passed to the MIN function. Well, it is probably of very little interest as the MIN function is very fast and processing less rows than MIN(X) should not boost the performance because of the overhead of sorting distinct values.

However, if you write your own aggregate, distinct may be interesting!

create type myudag_type as object
(
  myudag INTEGER,
  static function ODCIAggregateInitialize(
    sctx IN OUT myudag_type)
    return number,
  member function ODCIAggregateIterate(
    self IN OUT myudag_type,
    value IN INTEGER)
    return number,
  member function ODCIAggregateTerminate(
    self IN myudag_type,
    returnValue OUT INTEGER,
    flags IN number)
    return number,
  member function ODCIAggregateMerge(
    self IN OUT myudag_type,
    ctx2 IN myudag_type)
    return number
);
/

create or replace type body myudag_type is
static function ODCIAggregateInitialize(
  sctx IN OUT myudag_type)
  return number is
begin
  sctx := myudag_type(0);
  return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
  self IN OUT myudag_type,
  value IN INTEGER)
  return number is
begin
  -- doing nothing will cost you a lot !!!
  for i in 1..1000000 loop null; end loop; 
  return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
  self IN myudag_type,
  returnValue OUT INTEGER,
  flags IN number) return number is
begin
  returnValue := self.myudag;
  return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
  self IN OUT myudag_type,
  ctx2 IN myudag_type)
  return number is
begin
  return ODCIConst.Success;
end;
end;
/

CREATE FUNCTION myudag (
  input INTEGER)
  RETURN INTEGER
  AGGREGATE USING myudag_type;
/

SQL> select myudag(deptno) from emp;

MYUDAG(DEPTNO)
--------------
             0

Elapsed: 00:00:00.57
SQL> select myudag(distinct deptno) from emp;

MYUDAG(DISTINCTDEPTNO)
----------------------
                     0

Elapsed: 00:00:00.13

10.2.0.4 should be available this year on Linux x86. Check Metalink Certification for different plateforms.

Certify - Additional Info Oracle Database - Enterprise Edition Version 10gR2 On Linux (x86)

---

Operating System: Linux (x86) Version SLES-10
Oracle Database - Enterprise Edition Version 10gR2
N/A Version N/A
Status: Certified
Product Version Note:
None available for this product.
Certification Note:

Existing patch sets:
     10.2.0.2
     10.2.0.3 
     10.2.0.4 Q4CY2007

Yesterday I installed ContentDB. The installation is fairly straightforward.

Download and install Oracle Identity Management

• OID 10.1.4.0.1
• Install the Infrastructure, with the Identity Management and Repository, including Internet Directory, Single Sign-on, Delegated Administration Services and Directory provisioning

This will create a 10.1.0.5 database. The default parameters are too low for contentDB.

alter system set
  processes=250
    scope=spfile
  sga_max_size=629145600
    scope=spfile
  shared_pool_size=184549376
    scope=spfile
  java_pool_size=125829120
    scope=spfile
  db_cache_size=150994944
    scope=spfile
  db_file_multiblock_read_count=32
    scope=spfile
  db_create_file_dest='/u02/oradata'
    scope=spfile
  job_queue_processes=10
    scope=spfile
  session_max_open_files=50
    scope=spfile
  open_cursors=400
    scope=spfile
  star_transformation_enabled=true
    scope=spfile
  pga_aggregate_target=203423744
    scope=spfile;

It is recommended to stop the application server infrastructure before restarting the database to make the parameters above effective.

$ $ORACLE_HOME/bin/emctl stop iasconsole
$ $ORACLE_HOME/opmn/bin/opmnctl stopall
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup
$ $ORACLE_HOME/opmn/bin/opmnctl startall
$ $ORACLE_HOME/bin/emctl start iasconsole

It is now possible to install ContentDB in this database. Of course the ContentDB could be installed in a separate database, it does not have to be the same as the infrastructure database.

Now, download and install Oracle ContentDB

• Content Database 10.2.0.0.0
• Install the ContentDB

That’s all. There now two application server instances, one for the infrastructure and one for the content database.

To launch the Content Database web interface, just go to the http server of the ContentDB installation, something like http://server:7779.

$ /app/oracle/product/10.1.2/cdb_1/bin/opmnctl status -l

Processes in Instance: CONTENTDB01.srp20604ax
-------------------+----------+------------------------
ias-component      | status   | ports
-------------------+----------+------------------------
DSA                | Down     | N/A
HTTP_Server        | Alive    | http1:7779,http2:7202
LogLoader          | Down     | N/A
dcm-daemon         | Alive    | N/A
OC4J               | Alive    | ajp:12503,rmi:12403,...
WebCache           | Alive    | http:7778,invalidati...
WebCache           | Alive    | administration:9400
Content            | Alive    | node_dms_http:53900,...
Content            | Alive    | node_manager_locator...

Login with user ORCLADMIN and the password you specified for IAS_ADMIN.

ContentDB interface let you upload and download files. You can use it to keep your documentation in a single location. It has versioning capabilities too.

I just read on Renaps Blog that Oracle 11g for Windows is out.

Oracle Database Download

Oracle OpenWorld will host the

On the wiki.oracle.com Wiki, you can create your own unconference session.

I have proposed a session about SQL Model :
SQL Model

Niall Litchfield just wrote about 11g availability on 64bits linux.

Happy Birthday Niall
Download Oracle Database

Oracle Support started a series about database internals.

DATABASE INTERNALS NEWSLETTER

Volume 1: October, 2007 Introduction to the Internals Newsletter Support Tips on Block Corruption What Constitutes a Severity 1 Issue?

Note 460494.1

Check the Oracle’s Certification Matrices for projected dates for 11g.

For HP/UX, AIX, Solaris Sparc, Linux x86/x86-64 and Windows XP/Vista 32/64, the release is planned for 2007Q4, which means definitely not yesterday and hopefully this year

I just discussed with one client who wanted to deploy an 11g client on Windows as soon as available because of the GERMAN_SWITERLAND bug which prevents from installing 10gR2 clients.

Nan

select
  BINARY_DOUBLE_INFINITY INF, 
  BINARY_DOUBLE_NAN NAN,
  greatest(BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_NAN) GRE
from t;

INF NAN GRE
--- --- ---
Inf Nan Nan

Nan means not a number. It could be square root of -1, log of -1, 0/0, acos(1000), Inf-Inf, etc…

select
  SQRT(-1d),
  LN(-1d),
  0/0d,
  acos(1000d),
  BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
from t;
SQR LN- 00D ACO BIN
--- --- --- --- ---
Nan Nan Nan Nan Nan

According to the doc, it is greater than any value, inclusive positive infinity.

To check if a value is nan, it could be compared to BINARY_DOUBLE_NAN.
where :z = BINARY_DOUBLE_NAN
There is a function NANVL(:z, :y) which evaluates to :y when :z is equal Nan. if :z is not equal to Nan and :y is not null, then it evaluates to :z. NANVL evaluates to NULL when :z or :y is null.

select NANVL(1,null) from dual;
NANVL
------
[null]

I have a table with a blob

create table t(b blob);
insert into t values ('585858');

In 11g sql*plus, I can display raw data

select b from t;
B
------
585858

Ok, but if I want to display XXX (the character content)

select utl_raw.cast_to_varchar2(b) from t;
UTL
--- 
XXX

However, in sql, a raw cannot be more than 2000 bytes long.

Another way to print your blob content is to use DBMS_LOB.CONVERTTOCLOB

var c clob
set autoprint on
declare
  b blob;
  dest_offset integer := 1 ;
  src_offset  integer := 1 ;
  lang_context integer:= 1 ;
  warning integer;
begin
  select b into b from t for update;
  dbms_lob.createtemporary(:c,true);
  dbms_lob.converttoclob(
    :c, b, DBMS_LOB.LOBMAXSIZE,
    dest_offset, src_offset,
    1, lang_context, warning);
end;
/
C
---
XXX

Addition is supposed to be associative.
a+(b+c)=(a+b)+c

This may be wrong in Oracle when dealing with months and days

with t as (select
 interval '1' month a,
 date '2007-09-26' b,
 interval '7' day c
from dual)
select a+(b+c),(a+b)+c 
from t;

A+(B+C)     (A+B)+C
----------- -----------
03-NOV-2007 02-NOV-2007

The equality is supposed to be transitive
if (a=b and b=c) then (a=c)
However, in Oracle the comparison operator equal may imply some transformation

with t as (select '.0' a, 0 b, '0.' c from dual) 
select 
  case when a=b then 'YES' else 'NO ' end "A=B",
  case when b=c then 'YES' else 'NO ' end "B=C",
  case when a=c then 'YES' else 'NO ' end "A=C"
from t;
A=B B=C A=C
--- --- ---
YES YES NO

The equality operator is also supposed to be reflexive
a=a

This is unfortunately wrong with null

with t as (select null a from dual) 
select case when a=a then 'YES' else 'NO ' end "A=A" 
from t;
A=A
---
NO

I have start building my schedule. There are about 1722 sessions to chose from this year, so choice is difficult!

Due to jet-lag, I mostly cannot do all what I planed. And I also like to spent some time by the exhibitors and the boosts.

Ok, the one I will not miss :
Steven Feuerstein : Break Your Addiction to SQL!
Amit Ganesh : Oracle Database 11g: The Next-Generation Data Management Platform
Bryn Llewellyn : Doing SQL from PL/SQL: Best and Worst Practices
Thomas Kyte : The Top 10–No, 11–New Features of Oracle Database 11g
Lucas Jellema : The Great Oracle SQL Quiz

My schedule (draft) : schedule

I have been to a SOUG last Thursday.

We first had a presentation from Thomas Koch about performance in Zurich Kantonalbank. As I have been working as a DBA for about two years in that bank, I already had my opinion about performance there

The second presentation was about TimesTen. I must say I have never used Times Ten. So I was glad to hear Stefan Kolmar from Oracle presenting the product. Ok, here it is in a few lines.

In TimesTen, the whole database is in the memory. TimesTen is an Oracle Product and a Database, but it is not an Oracle Database. The objective must be to have a response time in microseconds and hundred of thousands of transactions per second. You have a log buffer, and you can decide to asynchronously dump the buffer to file.

Let me try to explain the example from Stefan :
You have a mobile phone company. Foreign call can be fairly expensive, so those transactions will be synchronously dumped to the disk. Local calls cost about 1 Euro in average. So if you dump the log to disk every ten transactions, in case of a failure an average of 5 Euros will not be billed. In this way you can select the transaction to have synchronous and the one to have asynchronous. It looks promising, but probably not for critical businesses like banking where you are required to guarantee zero data-loss.

There is an additional functionality in TimesTen which is called “cache for Oracle”. It is a layer between the client and the database. It does not offer the same functionality as Oracle. For example, you cannot do PL/SQL. But it may offer microsecond access.

I will document two examples :
1) read only
You have a flight reservation company. Flight reservation are very important, so they will be in the database (no data loss). Flight schedule are read-only for the client. They will be cached in TimesTen. So when accessing the timetables, it will be ultra-fast. When booking, it may take a few seconds.

2) on demand
You have a call center. When a customer phone, all data relative to the customer (history, name, contracts, contract details) are immediately loaded from the database in TimesTen. So when the Call Center employee asks for any info, they are immediately available

How much does it cost? Check on store.oracle.com

For a tiny database up to 2Gb it is 6000$/processor for 3 years. More options, more money…

Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. This privilege can be granted only by a user with DBA privileges. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside.

So as I was getting an ORA-55611, I opened a SR. The support engineer pointed me to the online documentation where I was astonished to read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside. To designate a flashback data archive as the system default flashback data archive, you must be logged in as SYSDBA.

Well, Read The Fine Online Manual !!!

The second tar is related to long retention (about the age of the earth)

SQL> alter flashback archive fba01 
  modify retention 4106694757 year;

Flashback archive altered.

SQL> select retention_in_days
  from DBA_FLASHBACK_ARCHIVE;
RETENTION_IN_DAYS
-----------------
                1

I tried this in 11g
TABLE T

X
123
-1.2e-3
abc

select x, 
  to_number(
    xmlquery('number($X)' 
      passing x as x 
      returning content)) n 
from t;
X                N
------- ----------
123            123
-1.2e-3     -.0012
abc

it is quite a common task to extract numbers from varchar2 and to dig out poor quality data.

select x, to_number(x) from t;
ERROR:
ORA-01722: invalid number

A well-known PL/SQL approach would be to use exception. Ex:

create or replace function f(x varchar2) 
return number is 
begin return to_number(x); 
exception when others then return null; 
end;
/
select x, f(x) n from t;
X                N
------- ----------
123            123
-1.2e-3     -.0012
abc

another approach in plain sql could involve CASE and REGEXP

select x,
case when
regexp_like(x,
‘^-?(\+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
then to_number(x)
end n
from t;
X N
——- ———-
123 123
-1.2e-3 -.0012
abc

Download oracle-oid-10.1.4.2.0-1.0.i386.rpm
Download oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Install the rpm
# rpm -i oracle-*.i386.rpm

In SLES 10, there is no /bin/cut, let’s create a link as root to avoid a mistake when running config-oid.sh
# ln -s /usr/bin/cut /bin/cut

Run the configure script as root
# /etc/init.d/oracle-oid configure
That’s all folks! It created an Oracle XE 10gR2 database, and configured a running database. Excellent!

LDAP Server is running and configured.

$ ldapsearch  cn=orcladmin dn
cn=orcladmin, cn=Users, dc=com

There is a nice video to run on linux : oracleauthenticationservices_demo.vvl
Save the file, set the display, then

$ chmod +x oracleauthenticationservices_demo.vvl
$ ./oracleauthenticationservices_demo.vvl

It shows also how to use Oracle LDAP Server OID to identify your Linux users with the preview of Oracle Authentication Service

I am always delighted to read the top features by Arup Nanda.

He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers

There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range partitioning. In 10g and before, a partitioning by dates required that the partition are defined before values are inserted.

Now we have automatic partition creation

create table t(d date) 
partition by range(d) 
interval(interval '1' month) 
(partition p1 values less than (date '0001-01-01'));

One partition must be created manually, here the partition will contain all dates from 1-JAN-4712BC to 31-DEC-0000 (which is not a legal date by the way)

There is also new syntax to query the partition

SQL> insert into t values (date '2000-01-10');

1 row created.

SQL> insert into t values (date '2000-01-20');

1 row created.

SQL> insert into t values (date '2000-03-30');

1 row created.

SQL> select * from t partition for (date '2000-01-01');
D
-------------------
10.01.2000 00:00:00
20.01.2000 00:00:00

Note the syntax can be used in any form of partitioning. Here in a list-list composite

SQL> create table t(x number, y number) 
  partition by list(x) 
  subpartition by list(y) 
    subpartition template (
      subpartition sp1 values(1),
      subpartition sp2 values(2)) 
  (partition values(1), partition values(2));

Table created.

SQL> insert into t values(1,2); 
1 row created.

SQL> select * from t subpartition for (1,2);
         X          Y
---------- ----------
         1          2

Ok, one more feature Arup introduced is the REF partitioning, where you have a schema with both the parent and child tables partitioned, and you want to partition on a column of the parent table that is not in the child table (as you had bitmap join indexes, you have now ref partitions). Check it on his site.

Finally Arup explained SYSTEM partitioning, which is not inconceivable, but will hardly be used.

Imagine you have a table containing just one single LOB column, and a LOB cannot be used as a partition key.

SQL> create table t(x clob)
  partition by system (
    partition p1, 
    partition p2, 
    partition p3, 
    partition p4);

Table created.

So far this seems fine. So what the problem? You cannot insert in that table!

SQL> insert into t values(1);
insert into t values(1)
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable
must be used for DMLs on tables partitioned by the
System method

so you must define in which partition you want to add data. For example round robin. Or random. Whatever.

SQL> insert into t partition (P1)  values ('x');

1 row created.

SQL> insert into t partition (P2)  values ('y');

1 row created.

If you want to use bind variable, you can use dataobj_to_partition

SQL> select object_id 
  from user_objects 
  where object_name='T' 
    and subobject_name is not null;
 OBJECT_ID
----------
     55852
     55853
     55854
     55855

SQL> var partition_id number
SQL> exec :partition_id := 55852

PL/SQL procedure successfully completed.

SQL> insert into t 
  partition (dataobj_to_partition("T",:partition_id))
  values ('x');

1 row created.
SQL> exec :partition_id := 55853

PL/SQL procedure successfully completed.

SQL> insert into t 
  partition (dataobj_to_partition("T",:partition_id))
  values ('x');

1 row created.

Actually, SYSTEM partitioning is misleading, YOU are responsible for choosing the partition in which you want to insert, not the system