Introduction:
In Oracle databases, datafiles are crucial components that store the actual data. Datafiles are physical files on disk that store the data for all database objects, such as tables and indexes.
Each tablespace in an Oracle database consists of one or more datafiles. If datafile mount 100% filled and no possibility to increase mount space, will database up and running?. Definitely No.
In Such Scenario, How to move CDB and PDB Datafiles from one mount to other mount online when datafile mount is 100% filled?
Issue Description:
In One of PROD EBS database, suddenly datafile mounts(/u01) reached 100% and PDB went down(mount). And CDB Only Accessible. /u01 mount can’t be increased for some reason. You have space in other mounts /u02 and /u03 mounts. And space can be added to these mounts if needed.
At this point, we have only the option of moving few datafiles from /u01 to other mounts to release space. we are not having option to move datafiles of PDB, as PDB went down.
To Sort out this situation, we have moved “users” tablespace of CDB from /u01 to /u02. After doing this, some space released in /u01 mount. And then we could able to open PDB.
Certainly! Moving Oracle datafiles from one mount point to another can be a crucial task, especially when you’re dealing with storage optimization or system maintenance. Let’s break it down:
Understand the Context:
Before you proceed, ensure you have a clear understanding of which datafiles you want to move and why. Are you relocating them for performance reasons, disk space management, or some other purpose?
Backup Your Database:
Always start by taking a full backup of your database. This is essential to ensure data integrity and recoverability in case anything goes wrong during the process.
How to move datafiles online from one mount to other mount:
Connect to CDB database:
SQL> select name from v$database;
NAME
———
CDBORCL
—- Move users tablespace related datafiles from /u01 mount to /u02.
SQL> alter database move datafile ‘/u01/oracle/CDBORCL/users01.dbf’ to ‘/u02/oracle/CDBORCL/users01.dbf’;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
SQL>
Connect to PDB database:
SQL> alter session set container=ORCL;
After some space released from /u01 mount, we can open PDB, and we can start move few datafiles of PDB from /u01 to /u02.
SQL> alter database move datafile ‘/u01/oracle/CDBORCL/ORCL/a_txn_ind01.dbf’ to ‘/u02/oracle/CDBORCL/ORCL/a_txn_ind01.dbf’;
Database altered.
SQL> alter database move datafile ‘/u01/oracle/CDBORCL/ORCL/a_txn_ind02.dbf’ to ‘/u02/oracle/CDBORCL/ORCL/a_txn_ind02.dbf’;
Database altered.
SQL> alter database move datafile ‘/u01/oracle/CDBORCL/ORCL/a_txn_ind03.dbf’ to ‘/u02/oracle/CDBORCL/ORCL/a_txn_ind03.dbf’;
Database altered.
How to Revert users tablespace related datafiles to Original?
Connect to CDB database:
SQL> select name from v$database;
NAME
———
CDBORCL
SQL> alter database move datafile ‘/u02/oracle/CDBORCL/users01.dbf’ to ‘/u01/oracle/CDBORCL/users01.dbf’;
Verify the Changes:
Confirm that the datafiles are now in the new location:
SQL> SELECT file_name FROM dba_data_files;
Conclusion:
This is the way we can move datafiles online even though specific datafile mount filled 100%. Remember that this process should be handled carefully, especially in a production environment. Always ORCL any changes in a non-production environment first. Additionally, consider any dependencies (such as tablespaces, indexes, or other objects) that might be affected by the datafile relocation
