Oracle File_Name_Covert

Title:

Move data file from one location to another location, and change the parameter DB_FILE_NAME_CONVERT

Problem Decription:

We are adding datafiles for SYSAUX tablespace in production database in following mountpoint: "/hilo_oradata01/HILO/sysaux02.dbf"

           

But in standby database it will be added in following mountpoint:"/hilo_oradata02/HILO/sysaux02.dbf"

Cause:

In parameter file DB_FILE_NAME_CONVERT="/hilo_oradata01/HILO,"/hilo_oradata02/HILO'(Remove or Comment this parameter) for that reason it through the error like that.

Action:

We need to comment that parameter in parameter file after that move the datafiles from "/hilo_oradata02/HILO" to "/hilo_oradata01/HILO".

Solution:

1.Create pfile

 Sql> create pfile from spfile;

2.Down the standby database.

Sql>Alter database Recover managed standby databae cancel;

Sql>Shutdown database;

3.Open pfile and comment or remove  the parameter

           

            # DB_FILE_NAME_CONVERT==/hilo_oradata01/HILO,/hilo_oradata02/HILO

4.Copy all datafile from "/hilo_oradata02/HILO" to "/hilo_oradata01/HILO" manually.

cp /hilo_oradata01/HILO/users01.dbf    /hilo_oradata02/HILO/users01.dbf

cp /hilo_oradata01/HILO/undotbs01.dbf  /hilo_oradata02/HILO/undotbs01.dbf

cp /hilo_oradata01/HILO/sysaux01.dbf   /hilo_oradata02/HILO/sysaux01.dbf

cp /hilo_oradata01/HILO/system01.dbf   /hilo_oradata02/HILO/system01.dbf

cp /hilo_oradata01/HILO/example01.dbf  /hilo_oradata02/HILO/example01.dbf

cp /hilo_oradata01/HILO/hrms_dat01.dbf /hilo_oradata02/HILO/hrms_dat01.dbf

cp /hilo_oradata01/HILO/hrms_dat02.dbf /hilo_oradata02/HILO/hrms_dat02.dbf

cp /hilo_oradata01/HILO/tds_dat01.dbf  /hilo_oradata02/HILO/tds_dat01.dbf

cp /hilo_oradata01/HILO/sysaux02.dbf   /hilo_oradata01/HILO/sysaux02.dbf

5.Startup mount pfile="/hilo_engn01/app/product/11.1.0/db_1/dbs/initHILO.ora".

6. Rename the datafiles.

Sql>alter database rename file '/hilo_oradata01/HILO/users01.dbf' 
to '/hilo_oradata02/HILO/users01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/undotbs01.dbf' to  '/hilo_oradata02/HILO/undotbs01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/sysaux01.dbf' to  '/hilo_oradata02/HILO/sysaux01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/system01.dbf' to '/hilo_oradata02/HILO/system01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/example01.dbf' to '/hilo_oradata02/HILO/example01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/hrms_dat01.dbf' to '/hilo_oradata02/HILO/hrms_dat01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/hrms_dat02.dbf' to '/hilo_oradata02/HILO/hrms_dat02.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/tds_dat01.dbf' to '/hilo_oradata02/HILO/tds_dat01.dbf';

Sql>alter database rename file '/hilo_oradata01/HILO/sysaux02.dbf' to '/hilo_oradata01/HILO/sysaux02.dbf';

7.Shutdown immediate;

8.Startup mount pfile="/hilo_engn01/app/product/11.1.0/db_1/dbs/initHILO.ora".

9.create spfle from pfile;

10.shutdown immediate;

11.startup nomount;

12. alter database mount standby database;

13.alter database recover managed standby database disconnect from session;

14. Finally remove the datafiles from "/hilo_oradata02/HILO" .

15 Task Completed Successfully