Saturday, November 06, 2010

Duplicate data files

In order to find duplicate data files in your environemnt use the following sql:

select tablespace_name,file_name from dba_data_files a where 1< (select count(*) from
dba_data_files b where substr(a.file_name,24,60)=substr (b.file_name,24,60)) ;

To fix the duplicate datafiles follow the below procedure:

1. If the database is up, shut it down.
> SHUTDOWN IMMEDIATE

2. Copy the datafile to the new name/location at operating system level.
> cp

3. Mount the database.
> STARTUP MOUNT
This command will read the control file but will not mount the datafiles.

4. Rename the file inside Oracle.
> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
Do this for all the datafiles that were renamed or moved at the operating system level.

5. Open the database.
> ALTER DATABASE OPEN;

6. Query v$dbfile to confirm that the changes made were correct.
> SELECT * FROM V$DBFILE;

7. Remove the datafile(s) from the old location at the operating system level.






Regards,
Sukhwinder Singh.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.