1 2 Previous Next 15 Replies Latest reply: Oct 17, 2012 5:51 AM by 744052 RSS

    Unable to open standby database

    744052
      Hi,

      I am using Oracle 10g Database on Lunix and have a physical standby database.

      I have been checking on the primary it shows

      From Primary

      SQL> archive log list;
      Database log mode              Archive Mode
      Automatic archival             Enabled
      Archive destination            /database/oraapps/PROD/db/tech_st/10.2.0/dbs/arch
      Oldest online log sequence     568
      Next log sequence to archive   569
      Current log sequence           569


      From Standby


      SQL> archive log list
      Database log mode              Archive Mode
      Automatic archival             Enabled
      Archive destination            /database/oraapps/PROD/db/tech_st/10.2.0/dbs/arch
      Oldest online log sequence     568
      Next log sequence to archive   0
      Current log sequence           569

      Whenever I execute

      alter system switch logfile it is getting changed, but when I try to open the standby database I am getting the error as below;

      SQL>  ALTER DATABASE OPEN;
      ALTER DATABASE OPEN
      *+
      ERROR at line 1:
      ORA-16004: backup database requires recovery
      ORA-01152: file 1 was not restored from a sufficiently old backup
      ORA-01110: data file 1: '/database/oraapps/PROD/db/apps_st/dddd/system01.dbf'

      Please advice.

      Thank you.

      Edited by: user8676543 on Oct 16, 2012 3:55 AM
        • 1. Re: Unable to open standby database
          Niket Kumar
          you cannot open standby database .it will be in mount stage.

          Edited by: Niket Kumar on Oct 16, 2012 4:01 AM
          • 2. Re: Unable to open standby database
            Osama_Mustafa
            if you need to switch from primary to stand by
            SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
            This error mean on standby that you created the standby controlfile before you took the backup of the Primary.recreate the standby database.
            • 3. Re: Unable to open standby database
              744052
              What do you mean by recreate the standby database? Just need to recreate the control files again from primary? Please explain
              • 4. Re: Unable to open standby database
                JohnWatson
                alter database recover managed standby database cancel;
                alter database open;
                • 5. Re: Unable to open standby database
                  Helios-GunesEROL
                  Hi;

                  1. Is there any error message at alert.log?

                  If you are on 11gR2 level than you can open your db as read only mode.

                  Regard
                  Helios
                  • 6. Re: Unable to open standby database
                    Osama_Mustafa
                    Try
                     SQL> RECOVER STANDBY DATABASE;
                    • 7. Re: Unable to open standby database
                      744052
                      Hi,

                      No there is no errors in the alert log file.

                      From the standby database

                      SQL> select thread#, sequence#, applied,
                      +2 to_char(first_time,'mm/dd/yy hh24:mi:ss') first,+
                      +3 to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,+
                      +4 to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion+
                      +5 from v$archived_log order by first_time;+

                      THREAD#  SEQUENCE# APP FIRST             NEXT              COMPLETION
                      ---------- ---------- --- ----------------- ----------------- -----------------
                      +1 569 NO 10/16/12 15:45:52 10/16/12 16:10:51 10/16/12 16:22:46+
                      +1 570 NO 10/16/12 16:10:51 10/16/12 16:18:52 10/16/12 16:22:46+
                      +1 571 NO 10/16/12 16:18:52 10/16/12 16:19:02 10/16/12 16:22:46+
                      • 8. Re: Unable to open standby database
                        Shivananda Rao
                        From the primary and standby database please post the outcome of the below query


                        Primary:
                        sql>select max(sequence#) from v$archived_log;
                        Standby:
                        sql>select max(sequence#) from v$archived_log where applied='YES';
                        sql>select process,status,sequence# from v$managed_standby;
                        • 9. Re: Unable to open standby database
                          744052
                          From the primary

                          SQL> select max(sequence#) from v$archived_log;

                          MAX(SEQUENCE#)
                          --------------
                          +571+

                          For the standby

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

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


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

                          PROCESS   STATUS        SEQUENCE#
                          --------- ------------ ----------
                          ARCH      CONNECTED             0
                          ARCH      CONNECTED             0
                          RFS       IDLE                  0
                          • 10. Re: Unable to open standby database
                            Osama_Mustafa
                            Did you try to recover standby by command i posted
                            • 11. Re: Unable to open standby database
                              Shivananda Rao
                              user8676543 wrote:
                              From the primary

                              SQL> select max(sequence#) from v$archived_log;

                              MAX(SEQUENCE#)
                              --------------
                              +571+

                              For the standby

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

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


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

                              PROCESS   STATUS        SEQUENCE#
                              --------- ------------ ----------
                              ARCH      CONNECTED             0
                              ARCH      CONNECTED             0
                              RFS       IDLE                  0
                              Your standby database has not applied even a single log and that MRP process is not started on your standby database which is why you are facing the error. Please start the MRP process on your standby database using this query:
                              sql>alter database recover managed standby database disconnect from session;
                              Wait for few minutes and post the outcome of the queries that I posted earlier in order to check if the logs are being applied on the standby or not.
                              • 12. Re: Unable to open standby database
                                744052
                                Once I execute the command as mentioned I get errors as below

                                Tue Oct 16 16:46:14 2012
                                Errors in file /database/oraapps/PROD/db/tech_st/10.2.0/admin/PROD_test/bdump/prod_mrp0_11412.trc:
                                ORA-01110: data file 1: '/database/oraapps/PROD/db/apps_st/data/system01.dbf'
                                ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
                                ORA-01110: data file 1: '/database/oraapps/PROD/db/apps_st/data/system01.dbf'
                                • 13. Re: Unable to open standby database
                                  Osama_Mustafa
                                  recreate your standby again , you did something wrong while creation .

                                  http://oracleinstance.blogspot.com/2010/01/step-by-step-document-for-creating.html
                                  • 14. Re: Unable to open standby database
                                    Shivananda Rao
                                    You need to recover your standby database. You can use the roll forward method to get your standby database in sync with primary. You can refer this http://shivanandarao.wordpress.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/

                                    If your database size is small enough, then I would suggest you to rebuild the standby.
                                    1 2 Previous Next