Multitenant – CDB and PDBs

Multitenant CDB and PDBs

Creating new Pluggable Database should be easy and straightforward as much as possible – Oracle claims that this technology can lead to many savings through firing couple useless database administrators who manage databases as a standalone beings. With 12c and multitenant option you can consolidate databases, leverage costs on database administrators, who spend too much time on administration and put sky-rocking charts on one of the walls in your chairman office. Ok, it’s time to check, what multitenant is.

Introduction

Starting point for this article is presented in a table below.

Attribute Value
Software Oracle Linux 6.5 (64 bit for x86)


Oracle Database 12.1.0.2.0 (64 bit for Linux 6.5)


Installation and configuration of this software is described in this article: http://oradistrict.com/?p=11
Databases Two container databases:
tcdb
beecdb
glogin.sql file Add couple new lines inside your login script for sql*plus. Most of the time you really don’t care about time zone in your test database.
set termout off
alter session set nls_timestamp_tz_format = ‘yyyy-mm-dd hh24:mi:ss’;
alter session set nls_timestamp_format = ‘yyyy-mm-dd hh24:mi:ss’;
alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
set linesize 92
set pagesize 250
set termout on
Scripts used in this guide ./mt/container121.sql – list containers (CDB + seed + PDBs) in your CDB
./sap/mysession121.sql – list details about your session by SID
./iad/databaseproperties112.sql – shows database properties (or current PDB)
./mt/cdatabaseproperties121.sql – show all PDBs properties (or current PDB)
./iad/parameter121.sql – list instance parameter active and specified in spfile values
./ts/datatempfiles121 – list of all data and temp files
./do/constraint112 – list of all constraints owned by pointed user
./do/object112.sql – list of all objects owned by pointed user
./mt/pdb12102 – shows PDBs status and logging options
./do/directory112.sql – shows database directories
All scripts can be downloaded from my github project: Oradistrict Toolbox – download, unzip and use trunk directory to start menu via odstart.sql file. Enjoy!
oracle user shell It’s good to use aliases in shell – for example:
alias sql=”sqlplus / as sysdba”
provide easy way to login internally as sysdba. You can put it in your .bash_profile or .profile file.

Why introduce a topic which was described in Oracle Database documentation in details? Just to lie background for my next guides. Real goal is to show how common users and roles works and what you should be aware off.

Creating a PDB from seed, cloning from another PDB or even standaone database – this is what you can find below.

Creating PDB from seed

Easiest way to create new PDB is to use seed pluggable database. This is a template which have been created with your CDB.

Copying from seed is quite easy. Start with a connection to your CDB, directly to root container. To do that just use old way to connect as sysdba – remember to set your environment variables first.

. oraenv
sqlplus / as sysdba

Here is an example were I’ll create pluggable database pipboy. During PDB creation new administrative user called maxstone will be created with one local role assigned to new PDB_DBA role which is common. Quite complex. Default tablespace will be used just like USERS with a standard database – default for all non-common users inside PDB. Convert seed file names to PDB file names – you can check your seed database file names in v$datafile. You won’t find seed datafiles neither in dba_data_files or cdb_data_files – only in v$datafile when considering cdb$root connection. Storage is similar to old quoting feature for accounts but it’s adjusted to meet PDBs requirements – one limit for all PDB datafiles and one limit for shared temporary tablespace usage. Last parameter is for directory objects prefix – you can enable this parameter only during PDB creation. Sounds interesting – I’ll check this parameter in depth later.

I’m going to use tcdb container database in this example.

Full syntax can be found in official documentation.

create pluggable database pipboy
  admin user maxstone identified by MxSt9281
  roles = (dba)
  default tablespace vault13 datafile '/u02/oradata/tcdb/pipboy/vault13_01.dbf' size 16m autoextend on next 16m maxsize 512m
  file_name_convert = ('/u02/oradata/tcdb/pdbseed/','/u02/oradata/tcdb/pipboy/')
  storage (maxsize 2048m max_shared_temp_size 1024m)
  path_prefix = '/u02/oradata/tcdb/pipboy/';

Use ./mt/container121.sql script to check current status of all containers.

start mt/container121
 CID NAME                         DbID Size in MB Open mode  Open time              Res
---- -------------------- ------------ ---------- ---------- ---------------------- ---
   1 CDB$ROOT                918792381          0 READ WRITE 2015-04-13 10:00:28    NO
   2 PDB$SEED               4034246255        845 READ ONLY  2015-04-13 10:00:28    NO
   3 PIPBOY                 3788905833          0 MOUNTED    2015-04-13 11:30:51

Escape from toolbox menu with Ctrl+C and Enter. Open pipboy PDB. In this case commands presented below will produce the same result – when using all as PDB name you can start all mounted PDBs. In our case only pipboy PDB can be opened. Choose one of those commands to start pipboy PDB. Read write is the default mode of opening PDB.

alter pluggable database pipboy open read write;
alter pluggable database pipboy open;
alter pluggable database all open;

Create new terminal session as oracle user and check listener status. Inside the listing you can find a new service which was created in CDB for PDB.

lsnrctl status
Service "pipboy" has 1 instance(s).
  Instance "tcdb", status READY, has 1 handler(s) for this service...

Add new entry in tnsnames.ora for this service.

vi $ORACLE_HOME/network/admin/tnsnames.ora
# tcdb - pluggable databases
PIPBOY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PIPBOY)
    )
  )

Connect as your PDB administrator – Max Stone.

sqlplus maxstone/MxSt9281@pipboy
start sap/mysession121
User Name    OS User        Program                    Service Name Machine
------------ -------------- -------------------------- ------------ ----------------
MAXSTONE     oracle         sqlplus@odone (TNS V1-V3)  pipboy       odone


Container Name
------------------------------------------------
PIPBOY

You can see that you are using service pipboy and you are connected as maxstone local user in container with ID = 3.

You can check your current CDB identifier in sql*plus with show command.

show con_name
CON_NAME
------------------------------
PIPBOY
show con_id
CON_ID
------------------------------
3

Check the same information for your cdb$root internal connection (switch to first ssh session).

start sap/mysession121
User Name    OS User        Program                    Service Name Machine
------------ -------------- -------------------------- ------------ ----------------
SYS          oracle         sqlplus@odone (TNS V1-V3)  SYS$USERS    odone


Container Name
------------------------------------------------
CDB$ROOT
show con_name
CON_NAME
------------------------------
CDB$ROOT
show con_id
CON_ID
------------------------------
1

If you’ve connected to database using internal connection then your service name is ‘SYS$USERS’. Try to connect as a sysdba through listener and check mysession.sql script result:

exit
sqlplus sys@tcdb as sysdba
start sap/mysession121
User Name    OS User        Program                    Service Name Machine
------------ -------------- -------------------------- ------------ ----------------
SYS          oracle         sqlplus@odone (TNS V1-V3)  tcdb         odone


