Friday, January 30, 2009

Oracle Logon Trigger for Auditing

Oracle Audit tables (aud$) don't collect program information, so you need a table and logon trigger if you want to collect this information.

CREATE TABLE cmc_audit_users
(
username VARCHAR2(30),
osuser VARCHAR2(30),
sid NUMBER ,
host VARCHAR2(30),
ip_address VARCHAR2(30),
program VARCHAR2(48),
logon_time DATE
) ;

CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
INSERT
INTO cmc_audit_users VALUES
(
sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','SID') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','IP_ADDRESS') ,
sys_context('USERENV','MODULE') ,
sysdate
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Tuesday, January 27, 2009

Clone ORACLE_HOME

For a single-node Oracle install it is usually faster to clone the ORACLE_HOME than to re-install and re-apply patches.
Cloning can be done using OEM or manually using clone.pl or runInstaller
Cloning guarantees that you have identical components installed on DEV, QA, PROD.
You can only clone between the same OS - e.g. from AIX to AIX.
It is possible to clone the ASM and DB ORACLE_HOME but not the OEM agent.

The process I followed to manually clone on AIX is as follows. You can find more information in Metalink Note 565009.1.

#1. prepare for cloning (this step currently does not do anything useful but it may be necessary in the future)
cd $ORACLE_HOME/clone/bin
chmod u+x prepare_clone.pl
vi prepare_clone.pl
change /usr/local/bin/perl to /usr/bin/perl
prepare_clone.pl

#2. copy all files under $ORACLE_HOME to the destination directory or destination server (e.g use tar/ftp or scp).

#3. run the clone process (create new inventory or register the ORACLE_HOME with existing inventory, relink binaries etc)
# check the contents of /etc/oraInst.loc
more /etc/oraInst.loc
cd $ORACLE_HOME/clone/bin
chmod u+x clone.pl
vi clone.pl
change /usr/local/bin/perl to /usr/bin/perl
# example clone the ASM ORACLE_HOME (clone.pl calls oui)
perl clone.pl ORACLE_HOME="/opt/oracle/product/10.2.0/asm" ORACLE_HOME_NAME="ASM_HOME"
# example clone the DB ORACLE_HOME
perl clone.pl ORACLE_HOME="/opt/oracle/product/10.2.0/db_1" ORACLE_HOME_NAME="db10g_home1"
# check the inventory to confirm the ORACLE_HOME is registered
more /opt/oracle/product/oraInventory/ContentsXML/inventory.xml

# 11gr2 requires the ORACLE_BASE parameter
perl clone.pl ORACLE_BASE="/opt/oracle" ORACLE_HOME="/opt/oracle/product/11.2.0/dbhome_1" ORACLE_HOME_NAME="db112_home1"

#4. run root.sh in each ORACLE_HOME

#5. for the ASM ORACLE_HOME ask the unix team to run the following as root to setup the cluster software
cd $ORACLE_HOME/bin
localconfig delete
localconfig add

# if you have problems cloning with clone.pl you can directly call runInstaller but you do need an Xterm or VNC GUI session.

./runInstaller -clone ORACLE_HOME="/opt/oracle/product/10.2.0/owb" ORACLE_HOME_NAME="owb10g_home1"

# To clone a RAC 11g Home :-

export DISPLAY=xxx.xxx.xxx.xxx:0

ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.1.0/db_1
cd $ORACLE_HOME/clone
THISNODE=`hostname -s`
E01=ORACLE_HOME=${ORACLE_HOME}
E02=ORACLE_HOME_NAME=11g_home1
E03=ORACLE_BASE=/opt/oracle
C01="-O'CLUSTER_NODES={q-prod-oranode-30, x-prod-oranode-31}'"
C02="-O'LOCAL_NODE=$THISNODE'"

perl $ORACLE_HOME/clone/bin/clone.pl $E01 $E02 $E03 $C01 $C02

# Or manually run runInstaller :-

