Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 92C9D196107E for ; Fri, 6 Jun 2014 00:06:05 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Fri, 6 Jun 2014 00:06:05 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7BD042C13A; Thu, 5 Jun 2014 18:06:04 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=fail (verification failed; insecure key) header.i=@gmail.com; dkim-adsp=none (insecure policy) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id axYT3t73H2xu; Thu, 5 Jun 2014 18:06:04 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 204E52BCA1; Thu, 5 Jun 2014 18:05:23 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 05 Jun 2014 18:04:42 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D22532BC45 for ; Thu, 5 Jun 2014 18:04:41 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id ZSRdWO4uocQX for ; Thu, 5 Jun 2014 18:04:41 -0400 (EDT) Received: from mail-vc0-f182.google.com (mail-vc0-f182.google.com [209.85.220.182]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 93C692BC42 for ; Thu, 5 Jun 2014 18:04:15 -0400 (EDT) Received: by mail-vc0-f182.google.com with SMTP id il7so1992329vcb.27 for ; Thu, 05 Jun 2014 15:04:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=DKgrA/oV1yl5WblACFZrKLAp4XlvxZz3KGerJayyjII=; b=qWGyZCcdETxTK/paVjzsjvDqnX0hRWIIEiSdQ3PNddA9sjt9iUmc2MJ38IvBuFyrPr z1JBBgHpWq6Y/K6IgSTl8c63fEPWLNOhg2wZNPVZPvg0BAey1Xb22r2j3dU+jnkn9DDv nNab5f4SzbjNGpAl1AXewk1ZlE+xIxF/vzoDUNDTUBEpJ1+bfbAZluhTfL3L+eihslYP TWp3PGK/FVZCmO3otGeGkbu3Ph3sUdYe3SnoqhymLExvIffNDfTtzRGa36kE6BdavDZs 2mIVLCgCzNso1DQpJC0Z7D3CmWu0FB0WGTAeBRh4gS89TW6OjYjZisxazADsS5hCj97j OCJw== MIME-Version: 1.0 X-Received: by 10.221.42.135 with SMTP id ty7mr804735vcb.14.1402005855014; Thu, 05 Jun 2014 15:04:15 -0700 (PDT) Received: by 10.52.165.148 with HTTP; Thu, 5 Jun 2014 15:04:14 -0700 (PDT) In-Reply-To: References: Date: Thu, 5 Jun 2014 15:04:14 -0700 Message-ID: Subject: Re: spfile used in RAC 10.2.0.4 From: Chen Zhou To: Riyaj Shamsudeen Cc: "oracle-l@freelists.org" Content-Type: multipart/alternative; boundary=001a11339974f6161604fb1dec9a X-archive-position: 54849 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: oracle.unknowns@gmail.com Precedence: normal Reply-To: oracle.unknowns@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --001a11339974f6161604fb1dec9a Content-Type: text/plain; charset=UTF-8 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 wrote: > Hi Chen > spfile location is stored in OCR as part of resource attribute: crsctl > stat res ora.db. -p will show all attributes and look for SPFILE. > crsctl stat res ora.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 > > Co-author of the books: Expert Oracle Practices > , Pro Oracle SQL, > Expert RAC > Practices 12c. Expert PL/SQL practices > > > > > > > On Thu, Jun 5, 2014 at 1:24 PM, Chen Zhou > 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 >> >> >> >> > --001a11339974f6161604fb1dec9a Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Riyaj,
The command you suggested does n= ot seem to work.=C2=A0

$crsctl stat res ora.db.proddb1 -p
Unknow= n parameter: stat

Does "crs_stat -p" do the same thi= ng?=C2=A0 If so, it does not show any spfile either.

$crs_stat -p
NAME=3Dora.proddb1.proddb11.inst
TYPE=3Dapplication<= br>ACTION_SCRIPT=3D/oracle/product/10g/bin/racgwrap
ACTIVE_PLACEMENT=3D0=
AUTO_START=3D1
CHECK_INTERVAL=3D600
DESCRIPTION=3DCRS application= for Instance
FAILOVER_DELAY=3D0
FAILURE_INTERVAL=3D0
FAILURE_THRESHOLD=3D0
HOST= ING_MEMBERS=3Dhost1
OPTIONAL_RESOURCES=3D
PLACEMENT=3Drestricted
R= EQUIRED_RESOURCES=3Dora.host1.ASM1.asm
RESTART_ATTEMPTS=3D5
SCRIPT_TI= MEOUT=3D600
START_TIMEOUT=3D0
STOP_TIMEOUT=3D0
UPTIME_THRESHOLD=3D7d
USR_ORA_A= LERT_NAME=3D
USR_ORA_CHECK_TIMEOUT=3D0
USR_ORA_CONNECT_STR=3D/ as sys= dba
USR_ORA_DEBUG=3D0
USR_ORA_DISCONNECT=3Dfalse
USR_ORA_FLAGS=3D<= br>USR_ORA_IF=3D
USR_ORA_INST_NOT_SHUTDOWN=3D
USR_ORA_LANG=3D
USR_ORA_NETMASK=3D
US= R_ORA_OPEN_MODE=3D
USR_ORA_OPI=3Dfalse
USR_ORA_PFILE=3D
USR_ORA_PRECONNECT=3Dnone
USR_ORA_SRV=3D=
USR_ORA_START_TIMEOUT=3D0
USR_ORA_STOP_MODE=3Dimmediate
USR_ORA_STOP_TIMEOUT=3D0
USR_ORA_VIP=3D=
...
...
...

Thank you,
Chen



On Thu, Jun 5, 2014 at 1:35 PM, Riyaj Shamsudeen <riyaj.shamsudee= n@gmail.com> wrote:
Hi Chen
=C2=A0 spfile location is stored in OCR as par= t of resource attribute: =C2=A0crsctl stat res ora.db.<yourdb> -p wil= l show all attributes and look for SPFILE.
=C2=A0 crsctl stat res= ora.db.<db> -p|grep SPFILE

=C2=A0 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 Shamsud= een
Principal DBA,
Ora!nternals -=C2=A0=C2=A0http://www.orainternals.com=C2= =A0- Specialists in Performance, RAC and EBS
Blog:=C2=A0http://orainternals.wordpr= ess.com/
Oracle ACE Director and=C2=A0OakTable member

<= p style=3D"margin:0in 0in 0pt">Co-author of t= he books:=C2=A0Expert Oracle Practices,=C2=A0Pro Oracle SQL,=C2=A0Expert RAC Practices 12c.=C2=A0Expert PL/SQL practices




On Thu, Jun 5, 2014 at 1:24 PM, Chen Zho= u <oracle.unknowns@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.=C2=A0 Th= e 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 pa= rameter in the scope of spfile (alter system set db_file_name_convert=3D'+DG1,+DG1=C2=A0 ' = scope=3Dspfile) sometime ago.
When I tried a bri= ng up the instance with "srvctl start instance" command, in the a= lert 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.=C2=A0 So that discrepancy prevents the instance from = being open.=C2=A0 In alert log, it also shows spfile is set to the common s= pfile on ASM.

My question #1= is how did srvctl find this spfile file?.=C2=A0 In the $ORACLE_HOME/dbs di= rectory, there is only 1 init file.=C2=A0 This init file does NOT point to = the common spfile, which exists on ASM.=C2=A0 So it seems to me srvctl does= not go to $ORACLE_HOME/dbs to look for spfile/pfile, rather it seems to &q= uot;remember" where the spfile was when the instance was up last time.= =C2=A0 Or it looks at the other 2 nodes and finds out where the command spf= ile is?=C2=A0

I ended up using sqlpl= us to start the instance with the local init file.=C2=A0 Now I have one ins= tance running with pfile, 2 other instances running with spfile.=C2=A0 And = the parameters match fine currently.=C2=A0 So db_file_n= ame_convert is unset.=C2=A0 However at the next bounce, the other 2 = nodes will try to take on the new value for db_file_nam= e_convert, and the discrepancy will prevent them from starting up.
So my questio= n #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 s= hutting all 3 nodes down then bringing them up.=C2=A0 Is it so?

Thank you= ,
Chen
=




--001a11339974f6161604fb1dec9a-- -- http://www.freelists.org/webpage/oracle-l