Re: Logfiles in RAC database

From: Manmohan Jalsingh <mjalsingh_at_gmail.com>
Date: Tue, 8 May 2007 13:28:22 -0700
Message-ID: <7c4806e20705081328q44702c37xe918800319290e22@mail.gmail.com>

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_at_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_at_yahoo.com> 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.
>
>
>

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

Received on Tue May 08 2007 - 15:28:22 CDT