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

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 !

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!