Wednesday, September 19, 2007

Adding Datafile with same name but different directory

Yesterday, one of my colleagues added a datafile with the same filename but different directory by mistake through OEM. He quickly realized it but suprised to find that OEM did not complain and created the datafile successfully.

We checked the alertlog and there were no errors either. We tried the same thing in a different test database and no issues there too. So this proves that Oracle treats the filename in its entirety (including the directory path) and hence the new datafile is still unique.

However this is not a great practice and hence we are going to use our next scheduled downtime to rename this newly added datafile.

Renaming a Datafile when the database is shutdown

1) Login to all of the RAC database nodes and issue 'shutdown immediate'
2) cp existing_datafile_name new_datafile_name
3) Login to the master RAC node and issue 'startup mount'
4) alter database rename file 'old_directory/old_datafilename'
5) shutdown immediate
6) Login to all the of the RAC database nodes and issue 'startup'
7) Verify the new name in v$datafile and dba_data_files.
8) Remove the datafile with the wrong name from the operating system using the OS command.

No comments:

Related Posts Plugin for WordPress, Blogger...