Home » Server Options » RAC & Failsafe » Listing hidden parameters for all RAC instances (Oracle 10.2.0.4.0 EE - Linux RH EE)
Listing hidden parameters for all RAC instances [message #484408] Tue, 30 November 2010 06:46 Go to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Hi, guys.

I used following statement (user SYS as SYSDBA)

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
  decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),  
  decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),  
  decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),     
  decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  
  decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  
  decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, ksppstcmnt, ksppihash  
from SYS.x$ksppi x, SYS.x$ksppcv y 
where x.indx = y.indx


to list hidden parameters. However, when using it on RAC I found that only a singe instance data is displayed. Anybody aware of any limitation/problem disabling the possibiliti to compare
hidden parameters for RAC?

Michael
Re: Listing hidden parameters for all RAC instances [message #484420 is a reply to message #484408] Tue, 30 November 2010 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you don't query the correct X$ tables; x$ksppsv should be used.

Regards
Michel
Re: Listing hidden parameters for all RAC instances [message #484424 is a reply to message #484420] Tue, 30 November 2010 08:44 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Michel, thank you for reply.

I'll make another try tomorrow, however I took the query definition from GV$PARAMETER view, which lists regular parameters from all instances and it references x$ksppcv table.

I tried selecting the data from each referenced table and even a single table select displays data from one instance only (the one my session is connected to).

Michael

[Updated on: Tue, 30 November 2010 08:47]

Report message to a moderator

Re: Listing hidden parameters for all RAC instances [message #484438 is a reply to message #484424] Tue, 30 November 2010 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because v$parameter refers to your session parameters, so your current instance.
The correct view for instance parameters is [g]v$system_parameter.

Regards
Michel

[Updated on: Tue, 30 November 2010 09:50]

Report message to a moderator

Re: Listing hidden parameters for all RAC instances [message #484454 is a reply to message #484408] Tue, 30 November 2010 12:33 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Michel,

I took the query definition from GV$PARAMETER view...

Re: Listing hidden parameters for all RAC instances [message #484459 is a reply to message #484454] Tue, 30 November 2010 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The correct view is GV$SYSTEM_PARAMETER.

Regards
Michel

[Updated on: Tue, 30 November 2010 13:15]

Report message to a moderator

Re: Listing hidden parameters for all RAC instances [message #484485 is a reply to message #484408] Wed, 01 December 2010 00:55 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Both views (GV$PARAMETER and GV$SYSTEM_PARAMETER are referencing the same tables X$KSPPI and X$KSPPSV) and the same problem remains:

1. Signed to 3 mode RAC as SYS as SYSDBA
2. Made:
SELECT * FROM V$FIXED_VIEW_DEFINITION
WHERE VIEW_NAME = 'GV$SYSTEM_PARAMETER'

3. Got following text from VIEW_DEFINITION column:
select x.inst_id, x.indx + 1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdf,
  decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
  decode(bitand(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE'),
  decode(bitand(ksppiflg, 4), 4, 'FALSE', decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
  decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE'),
  decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
  decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
  ksppdesc, ksppstcmnt, ksppihash
from x$ksppi x, x$ksppsv y
where (x.indx = y.indx)
  and ((translate(ksppinm, '_', '#') not like '##%') and
       ((translate(ksppinm, '_', '#') not like '#%') or
        (ksppstdf = 'FALSE') or (bitand(ksppstvf, 5) > 0)))


4. Now, when I execute:

SELECT * FROM GV$SYSTEM_PARAMETER


I receive 807 rows (data from all 3 instances), however, when I run (again as SYS user) the statement I got in par. #3 - I get 269 rows only (from an instance I connected to).

It does NOT matter if I use GV$PARAMETER of GV$SYSTEM_PARAMETER views. When retrieving data through GV$xxx views - everything works fine, but when selecting directly from X$ tables - only single instance data is displayed.

So the question WHY remains open.

Michael

Re: Listing hidden parameters for all RAC instances [message #484490 is a reply to message #484485] Wed, 01 December 2010 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68257
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Both views (GV$PARAMETER and GV$SYSTEM_PARAMETER are referencing the same tables X$KSPPI and X$KSPPSV) and the same problem remains:

Wrong! Just read your first query.

Quote:
everything works fine, but when selecting directly from X$ tables - only single instance data is displayed.

Just because you are unable to read.

Regards
Michel

[Updated on: Wed, 01 December 2010 01:18]

Report message to a moderator

Re: Listing hidden parameters for all RAC instances [message #484496 is a reply to message #484490] Wed, 01 December 2010 01:37 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Did you try it?
Previous Topic: root.sh is hanging on second node
Next Topic: mount.ocfs2: Invalid argument while mounting /dev/sdd1 on /u02. Check 'dmesg' for more information o
Goto Forum:
  


Current Time: Sun Aug 07 17:57:51 CDT 2022