Laurent Schneider

There a few major changes in the database administration and the database security between 9i and 10g.

In 9i, I used to grep in the listener.ora to find out the password.

LISTENER_LSC61 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200))
))
PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF

this 64bit encrypted string can be used in 9i to stop the listener

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.6.0 – Production on 05-DEC-2005 14:33:51

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc61
Current Listener is listener_lsc61
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)))
The command completed successfully

As a dba, it is quite handy, because you can use grep (or awk) to find out the password out of the listener.ora. As a security admin, you should make sure the listener.ora is not readable. Note that the default, when created by netmgr, is to be world-readable 🙁

However, this does no longer work in 10g

LISTENER_LSC62 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC62 = 1234567890ABCDEF

the encrypted string can no longer be used

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 – Production on 05-DEC-2005 14:37:24

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc62
Current Listener is listener_lsc62
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200)))
TNS-01169: The listener has not recognized the password
TNS-01189: The listener could not authenticate the user

As a security admin, you would think it is better so. But, how are you going to stop the listener in your script? Well, in 10g, we can use local authentification (default). So if the script is started as oracle, we would not need to use password

LISTENER_LSC63 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC63 = 1234567890ABCDEF

$ whoami
oracle
$ hostname
dbsrv85a.ex.zkb.ch
$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 – Production on 05-DEC-2005 14:43:33

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener LISTENER_LSC63
Current Listener is LISTENER_LSC63
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200)))
The command completed successfully

I read in an Alex Kornbrust post on Pete Finnigan forum, that a LOCAL_OS_AUTHENTICATION “undocumented” parameter could be used to “avoid” local authentication, but in that case, it is going to be a nightmare to “stop” the listener in an automated script, well, we can still use “kill”, but it is not very beautifoul.

It is monday, I am going to give a list of settings I like in sqlplus

set lin 32767 trimsp on tab off
extends the linesize and avoid line breaks. I use it before SPOoling. But it is annoying before DESCribing. Trimspool is necessary to avoid spaces at the end of the line. Set tab off makes sure sqlplus does not use “tab” for formatting, but spaces.

set emb on pages 0 newp none
this avoid page breaks. there is one header in the top, than no more, and no ^L. newp none is not working in version 7, there you must use newp 0.

set head on
set head off
show or hide column headers

set feedb 6
set feedb off
report result of query, set feedb 6 do not give feedback if a select returned 1 to 5 lines, because it is too easy to count… Set feedback off removes feedback

set ver off
I am never interrested in the translation of my defined variables

set termout on
set termout off
Off avoids screen output. Warning, this does not avoid spool output. Works only in scripts, not in command mode. Note that a command piped thru sqlplus is still a command more.

set echo on
set echo off
Display executed command. Works only in scripts, not in command mode.

sqlplus / <<EOF
set echo on
set termout off
select * from dual;
EOF
the echo on and termout off will have no effect, because it is not a sql script (called with @).

def _editor=vi
set editf /tmp/lscfile.sql
Use vi (instead of ed) as editor, and use a file in /tmp (instead of afiedt.buf in working directory) as temp file

set long 1000000000 longc 60000
do not truncate longs nor long chunks. Very usefull with clob in sqlplus.

set serverout on size 1000000
set serverout on size unlimited
allows dbms_output to print to current terminal. Unlimited is a 10gR2 enhancement

set sqlp “_USER @ _CONNECT_IDENTIFIER> ”
change the prompt to contain a dynamic user and connection string.

thursday I am having dinner with tom kyte, drop me a comment there if you want to come

I just wrote those two functions

add_years and years_between

they work similary to add_months and months_between, with the exception of leap years.

there is exactly 1 year between 28-feb-2003 and 28-feb-2004
there is 1.00273224 year (1+1/366) between 28-feb-2003 and 29-feb-2004
there is 0.99726776 year (1-1/366) between 29-feb-2004 and 28-feb-2005
there is exactly 1 year between 29-feb-2004 and 01-mar-2005

ok, here it is:

create or replace function add_years( d1 date, n number) return date is
d2 date;
begin
if ( n=0) then
return d1;
end if;
if ( d1 is null or n is null)
then
return null;
end if;
if ( to_char( d1, ‘MMDD’)=’0229′) then
if ( mod( n,1)=0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’);
elsif ( n>0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)+1,’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’)-
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’));
else
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’)-
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)-1,’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’));
end if;
else
if ( mod( n,1)=0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’);
elsif ( n>0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)+1,’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’) –
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’));
else
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’) –
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)-1,’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’));
end if;
end if;
return d2;
end;
/

create or replace function years_between( d1 date, d2 date) return number is
n number;
begin
if ( d1=d2) then
return 0;
end if;
if ( d1 is null or d2 is null) then
return null;
end if;
n:=trunc( ( to_char( d2,’SYYYYMMDDHH24MISS’)-to_char( d1, ‘SYYYYMMDDHH24MISS’))/10000000000);
if ( to_char( d1, ‘MMDD’)=’0229′) then
if ( d1<d2) then
if ( to_char( to_date( ( to_char( d1,’SYYYY’)+n)|| ‘060’,’SYYYYDDD’),’MMDD’)=’0229′)
then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’) +n)||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/366;
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/365;
end if;
else
if ( to_char( to_date( ( to_char( d1,’SYYYY’)+n-1)|| ‘060’,’SYYYYDDD’),’MMDD’)=’0229′)
then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’) +n)||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/366;
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/365;
end if;
end if;
else
if ( d1<d2) then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’))/
( to_date( ( to_char( d1,’SYYYY’)+n+1)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’) –
to_date( ( to_char( d1,’SYYYY’)+n)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’));
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’))/
( to_date( ( to_char( d1,’SYYYY’)+n)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’) –
to_date( ( to_char( d1,’SYYYY’)+n-1)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’));
end if;
end if;
return n;
end;
/

the logic is : if you are born 29-february, you will get your birthday the 60th day of the year. Otherwise, you will get your birthday on the same date as when you were born. The rest is to calculate fraction of year, and it “should” work with negatives, too.

click here

sorry, once again, I used the back button of my browser, when I was not supposed too…

Next wednesday, Dec 7th, starts the Tom Kyte workshop in Zurich.

In the evening we will have a dinner in Zurich.

Drop me a comment if you intend to come !

the first time I saw ansi literals was in June 2004 in a post on otn forum by alex goodmann. I just cannot stop using them, it is so handy!

Whenever I specify a date (or a timestamp or even a time), with Ansi Date, I do not rely on the NLS parameters, nor I do specify a format.

I simply use

date '2000-01-01'

I often use Jan 1st, 2000 as an anonymous date.
For avg(txndate), I can use

date '2000-01-01' +
avg(txndate-date '2000-01-01')

Other nice literals are timestamps

timestamp 
'2000-01-01 00:00:00.000000000 Etc/GMT+0'
timestamp '2000-01-01 00:00:00'

less usefull, because unsupported as oracle datatype

time '00:00:00.000000000 +00:00'
time '00:00:00'

also intervals

interval '1' day

one more I want to mention

q'[let's quote this]'

all this I found by reading the doc, more than once !

I will attend Tom Kyte 3-days session in Zurich in 2 weeks. I am immensely impatient to follow his workshop !

I hope palindnilap will be there too 😉 I invited him to come to my home to go to Tom Workshop, and to have a game of xiangqi on the brand new wooden board I received yesterday.

I just answered a question about the data dictionary on forums.oracle.com. It is all about the dictionary views.
How many tables should I know in the dictionary ?

SQL> select count(*) from dict;
1857

Well, that’s too much. But I can remove the GV$ view, which contain the instance for RAC, and the DBA_ ALL_ and USER_ have (almost) the same structure.

SQL> select count(*)
from dict
where table_name not like ‘GV$%’
and table_name not like ‘ALL%’
and table_name not like ‘DBA%’ ;
712

Anyway, who knows the 712 views by heart? Hopefully, there is one called DICTIONARY, which helps !

Coming back to the post, palindnilap wants to see which columns of a view are mapped to which column of a table. A quick look at ALL_VIEWS could do the trick, but than you will need to “understand” the query to see which view.column maps to which table.column. What’s more, ALL_VIEWS.TEXT is a long. Arghh!

if you have a view that contains all columns from a table, you could use ALL_DEPENDENCIES to see on which table it is based.

On my first answer, I pointed out that ALL_UPDATABLE_COLUMNS may reveal that a view column belongs to a table if the column is updatable.

My last try was to use the ACCESS_PREDICATES to get the column physically accessed.

SQL> select * from v02 where employee=123456;

no rows selected

SQL> select
max(substr(ACCESS_PREDICATES,1,instr(ACCESS_PREDICATES,’=’)-1))
from v$sql_plan
where ACCESS_PREDICATES like ‘%=123456’;
“EMPNO”

here we see EMPLOYEE is actually named “EMPNO” in the based table. It could be done with explain plan and PLAN_TABLE too.

Tom Kyte is coming to Zurich next month!
http://www.digicomp.ch/tomkyte

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant :

revoke CREATE DATABASE LINK from CONNECT;
revoke ALL on SYS.UTL_FILE from PUBLIC;
revoke ALL on SYS.UTL_HTTP from PUBLIC;
revoke ALL on SYS.UTL_SMTP from PUBLIC;
revoke ALL on SYS.UTL_TCP from PUBLIC;
grant EXECUTE on SYS.UTL_FILE to XDB;
grant EXECUTE on SYS.UTL_HTTP to MDSYS;
grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS;
@?/rdbms/admin/utlrp

Than, in OEM 10g, check for policy violations.

I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired

I just found in my RSS feed that a new agent has been released. 10.1.0.4 for AIX5L. Well, if you go to the OEM download page, you will see only 10.1.0.2 version for AIX, HPUX, Solaris Sparc. But by clicking on the link, for example
Grid Control (10.1.0.2)
for AIX5L Based Systems, you will find a link OEM 10g Grid Control Management Agent Release 1, from there I finally discovered that 10.1.0.4 agent download.

Well, I already had 10.1.0.4, but it was a 10.1.0.2 installation patched with 10.1.0.4. So I prefer installing directly 10.1.0.4 agent.

Not trivial to found, but hopefully, I subscribed to RSS feeds on Newest Downloads

In order to get a clean database configuration, I add the following two hidden parameters in my 10.2.0.1 parameter file.
Do not hurl that loud, I hear you from here!

Well, as I said already about the _pga_max_size, I never recommend using hidden parameters when you can do the same without.

However, I am going to communicate those parameters, and the metalink notes referencing them.
You will need them to have a cleaner 10.2.0.1 installation

_kgl_large_heap_warning_threshold=33554432
This parameter prevent Heap size 2800K exceeds notification threshold errors in the alert log and user trace dumps.
Note: 330239.1 Bugs: 4286095, 4390265

__dg_broker_service_names=”
In case you do not use dataguard but you do use local_listener parameter.
this parameter prevents pmon from registering a <DB_NAME>_XPT.<DOMAIN_NAME> service in the listener.
Thread: 611575.993 Bug: 4632635

Probably all this will be fixed in 10.2.0.2

with netca, it is easy to configure your sqlnet.ora to use LDAP instead of tnsnames.ora. The ldap.ora and sqlnet.ora are updated… than it works, sqlplus user@db is correctly looking in the ldap oracle content

what can I do with LDAP?
what is the difference between LDAP and Oracle Internet Directory?

Well, there is quite a lot of interresting documents, pictures and faq on otn :

OTN Directory homepage

Directory Admin guide

Identity Management ReferenceLDAP is a directory server, the info are stored in an Oracle 10.1.0.4.2 database.

When you download application server (about 2Gb), you get a fully functionnal 10.1.0.4.2 database preconfigured and an ldap server running.

To start the admin tool, just type oidadmin in the command line.

The password to use is the same as ias_admin and the username is orcladmin

My ldap server is up and running on my notebook with SLES9.

