Database Startup and Shutdown through JDBC

Starting in 11gR1 you have the ability to stop and start the database through a JDBC connection.

In order to startup and shutdown the database through JDBC you must use the INTERNAL_LOGON connection property and connect to the database as SYSDBA or SYSOPER.

If you are using the JDBC thin driver to connect the database must be configured to use a password file and the parameter REMOTE_LOGIN_PASSWORDFILE needs to be set to EXCLUSIVE. If you are using the JDBC OCI driver and connect as SYSDBA or SYSOPER locally, you do not need password file.

Database Startup

The oracle.jdbc.OracleConnection interface provides the startup method. The startup method supports the following database startup options.

FORCE – If the database is currently running it is shutdown in abort mode first before starting.
NO_RESTRICTION – Starts up the database with no restrictions
RESTRICT – Starts up the database but only access to users with both the CREATE SESSION and RESTRICTED SESSION privileges.

The startup options are defined in the oracle.jdbc.OracleConnection.DatabaseStartupMode class.

In order to startup a database that is down, you must connect as SYSDBA or SYSOPER and set the connection property PRELIM_AUTH to true. Keep in mind that while connected with PERLIM_AUTH set to true you will only be able to start the database. You will not be able to issue any SQL statements.

The startup method only starts the instance; it does not mount or open the database. In order to mount and open the database you have to reconnect as SYSDBA or SYSOPER without the PRELIM_AUTH mode.

Below is a code example of starting a database that is currently down.

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;


public class JDBCStartup {
 
  public static void main(String[] args) {
    try {
      // Set the connection properties.
      // required: SYSDBA and PRELIM_AUTH = true
      Properties prop = new Properties();
      prop.setProperty("user","sys");
      prop.setProperty("password","password");
      prop.setProperty("internal_logon","sysdba");
      prop.setProperty("prelim_auth","true");
      
      OracleDataSource ods = new OracleDataSource();
      ods.setConnectionProperties(prop);
      ods.setURL(/java/database-startup-and-shutdown-through-jdbc/"jdbc:oracle:thin:@/ora1:1521/orcl");
      OracleConnection ocon = (OracleConnection)ods.getConnection();
      System.out.println("Connected");
      
      // startup the database
      ocon.startup(OracleConnection.DatabaseStartupMode.NO_RESTRICTION);
      System.out.println("Instance started");
      ocon.close();
      ods.close();
      
      // At this time only the instance is started. The database is
      // not mounted or opened. You must reconnect as SYSDBA without
      // the PRELIM_AUTH
      prop.clear();
      prop.setProperty("user","sys");
      prop.setProperty("password","password");
      prop.setProperty("internal_logon","sysdba");
      
      ods = new OracleDataSource();
      ods.setConnectionProperties(prop);
      ods.setURL(/java/database-startup-and-shutdown-through-jdbc/"jdbc:oracle:thin:@/ora1:1521/orcl");
      ocon = (OracleConnection)ods.getConnection();
      System.out.println("Reconnected");
      
      // mount and open the database
      Statement stmt = ocon.createStatement();
      stmt.execute("alter database mount");
      stmt.execute("alter database open");
      System.out.println("Database mounted and open");
      stmt.close();
      ocon.close();
      ods.close();
    } catch(SQLException e) {
      System.out.println(e.getMessage());
    }
    
  }
}

If you get the following error:

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

The error is because the database is not statically registered with the listener. Database versions 8i and higher dynamically register with the local listener by default. If your database is down and is not statically registered with the listener you should see the following when listing the services supported by the listener.

[oracle@ora1 admin]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-APR-2010 12:35:11

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@ora1 admin]$

If the listener is statically registered you should see the following:

[oracle@ora1 admin]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-APR-2010 12:39:29

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
[oracle@ora1 admin]$

For instructions on how to statically register your database see the post Configure Static Service Information for a Database

Database Shutdown

The oracle.jdbc.OracleConnection interface provides the shutdown method. The shutdown method supports the following database shutdown options.

ABORT – Does not wait for current calls to complete or existing connections to end
CONNECT – Waits for existing connections to complete while blocking new connections.
FINAL – Shuts down the database
IMMEDIATE – Does not wait for current calls to complete or existing connections to end
TRANSACTIONAL – Waits for existing transactions to complete while blocking new connections
TRANSACTIONAL_LOCAL – Waits for local transactions to end while blocking new local transactions

The shutdown options are defined in the oracle.jdbc.OracleConnection.DatabaseShutdownMode class.

As with the startup method, you need to connect as SYSDBA or SYSOPER in order to shutdown a database.

The call to the shutdown method with CONNECT, IMMEDIATE, TRANSACTIONAL or TRANSACTIONAL_LOCAL must be followed by a second call to the shutdown method with FINAL option used. These options only stop the instance, they do not close or dismount the database. For example, the following will cause crash recovery to run on the next startup.

      ocon.shutdown(OracleConnection.DatabaseShutdownMode.IMMEDIATE);
      ocon.shutdown(OracleConnection.DatabaseShutdownMode.FINAL);

You can see from the alert log section below that the database was not closed or dismounted.

Wed Apr 07 13:45:03 2010
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC
Wed Apr 07 13:45:04 2010
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 4
All dispatchers and shared servers shutdown
Wed Apr 07 13:47:15 2010
License high water mark = 4
USER (ospid: 15300): terminating the instance
Instance terminated by USER, pid = 15300
Wed Apr 07 13:47:23 2010

A call shutdown with the ABORT option is the same as shutdown abort and does not need to be followed by a second call of the shutdown method with the FINAL option.

To avoid crash recovery on the next startup when using the CONNECT, IMMEDIATE, TRANSACTIONAL and TRANSACTIONAL_LOCAL options, issue statements to close and dismount the database.

    ocon.shutdown(OracleConnection.DatabaseShutdownMode.IMMEDIATE);
      Statement stmt = ocon.createStatement();
      stmt.execute("alter database close normal");
      stmt.execute("alter database dismount");
      ocon.shutdown(OracleConnection.DatabaseShutdownMode.FINAL);

Below is a complete example of how to shutdown a database using JDBC.

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;


public class JDBCShutdown {

  public static void main(String[] args) {
    try {
      // Set the connection properties.
      // required: SYSDBA
      Properties prop = new Properties();
      prop.setProperty("user","sys");
      prop.setProperty("password","password");
      prop.setProperty("internal_logon","sysdba");
      
      OracleDataSource ods = new OracleDataSource();
      ods.setConnectionProperties(prop);
      ods.setURL(/java/database-startup-and-shutdown-through-jdbc/"jdbc:oracle:thin:@/ora1:1521/orcl");
      OracleConnection ocon = (OracleConnection)ods.getConnection();
      System.out.println("Connected");     
      
      // shutdown the database
      ocon.shutdown(OracleConnection.DatabaseShutdownMode.IMMEDIATE);
      System.out.println("Instance stopped");
      Statement stmt = ocon.createStatement();
      stmt.execute("alter database close normal");
      stmt.execute("alter database dismount");
      stmt.close();
      System.out.println("Database closed and dismounted");
      ocon.shutdown(OracleConnection.DatabaseShutdownMode.FINAL);
      ocon.close();
      ods.close();
      System.out.println("Database stopped");
           
    } catch(SQLException e) {
      System.out.println(e.getMessage());
    }

  }
}

Note if you do not close the Statement object before the call to the second shutdown with the FINAL option you will get an ORA-01012 when you close the Statement object.