ORACLE12CR1 new features start and close of container database (CDB) and pluggable database (PDB)

The multihomed option introduced in ORACLE12C allows a container database to accommodate multiple separate pluggable databases (PDB). This article explains how to start and close the container database (CDB) and pluggable Database (PDB).

1. Container Database (CDB)

Starting and shutting down the container database is the same as ever. The startup and shutdown commands can be used in sql*plus when connecting to the CDB as an authorized user. The specific syntax is as follows:

STARTUP [Nomount | MOUNT | RESTRICT | upgrade| Force | READ only]

SHUTDOWN [IMMEDIATE | ABORT]

2. Pluggable Database (PDB)

Pluggable databases can be started and closed with the Sql*plus command or the ALTER pluggabledatabase command.

3. Sql*plus command

When you connect to a pluggable database as an authorized user, the following command can be used to start and close the database:

STARTUP Force;

STARTUP OPEN READ WRITE [RESTRICT];

STARTUP OPEN READ only [RESTRICT];

STARTUP UPGRADE;

SHUTDOWN [IMMEDIATE];

Examples are as follows:

STARTUP Force;

SHUTDOWN IMMEDIATE;

STARTUP OPEN READ WRITE RESTRICT;

SHUTDOWN;

STARTUP;

SHUTDOWN IMMEDIATE;

4. ALTER Pluggable DATABASE

The ALTER Pluggable Database command can be used in a CDB or PDB.

When connecting to the PDB as an authorized user, the following command can be used to open and close the current PDB.

ALTER pluggable DATABASE OPEN READ write[restricted] [force];

ALTER pluggable DATABASE OPEN READ only[restricted] [force];

ALTER pluggable DATABASE OPEN upgrade[restricted];

ALTER pluggable DATABASE CLOSE [IMMEDIATE];

Examples are shown below.

ALTER pluggable DATABASE OPEN READ onlyforce;

ALTER pluggable DATABASE CLOSE IMMEDIATE;

ALTER pluggable DATABASE OPEN READ WRITE;

ALTER pluggable DATABASE CLOSE IMMEDIATE;

When connected to a CDB as an authorized user, the following command can be used to turn one or more pdb on and off.

ALTER pluggable database<pdb-name-clause> OPEN READ write[restricted] [force];

ALTER pluggable database<pdb-name-clause> OPEN READ only[restricted] [force];

ALTER pluggable database<pdb-name-clause> OPEN UPGRADE [RESTRICTED];

ALTER pluggable database<pdb-name-clause> CLOSE [IMMEDIATE];

The <pdb-name-clause> clause can be the following value:

One or more comma-delimited pdb names.

The All keyword refers to all the PDB.

The all except keyword, followed by one or more comma-delimited pdb, refers to a PDB subset.

Examples are as follows:

ALTER pluggable DATABASE pdb1, pdb2 OPEN READONLY force;

ALTER pluggable DATABASE pdb1, pdb2 closeimmediate;

ALTER pluggable DATABASE all OPEN;

ALTER pluggable DATABASE all CLOSE IMMEDIATE;

ALTER pluggable DATABASE all EXCEPT pdb1open;

ALTER pluggable DATABASE all EXCEPT pdb1close IMMEDIATE;

5. Pluggable database (PDB) auto-start

The Oracle12.1.0.2 patch set has introduced the ability to maintain the PDB boot state, so it is no longer possible to automatically open the PDB by using the following trigger method.

Oracle12.1.0.2 before the CDB is started, all the PDB are in the loaded state. When CDB starts, there is no default mechanism to automatically start the PDB. You can automatically start some or all of the PDB by establishing a system trigger on the CDB.

CREATE OR REPLACE TRIGGER Open_pdbs

Afterstartup on DATABASE

BEGIN

EXECUTE IMMEDIATE ' ALTER pluggabledatabase all OPEN ';

END Open_pdbs;

/

If you do not want to start all of the PDB, you can modify the trigger by customizing it.

6. Keep the PDB in the boot state (after 12.1.0.2)

The Oracle12.1.0.2 patch set allows the PDB to remain up and running when the CDB restarts. This can be achieved through the Alterpluggable database command.

We can observe the whole process of the CDB restart. Note that the PDB is in read-write mode before rebooting, but is in load mode after a reboot.

SELECT name, open_mode from V$pdbs;

NAME Open_mode

------------------------------ ----------

Pdb$seed READ only

PDB1 READWRITE

PDB2 READWRITE

Sql>

SHUTDOWN IMMEDIATE;

STARTUP;

SELECT name, open_mode from V$pdbs;

NAME Open_mode

------------------------------ ----------

Pdb$seed READONLY

PDB1 Mounted

PDB2 Mounted

Sql>

Next, we open two PDBs, but only save the PDB1 state.

ALTER pluggable DATABASE pdb1 OPEN;

ALTER pluggable DATABASE pdb2 OPEN;

ALTER pluggable DATABASE pdb1 SAVE state;

View Dba_pdb_saved_states displays information about the container's saved state.

COLUMN con_name FORMAT A20

COLUMN instance_name FORMAT A20

SELECT Con_name, instance_name, state fromdba_pdb_saved_states;

Con_name instance_name State

-------------------- ----------------------------------

PDB1 CDB1 OPEN

Sql>

A different result will appear when restarting CDB.

SELECT name, open_mode from V$pdbs;

NAME Open_mode

------------------------------ ----------

Pdb$seed READONLY

PDB1 READWRITE

PDB2 READWRITE

Sql>

SHUTDOWN IMMEDIATE;

STARTUP;

SELECT name, open_mode from V$pdbs;

NAME Open_mode

------------------------------ ----------

Pdb$seed READONLY

PDB1 READWRITE

PDB2 Mounted

Sql>

The saved state can be discarded by the following command.

ALTER pluggable DATABASE pdb1 DISCARD state;

COLUMN con_name FORMAT A20

COLUMN instance_name FORMAT A20

SELECT Con_name, instance_name, state fromdba_pdb_saved_states;

No rows selected

Sql>

7. Note:

Ø only if the container is in read-only or read-write mode, the state can be saved and explicitly in view dba_pdb_saved_states. When you run an alter pluggable DATABASE on a container that is loading state ... SAVE State command, neither error nor record status, because this is the default state after the CDB restart.

Ø Like other examples of the ALTER pluggabledatabase command, PDBs can be listed separately, either as a comma-delimited PDB column, or with all or all except keywords.

In a Ørac environment, you can increase the instances clause. A single instance, a comma-delimited instance column, all or all except keywords can be written in this clause. Regardless of the instances clause, the Save/discard State command affects only the current instance.

ORACLE12CR1 new features start and close of container database (CDB) and pluggable database (PDB)