Next, next, next, install. That is it.

Oracle Application Server creates a 10.1.0.4.2 database and start the Oracle Internet Directory – understand LDAP server – automatically.

It can then be configured with the web interface.

Well, when 10g certification came out, it said : “will be available in late 2004”. In March 2005, I contacted Oracle University in Germany. They said : keep watching oracle.com/education. Later, it said : “will be available in mid 2005”.
In Open World, I met the certification principal, who meant it will start in December and upgrade from 9i ocm will start in January and will be available in all oracle universities which give RAC course. Fine.
When I came back I saw: “will be available in October”. Today I saw: “will be available in December”.

Such a move like OCM is very intense, I want to plan at least 2-3 weeks full time to prepare for OCM. I need to go to my boss and say : “this year I will go for 10g ocm”. But what a pity if the certification is always delayed… planned for 2004, no info why it did not take place in 2005, I also fair that there is no guarantee for 2006 in switzerland, as they will surely start in the United States – but do not ask why…

Keep cool, be patient 😉

In case you like this game and live in Zurich, I will be playing the swiss championship tomorrow.

what is a hierarchy?
I enjoy reading the wikipedia definition :

http://en.wikipedia.org/wiki/Hierarchy

In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship.

However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent.

This is no longer a practical relation.

Sterile variant :

SQL> select * from dept connect by 1=2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


Fertile variant :

SQL> select * from dept connect by 1=1

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
...


the connect by does defines a true or a false connection. when true, everyone is your parent and everyone is your child. If false, you are the parent, but you have no child.

connect by level

I just want to clarify something today : I do not like the connect by level<5 or connect by 1=1 where rownum, etc... I would prefer to receive ORA-01436: CONNECT BY loop in user data when trying such a hack. Nowadays, Tom Kyte uses this method in all his demonstrations. Which makes the users confident of using it. Still I do not feel this method to be safe. It just seems too much abstract to me. Also, it seems it could burn a lot of cpu time, depending on how the optimizer evaluate it. Let's try a few examples
SQL> select * from dual connect by level<3; D - X X
it works

SQL> select * from dual connect by level<3 and dummy=prior dummy; select * from dual connect by level<3 and dummy=prior dummy * ERROR at line 1: ORA-01436: CONNECT BY loop in user data
it fails. Can you tell me why? the first example is also doing a virtually infinite loop.

Oracle Enterprise Manager Downloads: oem download

this is a great announcement!

one fresh download today oracle database 10g download

I heard that grid control 10g release 2 may be available only in 2006. Quite annoying, since the repository cannot be on a 10gR2 db…

just waiting

Great challenge today: restore to a new host from a closed noarchivelog backup on tape library.

In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because even the MAXDATAFILES and MAXINSTANCES could be changed. Change controlfile is bad. Once a collegue forget one file, once he noticed it, only months later, he wondered how to recover it. Create a controlfile is way to much sensible thing too do. It is almost as bad as changing the dictionary!

Well. How to do than?

Ok, I have a database called LSC67 on prod, I want to recover it to LSC66 in devl. I have NO ACCESS to production, but I have access to tapes (one may wonder if this is a good security practice…)

Let’s start.

First, if LSC66 already exists, shutdown abort. Remove datafiles. Remove controlfiles. Remove redo logs.

Now I restore the data/control/redo files from prod:/dbms/oracle/LSC67 to devl in /dbms/oracle/LSC66.

First I rename the files, some are called systemLSC67.dbf. I do not want that…

find do the trick

find /dbms/oracle/LSC66 -name “*LSC67*” |
nawk ‘{printf “mv “$1” “; gsub(src,target);print}’ src=LSC67 target=LSC66 |
sh

I must just change the DB_NAME in my parameter file (which already exists at my site), to reflect the prod controlfile dbname

startup quiet force nomount
alter system set db_name=’LSC66′ scope=spfile;
startup quiet force mount

now I generate some statements for dynamically renaming the files