Container Name
------------------------------------------------
CDB$ROOT

You can see different service name – with resource manager you can get even more detailed information about your services. Maybe one day I’ll return to this topic and check, how resource manager can control services in Multitenant architecture.

Going back to my maxstone user session. I would like to check, what are my limits for this PDB and other settings. Use ./mt/cdatabaseproperties121.sql script.

start mt/cdatabaseproperties121
                           Database properties for a PDB:  PIPBOY

Property name            Property value           Property description
------------------------ ------------------------ ------------------------------------------
DBTIMEZONE               +02:00                   DB time zone
DEFAULT_EDITION          ORA$BASE                 Name of the database default edition
DEFAULT_PERMANENT_TABLES VAULT13                  Name of default permanent tablespace
PACE

DEFAULT_TBS_TYPE         SMALLFILE                Default tablespace type
DEFAULT_TEMP_TABLESPACE  TEMP                     Name of default temporary tablespace
DICT.BASE                2                        dictionary base tables version #
DST_PRIMARY_TT_VERSION   18                       Version of primary timezone data file
DST_SECONDARY_TT_VERSION 0                        Version of secondary timezone data file
DST_UPGRADE_STATE        NONE                     State of Day Light Saving Time Upgrade
EXPORT_VIEWS_VERSION     8                        Export views revision #
Flashback Timestamp Time GMT                      Flashback timestamp created in GMT
Zone

GLOBAL_DB_NAME           PIPBOY                   Global database name
MAX_PDB_STORAGE          2048 MB                  Maximum Space Usage of Datafiles and Local
                                                   Tempfiles in Container

MAX_SHARED_TEMP_SIZE     1024 MB                  Maximum Space Usage in Shared Temporary Ta
                                                  blespace

MAX_STRING_SIZE          STANDARD                 MAX_STRING_SIZE paramter used for dictiona
                                                  ry metadata

NLS_CALENDAR             GREGORIAN                Calendar system
NLS_CHARACTERSET         AL32UTF8                 Character set
NLS_COMP                 BINARY                   NLS comparison
NLS_CURRENCY             $                        Local currency
NLS_DATE_FORMAT          DD-MON-RR                Date format
NLS_DATE_LANGUAGE        AMERICAN                 Date language
NLS_DUAL_CURRENCY        $                        Dual currency symbol
NLS_ISO_CURRENCY         AMERICA                  ISO currency
NLS_LANGUAGE             AMERICAN                 Language
NLS_LENGTH_SEMANTICS     BYTE                     NLS length semantics
NLS_NCHAR_CHARACTERSET   AL16UTF16                NCHAR Character set
NLS_NCHAR_CONV_EXCP      FALSE                    NLS conversion exception
NLS_NUMERIC_CHARACTERS   .,                       Numeric characters
NLS_RDBMS_VERSION        12.1.0.2.0               RDBMS version for NLS parameters
NLS_SORT                 BINARY                   Linguistic definition
NLS_TERRITORY            AMERICA                  Territory
NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIMESTAMP_TZ_FORMAT  DD-MON-RR HH.MI.SSXFF AM Timestamp with timezone format
                          TZR

NLS_TIME_FORMAT          HH.MI.SSXFF AM           Time format
NLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZR       Time with timezone format
NO_USERID_VERIFIER_SALT  5A9235A582ACE9E5EEA30CEA
                         D2E1B6AC

OLS_OID_STATUS           0                        OLS OID Status used for Label Security
PATH_PREFIX              /u02/oradata/tcdb/pipboy All paths for objects such as directories
                                                  are relative to this

TDE_MASTER_KEY_ID
WORKLOAD_CAPTURE_MODE                             CAPTURE implies workload capture is in pro
                                                  gress

WORKLOAD_REPLAY_MODE                              PREPARE implies external replay clients ca
                                                  n connect; REPLAY implies workload replay
                                                  is in progress

Initialization parameters for PDB are inherited from cdb$root container. You can stop this behaviour by changing parameter value with alter system command from PDB session level. If you use cdb_properties view in PDB then you’ll get exactly the same data which can be reached through database_properties view. Difference is on container id column which is added to all CDB_ views.

Leaving properties area and going for initialization parameter file and server parameter file. In PDB there is no file associated with PDB in $ORACLE_HOME/dbs directory. As PDB uses data dictionary to store all information about itself there is no need for another spfile. Oracle let PDB inherit parameters from root container and allow to change some of the parameters. You can find which one can be changed checking column ISPDB_MODIFIABLE from v$system_parameter view.

As this behaviour is quite important to master we’ll take a quick trip behind the wheel of open_cursors parameter.

First check current value for root container and pipboy PDB. From cdb$root container connected as sysdba execute script ./iad/parameter121.sql.

start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
GLOBAL (conid=0) open_cursors                   300                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      1 300                              T

open_cursors parameter has been set to value of 300 by dbca. Default value for this parameter is 50.

Open new putty session and connect directly to pipboy as common user sys.

sqlplus sys/manager@pipboy as sysdba
start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
GLOBAL (conid=0) open_cursors                   300                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      0                                  F

ordinal column in v$spparameter differs between containers because root container use spfile to start and pipboy haven’t got the ability to read spfile during startup (PDB’s use data dictionary information only). This column has more useful value when listed to find out what is the correct order of strings within one parameter, for instance when checking control_files parameter with more than one copy of it. Other data is the same within presented scope.

Change open_cursors value in pipboy PDB to 456 – use session in pipboy PDB.

alter system set open_cursors = 456;
start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
PIPBOY           open_cursors                   456                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      1 456                              T

Value for open_cursors parameter has been changed for pipboy PDB. From this point of view spparameter present “global” value. This is true only for pipboy PDB and her “data dictionary server parameter file”. Check what we can see from root container point of view. Switch to cdb$root session.

start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
PIPBOY           open_cursors                   456                              F   T
GLOBAL (conid=0) open_cursors                   300                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      1 300                              T

From root container point of view we can see that someone has changed value for pipboy’s open_cursors parameter but in server parameter file we still have value 300. The same for active value in cdb$root. Note that con_id and con_name need to be considered as a point of view. Another thing is that we’ve broke inheritance behaviour – from pipboy point of view we can see that the parameter is no longer global (inherited) but it is specified within PDB.

How to revert to standard state where value of the parameter is inherited? We can reset this value from PDB side or use container = all and set this new value for all containers. To get back to default value we need to reset parameter on PDB side and reset in spfile. Let’s check setting the parameter everywhere at once. From root container use command with container clause.

alter system set open_cursors = 787 container = all;
start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
GLOBAL (conid=0) open_cursors                   787                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      1 787                              T

