Orphaned Clone PDB

I just had some fun with Oracle 12c PDBs and snapshot clones. In the alert.log I found some unexpected errors “ORA-01157: cannot identify/lock data file XX”. The file was part of a PDB which I used for testing. So I investigated that and found the file which was pretty small. So my assumption was that this must be related to PDB cloning.
My simple testcase proofed that.

Step 1: Create Parent PDB

SQL> create pluggable database parent12c admin user pdbadmin identified by oracle;

Pluggable database created.

Step 2: Open Parent PDB Read Only

SQL> alter pluggable database parent12c open read only;
alter pluggable database parent12c open read only
*
ERROR at line 1:
ORA-65085: cannot open pluggable database in read-only mode

Ok, that is not possible at this time. I need to open the PDB in read-write mode and then close it again to then open it read-only.

SQL> alter pluggable database parent12c open;

Pluggable database altered.

SQL> alter pluggable database parent12c close;

Pluggable database altered.

SQL> alter pluggable database parent12c open read only;

Pluggable database altered.

Step 3: Create Child PDB as snapshot copy

SQL> create pluggable database child12c from  parent12c snapshot copy;

Pluggable database created.

Step 4: See, what we have

SQL> select con_id, NAME, SNAPSHOT_PARENT_CON_ID from v$pdbs;

    CON_ID NAME       SNAPSHOT_PARENT_CON_ID
---------- ---------- ----------------------
         2 PDB$SEED                        0
         3 PDB12C                          0
         4 PARENT12C                       0
         5 CHILD12C                        0

Obviously the CHILD12C is not aware of it’s parent.

Step 5: Drop Parent PDB, and check

SQL> drop pluggable database parent12c including datafiles;

Pluggable database dropped.

SQL> select con_id, NAME, SNAPSHOT_PARENT_CON_ID from v$pdbs;

    CON_ID NAME       SNAPSHOT_PARENT_CON_ID
---------- ---------- ----------------------
         2 PDB$SEED                        0
         3 PDB12C                          0
         5 CHILD12C                        0

Step 6: Open Child PDB

SQL> alter pluggable database child12c open;
alter pluggable database child12c open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 28 - see DBWR trace file
ORA-01110: data file 28:
'/u01/app/oracle/oradata/CDB12C/32058CE3DA4B1D31E0530100007FC00B/datafile/o1_mf_
sysaux_cln024vx_.dbf'

Voila, there is the error.

Step 7: Check further

So I dropped the child PDB too and went through steps 1-4 again. But this time I opened the child PDB.

SQL> alter pluggable database child12c open;

Pluggable database altered.

SQL> select con_id, NAME, SNAPSHOT_PARENT_CON_ID from v$pdbs;

    CON_ID NAME       SNAPSHOT_PARENT_CON_ID
---------- ---------- ----------------------
         2 PDB$SEED                        0
         3 PDB12C                          0
         4 PARENT12C                       0
         5 CHILD12C                        4

The CHILD12C is now aware of it’s parent. But that is not sufficient as I learned. See what I can do anyway:

SQL> drop pluggable database parent12c including datafiles;

Pluggable database dropped.

SQL> select con_id, NAME, SNAPSHOT_PARENT_CON_ID from v$pdbs;

    CON_ID NAME       SNAPSHOT_PARENT_CON_ID
---------- ---------- ----------------------
         2 PDB$SEED                        0
         3 PDB12C                          0
         5 CHILD12C                        0

There is no check that prevents us from dropping the parent PDB. Not good.
Now my orphaned CHILD12C stopped working again.

SQL> alter pluggable database child12c close;

Pluggable database altered.

SQL> alter pluggable database child12c open;
alter pluggable database child12c open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 32 - see DBWR trace file
ORA-01110: data file 32:
'/u01/app/oracle/oradata/CDB12C/32058CE3DA4D1D31E0530100007FC00B/datafile/o1_mf_
sysaux_cln0byyl_.dbf'

Conclusion

Crosscheck on your own and at least twice before dropping PDBs, especially if you are using the “snapshot clone” feature.