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>

When Opening Database - 'ORA-01113: file 1 needs media recovery '

Hello All,

After restore and recovery is complete ,trying to open database with resetlogs option showed the following error message

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oracle/UPG/apps/apps_st/db/system01.dbf' was restored from an oldbackup

I have verified the time and the scn number until which I ran to restore, everything was correct but still unable to open the database.I tried applying archive logs and then to open failed again.

Research

I have issued the following command to recover 'recover database using backup controlfile' .

After some research and googling I issued the following command to open database

sqlplus > recover database using backup controlfile until cancel;

When prompted issued CANCEL.Now tried to open the database with resetlogs and database opened without any issues.

Session Details

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 370577110 generated at 06/12/2010 16:19:22 needed for thread
1
ORA-00289: suggestion :
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2671_699752349.arc
ORA-00280: change 370577110 for thread 1 is in sequence #2671


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2671_699752349.arc
ORA-00279: change 370578253 generated at 06/12/2010 16:27:55 needed for thread
1
ORA-00289: suggestion :
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2672_699752349.arc
ORA-00280: change 370578253 for thread 1 is in sequence #2672
ORA-00278: log file
'/u01/oracle/UPG/db/apps_st/arch/UPG_1_2671_699752349.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2672_699752349.arc
ORA-00279: change 370610644 generated at 06/12/2010 18:27:47 needed for thread
1
ORA-00289: suggestion :
/u01/oracle/UPG/db/apps_st/arch/UPG_1_2673_699752349.arc
ORA-00280: change 370610644 for thread 1 is in sequence #2673
ORA-00278: log file
'/u01/oracle/UPG/db/apps_st/arch/UPG_1_2672_699752349.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM


- Hope this helps when performing simple recovery to a different server.