Everywhere we’ve got the same value. This explains why we cannot see a row for pipboy PDB.

Execute the same script from PDB session.

start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
GLOBAL (conid=0) open_cursors                   787                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      1 456                              T

Data dictionary information has not been deleted yet for pipboy PDB. After pluggable database restart all return to normal state and there is no information in data dictionary cache.

Restart pluggable database from root container session.

alter pluggable database pipboy close immediate;
alter pluggable database pipboy open read write;

Exit from pipboy sys session and connect again.

start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
GLOBAL (conid=0) open_cursors                   787                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      0                                  F

Parameter has been reset to old value. The same could be achieved by resetting the parameter from pipboy PDB side. If you make change from root container of open_cursors parameter value to 300 then it will be inherited to pipboy PDB too as this PDB inheriting value again.

From pipboy sys user execute command.

alter system set open_cursors = 495;
start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?
IsS - is specified in spfile?
Add %% if needed for parameter name. Search is case insensitive.

Input system parameter name for like search: open_c%

1 - System parameter for LIKE with argument open_c%
(active instance parameters)

Container Name   Parameter Name                 Parameter Value                  IsD IsPM
---------------- ------------------------------ -------------------------------- --- ----
PIPBOY           open_cursors                   495                              F   T


2 - Server Parameter File information for LIKE with argument open_c%

Container Name   Parameter Name                  Ord Parameter Value                  IsS
---------------- ------------------------------ ---- -------------------------------- ---
GLOBAL (conid=0) open_cursors                      1 495                              T
alter system reset open_cursors;
start iad/parameter121
Key for column names
IsD - is default value?
IsPM - is modifiable in pluggable database?

1 - System parameter for LIKE with argument 'open_cursors'
(active instance parameters)

Parameter Name                 Parameter Value                  IsD IsPM Container Name
------------------------------ -------------------------------- --- ---- ----------------
open_cursors                   495                              F   T    PIPBOY

2 - Server Parameter File information for LIKE with argument 'open_cursors'

Parameter Name                 Parameter Value                  IsS  Ord Container Name
------------------------------ -------------------------------- --- ---- ----------------
open_cursors                                                    F      0 GLOBAL (conid=0)

From this point we could set parameter value from root container for all PDB’s or only dynamically apply value for pipboy PDB. Restart of pipboy PDB will result in clearing current memory value and applying inherited value from cdb$root container. One thing to remember is to do not forget about inheritance of PDB parameters from global configuration. To clear situation I’m going to set value for all containers and return old behaviour with inheritance. In the root container:

alter system set open_cursors = 300 container = all;

Another thing I wanted to check was path prefix setup on PDB creation time. We cannot change it later, we can set it only during PDB creation. According to documentation my path: /u02/oradata/tcdb/pipboy/ is now prefix for all database directories. I’ll create simple directory as pipboy administrator. We need quota on default maxstone tablespace to create expdp table which will be removed after the export job will be done.

Value for this property can be checked in database_properties view (presented above).

From maxstone user session execute statements (you can re-login in second session or create new ssh session):

alter user maxstone quota unlimited on vault13;
create directory maindir as 'maindir';
start do/directory112.sql
Owner            Directory name       Directory path
---------------- -------------------- ----------------------------------------------------
SYS              DATA_PUMP_DIR        /u01/app/oracle/admin/tcdb/dpdump/
SYS              MAINDIR              /u02/oradata/tcdb/pipboy/maindir
SYS              OPATCH_INST_DIR      /u01/app/oracle/product/12.1.0/dbhome_1/OPatch
SYS              OPATCH_LOG_DIR       /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch
SYS              OPATCH_SCRIPT_DIR    /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch
SYS              ORACLE_BASE          /
SYS              ORACLE_HOME          /
SYS              XSDDIR               /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/xml/sc
                                      hema

To test this directory we will use expdp from command line. Switch to ssh session (or create one), create subdirectory and change your working directory to newly created one.

mkdir /u02/oradata/tcdb/pipboy/maindir
cd /u02/oradata/tcdb/pipboy/maindir
expdp userid=maxstone/MxSt9281@pipboy dumpfile=d1.dmp logfile=d1.log directory=maindir content=metadata_only schemas=maxstone

For security reasons you should never input password in command line command – always use parameter file or input interactively requested string. Returning to main topic – we can see that new files have been created in selected directory:

pwd
/u02/oradata/tcdb/pipboy/maindir
ls -l
total 252
-rw-r----- 1 oracle oinstall 249856 Apr 14 15:29 d1.dmp
-rw-r--r-- 1 oracle oinstall   1536 Apr 14 15:29 d1.log

I’ll try to create absolute path for a directory object using sesssion with maxstone user logged in.

create directory absdir as '/u02/oradata/tcdb';
expdp userid=maxstone/MxSt9281@pipboy dumpfile=d1.dmp logfile=d1.log directory=absdir content=metadata_only schemas=maxstone

And all looks good. Files where created in /u02/oradata/tcdb directory. From Oracle Database SQL Language Reference under create pluggable database syntax:

path_prefix_clause

Use this clause to ensure that file paths for directory objects associated with the PDB are restricted to the specified directory or its subdirectories.

For me this works only for directories with non-absolute paths. Omit / in front of your path and you’ll get your directory inside pointed directory. My theory was correct as I found another statement about this parameter in Administrator’s Guide in a table “38-2 Clauses for Creating a PDB From the Seed” with question/answer list reviewing all available parameters:

Do you want to use a PATH_PREFIX clause to ensure that all relative directory object paths associated with the PDB are treated as relative to the specified directory or its subdirectories?

This is for relative paths only. Cleaning up my playground from maxstone user:

drop directory absdir;
drop directory maindir;

And removing operating system dump and log files d1.dmp/d1.log including maindir subdirectory.

Creating PDB from another PDB

We’re going to use pipboy PDB to create a clone named special. Below you can find sql command which will be our starting point for other examples. To introduce PDB parameter pdb_file_name_convert I’m going to omit file_name_convert clause in create pluggable database statement. If your pipboy PDB is open for read write close it and open for read only – it is required to use PDB as a source for cloning. As this is sql statement I’m going to create target directories before issuing commands. From ssh session:

mkdir -p /u02/oradata/tcdb/special/default_dpdir

And sql part:

alter session set pdb_file_name_convert = '/u02/oradata/tcdb/pipboy','/u02/oradata/tcdb/special';
alter pluggable database pipboy close;
alter pluggable database pipboy open read only;
create pluggable database special
  from pipboy
  storage (maxsize 2g max_shared_temp_size 2g)
  path_prefix = '/u02/oradata/tcdb/special/default_dpdir'
  user_tablespaces = all
  standbys = all
  logging
  create_file_dest = '/u02/oradata/tcdb/special';