./runInstaller -silent -clone -waitForCompletion ORACLE_HOME="/opt/oracle/product/11.1.0/db_1" ORACLE_HOME_NAME="11g_home1" ORACLE_BASE="/opt/oracle/product" "CLUSTER_NODES={q-prod-oranode-30, x-prod-oranode-31}" LOCAL_NODE="q-prod-oranode-30" -noConfig -nowait

Friday, January 16, 2009

Installing ProC demo on Oracle 10g

The default install of Oracle 10g Enterprise Edition includes OCI and Oracle Programmer which are needed if a developer wants to use ProC. You can confirm the install by checking the oraInventory or look for files in $ORACLE_HOME/precomp/public

Developers will often ask for the ProC demo to be installed so they can use the make files as a starting point. To install the demo, the DBA will need to install "Database Examples" from the Companion CD. (see attached)

After the install you should find the $ORACLE_HOME/precomp/demo directory.

Monday, January 5, 2009

Failure at final check of Oracle CRS stack.

# SYMPTOM :- when installing CRS you get Failure while running root.sh on second RAC node.

Failure at final check of Oracle CRS stack.
10 # Status = 10

[oracle@prod-oranode-221 bin]$ crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

# ocssd.log

[ CSSD]2008-12-31 16:26:19.165 [1158695232] >TRACE: clssnmReadDskHeartbeat: node 1, prod-oranode-220, has a disk HB, but no network HB, DHB has rcfg 126203009, wrtcnt, 374, LATS 2000274, lastSeqNo 374, timestamp 1230740777/2052924

# DEBUG :- Metalink Note :- 369699.1

[oracle@prod-oranode-220 ~]$ traceroute prod-oranode-221.cmc.local
traceroute to prod-oranode-221.cmc.local (10.220.1.104), 30 hops max, 40 byte packets
1 prod-oranode-221.cmc.local (10.220.1.104) 1.120 ms !X 1.094 ms !X 1.057 ms !X

man traceroute

After the time some additional annotation can be printed: !H, !N, or !P (host, network
or protocol unreachable), !S (source route failed), !F (fragmentation needed), !X (com-
munication administratively prohibited), !V (host precedence violation), !C (precedence
cutoff in effect), or ! (ICMP unreachable code ). If almost all the probes
result in some kind of unreachable, traceroute will give up and exit.

# FIX :- check firewall / routing / iptables issues

# 1. reboot and check that CRS processes are now running OK

[oracle@prod-oranode-221 bin]$ ./crs_stat -t
CRS-0202: No resources are registered.

# 2. Look at /opt/oracle/product/11.1.0/crs/install/rootconfig and run the missing steps (VIPCA)

[root@prod-oranode-221 bin]# CRS_NODELIST=prod-oranode-220,prod-oranode-221
[root@prod-oranode-221 bin]# CRS_NODEVIPS='prod-oranode-220/prod-vip-220/255.255.248.0/bond0,prod-oranode-221/prod-vip-221/255.255.248.0/bond0'
[root@prod-oranode-221 bin]# ./vipca -silent -nodelist $CRS_NODELIST -nodevips $CRS_NODEVIPS

Creating VIP application resource on (2) nodes...
Creating GSD application resource on (2) nodes...
Creating ONS application resource on (2) nodes...
Starting VIP application resource on (2) nodes...
Starting GSD application resource on (2) nodes...
Starting ONS application resource on (2) nodes...


Done.
[root@prod-oranode-221 bin]# ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....220.gsd application ONLINE ONLINE prod...-220
ora....220.ons application ONLINE ONLINE prod...-220
ora....220.vip application ONLINE ONLINE prod...-220
ora....221.gsd application ONLINE ONLINE prod...-221
ora....221.ons application ONLINE ONLINE prod...-221
ora....221.vip application ONLINE ONLINE prod...-221

# 3. Go back to OUI and click OK for post root.sh steps to continue.