RE: spfile used in RAC 10.2.0.4
Date: Thu, 5 Jun 2014 22:17:58 +0000
Message-ID: <9843B4DDAEB5BE42B2A704DE21AFBD62FFEDD9_at_ad-cc-mbx02>
Hi Chen,
If the other 2 nodes are up, why not just set the parameter back to what it should be on one of those?
On the node that’s up, run:
SQL > show parameter db_file_name_convert;
Because the instance hasn’t been restarted, it should have the correct value. (I think it’s the Space that is causing you the problem, btw) Once you have the value that the nodes came up on, set it back in the spfile.
SQL> alter system set db_file_name_convert=’put your value here’ scope=spfile;
It is possible that only the node that is down had the parameter change. If you don’t know how to create a pfile from spfile to see exactly what the values are, then use an active node to set it to the appropriate value for the inactive node… IE: alter system set db_file_name_convert=’the correct value’ scope = spfile sid = ‘thebrokennode’…. Or something similar. (note, don’t copy/paste this, my quotes are wrong)
Good Luck.
~ Tyfanie Wineriter ~
Database Administrator
University of Oregon
1212 University of Oregon
Eugene, OR 97402-1212
(541) 346-1366
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chen Zhou
Sent: Thursday, June 05, 2014 3:04 PM
To: Riyaj Shamsudeen
Cc: oracle-l_at_freelists.org
Subject: Re: spfile used in RAC 10.2.0.4
Riyaj,
The command you suggested does not seem to work.
$crsctl stat res ora.db.proddb1 -p
Unknown parameter: stat
Does "crs_stat -p" do the same thing? If so, it does not show any spfile either.
$crs_stat -p
NAME=ora.proddb1.proddb11.inst
TYPE=application
ACTION_SCRIPT=/oracle/product/10g/bin/racgwrap
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=600
DESCRIPTION=CRS application for Instance
FAILOVER_DELAY=0
FAILURE_INTERVAL=0 FAILURE_THRESHOLD=0 HOSTING_MEMBERS=host1
OPTIONAL_RESOURCES=
PLACEMENT=restricted
REQUIRED_RESOURCES=ora.host1.ASM1.asm
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=600
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME= USR_ORA_CHECK_TIMEOUT=0 USR_ORA_CONNECT_STR=/ as sysdba USR_ORA_DEBUG=0 USR_ORA_DISCONNECT=false USR_ORA_FLAGS= USR_ORA_IF= USR_ORA_INST_NOT_SHUTDOWN= USR_ORA_LANG= USR_ORA_NETMASK= USR_ORA_OPEN_MODE= USR_ORA_OPI=false USR_ORA_PFILE= USR_ORA_PRECONNECT=none USR_ORA_SRV= USR_ORA_START_TIMEOUT=0 USR_ORA_STOP_MODE=immediate USR_ORA_STOP_TIMEOUT=0 USR_ORA_VIP=
...
...
...
Thank you,
Chen
On Thu, Jun 5, 2014 at 1:35 PM, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com<mailto:riyaj.shamsudeen_at_gmail.com>> wrote:
Hi Chen
spfile location is stored in OCR as part of resource attribute: crsctl stat res ora.db.<yourdb> -p will show all attributes and look for SPFILE.
crsctl stat res ora.db.<db> -p|grep SPFILE
Also, if the parameter to be changed "must match in all instances", then it is not possible to do rolling bounce, AFAIK. You would need to shutdown all instances momentarily and start them.
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com<http://www.orainternals.com/> - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/
Oracle ACE Director and OakTable member<http://www.oaktable.com/>
Co-author of the books: Expert Oracle Practices<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> Expert RAC Practices 12c.<http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices<http://tinyurl.com/book-expert-plsql-practices>
On Thu, Jun 5, 2014 at 1:24 PM, Chen Zhou <oracle.unknowns_at_gmail.com<mailto:oracle.unknowns_at_gmail.com>> wrote:
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 06 2014 - 00:17:58 CEST