How To Rename Datafiles for Oracle Standard Edition (SE)
Starting with 12c Oracle introduced online move of the datafiles. This feature is great, as you can move or rename online the datafiles, without taking tablespaces, datafiles or the database offline. The only thing is, this feature only applies for Enterprise Edition, and not Standard Edition.
This blog post will focus on renaming the datafiles of a PDB, for Oracle Standard Edition. I ran all the exercise in Oracle 19c SE2. When I tried renaming the files online, unaware that this feature is only available in Enterprise Edition, I received the following error:
ORA-00439: feature not enabled: online move datafile
How can you rename the files in Oracle SE? Well it’s not that complicated… You need to use the old way, pre 12c.
In my example below I had to rename all the datafiles for a PDB. The files had to be renamed, as a result of renaming the PDB itself. I renamed PDB1 pluggable database to PDB1TEST pluggable database. As a result I wanted the new name of the PDB to be reflected in the datafiles as well, for consistency.
- First let’s get a list of the datafile names. This is the starting point
alter session set container=PDB1TEST;
select file_name from dba_data_files;
FILE_NAME
---------------------------------------
/u02/oradata/cdb1/PDB1/SYSTEM01.DBF
/u02/oradata/cdb1/PDB1/SYSAUX01.DBF
/u02/oradata/cdb1/PDB1/UNDOTBS01.DBF
/u02/oradata/cdb1/PDB1/USERS01.DBF
/u02/oradata/cdb1/PDB1/DATA_01.DBF
/u02/oradata/cdb1/PDB1/INDX_01.DBF
2. Close the PDB. This will guarantee the datafiles are not in use.
alter pluggable database PDB1TEST close immediate;
3. Move the datafiles at the OS level to the new location, with OS commands:
mkdir /u02/oradata/cdb1/PDB1TEST
mv /u02/oradata/cdb1/PDB1/SYSTEM01.DBF /u02/oradata/cdb1/PDB1TEST/.
mv /u02/oradata/cdb1/PDB1/SYSAUX01.DBF /u02/oradata/cdb1/PDB1TEST/.
mv /u02/oradata/cdb1/PDB1/UNDOTBS01.DBF /u02/oradata/cdb1/PDB1TEST/.
mv /u02/oradata/cdb1/PDB1/USERS01.DBF /u02/oradata/cdb1/PDB1TEST/.
mv /u02/oradata/cdb1/PDB1/DATA_01.DBF /u02/oradata/cdb1/PDB1TEST/.
mv /u02/oradata/cdb1/PDB1/INDX_01.DBF /u02/oradata/cdb1/PDB1TEST/.
4. Rename the datafiles in the database:
sqlplus / as sysdba
alter session set container=PDB1TEST;
alter database rename file '/u02/oradata/cdb1/PDB1/SYSAUX01.DBF' to '/u02/oradata/cdb1/PDB1TEST/SYSAUX01.DBF';
alter database rename file '/u02/oradata/cdb1/PDB1/SYSTEM01.DBF' to '/u02/oradata/cdb1/PDB1TEST/SYSTEM01.DBF';
alter database rename file '/u02/oradata/cdb1/PDB1/UNDOTBS01.DBF' to '/u02/oradata/cdb1/PDB1TEST/UNDOTBS01.DBF';
alter database rename file '/u02/oradata/cdb1/PDB1/USERS01.DBF' to '/u02/oradata/cdb1/PDB1TEST/USERS01.DBF';
alter database rename file '/u02/oradata/cdb1/PDB1/DATA_01.DBF' to '/u02/oradata/cdb1/PDB1TEST/PWRPLANT01.DBF';
alter database rename file '/u02/oradata/cdb1/PDB1/INDX_01.DBF' to '/u02/oradata/cdb1/PDB1TEST/PWRPLANT_IDX01.DBF';
select file_name from dba_data_files;
FILE_NAME
---------------------------------------------
/u02/oradata/cdb1/PDB1TEST/SYSTEM01.DBF
/u02/oradata/cdb1/PDB1TEST/SYSAUX01.DBF
/u02/oradata/cdb1/PDB1TEST/UNDOTBS01.DBF
/u02/oradata/cdb1/PDB1TEST/USERS01.DBF
/u02/oradata/cdb1/PDB1TEST/PWRPLANT01.DBF
/u02/oradata/cdb1/PDB1TEST/PWRPLANT_IDX01.DBF
5. Open the PDB.
SQL> alter pluggable database PDB1TEST open;
And that’s it. All the datafiles of the PDB have been renamed. If you only want to rename one datafile that is not a system datafile, then you don’t need to take an outage for the whole database, you can take just the one tablespace offline, and follow all the other steps above.
Next time, I’ll show you all the steps to rename a PDB, and the datafiles with it! Stay tuned!
–Diana