Diferrent Datafile Renaming Methodologies

As far as renaming a datafile is concerned we have 3 diferrent ways through which it can be achieved

method 1 - Offline a particular datafile and renaming it
method 2 - offline the tablespace and renaming the datafile alone
method 3 - shutdown DB,mount DB and rename the datafile



Method 1
=========

Before you can use this method the database must be in archive log mode.
In the below scenario I am renaming a datafile names test11.dbf to test12.dbf



SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline;
alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
SQL> alter database open;

Database altered.

SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' offline;

Database altered.


$ cp test11.dbf test12.dbf
$

SQL> alter database rename file '/u06/oracle/11GR2/oradata/D11GR2/test11.dbf' to '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf';

Database altered.

SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online;
alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf'


SQL> recover datafile 8;
Media recovery complete.
SQL>
SQL> alter database datafile '/u06/oracle/11GR2/oradata/D11GR2/test12.dbf' online;

Database altered.

SQL>





Method 2
=========



Tablespace Offline Datafile Rename method

I take the particular tablespace offline and then rename the datafile.

a)

alter tablespace APPS_TS_TX_IDX offline;

b)

mv /u01/oracle/PROD/db/apps_st/data5/a_media19.dbf /u01/oracle/PROD/db/apps_st/index3/a_txn_ind43.dbf


c)

alter tablespace APPS_TS_TX_IDX rename datafile
/u01/oracle/PROD/db/apps_st/data5/a_media19.dbf
to
/u01/oracle/PROD/db/apps_st/index3/a_txn_ind43.dbf;

d)

alter tablespace APPS_TS_TX_IDX online;




Method 3
=========

In this scenario we take the database down ,start the database in mount option and then rename the datafile.Finally open the database.

In the below example I am renaming datafile test2.dbf to test3.dbf.



SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u06/oracle/11GR2/oradata/D11GR2/system01.dbf
/u06/oracle/11GR2/oradata/D11GR2/sysaux01.dbf
/u06/oracle/11GR2/oradata/D11GR2/undotbs01.dbf
/u06/oracle/11GR2/oradata/D11GR2/users01.dbf
/u06/oracle/11GR2/oradata/D11GR2/example01.dbf
/u06/oracle/11GR2/oradata/D11GR2/test1.dbf
/u06/oracle/11GR2/oradata/D11GR2/test2.dbf
/u06/oracle/11GR2/oradata/D11GR2/test12.dbf

8 rows selected.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> exit


$ mv /u06/oracle/11GR2/oradata/D11GR2/test2.dbf /u06/oracle/11GR2/oradata/D11GR2/test3.dbf



$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 4 17:18:22 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2723282944 bytes
Fixed Size 2151200 bytes
Variable Size 1509952736 bytes
Database Buffers 1191182336 bytes
Redo Buffers 19996672 bytes
Database mounted.
SQL>
SQL>
SQL> alter database rename file '/u06/oracle/11GR2/oradata/D11GR2/test2.dbf' to '/u06/oracle/11GR2/oradata/D11GR2/test3.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u06/oracle/11GR2/oradata/D11GR2/system01.dbf
/u06/oracle/11GR2/oradata/D11GR2/sysaux01.dbf
/u06/oracle/11GR2/oradata/D11GR2/undotbs01.dbf
/u06/oracle/11GR2/oradata/D11GR2/users01.dbf
/u06/oracle/11GR2/oradata/D11GR2/example01.dbf
/u06/oracle/11GR2/oradata/D11GR2/test1.dbf
/u06/oracle/11GR2/oradata/D11GR2/test3.dbf
/u06/oracle/11GR2/oradata/D11GR2/test12.dbf

8 rows selected.

SQL>