Tag Archives: rename datafile

Renaming and Relocating Online Data Files in Oracle 12c

Renaming and Relocating Online Data Files in Oracle 12c

In Oracle 12c, New feature is given from moving the datafiles from one location to another without downtime of the environment.
It increase the great flexibility of the Oracle Database moving datafiles to one location to other without downtime like move datafiles which is less needed to low cost storage or Move datafile to ASM Storage online without downtime.

Renaming an Online Data File

--renames the data file user1.dbf to user01.dbf while keeping the data file in the same location
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user1.dbf' TO '/u02/oradata/ORCL/user01.dbf';

Relocating or Move the online Data file

-- Move the datafile from one location to anther online.
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf';

Copying an Online Data File
KEEP keyword is used to keep the old file as its and copy file to new location

-- Copy the datafile from one location to anther without removing the old location file.
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf' keep;

Relocating or Move an Online Data File and Overwriting an Existing File
REUSE Keyword is used to overwrite the existing file with same name present in Destination folder

ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf' REUSE;

Move or Relocate an online data file to ASM

ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '+dg01/data/orcl/datafile/user01.dbf';

Advertisements

Renaming and Relocating Offline Data Files for Oracle

Rename the data files for Oracle

Datafile movement can be performed without a database restart for those tablespaces, which can be taken offline (All except System, Undo or Temp).

Following are the steps for move or rename the Oracle Datafile:

1: Login to the database as SYSDBA

sqlplus sys as sysdba

2: Make the tablespace offline which file need to rename:

select tablespace_name,file_name from dba_data_files;

Alter tablespace USERS offline;

3: Copy the datafile to the required location at the OS level

$ cp filename1 filename2

4: Rename the datafile using Alter Database command

alter database rename file '' to '' ;

Step 5: Make the tablespace online.

SQL> alter tablespace USERS online;

The above mentioned steps is not worked for system , temp and undo tablespaces as they cannot be taken offline.
Normally we drop and recreate the undo/temp tablespaces rather than moving the datafiles as it doesn’t involve any downtime.

Following are the steps to move system datafiles:

Steps to move SYSTEM datafile from one location to another:

1: Login to the database as SYSDBA

sqlplus sys as sysdba

Step 2: Shutdown the database

Shutdown immediate

Step 3: Copy/rename the datafile at the OS level

$ cp filename1 filename2

Step 4: Start the database at MOUNT state.
At this stage control file is read.

Startup mount

Step 5: Rename the datafile using Alter Database command

alter database rename file '' to '' ;

Step 6: Open the database

Alter database open;

For drop or recreate temp tablespace follows following link:
Manage the Temp Tablespace