Oracle Database Commands and Practical Examples

Multitenant - Drop PDB database

Always close pdb before dropping otherwise it will through error ORA-65025

KEYWORDS:

DROP PLUGGABLE DATABASE PDB3 INCLUDING DATAFILES;

DROP PLUGGABLE DATABASE PDB3 KEEP DATAFILES;

EXAMPLE:

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> DROP PLUGGABLE DATABASE PDB3 including datafiles;
DROP PLUGGABLE DATABASE PDB3 including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB3 is not closed on all instances.

SQL> alter PLUGGABLE DATABASE PDB3 close immediate;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 MOUNTED
SQL> DROP PLUGGABLE DATABASE PDB3 including datafiles;

Pluggable database dropped.

SQL>

Multitenant- PDB OPEN STATE 12.2 release

After opening CDT root database by default all PDB are in the mount state , In 12.2 release we have option to make PDB in desired state

KEYWARDS:

ALTER PLUGGABLE DATABASE (pdb_name) SAVE STATE;

ALTER PLUGGABLE DATABASE (pdb_name) DISCARD STATE;

Example :

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 19:53:22 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

Pluggable database altered.

SQL> col CON_NAME for a40
SQL> SELECT CON_NAME, STATE FROM DBA_PDB_SAVED_STATES;

CON_NAME STATE
---------------------------------------- --------------
PDB2 OPEN
PDB1 OPEN
PDB3 OPEN

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 8793832 bytes
Variable Size 704643352 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> ALTER PLUGGABLE DATABASE ALL DISCARD STATE;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED

Multitenant- PDB open database commands

PDB - Database command to open and close database.

KEYWARDS-

Open PDB database commands

ALTER PLUGGABLE DATABASE PDB1 OPEN READ ONLY;
ALTER PLUGGABLE DATABASE PDB2 OPEN UPGRADE RESTRICTED;
ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;
ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;
ALTER PLUGGABLE DATABASE PDB3 OPEN READ ONLY RESTRICTED;
ALTER PLUGGABLE DATABASE ALL EXCEPT PDB1 OPEN;

Close PDB database commands

ALTER PLUGGABLE DATABASE PDB1 CLOSE;
ALTER PLUGGABLE DATABASE PDB2 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL CLOSE;

Example:

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 18:27:58 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

5 PDB3 READ WRITE NO

SQL> shut immediate;

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 5 18:28:37 2020

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1795162112 bytes

Fixed Size 8793832 bytes

Variable Size 704643352 bytes

Database Buffers 1073741824 bytes

Redo Buffers 7983104 bytes

Database mounted.

Database opened.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 MOUNTED

4 PDB2 MOUNTED

5 PDB3 MOUNTED

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ ONLY;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ ONLY NO

4 PDB2 MOUNTED

5 PDB3 MOUNTED

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN UPGRADE RESTRICTED;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ ONLY NO

4 PDB2 MIGRATE YES

5 PDB3 MOUNTED

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 MIGRATE YES

5 PDB3 MOUNTED

SQL> ALTER PLUGGABLE DATABASE PDB3 OPEN READ ONLY RESTRICTED;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 MIGRATE YES

5 PDB3 READ ONLY YES

SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB1 OPEN;

Pluggable database altered.

Close database PDB commands:

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MIGRATE YES
5 PDB3 READ ONLY YES
SQL> ALTER PLUGGABLE DATABASE PDB2 CLOSE IMMEDIATE;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 READ ONLY YES
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED

Oracle Database Commands and Practical Examples

HOME

Saturday 8 August 2020

Multitenant - Drop PDB database

Multitenant- PDB OPEN STATE 12.2 release

Multitenant- PDB open database commands