Monday, August 1, 2016

GUID Directory Not Created for PDBs During Full Restore in 12.1.0.2

Each database (non-CDB, CDB, PDB) on 12c has a generic unique identifier (GUID) associated with it. This GUID will not change throughout the life of the container/non-CDB. The 12c Admin guide says "the PDB GUID is primarily used to generate names for directories that store the PDB’s files, including both Oracle Managed Files directories and non-Oracle Managed Files directories". But it seems during RMAN restore the PDB related data files are not restored into a directory structure with GUID. There is a variant of this issue where "RMAN restore does not create GUID directory for pre-12c database" (16874123.8) which is fixed on 12.1.0.2 according to 1683802.1. The version used for testing is 12.1.0.2, so not sure if what's observed is a new bug or a different manifestation of the bug 16874123.
The source database has 3 PDBs (not counting the seed PDB) and data files are contained within a GUID directory.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         READ WRITE NO
         4 PDBTWO                         MOUNTED
         5 PDBTHREE                       MOUNTED

SQL> select con_id,name from v$datafile order by 1;

    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_bocndoww_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_bq3ngj79_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_bocng186_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_bocnf2j0_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_bocndyz0_.dbf
         2 /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_system_bocnds90_.dbf
         2 /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_sysaux_bocnf0mr_.dbf
         3 /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_sysaux_bqc9rzvz_.dbf
         3 /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_system_bqc9rzvt_.dbf
         3 /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_test_bqc9yss9_.dbf
         4 /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_sysaux_bqcb6642_.dbf
         4 /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_system_bqcb663x_.dbf
         5 /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_system_cq2d54cz_.dbf
         5 /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_sysaux_cq2d54dx_.dbf
A full backup of the database is taken and restored in another server which is identical to the source DB (OS, Oracle version including patch level etc). db_create_file_dest parameter is same on both source and restored DBs pfiles.
The restore output is shown below which indicates that PDB related files are restored into a directory structure with GUID.
RMAN> restore controlfile from '/home/oracle/backup/control.ctl';

Starting restore at 28-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/CDB12C/controlfile/o1_mf_csn2c773_.ctl
output file name=/opt/app/oracle/fast_recovery_area/CDB12C/controlfile/o1_mf_csn2c7hf_.ctl
Finished restore at 28-JUL-16

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 28-JUL-16
Starting implicit crosscheck backup at 28-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 28-JUL-16

Starting implicit crosscheck copy at 28-JUL-16
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 28-JUL-16

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_bocndoww_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_bocndyz0_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_bocnf2j0_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_bocng186_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_bq3ngj79_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5brboipc_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5brboipc_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00025 to /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_system_bqc9rzvt_.dbf
channel ORA_DISK_1: restoring datafile 00026 to /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_sysaux_bqc9rzvz_.dbf
channel ORA_DISK_1: restoring datafile 00027 to /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_test_bqc9yss9_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5crboir3_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5crboir3_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:18
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00028 to /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_system_bqcb663x_.dbf
channel ORA_DISK_1: restoring datafile 00029 to /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_sysaux_bqcb6642_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5drboirj_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5drboirj_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00039 to /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_system_cq2d54cz_.dbf
channel ORA_DISK_1: restoring datafile 00040 to /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_sysaux_cq2d54dx_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5erboisf_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5erboisf_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_system_bocnds90_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_sysaux_bocnf0mr_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5frboisv_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5frboisv_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 28-JUL-16

Starting recover at 28-JUL-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=221
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arch5grboitr_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/arch5grboitr_1_1 tag=TAG20160727T140338
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/fast_recovery_area/CDB12C/archivelog/2016_07_28/o1_mf_1_221_csn2jcf4_.arc thread=1 sequence=221
channel default: deleting archived log(s)
archived log file name=/opt/app/oracle/fast_recovery_area/CDB12C/archivelog/2016_07_28/o1_mf_1_221_csn2jcf4_.arc RECID=518 STAMP=918398107
unable to find archived log
archived log thread=1 sequence=222
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/28/2016 14:35:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 222 and starting SCN of 3544373
Running report schema shows that all the datafiles for PDBs are created at the same level as the CDB datafiles except for temp files used by PDBs which are listed with a directory path containing GUID.
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CDB12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    810      SYSTEM               ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2cvqv_.dbf
2    260      PDB$SEED:SYSTEM      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2hg6q_.dbf
3    980      SYSAUX               ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2cvqo_.dbf
4    595      PDB$SEED:SYSAUX      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2hg6g_.dbf
5    835      UNDOTBS1             ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
6    5        USERS                ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_csn2cvr5_.dbf
22   10       ROOTBS               ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_csn2cvr2_.dbf
25   270      PDBONE:SYSTEM        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
26   615      PDBONE:SYSAUX        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
27   10       PDBONE:TEST          ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf
28   270      PDBTWO:SYSTEM        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2g9g0_.dbf
29   605      PDBTWO:SYSAUX        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2g6mg_.dbf
39   260      PDBTHREE:SYSTEM      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2gz2w_.dbf
40   605      PDBTHREE:SYSAUX      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2gz2c_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_temp_bocnf32d_.tmp
2    20       PDB$SEED:TEMP        32767       /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_temp_bocnf33b_.tmp
3    20       PDBONE:TEMP          32767       /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_temp_bqc9rzw2_.dbf
4    20       PDBTWO:TEMP          32767       /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_temp_bqcb6645_.dbf
5    20       PDBTHREE:TEMP        32767       /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_temp_cq2d54f1_.dbf
However these temp files or the GUID directory doesn't exists on the filesystem.
$ ls -l /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/
ls: cannot access /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/: No such file or directory

$ ls -l /opt/app/oracle/oradata/CDB12C/
total 8
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:32 controlfile
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:34 datafile


Opening the database in reset log mode, will dynamically create the GUID directories and temp files inside them
RMAN> alter database open resetlogs;

Statement processed

$ ls -l /opt/app/oracle/oradata/CDB12C/
total 28
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 161DBD44EAAA7AC0E0535500A8C01F27
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 18037D5BA62802CEE0535500A8C0EC8F
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 1803968083DD030DE0535500A8C08A81
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 3643E46698E543F4E0535D00A8C0BDF3
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:32 controlfile
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:37 datafile
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:37 onlinelog

$ ls -l /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/
total 1024
-rw-r----- 1 oracle oinstall 20979712 Jul 28 14:37 o1_mf_temp_csn2n97z_.tmp
However the PDBs' datafiles are all in the same OMF path
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         READ WRITE NO
         4 PDBTWO                         MOUNTED
         5 PDBTHREE                       MOUNTED
SQL>  select con_id,name from v$datafile order by 1;

    CON_ID NAME
---------- ---------------------------------------------------------------------
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2cvqv_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_csn2cvr2_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_csn2cvr5_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2cvqo_.dbf
         2 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2hg6q_.dbf
         2 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2hg6g_.dbf
         3 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
         3 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
         3 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf
         4 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2g6mg_.dbf
         4 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2g9g0_.dbf
         5 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2gz2w_.dbf
         5 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2gz2c_.dbf
If a new tablespace or data file is added to one of the PDBs this will be created in a directory path with GUID.
SQL> alter session set container=pdbone;

SQL>  show con_name

CON_NAME
------------------------------
PDBONE

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf

SQL> create tablespace newtbs DATAFILE SIZE 10M;

Tablespace created.

SQL>  select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf
/opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_newtbs_csn2s0yz_.dbf
The restore was also tested by pre-creating the directory path with GUID, however there was no difference in the outcome. Also tested with latest PSU (July 2016) applied on source DB's home and new servers oracle home, and using a new backup taken from source DB after PSU applied. No difference in that case either. Also tested with patch 20464614 applied (mentioned in 1576755.1 in relation to OMF/ASM and PDBs). But didn't observe any difference in the final outcome.
A service request has been raised.

Useful metalink notes
Bug 16874123 - RMAN restore does not create GUID directory for pre-12c database [ID 16874123.8]
Step by Step Examples of Migrating non-CDBs and PDBs Using ASM for File Storage [ID 1576755.1]

Update 29 March 2017
This is fixed in 12.2. The PDB datafiles get restored into GUID directories.