Re: spfile used in RAC 10.2.0.4
Date: Sat, 7 Jun 2014 19:03:57 -0500
Message-ID: <CAJueESqyW4aWD3Ot-Oj0vkBUB2QWot7rhZbGdpYYcJhUpWBD=g_at_mail.gmail.com>
Have you tried "srvctl config database -d DBNAME"? Its output should include the SPFILE location, if defined to OCR, and I *believe* it works with 10g (but don't currently have a 10g database available to verify, however).
If no spfile is defined, then the usual $ORACLE_HOME/dbs default location should be used.
On Thu, Jun 5, 2014 at 5:04 PM, Chen Zhou <oracle.unknowns_at_gmail.com> wrote:
> 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
>>>
>>>
>>>
>>>
>>
>
-- "I'm too sexy for my code." -Awk Sed Fred -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jun 08 2014 - 02:03:57 CEST