Managing Pluggable Databases from Pluggable as well as Container Databases

Published by Jignesh Jethwa
Oracle 12c Logo

Following SQL* Plus are used to start/stop a pluggable databases.

Note:

  • Priviledged user must be use to connect pluggable database.
  • I will simulate whole pluggable database management with the help of HR & SALES pluggable databases.

 

[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> show con_name

CON_NAME
——————————
CDB$ROOT
SQL> alter session set container=hr;

Session altered.

SQL> show con_name

CON_NAME
——————————
HR

// Startup force.
SQL> startup force;

Pluggable Database opened.

SQL> select name,open_mode from v$pdbs where name=’HR’;

NAME OPEN_MODE
—————————— ———-
HR READ WRITE

// STARTUP pluggable database with READ WRITE:
SQL> shutdown immediate;

SQL> startup open read write;
Pluggable Database opened.
SQL> select name,open_mode from v$pdbs where name=’HR’;

NAME OPEN_MODE
—————————— ———-
HR READ WRITE

// STARTUP pluggable database with READ WRITE RESTRICT:
SQL> shutdown immediate;

SQL> startup open read write restrict;
Pluggable Database opened.

SQL> select name,open_mode, restricted from v$pdbs where name=’HR’;

NAME OPEN_MODE RESTRICTED
—————————— ———- ——————–
HR READ WRITE YES

// STARTUP pluggable database with READ ONLY:
SQL> shutdown immediate;
SQL> startup open read only;
Pluggable Database opened.

SQL> select name,open_mode from v$pdbs where name=’HR’;

NAME OPEN_MODE
—————————— ———-
HR READ ONLY

// STARTUP pluggable database with READ ONLY RESTRICT:
SQL> shutdown immediate;
SQL> startup open read only restrict;
Pluggable Database opened.

SQL> select name,open_mode, restricted from v$pdbs where name=’HR’;

NAME OPEN_MODE RESTRICTED
—————————— ———- ——————–
HR READ ONLY YES

// STARTUP pluggable database with UPGRADE:
SQL> shutdown immediate;
SQL> startup upgrade;
Pluggable Database opened.

ALTER PLUGGABLE DATABASE commands:

Note: We can issue ALTER PLUGGABLE DATABASE command from Container as well as Pluggable databases.

// ALTER pluggable database open:
SQL> shutdown immediate;
SQL> alter pluggable database open;

Pluggable database altered.

// ALTER pluggable database open with READ WRITE mode:
SQL> alter pluggable database open read write;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs where name=’HR’;

NAME OPEN_MODE
—————————— ———-
HR READ WRITE

// ALTER pluggable database open with READ WRITE RESTRICTED mode:
SQL> shutdown immediate;
SQL> alter pluggable database open read write restricted;

Pluggable database altered.

SQL> select name,open_mode, restricted from v$pdbs where name=’HR’;

NAME OPEN_MODE RESTRICTED
—————————— ———- ——————–
HR READ WRITE YES

// ALTER pluggable database open with READ ONLY mode:
SQL> alter pluggable database open read only;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs where name=’HR’;

NAME OPEN_MODE
—————————— ———-
HR READ ONLY

// ALTER pluggable database open with READ ONLY RESTRICTED mode:
SQL> alter pluggable database open read only restricted;

Pluggable database altered.

SQL> select name,open_mode, restricted from v$pdbs where name=’HR’;

NAME OPEN_MODE RESTRICTED
—————————— ———- ——————–
HR READ ONLY YES

// ALTER pluggable database open with UPGRADE mode:
SQL> shutdown immediate;
SQL> alter pluggable database open upgrade;

Pluggable database altered.

// ALTER pluggable database CLOSE:
SQL> alter pluggable database close;

Pluggable database altered.

// ALTER pluggable database CLOSE IMMEDIATE:
SQL> startup
SQL> alter pluggable database close immediate;

Pluggable database altered.

Managing Pluggable databases from Container databases.

Note: We can also OPEN, CLOSE multiple pluggable databases while connected to Container databases.

Managing PDB from CDB:
[oracle@12c ~]$ sqlplus “/ as sysdba”
SQL> show con_name

CON_NAME
——————————
CDB$ROOT

// ALTER pluggable database[s] open in READ WRITE mode:
SQL> alter pluggable database hr open read write;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs where name=’HR’;

NAME OPEN_MODE
—————————— ———-
HR READ WRITE

OR

SQL> alter pluggable database hr close immediate;
SQL> alter pluggable database hr,sales open read write;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs where name in (‘HR’,’SALES’);

NAME OPEN_MODE
—————————— ———-
HR READ WRITE
SALES READ WRITE

// ALTER pluggable database[s] open in READ WRITE RESTRICTED mode:
SQL> alter pluggable database hr,sales close immediate;
SQL> alter pluggable database hr open read write restricted;

Pluggable database altered.

OR

SQL> alter pluggable database hr,sales open read write restricted;

Pluggable database altered.

SQL> select name,open_mode, restricted from v$pdbs where name in (‘HR’,’SALES’);

NAME OPEN_MODE RES
—————————— ———- —
HR READ WRITE YES
SALES READ WRITE YES

// ALTER pluggable database[s] open in READ ONLY mode:
SQL> alter pluggable database hr open read only;

Pluggable database altered.

OR

SQL> alter pluggable database hr,sales open read only;

Pluggable database altered.

SQL> select name,open_mode, restricted from v$pdbs where name in (‘HR’,’SALES’);

NAME OPEN_MODE RES
—————————— ———- —
HR READ ONLY NO
SALES READ ONLY NO

// ALTER pluggable database[s] open in READ ONLY RESTRICTED mode:
SQL> alter pluggable database hr,sales close;
SQL> alter pluggable database hr open read only restricted;

Pluggable database altered.

OR

SQL> alter pluggable database hr,sales open read only restricted;

Pluggable database altered.

SQL> select name,open_mode, restricted from v$pdbs where name in (‘HR’,’SALES’);

NAME OPEN_MODE RES
—————————— ———- —
HR READ ONLY YES
SALES READ ONLY YES

// ALTER pluggable database[s] open in UPGRADE mode:
SQL> alter pluggable database hr open upgrade;

Pluggable database altered.

OR

SQL> alter pluggable database hr,sales open upgrade;

Pluggable database altered.

// ALTER pluggable database[s] close immediate:
SQL> alter pluggable database hr close immediate;

Pluggable database altered.

OR

SQL> alter pluggable database hr,sales close immediate;

Pluggable database altered.

// We can also open all pluggable databases as below:
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
HR READ WRITE
SALES READ WRITE

// We can also close all pluggable databases as below:
SQL> alter pluggable database all close;

Pluggable database altered.

OR

SQL> alter pluggable database all close immediate;

Pluggable database altered.
Cheers!! Now you can manage your pluggable database in any sense. Enjoy 🙂

 

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply

%d bloggers like this: