29 Database Management

Database Startup and Shutdown

Oracle Database 11g Release 1 (11.1) introduces two new JDBC methods, startup and shutdown, in oracle.jdbc.OracleConnection that enable you to start up and shut down an Oracle Database instance. This is similar to the way you would start up or shut down a database instance from SQL*Plus.

To use these methods, you must have a dedicated connection to the server. You cannot be connected to a shared server through a dispatcher.

To use the startup and shutdown methods, you must be connected as SYSDBA or SYSOPER. To connect as SYSDBA or SYSOPER with Oracle JDBC drivers, you need to set the internal_logon connection property accordingly.

To logon as SYSDBA with the JDBC Thin driver you need to configure the server to use the password file. For example, to configure system/manager to connect as sysdba with the JDBC Thin driver, perform the following:

1. From the command line, type:

   orapwd file=$ORACLE_HOME/dbs/orapw password=yourpass entries=5
   

2. Connect to database as SYSDBA and run the following commands from SQL*Plus:

   GRANT SYSDBA TO system;
   ALTER USER system IDENTIFIED BY manager;
   

3. Edit init.ora and add the following line:

   REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
   

4. Restart the database instance.

As opposed to the JDBC Thin driver, the JDBC OCI driver can connect as SYSDBA or SYSOPER locally without specifying a password file on the server.

To start a database instance using the startup method, the application must first connect to the database as a SYSDBA or SYSOPER in the PRELIM_AUTH mode, which is the only connection mode that is permitted when the database is down. You can do this by setting the new connection property prelim_auth to true. In the PRELIM_AUTH mode, you can only start up a database instance that is down. You cannot run any SQL statements in this mode.

The startup method only starts up a database instance. It does not mount it nor open it. You have to reconnect as SYSDBA or SYSOPER, but without the PRELIM_AUTH mode, and run the following commands to mount and open the database instance:

ALTER DATABASE MOUNT
ALTER DATABASE OPEN

The startup method takes a parameter that specifies the database startup option. Table 29-1 lists the supported database startup options. These options are defined in the oracle.jdbc.OracleConnection.DatabaseStartupMode class.

The shutdown method enables you to shut down an Oracle Database instance. To use this method, you must be connected to the database as a SYSDBA or SYSOPER.

Like the startup method, the shutdown method also takes a parameter. In this case, the parameter specifies the database shutdown option. Table 29-2 lists the supported database shutdown options. These options are defined in the oracle.jdbc.OracleConnection.DatabaseShutdownMode class.

For shut down options other than ABORT and FINAL, you must call the shutdown method again with the FINAL option to actually shut down the database.

A standard way to shut down the database is as follows:

1. Initiate shutdown by prohibiting further connections or transactions in the database. The shut down option can be either CONNECT, TRANSACTIONAL, TRANSACTIONAL_LOCAL, or IMMEDIATE.

2. Dismount and close the database by calling the appropriate ALTER DATABASE command.

3. Finish shutdown using the FINAL option.

In special circumstances to shutdown the database as fast as possible, the ABORT option can be used. This is the equivalent to SHUTDOWN ABORT in SQL*Plus.

Example

Example 29-1 illustrates the use of the startup and shutdown methods.

import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
/**
 * To logon as sysdba, you need to create a password file for user "sys":
 *   orapwd file=/path/orapw password=manager entries=300
 * and add the following setting in init.ora:
 *   REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 * then restart the database.
 */
public class DBStartup
{
  static final String DB_URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XYZ.com)(PORT=1521))"
