8 Replies Latest reply: Jan 18, 2013 2:21 AM by nww RSS

    Why archived log of an instance got created in another instance?

    nww
      Hi All,

      It is a 2 nodes RAC. I found that there are archived log of instance#1 created in instance#2. It is strange. Do you have any idea?

      instance#1
      [oracle@server1 dbs]$ ls -l /appl/erp/arch/orcl/
      total 1501340
      -rw-r-----  1 oracle erpdba 49469952 Jan 17 09:33 1_781_715695404.arc
      -rw-r-----  1 oracle erpdba 50551808 Jan 17 09:34 1_782_715695404.arc
      -rw-r-----  1 oracle erpdba     1024 Jan 17 09:39 1_785_715695404.arc
      -rw-r-----  1 oracle erpdba 49051648 Jan 17 10:00 1_786_715695404.arc
      -rw-r-----  1 oracle erpdba 83065344 Jan 17 10:00 2_615_715695404.arc
      -rw-r-----  1 oracle erpdba 51148800 Jan 17 10:01 1_787_715695404.arc
      -rw-r-----  1 oracle erpdba     1024 Jan 17 10:01 2_616_715695404.arc
      -rw-r-----  1 oracle erpdba 48924672 Jan 17 10:01 1_788_715695404.arc
      -rw-r-----  1 oracle erpdba 48935424 Jan 17 10:01 1_789_715695404.arc
      -rw-r-----  1 oracle erpdba 23964160 Jan 17 10:06 1_790_715695404.arc
      -rw-r-----  1 oracle erpdba 51796480 Jan 17 10:07 1_791_715695404.arc
      -rw-r-----  1 oracle erpdba 52080640 Jan 17 10:08 1_792_715695404.arc
      -rw-r-----  1 oracle erpdba 51973120 Jan 17 10:09 1_793_715695404.arc
      instance#2
      [oracle@server2 dbs]$ ls -l /appl/erp/arch/orcl/
      total 1501340
      -rw-rw----  1 oracle erpdba 101348352 Jan 16 15:46 2_605_715695404.arc
      -rw-rw----  1 oracle erpdba 101351424 Jan 16 16:05 2_606_715695404.arc
      -rw-rw----  1 oracle erpdba 101347840 Jan 16 16:06 2_607_715695404.arc
      -rw-r-----  1 oracle erpdba 101350400 Jan 16 16:55 2_596_715695404.arc
      -rw-rw----  1 oracle erpdba   3522560 Jan 16 18:13 2_609_715695404.arc
      -rw-rw----  1 oracle erpdba  47719424 Jan 17 09:33 2_610_715695404.arc
      -rw-rw----  1 oracle erpdba      7168 Jan 17 09:33 2_611_715695404.arc
      -rw-rw----  1 oracle erpdba   5175296 Jan 17 09:34 1_783_715695404.arc
      -rw-rw----  1 oracle erpdba   6170112 Jan 17 09:34 2_612_715695404.arc
      -rw-rw----  1 oracle erpdba  50551808 Jan 17 09:34 1_782_715695404.arc
      -rw-rw----  1 oracle erpdba      1024 Jan 17 09:34 1_784_715695404.arc
      -rw-rw----  1 oracle erpdba 102879232 Jan 17 09:36 2_613_715695404.arc
      -rw-rw----  1 oracle erpdba 103360512 Jan 17 09:38 2_614_715695404.arc
      Regards,
      nww
        • 1. Re: Why archived log of an instance got created in another instance?
          Salman Qureshi
          Hi,
          Paste out of following here
          select inst_id, name, thread#, sequence# from gv$archived_log where first_time like '16-JAN-13%' order by FIRST_TIME ;
          Salman
          • 2. Re: Why archived log of an instance got created in another instance?
            Sebastian Solbach -Dba Community-Oracle
            Hi,

            One explanation is, that if an instance fails, the other instance will do an instance recovery (with the redolog of the failed instance).
            After instance recovery is completed this redolog will be archived. Since the "owning" instance/thread is not up, the archiver of the other instance is doing it.

            However having local storage for archive redologs is a bad setup. Also archive redologs should be placed on a shared filesystem.
            Otherwise, if you need a RMAN recovery, it gets tricky (since you have to copy over the archived redologs).

            Regards
            Sebastian
            • 3. Re: Why archived log of an instance got created in another instance?
              nww
              Hi,

              Here you are.
                 INST_ID NAME                                          THREAD#  SEQUENCE#
              ---------- ------------------------------------------ ---------- ----------
                       2                                                  1        770
                       2 ORCLSB                                           1        770
                       1 ORCLSB                                           1        770
                       1                                                  1        770
                       1                                                  1        771
                       2                                                  1        771
                       1 ORCLSB                                           1        771
                       2 ORCLSB                                           1        771
                       2 ORCLSB                                           1        772
                       1                                                  1        772
                       2                                                  1        772
                       1 ORCLSB                                           1        772
                       1 /appl/erp/arch/ORCL/2_605_715695404.arc          2        605
                       2 /appl/erp/arch/ORCL/2_605_715695404.arc          2        605
                       2 ORCLSB                                           2        605
                       1 ORCLSB                                           2        605
                       1 /appl/erp/arch/ORCL/2_606_715695404.arc          2        606
                       2 /appl/erp/arch/ORCL/2_606_715695404.arc          2        606
                       1 ORCLSB                                           2        606
                       2 ORCLSB                                           2        606
                       2 ORCLSB                                           2        607
                       2 /appl/erp/arch/ORCL/2_607_715695404.arc          2        607
                       1 /appl/erp/arch/ORCL/2_607_715695404.arc          2        607
                       1 ORCLSB                                           2        607
                       1 ORCLSB                                           2        608
                       2 ORCLSB                                           2        608
                       1 /appl/erp/arch/ORCL/2_608_715695404.arc          2        608
                       2 /appl/erp/arch/ORCL/2_608_715695404.arc          2        608
                       2 ORCLSB                                           1        773
                       2 /appl/erp/arch/ORCL/1_773_715695404.arc          1        773
                       1 ORCLSB                                           1        773
                       1 /appl/erp/arch/ORCL/1_773_715695404.arc          1        773
                       2 /appl/erp/arch/ORCL/1_774_715695404.arc          1        774
                       1 ORCLSB                                           1        774
                       2 ORCLSB                                           1        774
                       1 /appl/erp/arch/ORCL/1_774_715695404.arc          1        774
                       2 ORCLSB                                           2        609
                       1 ORCLSB                                           2        609
                       1 /appl/erp/arch/ORCL/2_609_715695404.arc          2        609
                       2 /appl/erp/arch/ORCL/2_609_715695404.arc          2        609
                       1 ORCLSB                                           1        775
                       1 /appl/erp/arch/ORCL/1_775_715695404.arc          1        775
                       2 /appl/erp/arch/ORCL/1_775_715695404.arc          1        775
                       2 ORCLSB                                           1        775
                       2 /appl/erp/arch/ORCL/2_610_715695404.arc          2        610
                       1 ORCLSB                                           2        610
                       1 /appl/erp/arch/ORCL/2_610_715695404.arc          2        610
                       2 ORCLSB                                           2        610
                       2 ORCLSB                                           1        776
                       2 /appl/erp/arch/ORCL/1_776_715695404.arc          1        776
                       1 ORCLSB                                           1        776
                       1 /appl/erp/arch/ORCL/1_776_715695404.arc          1        776
              Regards,
              nww
              • 4. Re: Why archived log of an instance got created in another instance?
                Salman Qureshi
                Hi,
                List returned by query does not look complete, where is the entry for sequence numbers greater than 610 for instance 2 and grater than 776 for instance 1 (as physical files can been seen from your first post). Please also see point from Sebastian in his post, were there any instance reboot on 16th January?

                Salman
                • 5. Re: Why archived log of an instance got created in another instance?
                  nww
                  Hi Salman,

                  I don't know. I just modified your SQL a bit as below.
                  select inst_id, name, thread#, sequence#, first_time from gv$archived_log
                  where first_time like '16-JAN-13%' 
                  and sequence# between 605 and 793
                  order by FIRST_TIME ;
                  I tried again with the follow SQL. Do you know what's going on? The FIRST_TIME changed?
                  select inst_id, name, thread#, sequence#, first_time from gv$archived_log
                  where first_time >= trunc(sysdate-1)
                  and sequence# between 605 and 793
                  order by FIRST_TIME ;
                     INST_ID NAME                                          THREAD#  SEQUENCE# FIRST_TIM
                  ---------- ------------------------------------------ ---------- ---------- ---------
                           2                                                     1        770 16-JAN-13
                           2 ORCLSB                                              1        770 16-JAN-13
                           1 ORCLSB                                              1        770 16-JAN-13
                           1                                                     1        770 16-JAN-13
                           1                                                     1        771 16-JAN-13
                           2                                                     1        771 16-JAN-13
                           1 ORCLSB                                              1        771 16-JAN-13
                           2 ORCLSB                                              1        771 16-JAN-13
                           2 ORCLSB                                              1        772 16-JAN-13
                           1                                                     1        772 16-JAN-13
                           2                                                     1        772 16-JAN-13
                           1 ORCLSB                                              1        772 16-JAN-13
                           1                                                     2        605 16-JAN-13
                           2                                                     2        605 16-JAN-13
                           2 ORCLSB                                              2        605 16-JAN-13
                           1 ORCLSB                                              2        605 16-JAN-13
                           1                                                     2        606 16-JAN-13
                           2                                                     2        606 16-JAN-13
                           1 ORCLSB                                              2        606 16-JAN-13
                           2 ORCLSB                                              2        606 16-JAN-13
                           2 ORCLSB                                              2        607 16-JAN-13
                           2                                                     2        607 16-JAN-13
                           1                                                     2        607 16-JAN-13
                           1 ORCLSB                                              2        607 16-JAN-13
                           1 ORCLSB                                              2        608 16-JAN-13
                           2 ORCLSB                                              2        608 16-JAN-13
                           1                                                     2        608 16-JAN-13
                           2                                                     2        608 16-JAN-13
                           2 ORCLSB                                              1        773 16-JAN-13
                           2                                                     1        773 16-JAN-13
                           1 ORCLSB                                              1        773 16-JAN-13
                           1                                                     1        773 16-JAN-13
                           2                                                     1        774 16-JAN-13
                           1 ORCLSB                                              1        774 16-JAN-13
                           2 ORCLSB                                              1        774 16-JAN-13
                           1                                                     1        774 16-JAN-13
                           2 ORCLSB                                              2        609 16-JAN-13
                           1 ORCLSB                                              2        609 16-JAN-13
                           1                                                     2        609 16-JAN-13
                           2                                                     2        609 16-JAN-13
                           1 ORCLSB                                              1        775 16-JAN-13
                           1                                                     1        775 16-JAN-13
                           2                                                     1        775 16-JAN-13
                           2 ORCLSB                                              1        775 16-JAN-13
                           2                                                     2        610 16-JAN-13
                           1 ORCLSB                                              2        610 16-JAN-13
                           1                                                     2        610 16-JAN-13
                           2 ORCLSB                                              2        610 16-JAN-13
                           2 ORCLSB                                              1        776 16-JAN-13
                           2                                                     1        776 16-JAN-13
                           1 ORCLSB                                              1        776 16-JAN-13
                           1                                                     1        776 16-JAN-13
                           2 ORCLSB                                              1        777 17-JAN-13
                           1                                                     1        777 17-JAN-13
                           1 ORCLSB                                              1        777 17-JAN-13
                           2                                                     1        777 17-JAN-13
                           1                                                     1        778 17-JAN-13
                           1 ORCLSB                                              1        778 17-JAN-13
                           2                                                     1        778 17-JAN-13
                           2 ORCLSB                                              1        778 17-JAN-13
                           2                                                     2        611 17-JAN-13
                           1 ORCLSB                                              2        611 17-JAN-13
                           2 ORCLSB                                              2        611 17-JAN-13
                           1                                                     2        611 17-JAN-13
                           2 ORCLSB                                              1        779 17-JAN-13
                           1                                                     1        779 17-JAN-13
                           1 ORCLSB                                              1        779 17-JAN-13
                           2                                                     1        779 17-JAN-13
                           2 ORCLSB                                              1        780 17-JAN-13
                           2                                                     1        780 17-JAN-13
                           1 ORCLSB                                              1        780 17-JAN-13
                           1                                                     1        780 17-JAN-13
                           1                                                     2        612 17-JAN-13
                           1 ORCLSB                                              2        612 17-JAN-13
                           2                                                     2        612 17-JAN-13
                           2 ORCLSB                                              2        612 17-JAN-13
                           1 ORCLSB                                              1        781 17-JAN-13
                           1                                                     1        781 17-JAN-13
                           2                                                     1        781 17-JAN-13
                           2 ORCLSB                                              1        781 17-JAN-13
                           1                                                     1        782 17-JAN-13
                           2                                                     1        782 17-JAN-13
                           2                                                     1        783 17-JAN-13
                           1                                                     1        783 17-JAN-13
                           2 ORCLSB                                              1        783 17-JAN-13
                           1 ORCLSB                                              1        783 17-JAN-13
                           1 ORCLSB                                              1        784 17-JAN-13
                           1                                                     1        784 17-JAN-13
                           2                                                     1        784 17-JAN-13
                           2 ORCLSB                                              1        784 17-JAN-13
                           2                                                     2        613 17-JAN-13
                           1                                                     2        613 17-JAN-13
                           1 ORCLSB                                              2        613 17-JAN-13
                           2 ORCLSB                                              2        613 17-JAN-13
                           2                                                     2        614 17-JAN-13
                           1                                                     2        614 17-JAN-13
                           1 ORCLSB                                              2        614 17-JAN-13
                           2 ORCLSB                                              2        614 17-JAN-13
                           2 ORCLSB                                              2        615 17-JAN-13
                           1                                                     2        615 17-JAN-13
                           1 ORCLSB                                              2        615 17-JAN-13
                           2                                                     2        615 17-JAN-13
                           1 ORCLSB                                              1        785 17-JAN-13
                           1                                                     1        785 17-JAN-13
                           2                                                     1        785 17-JAN-13
                           2 ORCLSB                                              1        785 17-JAN-13
                           2 ORCLSB                                              1        786 17-JAN-13
                           2                                                     1        786 17-JAN-13
                           1 ORCLSB                                              1        786 17-JAN-13
                           1                                                     1        786 17-JAN-13
                           2                                                     1        787 17-JAN-13
                           1 ORCLSB                                              1        787 17-JAN-13
                           2 ORCLSB                                              1        787 17-JAN-13
                           1                                                     1        787 17-JAN-13
                           1 ORCLSB                                              2        616 17-JAN-13
                           2 ORCLSB                                              2        616 17-JAN-13
                           1                                                     2        616 17-JAN-13
                           2                                                     2        616 17-JAN-13
                           2                                                     1        788 17-JAN-13
                           1 ORCLSB                                              1        788 17-JAN-13
                           1                                                     1        788 17-JAN-13
                           2 ORCLSB                                              1        788 17-JAN-13
                           2 ORCLSB                                              1        789 17-JAN-13
                           1                                                     1        789 17-JAN-13
                           1 ORCLSB                                              1        789 17-JAN-13
                           2                                                     1        789 17-JAN-13
                           1 ORCLSB                                              1        790 17-JAN-13
                           1                                                     1        790 17-JAN-13
                           2                                                     1        790 17-JAN-13
                           2 ORCLSB                                              1        790 17-JAN-13
                           1                                                     1        791 17-JAN-13
                           1 ORCLSB                                              1        791 17-JAN-13
                           2 ORCLSB                                              1        791 17-JAN-13
                           2                                                     1        791 17-JAN-13
                           1                                                     1        792 17-JAN-13
                           1 ORCLSB                                              1        792 17-JAN-13
                           2 ORCLSB                                              1        792 17-JAN-13
                           2                                                     1        792 17-JAN-13
                           2 ORCLSB                                              1        793 17-JAN-13
                           2                                                     1        793 17-JAN-13
                           1 ORCLSB                                              1        793 17-JAN-13
                           1                                                     1        793 17-JAN-13
                           1 ORCLSB                                              2        617 17-JAN-13
                           1                                                     2        617 17-JAN-13
                           2                                                     2        617 17-JAN-13
                           2 ORCLSB                                              2        617 17-JAN-13
                           2 ORCLSB                                              2        618 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_618_715695404.arc             2        618 17-JAN-13
                           1 ORCLSB                                              2        618 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_618_715695404.arc             2        618 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_619_715695404.arc             2        619 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_619_715695404.arc             2        619 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_620_715695404.arc             2        620 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_620_715695404.arc             2        620 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_623_715695404.arc             2        623 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_622_715695404.arc             2        622 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_623_715695404.arc             2        623 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_621_715695404.arc             2        621 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_621_715695404.arc             2        621 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_622_715695404.arc             2        622 17-JAN-13
                           2 /appl/erp/arch/ORCL/2_624_715695404.arc             2        624 17-JAN-13
                           1 /appl/erp/arch/ORCL/2_624_715695404.arc             2        624 17-JAN-13
                  
                  162 rows selected.
                  BTW, what are those records having blank name?

                  Regards,
                  nww
                  • 6. Re: Why archived log of an instance got created in another instance?
                    onedbguru
                    Is "/appl/erp/arch/ORCL" a SHARED directory??? Archivelog destination in a RAC environment should be in a SHARED location (ASM or ACFS or some other cluster file system). Having "local" file system locations is a recipe for disaster. The reason... if NODE2 goes down, NODE1 must be able to access archivelogs and online redo logs to recover the failed instance.
                    • 7. Re: Why archived log of an instance got created in another instance?
                      Salman Qureshi
                      I actually provided you query with date matching 16 Jan, whereas these archives created on 17 and thats why we did not see it in first result which you pasted. Now these are visible because your WHERE clause is OK to extract all archives which we wanted to see
                      BTW, what are those records having blank name?
                      Archives which have been deleted (probably by RMAN after the backup)

                      Probably you should consider answer of Sebastian which states that it seems to instance crash situation which made other instance to perform instance recovery for failed instance and creating archived log on other instance's node.
                      Was there instance reboot for both instances? That is startup time from v$instance for both of instances?

                      It would be good for your to store archived redo logs from both instances at a shared location rather than storing them at a local file system.

                      Salman
                      • 8. Re: Why archived log of an instance got created in another instance?
                        nww
                        Thanks all! It really helps.