Oracle

These are my notes for installing Oracle 10g on Solaris.

1. Download Oracle 10g database from here.
   
   
2. Download the JDBC driver from here.
   
   Replace the classes12.jar file in the application with the ojdbc14.jar file from the download file.
   
   Create the Oracle user account:
   
   groupadd oinstall
groupadd dba
useradd -d /export/home/oracle -g oinstall -G dba -m -s /usr/bin/bash oracle
passwd -r files oracle
   
   
3. Edit /export/home/oracle/.profile, adding the following lines:
   
   umask 022
TMP=/tmp
TMPDIR=$TMP
export TMP TMPDIR

ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/10g
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
   
   
4. Create Oracle installation directory structure:
   
   mkdir /opt/oracle
mkdir /opt/oracle/10g
chown -R oracle:oinstall /opt/oracle
chmod -R 775 /opt/oracle
   
   
5. Turn on X11Forwarding in /etc/ssh/sshd_config:
   
   X11Forwarding yes
   
   Restart the sshd server:
   
   /etc/init.d/sshd restart
   
   
6. Add swap space:
   
   swap -l (lists files in swap)
   mkdir /var/swap
   mkfile 1g /var/swap/swap11
   swap -a /var/swap/swap11
   swap -l
   
   Note: This change is not permanent. The additional space will go away when the server is rebooted.
   
   
7. Set kernel parameters in /etc/system:
   
   set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmni=100
set semsys:seminfo_semvmx=32767
set noexec_user_stack=1
   
   These changes requires a server reboot to take effect:
   
   sync;sync;init 6
   
   
8. Download Cygwin from:
   
   http://www.cygwin.com/
   
   Install Cygwin on Windows XP client, default package selection is sufficient
   
   Launch Cygwin and run 'startx' from the command line.
   
   
9. Open a Putty session with X11 forwarding enabled and X display
   location set to '127.0.0.1:0.0'
   
   Username: oracle
   Password: oracle1
   
   
10. Uncompress the 10g database in a temporary directory:
    
    mkdir /var/tmp/oracle
mv 10gr2_db_sol.cpio.gz /var/tmp/oracle
cd /var/tmp/oracle
gunzip 10gr2_db_sol.cpio.gz
cpio -idmv < 10gr2_db_sol.cpio
    
    
11. Launch the Oracle installer:
    
    bash-2.05$ /var/tmp/oracle/runInstaller
    Starting Oracle Universal Installer...
    
    Checking installer requirements...
    
    Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.9
    Passed
    
    Checking Temp space: must be greater than 250 MB. Actual 6951 MB Passed
    Checking swap space: must be greater than 500 MB. Actual 7364 MB Passed
    Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
    
    All installer requirements met.
    
    
12. Select 'dba' for the UNIX DBA Group and set the Database Password to 'admin'
    
    The following J2EE Applications have been deployed and are accessible at the URLs listed below.
    
    iSQL-Plus URL:
    http://localhost:5560/isqlplus
    
    iSQL-Plus DBA URL:
    http://localhost:5560/isqlplus/dba
    
    Enterprise Manager 10g Database Control URL:
    http://localhost:1158/em
    
    
13. Create the database objects:
    
    CREATE SMALLFILE TABLESPACE "------" DATAFILE '/opt/oracle/oradata/orcl/------01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
    
    CREATE USER "------" PROFILE "DEFAULT" IDENTIFIED BY "------" DEFAULT TABLESPACE "------" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
    GRANT UNLIMITED TABLESPACE TO "------"
    GRANT "CONNECT" TO "------"
    GRANT "RESOURCE" TO "------"
    
    CREATE SEQUENCE HIBERNATE_SEQUENCE
    CREATE TABLE UNIQUE_KEY (NEXT_HI LONG NOT NULL)
    INSERT INTO UNIQUE_KEY VALUES (1000000)
    

Posted by Ross Olweiler in Oracle at 18:08 | Comments (0) | Trackbacks (0)

Seems that you can setup a Hibernate connection to a load-balanced Oracle cluster, right in your hibernate.connection.url:

hibernate.connection.url=jdbc:oracle:thin:@
(description=
(enable=broken)
(load_balance = yes)
(address = (protocol = tcp)(host = db-host-1)(port = 1521))
(address = (protocol = tcp)(host = db-host-2)(port = 1521))
(connect_data =
(server = shared)
(service_name = dbname)
(failover_mode=(type=select)(method=basic)(RETRIES=10)(DELAY=1))
)
)

Posted by Ross Olweiler in Oracle at 19:46 | Comments (0) | Trackbacks (0)

From the Oracle docs:

JDBC Thin Driver Character Set Conversion

If applications or applets use the JDBC thin driver, then there is no Oracle client installation. Because of this, the OCI client conversion routines in C are not available. In this case, the client conversion routines of the JDBC thin driver are different from conversion routines of the JDBC OCI driver.

If the database character set is US7ASCII, WE8ISO8859P1, UTF8, or AL32UTF8, then the data is transferred to the client without any conversion. The JDBC Class Library then converts the data to UTF-16 in Java.

Otherwise, the server first translates the data to UTF8 or AL32UTF8 before transferring it to the client. On the client, the JDBC Class Library converts the data to UTF-16 in Java.

Posted by Ross Olweiler in Oracle at 18:06 | Comments (0) | Trackbacks (0)