New PC

I bought a new PC at Eastern. I have installed 100Gb partition for Linux and 100Gb for Windows. I tried Fedora Core 5 and OpenSuse 10.0, in 64bit mode.

I currently spend most of my “free” time to prepare the OCA Application Server certification, next friday (28th).

Tonight I borrowed a game from a friend : Unreal Tournament 2004. I haven’t play such a game since a long time, and I must say, I enjoyed it very much. I am just overtired now. The graphic is fast, it is easy to master (well, I played at the novice level) and it is something I would never recommend to my kids ! Just shooting everywhere …

Well, they are just too young now, but Dora is really mastering Memory Game and she can even write her name when she achieved a high score! Yesterday she had her fourth birthday.

isqlplus and sqlplusw desupport

isqlplus and sqlplusw (gui sqlplus windows) will be desupported in 2010. Read Note 359855.1 and Note 359859.1 on Metalink.

Oracle recommends migrating to Raptor (SQLDeveloper) or to Application Express (HTMLDB) or command line sqlplus.

Oracle Support apparently does not know that Raptor changed name before going to production, well, names change so often, it is difficult to know them all!

dba workshop

Last week I gave an internal 2-days dba course. All three students had good experience of filesystem backup and unix, but hardly any oracle experience.

I figured out recovery is more complex than I thought! By explaining to other, how often do you realise do you do not know the answer yourself? It has been very educative to me too…

First inconsistency I discovered :
I tried to simulate a disaster by removing all controlfiles, redologs, datafiles. I expected the students to notice “something”. But, O Surprise, the db kept running, nothing written in alert logs, it was possible to create and drop tables, nothing went wrong (but they were no files). Probably the OS has not notified the file were no more there, and Oracle opened the files already. Strange. Even SHUTDOWN has been successful! But of course STARTUP did not work.

Next time I prepare a B&R workshop, I must invest time to check that a disaster is effective!

Second inconsistency:

Report unrecoverable datafiles and validate backups. I wrote this in the rman technology forum this morning: REPORT NEED BACKUP is not suffisant to have consistent backups, nor RESTORE VALIDATE DATABASE!

More inconsistencies :
somehow, when users are doing backups with nocatalog and try to restore with the recovery manager catalog, it is not working. Well, it is not that surprising, but it really increases the difficulty of recovery procedures!

However, those “inconsitencies” were good in this informal training, feedback from students was positive, we did “survive” all those problems and they realised RMAN recovery is not as simple as RMAN backup…

oracle days

I was yesterday at Oracle Days. I have met a dozen of people I know, and was pleased to see Troy, who gave me one more tip for installing EM grid on a high availability cluster. I also saw Lutz, which announced me the workshop with Jonathan Lewis in a few weeks www.digicomp.ch/jlewis

I took two photos with my new handy, one with my collegue Zarko and one with the Oracle Partner Chocolate I gave to my daughter :-)

OCM 10g

The 10g OCM practicum, expected for 2004, is now officially launched.

Oracle Database 10g Certified Master Exam

Well, depending on your country, you will get a
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275. In this case, just change your country to United States. Then you will see the details.

If you want to register, you probably need to wait a little bit more :

A public event is not currently available for this offering. To express your interest in a future publicly scheduled event, or, if you would like Oracle University to contact you regarding other options, please Not finding the course or date that you’re looking for? Let us know!

In addition to 10g New features like bigfile tablespaces, ASM, Flashback DB and Datapump, the 10g OCM includes RAC, logical standby, Grid Control and Performance Tuning assistants. Quite promising…

The 10g OCM upgrade is currently in developement. But I do not expect it to be available in my country or neighbour country in 2006.

to_char(interval)

There is no to_char function available for intervals.

Or at least it does not work as expected


SQL> select to_char(interval '1234' second, 'HH24:MM') from dual;
TO_CHAR(INTERVAL'12
-------------------
+00 00:20:34.000000

I just write my own one, with some new format elements

For interval day to second, I have DDD number of days, HH number of hours (0-24), HHH total number of hours (0-99999999999999), etc

here it is


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 17:15:59 2006

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> 
SQL> create or replace function tochards(f_int interval day to second,f_fmt varchar2) return varchar2 is
  2  -- valid formats are DDD, HHH, HH, MMM, MM, SSS, SS, FF
  3      ret varchar2(4000);
  4      f varchar2(4000);
  5      i interval day(9) to second(9);
  6  begin
  7      if (f_fmt is null or f_int is null) then
  8          return null;
  9      end if;
 10      f := upper(f_fmt);
 11      if (translate(f,'XDHMSF,.:;/- ','X') is not null) then
 12          raise_application_error(-20001,'Invalid format');
 13      end if;
 14      if (extract(day  from i)<0) then
 15          ret:='-';
 16          i:=f_int*(-1);
 17      else
 18          ret:='';
 19          i:=f_int;
 20      end if;
 21      while (f is not null) loop
 22          if (f like 'DDD%') then
 23              ret:=ret||to_char(extract(day from i),'FM999999999999999999');
 24              f:=substr(f,4);
 25          elsif (f like 'HHH%') then
 26              ret:=ret||to_char(extract(day from i)*24+extract(hour from i),'FM999999999999999999');
 27              f:=substr(f,4);
 28          elsif (f like 'HH%') then
 29              ret:=ret||to_char(extract(hour from i),'FM999999999999999999');
 30              f:=substr(f,3);
 31          elsif (f like 'MMM%') then
 32              ret:=ret||to_char(extract(day from i)*24*60+extract(hour from i)*60+extract(minute from i),'FM999999999999999999');
 33              f:=substr(f,4);
 34          elsif (f like 'MM%') then
 35              ret:=ret||to_char(extract(minute from i),'FM999999999999999999');
 36              f:=substr(f,3);
 37          elsif (f like 'SSS%') then
 38              ret:=ret||to_char(extract(day from i)*24*60*60+extract(hour from i)*60*60+extract(minute from i)*60+trunc(extract(second from i)),'FM999999999999999999');
 39              f:=substr(f,4);
 40          elsif (f like 'SS%') then
 41              ret:=ret||to_char(trunc(extract(second from i)),'FM999999999999999999');
 42              f:=substr(f,3);
 43          elsif (f like 'FF%') then
 44              ret:=ret||to_char(mod(extract(second from i),1),'FM999999999999999999');
 45              f:=substr(f,3);
 46          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
 47              ret:=ret||substr(f,1,1);
 48              f:=substr(f,2);
 49          else
 50              raise_application_error(-20001,'Invalid format : '||f_fmt);
 51          end if;
 52      end loop;
 53      return ret;
 54  end;
 55  /

Function created.

SQL> 
SQL> sho err
No errors.
SQL> 
SQL> create or replace function tocharym(f_int interval year to month,f_fmt varchar2) return varchar2 is
  2  -- valid formats are YYY, MMM, MM
  3      ret varchar2(4000);
  4      f varchar2(4000);
  5      i interval year to month;
  6  begin
  7      if (f_fmt is null or f_int is null) then
  8          return null;
  9      end if;
 10      f := upper(f_fmt);
 11      if (translate(f,'XYM,.:;/- ','X') is not null) then
 12          raise_application_error(-20001,'Invalid format');
 13      end if;
 14      if (extract(year  from i)<0) then
 15          ret:='-';
 16          i:=f_int*(-1);
 17      else
 18          ret:='';
 19          i:=f_int;
 20      end if;
 21      while (f is not null) loop
 22          if (f like 'YYY%') then
 23              ret:=ret||to_char(extract(year from i),'FM999999999999999999');
 24              f:=substr(f,4);
 25          elsif (f like 'MMM%') then
 26              ret:=ret||to_char(extract(year from i)*12+extract(month from i),'FM999999999999999999');
 27              f:=substr(f,4);
 28          elsif (f like 'MM%') then
 29              ret:=ret||to_char(extract(month from i),'FM999999999999999999');
 30              f:=substr(f,3);
 31          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
 32              ret:=ret||substr(f,1,1);
 33              f:=substr(f,2);
 34          else
 35              raise_application_error(-20001,'Invalid format : '||f_fmt);
 36          end if;
 37      end loop;
 38      return ret;
 39  end;
 40  /

Function created.

SQL> 
SQL> sho err
No errors.
SQL> 
SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'HHH:MM') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
53561:15

SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'DDD HH:MM:SS') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
2231 17:15:59

SQL> select tocharym(interval '25' month, 'YYY:MM') from dual;

TOCHARYM(INTERVAL'25'MONTH,'YY
------------------------------
2:1

SQL> select tocharym(interval '-25' month, 'MMM') from dual;

TOCHARYM(INTERVAL'-25'MONTH,'M
------------------------------
-25

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Application Server 10gR3 on Suse Linux 10

I just download and installed Oracle Application 10g Release 3 on my linux box.

The version released this week is very light! No ldap, no database, well, a light one…


Application Server 10g Release 3 x86: 531 MB
  Included:   
    * Oracle HTTP Server
    * Oracle Application Server Containers for J2EE (OC4J)
    * Oracle Enterprise Manager 10g Application Server Control
    * Oracle Business Rules
    * Oracle TopLink

Application Server 10g Release 2 x86: 2 GB
 Included:
    * Oracle HTTP Server Oracle Application Server Containers for J2EE (OC4J)
    * Oracle Application Server Web Cache
    * Oracle Application Server Portal
    * Oracle Application Server Wireless
    * Oracle Sensor Edge Server
    * Oracle Enterprise Manager 10g Application Server Control
    * Oracle Database Server 10g (10.1.0.4.2)
    * Oracle Internet Directory
    * Oracle Application Server Single Sign-On
    * Oracle Application Server Directory Integration Provisioning
    * Oracle Application Server Delegated Administration Services
    * Oracle Application Server Certificate Authority
    * Oracle Application Server Forms Services
    * Oracle Application Server Reports Services
    * Oracle Application Server Personalization
    * Oracle Business Intelligence Discoverer
    * Oracle Security Developer Tools
    * Oracle Application Server Guard
    * OracleAS Backup and Recovery Tool

I tried to install on my notebook, and, o surprise, it works…
the only thing that was missing and required was public domain korn shell, pdksh, which is easy to find by googling

Recursive PL/SQL

it will be a good week !

I found an elegant way to solve a query with recursive pl/sql.

an user wanted to have DHSGHDADSFDF translated in DHSGAF, that is, duplicated chars removed, order retained.

here is my function :

create or replace function f(v varchar2) return varchar2 is
begin
    if (v is null) then return null;
    else return substr(v,1,1)||f(replace(substr(v,2),substr(v,1,1));
    end if;
end;
/

ref: using recursion with PL/SQL

raptor early adopter release 3 is out

This is THE release I have been waiting for, a raptor which does NOT save the password on your local PC. Save a password, even encrypted, on you local PC allows every one with physical access to your PC to get access to your productive database, which may be high-secured and with controlled physical access.

Download immediatly on otn !

static expression

Using Static Expressions with Conditional Compilation defines the kind of expression you can use with conditional compilation…

I got one question on forums.oracle.com today, an user wanted to use bitand and could not.

I wrote my answer but, … , the site is down and eventually my answer did not get accepted.

So, to translate bitand(x,y)=z, using static expression, when x(
(
(x=0) or
(y=0) or
(x=1 and (y=2 or y=4 or y=6)) or
(x=2 and (y=1 or y=3 or y=5 or y=7)) or
(x=3 and y=4) or
(x=4 and (y=1 or y=2 or y=3)) or
(x=5 and y=2) or
(x=6 and y=1)
) and z=0
) or (
(
(x=1 and (y=1 or y=3 or y=5 or y=7)) or
(x=3 and (y=1 or y=5)) or
(x=5 and (y=1 or y=3)) or
(x=7 and y=1)
) and z=1
) or (
(
(x=2 and (y=2 or y=3 or y=6 or y=7)) or
(x=3 and (y=2 or y=6)) or
(x=6 and (y=2 or y=3)) or
(x=7 and (y=2))
) and z=2
) or (
(
(x=3 and (y=3 or y=7)) or
(x=7 and y=3)
) and z=3
) or (
(
(x=4 and (y=4 or y=5 or y=6 or y=7)) or
(x=5 and (y=4 or y=6)) or
(x=6 and (y=4 or y=5)) or
(x=7 and y=4)
) and z=4
) or (
(
(x=5 and (y=5 or y=7)) or
(x=7 and y=5)
) and z=5
) or (
(
(x=6 and (y=6 or y=7)) or
(x=7 and y=6)
) and z=6
) or (
x=7 and y=7 and z=7
)

quite big, but this is evaluated only once, at compilation time, so it should be an acceptable workaround in some case !

difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);.

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

SQL> set autot trace exp
SQL> delete emp;

14 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |    14 |    98 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> set autot off
SQL> select count(*) from emp;
         0
SQL> roll
Rollback complete.

but explain plan does not

SQL> select count(*) from emp;
        14

SQL> explain plan for delete emp;

Explained.

SQL> select * from table (dbms_xplan.display);  
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |    14 |    98 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
        14

change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change “automatically”

Demo


SQL> CREATE DATABASE
MAXDATAFILES 5
MAXINSTANCES 1
MAXLOGFILES 2
MAXLOGMEMBERS 1
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1;

Database created.

SQL> create tablespace t1;

Tablespace created.

SQL> create tablespace t2;

Tablespace created.

SQL> select count(*) from v$datafile;
  COUNT(*)
----------
         6

datafiles exceeded, but no error!


SQL> alter database add logfile 
  ('/dbms/oracle/LSC75/redo/f1.sql',
  '/dbms/oracle/LSC75/redo/f2.sql')  size 16M;

Database altered.

SQL> select group#, members from v$log;
    GROUP#    MEMBERS
---------- ----------
         1          1
         2          1
         3          2

logfiles and logmembers exceeded, but no error!


SQL> alter database add logfile instance 'I2';   

Database altered.

SQL> alter database add logfile instance 'I2';   

Database altered.

SQL> alter database enable instance 'I2';

Database altered.

SQL> select count(*) from v$INSTANCE_LOG_GROUP;
  COUNT(*)
----------
         2

instances exceeded, but no error!

so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?

select * from test where my_long like ‘%toto%’

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on developpez.com (french forum)


SQL> create table test ( my_long long);

Table created.

SQL> insert into test values ('hello toto !');

1 row created.

SQL> exec for r in ( select my_long from test ) 
   loop if (r.my_long like '%toto%') then 
   dbms_output.put_line(r.my_long); end if; 
   end loop 
hello toto !

oem grid control 10gR2 on Suse10

Last friday I installed RAC on my Suse10. Today, I installed Grid Control.

Of course it is not supported.

The packages I neeeded additionaly are db1 and openmotif.

It did not work 100%. I finally started the iasconsole (emctl start iasconsole), and within the iasconsole, I restarted the failed elements.

I created the repository manually (sysman/admin/emdrep/bin/RepManager)

Suse10 10gR2 Laptop RAC

Hi,
[EDIT]
I did read Dizwell article about Linux 10g Laptop RAC. The article is no longer available
[/EDIT]

I have just installed RAC 10gR2 on Suse10.

The document above guided me thru the steps.

Specific Suse10 :
– raw devices are configured by editing /etc/raw with values like raw1:loop1 and must be activated with rcraw start (+ chkconfig -a raw for persitence)

To take care :
– read point 8 to survive reboot…

Specific 10gR2 :
In 10gR2, MAXINSTANCES must not be specified by create controlfiles. In 10gR2, the controlfile can grow dynamically. Even if I created my controlfile with maxinstances=1, I can do alter database add logfile instance 'RAC2' ; alter database add logfile instance 'RAC2' ; alter database enable instance 'RAC2';, which is simply great! CREATE CONTROLFILE just belongs to the past!

Installation:
I had to run vipca manually as root at the end of the root script of the crs installation. I also removed my existing oracle_homes and rebooted before installing database software again. The VIP interface must be configured in /etc/hosts and DOWN (ifconfig down eth0:2).

Conclusion:
The doc written by Amit Poddar on Howad site is usefull. Enjoy rac’ing on your pocket desk calculator!

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION.

Except SYS, SYSTEM and DBSNMP

To minimize this security problem, I implemented the following strategy on my test system.

1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE
2) alter user SYSTEM account lock;
3a) in 10gR2 :
alter user dbsnmp quota 1T on sysaux;
create role secure_oem_role;
grant advisor, analyze any, analyze any dictionary, create job, create procedure, create session, create table, manage any queue, select any dictionary to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQ” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQADM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_DRS” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_MONITOR” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SERVER_ALERT” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SYSTEM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_WORKLOAD_REPOSITORY” to secure_oem_role;
exec SYS.DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(‘DEQUEUE’, ‘ALERT_QUE’, ‘SECURE_OEM_ROLE’)
revoke EXECUTE ON “SYS”.”DBMS_SERVER_ALERT” from dbsnmp;
revoke EXECUTE ON “SYS”.”DBMS_SYSTEM” from dbsnmp;
revoke UNLIMITED TABLESPACE from dbsnmp;
revoke SELECT ANY DICTIONARY from dbsnmp;
revoke CREATE PROCEDURE from dbsnmp;
revoke CREATE TABLE from dbsnmp;
revoke OEM_MONITOR from dbsnmp;
grant secure_oem_role to dbsnmp;
3b) in other versions, you probably can remove more and grant less, I think only in 10g it is necessary to have “quota”. In my other databases, dbsnmp have 0 segments.

