Renaming database datafiles using alter database rename.
On Oracle Applications 11i and Oracle database 9.2.0.7:
Renaming database datafiles using alter database rename after a graceful shutdown.
Today: 09-Jan-2009
Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
Note:
If this is Oracle Apps environment then remember:
Let’s say we are relocating some files on instance PROD:
Do the file relocation or rename first on PROD.
Save current tns and init to $HOME
Run autoconfig on all nodes as apps owner then as db owner on db node.
Copy original tns and init from $HOME to where they are supposed to go.
Bounce (NORMAL) PROD db and make sure the datafiles look good. Run a few queries. Use OEM to see relocated files.
Do preclone on PROD again if necessary.
Description of why we want to do this:
Renaming or relocating Oracle database datafiles:
I have two datafiles that have the same name.
To find datafiles with the same name use the following SQL.
This script finds all database duplicate datafiles.
Meaning datafiles with the same name:
select a.tablespace_name ,a.file_name,a.status
from dba_data_files a , dba_data_files b
where substr(to_char(a.file_name),23,LENGTH(to_char(a.file_name))) =
substr(to_char(b.file_name),23,LENGTH(to_char(b.file_name)))
and a.FILE_ID<>b.FILE_ID;
Here I want to rename:
‘/u05/oradata/proddata/hrd03.dbf’ to ‘/u05/oradata/proddata/hrd06.dbf’
And’
‘/u05/oradata/proddata/applsysd15.dbf’ to ‘/u05/oradata/proddata/applsysd19.dbf’
Using the following SQL make sure hrd06.dbf and applsysd19.dbf do not already exist,
This is useful before creating a new datafile making sure it does not already exist
avoiding therefore creating a duplicated datafile with different path:
select tablespace_name ,file_name,status
from dba_data_files
where substr(to_char(a.file_name),23,LENGTH(to_char(a.file_name))) = &Name;
I could put the tablespace offline and do a rename datafile statement, but since here I am dealing with apps and applsys objects and possibility of causing crash of various apps processes, I decided to shutdown system then do my rename.
1) backup controlfile to trace:
alter database backup controlfile to trace;
This would be useful incase things go wrong we can restore using the original controlfile which we just backed up.
2) Graceful shutdown PROD (immediate, startup restrict open, shutdown normal again)
3) From the OS move the DATA datafiles:
mv /u05/oradata/proddata/hrd03.dbf /u05/oradata/proddata/hrd06.dbf
mv /u05/oradata/proddata/applsysd15.dbf /u05/oradata/proddata/applsysd19.dbf
4) startup mount
5) From Oracle rename datafiles:
alter database rename file
‘/u05/oradata/proddata/hrd03.dbf’ to ‘/u05/oradata/proddata/hrd06.dbf’;
alter database rename file
‘/u05/oradata/proddata/applsysd15.dbf’ to ‘/u05/oradata/proddata/applsysd19.dbf’;
6) Bounce database:
shutdown immediate; (immediate, startup restrict open, shutdown normal again)
startup and verify datafiles and redo logs location.
7)Run autoconfig on all nodes as apps owner then as db owner on db node.
As apps owner on each node:
cd $COMMON_TOP/admin/scripts/<context_name>
./adautocfg.sh
As database owner on database node:
cd $ORACLE_HOME/appsutil/scripts/<context_name>
Copy original tns and init from $HOME to where they are supposed to go.
This is necessary because adautocfg.sh overwrites any custom entries you might have in $TNS_ADMIN and $ORACLE_HOME/dbs
Bounce (NORMAL) PROD db and make sure the datafiles look good. Run a few queries. Use OEM to see relocated files.