To bring online both databases issue commands:

alter pluggable database all open read write;

You’ll receive an error:

ORA-65019: pluggable database PIPBOY already open

PDB special have been opened from mount state and this is normal. Pipboy PDB is in read only mode and we can change it’s state using force keyword only.

alter pluggable database pipboy open read write force;

Other way to open for read write would be closing and opening the PDB. Both options are correct and does not harm your database – even more – currently connected sessions to read only database will be maintained and can continue after command with force clause. To demonstrate changing PDB state first change it’s state from root$PDB container:

alter pluggable database pipboy open read only force;

Create new ssh session and connect using sql*plus as maxstone user to pipboy PDB:

sqlplus maxstone/MxSt9281@pipboy

Check your session state and sid:

start sap/mysession121
User Name    OS User        Program                     CID Service Name Machine
------------ -------------- -------------------------- ---- ------------ ----------------
MAXSTONE     oracle         sqlplus@odone (TNS V1-V3)     3 pipboy       odone
select userenv('sid') from dual;
USERENV('SID')
--------------
           414
select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY

From root container session:

alter pluggable database pipboy open read write force;

And again check maxstone session.

select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
select userenv('sid') from dual;
USERENV('SID')
--------------
           414

You can check logon time too just to be sure that’s the same session. As you can see PDB give some flexibility – you can change to read only for cloning time and then to read write after cloning – user sessions will be intact. The same is true when going back to read write. You need to look out for migrate state as it is not allowed to use force keyword to go to/from this state with force keyword.

Just to check the state of our new PDB cloned from pipboy PDB check it’s state with couple scripts from root container:

start mt/container121
Key for column names
CID - Container ID

 CID NAME                         DbID Size in MB Open mode  Open time              Res
---- -------------------- ------------ ---------- ---------- ---------------------- ---
   1 CDB$ROOT                918792381          0 READ WRITE 2015-04-19 09:08:10    NO
   2 PDB$SEED               4034246255        845 READ ONLY  2015-04-19 09:08:10    NO
   3 PIPBOY                 3788905833        901 READ WRITE 2015-04-19 09:14:27    NO
   4 SPECIAL                3986562878        901 READ WRITE 2015-04-19 09:16:20    NO
start ts/datatempfiles121
~~ data files ~~

Tb Name      Tb Status  Fl ID Fl Name                                  Fl Status Cont. Name
------------ --------- ------ ---------------------------------------- --------- ----------
SYSAUX       ONLINE         3 /u02/oradata/tcdb/sysaux01.dbf           AVAILABLE CDB$ROOT
SYSTEM       ONLINE         1 /u02/oradata/tcdb/system01.dbf           AVAILABLE CDB$ROOT
UNDOTBS1     ONLINE         5 /u02/oradata/tcdb/undotbs01.dbf          AVAILABLE CDB$ROOT
USERS        ONLINE         6 /u02/oradata/tcdb/users01.dbf            AVAILABLE CDB$ROOT
SYSAUX       ONLINE        42 /u02/oradata/tcdb/pipboy/sysaux01.dbf    AVAILABLE PIPBOY
SYSTEM       ONLINE        41 /u02/oradata/tcdb/pipboy/system01.dbf    AVAILABLE PIPBOY
VAULT13      ONLINE        43 /u02/oradata/tcdb/pipboy/vault13_01.dbf  AVAILABLE PIPBOY
SYSAUX       ONLINE        45 /u02/oradata/tcdb/special/TCDB/140F91809 AVAILABLE SPECIAL
                              458086BE0530C38A8C0632D/datafile/o1_mf_s
                              ysaux_bm6ohqh4_.dbf

SYSTEM       ONLINE        44 /u02/oradata/tcdb/special/TCDB/140F91809 AVAILABLE SPECIAL
                              458086BE0530C38A8C0632D/datafile/o1_mf_s
                              ystem_bm6ohqgv_.dbf

VAULT13      ONLINE        46 /u02/oradata/tcdb/special/TCDB/140F91809 AVAILABLE SPECIAL
                              458086BE0530C38A8C0632D/datafile/o1_mf_v
                              ault13_bm6ohqh6_.dbf


10 rows selected.


~~ temp files ~~

Tb Name      Tb Status  Fl ID Fl Name                                  Fl Status Cont. Name
------------ --------- ------ ---------------------------------------- --------- ----------
TEMP         ONLINE         1 /u02/oradata/tcdb/temp01.dbf             ONLINE    CDB$ROOT
TEMP         ONLINE         3 /u02/oradata/tcdb/pipboy/temp01.dbf      ONLINE    PIPBOY
TEMP         ONLINE         4 /u02/oradata/tcdb/special/TCDB/140F91809 ONLINE    SPECIAL
                              458086BE0530C38A8C0632D/datafile/o1_mf_t
                              emp_bm6ohqh6_.dbf

I’ve set up both: pdb_file_name_convert as a session parameter and OMF parameter create_file_dest in create pluggable database clause. This forced Oracle to use OMF as it almost always takes precedence over other parameters. To do not use OMF I’m going to drop this PDB and create it again but using pdb_file_name create session parameter only. From root container session – clean up first:

alter pluggable database special close;
drop pluggable database special including datafiles;

And create special PDB once more:

alter session set pdb_file_name_convert = '/u02/oradata/tcdb/pipboy','/u02/oradata/tcdb/special';
alter pluggable database pipboy open read only force;
create pluggable database special
  from pipboy
  storage (maxsize 2g max_shared_temp_size 2g)
  path_prefix = '/u02/oradata/tcdb/special/default_dpdir'
  user_tablespaces = all
  standbys = all
  logging
  create_file_dest = none;

In my case above command returned an error:

ORA-65005: missing or invalid file name pattern for file -
/u02/oradata/tcdb/pipboy/system01.dbf

Unfortunately create_file_dest = none did not force Oracle Database to drop this relation with OMF and still something went wrong. Corrected command:

create pluggable database special
  from pipboy
  storage (maxsize 2g max_shared_temp_size 2g)
  path_prefix = '/u02/oradata/tcdb/special/default_dpdir'
  user_tablespaces = all
  standbys = all
  logging;

Just remove create_file_dest clause from command and all will be good.

alter pluggable database pipboy open read write force;
alter pluggable database special open read write;
start ts/datatempfiles121
~~ data files ~~

