2 Replies Latest reply: Jan 2, 2014 2:18 AM by Viren Patel RSS

    Confusion in the APP column in V$ARCHIVED_LOG

    817202

      Hi All,

       

      I have some confusion regarding APP column of v$ARCHIVED_LOG

      On my Primary machine when i execute :-

      SQL> SELECT THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED FROM V$ARCHIVED_LOG;

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1          2        527611       545327 NO

               1          3        545327       549119 NO

               1          4        549119       557792 NO

               1          5        557792       559897 NO

               1          6        559897       560068 NO

               1          7        560068       560072 NO

               1          6        559897       560068 YES

               1          7        560068       560072 YES

               1          5        557792       559897 YES

               1          8        560072       560335 NO

               1          9        560335       560338 NO

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1         10        560338       560340 NO

               1         10        560338       560340 YES

               1          8        560072       560335 YES

               1          9        560335       560338 YES

               1         11        560340       560819 NO

               1         11        560340       560819 YES

               1         12        560819       560840 NO

               1         12        560819       560840 YES

               1         13        560840       561853 YES

               1         13        560840       561853 NO

               1         14        561853       590041 YES

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1         14        561853       590041 NO

               1         15        590041       590061 NO

               1         15        590041       590061 YES

               1         16        590061       594075 YES

               1         16        590061       594075 NO

               1         17        594075       594119 NO

               1         17        594075       594119 YES

               1         18        594119       604547 YES

               1         18        594119       604547 NO

               1         19        604547       605449 YES

               1         19        604547       605449 NO

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1         20        605449       605682 NO

               1         20        605449       605682 YES

               1         21        605682       605722 NO

               1         21        605682       605722 YES

               1         22        605722       605726 NO

               1         22        605722       605726 YES

               1         23        605726       605728 NO

               1         23        605726       605728 YES

               1         23        605726       605728 NO

               1         22        605722       605726 NO

               1         21        605682       605722 NO

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1         19        604547       605449 NO

               1         20        605449       605682 NO

               1         18        594119       604547 NO

       

       

      On my standby Machine when i execute :-

       

      SQL>  SELECT THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED FROM V$ARCHIVED_LOG;

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1          7        560068       560072 YES

               1          6        559897       560068 YES

               1          5        557792       559897 YES

               1         10        560338       560340 YES

               1          8        560072       560335 YES

               1          9        560335       560338 YES

               1         11        560340       560819 YES

               1         12        560819       560840 YES

               1         13        560840       561853 YES

               1         14        561853       590041 YES

               1         15        590041       590061 YES

       

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APP

      ---------- ---------- ------------- ------------ ---

               1         16        590061       594075 YES

               1         17        594075       594119 YES

               1         18        594119       604547 YES

               1         19        604547       605449 YES

               1         20        605449       605682 YES

               1         21        605682       605722 YES

               1         22        605722       605726 YES

               1         23        605726       605728 YES

               1         24        605728       605818 YES

               1         25        605818       606571 YES

               1         26        606571       606586 NO

       

      So here we find that in primary machine in the APP column NO is mentioned but as i can check in the standby machine the log is present. So what do we understand by NO in primary machine.

        • 1. Re: Confusion in the APP column in V$ARCHIVED_LOG
          mseberg

          Hello;

           

          the column DEST_ID is the key on the Primary side. Try either including it in your query or setting it in the where clause to the Standby destination.

           

          For example:

           

          DEST_ID = 2

           

          So the Primary without it shows the same log twice, once for local and once for standby.

           

          There is no "APP" column. The correct column name is "APPLIED".

           

           

          Best Regards

           

          mseberg


          • 2. Re: Confusion in the APP column in V$ARCHIVED_LOG
            Viren Patel

            Hi,

             

            Yes It is not APP columns, Its APPLIED.

            Applied column says, The particular sequence of archive log is applied on stand by database or not

            If YES - Applied, NO - Not Applied, IN-MEMORY - In Process of applied

             

            On Primary database every sequence has entry with respected DEST_ID

            DEST_ID = 1 = Primary database

            DEST_ID = 2 = Standby database

             

            On Primary database O/P looks like

            SEQUENCE# APPLIED   ARCDEST_ID

            ---------- --------- --- ----------

            90763 YES   YES      2
            90763 NO    YES      1
            90764 YES   YES      2
            90764 NO    YES      1
            90765 YES   YES      2
            90765 NO    YES      1
            90766 YES   YES     

            2

             

             

            No Need to apply archive logs on primary that's why it says NO under Applied columns on Primary database

             

            Thanks

            Viren