Saturday, June 11, 2005

Oracle DBA for Dummy

Note to myself: set up Oracle user.

renegade:~ oracle$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Sat Jun 11 07:29:50 2005

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


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

SQL> DROP USER user1 CASCADE;

User dropped.

SQL> CREATE TABLESPACE user1 
  2  DATAFILE '/Volumes/u02/oradata/test1/user1.dbf'
  3  SIZE 100M
  4  AUTOEXTEND ON NEXT 10M
  5  MAXSIZE 2000M;

Tablespace created.

SQL> CREATE USER user1                    
  2  IDENTIFIED BY user1
  3  DEFAULT TABLESPACE user1
  4  TEMPORARY TABLESPACE temp;

User created.

SQL> GRANT CONNECT, RESOURCE TO user1;

Grant succeeded.

SQL> ALTER USER user1 QUOTA UNLIMITED ON user1;

User altered.

Technorati Tags:

Friday, June 10, 2005

Set up Oracle 10g on Tiger

This describes how I set up Oracle 10g on OSX Tiger for development use.

Sergio has an excellent instruction on how to install Oracle 10g on Panther. However because the Oracle requires gcc 3.3 and Tiger uses gcc 4 by default. Some hacks are in order.

Following Sergio's instruction to prepare Oracle installation environment. Issue the following commands as root before launch installer.

sudo nicl . -append /groups/admin users oracle
sudo gcc_select 3.3
Then launch the Oracle installer. The installation will fail towards the end complaining lost TNS when running database creation wizard. Simply abort and do following extra steps.

Step 1: Firstly, set up $ORACLE_HOME and $PATH in your .bash_profile. By default, the ORACLE_HOME is at "/Volumes/u01/app/oracle/product/10.1.0/db_1" if you follow Sergio's instruction. While logged in as oracle, add following two lines to .bash_profile, adjust the ORACLE_HOME value as necessary.

export ORACLE_HOME="/Volumes/u01/app/oracle/product/10.1.0/db_1"
export PATH=$PATH:~/bin:$ORACLE_HOME/bin
Issue following command to activate new settings.
cd
. .bash_profile

Step 2: Relink Oracle libraries.

cd $ORACLE_HOME/lib
mv libnnz10.dylib libnnz10.dylib.ori
relink all
mv libnnz10.dylib.ori libnnz10.dylib

Step 3: Run 'dbca' to create a database. What to do when dbca does not start?

Step 4: Run '$ORACLE_HOME/root.sh'. This script will appear hang at end and displaying a message "Expecting the CRS daemons to be up within 600 seconds." The CRS is only available on OSX Server so simply Ctrl-C terminate the process.

Step 5: Download Oracle startup and stop script and extract it to 'oracle' user home. Now you can start and stop Oracle instance by running 'startup.sh' and 'stop.sh' respectively.

Step 6: If your machine has a fixed IP, you probably don't need to perform this step. Since I run Oracle on my powerbook and I am only going to use this Oracle for development purpose, I change listener.ora and tnsnames.ora under $ORACLE_HOME/network/admin to make it work with dynamic ip like 192.168.0.1 and only connectable from localhost.

My listener.ora looks like:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = test1)
      (ORACLE_HOME = /Volumes/u01/app/oracle/product/10.1.0/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
    )
  )

And my tnsnames.ora looks like:

TEST1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test1)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

One final point, Oracle 10g has its own JDBC driver. Drivers for Oracle 9 won't work with 10g.

Update (Tue Jul 26 08:48:23 PDT 2005): how to start isqlplus.

Technorati Tags: ,

Thursday, June 02, 2005

Pragmatically set up JMS retry strategy in JBoss

JBoss uses so called "Dead Letter Queue" to handle undeliverable JMS message. It can be configured with "conf/standardjboss.xml".

In times though, you may want finer control over JMS retry strategy. By setting JBoss specific properties before sending message, you can pragmatically override how undeliverable JMS message is handled.

  • JMS_JBOSS_SCHEDULED_DELIVERY : scheduled time for message delivery
  • JMS_JBOSS_REDELIVERY_DELAY : delay between retry in milli-seconds
  • JMS_JBOSS_REDELIVERY_COUNT : count of retry
  • JMS_JBOSS_REDELIVERY_LIMIT : max number of retry attempt

TextMessage outMessage= topicSession.createTextMessage(outString);
            
outMessage.setLongProperty("JMS_JBOSS_REDELIVERY_DELAY", 10000);

outMessage.setLongProperty("JMS_JBOSS_SCHEDULED_DELIVERY", 
    deliveryDate.getTime());

topicPublisher.publish(outMessage);  

Technorati Tags: