A torn-down TCP session.

We recently fixed a problem where a user in London connected to an Oracle database server (using SQL Developer) in Atlanta.  She ran a query and then let the connection sit idle for 12 minutes while she answered email.  When she went back to run another query, SQL Developer just spun, ultimately failing with a connection timeout.

We broke out our trusty copy of Wireshark, and started sniffing on her workstation, hoping to see who was cutting off the conversation: the client or the server.

As you can see in the screen capture below, we have a normal Oracle TNS protocol conversation of PSH, ACK’s between her client at 172.25.4.29 and the server at 172.27.10.219.  At 72 seconds (1 minute) into the trace, everything is fine.  However, at 716 seconds (12 minutes) into the capture, the client tries to send a new SQL query to the server, and gets no response. It tries retransmitting its request several times-highlighted below as TCP Retransmissions, even marking one packet with the URG (urgent) flag to force the server to pay attention.

What is going on here?  Clearly the Oracle client still thinks it has a TCP-level connection with the Oracle server, but in reality the TCP session has already been torn down. We ultimately traced the problem back to a BlueCoat network appliance sitting between the user and the server that was disconnecting the session based on its own timeout value.

Cloning Oracle Application Server

Need to add another instance of OAS? Or maybe clone an existing instance of OAS to test something out? Here’s how you do it:
1) Stop all oracle processes in the existing environment.

2) Prepare the clone in the existing environment.
su – oracle
bash
perl $ORACLE_HOME/clone/bin/prepare_clone.pl

3) Compress the clone (outside the existing environment)
cd /d01/app/oracle/product/
tar -czvf oracleas.tar.gz 10.1.2.0.2

4) Create the home for the clone on the target machine.
mkdir -p /d01/app/oracle/product
chown -R oracle:dba /d01/app/oracle/product

5) Transfer the clone to the target machine:
scp oracleas.tar.gz [email protected]:/d01/app/oracle/product

6) Switch to the oracle home directory, and uncompress the tarball.
cd /d01/app/oracle/product
tar -zxvf oracleas.tar.gz

7) Run the clone command on the target machine (MUST be done in GUI mode using VNC).
$ perl 10.1.2.0.2/clone/bin/clone.pl ORACLE_HOME=/d01/app/oracle/product/10.1.2.0.2 ORACLE_HOME_NAME=OraHome_2 -instance forms_issac_uat_2 -ias_admin_old_pwd barfly -ias_admin_new_pwd barflea

8) Delete the cloned tarball to save space.
rm -f oracleas.tar.gz

Deploy a WAR to the OC4J root

Use Oracle Enterprise Manager, and click on the name of your OC4J container. From there, select Administration/Advanced Properties and edit the default-web-site.xml file. Find the line that looks like this:

< application="mywebapp" name="mywebapp" root="/mywebapp">

And change it to:

Restart the container as necessary. Next, select the HTTP server container. Once there, choose Administration/Advanced Server Properties, and open the mod_oc4j.conf file. Find the existing “Oc4jMount” directive for your website, and change it to:

Oc4jMount / Oc4J_instance_name
Oc4jMount /* OC4J_instance_name

Oracle version numbers explained

The significance of the version numbers are (lets use 9.0.1.1.2 as an example):

9 is the version number
0 is the new features release number
1 is the maintenance release number
1 is the generic patch set number
2 is the platform-specific patch set number

Run a command as user "x" in a shell script

Ever need to run a command as a specific user? Many Oracle commands require you to become the “oracle” user to make use of them.

To do this in a script, simply define your Oracle variables and use the “su” command. Here’s some example code:

ORA_HOME=/d01/app/oracle/product/9.2.0
ORA_OWNER=oracle
su – $ORA_OWNER -c “$ORA_HOME/bin/lsnrctl start listener_rmdyd1”
su – $ORA_OWNER -c “$ORA_HOME/bin/lsnrctl start listener_hbstgu1”

Test a JDBC connection to Oracle database

Many tools use JDBC to talk SQL to a database server. To verify your Oracle database’s ability to respond to JDBC calls, compile the following small Java program into a file called JdbcCheckup.java.

// import the Oracle JDBC drivers
import oracle.jdbc.pool.OracleDataSource;

// You need to import the java.sql package to use JDBC
import java.sql.*;

// We import java.io to be able to read from the command line
import java.io.*;

class JdbcCheckup
{
public static void main (String args [])
throws SQLException, IOException
{

// Create an OracleDataSource and set URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(“jdbc:oracle:thin:[email protected]:1525:apodevs”);

System.out.print (“Connecting to the database…”);
System.out.flush ();

// Connect to the database

System.out.println (“Connecting…”);
Connection conn = ods.getConnection();

System.out.println (“connected.”);

System.out.println (“Your JDBC configuration is correct.”);

// Close the connection
conn.close();
}
}

Before this program will compile, you need three things:

1) Oracle credentials:
The name of the database instance, the port it is listening on, the system account password, and the Oracle server name. These variables need to be placed in the JDBC connection string URL in the Java code. The syntax of a JDBC URL for Oracle is:

jdbc:oracle:thin:system/@::

In practice, it might look like this:
jdbc:oracle:thin:[email protected]:1525:apodevs

2) You will need to be running a JDK on the machine where you will be running this Java code. Any version will do.

3) Download the Oracle thin JDBC driver called “classes12.jar” from Oracle’s website:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

Once downloaded, place the JAR file in the jre|lib|ext directory of your installed JDK.

On Linux, place the JAR file in: /usr/java/jdk1.5.0_07/jre/lib/ext/
On Windows, place the JAR file in: C:Program FilesJavajdk1.6.0jrelibext

Now compile the .java file into a working class file: javac JdbcCheckup.java

Once compiled, you can run the program using the “java JdbcCheckup” command.

Oracle Financials 11i ports

Database port: 1521
RPC port: 1626
Reports port: 7000
Web Listener port: 8000
OProcMgr port: 8100
Web PLSQL port: 8200
Servlet port: 8800
Forms Listener port: 9000
Metrics Server Data port: 9100
Metrics Server Req port: 9200
JTF Fufillment Server port: 9300
Map Viewer Servlet port: 9800
OEM Web Utility port: 10000
VisiBroker OrbServer Agent port:10100
MSCA Server port: 10200
MSCA Dispatcher port: 10200
Java Object Cache port: 12345
OACORE Servlet Port Range: 16000-16009
Discoverer Servlet Port Range: 17000-17009
Forms Servlet Port Range: 18000-18009
XMLSVCS Servlet Port Range: 19000-19009