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.
Top comments (0)