Tb Name      Tb Status  Fl ID Fl Name                                  Fl Status Cont. Name
------------ --------- ------ ---------------------------------------- --------- ----------
SYSAUX       ONLINE         3 /u02/oradata/tcdb/sysaux01.dbf           AVAILABLE CDB$ROOT
SYSTEM       ONLINE         1 /u02/oradata/tcdb/system01.dbf           AVAILABLE CDB$ROOT
UNDOTBS1     ONLINE         5 /u02/oradata/tcdb/undotbs01.dbf          AVAILABLE CDB$ROOT
USERS        ONLINE         6 /u02/oradata/tcdb/users01.dbf            AVAILABLE CDB$ROOT
SYSAUX       ONLINE        42 /u02/oradata/tcdb/pipboy/sysaux01.dbf    AVAILABLE PIPBOY
SYSTEM       ONLINE        41 /u02/oradata/tcdb/pipboy/system01.dbf    AVAILABLE PIPBOY
VAULT13      ONLINE        43 /u02/oradata/tcdb/pipboy/vault13_01.dbf  AVAILABLE PIPBOY
SYSAUX       ONLINE        48 /u02/oradata/tcdb/special/sysaux01.dbf   AVAILABLE SPECIAL
SYSTEM       ONLINE        47 /u02/oradata/tcdb/special/system01.dbf   AVAILABLE SPECIAL
VAULT13      ONLINE        49 /u02/oradata/tcdb/special/vault13_01.dbf AVAILABLE SPECIAL

10 rows selected.


~~ temp files ~~

Tb Name      Tb Status  Fl ID Fl Name                                  Fl Status Cont. Name
------------ --------- ------ ---------------------------------------- --------- ----------
TEMP         ONLINE         1 /u02/oradata/tcdb/temp01.dbf             ONLINE    CDB$ROOT
TEMP         ONLINE         3 /u02/oradata/tcdb/pipboy/temp01.dbf      ONLINE    PIPBOY
TEMP         ONLINE         4 /u02/oradata/tcdb/special/temp01.dbf     ONLINE    SPECIAL

This time file names are much more understandable for us.

Creating PDB from non-cdb

I’ve created simple database with a name molerat as a non-CDB database. Before conversion I’ll put there some database objects which will be converted as PDB objects. All database files are clustered in one directory: /u02/oradata/molerat/. From sys user I’m starting with two users, couple tablespaces, tables and some very simple data.

create tablespace megaton_data datafile '/u02/oradata/molerat/megaton_data_01.dbf' size 16m autoextend on next 8m maxsize 128m;
create tablespace megaton_indx datafile '/u02/oradata/molerat/megaton_indx_01.dbf' size 16m autoextend on next 8m maxsize 128m;
create temporary tablespace megaton_temp tempfile '/u02/oradata/molerat/megaton_temp_01.dbf' size 8m autoextend on next 8m maxsize 256m;
create user agnes identified by agnes
  default tablespace megaton_data
  temporary tablespace megaton_temp
  quota unlimited on megaton_data
  quota unlimited on megaton_indx
  account unlock;
create role megaton_citizen;
grant create session, create table, create sequence, create synonym, create view to megaton_citizen;
grant megaton_citizen to agnes;
create user lewis identified by lewis
  default tablespace megaton_data
  temporary tablespace megaton_temp
  quota unlimited on megaton_data
  quota unlimited on megaton_indx
  account unlock;
grant megaton_citizen to lewis;

connect agnes/agnes

create table room
(
  id number(10),
  name varchar2(128),
  floor number(2)
) tablespace megaton_data;

create unique index room_uidx1 on room(id)
  tablespace megaton_indx;
alter table room
  add constraint room_pk
  primary key (id)
  using index room_uidx1;

create table citizen
(
  id number(16),
  firstname varchar2(32),
  surename varchar2(64),
  room_id number(10)
) tablespace megaton_data;

create unique index citizen_uidx1 on citizen(id)
  tablespace megaton_indx;
alter table citizen
  add constraint citizen_pk
  primary key (id)
  using index citizen_uidx1;
alter table citizen
  add constraint citizen_room_fk
  foreign key (room_id) referencing room(id);

create sequence room_seq
  start with 1 increment by 1 cache 100;
create sequence citizen_seq
  start with 1 increment by 1 cache 100;

declare
  v_room_id room.id%type;
begin
  v_room_id := room_seq.nextval;
  insert into room values (v_room_id, 'summer heat', 1);
  insert into citizen values (citizen_seq.nextval, 'Jimmy', 'James', v_room_id);
  insert into citizen values (citizen_seq.nextval, 'Shannon', 'Shawn', v_room_id);
  
  v_room_id := room_seq.nextval;
  insert into room values (v_room_id, 'dirty water', 3);
  insert into citizen values (citizen_seq.nextval, 'Danny', 'Dean', v_room_id);
  insert into citizen values (citizen_seq.nextval, 'Mickey', 'Mick', v_room_id);  
end;
/

commit;

grant select, referencing on room to lewis;

connect lewis/lewis

create table gun
(
  id number(8),
  name varchar2(32),
  room_id number(10)
) tablespace megaton_data;

create unique index gun_uidx1 on gun(id)
  tablespace megaton_indx;
alter table gun
  add constraint gun_pk
  primary key (id)
  using index gun_uidx1;
alter table gun
  add constraint gun_room_fk
  foreign key (room_id) referencing agnes.room(id);

create sequence gun_seq
  start with 1 increment by 1 cache 100;

insert into gun values (gun_seq.nextval, 'big gun', (select min(id) from agnes.room));
insert into gun values (gun_seq.nextval, 'tiny gun', (select max(id) from agnes.room));
commit;

connect / as sysdba

This is our simple application which we’ll check, if it still exists in PDB after conversion. To see those objects we need couple simple queries with very predictable results. Run couple scripts for validation. As sysdba execute:

start do/constraint112
Key for column names
CT - Constraint type:
..C - Check constraint on a table
..P - Primary key
..U - Unique key
..R - Referential integrity
..V - With check option, on a view
..O - With read only, on a view
..H - Hash expression
..F - Constraint that involves a REF column
..S - Supplemental logging
Add %% if needed for owner name. Search is case insensitive.

Input constraints owner: LEWIS
Constraints with owner LIKE LEWIS

Owner            Table name           Constraint name  CT Refer. obj.  Ref. constr. nam
---------------- -------------------- ---------------- -- ------------ ----------------
LEWIS            GUN                  GUN_PK           P
LEWIS            GUN                  GUN_ROOM_FK      R  AGNES        ROOM_PK
start do/constraint112
Input constraints owner: agnes
Constraints with owner LIKE agnes

Owner            Table name           Constraint name  CT Refer. obj.  Ref. constr. nam
---------------- -------------------- ---------------- -- ------------ ----------------
AGNES            CITIZEN              CITIZEN_PK       P
AGNES            CITIZEN              CITIZEN_ROOM_FK  R  AGNES        ROOM_PK
AGNES            ROOM                 ROOM_PK          P

All objects in schemas can be listed through another script.

start do/object112
Add %% if needed for owner name. Search is case insensitive.

Input objects owner: lewis
Objects with owner LIKE lewis

Object type          Object name                    Creation date       Status
-------------------- ------------------------------ ------------------- -------
INDEX                GUN_UIDX1                      2015-04-19 10:54:58 VALID
SEQUENCE             GUN_SEQ                        2015-04-19 10:54:58 VALID
TABLE                GUN                            2015-04-19 10:54:58 VALID
start dbobjects
Add %% if needed for owner name. Search is case insensitive.

Input objects owner: agnes
Objects with owner LIKE agnes

Object type          Object name                    Creation date       Status
-------------------- ------------------------------ ------------------- -------
INDEX                CITIZEN_UIDX1                  2015-04-19 10:54:57 VALID
INDEX                ROOM_UIDX1                     2015-04-19 10:54:57 VALID
SEQUENCE             CITIZEN_SEQ                    2015-04-19 10:54:58 VALID
SEQUENCE             ROOM_SEQ                       2015-04-19 10:54:58 VALID
TABLE                CITIZEN                        2015-04-19 10:54:57 VALID
TABLE                ROOM                           2015-04-19 10:54:57 VALID

And to see segment allocation in tablespaces simple query from dba_segments:

col segment_name for a32
col tablespace_name for a32
select segment_name, tablespace_name, bytes
  from dba_segments
 where owner in ('AGNES','LEWIS');
SEGMENT_NAME                     TABLESPACE_NAME                       BYTES
-------------------------------- -------------------------------- ----------
GUN                              MEGATON_DATA                          65536
CITIZEN                          MEGATON_DATA                          65536
ROOM                             MEGATON_DATA                          65536
GUN_UIDX1                        MEGATON_INDX                          65536
CITIZEN_UIDX1                    MEGATON_INDX                          65536
ROOM_UIDX1                       MEGATON_INDX                          65536

All is ready for our test. We’ll try to clone non-CDB to our tcdb as a PDB. From root container create a database link to molerat database. It’s not written anywhere in documentation but I assume that I should connect as system user. I’ve created tnsnames entry for my molerat database with the same name to ease database link creation:

# molerat - non-CDB for cloning to tcdb
MOLERAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = molerat)
    )
  )

Then create directory for molerat database files in tcdb database.

create database link molerat
  connect to system identified by manager
  using 'molerat';
!mkdir /u02/oradata/tcdb/molerat/

Issue command for non-CDB cloning using from clause in create pluggable database command.

create pluggable database molerat_clone
  from non$cdb@molerat
  storage unlimited
  file_name_convert = ('/u02/oradata/molerat/','/u02/oradata/tcdb/molerat/')
  user_tablespaces = all
  logging
  create_file_dest = none;

Cloned database is now in mounted state. Notice that in documentation it is required to put source database into read only mode. We didn’t do that and there were no errors. In production environment you should always put source non-CDB database in read only mode before cloning. create_file_dest clause works fine too and file names aren’t created with OMF enabled – only converted paths were applied here. Next step is to convert this non-CDB to PDB using dedicated script. First check your current status of your PDB’s.

start mt/container121
Key for column names
CID - Container ID

 CID NAME                         DbID Size in MB Open mode  Open time              Res
---- -------------------- ------------ ---------- ---------- ---------------------- ---
   1 CDB$ROOT                918792381          0 READ WRITE 2015-04-19 09:08:10    NO
   2 PDB$SEED               4034246255        845 READ ONLY  2015-04-19 09:08:10    NO
   3 PIPBOY                 3788905833        901 READ WRITE 2015-04-19 09:14:27    NO
   4 SPECIAL                 510174438        901 READ WRITE 2015-04-19 09:26:11    NO
   5 MOLERAT_CLONE          2993991422          0 MOUNTED    2015-04-19 11:08:50

Switch you session to molerat_clone container and start dedicated script for conversion from non-CDB to PDB.

alter session set container = molerat_clone;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

During execution of this script my session hangs on recompilation with a command utl_recomp.recomp_parallel – as I have only couple database objects which do not require recompilation I thought that I shouldn’t even see this prompt – but still – this job took 12c huge amount of time (*in my virtual environment). I’ve hit enter after some time – maybe this was only a coincidence but script react and moved forward almost instantly.

To finish cloning process we need to open the database for read write (do not use read only as this can cause error – according to documentation).

alter database open read write;
exit

As the script totally mess up your sql*plus session with strange settings I recommend to exit from this session and login again to root container and check status of all containers. Check if your environment is setup to connect to tcdb. Add tnsnames.ora entry to connect to PDB via listener.

# molerat_clone - cloned non-cdb to pdb in tcdb
MOLERAT_CLONE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = odone)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MOLERAT_CLONE)
    )
  )
sqlplus / as sysdba
start mt/container121
Key for column names
CID - Container ID

 CID NAME                         DbID Size in MB Open mode  Open time              Res
---- -------------------- ------------ ---------- ---------- ---------------------- ---
   1 CDB$ROOT                918792381          0 READ WRITE 2015-04-19 09:08:10    NO
   2 PDB$SEED               4034246255        845 READ ONLY  2015-04-19 09:08:10    NO
   3 PIPBOY                 3788905833        901 READ WRITE 2015-04-19 09:14:27    NO
   4 SPECIAL                 510174438        901 READ WRITE 2015-04-19 09:26:11    NO
   5 MOLERAT_CLONE          2993991422       1315 READ WRITE 2015-04-19 11:26:20    NO

For some unknown reason my cloned PDB has the same size as source database counting only allocated space of the database files. Source database, counting by segments, has 686 MB and 1487 MB of allocated space in dba_data_files. What happened during the conversion – maybe some dictionary objects were not converted as common and they exists as common and local in the same time (see the difference between pipboy and molerat_clone sizes above – both PDBs are almost empty). Counting internally from molerat_clone PDB database has allocated 1287 MB and used by segments 606 MB. I think that this process still can be improved as I used the same Oracle Database binaries for creating tcdb container database and molerat non-cdb database (for cloning from non-CDB to PDB you have to use 12.1.0.2.0 or later version).

Quickly check database objects state. Add tnsname entry in tnsname.ora file for newly created PDB and connect as agnes. In separate ssh session connect to tcdb PDB named molerat_clone. Then connect as lewis to check his accessibility and finaly switch to sys user and execute scripts as previously in molerat non-cdb.

sqlplus agnes/agnes@molerat_clone
connect lewis/lewis@molerat_clone
connect sys/manager@molerat_clone as sysdba
start do/constraint112
Input constraints owner: agnes
Constraints with owner LIKE agnes

Owner            Table name           Constraint name  CT Refer. obj.  Ref. constr. nam
---------------- -------------------- ---------------- -- ------------ ----------------
AGNES            CITIZEN              CITIZEN_PK       P
AGNES            CITIZEN              CITIZEN_ROOM_FK  R  AGNES        ROOM_PK
AGNES            ROOM                 ROOM_PK          P
start do/constraint112
Input constraints owner: lewis
Constraints with owner LIKE lewis

