Oracle Magazine January-February 2007

Check the oracle magazine on otn.oracle.com/oramag

In the peer-to-peer, you can read more about 3 Oracle Aces, Steve Karam, Nicolas Gasparotto, Dave Moore.

In the technology columns, three more aces talk about Oracle technlogies. Steven Feuerstein about The Right Place for PL/SQL, Arup Nanda about Recover in a Flash, and Tom Kyte about On Top-n and Pagination Queries. Tom also remind us that group by does not sort. However, if you have one application which used to expect sorted result in 9i and is now produced unexpected result in 10g, you can still read the Note:345048.1 in Metalink to find some workarounds, for example by deactivating hash-group-by with _gby_hash_aggregation_enabled parameter set to false

Welcome to the Oracle Database 11g Release 1 Beta Program!

Today is a good day, I have been accepted to the beta program and will be able to download 11gR1 beta 4 shortly.

There is a Confidential Disclosure Agreement, so do not expect to read more on 11g here before the production release.

My blog articles about 11g in OpenWorld 2006:
11g New Features
11g New Features part II

10.2.0.3

I successfully installed 10gR2 patchset 2 on my notebook :

SQL> select ACTION,VERSION,COMMENTS from registry$history;

ACTION  VERSION    COMMENTS
------- ---------- ------------------------------
CPU     10.2.0.2.0 CPUOct2006
UPGRADE 10.2.0.3.0 Upgraded from 10.2.0.2.0

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- -----------
Oracle Expression Filter            10.2.0.3.0 VALID
Oracle Database Catalog Views       10.2.0.3.0 VALID
Oracle Database Packages and Types  10.2.0.3.0 VALID
Oracle Database Java Packages       10.2.0.3.0 VALID
JServer JAVA Virtual Machine        10.2.0.3.0 VALID
Oracle XDK                          10.2.0.3.0 VALID

How Much Is My Blog Worth?

Inspired by How Much Is My Blog Worth?

This is all the pain by changing domain, bye bye top position in google, bye bye backlinks…

My old blog is still active, and I hope to get equivalent ranking until end of 2007. I think it was a smart move, I have now more freedom to customize my site with any content, to edit my feed, my pages, my pictures, etc. I wish I had done it before 😮

Restrict network access to listener

If I have a limited number of db clients which are authorized to access my listener, I can restrict access to my listener by setting only two parameters in sqlnet.ora.

TCP.VALIDNODE_CHECKING = yes
TCP.INVITED_NODES = (dbclient001,chltlxlsc1)

chltlxlsc1 is my db server, I include it in the list, it is required to start the listener locally.

From dbclient001, I can connect :

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:47:43 2006

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

From dbclient002, I cannot connect

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:48:26 2006

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

ERROR:
ORA-12547: TNS:lost contact

backup your blog!

You surely have not missed the “Amazing November 2006 Blog Catastrophe” of Mark Rittman.

I use blogger and by typing “backup blogger” in google, I found [edit]something that has been removed in the meantime (and I do not use blogger anymore) [/edit, janv 2011].

It let you change the formatting of your blog to have all your posts (max 999) and all your comments in one page. If you do not have your own ISP but use blogspot.com, then it will replace your blog by this not-fancy page. But just for the time to save it on your local disk and then you restore your template. Surely less than 5 minutes , unless you surf with a 100 bps or 480 bps underwater accoustic modem like this one.

I have now a dump of my blog. The size of the html file is 440k right now.

pivot table

First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.

Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT

Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)

select person.name,
  property.type,
  property.value
from person, property
where 
  person.id=property.person;

NAME TYPE     VALUE
---- -------- ------
John gender   male
Mary category junior
Mary gender   female

for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)

select name, 
  gender.value gender, 
  category.value category
from person,
  property gender,
  property category
where
  person.id = gender.person(+) 
  and gender.type(+)='gender'
  and person.id = category.person(+)
  and category.type(+)='category';

NAME GENDER CATEGO
---- ------ ------
Mary female junior
John male

By using the Tom Kyte method described on asktom, I could have used aggregation.

select name,
  max(decode(type,'gender',value)) gender,
  max(decode(type,'category',value)) category
from person , property
where person.id = property.person (+)
group by name;

NAME GENDER CATEGO
---- ------ ------
John male
Mary female junior

To do the opposite, I posted once in a forum

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

    DEPTNO TYPE  VALUE
---------- ----- --------------
        10 DNAME ACCOUNTING
        20 DNAME RESEARCH
        30 DNAME SALES
        40 DNAME OPERATIONS
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

Well, in the next generation database, this is going to be easier, maybe.

With the introduction of pivot keyword, the following should work

select name, type, value
from person , property
pivot (max(value) 
for type in (
  'gender' as gender,
  'category' as category))
where person.id = property.person (+);

and with the unpivot keyword

select * 
from dept 
unpivot (value 
for type in (
  dname as 'DNAME',
  loc as 'LOC'));

It would be interesting to compare the execution plans !

How To Add The Domain Name Of The Host To Name Of The Agent

I have been looking for this note for ages : Metalink note 295949.1

Now I know how to rename my targets in Grid Control! the trick is to stop the agent
AH/emctl stop agent
to remove the upload and state files/subdirectories

cd AH/sysman/emd
find state upload recv agntstmp.txt lastupld.xml protocol.ini -type f -exec rm {} ';'
rm -r state/*

to remove the agent in the grid

col TARGET_TYPE for a17
col TARGET_NAME for a60
set lin 100 pages 0 emb on newp none head on autop on
select TARGET_TYPE,TARGET_NAME
from MGMT$TARGET
where TARGET_TYPE='oracle_emd'
order by TARGET_NAME;
var target_name varchar2(40)
prompt Enter the target_name to delete
exec :target_name:='&TARGET_NAME';if (:target_name is not null) then mgmt_admin.cleanup_agent(:target_name); end if

to edit the targets.xml
vi AG/sysman/emd/targets.xml ### Get the source of the traditional vi
to rename your targets (listener, host, db), and restart your agent
AH/emctl start agent
hth

difference between two dates

How can i get the difference in days between two dates, d1 and d2 ?

for example :

with t as (select
  to_date('2000-02-01','YYYY-MM-DD') d1,
  to_date('2000-01-23','YYYY-MM-DD') d2
  from dual)
select d1,d2,d2-d1
from t;
D1         D2              D2-D1
---------- ---------- ----------
01.02.2000 23.01.2000         -9

How can i get the difference in hours:minutes:seconds between two dates, d1 and d2 ?
Use the interval datatype. To have a format different than the default +000000000 00:00:00.000000, use extract


with t as (select
to_date(‘2000-02-01 11:22:02’,
‘YYYY-MM-DD HH24:MI:SS’) d1,
to_date(‘2000-01-23 12:00:03’,
‘YYYY-MM-DD HH24:MI:SS’) d2
from dual)
select d1,d2,case
when d1d2 then
‘-‘||
(extract(day from ((d1 – d2)
day to second))*24 +
extract(hour from ((d1 – d2)
day to second)))||’:’||
extract(minute from ((d1 – d2)
day to second))||’:’||
extract(second from ((d1 – d2)
day to second))
when d1=d2 then
‘0:0:0’
end “D2-D1”
from t
/
D1 D2 D2-D1
——————- ——————- ———-
01.02.2000 11:22:02 23.01.2000 12:00:03 -215:21:59

alias oraver 2.0

Thanks to an anonymous comment in my post yesterday, I can now provide a more flexible version of my alias, which do not require the database to be running nor the sysdba privilege


$ alias oraver
oraver=’echo ‘\”ORACLE_SID VERSION %CPU RSZ VSZ START_TIME’\”;awk -F: ‘\”/^[^ *#]/{print “printf 42%-9s %11s %5s %8s %8s %s\\n42″,$1,”$(ORACLE_HOME=”$2,$2″/bin/sqlplus -v 2>/dev/null|cut -d47 47 -f3) $(ps -eo pcpu,rsz,vsz,start_time,args|sed -n 42s/ [o]ra_pmon_”$1″.*//p42)”}’\” /etc/oratab |sh’
$ oraver

ORACLE_SID    VERSION  %CPU      RSZ      VSZ START_TIME
LSC01      10.2.0.2.0   0.0    12184   508448 10:09
LSC02       9.2.0.8.0   0.0     8420   303320 10:10
LSC03       9.2.0.8.0

Well, it is based on the sqlplus version, which is not necessarly the same as database version, like 8.1.7.3 does show 8.1.7.0, but in most of the recent versions, it should be ok

ps parameters may not work on all os, you can use -ef if you prefer

ps -ef |grep pmon alternative

I just wrote a new alias to check if the databases are up and running. I added the version and a dash for non-running database. Needed is /etc/oratab + sysdba access to the database.

Here it is :

awk -F: ‘/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2” “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep 42^+42||echo 42- “$1″42”)}’ /etc/oratab

+ LSC01         10.2.0.2.0
+ LSC02         9.2.0.8.0
- LSC03

Or, as an alias :

alias oraver=’awk -F: ‘\”/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2” “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep 42^+42||echo 42- “$1″42″)}’\” /etc/oratab’

rac automation

Werner Puschitz talked yesterday about Automated Oracle Real Application Clusters Deployment: How Dell Does IT.
The result of the automation is, install a 6 nodes cluster in 30 minutes. Which is quite impressive.

Werner divided the automation in three RPM packages.

1) configure the host: set up private and virtual interface (according to a naming convention, for example private is hostname-priv, vip is hostname-vip). Set up the system configuration (shared memory, etc). Set up the SAN, shared devices. Set up ssh. Define a cronjob to check if all nodes are configured and accessible. Write an email to the sysadmin when all nodes are ready

2) install the clusterware. the RPM is build with a response file + the oracle software. The response file is recorded with ./runInstaller -record.

3) install the database software. the RPM is build with a response file too

Werner said it is rather worth to build those packages for large companies, where you have many RAC installations. In my opinion, even if you have only one or two rac, you can benefit from this. It provides you an easy way to reinstall the system, upgrade it to a new version, create two systems (dev+prod) with the same configuration.

Thanks Werner for this very good one!

11g new features part II

I have been following 2 sessions with Juan Loaiza about new features.

A very few of them here :
SQL> create edition v1_0;
SQL> alter session set edition=v1_0;

You can create a versioning of your objects, so you will be able to have many versions of the same package at the same time. It is a bit similar to workspace, where you have many versions of the data at the same time. It is very profitable in release management and availability.

About the cache result hint, you can also use
alter session cache results;, which can be useful for lookup table. As commented on a previous post, you can still use the old alter table cache; or the keep buffer pool. It depends.

Performance enhancement in the RAC interconnect.

Server side connection pooling. An additional layer to the shared server, to enable faster [actually to bypass] session creation.
edit: check Database Resident Connection Pooling

Securefiles. This is a good one. It is a huge improvement to BLOB. Faster, with compression, encryption.

Partition interval. This is a marvelous one ! You can now partition by date, one partition per month for example, with automatic partition creation.

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.

Capture/replay workload. Sounds appealing. You can capture the workload in prod and apply it in development.

Many other enhancement.

Juan expect to see a Petabyte single database, with a terabyte of memory and 1000 cpus before the end of the decade

this was a 2006 announcement, not all features announced have been implemented in prod

OOW 2K6 day 1

I tried to watch the keynotes yesterday, but it is just to difficult to sit and not sleep for me… so I have seen only a few minutes of each one.

The PL/SQL 11g session was excellent, I wrote an overview in a separate post.

I started the afternoon with APC session, about the importance of testing and the power of the utPLSQL package. He presented that methodology and also the concept of testing. Fixing a bug at the development stage is fine, fixing it in production is very costly. I resume the method in those steps. First, checkout your package and your testing procedure. Second, modify your package. Never commit before testing. Improve your tests. Finally, commit your changes in both the source and the tests. APC was somehow unlucky with a laggy sql-developer and an unregistered textpad. Thanks Andrew for this presentation.