+ "(CONNECT_DATA=(SERVICE_NAME=rdbms.devplmt.XYZ.com)))";

  public static void main(String[] argv) throws Exception
  {
// Starting up the database:
    OracleDataSource ds = new OracleDataSource();
    Properties prop = new Properties();
    prop.setProperty("user","sys");
    prop.setProperty("password","manager");
    prop.setProperty("internal_logon","sysdba");
    prop.setProperty("prelim_auth","true");
    ds.setConnectionProperties(prop);
    ds.setURL(DB_URL);
    OracleConnection conn = (OracleConnection)ds.getConnection();
    conn.startup(OracleConnection.DatabaseStartupMode.NO_RESTRICTION);
    conn.close();

// Mounting and opening the database
    OracleDataSource ds1 = new OracleDataSource();
    Properties prop1 = new Properties();
    prop1.setProperty("user","sys");
    prop1.setProperty("password","manager");
    prop1.setProperty("internal_logon","sysdba");
    ds1.setConnectionProperties(prop1);
    ds1.setURL(DB_URL);
    OracleConnection conn1 = (OracleConnection)ds1.getConnection();
    Statement stmt = conn1.createStatement();
    stmt.executeUpdate("ALTER DATABASE MOUNT");
    stmt.executeUpdate("ALTER DATABASE OPEN");
    stmt.close();
    conn1.close();

// Shutting down the database
    OracleDataSource ds2 = new OracleDataSource();
    Properties prop = new Properties();
    prop.setProperty("user","sys");
    prop.setProperty("password","manager");
    prop.setProperty("internal_logon","sysdba");
    ds2.setConnectionProperties(prop);
    ds2.setURL(DB_URL);
    OracleConnection conn2 = (OracleConnection)ds2.getConnection();
    conn2.shutdown(OracleConnection.DatabaseShutdownMode.IMMEDIATE);
    Statement stmt1 = conn2.createStatement();
    stmt1.executeUpdate("ALTER DATABASE CLOSE NORMAL");
    stmt1.executeUpdate("ALTER DATABASE DISMOUNT");
    stmt1.close();
    conn2.shutdown(OracleConnection.DatabaseShutdownMode.FINAL);
    conn2.close();
  }
}

Database Change Notification

Generally, a mid-tier data cache duplicates some data from the back-end database server. Its goal is to avoid redundant queries to the database. However, this is efficient only when the data rarely changes in the database. The data cache has to be updated or invalidated when the data changes in the database. The 11g release 1 (11.1) Oracle JDBC drivers provide support for the Database Change Notification feature of Oracle Database. Using this functionality of the JDBC drivers, multi-tier systems can take advantage of the Database Change Notification feature to maintain a data cache as updated as possible by receiving invalidation events from the JDBC drivers.

The JDBC drivers can register SQL queries with the database and receive notifications in response to:

• DML or DDL changes on the objects associated with the queries.

• DML or DDL changes that affect the result set.

The notifications are published when the DML or DDL transaction commits (changes made in a local transaction do not generate any event until they are comitted).

To use Oracle JDBC driver support for Database Change Notification, you need to perform the following:

1. Registration: You first need to create a registration

2. Query association: After you have created a registration, you can associate SQL queries with it. These queries are part of the registration.

3. Notification: Notifications are created in response to changes in tables or result set. Oracle database communicates these notifications to the JDBC drivers through a dedicated network connection and JDBC drivers convert these notifications to Java events.

Also, you need to grant the CHANGE NOTIFICATION privilege to the user. For example, if you connect to the database using the SCOTT user name, then you need to run the following command in the database:

grant change notification to scott;

Creating a Registration

Creating a registration is a one-time process and is done outside of the currently used transaction. The API for creating a registration in the server is executed in its own transaction and is committed immediately. You need a JDBC connection to create a registration, however, the registration is not attached to the connection. You can close the connection after creating a registration, and the registration survives. In a RAC environment, a registration is a persistent entity that exists on all nodes. If a node goes down, then the registration continues to exist and will be notified when the tables change.

There are two ways to create a registration:

• The JDBC-style of registration: Use the JDBC driver to create a registration on the server. The JDBC driver launches a new thread that listens to notifications from the server (through a dedicated channel) and converts these notification messages into Java events. The driver then notifies all the listeners registered with this registration.

• The PLSQL-style of registration: If you want a PLSQL stored procedure to handle the notifications, then create a PLSQL-style registration. Like in the JDBC-style of registration, the JDBC drivers enable you to attach statements (queries) to this registration. However the JDBC drivers do not get notifications from the server because the notifications are handled by the PLSQL stored procedure.

You can use the registerDatabaseChangeNotification method of the oracle.jdbc.OracleConnection interface to create a JDBC-style of registration. You can set certain registration options through the options parameter of this method. Table 29-3 lists some of the registration options that can be set. To set these options, use the java.util.Properties object. These options are defined in the oracle.jdbc.OracleConnection interface. The registration options have a direct impact on the notification events that the JDBC drivers will create. Example 29-1 illustrates how to use the Database Change Notification feature.

