Oracle Database Commands and Practical Examples

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

Multitenant Example

Multitenant Dataguard: Create multitenant database on priamry and standby database using FILE_NAME_CONVERT parameter

Multitenant - Performing Flashback for PDB using restorepoint

Multitenant - Enable Flashback on CDB and perform flashback on CDB Common User

Multitenant - Performing Backup and Restore on Pluggable database

Multitenant - Performing RMAN Recovery from non-SYSTEM Root Datafile Loss on CDB

Multitenant - Granting the Privileges as Common or Local on CDB and PDB

Multitenant -Creating the Common and Local Roles on CDB and PDB users

Multitenant- Create Local Users on current PDB

Multitenant - Create Common Users in CDB and PDB

Multitenant - Refreshable PDB Creation from local to remote CDB

Multitenant- Relocating existing PDB from Local CDB to Remote CDB

Multitenant- Creating PDB Using the DBMS_PDB Package from Non-CDB

Multitenant - Create PDB from NON CDB database using DBLINK

Multitenant - Unplugged and Plugged database with Different GUID NO copy CLONE method

Multitenant- PDB OPEN STATE 12.2 release

Multitenant - Creating PDB from seed by mapping names of existing files to new file names destination

Multitenant- Create PDB using db links from remote PDB

Multitenant - Unplugged and Plugged database with same GUID copy method

Multitenant - Drop PDB database

Multitenant- PDB open database commands

Multitenant - Triggers 12.1.0.2.0

Multitenant - Unplugged and Plugged database with Different GUID NO copy CLONE method

Multitenant - Unplugged and Plugged database with Different GUID NO copy method on same host database.

UPLUGGABLE : PDB2

PLUGGABLE :PLM3

NOTE: If we specify CLONE kewords with NOCOPY then with different GUID created and able to create PLUGGABLE DATABASE on same host CDB database.

ERROR :
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

Keywards:

ALTER PLUGGABLE DATABASE PDB2 UNPLUG INTO '/HOME/ORACLE/PDB2.PDB';

Check compatibilty of unpluggable and pluggable on both host or remote database.

DECLARE
2 compatible BOOLEAN := FALSE;
BEGIN
compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(PDB_DESCR_FILE => '/home/oracle/pdb3.xml');
if compatible then
DBMS_OUTPUT.PUT_LINE('It is compatible');
else
DBMS_OUTPUT.PUT_LINE('It is NOT compatible');
end if;
END;
/

CREATE PLUGGABLE DATABASE PLM3 AS CLONE USING '/HOME/ORACLE/PDB2.PDB' COPY;

EXAMPLE :

SELECT NAME FROM V$DATAFILE WHERE CON_ID = (SELECT CON_ID FROM V$PDBS WHERE NAME='PDB2');

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLM1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PLM2 READ WRITE NO
SQL> SELECT NAME FROM V$DATAFILE WHERE CON_ID = (SELECT CON_ID FROM V$PDBS WHERE NAME='PDB2');

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/PDB2/system01.dbf
/u01/app/oracle/oradata/SMRCDB/PDB2/sysaux01.dbf
/u01/app/oracle/oradata/SMRCDB/PDB2/undotbs01.dbf

SQL> ALTER SESSION SET CONTAINER=PDB2;

Session altered.

SQL> SELECT ROUND(SUM(BYTES)/1024/1024,2) SPACE_IN_MB FROM DBA_SEGMENTS;

SPACE_IN_MB
-----------
585.75

SQL>
ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/home/oracle/pdb2.pdb';

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Session altered.

SQL> ALTER PLUGGABLE DATABASE PDB2 close;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/home/oracle/pdb2.pdb';

Pluggable database altered.

SQL> host ls -alh /home/oracle/pdb2.pdb
-rw-r--r-- 1 oracle oinstall 161M Aug 7 19:59 /home/oracle/pdb2.pdb

SQL>
CREATE PLUGGABLE DATABASE PLM3 USING '/home/oracle/pdb2.pdb' COPY;

SQL> CREATE PLUGGABLE DATABASE PLM3 USING '/home/oracle/pdb2.pdb' COPY;
CREATE PLUGGABLE DATABASE PLM3 USING '/home/oracle/pdb2.pdb' COPY
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

SQL>
CREATE PLUGGABLE DATABASE pdb6 AS CLONE USING '/home/oracle/pdb5.pdb' COPY;

CREATE PLUGGABLE DATABASE PLM3 AS CLONE USING '/home/oracle/pdb2.pdb' COPY;

SQL> CREATE PLUGGABLE DATABASE PLM3 AS CLONE USING '/home/oracle/pdb2.pdb' COPY;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE PLM3 open;

Pluggable database altered.

SQL> col pdb_name format a5
SQL> SELECT PDB_NAME, STATUS FROM CDB_PDBS WHERE PDB_NAME IN ('PDB2','PLM3');

PDB_N STATUS
----- ----------
PDB2 UNPLUGGED
PLM3 NORMAL

SQL> SELECT OPEN_MODE FROM V$PDBS WHERE NAME IN ('PDB2','PLM3');

OPEN_MODE
----------
MOUNTED
READ WRITE

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLM1 READ WRITE NO
4 PDB2 MOUNTED
5 PLM2 READ WRITE NO
7 PLM3 READ WRITE NO
SQL> SELECT GUID FROM V$PDBS WHERE NAME='PLM3';

GUID
--------------------------------
AC4B491CE9DAB663E0534938A8C039B1

SQL> SELECT GUID FROM V$PDBS WHERE NAME='PDB2';

GUID
--------------------------------
AC217FDB78BE1A68E0534938A8C0C30E

HOME

Saturday 8 August 2020

Multitenant- PDB open database commands

Multitenant Example

Multitenant - Unplugged and Plugged database with Different GUID NO copy CLONE method