How to change controlfile destination

[Former Member]

Hi

We have a test server in which solman7 is installed,we have 3 partition drives C,D,F.During installation origlogA and origlogB control_files has been installed in E drive,due to space constrain we are planning to shift those control file destination to C drive.

What i did was i edited the initSID.ora file and changed the destination of control_files to C drive and copied origlogA and origlogB to new destination and started the DB but still there is no success .

SQL> startup

ORACLE instance started.

Total System Global Area 679477248 bytes

Fixed Size 2056000 bytes

Variable Size 348127424 bytes

Database Buffers 322961408 bytes

Redo Buffers 6332416 bytes

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

Even i tried executing the below query

SQL> alter system set control_files = C:\ORACLE\N6P\ORIGLOGA\CNTRL\CNTLRN6P.DBF

alter system set control_files = C:\ORACLE\N6P\ORIGLOGA\CNTRL\CNTLRN6P.DBF

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

1:Is there any thing i missed out

Regards

Uday

[Former Member · Accepted solution]

Hi Joe

You may have sucessfully changed controlfile location, but Oracle still is expecting online redo logs at the former place.
Move them back, then you should be able to start Oracle

Fine i can move back those changes for Oracle to startup, but i thought editing the changes in initSID.ora will work but no success

SAP note 309526

I have read this note which says

To adjust the size of redo log files, you must create new redo log files and then delete the previous files.

To create new log files, use the following command:

alter database add logfile group 11

('/oracle/<SID>/origloga/log_g11_m1.dbf',

'/oracle/<SID>/mirrloga/log_g11_m2.dbf') size 50M;

This command creates log file group 11 with two members of the size 50MB. You must ensure that this group does not yet exist. Group '11' is mentioned only as an example.

To delete old redo log files, use the following command:

alter database drop logfile group 11;

Again, group '11' is mentioned only as an example, replace it with the group you want to delete.

If the redo log file is being used, you cannot delete the file. Initiate a log switch beforehand:

alter system switch logfile;

You can execute all actions described above online. Restarting the database is not necessary

will this really work in my scanerio?

Regards

Uday

[Former Member · Accepted solution]

Yes, the note explains how to create new online redo logs, and how to drop old ones.

It doesn't matter whether you want to change size (the most frequent reason, I think) or change location.

(There is a caveat: You cannot drop the current log file group; you have to wait in this case.)

regards

[Former Member · Accepted solution]

And by the way: Location of online redo log files is not in initSID.ora anyways.

Only location of controlfiles is there.

Edited by: Joe Bo. on Jul 22, 2009 12:29 PM

[JPReyes · Accepted solution]

Read [here|http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_35.shtml#Moving Online Redo Log Files]

That might give you an idea.

Regards

Juan

[Former Member · Accepted solution]

Good link, Juan!

I suspected there should be something similar.

But on the other hand, I am satisfied with knowing one working method.

regards

[Former Member]

Thanks a lot Juan

Link provided by you has solved my problem

Regards

Uday

[Former Member]

Thanks Juan

I will check it out and get back

Regards

uday

[Former Member]

Hi

Now when i check the controlfile location with below query they are pointing toward new location

SQL> show parameter control_files;

NAME TYPE VALUE

-

---

-

---

-

---

control_files string C:\ORACLE\N6P\ORIGLOGA\CNTRL\C

NTLRN6P.DBF, C:\ORACLE\N6P\ORI

GLOGB\CNTRL\CNTRLN6P.DBF, D:\O

RACLE\N6P\SAPDATA1\CNTRL\CNTRL

N6P.DBF

But MIRRLOGB and ORIGLOGB

SQL> startup

ORACLE instance started.

Total System Global Area 679477248 bytes

Fixed Size 2056000 bytes

Variable Size 348127424 bytes

Database Buffers 322961408 bytes

Redo Buffers 6332416 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: 'E:\ORACLE\N6P\MIRRLOGB\LOG_G12M2.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

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

ORA-00312: online log 2 thread 1: 'E:\ORACLE\N6P\ORIGLOGB\LOG_G12M1.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

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

Regards

Uday

[Former Member]

mirrlog and origlog are mainly meant for online redo log files.

You may have sucessfully changed controlfile location, but Oracle still is expecting online redo logs at the former place.

Move them back, then you should be able to start Oracle.

As for relocating online redo log files, this is usually done by creating new ones and dropping the old ones. Syntax is for example explained in SAP note 309526.

hope this helps

[Former Member]

You cannot alter system set control_files= . You have to shut down the database

I have done it

move the control files, edit the pfile

Moved mirrlogA,mirrlogB,origlogA,origlogB to new location checked the permission (As it is a test server never mind all the files at onelocation)

open the database with the pfile (instead the spfile) using startup pfile='mypath/init.ora'

SQL> startup pfile=D:\oracle\N6P\102\database\initN6P.ora

ORACLE instance started.

Total System Global Area 679477248 bytes

Fixed Size 2056000 bytes

Variable Size 348127424 bytes

Database Buffers 322961408 bytes

Redo Buffers 6332416 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: 'E:\ORACLE\N6P\MIRRLOGB\LOG_G12M2.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

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

ORA-00312: online log 2 thread 1: 'E:\ORACLE\N6P\ORIGLOGB\LOG_G12M1.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

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

I have cross checked the permission every thing fine

Regards

Uday

[Former Member]

You cannot alter system set control_files= . You have to shut down the database, move the control files, edit the pfile, and open the database with the pfile (instead the spfile) using startup pfile='mypath/init.ora'.

Regards,

Pavan