Oracle 7 Apero

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

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!

SOA

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

set my ORACLE_HOME, PATH, ORACLE_SID

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…

OCM preparation

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?

  • Part I, get confident with the environment :
    According to http://education.oracle.com/certification, the exam will be on redhat AS3 with DB 10.1.0.4. RHAS3 is not free. If I had not RH in my company, I would then probably use WhiteBox Linux or a similar RedHat clone to get an environment as close as possible to the lab. It seems very important to me is to get the same terminals (Gnome) and the same Browser (Mozilla 1.6).

    I am trained to customized my environment within seconds. For example, I like to have a bookmark for 10G Book List, one for the Reference, one for SQL Reference, one for PL/SQL Packages Ref and one for RMAN Reference. So within one click, I can reach the appropriate book. I also like to shortcut most of my commands… I like to type pmon instead of ps -ef |grep ora_pmon and so I defined some aliases. I am able to define those below plus a few more obscure ones in less than one minute.

    
    alias abort='echo shutdown abort|sqlplus -L -s / as sysdba'
    alias alert='vi $ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log'
    alias nomount='echo startup nomount quiet|sqlplus -L -s / as sysdba'
    alias ora='cd $ORACLE_HOME'
    alias pmon='ps -ef | grep [p]mon'
    alias startup='echo startup quiet|sqlplus -L -s / as sysdba'
    alias sysdba='sqlplus -L / as sysdba'
    alias tns='cd $ORACLE_HOME/network/admin'
    

    I have read man vi until my eyes hurt. I have trained using GNOME keyboard shortcuts until my finger skin burnt.

    I have shortcut to set my oracle homes, and I am proficient with Unix commands.

    With the few commands I defined, I am able to do most maintenance tasks and to call the oracle binaries like sqlplus faster than Lucky Luke.

    I have read the SQL reference manual, the reference manual, and the RMAN reference manual.
    I can type in less than two minutes the complete syntax for creating a database. I am familiar with the most commonly used character sets. I can create tablespaces, add and remove files (well, removing logfile and tempfiles only in 10gR1).

    I have a bulletproof backup and recovery strategy. I know how to check critical ORA- message in the alert log, I can identify the missing files / directories on disk, I can recover from any error.

    I know -almost- every listener.ora, sqlnet.ora (except the security related ones), and tnsnames.ora. I can create sqlnet.ora, listener.ora, tnsnames.ora, start and stop my listener within one minute.

  • Part II, read the specification
    I print the documentation and I make sure I have understood the objectives. I try every scenario. In order to save time during the preparation, I am using scripts, so that I can generate a few databases quickly. For each scenario, I am generating errors. If you train with a collegue, try to generate the most surprising database corruption for your collegue at each scenario ;-)

    There is a RAC scenario. I have received an Egenera blade to test Oracle Real Application Cluster, with up to 8 nodes. I documented in a previous post the steps I followed to install RAC on my notebook before receiving the Egenera hardware : Suse10 10gR2 Laptop Rac.

    … invest enough time … do not schedule it too early … search for articles in Oracle magazine

  • Part III, relax
    It is a nice challenge. Do sleep enough before the exam. Book a quiet hotel at least one day before. Do not work on the day before. Just rest.