oracle_multitenant_pdb_management [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== Overview =====
Management of PDBs is fairly simple now. 

We can perform several actions with a PDB:

  * OPEN
  * Plug & Unplig
  * Close
  * Restore / Recover
  * Clone
  * Clone in Hot Mode

===== Configuration Settings =====
PDBs can be heavily customizable and offer variety of features.

==== Local / Shared Undo ====
One key aspect which we need to consider when creating a PDB is whether we want a shared undo or a local undo
Shared undo is the default and it was since 12.1. However with Local undo, we can perform clones of the database while transactions are going.

{{:screen_shot_2021-11-13_at_6.32.43_pm.png?600|}}

===== Operations ======
==== Open ====
After we create a CDB, we can to open it, as follows:

<Code:bash|Open PDB>
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
</Code>

That command, will open us a database and it will become available to the users.
We can also close a pluggable database as follows:

<Code:bash|Close PDB>
ALTER PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE;
</Code>

==== Unplug a PDB ====
In 12.1 we could unplug a PDB into .xml file and the migration of the PDB, would be the migration of that XML file AND the relevant database files. 

From 12.2 we can also unplug the PDB into a .pdb archive file, which contains information about the PDB AND the relevant database files. That will ease our migration.

<Code:bash|Unplug to .pdb file>
export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba
ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 UNPLUG INTO '/u01/pdb5.pdb';
</Code>

That assumes, you are using OMF file. The resulting file contains all the necessary data:

<Code:bash|List .pdb file>
HOST ls -al /u01/pdb5.pdb
-rw-r--r--. 1 oracle oinstall 161702502 Jan  7 21:01 /u01/pdb5.pdb

SQL>
</Code>

====Plug a PDB====
To plug a database, we need the file from above and we need to verify the compatibility as follows: 
<Code:bash|Plug a PDB>
SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/pdb5.pdb',
                pdb_name       => 'pdb5');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/
compatible

PL/SQL procedure successfully completed.

SQL>
</Code>

=== Create a PDB using the source file ===
Then we can create a PDB using the .pdb file as follows:

<Code:bash|Plug-in PDB>
CREATE PLUGGABLE DATABASE pdb5 USING '/u01/pdb5.pdb';

ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

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

SQL>
</Code>

====Clone a PDB====
We can clone a PDB as follows:

<Code:bash|Cloning a PDB>
-- Setting the source to read-only is not necessary for Oracle 12cR2 with local undo, if you use 12.1 or 12.2 without local undo, it is needed:
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY;



CREATE PLUGGABLE DATABASE pdb4 FROM pdb3
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/');

ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;



-- Switch the source PDB back to read/write if you made it read-only.
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
</Code>

==== Clone a remote PDB (12.1) ====
For that feature, you need to be at least: 12.1.0.2, there is a big with 12.1.0.1.
Furthermore, there are two varieties of that procedure.

  * Clone from PDB within other CDB
  * Clone a non-CDB

For both, there are the following pre-requisites:

===Pre-requisites===
  * The user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the root container.
  * The remote database (PDB or non-CDB) must be open in read-only mode.
  * The local database must have a database link to the remote database. If the remote database is a PDB, the database link can point to the remote CDB using a common user, or the PDB using a local or common user.
  * The user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASE privilege.
  * The local and remote databases must have the same endianness, options installed and character sets.
  * If the remote database uses Transparent Data Encryption (TDE) the local CDB must be configured appropriately before attempting the clone. If not you will be left with a new PDB that will only open in restricted mode.
  * The default tablespaces for each common user in the remote PDB *must* exist in local CDB. If this is not true, create the missing tablespaces in the root container of the local PDB. If you don't do this your new PDB will only be able to open in restricted mode (Bug 19174942).
  * When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.

===Clone from a PDB in another CDB ===
As explained, we need to create a user and open the remote PDB in read only, and create Link in the local one:

==On remote==
<Code:bash|Create a user on remote>
export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

