Laurent Leturgez's blog

I moved to Wordpress

2011-02-10T12:11:00.001+01:00

I decided to move my blog to wordpress.
You can access it at this address : http://laurentleturgez.wordpress.com/

See you soon !

Control bug fixes activation in Oracle

2011-02-09T18:02:00.008+01:00

When you upgrade oracle database version, you can be faced to a problem of CBO that changes its behaviour.

To do this, Oracle have introduced an undocumented parameter : "_fix_control" (Since 10.2.0.2). With this parameter, you can unable or disable a bug fixe to see, for example, if the CBO behaviour is controlled by a bug fix or not.

Off course, this kind of tests have to be implemented for testing purposes only !

To see what bug fix is implemented on your database, oracle have implemented two views V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL.

In these views, you will find information about the bug number, a short description, and the value of the parameter optimizer_features_enabled in which the bug fix is activated.

SQL> select bugno,value,description,optimizer_feature_enable,is_default from v$system_fix_control;

BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE IS_DEFAULT

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

3834770 1 Lift restriction on unnest subquery with a view 8.0.0 1

3746511 1 do not combine predicates from LNNVL 8.0.0 1

4519016 1 Pick view card from view qb instead of parent qb 9.2.0 1

3118776 1 Check for obj# for named view estimated card 9.2.0.8 1

4488689 1 ignore IS NOT NULL predicate as an index filter 10.2.0.2 1

2194204 0 disable push predicate driven by func. index into partition view 1

2660592 1 do not trigger bitmap plans if no protential domain index driver 8.1.7 1

2320291 1 push into table with RLS 9.2.0 1

2324795 1 add(remove) cluster index for push view 8.1.7 1

4308414 1 outer query must have more than one table unless lateral view 10.1.0.5 1

3499674 0 enable tiny index improvements: consider small indexes as cachhe 1

4569940 1 Use index heuristic for join pred being pushed 10.1.0.5 1

4631959 1 Refine criteria for additional phase in JPPD 10.2.0.2 1

4519340 1 Generate distinct view in SU if candidate for JPPD 10.2.0.2 1

4550003 1 do not consider no sel predicates in join selectivity sanity 10.1.0 1

.../....

In this example, we can see that the bug fix #4488689 is activated in our database (because of the value parameter set to 1).

We can see too that some og the buf fixes are not activated by default (ex: 2194204)

You can have a session view by querying the V$SESSION_FIX_CONTROL.

Now, if you want to test to desactivate a bug fix, you can do it by using the _fix_control undocumented parameter.

To disable a bug fix, you have to specify the bug number completed by ON or OFF.

For example:

-- To deactivate a single bug fix:

SQL> alter session set "_fix_control"='4488689:OFF';

-- To deactivate many bug fixes:

SQL> alter session set "_fix_control"='4488689:OFF','4631959:OFF','4519340:OFF';

-- To activate it, you have to use ON instead of OFF

SQL> alter session set "_fix_control"='4488689:ON';

If we control the V$SESSION_FIX_CONTROL view on these specific bug fixes, we can view that they are desactivated (value column equals 0).

SQL> select SESSION_ID,BUGNO,VALUE,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT

2 from V$SESSION_FIX_CONTROL

3 where session_id=sys_context('USERENV','SID') and bugno in (4488689,4631959,4519340);

SESSION_ID BUGNO VALUE OPTIMIZER_FEATURE_ENABLE IS_DEFAULT

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

22 4488689 0 10.2.0.2 0

22 4631959 0 10.2.0.2 0

22 4519340 0 10.2.0.2 0

Disassembling V$ views

2010-09-08T17:25:00.014+02:00

If you are instesting by Oracle Internals, maybe you have already tried to use the DBMS_METADATA Package to get the definition code of an Oracle static view :

SQL> set long 5000
SQL> set pages 5000
SQL> select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES') from dual;

DBMS_METADATA.GET_DDL('VIEW','DBA_DATA_FILES')

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

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "F

ILE_ID", "TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO

", "AUTOEXTENSIBLE", "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BY

TES", "USER_BLOCKS", "ONLINE_STATUS") AS

select v.name, f.file#, ts.name,

ts.blocksize * f.blocks, f.blocks,

decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),

f.relfile#, decode(f.inc, 0, 'NO', 'YES'),

ts.blocksize * f.maxextend, f.maxextend, f.inc,

ts.blocksize * (f.blocks - 1), f.blocks - 1,

decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'S

YSTEM'),

decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECO

VER'))

from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe

where v.file# = f.file#

and f.spare1 is NULL

and f.ts# = ts.ts#

and fe.fenum = f.file#

union all

select

.../...

But if you try to get the definition code of the "V$" dynamic views, you will obtain a strange output which mentioned a call to V$DATAFILE (at the end)

SQL> select dbms_metadata.get_ddl('VIEW','V_$DATAFILE') from dual;

DBMS_METADATA.GET_DDL('VIEW','V_$DATAFILE')

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

CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION

_CHANGE#", "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHE

CKPOINT_CHANGE#", "CHECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOV

ERABLE_TIME", "LAST_CHANGE#", "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_

CHANGE#", "ONLINE_TIME", "BYTES", "BLOCKS", "CREATE_BYTES", "BLOCK_SIZ

E", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NAME", "FIRST_NONLOGGE

D_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CREATION_CHAN

GE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "

PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS

select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","ST

ATUS","ENABLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_

CHANGE#","UNRECOVERABLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHAN

GE#","ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS","CREATE_BYTES","B

LOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_OFFSET","AUX_NAME","FIRST_NONLO

GGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","FOREIGN_CREATION_CHAN

GE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHANGE#","PLUG

IN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile

In fact, dynamic performance view definition are stored in the V$FIXED_VIEW_DEFINITION.
As in 10g and 11g, the V$ views are results of GV$ view, if you want to get the definition view of the V$DATAFILE, a better way is to query this view with the GV$DATAFILE definition.

SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='V$DATAFILE'
4 /

VIEW_DEFINITION

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

select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# , STAT

US , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME, UNRECOVERABLE_CHA

NGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME , OFFLINE_CHANGE# ,

ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS , CREATE_BYTES , BLOCK_

SIZE , NAME, PLUGGED_IN, BLOCK1_OFFSET , AUX_NAME , FIRST_NONLOGGED_SC

N, FIRST_NONLOGGED_TIME, FOREIGN_DBID, FOREIGN_CREATION_CHANGE#, FOREI

GN_CREATION_TIME, PLUGGED_READONLY, PLUGIN_CHANGE#, PLUGIN_RESETLOGS_C

HANGE#, PLUGIN_RESETLOGS_TIME from GV$DATAFILE where inst_id = USERENV

('Instance')

SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='GV$DATAFILE'
4 /

VIEW_DEFINITION

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

select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_t

im,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fetsn,fe.ferfn,

decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), d

ecode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')), decode(f

e.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED'

,4,'READ ONLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_da

