Laurent Schneider

I recently posted about Oracle Password Repository (OPR).

I did get a comment from Andreas Piesk about something similar in Oracle, the wallet.

Let’s do a quick test.

First, I create a .sqlnet.ora in my home directory (I do not want to mess up the system-wide sqlnet.ora).


$ cat /home/lsc/.sqlnet.ora
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/lsc)))


I now create the wallet


$ mkstore -create -wrl /home/lsc
Enter password:
Enter password again:


and the credentials

$ mkstore -wrl /home/lsc -createCredential LSC01 scott tiger
Enter password:
Create credential oracle.security.client.connect_string1


now I try to login

$ sqlplus /@LSC01

SQL> sho user
USER is "SCOTT"


This sounds to work very nicely. Let’s see if this is more secure than OPR :

$ mkstore -wrl /home/lsc -list
Enter password:

Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.connect_string1
Enter password:
oracle.security.client.connect_string1 = LSC01
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.username1
Enter password:
oracle.security.client.username1 = scott
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.password1
Enter password:
oracle.security.client.password1 = tiger


Definitely! The password is not reveal, unless you know the password of the wallet. Remember in OPR, the application had direct access to the password. Here it is not the case, if you do not know the password of the wallet, you may login, but you cannot find out what the password is. I like this very much.

of course do not forget to protect your wallet

Ok, what I did not achieve until yet is logging in externally when using wallet_override :

$ mv /home/lsc/.sqlnet.ora /home/lsc/.sqlnet.ora.disable
$ sqlplus /

ERROR:
ORA-01017: invalid username/password; logon denied


I will update this if I find out how to login externally too.

I just read about a query to retrieve last modification row of a date


SQL> SELECT ora_rowscn FROM tab_test;

6 rows selected.


conclusion of the author : very neat to retrieve last modification date.

Well, I am worried. I have answered so many times on the technical forums here for example, that the only way to retrieve the last rows is to use a date column, and to manually update it (or with a trigger or a default value for insert) with the last modification date.

What should I do?
test it!


14:52:07 SQL> create table t as
select rownum x from all_objects;

let’s update one row


14:54:12 SQL> update t set x=-1 where rownum=1;

what? I updated one row, why did it updated so many rows? Let’s look at the block

14:58:16 SQL> select dbms_rowid.rowid_block_number(rowid) block_number,scn_to_timestamp(ora_rowscn) timestamp,count(*) from t group by dbms_rowid.rowid_block_number(rowid),scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);

BLOCK_NUMBER TIMESTAMP COUNT(*)
------------ ---------------------- ----------
651 04.08.2006 14:56:23.00 658
652 04.08.2006 14:56:23.00 658
653 04.08.2006 14:56:23.00 658
654 04.08.2006 14:56:23.00 658
655 04.08.2006 14:56:23.00 658
656 04.08.2006 14:56:23.00 290
650 04.08.2006 14:57:14.00 658 <=====================

ok, what has the doc to say about this :

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking

Well, let's try again with row dependencies.


15:04:53 SQL> drop table t;

TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4240
04.08.2006 15:06:08.00 1


sounds better! probably not 100% reliable but way better, it seems almost usable ...

I just registered to Oracle OpenWorld in October in San Francisco… Well, I know it is a long long long way to the west, but I will survive again, but this time I will avoid Delta Airlines, they refused to serve me a glass of wine last year in the plane! I am going to take maybe Lufthansa.

As an ACE, I got the Oracle Develop for free this year too. I also took the gold pass, well, waiting hours for keynotes is just too much boring. Since it is the only thing I pay myself (ok wine in the plane too), I guess this 150$ investment is ok.

If available, I will try the Oracle Application Server OCP Beta exam 1Z1-312 in SF. Also, I will ask them why this OCM 10g upgrade is still not available.

I read forums.oracle.com frequently. I have read and answered thousands of questions there.

Those days, they launched a new release, I do not know what the improvements are, but it is fairly unstable. Well, it has been worst in the past, but I keep getting We’re sorry, the server encountered an unexpected condition and timetouts occasionaly.

Another nightmare for users was the blank-trimming effect, it was hardly possible to format the post, all multiple spaces were trimmed to one space, even by using [code] or [pre]. It is now fixed!

There has been also two new icons, Usefull answer and Correct answer, but it has been removed already. It makes me remembers the smilies they introduced in a previous release, where :p bind variable was translated to a smile…

Problem with the downtime is that the experts go away. I post and read hundreds of messages on metalink, especially in sqlplus and plsql forums. The problem on metalink, lot’s of users expect to have an immediate and advanced answer from Oracle, because they pay for support, and they do not invest time to write the question properly nor to say please or thank you.

I recently start writing on the linux and oracle forums of developpez.net, where they put a high value on proper formatting, code of conduct, correct spelling (in french).

oas-console
Originally uploaded by laurentschneider.

I installed OAS 10.1.3 on my notebook. Why? well, just to test if it is installable… and to justify my need of 2Gb of memory to my boss 😉

Ok, SLES10 is not certified yet, Not even planned yet on metalink. But it will definitely be certified one day.

The first step is to install the necessary packages

1) db1 : Berkeley DB Database Library Version 1.85
this package is needed, in order to avoid :

httpd: error while loading shared libraries: libdb.so.2: 
cannot open shared object file: No such file or directory

2) openmotif21-libs, sysstat
as listed in the quick installation guide for SLES9

3) libgnome and libgnome-devel
replace the old gnome-libs and gnome-libs-devel

4) pdksh
ksh-93r seems to be suffisant

5) glibc 2.3 and gcc 3.3
no problem until yet by using glibc 2.4 and gcc 4.1

The second and last step is to launch the installer, with -ignoreSysPrereqs or with an updated install/oraparam.ini

That’s all. I did get some warning about memory and requirements, but it seems that it works.

Novell SLES10 Oracle Database
Originally uploaded by laurentschneider.

The orarun package to be installed in SLES10

I checked this tool today :
http://sourceforge.net/projects/opr

This tool provide a simple way of not hardcoding passwords in shell scripts. Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc… The passwords may change often too.

This tool uses a simple file to stores the passwords encrypted. Well, I urge you to secure this file to make it non-accessible for others.

I am not going to decode nor rate the encryption algorythm, the fact is, you do not have clear text passwords. It could be also possible to have clear text password in a separate text file, which would not be that less secure, but it is ugly.

To make it clear (I hope), it is a 2-ways encryption, so if you have access to the file and the source code of the algorythm, you can crack the password. Sounds weak? Well, Oracle Proxy Users with Internet Directory, Application Server, Portal and all those products are not much different. The only 100% passwordless solution I am aware of is the external OS identification.

Well, that said, let’s look how it works.

# ./configure
# make
# make install

let’s create the repository

 
$ export OPRREPOS=$ORACLE_HOME/dbs/oprrepos
$ opr -c

store the password for scott on LSC01

$ opr -a LSC01 SCOTT lsc
please enter the password :
please re-enter the password :
entry (LSC01, scott, lsc) added.

let’s test

$ sqlplus scott/$(opr -r LSC01 SCOTT)@LSC01

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jul 25 13:51:48 2006

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


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

This is quite convenient. I will use this to store the RMAN password to connect to the RMAN repository in my backup scripts

How neat is Suse!

I downloaded the DVD SLES10 on Novell.com. At the installation time, appart from Gnome, Development, X Window, there are a few new categories. One of them is called Oracle Database, and guess what, it installs all what I needed for Oracle. It creates an oracle account, with oinstall as primary group and dba as secondary group, it sets usefull things like ORACLE_HOME.

Ok, let’s unlock the account

chltlxlsc1:~ # usermod -s /bin/bash oracle
chltlxlsc1:~ # passwd oracle
Changing password for oracle.
New Password:
Reenter New Password:
Password changed.

Ok, let’s install. SLES10 is not recognized as a certified OS yet. The DISPLAY thing has to be manually set too.

oracle@chltlxlsc1:~> ./runInstaller  -ignoreSysPrereqs
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, 
redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
                                      Failed <<<<


>>> Ignoring required pre-requisite failures. Continuing...

this is one of the friendliest installation I have ever made…

I successfully applied patch 5225799 on my notebook

SQL> select action,version,id,comments from dba_registry_history;
ACTION VERSION ID COMMENTS
------ ---------- -------- ----------
CPU 10.2.0.2.0 5225799 CPUJul2006


I am aware indian and pakistan ISP did block access to blogspot blogs. There are some workaround available on google, for example to access my site from Pakistan, you may be able to use pkblogs

http://www.pkblogs.com/laurentschneider

I am quite sad about this, I used to have a lot of access from India in the past (according to statcounter.com), and this is quite a limiting move for the indian Internet community.

Update: only relevant to my old blogger blog

