Oracle

JavaOne 2010 Related Links

Here are some information that I found on the net about JavaOne 2010. Feel free to comment with additional resources. To start, Oracle posted some video highlights. If you have JavaOne login, you can view the full versions at On Demand site. If not, you can still see some of the contents here. Beyond that, here are posts from various speakers on their talks and related links.

Comments on generating a web app from database tables

You think Hibernate is cool? Checkout what NetBeans 5.5 rc1 can do out-of-the-box. It can do O/R mapping, using bundled Hibernate, Top Link, KODO, or a persistence library of your choice. I am going to briefly describe high level steps on generating a web app from an existing Oracle database and generate simple JSF JSPs to view, delete, and update records in your database:

Once you installed Glassfish and NetBeans 5.5 rc1, you should register Glassfish with NetBeans under Runtime tab. Next, you should copy Oracle JDBC driver (class12.jar) to <glassfish_home>/lib directory. In NetBeans, register the same JDBC driver under Driver node, under Databases, within Runtime tab. Create a New Connection by right click on Databases node, within Runtime tab. Choose the driver, enter the correct URL (i.e. jdbc:oracle:thin:@host_name:1521:sid), user name, and password. If you entered correct data and the database is running correctly, you should be able to establish a connection.

Create a new project (File->New Project). Choose Web Application under Web node. On the next screen, enter a name for your web application. On the next screen, choose Java Server Faces. Now, you should have a new project created.

Create a new persistence mapping (File->New File…). Choose Persistence, then Entity Classes from Database. On the next screen, click on the drop-down box next to Data Source and choose New Data Source. Enter “jdbc/oracle” (without the quotes) for JNDI name. Choose the JDBC connection you created earlier. NetBeans will connect to the database and look-up all tables that it can access to generate a table list for you to decide what to map. Choose the tables that you want to map and go to the next screen. You can optionally enter a package name. Click on Create Persistence Unit… By default, NetBeans uses TopLink and that is what I used. Click Create and then click Finish. If TopLink libraries didn’t get added automatically, right click on Libraries node under Projects tab, choose Add Library. Choose TopLink Essentials. You can choose Hibernate if you want. If you do choose Hibernate, make sure the appropriate libraries are added to the project.

Create a generic JSF web GUI for the entity classes (File->NewFile…). Choose Persistence then JSF Pages from Entity Class. Click Add All. On the next screen, click browse and choose Web Pages node. If pages generation works, then you should see sub-directories appear Web Pages node in your project. If page generation failed, you might want to delete the project completely, restart the IDE and give it another try. I had to retry a second time once, so your millage may very.

Expand one of the new sub-directories, right click on a JSP and choose Run File.

Access an Oracle database with NetBeans 5.5 beta2

I just discovered that NetBeans can allows a user to access databases to view, edit, and execute commands. It looks pretty cool for an IDE. If you prefer to use command line, use sqlplus or command line tool for the database. If you use Windows operating system and Oracle database, TOAD could be another option. If you are using NetBeans and prefer to stay within the IDE, you can use its database features.

To get started, here are my notes on connecting to an existing Oracle database:

1. Click on Runtime tab on the panel on the upper left side.

2. Expand Databases node

3. Right click on Drivers and choose New Driver

4. Click Add

5. Choose class12.zip file from your local file system and click Open
6. Click Ok to accept the default config

7. Right click on database and choose New Connection

8. Seect Oracle driver

9. Change <HOST> with proper host name. Change <PORT> with proper port number. And change <SID> with the proper SID value.

10. Enter a user name and password for the database.

11. Click OK

12. You should see an extra node that shows the connection you established. Expand the code and browse around. Right click on a node and see what tasks you can do.

This little discovery certainly was a pleasant surprise.

Oracle 10g on Solaris 10 SPARC installation notes

groupadd -g 100 dba
groupadd -g 101 oinstall
useradd -u 101 -d /export/home/oracle -g oinstall -G dba -m -s /usr/bin/bash oracle
passwd oracle
cp /etc/system /etc/system.orig

Add the following parameters in /etc/system

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10

/usr/sbin/reboot
su – oracle
mkdir oradata

Add the following parameters in .bash_profile

umask 022
export ORACLE_SID=[sid]
export ORACLE_HOME=[path]

export PATH=$ORACLE_HOME/bin:$PATH

. ./.bash_profile

Upload Oracle 10g binary (i.e. cpio or cpio.gz files) to a directory on the target machine. An example could be /download go to the directory where the Oracle binary are located gunzip each file by running gunzip [gz file]

su –

go to the directory where the Oracle binary are located
Create a working directory for each cpio file.

cpio -idmv < [cpio file]
cd /var/opt
mkdir oracle
cd oracle

Create a text file called oraInst.loc with the following content

inventory_loc=[ORACLE_HOME]/oraInventory
inst_group=

Then, set the file properties:
chown oracle:oinstall oraInst.loc
chmod 644 oraInst.loc

Edit the Oracle response file

cd into [cpio extracted directory]/Disk1/response/ directory
cp enterprise.rsp ../response_file.rsp

edit response_file.rsp

Define the following parameters:
ORACLE_HOME=”[oracle_home_value]” ORACLE_HOME_NAME=”[oracle_home_name_value]“
s_nameForDBAGrp=”dba”
s_nameForOPERGrp=”dba”
SHOW_DATABASE_CONFIGURATION_PAGE=false SHOW_DATABASE_OPTIONS_PAGE=false
s_globalDBName
s_dbSid SHOW_DATABASE_MANAGEMENT_PAGE=false SHOW_DATABASE_FILE_STORAGE_OPTION_PAGE=false

s_mountPoint=[data file location, ex: ORACLE_HOME/oradata] SHOW_BACKUP_RECOVERY_OPTION_PAGE=false SHOW_SCHEMA_PASSWORDS_PAGE=false
s_superAdminSamePasswd=[passord value] s_superAdminSamePasswordAgain=[passowrd value]
sl_superAdminPassowrds=[password value]
sl_superAdminPasswordsAgain=[password value]

comment out rest of parameters that has “Value Unspecified”, which are listed below: CLUSTER_NODES
s_dlgEMSMTPServer
s_dlgEMEmailAddress
s_dlgEMCentralAgentSelected
s_rawDeviceMapFileLocation
s_dlgRBORecoveryLocation
s_dlgRBOUsername
s_dlgRBOPassword

You should make sure that the following two parameters are defined:
ORACLE_HOME = “[home value]“
ORACLE_HOME_NAME=”[name value]”

su – oracle

cd to the directory where the installer is located, i.e. [directory where you ran cpio command]/Disk1

runInstaller -ignoreSysPrereqs -silent -responseFile [full path]/response_file.rsp

Note: -ignoreSysPrereqs is only required on Solaris 10. After the installation completed, run the root.sh script in [ORACLE_HOME]/root.sh.

How to create an instance of Oracle database using DBCA command line tool?

Pre-condition: You must have Oracle database software installed and this note is for 10g.

You will only need to create an instance if you don’t already have one or if you wish to create a new instance. Check what you already have using ps -ef |grep ora

1. Make a copy of the response file (dbca.rsp in the installation binary directory, which should be [installation bin dir]/Disk1/response/dbca.rsp).

2. Make a copy: cp dbca.rsp ..

3. Edit the response file with local environment configuration information. The parameters that you should update are:

GDBNAME=”Your_GDBNAME_Value”
SID=”YourSID”
TEMPLATENAME = “New Database”

3. run ONE of the the following two command as Oracle user:
dbca -progress_only -responseFile [response file]

or

dbca -silent -responseFile [response file]

Note: -progress_only option will require graphical display. Set your DISPLAY env parameter if you are working remotely.

Oracle notes

Oracle 10g is Buggy!
Out of box, 10g (10.1.0.2.0) ‘s /rdbms/admin/spcreate.sql is broken. It would throw the following error when I tried to install statspack:
SQL> — Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

I had to change the password and add in @ in the script to get the statspack installation complete.

Error! in spcreate.sql:
connect perfstat/&&perfstat_password

My correction in spcreate.sql:
connect perfstat/perfstat @ oracle

A Good Reference

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

To verify timed_statistics parameter
SQL> select name, value, isdefault from v$parameter where name = ‘timed_statistics’;