te(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number

(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Grego

rian'), to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL), to_d

ate(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_numb

er(fe.feofs),to_number(fe.feonc_scn), to_date(fe.feonc_tim,'MM/DD/RR H

H24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fe

csz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb, fn.fnbof, decode(fe.fepax,

0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam), to_number(fh.fhfirstunr

ecscn), to_date(fh.fhfirstunrectime,'

MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fepdi, fe.fefcrs, f

e.fefcrt, decode(fe.fefdb, 1, 'YES', 'NO'), fe.feplus, fe.feprls, fe.

feprlt from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where

((fe.fe

pax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or

((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno

and fnaux.fntyp=4 and fnaux.fnnam is not null and

bitand(fnaux.fnflg, 4) != 4

and fe.fefnh=fnaux.fnnum))

and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil an

d fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.f

nnam is not null and bitand(fn.fnflg, 4) != 4 order by fe.fenum

Now you know that GV$DATAFILE view is using x$kccfe, x$kccfn, x$kcvfh fixed tables.

Convert a virtualBox disk to plug it into Vmware

2010-04-26T23:15:00.004+02:00

Oracle VirtualBox is a powerful virtualization software used on workstation.
The software is fitted with a set of powerful tools that can be used to migrate your disks to VMWare.

If you want to migrate a hard disk initially built with Oracle Virtual Box (VDI) to a VM Ware hard disk. There's a very easy and it can be done in only two steps:

- first step, you convert the vdi to a raw disk. To do it, you will use "vboxmanage" command from virtualbox

G:\WM_disks> "c:\Program Files\Sun\VirtualBox\vboxmanage" internalcommands converttoraw linux_1_d1.vdi linux_1_d1.raw

- and last step :) you convert the raw disk to a VMDK format disk. In this step, you can still use "vboxmanage" command from virtualbox

G:\WM_disks> "c:\Program Files\Sun\VirtualBox\vboxmanage" convertfromraw linux_1_d1.raw linux_1_d1.vmdk -format VMDK

The conversion is a little bit long and depends on the size of the disk.

To finish, you can add the converted disk to an existing or new VM ware virtual machine.

I'm Back

2010-03-02T12:42:00.004+01:00

A quick post to inform readers that I was a little bit busy last month because of my removal and because I have started a new job on a French Oracle consulting company DIGORA.

So I will write new posts next days !!!

See you soon !

Good news

2010-01-22T10:18:00.002+01:00

EU has officially validated the acquisition of Sun Microsystems by Oracle.
That's a really good news for Sun hardware and Software.

Oracle 11g md_restore and compatible.rdbms

2009-11-19T16:02:00.006+01:00

I have recently made a restore test of my diskgroup metadata with the new version of oracle server : 11.2.0.1.

Initially, I have created a TEST_DG diskgroup with the new asmca interface.
As I had a 10g database on my laptop, I have set the compatibility.rdbms parameter on 10.2.0.0.
After a rapid md_backup command to save my diskgroup metadatas, I have tried to restore it with the md_restore command.

What a surprise when I saw this error message:

ASMCMD [+] > md_restore md_backup.sav -G TEST_DG
Current Diskgroup metadata being restored: TEST_DG
ASMCMD-09352: CREATE DISKGROUP failed
ORA-15018: diskgroup cannot be created
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.7.0 or higher (DBD ERROR: OCIStmtExecute)

After a short research, I realize that the CREATE DISKGROUP command is generated like this:

create diskgroup TEST_DG EXTERNAL redundancy disk '/dev/oracleasm/disks/ASM10' name TEST_DG_0001 size 100M disk '/dev/oracleasm/disks/ASM05' name
TEST_DG_0000 size 100M attribute 'compatible.asm' = '11.2.0.0.0' , 'compatible.rdbms' = '10.2.0.0.0' , 'au_size' = '1048576', 'sector_size' = '512
', 'cell.smart_scan_capable' = 'FALSE';

And this command uses properties which have been introduced in Oracle 11g release (for example: sector_size). Next command works fine:

create diskgroup TEST_DG EXTERNAL redundancy disk '/dev/oracleasm/disks/ASM10' name TEST_DG_0001 size 100M disk '/dev/oracleasm/disks/ASM05' name
TEST_DG_0000 size 100M attribute 'compatible.asm' = '11.2.0.0.0' , 'compatible.rdbms' = '10.2.0.0.0' ;

So, if like me, you have some Oracle 10g databases that run on an ASM 11g release, you have to restore your diskgroup metadata in an SQL file (by using -S option of the md_restore command) and adapt the DDLs for your needs.

Not really easy ! ;)

delete ADR "problems"

2009-11-05T16:02:00.010+01:00

When you are using adrci to interface adr you can easily purge incidents, health monitor reports etc...

... but you can't purge problems:

adrci> help purge

Usage: PURGE [[-i | ] |
[-age [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:

Purpose: Purge the diagnostic data in the current ADR home. If no
option is specified, the default purging policy will be used.

Options:
[-i id1 | id1 id2]: Users can input a single incident ID, or a
range of incidents to purge.

[-age ]: Users can specify the purging policy either to all
the diagnostic data or the specified type. The data older than
ago will be purged

[-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
data to be purged.

Examples:
purge
purge -i 123 456
purge -age 60 -type incident

No trace of problem purge :(

If you want to delete those problems, you have to know about a SQL interface directly in adrci. (The "tables" represents XML files mappings, these files are located in the repository).

So, I paste above a little example of a DELETE in adrci interface:

adrci> delete from problem;

3 Rows Deleted
adrci> commit;

Transaction Committed
adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/racdb/racdb1:
*************************************************************************
0 rows fetched

Be careful, if you delete problems, you will loose the problem memory of your database.

Win $10 million, if your performance are better than exadata v2

2009-10-14T11:24:00.004+02:00

Larry Ellison has announced a new challenge !
If you win it you can earn $10 million.

The challenge: if one of your existing database application cannot run twice as fast on exadata v2 system, those million are for you !

Off course, IBM can challenge it ;)

Read more here : http://www.oracle.com/features/exadatachallenge.html

[11gR2 RAC] instances disappear from crs_stat output

2009-09-25T09:28:00.004+02:00

In an Oracle 11g Release 2 Real Application Cluster configuration, your instances are located on the cluster nodes.

In Oracle 10g, you could see where the instances were located by using the crs_stat -t command.

In 11gR2, crs_stat output doesn't show instances as application.

If you want to know where your instance is running, you must use srvctl command to get this information :

$ srvctl status instance -d racdb -i racdb1
Instance racdb1 is running on node linux1

load ACFS on CentOS 5

2009-09-22T10:16:00.005+02:00

In my last post, I wrote that acfs was not compatible on CentOS.
So I decided to search a solution ... and I found it.

acfsload, the program who load the acfs driver makes an OS version check which fails.
This program uses a perl module osds_acfslib.pm which is located on the $ORACLE_HOME/lib directory of the Grid Infrastructure.

In this perl module, you can find at lines 280/281 the perl code that makes this version check. This check is only an "rpm -qa | grep release" !

So if you are on a CentOS 5, you just have to complete this line by adding a centos check :

[root@linux1 lib]# cd /u01/app/oracle/product/11.2.0/grid/lib
[root@linux1 lib]# cp -p osds_acfslib.pm osds_acfslib.pm.ORIG
.../...
[root@linux1 lib]# diff osds_acfslib.pm osds_acfslib.pm.ORIG
281,281
< ($release =~ /redhat-release-5/) || ($release =~ /centos-release-5/))
---
> ($release =~ /redhat-release-5/))

When you have done this add-on, you must finish what the installer have failed ie. copy the acfs kernel modules at the right place and regenerate
kernel module dependencies.

NB: Be careful of your kernel version, in this example, I was using a 2.6.18-92.1.22.el5 kernel.

[root@linux1 lib]# mkdir /lib/modules/2.6.18-92.1.22.el5/extra/usm
[root@linux1 lib]# cp /u01/app/oracle/product/11.2.0/grid/install/usm/EL5 /i386/2.6.18-8/2.6.18-8.el5-i686/bin/*.ko /lib/modules/2.6.18-92.1.22.el5/extra/usm/
[root@linux1 lib]# chmod 744 /lib/modules/2.6.18-92.1.22.el5/extra/usm/*
[root@linux1 lib]# depmod

Now you can execute the acfsload and load the acfs modules without warning:

[root@linux1 lib]# /u01/app/oracle/product/11.2.0/grid/bin/acfsload start -s

You can check everything is loaded with the acfsdriverstate command:

[root@linux1 lib]# cd /u01/app/oracle/product/11.2.0/grid/bin
[root@linux1 bin]# ./acfsdriverstate -orahome /u01/app/oracle/product/11.2.0/grid loaded
ACFS-9203: TRUE

Note, on the next reboot, you have to load the acfs modules, and mount the acfs filesystem.
You can do this by writing you own init shell script based on the chkconfig format, and load it on the desired runlevel.

acfs is not compatible on CentOS

2009-09-15T20:19:00.003+02:00

[root@linux1 bin]# ./acfsload start -s
ADVM/ACFS is not supported on centos-release-5-2.el5.centos

Yuk :(

I'm searching for a solution

Generate a random date in Oracle

2009-09-10T16:55:00.005+02:00

This morning, I had to generate a random date using DBMS_RANDOM package. As the documentation mentions this package can only generate alphanumeric and numeric value, I have to search a little bit.

So the trick for generating a random date is to choose two dates between the random process will choose a date.

Those dates will be translated to Julian format. As the Julian format is the number of days since January 1, 4712 BC, you can use the numeric random generation procedure of DBMS_RANDOM package.

Let's see how we can generate a random date between 2009 Jan, 1st and 2009 Dec, 31st :

SQL> create table t (d date);

Table created.

SQL> select to_char(to_date('2009/01/01','YYYY/MM/DD'),'J') "20090101",
2 to_char(to_date('2009/12/31','YYYY/MM/DD'),'J') "20091231"
3 from dual;

2009010 2009123
------- -------
2454833 2455197

SQL> begin
2 for i in 1..5 loop
3 insert into t values(
to_date(trunc(dbms_random.value(2454833,2455197)),'J'));
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select * from t;
D
----------------------
2009-NOV-30 12AM:00:00
2009-MAY-31 12AM:00:00
2009-SEP-01 12AM:00:00
2009-DEC-28 12AM:00:00
2009-MAY-03 12AM:00:00

Event for crashing an oracle session

2009-09-09T17:10:00.006+02:00

Thanks to Grimm for this information relayed by Laurent Schneider.

This is a tricky method for crashing a session without disconnecting the user :

Session 1
- - - - - - -
SQL> begin
2 while 2>1 loop
3 null;
4 end loop;
5 end;
6
/

Session 2 (as user SYS or as user who has privilege to execute the package DBMS_SYSTEM.SET_EV)
———-
SQL> exec dbms_system.set_ev(sid,serial#,10237,4,'');

PL/SQL procedure successfully completed.

Session 1
————-
ERROR at line 1:
ORA-01013: user requested cancel of current operation

The session is not killed, user is still connected -
If you want to perform other queries, administrator have to invalidate this event (exec dbms_system.set_ev(sid,serial#,10237,0,'');) otherwise you will encountered some "ORA-00604: error occurred at recursive SQL level 1"

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit

Hello World :)

2009-09-08T00:09:00.000+02:00

Because it's a way of life for numbers of Oracle DBA in the world.
I did it ...

On this blog, I will publish messages concerning my job, and my experiences of Oracle Database.

Laurent