How to reallocate and rename a datafile on SAP oracle

The Condition if we want to reallocate and rename a datafile oracle :

a. We have created a datafile on the wrong filesystem or drive.
b. We have created a datafile where a hidden character or a linefeed was introduced by by accident.
c. We add a new filesystem(s), drive(s) to the system and want to redistribute the datafiles.
d. We restore some datafiles to a new location because the original is not available any more ( media error )

Before setting offline any tablespace, be sure that all datafiles of the tablespace are ONLINE.

For example, use the following command:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>
SQL> SELECT vtbs.name, vdf.status, count(*) cnt FROM  v$datafile vdf, v$tablespace vtbs WHERE  vdf.TS#=vtbs.TS# AND vtbs.name = '<tablespace_name>' GROUP BY vtbs.name, vdf.status;

There are several options to perform this condition:

Option 1) 

Connect to the open database:
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

Take the tablespace offline:
SQL> ALTER TABLESPACE <NAME> OFFLINE;

Copy the datafiles to the new location by using OS commands manually: 
Compare the size of the two files, they have to be the same

Update the new datafile location with one of this commands:
SQL> ALTER DATABASE RENAME FILE '<old>' TO '<new>';
SQL> ALTER TABLESPACE <NAME> RENAME DATAFILE '<old>' TO '<new>';

Bring the tablespace online with the command:
SQL> ALTER TABLESPACE <NAME> ONLINE;

Remove at OS level the original file.

You cannot use this method for the SYSTEM tablespace.


Option 2)

Stop the database:
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> shutdown immediate;

Copy the datafiles to the new location by using OS commands manually: 

Compare the size of the two files, they have to be the same

Mount the database:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> startup mount;

Update the new datafile location:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> ALTER DATABASE RENAME FILE '<old>' TO '<new>';

Open the database:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> ALTER DATABASE OPEN;

Remove at OS level the original file.


Option 3)

This option is useful when you can not type the name due to the linefeed or hidden character.

Make a trace file:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Modify datafile pointers in the tracefile generated above to reflect the changes and rename the file so the extension is "sql".

Stop the database:
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> shutdown immediate;

Copy the datafiles to the new location by using OS commands manually: 

Compare the size of the two files, they have to be the same

Run the script created before:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> @filename.sql

Remove at OS level the original file.


Option 4)

Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online: 
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> ALTER DATABASE MOVE DATAFILE ('<old>') TO ('<new>') [REUSE] [KEEP];

The REUSE keyword indicates the new file should be created even if it already exists.
The KEEP keyword indicates the original copy of the datafile should be retained.

This option is also available in the BRSPACE tool (function "dfmove" option "-o yes").

NOTE: BEFORE making any structural changes to a database, such as renaming and relocating the datafiles of one or more tablespaces, always completely backup the database. AFTER making any structural changes to a database, always perform an immediate and complete backup.