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:


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

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 PM   1720768  283115520
         3 3403102439 3403102439 F2A023F791663F8DE0430B2910AC37F7 PDBHANSOLO MOUNTED        17-FEB-14 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


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'

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.