Can you create more than one pluggable database (PDB) within the same directory?
And how does the file naming work? Considering the fact each PDB’s SYSTEM tablespace will be named system01.dbf by default the question is not trivial.
This question got asked by a customer during one of the workshops in Switzerland last week. And the solution is straight forward. Thanks to Roy for trying it out yesterday at 170 km/h on our way back from Stuttgart 🙂
Thanks 🙂
-Mike
Additional information:
Within ASM with OMF the file structure looks like this:
select con_id, substr(file_name,1,90),tablespace_name from cdb_data_files order by 1; CON_ID SUBSTR(FILE_NAME,1,90) TABLESPACE_NAME ---------- -------------------------------------------------------------------------------- --------------- 1 +DA1/CDBUPGR/DATAFILE/system.394.845632641 SYSTEM 1 +DA1/CDBUPGR/DATAFILE/users.475.845632685 USERS 1 +DA1/CDBUPGR/DATAFILE/undotbs4.448.845632683 UNDOTBS4 1 +DA1/CDBUPGR/DATAFILE/sysaux.392.845632651 SYSAUX 1 +DA1/CDBUPGR/DATAFILE/undotbs2.393.845632679 UNDOTBS2 1 +DA1/CDBUPGR/DATAFILE/undotbs1.471.845632657 UNDOTBS1 1 +DA1/CDBUPGR/DATAFILE/undotbs3.478.845632681 UNDOTBS3 2 +DA1/CDBUPGR/F7B70DCBF2D4ECEAE0437A28890AE4D8/DATAFILE/sysaux.472.845632655 SYSAUX 2 +DA1/CDBUPGR/F7B70DCBF2D4ECEAE0437A28890AE4D8/DATAFILE/system.398.845632647 SYSTEM 3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/system.493.845643325 SYSTEM 3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/sysaux.468.845643325 SYSAUX 3 +DA1/CDBUPGR/F6A142792168D540E0437A28890A4707/DATAFILE/soets.452.845643325 SOETS 4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/system.491.845643937 SYSTEM 4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/sysaux.488.845643937 SYSAUX 4 +DA1/CDBUPGR/F7B9BDC2AEC4411EE0437A28890A2B81/DATAFILE/soets.484.845643937 SOETS 5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/system.485.845644149 SYSTEM 5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/sysaux.490.845644149 SYSAUX 5 +DA1/CDBUPGR/F7B9CA6B92804A56E0437A28890A2721/DATAFILE/soets.487.845644149 SOETS 6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/system.486.845644363 SYSTEM 6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/sysaux.483.845644363 SYSAUX 6 +DA1/CDBUPGR/F7B9D727715B5B4AE0437A28890AB3D9/DATAFILE/soets.481.845644363 SOETS 7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/system.453.845644575 SYSTEM 7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/sysaux.482.845644575 SYSAUX 7 +DA1/CDBUPGR/F7B9E3D23CFC67F1E0437A28890A5A68/DATAFILE/soets.467.845644575 SOETS 8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/system.465.845644785 SYSTEM 8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/sysaux.455.845644785 SYSAUX 8 +DA1/CDBUPGR/F7B9F051E81B7892E0437A28890AD3A3/DATAFILE/soets.479.845644785 SOETS 9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/system.464.845645805 SYSTEM 9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/sysaux.500.845645805 SYSAUX 9 +DA1/CDBUPGR/F7BA2D0F2F17A755E0437A28890A72C6/DATAFILE/soets.498.845645805 SOETS 10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/system.499.845646023 SYSTEM 10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/sysaux.504.845646023 SYSAUX 10 +DA1/CDBUPGR/F7BA3A179DAFB12FE0437A28890ABBF3/DATAFILE/soets.502.845646023 SOETS 11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/system.503.845646233 SYSTEM 11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/sysaux.508.845646233 SYSAUX 11 +DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/soets.506.845646233 SOETS ...
Hi mike.
Can you show a screenshot of the files in the Directory by doing a ls -ail
Mike,
is there way can we take it out GUID from datafile directory structure?
Example.
This datafile signiture with GUID
+DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/soets.506.84564623
We need directory structure like below whenever we create new tablespace under pluggable db. (Without GUID)
+DA1/CDBUPGR/DATAFILE/soets.506.84564623
Thanks
Yousuf
Yousuf,
spontaneously I would exclaim:
ALTER DATABASE MOVE DATAFILE ‘+DA1/CDBUPGR/F7BA46A1A6B7B9C2E0437A28890AE021/DATAFILE/soets.506.84564623’ TO ‘+DA1/CDBUPGR/DATAFILE/soets.506.84564623’;
as workaround. 12c EE feature – online datafile move.
Next idea would be to be to use the PDB_FILE_NAME_CONVERT parameter – but I haven’t tried it.
Or CREATE_FILE_DEST.
Cheers
Mike