Than I went to Tom Kyte session about the worst things to do. Instead of telling what to do, Tom ironised on what we Should Always Do. He started with The Experts Are Always Right. Funny presentation, lots of laugh in the audience. I have meet Ken Jacobs during Tom Session. A good moment.

I have been to the exhibition hall, and I talked to the egenera team about RAC on BladeFrame and will go to the session S283094 on Wednesday.

11g new features

The presentation of Bryn Llewellyn was a good one! He talked about the 11g new features. Well, he said it is maybe not named 11g and maybe none of the features will be available. But since the beta is already available, I guess all of the features he talked will be available. It is very rich, and went fast, so I apologize for not relating all of them, or any mistakes that I include.

The result set caching. My favorite. This sounds like a huge performance improvement for many applications. I have an application which is doing a select count(*) at every screen. It is the bottleneck. A possible workaround is to use query rewrite and materialized view. Now in 11g, you just add a /*+result_cache*/ hint, so the result of the query will be cached. Justin Cave asked about the integrity. The answer is : there is no compromise of the integrity. There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated. As for the materialized view rewrite in 9iR1, I guess there will be quite a few P1 bug for that hint in 11.1.0, but still it is a great announcement. This hint also works for PL/SQL functions, the declaration of the function can include result_cache relies on (emp)

Fine grained dependency tracking. Currently, if you have a view or a function based on a table, and if you modify that table, the view is getting invalid. This is what Bryn called : aggressive object invalidation. Now in 11g it has improved, so if you modify an element of the table which is not included in that view (ex: you add a column), the view is not invalidated. This is wise.

Regular expression improvements. This is rather a SQL improvement, glad Bryn did presented it too. One of this is a new function called regexp_count. All of the existing functions have been improved. I love that kind of new features, because they offer new way of solving queries. I hope I will be able to reanswer Re: Can any one retrive Nth column from a table with one less function in 11g.

Using the => syntax in sql. In 11g you can use select f(x=>1) from dual, which is a goodie.

Fine grained access control to the TCP packages family (utl_tcp, utl_smtp, …). This is a good one. You can grant execute on the package only for specific IP addresses.

Read-only table. Ok, it reminds me one thread how-to-set-a-table-in-read-only-mode. Glad to see this one too!

Fast triggers. Nothing to do about it, triggers are just faster in 11g.

Intra-Unit inlining. This is the 3rd level of PLSQL_optimize_level.

Trigger order. You have many triggers on one table, you can now specify in which order they will be fired.

Compound trigger. A new powerfull type of trigger, which enables you to specify a declarative section (and to have a kind of package global variable), a before procedure, an after each row procedure, and an after procedure. All this in a single trigger.

Create disabled trigger. It is now possible to create a disabled trigger. You can create an invalid trigger, and compile it later, but if not used appropriately by the developers, it may create a new kind of invalid objects in the databases. Good for the developer. Not necessarily pleasant for the DBA.

Using sequence in PL/SQL without DML. It is possible to use x:=s.nextval in plsql, instead of selecting from dual.

PLSQL_Warning. You can compile a procedure with PLSQL_WARNING=enable all, to get errors when others exceptions are caught and not raised. Tom Kyte does appreciate this one 😉

A special mention to the super keyword. I am enthusiastic but somehow skeptic about the implementation of inheritance in PL/SQL. Well, I am looking forward from reading more about this.

Faster and real native compilation. Improvement have been done to bypass the filesystem and the external C compiler in this release.

DBMS_SQL improvement. Ways to bypass the 32k limitation. I have not understand very well this change.

The continue keyword. You can use the continue keyword in your plsql loops, as you would do in another languages.

disclaimer: the post was based on OOW 2006 announcement and may not reflect the production release of 11g

not enough time to do all

I updated my calendar for Oracle Open World. By rechecking the location, I figured out I did book to much. Unfortunately, I will not be able to go to steven feuerstein session S281918 on monday, because it starts at the same time as tom session ends.

