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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.