Renaming PDB

 Renaming the PDBS is very easy in the CDB / PDB architure. 

With 18c onwards , we can have 3 pdbs with the regular oracle license. 

Please refer to the documentation for further details. 

In my example , I have 3 pdbs. We will be renaming one of the pdbs ( from PDB1 to  pdb_lx). 

Here are my list of PBS

SQL> select name  from v$pdbs;

NAME

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

PDB$SEED

PDB1

PDB2

PDB3

Let us confirm if we are in the correct container. 

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_id

CON_ID

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

3

SQL> show con_name

CON_NAME

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

PDB1

PDB needs to be restricted state for it to be renamed. 

So , we will close and reopen in the restricted mode. 

SQL> alter pluggable database rename global_name to pdb_lx;

alter pluggable database rename global_name to pdb_lx

*

ERROR at line 1:

ORA-65045: pluggable database not in a restricted mode

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> alter pluggable database PDB1 open restricted;

Pluggable database altered.

SQL> alter pluggable database rename global_name to pdb_lx;

Pluggable database altered.

Just to confirm , we will select the old name for the PDB. 

As expected , this will fail. 

SQL> alter session set container=PDB1;

ERROR:

ORA-65011: Pluggable database PDB1 does not exist.

Even though the renaming of the PDB is successful , the underlying file structure still has pdb1. 

$ ls -lrt /ora01/oradata/CDBLX/pdb1

total 640088

-rw-r-----. 1 oracle oinstall  37756928 Apr 19 11:32 temp01.dbf

-rw-r-----. 1 oracle oinstall   5251072 Apr 19 11:39 users01.dbf

-rw-r-----. 1 oracle oinstall 104865792 Apr 19 11:39 undotbs01.dbf

-rw-r-----. 1 oracle oinstall 283123712 Apr 19 11:39 system01.dbf

-rw-r-----. 1 oracle oinstall 262152192 Apr 19 11:39 sysaux01.dbf

We will create directory and move the datafiles ONLINE ( hey , we are in 19c) . 

mkdir -p / /ora01/oradata/CDBLX/pdb_lx

$ ls -lrt /ora01/oradata/CDBLX/pdb_lx/

total 0

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 19 11:47:46 2021

Version 19.3.0.0.0

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

SQL> alter session set container=pdb_lx;

Session altered.

SQL>  alter database move datafile '/ora01/oradata/CDBLX/pdb1/undotbs01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/undotbs01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/sysaux01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/sysaux01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/system01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/system01.dbf';

Database altered.

SQL> alter database move datafile '/ora01/oradata/CDBLX/pdb1/users01.dbf' to '/ora01/oradata/CDBLX/pdb_lx/users01.dbf';

Database altered.

We will drop the temp tablespace datafile from the old directory and recreate it in the new directory.

SQL> alter database tempfile '/ora01/oradata/CDBLX/pdb1/temp01.dbf' drop including datafiles;

Database altered.

SQL> alter tablespace TEMP add tempfile '/ora01/oradata/CDBLX/pdb_lx/temp01.dbf' size 1G reuse;

Tablespace altered.

Let us confirm the contents of the  directory structure

SQL> ! ls -lrt /ora01/oradata/CDBLX/pdb1/

total 0

SQL> ! ls -lrt /ora01/oradata/CDBLX/pdb_lx

total 641056

-rw-r-----. 1 oracle oinstall  104865792 Apr 19 11:48 undotbs01.dbf

-rw-r-----. 1 oracle oinstall  262152192 Apr 19 11:49 sysaux01.dbf

-rw-r-----. 1 oracle oinstall  283123712 Apr 19 11:49 system01.dbf

-rw-r-----. 1 oracle oinstall    5251072 Apr 19 11:49 users01.dbf

-rw-r-----. 1 oracle oinstall 1073750016 Apr 19 11:49 temp01.dbf

Let us say close the PDB and open it in the normal mode. 

SQL> alter pluggable database pdb_lx close;

Pluggable database altered.

SQL> alter pluggable database pdb_lx open;

Pluggable database altered.

We are back in business with the new name for the PDB. 

For the databases in non-CDB architecture , we will be using DBNEWID (nid) utility. 

Auto restart of PDB in multi tenant ( CDB) database

Most of the time , when we start/restart  the container database ( CDB) , we would like one ot more pluggable database ( PDBs) to start with the CDB.

In 12c release 1 , the option was to use the trigger (AFTER STARTUP ON DATABASE )  to open the pdbs .  In 12c release 2 , the other option was to save the current state  , so that upon restart of the CDB , the PDB initializes to the saved state ( in our example , READ_WRITE). 