set ver off emb on pages 0 newp 0 lin 9999 trims on head off feedb off termout off
spool /tmp/rename_LSC67_to_LSC66.sql
select ‘alter database rename file ”’||name||”’ to ”’||replace(name,’LSC67′,’LSC66′)||”’;’
from (
select name from v$datafile
union all
select member from v$logfile
)
where name like ‘%LSC67%’;
spool off
spool /tmp/drop_temp_LSC67_to_LSC66.sql
select ‘alter database tempfile ”’||tf.name||”’ drop;’
from v$tempfile tf
where tf.name like ‘%LSC67%’;
spool off
spool /tmp/create_temp_LSC67_to_LSC66.sql
select ‘alter tablespace “‘||ts.name||'” add tempfile ”’||
replace(tf.name,’LSC67′,’LSC66′)||
”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
from v$tablespace ts , v$tempfile tf
where tf.name like ‘%LSC67%’ and tf.ts#=ts.ts#;
spool off

ok, now I am in mount, I do a rename datafile and drop tempfile. after I open database, and add tempfile. I am not taking care of the original size and autoextend clause of the original tempfiles, just 128M next 128M max 2G.

set echo on termout on feedb 6
@/tmp/rename_LSC67_to_LSC66.sql
@/tmp/drop_temp_LSC67_to_LSC66.sql
alter database open;
@/tmp/create_temp_LSC67_to_LSC66.sql

now I nid

shutdown immediate
startup quiet mount restrict

nid dbname=LSC66 target=/

and I change the db name and open resetlogs

startup quiet force nomount
alter system set db_name=’LSC66′ scope=spfile;
startup quiet force mount
alter database open resetlogs;

I just remembered the woman who asked Lawrence Ellison why you need at least three days to get a qualified support representative when you open an iTar.

Since a few days, I have been wondering why a non-dba user was not able to do sqlplus in 10gR2. Well, I saw the bug 4516865 on metalink for 9.2.0.7 and 10.2.0.1, but did not agree that it is a “desirable improvement”, imho it is a major lost of service.

Just a few minutes (seconds?) after I finished writing my iTar, severity 2, I received a call from Oracle Support.

Apparently a group of customers asked to prevent any non-dba user from using sqlplus on a database server (just on the client). This kind of answer I do not like. But we had a long talk, he finally showed me a workaround for 10.2.0.1 : doing

chmod -R 755 $ORACLE_HOME

before root.sh.

Well, this should have made me hurl. But ok, I can do this.

He also explained me, that I should escalate this bug if I want development starting working on it. Also there is another bug, 4533592

My satisfaction with this tar is good (4/5). I have been contacted immediatly, informed about the bugs, and informed about the procedure to escalate this case when I will require too.

My overall satisfaction with Metalink is about 3/5. But sometimes, it is really 0/5. The worst I have was :

lsc: how do you remove headers in the middle of your result in sqlplus ? I have a bug with set emb on and set pages 9999.
metalink: This is a bug in sqlplus. Workaround is to use Oracle Reports.

I could just have kill her! The “good” solution is “set pages 0”, ref: set pages 50000

One also like this :

lsc: When I try to do an executable in scheduler I got an ora-27371 (AIX)
metalink: switch to a new platfom
lsc: @*#!!
metalink: It will be fixed in 10.1.0.4
lsc: no it is not fixed in 10.1.0.4
metalink: it is an internal bug
…months later…
lsc: still not fix in 10.2.0.1
metalink: development is working on it

well, I have always known that AIX is not a strategic platform and many shell scripts function utterly wrong… still very frustrating one!

Larry just told that woman : just drop me an email. Should I write Larry each time I do receive an idiot answer?

Ref: part 1
I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359

Here is my test case (take care, it will create a new db!) :

SQL> startup force quiet nomount;
ORACLE instance started.
SQL> create database controlfile reuse extent management
local default tablespace users default temporary tablespace temp
undo tablespace undotbs1;

