Re: spfile used in RAC 10.2.0.4
Date: Mon, 9 Jun 2014 13:46:39 -0700
Message-ID: <CAJUY3dQu3va2tuwLHvJd3rHCPRpzOCG5CyVtdZc5EcBBkBmi4Q_at_mail.gmail.com>
Ha! The OCR dump does have the spfile defined. Thank you.
On Mon, Jun 9, 2014 at 9:29 AM, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com
> wrote:
> 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-lReceived on Mon Jun 09 2014 - 22:46:39 CEST