Online Datafile movement inconsistencies in 12.2

Is always nice to have some test systems where you can do testing and even find some inconsistencies between system versions behaviour, specially when we talk about new features.

I recently watched a video from the fantastic channel Oracle Database Upgrades and Migrations related to Online Datafile movement which started in 12c

For some reason in 12.2 version, the system will chose a different location when we create a new datafile than when we just do the datafile move command.

I shared my thoughts and test in Daniel’s blog post

I decided to try a bit more and confirm as Daniel mentioned, that this seems to be only affected old versions like 12.2.

Here is a bit of the tests and troubleshooting I decided to do on this.

The first thing I did after creating a 12.2 No-ASM Single Instance Database, was to make sure the parameter db_create_file_dest is set correctly (after the Database creation was empty) and make sure is the same inside our PDB (SINGLE_PDB) created from the DBCA command

[oracle@rac1-node1 ~]$ dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname single -sid single -responseFile NO_VALUE \
> -characterSet AL32UTF8 \
> -sysPassword Welcome1 \
> -systemPassword Welcome1 \
> -createAsContainerDatabase true \
> -numberOfPdbs 1 \
> -pdbName SINGLE_PDB \
> -databaseType MULTIPURPOSE \
> -memoryMgmtType auto_sga \
> -totalMemory 1536 \
> -storageType FS \
> -datafileDestination "/u01/app/oracle/oradata/" \
> -redoLogFileSize 50 \
> -emConfiguration NONE \
> -ignorePreReqs
Enter PDBADMIN User Password: 
[....]

[oracle@rac1-node1 ~]$ sqlplus / as sysdba
[....]

SQL> show parameter db_create_file_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest          string

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/';

System altered.

SQL> show parameter db_create_file_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest          string  /u01/app/oracle/oradata/
SQL> 

SQL> alter session set container=SINGLE_PDB;

SQL> show con_name

CON_NAME
------------------------------
SINGLE_PDB

SQL> show parameter db_create_file_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest          string  /u01/app/oracle/oradata/

Lets see the PDB default Datafiles location after the Database is created:

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/single/SINGLE_PDB/system01.dbf
    10 SYSAUX       /u01/app/oracle/oradata/single/SINGLE_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/single/SINGLE_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/single/SINGLE_PDB/users01.dbf

SQL> 

When we create a new Tablespace however, the new datafile will be created in same default location but adding "PDB GUID" directory, "datafile" and the file will be having an "OMF name":

SQL> CREATE TABLESPACE P42 ;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/single/SINGLE_PDB/system01.dbf
    10 SYSAUX       /u01/app/oracle/oradata/single/SINGLE_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/single/SINGLE_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/single/SINGLE_PDB/users01.dbf

    13 P42          /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/o1_mf_p42_j37bj2js_.dbf <<<<<<<<<

Ideally, if we now move the Default datafiles created during Database creation, we should be moving them to the same location that the datafiles for the P42 Tablespace… at least that is what I thought.
Lets try it out:

SQL> alter database move datafile 9;

Database altered.

SQL> SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE/datafile/o1_mf_system_j37cj990_.dbf <<<<<<<<<

    10 SYSAUX       /u01/app/oracle/oradata/single/SINGLE_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/single/SINGLE_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/single/SINGLE_PDB/users01.dbf
    13 P42          /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/o1_mf_p42_j37bj2js_.dbf 

As we can see, the system generated a new "OMF name" for the datafile, but it was not moved to the same location where we have the P42 tablespace datafile, but to a "new location" that is not its orginal location or the same location of the new Tablespace.

Let’s force the system to bring the datafile to another location using a manual name string:

SQL> alter database move datafile 9 to '/u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/system01.dbf';

Database altered.

SQL> SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/system01.dbf <<<<<<<<<

    10 SYSAUX       /u01/app/oracle/oradata/single/SINGLE_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/single/SINGLE_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/single/SINGLE_PDB/users01.dbf
    13 P42          /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/o1_mf_p42_j37bj2js_.dbf

What if we try to get "OMF name" now that we have it in that location?
For some reason, datafile will be moved back to the previous location

SQL> alter database move datafile 9;

Database altered.

SQL>  SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE/datafile/o1_mf_system_j37cxzjg_.dbf <<<<<<<<<

    10 SYSAUX       /u01/app/oracle/oradata/single/SINGLE_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/single/SINGLE_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/single/SINGLE_PDB/users01.dbf
    13 P42          /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/o1_mf_p42_j37bj2js_.dbf

Things can actually get a bit more messy when we decide to add datafiles to that same SYSTEM tablespace, since the system will now decide to use the location with the PDB GUID included

SQL> ALTER TABLESPACE SYSTEM ADD DATAFILE SIZE 5M;

Tablespace altered.

SQL> SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE/datafile/o1_mf_system_j37cxzjg_.dbf

    14 SYSTEM       /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/o1_mf_system_j37d4f9c_.dbf <<<<<<<<<

    10 SYSAUX       /u01/app/oracle/oradata/single/SINGLE_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/single/SINGLE_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/single/SINGLE_PDB/users01.dbf
    13 P42          /u01/app/oracle/oradata/SINGLE/BBED3CBAD8A22675E0530B01A8C0E8D8/datafile/o1_mf_p42_j37bj2js_.dbf

6 rows selected.

As result of all of this, our Filesystem will end up looking like this and having datafiles for the same PDB on 3 different folders

[oracle@rac1-node1 oradata]$ tree
.
├── single
│   ├── control01.ctl
│   ├── control02.ctl
│   ├── pdbseed
│   │   ├── sysaux01.dbf
│   │   ├── system01.dbf
│   │   ├── temp012021-02-22_12-44-52-711-PM.dbf
│   │   └── undotbs01.dbf
│   ├── redo01.log
│   ├── redo02.log
│   ├── redo03.log
│   ├── SINGLE_PDB
│   │   ├── sysaux01.dbf    <<< SINGLE_PDB Datafile
│   │   ├── temp01.dbf      <<< SINGLE_PDB Datafile
│   │   ├── undotbs01.dbf   <<< SINGLE_PDB Datafile
│   │   └── users01.dbf     <<< SINGLE_PDB Datafile
│   ├── sysaux01.dbf
│   ├── system01.dbf
│   ├── temp01.dbf
│   ├── undotbs01.dbf
│   └── users01.dbf
└── SINGLE
    ├── BBED3CBAD8A22675E0530B01A8C0E8D8
    │   └── datafile
    │       ├── o1_mf_p42_j37bj2js_.dbf     <<< SINGLE_PDB Datafile
    │       └── o1_mf_system_j37d4f9c_.dbf  <<< SINGLE_PDB Datafile
    └── datafile
        └── o1_mf_system_j37cxzjg_.dbf      <<< SINGLE_PDB Datafile

Like Daniel mentioned, "What about 19c?" so I decided to try with more satisfactory results.

Let’s do the same, create a new Database and follow same steps.

[oracle@rac1-node1 ~]$ dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname single19 -sid single19 -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword Welcome1 \
-systemPassword Welcome1 \
-createAsContainerDatabase true \
-numberOfPdbs 1 \
-pdbName SINGLE19_PDB \
-databaseType MULTIPURPOSE \
-memoryMgmtType auto_sga \
-totalMemory 1536 \
-storageType FS \
-datafileDestination "/u01/app/oracle/oradata/" \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs
Enter PDBADMIN User Password: 
[....]

[oracle@rac1-node1 ~]$ sqlplus / as sysdba
[....]

SQL> show parameter db_create_file_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest          string

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/';

System altered.

SQL> show parameter db_create_file_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest          string  /u01/app/oracle/oradata/

SQL> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 SINGLE19_PDB           READ WRITE NO

SQL> alter session set container=SINGLE19_PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
SINGLE19_PDB

SQL> show parameter db_create_file_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest          string  /u01/app/oracle/oradata/

We can see the same picture we had in 12.2 at the start of the DB creation:

SQL> SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/system01.dbf
    10 SYSAUX       /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/users01.dbf

However, if we move the datafiles or create a new one, the system will be actually consistent and will end up in same location (as expected)

SQL> alter database move datafile 9;

Database altered.

SQL> SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE19/BBEE2C084F4A5608E0530B01A8C079C5/datafile/o1_mf_system_j37gbrrc_.dbf <<<<<<<<<

    10 SYSAUX       /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/users01.dbf

SQL> ALTER TABLESPACE SYSTEM ADD DATAFILE SIZE 5M;

Tablespace altered.

SQL> SELECT FILE_ID, tablespace_name,file_name FROM  dba_data_files;

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ----------------------------------------------------------------------------------------------------
     9 SYSTEM       /u01/app/oracle/oradata/SINGLE19/BBEE2C084F4A5608E0530B01A8C079C5/datafile/o1_mf_system_j37gbrrc_.dbf

    13 SYSTEM       /u01/app/oracle/oradata/SINGLE19/BBEE2C084F4A5608E0530B01A8C079C5/datafile/o1_mf_system_j37gc3mb_.dbf <<<<<<<<<

    10 SYSAUX       /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/sysaux01.dbf
    11 UNDOTBS1     /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/undotbs01.dbf
    12 USERS        /u01/app/oracle/oradata/SINGLE19/SINGLE19_PDB/users01.dbf

To be clear, this is not a an actual issue since what is important is the controlfile information which will have the actual location of any datafile in the Database.

Also, for some reason, when you create a new PDB directly from the CDB, the datafiles location will be actually "correct":

SQL> CREATE PLUGGABLE DATABASE SINGLE_PDB3 ADMIN USER pdb_adm IDENTIFIED BY Welcome1 CREATE_FILE_DEST='/u01/app/oracle/oradata'; 
[....]

   FILE_ID TABLESPACE_NAME  FILE_NAME
---------- -------------------- ---------------------------------------------------------------------------------------------------------
    19 SYSTEM       /u01/app/oracle/oradata/SINGLE/BBF09222D38C2960E0530B01A8C0819C/datafile/o1_mf_system_j37r2vp6_.dbf
    20 SYSAUX       /u01/app/oracle/oradata/SINGLE/BBF09222D38C2960E0530B01A8C0819C/datafile/o1_mf_sysaux_j37r2vpq_.dbf
    21 UNDOTBS1     /u01/app/oracle/oradata/SINGLE/BBF09222D38C2960E0530B01A8C0819C/datafile/o1_mf_undotbs1_j37r2vpr_.dbf

This was just a test using 12.2 without any patch, so is possible this was resolved in your system even if you are still using 12.2, but be aware using old versions like 12c, could have these unwelcome inconsistencies.