Check what system privileges are potentially dangerous to the system :

select path
from
(
select
grantee,
sys_connect_by_path(privilege, ‘:’)||':’||grantee path
from (select grantee, privilege, 0 role from dba_sys_privs union all select grantee, granted_role, 1 role from dba_role_privs)
connect by privilege=prior grantee
start with role=0
)
where
grantee in (
select username from dba_users
where lock_date is null
and password != ‘EXTERNAL’
and username != ‘SYS’)
or grantee=’PUBLIC’
/
:ADVISOR:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP
:CREATE JOB:SECURE_OEM_ROLE:DBSNMP
:CREATE PROCEDURE:SECURE_OEM_ROLE:DBSNMP
:CREATE SESSION:USER1
:CREATE SESSION:USER2
:CREATE SESSION:SECURE_OEM_ROLE:DBSNMP
:CREATE TABLE:SECURE_OEM_ROLE:DBSNMP
:MANAGE ANY QUEUE:SECURE_OEM_ROLE:DBSNMP
:SELECT ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP

it sounds better…

the forums I use

on otn : forums.oracle.com
1) SQL and PL/SQL
2) Database General
3) iSQL*Plus
4) Documentation Feedback

when otn is down or too slow, I read the metalink forums. Especially SQL*Plus and Oracle PL/SQL. If necessary, I post questions on the DBA Administration forum.

to post questions/comments specific to security, I go to Pete Finnigan’s Oracle Security Forum.

Sometimes I post comments on the asktom site. About leap years, number to octal, number to words…

Occasionaly, I visit the Dizwell Forum, just to grab some interesting post…

When it is nice weather, I go to usenet comp.databases.oracle.server

And of course, I use google a lot to find new places to go !

idle events in 10gR2

I just noticed this morning that idle events are very easily identifiable by a new column called wait_class in 10gR2

To ignore idle event, I just wrote


select WAIT_CLASS, event
from (
  select * 
  from V$SYSTEM_EVENT 
  where WAIT_CLASS#!=6
  order by TIME_WAITED_MICRO desc)
where rownum<6 ;

WAIT_CLASS    EVENT
------------- ----------------------------------------
System I/O    log file parallel write                  
Configuration log file switch (checkpoint incomplete)
Configuration log file switch completion             
System I/O    db file parallel write                 
System I/O    control file parallel write