Home » RDBMS Server » Server Administration » multiplexing the controlfile
multiplexing the controlfile [message #247981] Wed, 27 June 2007 13:31 Go to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
i have my controlfiles all in a single directory but was just thinking that won't be ideal enough,as shown below. 

SQL> show parameters control_files;

NAME           TYPE                VALUE
-------- ----------- ------------------------------
control_files     string      C:\ORACLE\PRODUCT\10.1.\ORADATA\ORCL_1\CONTROL01.CTL, 
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL02.CTL, 
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL03.CTL


i need to make a multiplex to a new directory like
''C:\oracle\product\10.1.0\oradata\orcl_1\mulplx\CONTROL04.CTL''
If I do
alter system set control_file=''C:\oracle\product\10.1.0\oradata\orcl_1\CONTROL01.CTL'',''C:\oracle\product\10.1.0\oradata\orcl_1\mulplx\CONTROL04.CTL '' scope = spfile;

Will oracle synchronise the new controlfile withe the old ones even if they are in different directory.
thanks

Report message to a moderator

Re: multiplexing the controlfile [message #247987 is a reply to message #247981] Wed, 27 June 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to copy the control file to the new destination (after shutting down the database).

One remark: the new control file is still in the same disk, this is not good thing.
For performances reason, don't use more than 3 control files.

Regards
Michel

Report message to a moderator

Re: multiplexing the controlfile [message #247989 is a reply to message #247981] Wed, 27 June 2007 14:00 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
i have only one drive on my system that is why i want to multiplex to a different directory which i think is better than none. pls will oracle synchronise them if i do it that way.

further advise will be appreciated. thanks

Report message to a moderator

Re: multiplexing the controlfile [message #247991 is a reply to message #247989] Wed, 27 June 2007 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
You have to copy the control file to the new destination (after shutting down the database).

After that yes Oracle synchronizes every control file wherever they are. More, if it can't it shut down the database.

Regards
Michel

Report message to a moderator

Re: multiplexing the controlfile [message #247993 is a reply to message #247981] Wed, 27 June 2007 14:28 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
thanks mitchel, do i have to change the initfile to include the new directory

Report message to a moderator

Re: multiplexing the controlfile [message #247998 is a reply to message #247993] Wed, 27 June 2007 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You said you use a spfile so updating the init file is not mandatory.
Do it as you want.

Regards
Michel

Report message to a moderator

Re: multiplexing the controlfile [message #248043 is a reply to message #247981] Wed, 27 June 2007 18:59 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
I connected as sysdba did the following
1)shutdown immediate
2)copy the controlfile successfully to new directory using the
os command
3)startup nomount

Then i i tried setting the control file to the new one but got the error below.

SQL> alter system set control_files = ''C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL'',''C:
\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\MLPLX_CTL\CONTROL01'' scope=spfile;
alter system set control_files = ''C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL'',''C:\ORAC
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


I queried the v$parameter to confirm that i am using spfile as below

SQL> select name,value from v$parameter where name = 'spfile';

NAME                           VALUE
-----------------------------------------------------------------
spfile     C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SPFILEORCL1.ORA

SQL> select name,value from v$parameter where name = 'pfile';

no rows selected

pls can you help

[Updated on: Wed, 27 June 2007 19:01]

Report message to a moderator

Re: multiplexing the controlfile [message #248046 is a reply to message #247981] Wed, 27 June 2007 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sqlplus
/ as sysdba
create pfile from spfile;
exit
#manually edit pfile
sqlplus
/ as sysdba
startup -- using modified pfile
create spfile from pfile;
shutdown immediate
startup
exit

Report message to a moderator

Re: multiplexing the controlfile [message #248047 is a reply to message #248043] Wed, 27 June 2007 19:26 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
seyiisq wrote on Wed, 27 June 2007 19:59

SQL> alter system set control_files = ''C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL'',''C:
\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\MLPLX_CTL\CONTROL01'' scope=spfile;
alter system set control_files = ''C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL'',''C:\ORAC
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified



Just surround each controlfile name with sinlge-quotes, ie:

alter system set control_file='C:\oracle\product\10.1.0\oradata\orcl_1\CONTROL01.CTL',
 'C:\oracle\product\10.1.0\oradata\orcl_1\mulplx\CONTROL04.CTL' scope = spfile;

[Updated on: Wed, 27 June 2007 19:37]

Report message to a moderator

Re: multiplexing the controlfile [message #248048 is a reply to message #247981] Wed, 27 June 2007 19:45 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
pfile created sucessfully control file edited as shown but while starting up the db gave the error as below
###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.1.0\oradata\orcl_1\control01.ctl", "C:\oracle\product\10.1.0\oradata\orcl_1\control02.ctl", "C:\oracle\product\10.1.0\oradata\orcl_1\control03.ctl","C:\oracle\product\10.1.0\oradata\orcl_1\mlplx_ctl\control01.ctl")
db_recovery_file_dest=C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size=2147483648


SQL> startup open pfile=C:\oracle\product\10.1.0\admin\orcl_1\pfile\init.ora
LRM-00109: could not open parameter file 'C:\oracle\product\10.1.0\admin\orcl_1\pfile\init.ora'
ORA-01078: failure in processing system parameters

Report message to a moderator

Re: multiplexing the controlfile [message #248051 is a reply to message #247981] Wed, 27 June 2007 21:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Correct the error & try again,again.

Report message to a moderator

Re: multiplexing the controlfile [message #248291 is a reply to message #248051] Thu, 28 June 2007 09:56 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
SQL> startup open pfile=C:\oracle\product\10.1.0\admin\orcl_1\pfile\init.ora
LRM-00109: could not open parameter file 'C:\oracle\product\10.1.0\admin\orcl_1\pfile\init.ora'
ORA-01078: failure in processing system parameters


check alert log for more information..

Report message to a moderator

Re: multiplexing the controlfile [message #248319 is a reply to message #247981] Thu, 28 June 2007 11:17 Go to previous messageGo to next message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
the 'alter system set control_file ..... scope=spfile' finally worked but when restarted the db i got this error.

SQL> startup force;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
ORA-00214: controlfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL'
version 2059 inconsistent with file
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\MLPLX_CTL\CONTROL01.CTL' version 2012

Report message to a moderator

Re: multiplexing the controlfile [message #248322 is a reply to message #248319] Thu, 28 June 2007 11:28 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
dONT USE STARTUP FORCE..

After multiplexing shutdown normal.

check the files on path which you mentioned in parameter, and after that start your instance.

http://www.siue.edu/~dbock/cmis565/module7-control_file.htm

[Updated on: Thu, 28 June 2007 11:29]

Report message to a moderator

Re: multiplexing the controlfile [message #248330 is a reply to message #247981] Thu, 28 June 2007 12:19 Go to previous message
seyiisq
Messages: 125
Registered: September 2005
Senior Member
thanks it worked after doing it as shown below
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> host copy C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL C:\ORACLE\PRODUCT\10.1.0\ORADAT
A\ORCL_1\MLPLX_CTL\CONTROL01.CTL

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.


when i did
SQL> show parameter control_files;

NAME               TYPE        VALUE
------------------------------------ ----------- ---------------
control_files      string   
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL01.CTL, 
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\MLPLX_CTL\CONTROL01.CTL

SQL>
but initially before multiplexing i had 3 controlfiles as seen at the start of this thread. could it be that after multiplexing i would only be shown the multiplexed files.

Report message to a moderator

Previous Topic: Converting from Informix to Oracle
Next Topic: How to track all "selects" thrown against a table ?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 30 06:30:45 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.