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.