2 Replies Latest reply on Jan 2, 2014 8:18 AM by 757872

    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
            757872

            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