20. Starting and Stopping Oracle Database

There are two groups of database start up and shut down functions:

Simple usage: oracledb.startup() and oracledb.shutdown()
Flexible usage: connection.startup() and connection.shutdown()

These can be used to control database instances. With the Oracle Database Multitenant architecture, you use these functions on a “CDB” container database instance and then use SQL commands to control the pluggable “PDB” databases.

Note

In this release, database start up and shut down functionality is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

20.1. Simple Database Start Up and Shut Down

The simple methods accept database credentials and perform the requested start up or shut down. Internally a standalone connection with privilege oracledb.SYSOPER is created, used, and then closed. The methods require appropriate connection attributes, which will vary depending with your database access rights, and if you are connecting over TCP or to a local database. A username, password, connection string and mode to specify external authentication can be used.

20.1.1. Simple Start Up

The simple oracledb.startup() method to start up a local database instance, when your operating system user is in the operating system’s oper group, is:

await oracledb.startup( {
    externalAuth: true
});

Start up options can be specified. You can use a database parameter pfile, or indicate that database access should be restricted after start up, or force the database instance to be shut down before restarting it. For example:

await oracledb.startup( {
    externalAuth: true
    },
    {
        force: true,
        restrict: true
        pfile: '/my/path/to/my/pfile.ora'
    }
);

By default when the options are not specified, the database is opened normally, and uses the database parameter file.

To start up a remote database, configure the Oracle Net listener to use static service registration by adding a SID_LIST_LISTENER entry to the database listener.ora file. Starting the database in node-oracledb would then be like:

await oracledb.startup( {
    user: 'sys',
    password: syspw,
    connectString: 'mymachine.example.com/orclcdb'
});

20.1.2. Simple Shut Down

The simple oracledb.shutdown() method to shut down a remote database is:

const syspw = ...  // set syspw to the sys schema password

await oracledb.shutdown( {
    user: "sys",
    password: syspw,
    connectString: "mymachine.example.com/orclcdb"
});

An optional, shutdownMode can be passed, for example to terminate uncommitted transactions and roll back:

await oracledb.shutdown( {
    user: "sys",
    password: syspw,
    connectString: "mymachine.example.com/orclpdb1"
    },
    oracledb.SHUTDOWN_MODE_IMMEDIATE
);

The shut down mode should be one of the constants: oracledb.SHUTDOWN_MODE_ABORT, oracledb.SHUTDOWN_MODE_DEFAULT, oracledb.SHUTDOWN_MODE_IMMEDIATE, oracledb.SHUTDOWN_MODE_TRANSACTIONAL, or oracledb.SHUTDOWN_MODE_TRANSACTIONAL_LOCAL. If a mode is not specified, oracledb.SHUTDOWN_MODE_DEFAULT is used.

20.2. Flexible Database Start Up and Shut Down

The ‘flexible’ functions for starting and stopping databases allow you more control over connection access, for example you can use the oracledb.SYSDBA privilege instead of oracledb.SYSOPER. The functions also let you, for example, do database recovery as part of the database start up.

20.2.1. Flexible Start Up

A connection.startup() example that is equivalent to the first ‘simple’ start up example above is:

connection = await oracledb.getConnection( {
    externalAuth: true
    privilege: oracledb.SYSOPER | oracledb.SYSPRELIM
});

await connection.startup();  // options could be passed, if required

await connection.close();

connection = await oracledb.getConnection( {
    externalAuth: true
    privilege: oracledb.SYSOPER
});

await connection.execute(`ALTER DATABASE MOUNT`);
await connection.execute(`ALTER DATABASE OPEN`);

await connection.close();

The SYSPRELIM privilege is required for the first connection. The connection.startup() method lets you optionally specify a database parameter ‘pfile’, or indicate the database access should be restricted after start up, or force the database instance to be shut down before restarting it.

After calling connection.startup(), you can use your choice of SQL statements, for example to perform database recovery.

20.2.2. Flexible Shut Down

The flexible connection.shutdown() example equivalent to the first ‘simple’ shut down example above is:

connection = await oracledb.getConnection({
    user: "sys",
    password: syspw,
    connectString: "mymachine.example.com/orclcdb",
    privilege: oracledb.SYSOPER
});

await connection.shutdown();  // a shut down mode can be specified, if required

await connection.execute (`ALTER DATABASE CLOSE NORMAL`);
await connection.execute (`ALTER DATABASE DISMOUNT`);

await connection.shutdown(oracledb.SHUTDOWN_MODE_FINAL);

connection.close();

If the connection.shutdown() shutdownMode oracledb.SHUTDOWN_MODE_ABORT is used, then connection.shutdown() does not need to be called a second time.

20.3. Oracle Multitenant Pluggable and Container Databases

You can use the startup() and shutdown() methods on Oracle Multitenant container database instances.

Once a CDB is running, you can connect as a privileged user and execute SQL ALTER PLUGGABLE commands to start or stop PDBs. Similar commands can also be run if you connect directly to a PDB.

For example, when connected to a CDB, you can open the pluggable database in it called ‘orclpdb1’ with:

ALTER PLUGGABLE DATABASE orclpdb1 OPEN

or, to open all PDBs:

ALTER PLUGGABLE DATABASE ALL OPEN

The command:

ALTER PLUGGABLE DATABASE ALL SAVE STATE

can be used so that a subsequent restart of a CDB will automatically open all currently open PDBs.

To close a PDB, you can use a command like ALTER PLUGGABLE DATABASE mypdbname CLOSE.

Refer to the Oracle Database Administrator’s Guide for more options.