I had a difficult choice for tuesday :
– S281213 (now full) Developing a Successful Database Backup and Recovery Strategy: Customer Experiences with Jonathan Intner, oracle specialist at Novartis
– S283505 Battle-Tested Best Practices for Provisioning, Managing, and Monitoring Oracle Real Application Clusters Environments with Arup Nanda, Oracle Ace
– S283870 Flexframe for Oracle: Adaptive Infrastructure Solution for Oracle Database and Oracle Application Server 10g

I chose the third one, because I am managing a workshop with RAC on Egenera BladeFrame one week later in Zürich, so it will be very useful material.

I also managed to add three expert sessions at the otn lounge to my schedule :

Steven Feuerstein, Arup Nanda and Tom Kyte with APC

apply CPUOct2006

I downloaded the CPUOct2006 patch for 10.2.0.2 and applied it successfully to my database.

SQL> select action,id,comments from dba_registry_history;
ACTION         ID COMMENTS
------ ---------- ----------
CPU       5490848 CPUOct2006

in the logfile, apart from a few ignorable ORA-02303: cannot drop or replace a type with type or table dependents errors, everything went fine.

Applying the security patch shortly after they are released is in my opinion a good practice, because a fix could sometimes reverse-engineered to reveal the bug.

Tomorrow afternoon I flight via London to SF for OOW2006. I am quite unhappy about not taking my notebook, my phone, not even a book or a SuDoKu in the plane 🙁 Well, I hope BA will not refuse to serve me wine this time as Delta did last year

Migration of tnsnames.ora to LDAP (Sun Java System Directory Server)

In this post, I did show how easy it is to use OID to resolve your network service names.

Apart OID, AD (Microsoft Active Directory) is also supported.

However, I do not want to use such products, as my customer already have a Sun Java System Directory Server running.

It is quite easy. Here are the steps with the SunOne Console.

1) expand the schema
login to the Directory Server as cn=directory manager
click schema in the configuration tab
In the Attributes subtab, click create, and type it orclnetdescstring as attribute name, and select OctetString as Syntax, and uncheck multi-valued, click OK.
In the Object Classes subtab, Create a class named OrclService , add cn as required attribute and orclnetdescstring as allowed attribute. Click OK

2) start adding services
either with your prefered ldap GUI (like Siemens DirX Manager) or with command line
lsc01.diff
dn: ou=intranet, dc=lcsys, dc=ch
ou: intranet
objectClass: top
objectClass: organizationalunit

dn: ou=applications, ou=intranet, dc=lcsys, dc=ch
ou: applications
objectClass: top
objectClass: organizationalunit

dn: ou=TNSnames, ou=applications, ou=intranet, dc=lcsys,dc=ch
ou: TNSnames
objectClass: top
objectClass: organizationalunit

dn: cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch
cn: OracleContext
objectClass: top
objectClass: orclservice

dn: cn=lsc01, cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch
cn: lsc01
objectClass: top
objectClass: orclservice
orclnetdescstring: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=blade01.lcsys.ch)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = lsc01.lcsys.ch)))

which I can add with

ldapadd -h blade01 -p 34001 -D “cn=Directory Manager” -w *** -f lsc01.ldif

adding new entry ou=intranet, dc=lcsys, dc=ch

adding new entry ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry cn=OracleContext,ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry cn=lsc01, cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

Configuring sqlnet.ora and ldap.ora is the last step :
sqlnet.ora
TNSPING.TRACE_LEVEL = SUPPORT
TNSPING.TRACE_DIRECTORY = /tmp
NAMES.DIRECTORY_PATH= (LDAP)

ldap.ora
DIRECTORY_SERVERS= (blade01:34001)
DEFAULT_ADMIN_CONTEXT = "ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch"

try to tnsping, it should work. If it does not, check /tmp/tnsping.trc

$ tnsping LSC01

TNS Ping Utility for Solaris: Version 9.2.0.8.0 - Production on 09-OCT-2006 15:50:42

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
/export/home/schnela1/tmp/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade01)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = lsc01.lcsys.ch)))
OK (0 msec)

note that using something else than OID or AD is not supported