I will be playing the The Geneva Bughouse Gathering 2006 tournament on Sat 19th August 2006. It is a variant of chess, very wild, played with four players in teams of two. The photo on my profile has been taken in the 2005 edition.

at least we got one record in the world cup 2006. We are the first team in world cup history that got eliminated without receiving one goal 🙂

Download of the pre-release version of Suse Linux Entreprise 10 is now available
http://www.novell.com/linux/prerelease.html?sourceid=prerelease1

I just received my Oracle Magazine yesterday. Blair Campbell from Oracle published my interview in the peer-to-peer columns.

Go to otn.oracle.com/oramag

Last month I had an application server seminar I blogged in SOA . Yesterday it was database 10g day.

First we had exactly the same talk about EMEA marketing, vision, partner and blabla for one hour. I did not expect to hear this comparison between Oracle Database and the C: prompt (origin) and fusion and mswin (ultimative achievement)… I almost told him than the C: prompt was not the first think to appear in MSDOS, but the A: prompt, well, if I will say it next time 😉

We heared about partitioning, parallel query, real application cluster, flashback database, undo management, in about 250 powerpoint slides and not a single line of sql in the whole presentation. Well, not willing to blame the teacher, I just felt losing my time listening to this superficial presentation of oracle database.

About the things I found interesting :
– Using the listener as http server. Especially interesting is to use lsnrctl star to start the listener (just joking).

listener.ora
HTTP= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)
(HOST=chltlxlsc1) (PORT=8080)) (presentation=http)
(session=raw))

TNSLSNR for Linux: Version 10.2.0.2.0 - Production
System parameter file is .../network/admin/listener.ora
Log messages written to .../network/log/http.log
Listening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)
(HOST=chltlxlsc1.lcsys.ch) (PORT=8080))
(presentation=http) (session=raw))


than I should be able with the package DBMS_EPG to administer the website

– An Excel plugin : well, if your marketing department is hooked on excel, there is a plugin which enable you some connectivity within your spreadsheet. You can download it on otn for free on BI Spreadsheet Addin. I did not try it yet, but it did look sexy.

– As last month, the pause has been enjoyable. Fine starters, great cat fish, delicious dessert

Germany we are coming
Originally uploaded by laurentschneider.

this cow is funny, it is quite exceptional for Switzerland to play in the World Cup !

EXP / IMP has a quite a lot of bugs and limitations.

Today I had a trouble with a function based index first, generating ORA-942 table does not exist on import. I also had an error with AQ.

I have a few invalid objects too :

before exp, on source system (tru64/9.2.0.4)
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
54


after imp, on target system (rh4/10.2.0.2)
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
142240


let’s recompile
SQL> @?/rdbms/admin/utlrp

COUNT(*)
----------
2699


… better, but still too much !

I probably did hit bug 3169196 too. The workaround is delicious :

manually clean the data dictionary

Well, I am going to drop the destination database and try again…

oracle 7 bin
Originally uploaded by laurentschneider.

end of life of oracle 7 reference, cd and documentation

Today we definitely shutdown our last productive Oracle7 database. Quite good news actually, I can now use set newp none, sqlplus “/ as sysdba” in all my scripts, svrmgrl is no longer needed (well, sometimes in 8i to do abort, but this is rare), and we garbage a hudge bin of reference manuals and cds!

And it is a good atmosphere, we talk about the good old time and this long life with no support and no change.

We have about 20% Oracle8i, 70% Oracle9i and 10% Oracle10g right now.

mod_plsql is an oracle module for Apache. Let’s do it from A to Z.

I install HTTP server. In 10g, it is located on the companion cd and requires a separate oracle home.

I copy my tnsnames.ora in COMPANIONHOME/network/admin

Then, I configure COMPANIONHOME/Apache/modplsql/conf/dads.conf

<Location /helloworld>
SetHandler pls_handler
PlsqlDatabaseUsername scott
PlsqlDatabasePassword tiger
PlsqlDatabaseConnectString LSC01
</Location>


I create my procedure
create or replace procedure helloworld is begin htp.bold('hello world'); end;

I start my Apache server
$ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server

I open my browser
firefox http://localhost:7780/helloworld/scott.helloworld

So easy!

Michel Hascoët
Originally uploaded by laurentschneider.

Michel Hascoët is Principal Consultant in Oracle France and gives workshop for SOA integration

Dessert Buffet at Hilton Hotel during the seminar
Originally uploaded by laurentschneider.

Taking energy for the second part of the seminar

The seminar yesterday focused on SOA, Service Oriented Architecture. Teacher Michel Hascoët gave us an impressive demo with his notebook and JDeveloper; the SOA Workshop is usually taking four days, and for us he did it in one day.

Ok, first ADF. With JDeveloper, he created with just a few clicks a connection to a database, dropped two screens, one for browsing customers, one for editing a customer, and just by clicking “run”, the application started in a browser.

Later, we saw the BPEL process manager in JDeveloper. I have been quite impressed. Well, I am a vi man after all, and I am typically quite sceptical about click-click-click and it works. What is really sexy in the BPEL approach is that the application is built in a natural way. You just define process, like you would do “if (y==2)”, or “x=1” in vi, but in a good-looking fashion. At the end, the application is self-documented, and this is a real bonus, because the next developer in task for your project will understand your work immediatly.

As Michel pointed out, the key point to success is the data format. If you can exchange Data from one application to another, than building a new process is just a matter of a few clicks.

In the afternoon, we had a look at BAM, the Oracle Business Activity Monitor. This Windows tool (needs a windows server and an internet explorer client) let you build graphs and send alerts according to rules and sensors you can define in JDeveloper. Michel believes this tool will be rewritten by Oracle in a near future to comply with the OS strategy of Oracle, understand Java.

At the end of the day, we have been watching a OWSM demo. Oracle Web Service Manager is a security product for your application. Instead of connecting to your OC4J component directly (with http), you actually access a Proxy server, where you can eventually add authentication with a directory server like OID and authorization, than access the OC4J url, which can be behind a firewall.

Learn more : http://otn.oracle.com/soa

I will attend the following OraclePartner Event :

Oracle Application Server 10g
Mai 11th, 2006
Hotel Hilton Zurich Airport

I posted yesterday some of my aliases. My favorite (and most obfuscated) one is the following (for bash)

eval $(awk -F: '/^[+a-zA-Z]/{l=tolower($1); sub("^+","",l); print "alias "l"=47x="$2";PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID="$1"; echo ORACLE_SID="$1"; 47; "}' /etc/oratab 2>/dev/null)


I am setting my path and a default sid/home in my .profile

[ -z "$ORACLE_SID" ] && export ORACLE_SID=LSC01
export ORACLE_HOME=$(sed -n "s/:.$//;s/^$ORACLE_SID://p" /etc/oratab)
PATH=$ORACLE_HOME/bin
PATH=$PATH:$ORACLE_HOME/opmn/bin
PATH=$PATH:$ORACLE_HOME/dcm/bin
PATH=$PATH:$HOME/bin
PATH=$PATH:/usr/local/bin
PATH=$PATH:/usr/bin
PATH=$PATH:/usr/X11R6/bin
PATH=$PATH:/bin
PATH=$PATH:/usr/sbin
PATH=$PATH:/opt/gnome/bin
PATH=$PATH:/opt/kde3/bin
PATH=$PATH:.
export PATH


Than in my eval command above set an alias for each line in /etc/oratab
# /etc/oratab
LSC01:/app/oracle/oracle/product/10.2.0/db_2:Y
+ASM1:/home/oracle/oracle/product/10.2.0/db_2:Y
RAC1:/home/oracle/oracle/product/10.2.0/db_2:Y
RAC2:/home/oracle/oracle/product/10.2.0/db_2:Y
crs:/app/oracle/product/10.2.0/crs:N


so here is the list of my dynamically generated aliases
alias asm1='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=+ASM1; echo ORACLE_SID=+ASM1;'
alias crs='x=/app/oracle/product/10.2.0/crs; PATH=${PATH//$ORACLE_HOME/$x};ORACLE_HOME=$x; ORACLE_SID=crs; echo ORACLE_SID=crs;'
alias lsc01='x=/app/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x};ORACLE_HOME=$x; ORACLE_SID=LSC01; echo ORACLE_SID=LSC01;'
alias rac1='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=RAC1; echo ORACLE_SID=RAC1;'
alias rac2='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=RAC2;echo ORACLE_SID=RAC2;'


One alias I also like to set is a switch-user alias + keep settings and profile !
alias oracle='su - oracle -c "DISPLAY=$DISPLAY ORACLE_HOME=$ORACLE_HOME ORACLE_SID=$ORACLE_SID PATH=$PATH bash --rcfile ~lsc/.bashrc"'

I also have exotic, less recommendable, aliases…

alias +='sqlplus -L /'
alias -- -='cd -'
alias ..='cd ..'
alias ...='cd ../..'
alias ....='cd ../../..'
alias .....='cd ../../../..'
alias ......='cd ../../../../..'
alias .......='cd ../../../../../..'
alias ........='cd ../../../../../../..'


I also have a very handy function, called p
p() {
sqlplus -L -s "/ as sysdba" <<EOF | sed -n 's/@ //p'
set echo off lin 9999 trimsp on feedb off head off pages 0 tab off
col name for a25
select '@',name, value from v\$parameter2 where upper(name) like upper('%$1%');
EOF
}
P() {
sqlplus -L -s "/ as sysdba" <<EOF | sed -n 's/@ //p'
set echo off lin 9999 trimsp on feedb off head off pages 0 tab off
col name for a25
select '@',ksppinm name,ksppstvl value FROM x\$ksppi join x\$ksppcv using (inst_id,indx) where upper(ksppinm) like upper('%$1%');
EOF
}


so I can check the parameter directly from the shell
$ P shared_pool_size
_io_shared_pool_size 4194304
shared_pool_size 0
__shared_pool_size 83886080


I also use KSH sometimes, there I have a slicly different version of my aliases…

The OCM 10g Upgrade is currently under development, and the OCM 10g exam should be available in May.

How will I prepare for the 10g OCM?

One month ago, I attended an Oracle Application Server course in Geneva, Switzerland. We were a group of 3 students and the 5-days lesson has been extremly interesting and intense. Starting at 9am, we used to finish at 5:30pm or later.

At LC Systems, we have a few customers using OAS, that is why I did jump into this non-database product cursus.

As I experienced in the course, it is quite different from Oracle Database. Technically speaking, you do not need much database know-how to administer an application server.

There are basically two parts. One is the Infrastructure. It contains a Directory Server (LDAP) which is stored physically on a database. As for the Grid Control, you can just click Next->Next->Next->Install to have it up and running (if you are lucky). You do not specify the size of the memory, the location of the files, the logging mode, nothing. Just the database passwords.

The second part is called the Middle Tier. It contains a J2EE engine. There you can “hotplug” your application : portal, forms, reports, discoverer…

The consistency, availability, backup strategy are not as clearly defined as in the db world I know. In the “online backup” mode, you should just hope that nothing is going to modify the xml configuration files at the time you edit them. If your file system is getting full of logs, it can well happen that your config file get truncated. If you think you are not modifying anything during the backup, it could well be Oracle itself that updates the xml files. What did also sound dubious to me is the point in time recovery. It sounds like : “Well, I will try to recover until time your repository with rman, and if I found your backup sets on the disk, it may work. If the config changed since that time, well, this is probably not the best thing to try…”

The security concept made me hurl more than one time. For example Portal. There is a superuser called PORTAL in the Oracle Internet Directory (OID). To my surprise, you can see his database password in clear text in the OID. This user has, no joke, DBA role in the database. You can “revoke” the DBA role from PORTAL in the Application Server Console, but, believe me, it does not revoke the DBA at the db level. The mod_plsql enables you to encrypt passwords in files. This is a 2-ways encryption, I doubt the algorythm is very strong. The concept rather rely on “No one should ever log-in neither on the application server (with telnet) nor on the repository database (with sqlplus).

This morning I passed my 1Z0-311 exam with success (0 error).

Ralph Bellinghausen
Originally uploaded by laurentschneider.

Oracle Application Server 10gR2 course in Oracle University, Geneva

Oracle Secure Backup is out. It enables the use encryption of backup on tapes, it can help you to save on media library costs (no need from a media manager any more).

Well, I am curious how many customer will take the challenge to migrate from tivoli or netbackup to oracle secure backup…

I read in blog from Pete Finnigan about the potential security hole in DBMS_SCHEDULER package.

DBMS_SCHEDULER as a new alternative for DBMS_JOB by Patrick Sinke

Note that on some OS, like AIX5L / oracle 10.2.0.2, the job runs as ORACLE, not as NOBODY

There is a new INSIDE OCP column in the Oracle Magazine this month (May-June 2006 Edition).

It is about the Application Server exam. In the magazine, it is referenced as 1Z1-311, but the beta phase ended half a year ago, so the exam is now production 1Z0-311. I could guess most answers, and I hope I can succeed at first try, which is no sure thing. Anyway, I will try it next Friday at Oracle University in Baden/Zurich.