Category Archives: Database

@Oracle Database Connection Pooling


Be it a JDBC Connection or any other connection from an application to a database, a TCP connection is established to talk to the database server. The connection made to a database is a relatively expensive call and that is the reason why application servers and frameworks such as hibernate use connection pooling.

Each database connection for example in JDBC takes the following sequence of steps:

  1. The JVM opens a socket connection.
  2. The call to get the database connection needs to pass through firewalls before it reaches the destination database host. Generally database servers are behind firewalls that only allow ports such as 1521 for Oracle and 1433 for SQL Server.
  3. The database server processes the new connection request and initializes the new connection to the database.
  4. It acknowledges the connection back to the JDBC client.

Application servers use connection pooling to maintain a pool of active TCP connections to the database. This will allow the connections to be reused and also improve the user experience. When a client requests for a connection, a TCP connection to the database doesn’t happen at request time but one of the connections from the pool is returned. And when the client closes the connection, a TCP connection to the database isn’t closed but the connection object is returned to the pool waiting for some other requests.

Generally, when DBA’s bounce the database server either for upgrade or patching, they use a Normal Shutdown option. For example in @Oracle, the following two conditions must be true:

  1. No new connections are allowed after the statement is issued.
  2. Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database.

See http://docs.oracle.com/cd/B10501_01/server.920/a96521/start.htm#7871

 

Advertisements

Start and Stop Oracle in Unix


Start Oracle server in UNIX
Type the following command, enter:
First login as oracle user
$ su – oracle
Now use lsnrctl command to start service (usually located at /home/oracle/oracle/product/10.2.0/bin directory):
$ lsnrctl start
Next start database:
$ dbstart

Stop Oracle service in UNIX

To stop Oracle servuice type following two commands:
$ lsnrctl stop
$ dbshut