Re: spfile used in RAC 10.2.0.4

From: Chen Zhou <oracle.unknowns_at_gmail.com>
Date: Thu, 5 Jun 2014 15:37:06 -0700
Message-ID: <CAJUY3dR=QtmphjCGoPnLKtHhHEEnz2or+2dZyvW9tGk42ByOWg_at_mail.gmail.com>



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_at_uoregon.edu> 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_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> 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_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-l
Received on Fri Jun 06 2014 - 00:37:06 CEST

Original text of this message