ALTER SESSION SET CONTAINER=pdb5;

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;
EXIT;
</Code>

Then we can move to the local machine and create the link:

==On local==
<Code:bash|Create link>
###Edit local tnsnames.ora
PDB5 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdb5)
    )
  )
  
export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE DATABASE LINK clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'pdb5';

-- Test link.
DESC user_tables@clone_link

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

Pluggable database created.

SQL>

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        MOUNTED

SQL>

ALTER PLUGGABLE DATABASE pdb5new OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        READ WRITE

SQL>
</Code>


==== Clone a non-CDB ====
Non-CDB in this case, refers to normal database, pre 12c
So let's see how it will be done. As before we need to create a user and bring the whole database in read only mode:
In fact there are multiple ways to be done:


{{:moving_noncdb_cdb.jpg?600|}}

But let's do it cloning:

==On remote==
<Code:bash|Create user and enable read-only>
export ORAENV_ASK=NO
export ORACLE_SID=db12c
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
EXIT;
</Code>

Then on the local we have to do the same as before:

==On local==

<Code:bash|Create DB Link>
DB12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db12c)
    )
  )
  
export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE DATABASE LINK clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'db12c';

-- Test link.
DESC user_tables@clone_link

</Code>
Then we can create the PDBs as follows:

<Code:bash| Create the PDB>
CREATE PLUGGABLE DATABASE db12cpdb FROM NON$CDB@clone_link;

Pluggable database created.

SQL>

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

NAME                           OPEN_MODE
------------------------------ ----------
DB12CPDB                       MOUNTED

SQL>
ALTER SESSION SET CONTAINER=db12cpdb;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

ALTER PLUGGABLE DATABASE db12cpdb OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

NAME                           OPEN_MODE
------------------------------ ----------
DB12CPDB                       READ WRITE

SQL>
</Code>

REMEMBER, as the cloned DB wasn't a PDB, we need to run the noncdb_to_pdb.sql.


==== Cross-Platform PDB Transport ====
We can perform also cross platform migration of PDB, however there are certain requirements:

  * COMPATIBLE: Greater or Equal to 12.2
  * OPEN_MODE: MOUNTED


{{:cross_platform_move.jpg?600|}}

This action is done either on the source server, when the backup is done, OR on the destination host, when the restore is done.

=== Backup ====
We can transform the data either on the source or the destination server. Different situations call for different solutions.

==On Source host==

<Code:bash|Rman backup & Conversion>
RMAN> BACKUP TO PLATFORM 'Linux x86_64-bit'
      UNPLUG INTO /tmp/pdb2.xml' PLUGGABLE DATABASE pdb1
      FORMAT '/bkp_dir/transport_%U'
</Code>

Bare in mind, as we backup to XML, that WILL NOT SAVE THE DATA, ONLY THE METADATA, you want the data, dump to .pdb file.

==On destination host==
<Code:bash|Rman backup & Conversion>
RMAN> BACKUP FOR TRANSPORT UNPLUG INTO /tmp/pdb2.xml'
      PLUGGABLE DATABASE pdb1 FORMAT '/bkp_dir/transport_%U';
</Code>

=== Restore ===
After we have moved the backup sets and the XML to the destination server. We can start an RMAN session and connect to the target CDB.
Also here, depending on where the conversation happened, we have two chocies.

== If conversation was on source host ==
<Code:bash|RMAN Restore & Conversion>
RMAN> RESTORE USING '/tmp/pdb2.xml' FOREIGN PLUUGABLE DATABASE pdb1 to NEW 
      FROM BACKUPSET '.bkp_dir/transport_0gqoejqv_1_1';
</Code>

== If conversation was on the destination host ==
<Code:bash|RMAN Restore & Conversation>
RMAN> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata/new_pdb';
RMAN> RESTORE FROM PLATFORM 'LINUX x86 64-bit' USING '/tmp/pdb2.xml'
      FOREIGN PLUGGABLE DATABASE pdb1 TO NEW
      FROM BACKUPSET '/bkp_dir/transport_0gqoejqv_1_1';
</Code>


===== Hot clone =====
There is however one BIG flow in that procedure. The problem is that we need the database (non-cdb) or even PDB to be in read-only mode. That pretty much kills all the desire to perform the procedure as the downtime would be too much for production.
Because of that from 12.2 we can do a hot clone, which eliminates that prerequisites :)

So let's see how it is done on both:

  * Clone another PDB
  * Clone not-CDB

So let's see what we need:
=== Prerequisites ===
<Code:bash|Prerequisites>
  * The user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the root container.
  * The remote CDB must use local undo mode. Without this you must open the remote PDB or non-CDB in read-only mode.
  * The remote database should be in archivelog mode. Without this you must open the remote PDB or non-CDB in read-only mode.
  * The local database must have a database link to the remote database. If the remote database is a PDB, the database link can point to the remote CDB using a common user, the PDB or an application container using a local or common user.
  * The user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASE privilege.
  * The local and remote databases must have the same endianness.
  * The local and remote databases must either have the same options installed, or the remote database must have a subset of those present on the local database.
  * If the character set of the local CDB is AL32UTF8, the remote database can be any character set. If the local CDB does not use AL32UTF8, the character sets of the remote and local databases much match.
  * If the remote database uses Transparent Data Encryption (TDE) the local CDB must be configured appropriately before attempting the clone. If not you will be left with a new PDB that will only open in restricted mode.
  * Bug 19174942 is marked as fixed in 12.2. I can't confirm this, so just in case I'll leave this here, but it should no longer be the case. The default tablespaces for each common user in the remote CDB *must* exist in local CDB. If this is not true, create the missing tablespaces in the root container of the local PDB. If you don't do this your new PDB will only be able to open in restricted mode (Bug 19174942).
  * When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.
  * In the examples below I have three databases running on the same virtual machine, but they could be running on separate physical or virtual servers.

cdb1 : The local database that will eventually house the clones.
db12c : The remote non-CDB.
cdb3 : The remote CDB, used for cloning a remote PDB (pdb5).
</Code>

====Cloning PDB====

===On remote===
On the remote machine, we have to however assure couple stuff:

<Code:bash|Create user and verify archivelog>
export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>
</Code>



===On local===
Now, we can create a DB link on the local machine and then we can create the PDB itself :)

<Code:bash|Create DB Link>
CDB3=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-server.my-domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb3)
    )
  )
  
export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE DATABASE LINK clone_link
  CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user USING 'cdb3';

-- Test link.
DESC user_tables@clone_link

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

Pluggable database created.

SQL>

COLUMN name FORMAT A30

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        MOUNTED

SQL>

ALTER PLUGGABLE DATABASE pdb5new OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        READ WRITE

SQL>
</Code>

====Cloning non-CDB====
Again with non-CDB, we can leave the DB open as long as it has the archivelog enabled:

===On remote===
<Code:bash|Verify archivelog & Create user>
export ORAENV_ASK=NO
export ORACLE_SID=db12c
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;
SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>
</Code>

Then we can go on the local and setup the database there

===On local===
As before we need to create a link and create the database
<Code:bash|Create database link>
DB12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my-server.my-domain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db12c)
    )
  )
  
export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE DATABASE LINK clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'db12c';

-- Test link.
DESC user_tables@clone_link
</Code>

Then we can create the database:

<Code:bash|Create database>
CREATE PLUGGABLE DATABASE db12cpdb FROM NON$CDB@clone_link;

Pluggable database created.

SQL>
COLUMN name FORMAT A30

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

NAME                           OPEN_MODE
------------------------------ ----------
DB12CPDB                       MOUNTED

SQL>
ALTER SESSION SET CONTAINER=db12cpdb;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

ALTER PLUGGABLE DATABASE db12cpdb OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

NAME                           OPEN_MODE
------------------------------ ----------
DB12CPDB                       READ WRITE

SQL>
</Code>