11 Replies Latest reply: Feb 12, 2014 8:01 AM by rp0428 RSS

PDB Directory Structure with OMF

carajandb Oracle ACE
Currently Being Moderated

If one create a PDB using Oracle Managed Files (OMF) the directory structure for the files are using the GUID instead of the PDB Name. Is there any chance to change this? It's very difficult to find out which file belongs to which PDB if you have serveral PDBs up and running.

  • 1. Re: PDB Directory Structure with OMF
    ***Anuj*** Journeyer
    Currently Being Moderated

    Hi There!

     

    You can use FILE_NAME_CONVERT

     

    CONN / AS SYSDBA

     

    CREATE PLUGGABLE DATABASE PDB14 ADMIN USER pdb14adm IDENTIFIED BY secret

    FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb14/');

     

    checkout my webcast for further details on multitenant   : Oracle-12c : Lets Learn Together

     

    HTH

    Anuj Mohan

    Oracle12cSIG(IOUG)

  • 2. Re: PDB Directory Structure with OMF
    carajandb Oracle ACE
    Currently Being Moderated

    Hi Anuj

    that doesn't work for oracle managed files. If you create a tablespace without naming the datafile (as for OMF) the file is created in <GUID>/datafile.

  • 3. Re: PDB Directory Structure with OMF
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    I'm not aware that this can be changed. If you are on unix/linux you could create symbolic links from the GUID to the pdb name which might help?

     

    Cheers,

    Rich

  • 4. Re: PDB Directory Structure with OMF
    carajandb Oracle ACE
    Currently Being Moderated

    Hi Richard

    that's exactly what I did so far. But nevertheless Oracle might want to change that in a future release.

  • 5. Re: PDB Directory Structure with OMF
    rp0428 Guru
    Currently Being Moderated
    If one create a PDB using Oracle Managed Files (OMF) the directory structure for the files are using the GUID instead of the PDB Name. Is there any chance to change this? It's very difficult to find out which file belongs to which PDB if you have serveral PDBs up and running.

    What value of DB_CREATE_FILE_DEST are you using? Just set it to where you want the files created for the PDB.

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/omf003.htm#i1006430

    When creating a tablespace, either a permanent tablespace or an undo tablespace, the DATAFILE clause is optional. When you include the DATAFILE clause the filename is optional. If the DATAFILE clause or filename is not provided, then the following rules apply:

    •   If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle-managed datafile is created in the location specified by the parameter.

    Here is a simple example:

    SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/myDir/myNewPDB′;


    System altered.


    SQL> create pluggable database myNewPDB from myOldPDB;


    Pluggable database created.

    You can't control the actual filenames when using OMF, only the destination folder.

     

    Edited by: rp0428 to fix typo

  • 6. Re: PDB Directory Structure with OMF
    ***Anuj*** Journeyer
    Currently Being Moderated

    @rp0428

     

    SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/myDir//myNewPDB′;

     

    There are two "//" before myNewPDB. I think this is typo. Correct?

     

    Thanks,
    Anuj

  • 7. Re: PDB Directory Structure with OMF
    rp0428 Guru
    Currently Being Moderated

    Thanks - that was a typo.

  • 8. Re: PDB Directory Structure with OMF
    ***Anuj*** Journeyer
    Currently Being Moderated

    No Problem !!

     

    Also I noticed that if Set db_create_file_dest='C:\app\pdb3333'  it creates additional folders inside this folder to keep datafiles like C:\app\pdb3333\ANUJCDB\6030BFFDC6054EF0AE187B904A4B039A\DATAFILE

     

    Thanks,
    Anuj

  • 9. Re: PDB Directory Structure with OMF
    rp0428 Guru
    Currently Being Moderated

    Please mark the thread ANSWERED.


  • 10. Re: PDB Directory Structure with OMF
    carajandb Oracle ACE
    Currently Being Moderated

    rp0428 wrote:

     

    If one create a PDB using Oracle Managed Files (OMF) the directory structure for the files are using the GUID instead of the PDB Name. Is there any chance to change this? It's very difficult to find out which file belongs to which PDB if you have serveral PDBs up and running.

    What value of DB_CREATE_FILE_DEST are you using? Just set it to where you want the files created for the PDB.

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/omf003.htm#i1006430

    When creating a tablespace, either a permanent tablespace or an undo tablespace, the DATAFILE clause is optional. When you include the DATAFILE clause the filename is optional. If the DATAFILE clause or filename is not provided, then the following rules apply:

    •   If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle-managed datafile is created in the location specified by the parameter.

    Here is a simple example:

    SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/myDir/myNewPDB′;


    System altered.


    SQL> create pluggable database myNewPDB from myOldPDB;


    Pluggable database created.

    You can't control the actual filenames when using OMF, only the destination folder.

     

    Edited by: rp0428 to fix typo

     

    Your comment about the optional parameter DATAFILE is only valid if you don't want to change any of the default values for size, extendsize and maxsize. As I don't like "UNLIMITED" for the maxsize I have to specify DATAFILE according to the documentation:

    Oracle 12c Database Administrator's Guide 12c: "However, if in your DATAFILE clause you override these defaults by specifying a SIZE value (and no AUTOEXTEND clause), then the data file is not autoextensible."

     

    Your example makes the directory structure even more complex: Oracle always adds the name of the database and the GUID to the directory. So for your example you end up with the following directory structure:

     

    /u01/app/oracle/oradata/myDir/myNewPDB/<CDB_NAME>/<GUID>


    Her's an example:

    SQL> ALTER SYSTEM SET db_create_file_dest='/u02/oradata/JO3';

    SQL> CREATE PLUGGABLE DATABASE JO3 ...

    SQL> SELECT name FROM v$datafile WHERE CON_ID=7;

    NAME

    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    /u02/oradata/JO3/CJOHANN/F23385330E6F7CE2E043411E10ACD344/datafile/o1_mf_system_9hpk1l9f_.dbf

    /u02/oradata/JO3/CJOHANN/F23385330E6F7CE2E043411E10ACD344/datafile/o1_mf_sysaux_9hpk1l9t_.dbf

     

  • 11. Re: PDB Directory Structure with OMF
    rp0428 Guru
    Currently Being Moderated
    Your comment about the optional parameter DATAFILE is only valid if you don't want to change any of the default values for size, extendsize and maxsize.

    Reread my reply: that isn't 'my comment': it is a quote from the Oracle documentation.

    Your example makes the directory structure even more complex:

    I don't see that as any more 'complex' than the normal that Anuj showed in the FILE_NAME_CONVERT example:

    FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb14/');

    You can't control the name when you ask Oracle to name the files for you. Oracle will use GUID to ensure that the name is globally unique.

     

    The only thing you can control is where the files are actually created. It is up to you whether you use the default or specify your own value.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points