11 Replies Latest reply: Mar 20, 2012 11:28 AM by CKPT RSS

    How to apply missing arching logs in a logical stand by database

    User446051-Oracle
      hi,

      I have created logical stand by data base for our production.It was working fine.We have not set the value for standby_archvie_dest and it send the archive files to $ORACLE_HOME/dbs, due to high volume transavtion many files generated and $ORACLE_HOME mount got filled and logical stan by apply stopped working and Db went down as well.

      I tried to apply the files once i bought back the instance but after applying one archivie file it stopped applying further.
      and logical stand by not working appropriately.

      Please let me know that is there is a mechanism to apply the missing logs?

      DB version : 10.2.0.5
      OS :OEL 5

      regards
      Manoj
        • 1. Re: How to apply missing arching logs in a logical stand by database
          CKPT
          I have created logical stand by data base for our production.It was working fine.We have not set the value for standby_archvie_dest and it send the archive files to $ORACLE_HOME/dbs, due to high volume transavtion many files generated and $ORACLE_HOME mount got filled and logical stan by apply stopped working and Db went down as well.

          I tried to apply the files once i bought back the instance but after applying one archivie file it stopped applying further.
          and logical stand by not working appropriately.

          Please let me know that is there is a mechanism to apply the missing logs?
          Manoj,

          post

          SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
          SQL> COLUMN STATUS FORMAT A60
          SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;
          SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
          SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM D BA_LOGSTDBY_PROGRESS;
          • 2. Re: How to apply missing arching logs in a logical stand by database
            User446051-Oracle
            Hi,

            Since then the issue happened I have noticed archives are not shipping.


            Following are the outputs

            SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

            Session altered.

            SQL> COLUMN STATUS FORMAT A60
            SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;

            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            18-MAR-12 11:11:35 ORA-16111: log mining and apply setting up


            18-MAR-12 22:34:26 ORA-16226: DDL skipped due to lack of support
            alter database begin backup

            18-MAR-12 22:34:26 ORA-16226: DDL skipped due to lack of support
            alter database end backup


            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            18-MAR-12 22:49:25 ORA-16226: DDL skipped due to lack of support
            alter database backup controlfile to '/tmp/PCEGYK_control.ctl'

            18-MAR-12 22:49:25 ORA-16226: DDL skipped due to lack of support
            alter database backup controlfile to trace

            18-MAR-12 22:49:25 ORA-16226: DDL skipped due to lack of support
            create pfile='/pcegyk/backup/hot_backups/18032012_2234/initPCEGYK.ora_from_spfil


            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            19-MAR-12 00:04:40 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218894 to "SYSADM"

            19-MAR-12 00:04:41 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218895 to "SYSADM"

            19-MAR-12 00:04:41 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218896 to "SYSADM"


            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            19-MAR-12 00:04:41 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218897 to "SYSADM"

            19-MAR-12 00:04:41 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218898 to "SYSADM"

            19-MAR-12 00:04:41 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218899 to "SYSADM"


            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            19-MAR-12 00:19:26 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218900 to "SYSADM"

            19-MAR-12 00:19:26 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218901 to "SYSADM"

            19-MAR-12 00:19:26 ORA-16227: DDL skipped due to missing object
            grant select,insert on sys.ora_temp_1_ds_218902 to "SYSADM"


            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            20-MAR-12 03:28:09 ORA-16111: log mining and apply setting up


            20-MAR-12 03:31:54 ORA-16128: User initiated stop apply successfully completed


            20-MAR-12 03:55:13 ORA-16111: log mining and apply setting up



            EVENT_TIME STATUS
            ------------------ ------------------------------------------------------------
            EVENT
            --------------------------------------------------------------------------------
            20-MAR-12 04:17:38 ORA-16128: User initiated stop apply successfully completed


            20-MAR-12 04:17:54 ORA-16111: log mining and apply setting up


            20-MAR-12 21:20:20 ORA-16111: log mining and apply setting up



            21 rows selected.

            SQL>
            ===========================
            SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

            FILE_NAME
            --------------------------------------------------------------------------------
            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
            ---------- ------------- ------------ ------------------ --- --- ----------
            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138743_679263487.arc
            138743 7.4580E+12 7.4580E+12 19-MAR-12 06:33:16 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138744_679263487.arc
            138744 7.4580E+12 7.4580E+12 19-MAR-12 06:36:22 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138745_679263487.arc
            138745 7.4580E+12 7.4580E+12 19-MAR-12 06:39:21 NO NO 1


            FILE_NAME
            --------------------------------------------------------------------------------
            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
            ---------- ------------- ------------ ------------------ --- --- ----------
            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138746_679263487.arc
            138746 7.4580E+12 7.4580E+12 19-MAR-12 06:41:25 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138747_679263487.arc
            138747 7.4580E+12 7.4580E+12 19-MAR-12 06:43:24 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138748_679263487.arc
            138748 7.4580E+12 7.4580E+12 19-MAR-12 06:45:21 NO NO 1


            FILE_NAME
            --------------------------------------------------------------------------------
            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
            ---------- ------------- ------------ ------------------ --- --- ----------
            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138749_679263487.arc
            138749 7.4580E+12 7.4580E+12 19-MAR-12 06:48:07 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138750_679263487.arc
            138750 7.4580E+12 7.4580E+12 19-MAR-12 06:50:19 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138751_679263487.arc
            138751 7.4580E+12 7.4580E+12 19-MAR-12 06:52:52 NO NO 1


            FILE_NAME
            --------------------------------------------------------------------------------
            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
            ---------- ------------- ------------ ------------------ --- --- ----------
            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138752_679263487.arc
            138752 7.4580E+12 7.4580E+12 19-MAR-12 06:55:32 NO NO 1

            /pcegyk/oracle/product/102/dbs/archPCEGYK_1_138805_679263487.arc
            138805 7.4580E+12 7.4580E+12 19-MAR-12 15:33:26 NO NO 1

            =================

            SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

            APPLIED_SCN NEWEST_SCN
            ----------- ----------
            7.4580E+12 7.4580E+12
            • 3. Re: How to apply missing arching logs in a logical stand by database
              CKPT
              >
              SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

              APPLIED_SCN NEWEST_SCN
              ----------
              7.4580E+12 7.4580E+12
              >

              I didnt see any Differences here...
              How can you say missing archive logs? can you post here ?

              sql> SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;
              • 4. Re: How to apply missing arching logs in a logical stand by database
                User446051-Oracle
                HI,

                As i told u earlier, due to space issue on mount there were many errors throwing in alert log, and server got rebooted .Then we made required space on the mount and around 130 + archives moved to stand by and tried applying by issuing the command "ALTER DATABASE START LOGICAL STAND BY APPLY " but it applied only one file and stopped....looks like primary to stand by sync has lost...How I can overcome this...
                • 5. Re: How to apply missing arching logs in a logical stand by database
                  CKPT
                  user446051 wrote:
                  HI,

                  As i told u earlier, due to space issue on mount there were many errors throwing in alert log, and server got rebooted .Then we made required space on the mount and around 130 + archives moved to stand by and tried applying by issuing the command "ALTER DATABASE START LOGICAL STAND BY APPLY " but it applied only one file and stopped....looks like primary to stand by sync has lost...How I can overcome this...
                  Hello,

                  Recovering archive gaps, Is similar like physical standby AFAIK for sure.
                  Have you moved all of the archives to standby?

                  post from primary
                  SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

                  Post from standby
                  SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
                  SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

                  Check what is the value of the second query and the third query, so thats the gap you having.. Now recover with all those log files.
                  Thanks.
                  • 6. Re: How to apply missing arching logs in a logical stand by database
                    User446051-Oracle
                    Primary o/p
                    ===========


                    SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

                    THREAD# MAX(SEQUENCE#)
                    ---------- --------------
                    1 139013

                    SQL>


                    Stand by Out o/p
                    ================
                    SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

                    THREAD# MAX(SEQUENCE#)
                    ---------- --------------
                    1 138646

                    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

                    THREAD# MAX(SEQUENCE#)
                    ---------- --------------
                    1 138645


                    I have copied manually to stand by. I will try to recover as normal physical stan dby. once that is succesfull do I need to issue again command for converting to logical stand by ?

                    Thanks for your assistance
                    • 7. Re: How to apply missing arching logs in a logical stand by database
                      CKPT
                      There is *367* archive gaps between primary & standby.
                      Can you copy those all archives from sequence# 138646 to 139013 ? then perform logical standby apply

                      (or)

                      I'm not proficient at Logical standby, Actually if any such gaps in physical standby used to do incremental rollforward using RMAN , which is mentioned in my Blog.
                      http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

                      May be this is applicable on logical standby also, but i never tested. You can have a try..
                      • 8. Re: How to apply missing arching logs in a logical stand by database
                        User446051-Oracle
                        Thanks so much...Your blog is really wonderfull.

                        Since I opened the db with reset log option if i try to recovery it will show error message as follows.



                        SQL> recover standby database until cancel;
                        ORA-00283: recovery session canceled due to errors
                        ORA-01665: control file is not a standby control file


                        Let me try for applying the missing logs in logical stand by scenario.I will definitely post the results. Sicne production is pretty huge dn again taking a backup and shipping to standby is
                        a painful process(basically in terms of customer Change request procedure)

                        If I get a chance I would follow ur blog..Thanks once again.
                        • 9. Re: How to apply missing arching logs in a logical stand by database
                          CKPT
                          Thanks so much...Your blog is really wonderfull.
                          Thanks a Lot :)
                          Since I opened the db with reset log option if i try to recovery it will show error message as follows.
                          SQL> recover standby database until cancel;
                          ORA-00283: recovery session canceled due to errors
                          ORA-01665: control file is not a standby control file
                          Why you have opened? is this flashback enabled? Now its no more standby .. Not sure what steps you followed..

                          Let me try for applying the missing logs in logical stand by scenario.I will definitely post the results. Sicne production is pretty huge dn again taking a backup and shipping to standby is
                          a painful process(basically in terms of customer Change request procedure)
                          All the best, if your standby is still safe.. but you mentioning opened resetlogs..
                          If I get a chance I would follow ur blog..Thanks once again.
                          Sure.. I will update more :)
                          • 10. Re: How to apply missing arching logs in a logical stand by database
                            User446051-Oracle
                            Probably you are aware logical stand by u can open on reset logs followed by "alter database logical stand by apply " and once done using the log mining it will keep on applying the archives.That is the reason I would not be in positional to go back to "local stand by recover" since the control file got updated.
                            • 11. Re: How to apply missing arching logs in a logical stand by database
                              CKPT
                              user446051 wrote:
                              Probably you are aware logical stand by u can open on reset logs followed by "alter database logical stand by apply " and once done using the log mining it will keep on applying the archives.That is the reason I would not be in positional to go back to "local stand by recover" since the control file got updated.
                              No.. Thats why i have mentioned that i'm not proficient ;-)
                              Check rollforward, it may work or apply manually. :)