Hi, Tyfanie, I will consider changing to spfile once the main DBA comes back to find out what value he actually wants this parameter set to and I may need to schedule a downtime for the database. Thank you, Chen On Thu, Jun 5, 2014 at 3:17 PM, Tyfanie Wineriter <tyfaniew@xxxxxxxxxxx> wrote: > 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@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Chen Zhou > *Sent:* Thursday, June 05, 2014 3:04 PM > *To:* Riyaj Shamsudeen > *Cc:* oracle-l@xxxxxxxxxxxxx > *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@xxxxxxxxx> 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 - 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@xxxxxxxxx> > 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 > > > > > > > > >