Re: Logfiles in RAC database

Pete,

I was expecting to see only two redo logfiles under a instance, but it is showing all four files. In my case -

Instance#1 has 2 redo files - redo_1_01.log & redo_1_02.log Instance#2 has 2 redo files - redo_2_01.log & redo_2_02.log

system_at_t3ky1>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   a.inst_id=( select instance_number from v$instance);

   INST_ID Group#    THREAD#                  Member
---------- ------ ---------- ----------------------------------------
         1      1          1 /usr5/apl/ORACLE10R2/t3ky/redo_1_01.log
         1      2          1 /usr5/apl/ORACLE10R2/t3ky/redo_1_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, Pete Sharman <peter.sharman_at_oracle.com> wrote:
>
> Either I'm missing something entirely or you've answered your own
> question. INST_ID is the instance ID for the instance the logfiles are used
> on. If you want the instance name instead, you can get that from
> v$instance.
>
>
>
> Or am I really missing something entirely? J
>
>
>
> Pete
>
>
>
> "Controlling developers is like herding cats'"
>
> Kevin Loney, Oracle DBA Handbook
>
>
>
> "Oh no, it's not, it's much harder than that!"
>
> Bruce Pihlamae, long term Oracle DBA
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Manmohan Jalsingh
> *Sent:* Wednesday, 9 May 2007 4:46 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Logfiles in RAC database
>
>
>
> 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.
>
> system_at_t3kq>select * from gv$logfile
> 2 /
>
> INST_ID Group# Status Type Member
> IS_
> ---------- ------ ------- ------- ----------------------------------------
> ---
> 1 1 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_1_01.log
> NO
> 1 2 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_1_02.log
> NO
> 1 3 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_2_01.log
> NO
> 1 4 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_2_02.log
> NO
> 2 1 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_1_01.log
> NO
> 2 2 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_1_02.log
> NO
> 2 3 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_2_01.log
> NO
> 2 4 ONLINE /usr5/apl/ORACLE10R2/t3kq/redo_2_02.log
> NO
>
> 8 rows selected.
>
> Thanks
> Manmohan
>

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