Here is an example. 

As it can be seen below , the PDB is not in 'OPEN' state once the CDB is restarted. 


SQL> col name format a20

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 12468584 bytes
Variable Size            2751467160 bytes
Database Buffers         7549747200 bytes
Redo Buffers               21082112 bytes
Database mounted.
Database opened.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              MOUNTED


Now , let us open the PDB , confirm its status and save the state. 
The view 'dba_pdb_saved_states' shows the saved state of all PDBs.

SQL> alter pluggable database PDB_SIS open ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> alter pluggable database PDB_SIS save state ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              READ WRITE

SQL> col con_name format a20
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------

PDB_SIS               OPEN

Now , let us bounce the CDB and look at the status of the PDB. Now , the PDBs  re-initalized to the saved state. 

SQL>  shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1.0335E+10 bytes

Fixed Size                 12468584 bytes

Variable Size            2751467160 bytes

Database Buffers         7549747200 bytes

Redo Buffers               21082112 bytes

Database mounted.

Database opened.

SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE

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

PDB_SIS               OPEN

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE

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

PDB$SEED             READ ONLY

PDB_SIS               READ WRITE

Thanks !

Creating Common User in Oracle 12c

Oracle has introduced multi-tenant architecture in 12c. With this , we will have an instance ( Single Instance) , multiple instances ( RAC) will be hosting multiple pluggable databases ( PDBs) with in a  container database  ( CDB). 

Please note that the  CDB/PDB is extra licensing option . With the regular license , we can have a CDB with only one PDB. 

Accordingly , there are changes to how users are maintained in 12c.  There are two type of users. 

a) Common User  

b) Local User 

In short , common user is for all PDBs and Local user is local to a PDB. 


There are few rules / restrictions in creating a common user. 
One of them being , the username has to be prefixed with ( c##) . 
However  , this can be circumvented . But I would recommend against against this practice, as this is not ( likely ) supported by oracle . 

Here is an example . 


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user c##zahir identified by ZahirPassword container=all;

User created.


As you can see, common user ( c##zahir) was created sucessfully . 

Let us try , by removing c##. 

SQL> create user zahirm identified by ZahirPassword container=all;
create user zahirm identified by ZahirPassword container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


Let us set the UNDOCUMENTED parameter to true . 
Being this is undocumented parameter ; as like many Oracle professionals , 
I would not recommend with meddling  with these  parameters unless otherwise directed by Oracle support. 

Note : This parameter also needed , when you need to create a CDB from sqlplus. 

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> create user zahirm identified by ZahirPassword container=all;

User created.

Voila . The user was created . 


Comments Welcome. 

Renaming PDB

Oracle 12c has introduced CDB/ PDB architecture . Prior to 12c , Only one database can  be serviced by an instance ( in a Single Instance ) / by instances ( in RAC) . In other words , no more than one databases can be serviced by an instance(s). 

In Oracle 12c, we can have ( pluggable) databases that are  contained with in a  (container) database. Container database will not have user data , where as the pluggable database will have the user data. This is almost synonymous with Sybase / MS SQL Server or DB2.  Loosely speaking , we can equate Container database as to Combination of System (master , msdb , model and resource ) databases in Sybase / MS-SQL Server. 

Note : With the standard license , we can one CDB and one PDB . We would need additional license , if we are planning to have more than one PDB. 

Let us say , we created a database with DBCA and asked for 2 PDBs with the pdb_docs, then we will have a CDB ( whatever we have given ) , PDB seed  and two pdbs with pdb_docs1 and pdb_docs2. 

As you can see , OXYZ12DB is a the containter database .

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:37:02 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name , CDB  from v$database;

NAME      CDB
--------- ---
OXYZ12DB  YES

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB$SEED                       READ ONLY  NO
PDB_DOCS1                      READ WRITE YES

PDB_DOCS2                      READ WRITE NO

Now , let us see how we can change the name of pdb_docs2 to pdb_noncredit .
We need to be connected the correct PDB and it needs to be in restricted mode. 

C:\>sqlplus sys@pdb_docs2 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:50:16 2015

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name , open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------

PDB_DOCS2                      READ WRITE


We need to open the PDB in restricted mode , so we can its change its name. 

SQL>  alter pluggable database pdb_docs2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb_docs2 open restricted;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_DOCS2                      READ WRITE YES



SQL> alter pluggable database pdb_docs2  rename global_name to pdb_noncredit;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_NONCREDIT                  READ WRITE YES

Here we go. Now the PDB's name is changed to PDB_NONCREDIT.

Make sure that you change your tnsnames.ora to reflect the new name . 

Comments welcome.