Re: spfile used in RAC 10.2.0.4

From: Mark Bobak <Mark.Bobak_at_proquest.com>
Date: Mon, 9 Jun 2014 16:11:03 +0000
Message-ID: <CFBB5451.5BAF6%Mark.Bobak_at_ProQuest.com>

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<mailto:landstander668_at_gmail.com>> Reply-To: "landstander668_at_gmail.com<mailto:landstander668_at_gmail.com>" <landstander668_at_gmail.com<mailto:landstander668_at_gmail.com>> Date: Saturday, June 7, 2014 at 8:03 PM
To: "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto: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<mailto: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<mailto: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<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<mailto: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:11:03 CEST