How to move or rename datafiles in Oracle 11gR2

There are multiple ways to move or rename the data files in oracle 11g, oracle 12c has come with a wonderful feature where we can move the files being online.

However prior to 12c we need to do this practice after making the database offline, there are online ways are also available but in those also there is some time delay in processing.

There are the steps by which we can move/rename our datafiles:

  1. Shutdown the database.
  2. Rename the physical file on the OS.
  3. Start the database in mount mode.
  4. Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
  5. Open the database.

Past Detail – I made this TEST database using duplicate command from PROD, however i missed to change the Data Files name, so i thought of doing it instead of creating a clone again.

Find out the instance name:

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
TEST

SQL>

Find out the Database Name:

SQL> show parameter db_name

NAME TYPE VALUE
———————————— ——————————-
db_name string TEST
SQL>
SQL>

Find out the location of all Data files:

SQL> select file_name from dba_data_files;

FILE_NAME
———————————————————————–
/u02/oradata/TEST/PROD_system01.dbf
/u02/oradata/TEST/PROD_sysaux01.dbf
/u02/oradata/TEST/PROD_undotbs01.dbf
/u02/oradata/TEST/PROD_banaq01.dbf
/u02/oradata/TEST/PROD_baniam01.dbf
/u02/oradata/TEST/PROD_banlob01.dbf
/u02/oradata/TEST/PROD_development01.dbf
/u02/oradata/TEST/PROD_tools01.dbf
/u02/oradata/TEST/PROD_users01.dbf
/u02/oradata/TEST/workflow_PROD_01.dbf

10 rows selected.

SQL>

Shutdown the database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Move all files one by one using OS Command:

SQL> !mv /u02/oradata/TEST/workflow_PROD_01.dbf /u02/oradata/TEST/workflow_TEST_01.dbf

SQL> !mv /u02/oradata/TEST/PROD_users01.dbf /u02/oradata/TEST/TEST_users01.dbf

SQL> !mv /u02/oradata/TEST/PROD_tools01.dbf /u02/oradata/TEST/TEST_tools01.dbf

SQL> !mv /u02/oradata/TEST/PROD_development01.dbf /u02/oradata/TEST/TEST_development01.dbf

SQL> !mv /u02/oradata/TEST/PROD_banlob01.dbf /u02/oradata/TEST/TEST_banlob01.dbf

SQL> !mv /u02/oradata/TEST/PROD_baniam01.dbf /u02/oradata/TEST/TEST_baniam01.dbf

SQL> !mv /u02/oradata/TEST/PROD_banaq01.dbf /u02/oradata/TEST/TEST_banaq01.dbf

SQL> !mv /u02/oradata/TEST/PROD_undotbs01.dbf /u02/oradata/TEST/TEST_undotbs01.dbf

SQL> !mv /u02/oradata/TEST/PROD_sysaux01.dbf /u02/oradata/TEST/TEST_sysaux01.dbf

SQL> !mv /u02/oradata/TEST/PROD_system01.dbf /u02/oradata/TEST/TEST_system01.dbf

SQL>

Start the database in mount state, because control file does not know new location of datafiles or new name of datafiles, we need to update in the control file also with the alter command:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size 2163968 bytes
Variable Size 2399144704 bytes
Database Buffers 788529152 bytes
Redo Buffers 16998400 bytes
Database mounted.
SQL>

Now Alter the location of all datafiles:

SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_development01.dbf’ to ‘/u02/oradata/TEST/TEST_development01.dbf’;

Database altered.

SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_banlob01.dbf’ to ‘/u02/oradata/TEST/TEST_banlob01.dbf’;

Database altered.

SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_baniam01.dbf’ to ‘/u02/oradata/TEST/TEST_baniam01.dbf’;

Database altered.

SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_banaq01.dbf’ to ‘/u02/oradata/TEST/TEST_banaq01.dbf’;

Database altered.

SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_undotbs01.dbf’ to ‘/u02/oradata/TEST/TEST_undotbs01.dbf’;

Database altered.

SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_sysaux01.dbf’ to ‘/u02/oradata/TEST/TEST_sysaux01.dbf’;

Database altered.

SQL>
SQL> ALTER DATABASE RENAME FILE ‘/u02/oradata/TEST/PROD_system01.dbf’ to ‘/u02/oradata/TEST/TEST_system01.dbf’;

Database altered.

SQL>

After changing the name or location, we need to open the database:

SQL> alter database open;

Database altered.

SQL>

Check new name or new location again:

SQL> select file_name from dba_data_files;

FILE_NAME
———————————————————————–
/u02/oradata/TEST/TEST_system01.dbf
/u02/oradata/TEST/TEST_sysaux01.dbf
/u02/oradata/TEST/TEST_undotbs01.dbf
/u02/oradata/TEST/TEST_banaq01.dbf
/u02/oradata/TEST/TEST_baniam01.dbf
/u02/oradata/TEST/TEST_banlob01.dbf
/u02/oradata/TEST/TEST_development01.dbf
/u02/oradata/TEST/TEST_tools01.dbf
/u02/oradata/TEST/TEST_users01.dbf
/u02/oradata/TEST/workflow_TEST_01.dbf

10 rows selected.

SQL>

So we are done with moving the files in Offline Mode. Hopefully in next blog i will discuss how can we do that Online in Oracle 11g.

Hope it helps..

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s