Re: Logfiles in RAC database
- From: "Manmohan Jalsingh" <mjalsingh@xxxxxxxxx>
- To: "Bradd Piontek" <piontekdd@xxxxxxxxx>
- Date: Tue, 8 May 2007 13:28:22 -0700
Thanks.
Looks like I have to specify both inst_id and thread# on join between
gv$logfile and gv$log. Any idea why just one of them
is not enough. Isn't instance #1 should means thread#1 and instance#2 means
thread#2. May be I am missing something.
Correct results -
select a.inst_id, a.group#, b.thread#, a.member
from gv$logfile a, gv$log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance);
INST_ID Group# THREAD# Member
---------- ------ ---------- ----------------------------------------
2 3 2 /usr5/apl/ORACLE10R2/t3ky/redo_2_01.log
2 4 2 /usr5/apl/ORACLE10R2/t3ky/redo_2_02.log
Wrong results -
system@t3ky>select a.inst_id, a.group#, b.thread#, a.member
2 from gv$logfile a, gv$log b
3 where a.group#=b.group#
4 and a.inst_id=b.inst_id
5 and b.thread#=(select value from v$parameter where name = 'thread')
6 /
INST_ID Group# THREAD# Member
---------- ------ ---------- ----------------------------------------
2 3 2 /usr5/apl/ORACLE10R2/t3ky/redo_2_01.log
2 4 2 /usr5/apl/ORACLE10R2/t3ky/redo_2_02.log
1 3 2 /usr5/apl/ORACLE10R2/t3ky/redo_2_01.log
1 4 2 /usr5/apl/ORACLE10R2/t3ky/redo_2_02.log
4 rows selected.
Thanks
Manmohan
On 5/8/07, Bradd Piontek < piontekdd@xxxxxxxxx> wrote:
I believe you need to do two things (may be a simpler way to do it).
1. Get the thread id for the instance (select value from v$parameter where
name = 'thread')
2. use the thread number for that instance to joint v$log and v$logfile
(or gv$log, gv$logfile) using the thread# and joining on the group#.
----- Original Message ----
Hi,
In RAC database, how to find out logfiles used by a instance. I queried
gv$logfile view, but
it is showing all logfiles and it is not clear which files are attached to
which instance. Following is
the output of gv$logfile for a 2 nodes RAC database with 2 logfiles
created for each thread.
Is alert.log is the only way for finding it out.
Other related posts:
