Clonewars – Next Gen Cloning with Oracle 12.2 Multitenancy (Part Un)

In this blog, we will walk through Oracle 12.2 Remote Cloning of PDB feature. In Oracle 12.1, remote cloning was also available, however, this required placing the productions database (which is usually the source) in read-only mode. This makes the cloning feature very inefficient to leverage. In 12.2, it is now possible to maintain the production database in read-write mode and allow for online copy of the database, this is reffered to as a “hot clone”.  The distinction between a hot clone and a cold clone is only relevant for customers running 12.1 Multitenancy. As of 12.2 all clones are hot clones, unless the source database is explicitly closed.

We will illustrate two examples of this real-world example, just the names have been changed to protect the extremely innocent. And sorry about the StarWars references.. just couldn’t help myself!!

Note, for clarity, the remote DB is source database which will cloned, and the local DB is the CDB where the PDB will cloned into.

Cloning Example 1: Remote clone copy from existing non-CDB into a local PDB (non-CDB->PDB).  In this example “hansolo” is remote non-CDB (source PDB).

Cloning Example 2: Remote clone copy from existing CBD/PDB into a local PDB (PDB->PDB). In this example “darkside” is CDB with obiwan being the source PDB and  nishan-obiwan (PDB) is local.

Cloning Example 1

Prep work and validation
 Hansolo$SQL> startup
 ORACLE instance started.
 Total System Global Area 2483027968 bytes
 Fixed Size 8795808 bytes
 Variable Size 637536608 bytes
 Database Buffers 1610612736 bytes
 Redo Buffers 7979008 bytes
 In-Memory Area 218103808 bytes
 Database mounted.
 Database opened.
Hansolo$SQL> select database_name from v$database;
 DATABASE_NAME
 ------------------------------------------------------
 HANSOLO

Nishan$SQL> select name from v$pdbs;
 NAME
 ------------------------------------------------------------------------------
 PDB$SEED
 OBIWAN

In 12.2, each PDB will have its own undo tablespace. 
This new undo management configuration is called local undo mode, and is the underlying 
design for many of the portability features in 12.2. Local Undo is the default for greefield/fresh 12.2 installs, 
for upgrades to 12.2 the Shared Undo will need to converted to Local (we won't cover that here)
 
Hansolo$SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED ';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE

Hansolo$SQL> archive log list
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 118
 Next log sequence to archive 120
 Current log sequence 120

Hansolo$SQL> select name, open_mode from v$database
NAME OPEN_MODE
--------- --------------------
HANSOLO READ WRITE

Hansolo$SQL> create tablespace kyloren datafile size 20M;

Tablespace created.

Hansolo$SQL> create tablespace MazKanata datafile size 20M

Tablespace created.

Hansolo$SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS1
 TEMP
 USERS
 KYLOREN
 MAZKANATA

Hansolo$SQL> select current_scn from v$database;

CURRENT_SCN
 -----------
 27506427

$cat hansolo_create_remoteclone.sql
 CREATE USER cloneuser IDENTIFIED BY cloneuser123;
 GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO cloneuser;

Hansolo$SQL>@hansolo_create_remoteclone.sql

Verify user connection

Hansolo$SQL> connect cloneuser/cloneuser123;
 Connected.

Now, prep the source environment

Nishan$SQL> select database_name from v$database;

DATABASE_NAME
-------------------------------------------------
NISHAN

Create DBLink to hansolo from nishan

$cat pdbclone_dblink.sql
CREATE DATABASE LINK remoteclonelink CONNECT TO cloneuser IDENTIFIED BY 
cloneuser123 USING 'hansolo'

Nishan$SQL> @pdbclone_dblink.sql

Nishan$SQL> select db_link, host from dba_db_links;
DB_LINK            HOST
----------------  -----------------
SYS_HUB           SEEDDATA 
REMOTECLONELINK   hansolo 

Verify connection to hansolo from forceawakens PDB

$ sqlplus cloneuser/cloneuser123@hansolo

Nishan$SQL> create pluggable database forceawakens from non$cdb@REMOTECLONELINK;

Pluggable database created.

Nishan$SQL> alter session set container = FORCEAWAKENS;
Session altered.

forceawakens$SQL> select name, open_mode from v$pdbs;
 NAME           OPEN_MODE
---------       ----------------------
FORCEAWAKENS    MOUNTED

forceawakens$SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------
 +DATA/NISHAN/4E6DBABFDE2EBBECE0538514A8C0247B/DATAFILE/system.302.942700581
 +DATA/NISHAN/4E6DBABFDE2EBBECE0538514A8C0247B/DATAFILE/sysaux.301.942700581
 +DATA/NISHAN/4E6DBABFDE2EBBECE0538514A8C0247B/DATAFILE/undotbs1.300.942700581
 +DATA/NISHAN/4E6DBABFDE2EBBECE0538514A8C0247B/DATAFILE/users.297.942700581
 +DATA/NISHAN/4E6DBABFDE2EBBECE0538514A8C0247B/DATAFILE/kyloren.298.942700581
 +DATA/NISHAN/4E6DBABFDE2EBBECE0538514A8C0247B/DATAFILE/mazkanata.299.942700581

forceawakens$SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 0

Since the source database was a non-CDB, it needs to be cleansed to be PDB-capable using the @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql. This is a requirement before you can open and online the PDB.

forceawakens$SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

forceawakens$SQL> alter pluggable database open;

Oh..GUID of PDB World


We have done a lot talks, sessions, blogs on Oracle 12c Pluggable Databases (PDB).  The question that seems to come up a lot is what is this long string of alphanumeric characters embedded in the database file name.

Before we answer that, let's take a trip down memory lane and understand how OMF works and its relationship with this GUID

Oracle Managed Files (OMF) was a feature introduced in 9i to minimize the overhead of managing database files.  Part of this feature is the database automatic naming of database files (on successful file creation). 
Files are named using system generated names and placed in the location as defined by the DB_CREATE_FILE_DEST init.ora .  In Data Guard configurations there are *_FILE_NAME_CONVERT, STANDBY_FILE_MANAGEMENT to assist with converting names of existing and newly created datafiles when OMF is in use.


OMF really came into play with widespread use, because of the implementation of ASM.  When ASM is used, OMF is inherently used for file management.  The ASM-OMF directory structure for datafiles traditionally consists of //DATAFILE/.  A traditional file name in ASM consists of 3 parts, ...  For example:


+PDBDATA/YODA/DATAFILE/system.258.8238921091

Note,Users are not allowed to directly create files with this naming structure, if you try you'll get a single-form file name error ORA-15046!


So what does this GUID thingy mean for 12c PDB configurations with ASM-OMF.  In addition to the OMF file naming and directory structure (discussed above), there is an embedded global unique identifier (GUID).  The GUID is globally unique immutable ID assigned to the 12c database at creation time.  Each 12c database, whether its non-CDB, CDB, or PDB, has a GUID associated with it.  Thus, with PDB, the directory structure changes for each pluggable database (PDB) in a container database (CDB). 


For pre-12c non-CDB databases, the GUID will be created when the database is upgraded to 12c.  


There are so many identifiers for a 12c database, let's make sure we get this straight. There's dbid, con_id, con_uid, and guid. The DBID is the database id embedded in the database file, control file, redo log header. The con_id is simply a container number in that specific CDB, starts with 0 and 1 is for root PDB. The con_uid is a local unique identifier within that CDB. The GUID is universal across all CDB/PDB.  Keep in mind that we can unplug a PDB from one CDB into another CDB, so the GUID provides this uniqueness and streamlines portability. More on this later!


The following query shows the different 12c database identifiers:

CDB$ROOT@YODA> select CON_ID,DBID,NAME,TOTAL_SIZE from v$pdbs;    
CON_ID      DBID     NAME                     TOTAL_SIZE
---------- ---------- -------------          -------------      
2    4066465523 PDB$SEED                      283115520      
3     483260478 PDBOBI                        917504000      
4     994649056 PDBVADER                              0

Note, that the GUID does not change throughout the life of the PDB/non-CDB. The GUID for a particular container/non-CDB can be found by querying V$CONTAINERS or v$PDBs. To assist with identifying which files belong to which PDB, an ASM directory structure of ///DATAFILE/ is used for PDBs. This is one of the main reasons a PDB should be cloned (cloning generates a new GUID) rather than copying the same PDB to multilple locations and plugging in to multiple CDBs.

See the example below, for GUID:

 
CDB$ROOT@YODA> select name, con_id from v$datafile order by con_id
NAME                                                                                    CON_ID
----------------------------------------------------------------------------------- ----------
+PDBDATA/YODA/DATAFILE/undotbs1.260.823892155                                                1
+PDBDATA/YODA/DATAFILE/sysaux.257.823892063                                                  1
+PDBDATA/YODA/DATAFILE/system.258.823892109                                                  1
+PDBDATA/YODA/DATAFILE/users.259.823892155                                                   1
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.823892297                 2
+PDBDATA/YODA/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.823892297                 2
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.275.823892813                3
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.277.823892813                  3
+PDBDATA/YODA/E456D87DF75E6553E043EDFE10AC71EA/DATAFILE/obiwan.284.824683339                 3
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/system.276.823892813                 3
+PDBDATA/YODA/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.274.823892813                 3
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/sysaux.279.823980769                 4
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/users.281.823980769                  4
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/example.282.823980769                4
+PDBDATA/YODA/E46B24386A131109E043EDFE10AC6E89/DATAFILE/system.280.823980769                 4

That long identifier, "E46B24386A131109E043EDFE10AC6E89", in the OMF name, is the GUID.

Now a similar example from ASM (asmcmd) perspective

ASMCMD [+PDBDATA] > ls -l dagobah
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    DD7C48AA5A4404A2E04325AAE80A403C/
                                                 Y    F2F952556B226FA5E0430B2910AC1FE5/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
                                                 Y    TEMPFILE/
PASSWORD       UNPROT  COARSE   FEB 21 23:00:00  N    orapwdagobah => +PDBDATA/DAGOBAH/PASSWORD/pwddagobah.293.840152893
PARAMETERFILE  UNPROT  COARSE   APR 09 15:00:00  N    spfiledagobah.ora => +PDBDATA/DAGOBAH/PARAMETERFILE/spfile.310.840153477

ASMCMD [+PDBDATA/TATOOINE/F2F7CA2C1F1F0593E0430A2910AC246A/datafile] > ls
SYSAUX.258.840146475
SYSTEM.286.840146475

Let's look at two examples of PDB creation and the GUID.
Example1. This example illustrates the PDB creation and GUID


SQL> CREATE PLUGGABLE DATABASE pdbhansolo admin user hansolo identified by hansolo roles=(dba);

Pluggable database created.

SQL> select * from v$pdbs ;


    CON_ID       DBID    CON_UID GUID                             NAME      OPEN_MODE  RES     OPEN_TIME             CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------- ------   -----------           ----------- ------------
         2 4080865680 4080865680 F13EFFD958E24857E0430B2910ACF6FD PDB$SEED   READ ONLY  NO  17-FEB-14 01.01.13.909 PM   1720768  283115520
         3 3403102439 3403102439 F2A023F791663F8DE0430B2910AC37F7 PDBHANSOLO MOUNTED        17-FEB-14 01.27.08.942 PM   1846849          0

Example two. Here, we are going to plug in (convert) a PDB from a non-CDB. Note, that we can see the GUID in manifest file. In the XML output below (from manifest xml file), you see the GUID listed for this non-CBD

<PDB>
  <pdbname>wookie</pdbname>
  <cid>0</cid>
  <byteorder>1</byteorder>
  <vsn>202375168</vsn>
  <dbid>2940614436</dbid>
  <cdbid>2940614436</cdbid>
  <guid>F2BBDF340FFE3E90E0430B2910AC097F</guid>

Now connect to the CDB and create the Wookie PDB from the manifest file

CDB_SQL>CREATE PLUGGABLE DATABASE wookie USING '/home/oracle/wookie_pdb.xml'
  NOCOPY;


Pluggable database created.

SQL> select name, open_mode from v$pdbs;


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

SQL> select name, guid from v$pdbs;


NAME                           GUID
------------------------------ --------------------------------
PDB$SEED                       F13EFFD958E24857E0430B2910ACF6FD
WOOKIE                         F2BBDF340FFE3E90E0430B2910AC097F

Here's where the big issue comes in. Many DBAs have mentioned to me that there is no real way to identify the PDB by solely looking at the path name. We do however, know the name of the CDB its in, but that's as far as we can go. In order to determine the PDB associated with the file, you would need to login directly to PDB (not even the CDB), and get the name

Initially there are some issues w/ GUID/OMF/ASM when are files are copied, and a physical standby database is in place. There have been improvements made to the multitenant plugin operation on both the primary and standby environments, e.g., You need PSU 2 at least, then DataGuard will do the right thing when you plug on a new pdb at the primary after making sure the files are at the standby first. RMAN has been enhanced so that, when copying files between databases it recognizes the GUID and acts accordingly when writing the files.

Here are some additional RMAN considerations for GUID management

* If the clone/auxiliary instance being connected to for clone operations is a CDB root, the GUID of the RMAN target database is used to determine the directory structure to write the datafiles. Connect to the CDB root as the RMAN clone/auxiliary instance when the source database should be a 12c non-CDB or PDB that is going to be migrated and plugged into a remote CDB as a brand new PDB. This will ensure that the files copied by RMAN will be written to the GUID directory of source database for the migration.
* If the clone/auxiliary instance being connected to for clone operations is a PDB, the GUID of the auxiliary PDB will be used to determine the directory structure to write the datafiles. Connect to the destination PDB as the RMAN clone auxiliary instance when the source database is a 12c non-CDB or PDB that requires a cross platform full transportable database import and the data and files will be imported into an existing PDB. This will ensure the files copied by RMAN will be written to the GUID directory of the PDB target database for the migration.

* The enhancements for multitenant plugin operations with OMF simplify the process extensively. The manifest generated on the source non-CDB/PDB contains all of the filenames and characteristics about each file. Normally, the plugin operation would use the filenames in the manifest and look for those exact filenames or partially converted (using the SOURCE_FILE_NAME_CONVERT clause on the CREATE PLUGGABLE DATABASE....USING...statement). Since all filenames will be different when copied to a new location when OMF is used, you would need to specify full directory and filename convert pairs for EACH file being plugged in. By using the SOURCE_FILE_DIRECTORY clause on the CREATE PLUGGABLE DATABASE....USING... statement in the plugin operation, the filename in the manifest is ignored and the plugin looks for a file to match additional characteristics about the file stored in the manifest, looking for the file in the SOURCE_FILE_DIRECTORY location.