Re: spfile used in RAC 10.2.0.4
- From: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>
- To: "oracle.unknowns@xxxxxxxxx" <oracle.unknowns@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Jun 2014 20:40:53 +0000
Hi Chen,
When the database entry was created using 'srvctl add database', the spfile
path was (probably) provided via the '-p' flag. The database definition in the
OCR contains the location of the spfile, and that's how it can be located when
you start with srvctl.
As to resolving the problem, you should be able to go to one of the remaning
nodes that are up, and do either 'alter system set' or 'alter system reset' and
specify the SID of the instance that has the incorrect value in the spfile, to
either update or remove that parameter for that instance. Then, just shutdown
the problem instance, and you should be able to restart it with 'srvctl' and
the default spfile.
Then make a note to follow up with the other DBA on the correct value of that
parameter, and when it can be implemented consistently across all db instances.
As you learned, it can be dangerous to set a parameter in spfile only, if it's
value needs to be consistent across instances. Anytime you set such a
parameter, it needs to be immediately before a complete DB bounce of all
instances.
Hope that helps,
-Mark
From: Chen Zhou <oracle.unknowns@xxxxxxxxx<mailto:oracle.unknowns@xxxxxxxxx>>
Reply-To: Chen Zhou
<oracle.unknowns@xxxxxxxxx<mailto:oracle.unknowns@xxxxxxxxx>>
Date: Thursday, June 5, 2014 at 4:24 PM
To: "oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>"
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: spfile used in RAC 10.2.0.4
Hi, Experts,
We have a 3-node 10.2.0.4 RAC on Solaris 10.
One node crashed due to ORA-600 [kturacf1] error. The main DBA of this
database was out, so I had to bring it up.
I can see in the alert log of that instance the DBA had run a command to change
a parameter in the scope of spfile (alter system set
db_file_name_convert='+DG1,+DG1 ' scope=spfile) sometime ago.
When I tried a bring up the instance with "srvctl start instance" command, in
the alert log I can see that changed parameter db_file_name_convert takes the
new value and is different from what the value is in the other 2 nodes. So
that discrepancy prevents the instance from being open. In alert log, it also
shows spfile is set to the common spfile on ASM.
My question #1 is how did srvctl find this spfile file?. In the
$ORACLE_HOME/dbs directory, there is only 1 init file. This init file does NOT
point to the common spfile, which exists on ASM. So it seems to me srvctl does
not go to $ORACLE_HOME/dbs to look for spfile/pfile, rather it seems to
"remember" where the spfile was when the instance was up last time. Or it
looks at the other 2 nodes and finds out where the command spfile is?
I ended up using sqlplus to start the instance with the local init file. Now I
have one instance running with pfile, 2 other instances running with spfile.
And the parameters match fine currently. So db_file_name_convert is unset.
However at the next bounce, the other 2 nodes will try to take on the new value
for db_file_name_convert, and the discrepancy will prevent them from starting
up.
So my question #2 is if there is a rolling way changing this parameter or all
non-system modifiable parameters. It doesn't seem possible to change it without
shutting all 3 nodes down then bringing them up. Is it so?
Thank you,
Chen
Other related posts:
