Re: spfile used in RAC 10.2.0.4

From: Chen Zhou <oracle.unknowns_at_gmail.com>
Date: Thu, 5 Jun 2014 15:04:14 -0700
Message-ID: <CAJUY3dRLwuGdR6GU8LZLkLOrQJ-t9hU0DQ56NGiBP+Ec=n9J3w_at_mail.gmail.com>



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> <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>
>
> <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:04:14 CEST

Original text of this message