13 Replies Latest reply: Nov 26, 2012 11:03 PM by Shivananda Rao RSS

    Last Applied Log is null in standby database

    971970
      Hello,

      I found, for unkown reason, the physical standby database is behind,

      in primary, current log sequence# is 35749, and in standby

      standby>select max(sequence#) from v$archived_log where applied='YES';

      MAX(SEQUENCE#)
      --------------
      34660


      then, I repair the stanby database by using RMAN incremental backups to Roll forward a Phisical standby database (10.2.0.4), like below:

      http://shivanandarao.wordpress.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/

      in fact, I did repair the same stanby database like above method in last month, and it did work well.
      but this time, I did exactly as I did last time, I found problem that I can't see last applied log number, like

      standby >select max(sequence#) from v$archived_log where applied='YES';

      MAX(SEQUENCE#)
      --------------

      (null)

      then

      standby> select process,sequence#,status from v$managed_standby;

      PROCESS SEQUENCE# STATUS
      --------- ---------- ------------
      ARCH 35755 CLOSING
      MRP0 34661 WAIT_FOR_GAP
      RFS 35756 IDLE

      in grid control , I found the problem is same, LAST Applied log is shown as null (no value).

      Is there anything I can do to fix this problem?

      (background: in stanby database, I didn't use rman catalog, in primary, I use rman catalog, but last time, I did repair the behind standby by using incremental backup)


      thank you very much and happy thanksgiving.
        • 1. Re: Last Applied Log is null in standby database
          971970
          Hi,.
          Maybe I create this ticket before Thankgiving, so it is not right time... while, is there anyone who can help this question?

          thank you
          • 2. Re: Last Applied Log is null in standby database
            vlethakula
            can you post below

            select FACILITY,SEVERITY,to_char(TIMESTAMP,'DDMonYY hh24:mi:ss'),message from V$DATAGUARD_STATUS order by 3;

            select * from v$archive_gap;

            Edited by: vlethakula on Nov 26, 2012 12:37 PM
            • 3. Re: Last Applied Log is null in standby database
              971970
              THANK YOU VERY MUCH
              1.
              select * from v$archive_gap;

              no rows selected

              2.

              select FACILITY,SEVERITY,to_char(TIMESTAMP,'DDMonYY hh24:mi:ss'),message from V$DATAGUARD_STATUS order by 3;


              FACILITY SEVERITY TO_CHAR(TIMESTAM MESSAGE
              ------------------------ ------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Remote File Server Informational 26Nov12 08:19:13 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Informational 26Nov12 08:19:13 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Warning 26Nov12 08:19:13 RFS[23]: No standby redo logfiles of size 102400 blocks available
              Remote File Server Error 26Nov12 08:19:14 Creating archive destination file : +RECOVER (102400 blocks)
              Log Apply Services Informational 26Nov12 08:19:17 -- Connected User is Valid
              Log Apply Services Informational 26Nov12 08:19:17 Redo Shipping Client Connected as PUBLIC
              Remote File Server Informational 26Nov12 08:19:17 RFS[24]: Assigned to RFS process 15991104
              Remote File Server Warning 26Nov12 08:19:17 RFS[24]: No standby redo logfiles of size 2437 blocks available
              Remote File Server Informational 26Nov12 08:19:17 RFS[24]: Identified database type as 'physical standby'
              Log Transport Services Error 26Nov12 08:21:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 08:27:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'

              FACILITY SEVERITY TO_CHAR(TIMESTAM MESSAGE
              ------------------------ ------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Log Transport Services Error 26Nov12 08:33:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 08:39:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 08:45:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Apply Services Informational 26Nov12 08:49:27 Redo Shipping Client Connected as PUBLIC
              Log Apply Services Informational 26Nov12 08:49:27 -- Connected User is Valid
              Remote File Server Error 26Nov12 08:49:27 Creating archive destination file : +RECOVER (102400 blocks)
              Remote File Server Warning 26Nov12 08:49:27 RFS[25]: No standby redo logfiles of size 102400 blocks available
              Remote File Server Informational 26Nov12 08:49:27 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Informational 26Nov12 08:49:27 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Informational 26Nov12 08:49:27 RFS[25]: Identified database type as 'physical standby'
              Remote File Server Informational 26Nov12 08:49:27 RFS[25]: Assigned to RFS process 24379546

              FACILITY SEVERITY TO_CHAR(TIMESTAM MESSAGE
              ------------------------ ------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Log Apply Services Informational 26Nov12 08:50:21 Redo Shipping Client Connected as PUBLIC
              Log Apply Services Informational 26Nov12 08:50:21 -- Connected User is Valid
              Remote File Server Informational 26Nov12 08:50:21 RFS[26]: Assigned to RFS process 16515446
              Remote File Server Informational 26Nov12 08:50:21 RFS[26]: Identified database type as 'physical standby'
              Remote File Server Warning 26Nov12 08:50:21 RFS[26]: No standby redo logfiles of size 893 blocks available
              Log Transport Services Error 26Nov12 08:51:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 08:57:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:03:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:09:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:15:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Remote File Server Informational 26Nov12 09:19:13 RFS[27]: Assigned to RFS process 16908314

              FACILITY SEVERITY TO_CHAR(TIMESTAM MESSAGE
              ------------------------ ------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Log Apply Services Informational 26Nov12 09:19:13 -- Connected User is Valid
              Log Apply Services Informational 26Nov12 09:19:13 Redo Shipping Client Connected as PUBLIC
              Remote File Server Informational 26Nov12 09:19:13 RFS[27]: Identified database type as 'physical standby'
              Remote File Server Warning 26Nov12 09:19:14 RFS[27]: No standby redo logfiles of size 102400 blocks available
              Remote File Server Informational 26Nov12 09:19:14 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Informational 26Nov12 09:19:14 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Error 26Nov12 09:19:14 Creating archive destination file : +RECOVER (102400 blocks)
              Log Apply Services Informational 26Nov12 09:19:27 Redo Shipping Client Connected as PUBLIC
              Log Apply Services Informational 26Nov12 09:19:27 -- Connected User is Valid
              Remote File Server Informational 26Nov12 09:19:27 RFS[28]: Assigned to RFS process 34013222
              Remote File Server Informational 26Nov12 09:19:27 RFS[28]: Identified database type as 'physical standby'

              FACILITY SEVERITY TO_CHAR(TIMESTAM MESSAGE
              ------------------------ ------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Remote File Server Warning 26Nov12 09:19:27 RFS[28]: No standby redo logfiles of size 2238 blocks available
              Log Transport Services Error 26Nov12 09:21:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:27:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:33:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:39:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Transport Services Error 26Nov12 09:45:04 ARC0: Error 19504 Creating archive log file to '+RECOVER'
              Log Apply Services Informational 26Nov12 09:49:39 Redo Shipping Client Connected as PUBLIC
              Log Apply Services Informational 26Nov12 09:49:39 -- Connected User is Valid
              Remote File Server Informational 26Nov12 09:49:39 RFS[29]: Assigned to RFS process 35193086
              Remote File Server Informational 26Nov12 09:49:39 RFS[29]: Identified database type as 'physical standby'
              Remote File Server Informational 26Nov12 09:49:39 Primary database is in MAXIMUM PERFORMANCE mode

              FACILITY SEVERITY TO_CHAR(TIMESTAM MESSAGE
              ------------------------ ------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Remote File Server Informational 26Nov12 09:49:40 Primary database is in MAXIMUM PERFORMANCE mode
              Remote File Server Error 26Nov12 09:49:40 Creating archive destination file : +RECOVER (102400 blocks)
              Remote File Server Warning 26Nov12 09:49:40 RFS[29]: No standby redo logfiles of size 102400 blocks available
              • 4. Re: Last Applied Log is null in standby database
                971970
                and there is something more...:

                in the standby,

                SYS@rimfcs> select a.group#,a.member,b.status,b.archived,bytes/1024/1024 mbytes
                2 from v$logfile a, v$log b
                3 where a.group# = b.group#
                4 order by 1,2;

                GROUP# MEMBER STATUS ARC MBYTES
                ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- --- ----------
                1 /logfile1/rimfcs/redo1a.rdo CLEARING_CURRENT YES 50
                1 /logfile2/rimfcs/redo1b.rdo CLEARING_CURRENT YES 50
                2 /logfile1/rimfcs/redo2a.rdo CLEARING YES 50
                2 /logfile2/rimfcs/redo2b.rdo CLEARING YES 50
                3 /logfile1/rimfcs/redo3a.rdo CLEARING YES 50
                3 /logfile2/rimfcs/redo3b.rdo CLEARING YES 50

                6 rows selected.

                SYS@rimfcs> select distinct group#, type from v$logfile order by group#;

                GROUP# TYPE
                ---------- -------
                1 ONLINE
                2 ONLINE
                3 ONLINE
                4 STANDBY
                5 STANDBY
                6 STANDBY
                7 STANDBY

                7 rows selected.
                • 5. Re: Last Applied Log is null in standby database
                  vlethakula
                  can you also run the below command on primary ( assuming you are not having more than 2 standby's if more please change it to more in where clause)

                  SELECT DEST_ID "ID",
                  STATUS "DB_status",
                  DESTINATION "Archive_dest",
                  ERROR "Error"
                  FROM V$ARCHIVE_DEST WHERE DEST_ID <=3;
                  • 6. Re: Last Applied Log is null in standby database
                    971970
                    SQL> SELECT DEST_ID "ID",
                    2 STATUS "DB_status",
                    3 DESTINATION "Archive_dest",
                    4 ERROR "Error"
                    5 FROM V$ARCHIVE_DEST WHERE DEST_ID <=3;

                    ID DB_status Archive_dest Error
                    ---------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------
                    1 VALID (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx_vlan25.xxx.com)(PORT=1558)))(CONNECT_DATA=(SERVICE_NAME=rimfcsmw_XPT.xxxx.com)(INSTANCE_NAME=rimfcs)(SERVER=dedicated)))
                    2 INACTIVE
                    3 INACTIVE

                    thank you very much
                    • 7. Re: Last Applied Log is null in standby database
                      vlethakula
                      As per the v$dataguard_status

                      Remote File Server Error 26Nov12 09:49:40 Creating archive destination file : +RECOVER (102400 blocks)
                      Remote File Server Warning 26Nov12 09:49:40 RFS[29]: No standby redo logfiles of size 102400 blocks available

                      Looks like issue with standby redo log file size, what is your production redo logs size?

                      Edited by: vlethakula on Nov 26, 2012 2:57 PM
                      • 8. Re: Last Applied Log is null in standby database
                        vlethakula
                        And also log_archive_dest_2 is INACTIVE (Did you run this on primary or standby?)

                        on primary run the command

                        SQL> SELECT DEST_ID "ID",
                        2 STATUS "DB_status",
                        3 DESTINATION "Archive_dest",
                        4 ERROR "Error"
                        5 FROM V$ARCHIVE_DEST WHERE DEST_ID <=3;
                        • 9. Re: Last Applied Log is null in standby database
                          971970
                          --it is 50M each, in primary


                          select a.group#,a.member,b.status,b.archived,bytes/1024/1024 mbytes
                          from v$logfile a, v$log b
                          where a.group# = b.group#
                          order by 1,2;

                          GROUP# MEMBER STATUS ARC MBYTES
                          ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- --- ----------
                          1 /logfile1/rimfcs/redo1a.rdo INACTIVE YES 50
                          1 /logfile2/rimfcs/redo1b.rdo INACTIVE YES 50
                          2 /logfile1/rimfcs/redo2a.rdo CURRENT NO 50
                          2 /logfile2/rimfcs/redo2b.rdo CURRENT NO 50
                          3 /logfile1/rimfcs/redo3a.rdo INACTIVE YES 50
                          3 /logfile2/rimfcs/redo3b.rdo INACTIVE YES 50

                          --in standby

                          SYS@rimfcs> select distinct group#, type from v$logfile order by group#;

                          GROUP# TYPE
                          ---------- -------
                          1 ONLINE
                          2 ONLINE
                          3 ONLINE
                          4 STANDBY
                          5 STANDBY
                          6 STANDBY
                          7 STANDBY

                          --can I do this in standby?


                          whenever sqlerror continue;
                          alter database drop standby logfile group 4;
                          alter database drop standby logfile group 5;
                          alter database drop standby logfile group 6;
                          alter database drop standby logfile group 7;

                          alter database add standby logfile group 4 (
                          '/logfile1/rimfcs/srl_redo4a.rdo'
                          '/logfile2/rimfcs/srl_redo4b.rdo') size 50M;

                          alter database add standby logfile group 5 (
                          '/logfile1/rimfcs/srl_redo5a.rdo'
                          '/logfile2/rimfcs/srl_redo5b.rdo') size 50M;

                          alter database add standby logfile group 6 (
                          '/logfile1/rimfcs/srl_redo6a.rdo'
                          '/logfile2/rimfcs/srl_redo6b.rdo') size 50M;

                          alter database add standby logfile group 7 (
                          '/logfile1/rimfcs/srl_redo7a.rdo'
                          '/logfile2/rimfcs/srl_redo7b.rdo') size 50M;


                          thank you
                          • 10. Re: Last Applied Log is null in standby database
                            vlethakula
                            yes, you can do that

                            check:
                                 Alert.log shows No Standby Redo Logfiles Of Size 153600 Blocks Available [ID 405836.1]
                            • 11. Re: Last Applied Log is null in standby database
                              971970
                              I found the problem is

                              Errors in file /app/oracle/product/mfcs/10.2.0.4/admin/rimfcs/bdump/rimfcs_arc0_35389558.trc:
                              ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
                              ORA-17502: ksfdcre:4 Failed to create file +RECOVER
                              ORA-15041: diskgroup "RECOVER" space exhausted


                              --to fix this,
                              how to clean up in standby for free space?

                              (I try to run "backup archivelog all delete all input;" in rman"

                              rman target / catalog xxx@xxx
                              RMAN> register database;

                              RMAN-00571: ===========================================================
                              RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                              RMAN-00571: ===========================================================
                              RMAN-03002: failure of register command at 11/26/2012 17:41:12
                              RMAN-08040: full resync skipped, control file is not current or backup

                              )

                              Edited by: 968967 on Nov 26, 2012 3:45 PM
                              • 12. Re: Last Applied Log is null in standby database
                                sb92075
                                968967 wrote:
                                I found the problem is

                                Errors in file /app/oracle/product/mfcs/10.2.0.4/admin/rimfcs/bdump/rimfcs_arc0_35389558.trc:
                                ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
                                ORA-17502: ksfdcre:4 Failed to create file +RECOVER
                                ORA-15041: diskgroup "RECOVER" space exhausted
                                15041, 00000, "diskgroup \"%s\" space exhausted"
                                // *Cause:  The diskgroup ran out of space.
                                // *Action: Add more disks to the diskgroup, or delete some existing files.
                                //
                                • 13. Re: Last Applied Log is null in standby database
                                  Shivananda Rao
                                  Log Transport Services Error 26Nov12 08:21:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
                                  Log Transport Services Error 26Nov12 08:27:03 ARC0: Error 19504 Creating archive log file to '+RECOVER'
                                  ORA-15041: diskgroup "RECOVER" space exhausted
                                  Hello,

                                  On the standby database, you can delete the archives that have already been applied. I guess that the diskgroup "RECOVER" is the archive destination of the standby database. Do as:
                                  rman target sys/<pwd>@<standby-db>
                                  RMAN>delete archivelog all completed before 'SYSDATE-7';
                                  This deletes the archives past 7 days on the standby database.

                                  Also, please post the following details:

                                  From Primary database:
                                  show parameter log_archive_dest_2
                                  show parameter log_archive_dest_state_2
                                  From the standby database:
                                  show parameter log_archive_dest_1
                                  show parameter log_archive_dest_state_1