Re: spfile used in RAC 10.2.0.4

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Mon, 9 Jun 2014 09:29:40 -0700
Message-ID: <CAA2DszzO+ptEufn_JGU_RDcUEwE5H8SJi36rWyLOOa6eOPBJzA_at_mail.gmail.com>

Hello Chen
  Last option is to dump the OCR and search for your spfile name. That should show that spfile name is stored in OCR, hopefully.

  ocrdump /tmp/ocrdump.txt

   It will generate an XML file.

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 Mon, Jun 9, 2014 at 9:18 AM, Chen Zhou <oracle.unknowns_at_gmail.com> wrote:

> Yeah. I tried every command I can think of and these suggested here, none
> showed spfile.
> Although it was obvious srvctl was using spfile to start up the database.
> Thank you,
> Chen
>
>
> On Mon, Jun 9, 2014 at 9:11 AM, Mark Bobak <Mark.Bobak_at_proquest.com>
> wrote:
>
>> Sadly, ‘srvctl config’ output in 10.2.0 is bare minimum…doesn’t include
>> SPFILE location…or much of anything else:
>>
>> -bash-3.2$ srvctl config database -d pqpep
>>
>> pqlegrac201 pqpep1 /oracle/product/10.2.0/db_1
>>
>> pqlegrac202 pqpep2 /oracle/product/10.2.0/db_1
>>
>> Annoying, isn’t it?
>>
>> -Mark
>> From: Adric Norris <landstander668_at_gmail.com>
>> Reply-To: "landstander668_at_gmail.com" <landstander668_at_gmail.com>
>> Date: Saturday, June 7, 2014 at 8:03 PM
>> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
>>
>> Subject: Re: spfile used in RAC 10.2.0.4
>>
>> 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-l

Received on Mon Jun 09 2014 - 18:29:40 CEST