The registerDatabaseChangeNotification method creates a new database change registration in the database server with the given options. It returns a DatabaseChangeRegistration object, which can then be used to associate a statement with this registration. It also opens a listener socket that will be used by the database to send notifications.

If there exists an registration, then you can also use the getDatabaseChangeRegistration method to map the existing registration with a new DatabaseChangeRegistration object. This method is particularly useful if you have created a registration using PL/SQL and want to associate a statement with it.

Associating a Query With a Registration

After you have created a registration or mapped to an existing registration, you can associate a query with it. Like creating a registration, associating a query with a registration is a one-time process and is done outside of the currently used registration. The query will be associated even if the local transaction is rolled back.

You can associate a query with registration using the setDatabaseChangeRegistration method defined in the OracleStatement class. This method takes a DatabaseChangeRegistration object as parameter. The following code snippet illustrates how to associate a query with a registration:

...
// conn is a OracleConnection object.
// prop is a Properties object containing the registration options.
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotifictaion(prop);
...
Statement stmt = conn.createStatement();
// associating the query with the registration
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
// any query that will be executed with the 'stmt' object will be associated with
// the registration 'dcr' until 'stmt' is closed or
// '((OracleStatement)stmt).setDatabaseChangeRegistration(null);' is executed.
...

Notifying Database Change Events

To receive database change notifications, you need to attach a listener to the registration. When a database change event occurs, the database server notifies the JDBC driver. The driver then constructs a new java event, identifies the registration to be notified, and notifies the listeners attached to the registration. The event contains the object ID of the database object that has changed and the type of operation that caused the change. Depending on the registration options, the event may also contain row-level detail information. The listener code can then use the event to make decisions about the data cache.

You can attach a listener to a registration using the addListener method. The following code snippet illustrates how to attach a listener to a registration:

...
// conn is a OracleConnection object.
// prop is a Properties object containing the registration options.
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotifictaion(prop);
...
// Attach the listener to the registration.
// Note: DCNListener is a custom listener and not a predefined or standard 
// lsiener
DCNListener list = new DCNListener();
dcr.addListener(list);
...

Deleting a Registration

You need to explicitly unregister a registration to delete it from the server and release the resources in the driver. You can unregister a registration using a connection different from one that was used for creating it. To unregister a registration, you can use the unregisterDatabaseChangeNotification method defined in oracle.jdbc.OracleConnection.

You must pass the DatabaseChangeRegistration object as a parameter to this method. This method deletes the registration from the server and the driver and closes the listener socket.

If the registration was created outside of JDBC, say using PL/SQL, then you need to pass the registration ID instead of the DatabaseChangeRegistration object. The method will delete the registration from the server, however, it does not free any resources in the driver.

Example

Example 29-2 illustrates how to use the Database Change Notification feature. In this example, the SCOTT user is connecting to the database. Therefore in the database you need to grant the following privilege to the user:

grant change notification to scott;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
 
public class DBChangeNotification
{
  static final String USERNAME= "scott";
  static final String PASSWORD= "tiger";
  static String URL;
  
  public static void main(String[] argv)
  {
    if(argv.length < 1)
    {
      System.out.println("Error: You need to provide the URL in the first argument.");
      System.out.println("  For example: > java -classpath .:ojdbc5.jar DBChangeNotification \"jdbc:oracle:thin:
@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourhost.yourdomain.com)(PORT=1521))(CONNECT_DATA=
(SERVICE_NAME=yourservicename)))\"");
 
      System.exit(1);
    }
    URL = argv[0];
    DBChangeNotification demo = new DBChangeNotification();
    try
    {
      demo.run();
    }
    catch(SQLException mainSQLException )
    {
      mainSQLException.printStackTrace();
    }
  }
 
  void run() throws SQLException
  {
    OracleConnection conn = connect();
      
    // first step: create a registration on the server:
    Properties prop = new Properties();
    
    // if connected through the VPN, you need to provide the TCP address of the client.
    // For example:
    // prop.setProperty(OracleConnection.NTF_LOCAL_HOST,"14.14.13.12");
 
    // Ask the server to send the ROWIDs as part of the DCN events (small performance
    // cost):
    prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");
 
    // The following operation does a roundtrip to the database to create a new
    // registration for DCN. It sends the client address (ip address and port) that
    // the server will use to connect to the client and send the notification
    // when necessary. Note that for now the registration is empty (we haven't registered
    // any table). This also opens a new thread in the drivers. This thread will be
    // dedicated to DCN (accept connection to the server and dispatch the events to 
    // the listeners).
    DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);
 
    try
    {
      // add the listenerr:
      DCNDemoListener list = new DCNDemoListener(this);
      dcr.addListener(list);
       
      // second step: add objects in the registration:
      Statement stmt = conn.createStatement();
      // associate the statement with the registration:
      ((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
      ResultSet rs = stmt.executeQuery("select * from dept where deptno='45'");
      while (rs.next())
      {}
      String[] tableNames = dcr.getTables();
      for(int i=0;i<tableNames.length;i++)
        System.out.println(tableNames[i]+" is part of the registration.");
      rs.close();
      stmt.close();
    }
    catch(SQLException ex)
    {
      // if an exception occurs, we need to close the registration in order
      // to interrupt the thread otherwise it will be hanging around.
      if(conn != null)
        conn.unregisterDatabaseChangeNotification(dcr);
      throw ex;
    }
    finally
    {
      try
      {
        // Note that we close the connection!
        conn.close();
      }
      catch(Exception innerex){ innerex.printStackTrace(); }
    }
    
    synchronized( this ) 
    {
      // The following code modifies the dept table and commits:
      try
      {
        OracleConnection conn2 = connect();
        conn2.setAutoCommit(false);
        Statement stmt2 = conn2.createStatement();
        stmt2.executeUpdate("insert into dept (deptno,dname) values ('45','cool dept')",Statement.RETURN_GENERATED_KEYS);
        ResultSet autoGeneratedKey = stmt2.getGeneratedKeys();
        if(autoGeneratedKey.next())
          System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));      
        stmt2.executeUpdate("insert into dept (deptno,dname) values ('50','fun dept')",Statement.RETURN_GENERATED_KEYS);
        autoGeneratedKey = stmt2.getGeneratedKeys();
        if(autoGeneratedKey.next())
          System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));
        stmt2.close();
        conn2.commit();
        conn2.close();
      }
      catch(SQLException ex) { ex.printStackTrace(); }
 
      // wait until we get the event
      try{ this.wait();} catch( InterruptedException ie ) {}
    }
    
    // At the end: close the registration (comment out these 3 lines in order
    // to leave the registration open).
    OracleConnection conn3 = connect();
    conn3.unregisterDatabaseChangeNotification(dcr);
    conn3.close();
  }
  
  /**
   * Creates a connection the database.
   */
  OracleConnection connect() throws SQLException
  {
    OracleDriver dr = new OracleDriver();
    Properties prop = new Properties();
    prop.setProperty("user",DBChangeNotification.USERNAME);
    prop.setProperty("password",DBChangeNotification.PASSWORD);
    return (OracleConnection)dr.connect(DBChangeNotification.URL,prop);
  }
}
/**
 * DCN listener: it prints out the event details in stdout.
 */
class DCNDemoListener implements DatabaseChangeListener
{
  DBChangeNotification demo;
  DCNDemoListener(DBChangeNotification dem)
  {
    demo = dem;
  }
  public void onDatabaseChangeNotification(DatabaseChangeEvent e)
  {
    Thread t = Thread.currentThread();
    System.out.println("DCNDemoListener: got an event ("+this+" running on thread "+t+")");
    System.out.println(e.toString());
    synchronized( demo ){ demo.notify();}
  }
}

This code will also work with Oracle Database 10g release 2 (10.2). This code uses "table registration". That is, when you register a SELECT query, what you register is the name of the tables involved and not the query itself. In other words you might select one single row of a table and if another row is updated, you will be notified although the result of your query has not changed.

When using Oracle Database 11g, you can use a different option, the "query registration" with finer granularity. This can be done by setting the DCN_QUERY_CHANGE_NOTIFICATION option.

In this example, if you leave the registration open instead of closing it, then the database change notification thread continues to run. Now if you run a DML query that changes the SCOTT.DEPT table and commit it, say from SQL*Plus, then the java program prints the notification.