Owner            Table name           Constraint name  CT Refer. obj.  Ref. constr. nam
---------------- -------------------- ---------------- -- ------------ ----------------
LEWIS            GUN                  GUN_PK           P
LEWIS            GUN                  GUN_ROOM_FK      R  AGNES        ROOM_PK
start do/object112
Input objects owner: agnes
Objects with owner LIKE agnes

Object type          Object name                    Creation date       Status
-------------------- ------------------------------ ------------------- -------
INDEX                CITIZEN_UIDX1                  2015-04-19 10:54:57 VALID
INDEX                ROOM_UIDX1                     2015-04-19 10:54:57 VALID
SEQUENCE             CITIZEN_SEQ                    2015-04-19 10:54:58 VALID
SEQUENCE             ROOM_SEQ                       2015-04-19 10:54:58 VALID
TABLE                CITIZEN                        2015-04-19 10:54:57 VALID
TABLE                ROOM                           2015-04-19 10:54:57 VALID
start do/object112
Input objects owner: lewis
Objects with owner LIKE lewis

Object type          Object name                    Creation date       Status
-------------------- ------------------------------ ------------------- -------
INDEX                GUN_UIDX1                      2015-04-19 10:54:58 VALID
SEQUENCE             GUN_SEQ                        2015-04-19 10:54:58 VALID
TABLE                GUN                            2015-04-19 10:54:58 VALID

And separate script for segments:

col segment_name for a32
col tablespace_name for a32
select segment_name, tablespace_name, bytes
  from dba_segments
 where owner in ('AGNES','LEWIS');
SEGMENT_NAME                     TABLESPACE_NAME                       BYTES
-------------------------------- -------------------------------- ----------
GUN                              MEGATON_DATA                          65536
CITIZEN                          MEGATON_DATA                          65536
ROOM                             MEGATON_DATA                          65536
GUN_UIDX1                        MEGATON_INDX                          65536
CITIZEN_UIDX1                    MEGATON_INDX                          65536
ROOM_UIDX1                       MEGATON_INDX                          65536

Looks good. Try to add another row in lewis table using simple insert.

connect lewis/lewis@molerat_clone
insert into gun values (gun_seq.nextval, 'old gun', (select min(id) from agnes.room));
insert into gun values (gun_seq.nextval, 'shiny gun', (select max(id) from agnes.room));
commit;
select *
  from gun;
        ID NAME                                ROOM_ID
---------- -------------------------------- ----------
         1 big gun                                   1
         2 tiny gun                                  2
       101 old gun                                   1
       102 shiny gun                                 2
select *
  from session_roles;
ROLE
--------------------------------------------------------------------------------------------
MEGATON_CITIZEN

Copying PDB to another CDB

Some time ago I’ve created another CDB and named her beecdb. Right now I’m going to use it to clone molerat_clone to this container database.

In target CDB from root container create new database link to molerat PDB in tcdb database.

create database link molerat_clone
  connect to system identified by manager
  using 'molerat_clone';
!mkdir /u02/oradata/BEECDB/molerat_clone2

From tcdb CDB switch molerat_clone PDB to read only mode.

alter pluggable database molerat_clone open read only force;

Issue command from a beecdb database to clone PDB using from clause in create pluggable database command. Remember to connect as sys in root container. After creating PDB open it.

create pluggable database molerat_clone2
  from molerat_clone@molerat_clone
  storage unlimited
  file_name_convert = ('/u02/oradata/tcdb/molerat','/u02/oradata/BEECDB/molerat_clone2')
  user_tablespaces = all
  logging
  create_file_dest = none;
alter pluggable database molerat_clone2 open read write;

After creating PDB switch current container to check if there are the database objects which were created earlier.

alter session set container = molerat_clone2;
start do/object112
Input objects owner: agnes
Objects with owner LIKE agnes

Object type          Object name                    Creation date       Status
-------------------- ------------------------------ ------------------- -------
INDEX                CITIZEN_UIDX1                  2015-04-19 10:54:57 VALID
INDEX                ROOM_UIDX1                     2015-04-19 10:54:57 VALID
SEQUENCE             CITIZEN_SEQ                    2015-04-19 10:54:58 VALID
SEQUENCE             ROOM_SEQ                       2015-04-19 10:54:58 VALID
TABLE                CITIZEN                        2015-04-19 10:54:57 VALID
TABLE                ROOM                           2015-04-19 10:54:57 VALID
start do/object112
Input objects owner: lewis
Objects with owner LIKE lewis

Object type          Object name                    Creation date       Status
-------------------- ------------------------------ ------------------- -------
INDEX                GUN_UIDX1                      2015-04-19 10:54:58 VALID
SEQUENCE             GUN_SEQ                        2015-04-19 10:54:58 VALID
TABLE                GUN                            2015-04-19 10:54:58 VALID

Constraints are the same too. Looks like this procedure works just fine.

Moving PDB between CDBs

There is one more scenario which I would like to test – unplug PDB from beecdb container and then plug into tcdb. First, unplug molerat_clone2 from beecdb. Use a connection to root container – switch between containers if needed.

alter session set container = cdb$root;
alter pluggable database molerat_clone2 close;
alter pluggable database molerat_clone2 unplug into '/u02/oradata/BEECDB/molerat_clone2/molerat_clone2_meta.xml';
start mt/pdb12102
Key for column names
FL - force logging
FnL - force nologging
CID - Container ID

 CID Pluggable database name          Status        Logging   FL  FnL
---- -------------------------------- ------------- --------- --- ---
   2 PDB$SEED                         NORMAL        LOGGING   NO  NO
   3 MOLERAT_CLONE2                   UNPLUGGED     LOGGING   NO  NO

We can see that our molerat_clone2 status has changed to unplugged. This PDB is still part of the database and will be in backupset if you’ll start rman backup of the database. I’ve checked this by issuing command from rman (open a new session to check this command if needed):

rman target "'/ as sysbackup'"
backup as compressed backupset incremental level 0 database plus archivelog delete all input;

In output I can find:

[...]
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00019 name=/u02/oradata/BEECDB/molerat_clone2/system01.dbf
input datafile file number=00020 name=/u02/oradata/BEECDB/molerat_clone2/sysaux01.dbf
input datafile file number=00022 name=/u02/oradata/BEECDB/molerat_clone2/megaton_data_01.dbf
input datafile file number=00023 name=/u02/oradata/BEECDB/molerat_clone2/megaton_indx_01.dbf
input datafile file number=00021 name=/u02/oradata/BEECDB/molerat_clone2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-15
[...]

Now let’s clone this PDB in the same database first. To do so we need to use as clone clause and rename database files during copy. First create target directory for new files.

!mkdir /u02/oradata/BEECDB/molerat_clone44/

And execute cloning command:

create pluggable database molerat_clone44
  as clone using '/u02/oradata/BEECDB/molerat_clone2/molerat_clone2_meta.xml'
  copy file_name_convert = ('/u02/oradata/BEECDB/molerat_clone2/', '/u02/oradata/BEECDB/molerat_clone44/')
  storage unlimited
  path_prefix = '/u02/oradata/BEECDB/molerat_clone44'
  user_tablespaces = all
  logging;

I’m not using source_file_name_convert because I didn’t move the files anywhere earlier (manually). They are in the same place, where they were during unplugging from beecdb and I need to use file_name_convert (or initialization parameter pdb_file_name_convert) to copy files to correct location.

start mt/pdb12102
Key for column names
FL - force logging
FnL - force nologging
CID - Container ID

 CID Pluggable database name          Status        Logging   FL  FnL
---- -------------------------------- ------------- --------- --- ---
   2 PDB$SEED                         NORMAL        LOGGING   NO  NO
   3 MOLERAT_CLONE2                   UNPLUGGED     LOGGING   NO  NO
   4 MOLERAT_CLONE44                  NEW           LOGGING   NO  NO

Our newly created PDB is in place. Let’s check what happens during opening of molerat_clone44.

alter pluggable database molerat_clone44 open read write;

Status is switched to normal and this PDB is available for use along with agnes and lewis users. Another attempt will be to use this unplugged database but I will move those files first. Just to clear this CDB of old entries I’m going to remove this PDB from beecdb but leave the files intact.

drop pluggable database molerat_clone2 keep datafiles;
start mt/pdb12102
Key for column names
FL - force logging
FnL - force nologging
CID - Container ID

 CID Pluggable database name          Status        Logging   FL  FnL
---- -------------------------------- ------------- --------- --- ---
   2 PDB$SEED                         NORMAL        LOGGING   NO  NO
   4 MOLERAT_CLONE44                  NORMAL        LOGGING   NO  NO

PDB molerat_clone2 vanished from the list. Now I’ll move those files to new location. I’m creating new directory:

!mv /u02/oradata/BEECDB/molerat_clone2 /u02

Now paths from xml file points to location /u02/oradata/BEECDB/molerat_clone2/ but it’s not true anymore. They are located in /u02/molerat_clone2/. I’m switching to tcdb database and I’ll try to copy those files with directory conversion to copy files to /u02/oradata/tcdb/molerat_clone37. First, I’ll create target directory.

mkdir /u02/oradata/tcdb/molerat_clone37

And now I’m ready to create new PDB from xml using this unplugged PDB. From root container in CDB named tcdb:

create pluggable database molerat_clone37
  using '/u02/molerat_clone2/molerat_clone2_meta.xml'
  source_file_name_convert = ('/u02/oradata/BEECDB/molerat_clone2/', '/u02/molerat_clone2/')
  copy file_name_convert = ('/u02/molerat_clone2/', '/u02/oradata/tcdb/molerat_clone37/')
  storage unlimited
  path_prefix = '/u02/oradata/tcdb/molerat_clone37'
  user_tablespaces = all
  logging;
start mt/pdb12102
Key for column names
FL - force logging
FnL - force nologging
CID - Container ID

 CID Pluggable database name          Status        Logging   FL  FnL
---- -------------------------------- ------------- --------- --- ---
   2 PDB$SEED                         NORMAL        LOGGING   NO  NO
   3 PIPBOY                           NORMAL        LOGGING   NO  NO
   4 SPECIAL                          NORMAL        LOGGING   NO  NO
   5 MOLERAT_CLONE                    NORMAL        LOGGING   NO  NO
   6 MOLERAT_CLONE37                  NEW           LOGGING   NO  NO
alter pluggable database molerat_clone37 open read write;

You can check details about all database files using ts/datatempfiles121.sql script and switch to molerat_clone37 container and see database objects through do/object112.sql script as previously. The last attempt would be with move option to create new PDB in tcdb container database with relocation of the database files. First, I’m going to drop this pluggable database and then create new one again. Command to do this from tcdb root container:

alter pluggable database molerat_clone37 close;
drop pluggable database molerat_clone37 including datafiles;

create pluggable database molerat_clone37
  using '/u02/molerat_clone2/molerat_clone2_meta.xml'
  source_file_name_convert = ('/u02/oradata/BEECDB/molerat_clone2/', '/u02/molerat_clone2/')
  move file_name_convert = ('/u02/molerat_clone2/', '/u02/oradata/tcdb/molerat_clone37/')
  storage unlimited
  path_prefix = '/u02/oradata/tcdb/molerat_clone37'
  user_tablespaces = all
  logging;

alter pluggable database molerat_clone37 open read write;

I can see that (again) all files are in place and PDB works properly.

In /u02/molerat_clone2/ directory no database files were kept, only molerat_clone2_meta.xml file was not deleted (looks like it’s auxiliary file for the database – it’s not registered anywhere) – they were moved to /u02/oradata/tcdb/molerat_clone37. During this test you could see that moving files inside the same file system was really fast just like with mv os command. Cleaning up after my copy operations:

rm -rf /u02/molerat_clone2

Using xml file is a good way to copy unplugged PDB and restore it from the “sands of time”. I cannot see clearly the benefits of unplugged PDB but maybe with next releases we would see something funky right here too – right now unplugged PDB can be only dropped. This way she will vanish from rman backup list.

Summary

It’s worth to mention that moving from 12.1.0.1.0 to 12.1.0.2.0 brings some facility in a matter of PDB creation from non-CDB – we don’t have to use anymore (manually) script or DBMS package to extract some xml files, import it to target CDB and follow complex procedure. Right now you’ll just create a database link and execute script which accommodate this new PDB in your CDB. Old way with xml file is still valid, especially in environment where you cannot use database link to connect directly with source container database.

Beside standard procedure you can use optional step in plugging in unplugged PDB into CDB – DBMS_PDB package. You can describe PDB if the xml file is lost using recover procedure, use describe routine to generate xml file which can be used to check, if this PDB can be plugged into different container or even check the compatibility for such operation between CDB’s by using check_plug_compatibility subprogram.

Looks like Multitenant idea will be explored by Oracle more deeply in future releases and we can expect new functionality with each cumulative update.

Patrycjusz Konkol has written 5 articles

Oracle Database Administrator with around 7+ years of experience in Oracle Databases. From beginning of professional career worked as SQL and PL/SQL developer with use of Oracle Forms&Reports 6i, C# programmer and finally Oracle DBA with wide area of responsibilities in IBM Power environment with AIX 6 and 7. Right now works for Atos - international information technology services company as an Oracle Database Administrator.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>