Moving controlfiles in Oracle DB 10.2.0.4

[former_member248757]

Dear Expert, <br><br>

Please help me, how to move controlfile from C:\oracle\CE2\origlogA\cntrl\filename.DBF to

D:\oracle\CE2\origlogA\cntrl\filename.DBF. We are using Windows Server 2003 64 bit, and Oracle 10.2.0.4. Please guide me <br><br>

Regards

Febri Ishani

[Former Member]

Hi,

You can change the location of the control file by following the procedure below.

Logon to the server as <SID>ADM
Take a copy of init<SID>.ora and the current control file.
Connect to the SQLPLUS and then execute the following,
backup control file to trace;
create pfile from spfile;(this replaces existing init<SID>.ora with new file)
shutdown immediate;
Now edit the pfile (init<SID>.ora) and modify the controlfile location.
Copy the controlfile physically to the new location. After the changes proceed as follows,
Connect to the SQLPLUS and then execute the following,
startup pfile='location of init<SID>.ora';
create spfile from pfile;
shutdown immediate;
startup;

NB: Make sure that u have taken a copy of the existing pfile before doing any creating and editing a new one.

Good Luck!!!

Regards,

Varadharajan M

[Former Member]

Hi,

1. Check whether you are using the spfile or pfile.

SQL> show parameter spfile

if this query return the type as "string" for the name spfile, then spfile is used. If the value is empty then pfile is used.

2. If you are using the spfile, create the pfile from spfile.

SQL> create pfile from spfile.

3. Shut down the database.

4. Move the control file to the new location

5. Change the location of the control file in init<sid>.ora location.

6. start the database.

7. Create the spfile from pfile.

SQL> create spfile from pfile.

Rgds,

JP.

[former_member709110]

Hi ,

One way is ....

1. Shutdown the databse

2. Copy the control file to the new location

3. Adjust the changes in your ini<SID>.ora ( spfile as applicable) the parameter CONTROL_FILES

4. Bring back the database

Regards,

Neel

[former_member248757]

Hi Neel,

Thx for your reply. I have try your suggestion, i copy the file to another drive and change control_files configuratio in init<SID>.ora, then i delete the originall file. But when i startup the database an error happen:

SQL> startup;

ORACLE instance started.

Total System Global Area 960495616 bytes

Fixed Size 2070352 bytes

Variable Size 490735792 bytes

Database Buffers 465567744 bytes

Redo Buffers 2121728 bytes

ORA-00205: error in identifying control file, check alert log for more info

At the last sentences from alert<SID>.log say:

Thu Sep 23 07:38:46 2010

ALTER DATABASE MOUNT

Thu Sep 23 07:38:46 2010

ORA-00202: control file: 'C:\ORACLE\<SID>\ORIGLOGA\CNTRL\CNTRL<SID>.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Any other idea please ?

Regards

Febri

[Former Member]

Hi,

Please follow the following

1. Copy the control file back to old location & revert back the entries in init<SID>.ora file

2. Start your db & check its started successfully or not.

3. If db started execute follwing sql

alter database backup control file to trace.

4. Above sql will create one trace file in saptrace folder

5. Edit the file & remove all entries after CHARACTER SET UTF8; and save the file as .sql file

6. Now change init<SID>.ora file to new location

7. Now execute the SQL file. It will show message something like "Control file created"

8. Now execute command ALTER DATABASE OPEN RESETLOGS

Regards

Mrinal

[former_member709110]

Hi Febri,

I am sorry that the solution didn't work for you . Not sure why ?

Hope you are not using spfile (bcoz the log shows it is looking for the old path) , if you have a spfile you will have to re-create it from the edited pfile.

Regards,

Neel