Database created.
SQL> @?/rdbms/admin/catalog
SQL> @?/rdbms/admin/catproc
SQL> col username for a10
SQL> col PROFILE for a7
SQL> col LIMIT for a12
SQL> select username, profile, limit from dba_users join 
dba_profiles using (profile) 
where resource_name='FAILED_LOGIN_ATTEMPTS';
USERNAME   PROFILE LIMIT 
---------- ------- ------------ 
SYSTEM     DEFAULT 10
SYS        DEFAULT 10
TSMSYS     DEFAULT 10
DIP        DEFAULT 10
DBSNMP     DEFAULT 10
OUTLN      DEFAULT 10

I just noticed FAILED_LOGIN_ATTEMPTS now defaults to 10 in 10gR2. Not found in the doc. I will report it tomorrow in the documentation feedback on otn

one more try with model, available 10gR1

select * from 
  (select extract(year from hiredate) h, count(*) c 
    from emp 
    group by extract(year from hiredate))
model
  dimension by (h)
  measures (c)
  rules(
    c[FOR h FROM 1980 to 1990 INCREMENT 1] = 
    case when c[CV()] is present then c[CV()] else 0 end)
order by h;

         H          C
---------- ----------
      1980          1
      1981         10
      1982          1
      1983          0
      1984          0
      1985          0
      1986          0
      1987          2
      1988          0
      1989          0
      1990          0

pivot table part 1
pivot table part 2

One more try with 10gR2

select to_number(column_value) HIREDATE, count(decode(to_number(extract(year from hiredate)), to_number(column_value), 1)) COUNT
from
emp,xmltable(‘for $i in 1980 to 1990 return $i’ )
group by to_number(column_value)
order by to_number(column_value)
/
1980 1
1981 10
1982 1
1983 0
1984 0
1985 0
1986 0
1987 2
1988 0
1989 0
1990 0

pivot table part 1
pivot table part 3

Very intense last day. In the morning I waked up late, and just went to OTN lounge. Well, I meet Puschitz, which speaks german too because he is austrian, and also Wim. I then ran to Moscone South for my XQuery session. Very interresting indeed…

In the afternoon, pure DBA staff :
1) shared memory
very interresting, difficult staff
2) dss
a lot of nice improvement, but highly soporific presentation
3) storage
great great great presentation from Amit Ganesh, Storage Director at oracle

Than I had a talk with Joel and Arup and a glass of wine in the garden. Than I came in the hotel, I have been kindly invited for a glass of red wine by Barbara and Lisa, two lawyers residing also in the argent hotels, thanks if you read me!

Well, I had to pack, reserve my shuttle, organise wake-up service for 05:00am

What a queue for Tom Kyte session! 1000 persons have attended, and apparently a lot more had to wait outside. Very interresting. I am especially seduced by the Online Transportable Tablespace, it was SO BAD to make the tablespaces readonly before transport. I got my signed copy of his book 🙂
In the afternoon, I was too much dead to assist some session, so I visited the exhibitors and will come home with plenty of gifts for Dora and Loïc – what a pain to type the ï on an american keyboard, but alt-139 does the trick.
Than OTN/lounge, with a dozen of fans we had the opportunity to ask questions to tom.
On the evening, we had the blogger dinner. I particullary appreciated! Even if I have a terrible french accent, I have been able to chat with my neighbours. The food was good, apart from the sorbet, but the way I pronounced “sorbet” was apparently very amusing!

I remembered the time in 9iR2 when load/balancing was just dicing between the instance. In 10gR2, I noticed that load balancing is much better. I did a test, start 28 sessions from an external client, 12 went to node 1, 12 to node 2. Very impressive.

In 10gR2 RAC, the listener gets statistics collected by MMON, and balances load much more efficiently…

The Failover is also working correctly. I just powered off Doug PC (we work in team in this lab), and after second try it worked. The TNSNAMES must be configured correctly, with FAILOVER and virtual ip.

I also tried to install RAC/Linux/vmware on my notebook, but I stupidely remove the linux grub boot partition and I cannot boot anymore… I need a doctor soon 🙂

This first RAC day was intense! My neighbour and I just installed clusterware, configure iscsi to use LUN in SUSE and started ASM and a clustered Database!

all doc are on
http://www.x-treme-lab.com