Where do I create a create_file_dest directory for a pdb?

Question

I'm trying to learn how to create a PDB from PDB$SEED and I just have one question about this video that has been great at spoon feeding me information. What program is this guy using to mkdir for his create_file_dest directory? Also, where is he creating this directory? Or more importantly, where should one create this directory and how do I know that i'm in the right place? He just exits and creates a directory.. but where?

Answer 1

You're following examples related to Unix/Linux O/s.From your other posts your directory structure is really a big mess and top of all you are creating directories for PDB in Oracle home or Oracle binaries that's a big NO

e.g C:\App\db_home\WINDOWS.X64_193000_db_home>mkdir -p /u01/pdb1/

Follow the Oracle documentation for Windows OFA.

Oracle 19c set up on my Personal desktop

C:\echo %ORACLE_HOME%
C:\app\oracle\product\19.3.0\dbhome_1 --(on your system oracle home should be C:\app\elle\product\19.3.0\dbhome_1)

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter db_create_file_dest

PARAMETER_NAME                  TYPE        VALUE
------------------------------  ----------  -----------
db_create_file_dest             string      D:\oradata

CDB/PDB datafiles (this is my personal desktop created ORADATA on different drive but as per OFA data files should be placed in ORADATA dir in ORACLE_BASE dir e.g C:\app\oracle\ORADATA)

        SQL>SELECT name FROM v$datafile
        NAME
        ----------------------------------------------------------------------
        D:\ORADATA\ORCL\SYSTEM01.DBF
        D:\ORADATA\ORCL\SYSAUX01.DBF
        D:\ORADATA\ORCL\UNDOTBS01.DBF
        D:\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF
        D:\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
        D:\ORADATA\ORCL\USERS01.DBF
        D:\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF
        D:\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
        D:\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
        D:\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
        D:\ORADATA\ORCL\ORCLPDB\USERS01.DBF
        D:\ORADATA\ORCL\ORCLPDB\EXAMPLE01.DBF

As you can see based on parameter db_create_file_dest all data files are created in root folder D:\ORADATA.Created pdb with parameter file_name_convert('D:\ORADATA\ORCL\PDB$SEED','D:\ORADATA\ORCL\ORCLPDB') instead of using db_create_file_dest(since it's already set in initialization parameter)

Follow the examples with DBCA or SQLPLUS (these examples are on Linux O/S)

Here is the fun part creating tablespaces in PDB

Switch to PDB container

    SQL> CREATE TABLESPACE test;   -- 1 without datafile parameter
    
    Tablespace created.
    
    
    SQL> alter session set db_create_file_dest='D:\oradata\ORCL\orclpdb';   --2
    
    Session altered.
    
    SQL> create tablespace test01;
    
    Tablespace created.
    
    SQL> create tablespace test03 datafile 'D:\ORADATA\ORCL\ORCLPDB\TEST03.DBF' size 100M;  --3 with datafile parameter
    
    Tablespace created.
    SQL>COL T_NAME FORMAT A6
    SQL>select t.name t_name,d.name datafile
    from v$datafile d
    join v$tablespace t
    on d.ts#=t.ts#
    where t.ts# in (select ts# from v$tablespace
    where name like 'TEST%')
     
    T_NAME DATAFILE
     ------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    TEST   D:\ORADATA\ORCL\4A506465BEFC4D16865F184CA10BAD44\DATAFILE\O1_MF_TEST_HNRC8S4O_.DBF                 --1
    TEST01 D:\ORADATA\ORCL\ORCLPDB\ORCL\4A506465BEFC4D16865F184CA10BAD44\DATAFILE\O1_MF_TEST01_HNRCMPH1_.DBF  --2
    TEST03 D:\ORADATA\ORCL\ORCLPDB\TEST03.DBF  --3

Avoid statement number 2.Oracle uses internal data file naming convention for tablespaces created without datafile parameters.

Edit:-

Directories in Oracle Base

And ofcourse ORADATA dir should be there (since I set ORADATA in differnt drive it's not in the image).

In future on windows First make oracle home dir from command prompt(I have 18.3 zip files.)

C:\mkdir -p C:\app\oracle\product\18.3\db_home1

Then extract into above dir

Answer 2

What do you mean "What program is this guy using to mkdir for his create_file_dest file? "

He is at the shell prompt. He is using the shell's own internal 'mkdir' command.

Where is he creating this file? It's not a file, it's a directory. WHEN you finally get to set CREATE_FILE_DEST (which is an initialization parameter for the database) then then database will use that directory as the default location for any data files created with the oracle CREATE TABLESPACE or ALTER TABLESPACE .. ADD DATAFILE' commands.

Answer 3

The answer is it doesn't matter where the CREATE_FILE_DEST directory is.