How to move online tablespace in Oracle 19c

A few days ago, when the team performed some application deployment tasks, we noticed that the different schemas, created the datafiles, which belong to a TBS, were created at the destination of the CDB or PDB’s.

We start with the first task of verifying that the db_create_file_dest parameter was configured.

This parameter that differs in container paths as from PDB, is the one that guarantees that, through the OMF utility, the datafiles are created in the correct paths.

A demo was made, to understand the problem with developers, devops and dbas.
So that the importance of setting these values and the impact of not having their configuration is understood.

Likewise, we proceeded to move the datafiles, from the wrong paths, to the correct paths where the datafiles should be created.

Hands on

Check and configure the db_create_file_dest .

Check the default configuration

show parameter db_create_file_dest
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL>

It’s empty.

Set the configuration for Container Database (CDB)

ALTER SYSTEM SET db_create_file_dest='/opt/oracle/oradata/ORCLCDB';
SQL> ALTER SYSTEM SET db_create_file_dest='/opt/oracle/oradata/ORCLCDB';

System altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata/ORCLCDB

Set the configuration for Plugabble Databse (PDB)

Remeber, if you not have created an oracle dba user on your PDB and you login as sysdba, you must be change session from container to PDB with the following command:

alter session set container = <PDB_NAME>

and set the correct value:

ALTER SYSTEM SET db_create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB1';
SQL> ALTER SYSTEM SET db_create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB1';

System altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata/ORCLCDB/OR
                                                 CLPDB1
SQL>

Tablespaces

Before making our change to the db_create_file_dest parameter, we will run a test, which is to create tablespaces with the empty parameter in the PDB.

We will create the Tablespaces and from there, we can see what happened and how we can correct it if necessary.

CREATE BIGFILE TABLESPACE ABCDEF_DATOS
  DATAFILE 'ABCDEF_DATOS_F1.dbf'
  SIZE 100m AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE ABCDEF_INDICES
  DATAFILE 'ABCDEF_INDICES_F1.dbf'
  SIZE 100m AUTOEXTEND ON;

The output of this query, show the tespace name and path, of DATA or INDEX.

1 /opt/oracle/product/19c/dbhome_1/ DATA
2 /opt/oracle/product/19c/dbhome_1/ INDEX
PAths of TBS DATA and INDEX

Query to obtain the datafile path, tablespace name and file_id

 select 
 FILE_ID, 
 FILE_NAME, 
 TABLESPACE_NAME , 
 BYTES/1024/1024 from CDB_DATA_FILES;

Executed the command with the name obtain from query.

ALTER DATABASE MOVE DATAFILE '/opt/oracle/product/19c/dbhome_1/dbs/ARPA_DATOS_F1.dbf' TO '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ARPA_DATOS_F1.dbf';

And the result was saved in green, another path for the TBS of DATA.

Now, execute a second alter.

We use the clausule FILE_ID , the number for this datafile is 16.

ALTER DATABASE MOVE DATAFILE 16 TO '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/ARPA_INDICES_F1.dbf' KEEP;

Our work finished with the Datafiles, memebers of the Tablespaces, in the correct place.

The most important point here is, all task was made in ONLINE databse mode.