Oracle database shutdown and startup via jdbc

While working on the automation of our database setup procedures, we learned that you can shut down and start up an oracle db instance remotely via jdbc. It took some trials and finally reading this blog post to understand how to achive that.

From the oracle java api documentation it is not obvious, that you have to call several methods to achieve the same effect of a shutdown transactional from within sqlplus. You actually should issue four commands as shown in the following code example

   import static oracle.jdbc.OracleConnection.DatabaseShutdownMode.TRANSACTIONAL;

   ...

   OracleConnection connection = connectionHolder.getConnection();
    try {
      connection.shutdown(TRANSACTIONAL);

      Statement stmt = connection.createStatement();
      stmt.execute("alter database close normal");
      stmt.execute("alter database dismount");
      stmt.close();
      connection.shutdown(OracleConnection.DatabaseShutdownMode.FINAL);
    } finally {
      connection.close();
    }

For startup  it is :

    connectionHolder.getPrelimAuthConnection().startup(NO_RESTRICTION);

    Statement statement = connectionHolder.getConnection().createStatement();
    statement.execute("alter database mount");
    statement.close();
 

Both calls require a connection with sysdba or sysoper role. Startup requires a preliminary auth connection. To get one use

  private OracleConnection getPrelimAuthConnection() 

      throws SQLException 

  {
    Properties props = new Properties();
    props.put(USER_KEY, username);
    props.put(PASSWORD_KEY, password);
    props.put(ROLE_KEY, "sysdba");
    props.put(PRELIM_AUTH_KEY, "true");

    OracleConnection newConnection = 

       (OracleConnection) DriverManager.getConnection(connectionString, props);
    return newConnection;
  }