8 Replies Latest reply: Jan 28, 2013 3:41 AM by 985892 RSS

    ORA-10458: standby database requires recovery

    985892
      hi

      kindly help me... i got error when standby open in read only mode

      SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 19 14:37:46 2013

      Copyright (c) 1982, 2011, Oracle. All rights reserved.

      Connected to an idle instance.
      SQL> startup mount
      ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
      ORACLE instance started.

      Total System Global Area 5344731136 bytes
      Fixed Size 2237776 bytes
      Variable Size 1275071152 bytes
      Database Buffers 4060086272 bytes
      Redo Buffers 7335936 bytes
      Database mounted.
      SQL> Alter Database Recover Managed Standby Database Disconnect;

      Database altered.

      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

      Database altered.

      SQL> ALTER DATABASE OPEN READ ONLY;
      ALTER DATABASE OPEN READ ONLY
      *
      ERROR at line 1:
      ORA-10458: standby database requires recovery
      ORA-01152: file 22 was not restored from a sufficiently old backup
      ORA-01110: data file 22: '/archive/data/undo01.dbf'
        • 2. Re: ORA-10458: standby database requires recovery
          Victor Armbrust
          You should verify if your standby is fullly syncronized with primary.
          you can do that by verifying your alert.log (all sequences must be applied) and also using v$log
          select thread#, max(sequence#) from v$log group by thread#;
          Victor
          • 3. Re: ORA-10458: standby database requires recovery
            985892
            hi Osama_mustafa

            I executed the action plan as you recommend and got below information,

            SQL> select sequence#,applied from v$archived_log;

            SEQUENCE# APPLIED

            --------------------------------------------------------------------------------
            ---------
            45019 YES
            45020 YES
            45021 YES
            45022 YES
            45023 YES
            45024 YES
            45025 YES
            45026 YES
            45027 NO


            SQL> select to_char(CURRENT_SCN) CURRENT_SCN FROM V$DATABASE;

            CURRENT_SCN



            --------------------------------------------------------------------------------
            5983485554579

            SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

            Database altered.

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS

            --------------------------------------------------------------------------------
            NOT ALLOWED

            SQL> recover standby database;
            ORA-00279: change 5983485554580 generated at 01/01/2013 19:27:17 needed for thread 1
            ORA-00289: suggestion : /archive/PROD_1_750568044_45027.ARC
            ORA-00280: change 5983485554580 for thread 1 is in sequence #45027

            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
            AUTO
            ORA-00308: cannot open archived log '/archive/PROD_1_750568044_45027.ARC'
            ORA-27037: unable to obtain file status
            Linux-x86_64 Error: 2: No such file or directory
            Additional information: 3


            ORA-00308: cannot open archived log '/archive/PROD_1_750568044_45027.ARC'
            ORA-27037: unable to obtain file status
            Linux-x86_64 Error: 2: No such file or directory
            Additional information: 3


            ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
            ORA-01152: file 22 was not restored from a sufficiently old backup
            ORA-01110: data file 22: '/archive/data/undo01.dbf'

            Note. I don't have archived log '/archive/PROD_1_750568044_45027.ARC'
            • 4. Re: ORA-10458: standby database requires recovery
              CKPT
              Hello,

              Can you start apply 2-3 archives , Then cancel the MRP and later try checking whether you able to open or not.
              if you dont have any archives to be applied, recommended to perform some log switches and wait until it completely applied.
              • 5. Re: ORA-10458: standby database requires recovery
                985892
                hi CKPT
                I executed the action plan as you recommend and got below information;

                SQL> recover standby database;
                ORA-00279: change 5983485554580 generated at 01/01/2013 19:27:17 needed for
                thread 1
                ORA-00289: suggestion : /archive/PROD_1_750568044_45027.ARC
                ORA-00280: change 5983485554580 for thread 1 is in sequence #45027


                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                '/archive/PROD_1_750568044_45400.ARC'
                ORA-00310: archived log contains sequence 45400; sequence 45027 required
                ORA-00334: archived log: '/archive/PROD_1_750568044_45400.ARC'


                ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                ORA-01152: file 22 was not restored from a sufficiently old backup
                ORA-01110: data file 22: '/archive/data/undo01.dbf'


                SQL> alter database open resetlogs;
                alter database open resetlogs
                *
                ERROR at line 1:
                ORA-01666: control file is for a standby database


                SQL> alter system switch logfile;
                alter system switch logfile
                *
                ERROR at line 1:
                ORA-01109: database not open
                ---------------------------------- and some other activity but still not found any solution-------------
                RMAN> recover database until sequence
                2> 45026;

                Starting recover at 23-JAN-13
                using channel ORA_DISK_1
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of recover command at 01/23/2013 16:42:47
                RMAN-06556: datafile 1 must be restored from backup older than SCN 5983484595990

                RMAN> recover database until sequence 45027;

                Starting recover at 23-JAN-13
                using channel ORA_DISK_1
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of recover command at 01/23/2013 16:42:59
                RMAN-06556: datafile 22 must be restored from backup older than SCN 5983485554580

                RMAN> recover database until sequence 45025;

                Starting recover at 23-JAN-13
                using channel ORA_DISK_1
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of recover command at 01/23/2013 16:43:09
                RMAN-06556: datafile 1 must be restored from backup older than SCN 5983484160276

                RMAN> alter database open resetlogs;

                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of alter db command at 01/23/2013 16:43:25
                ORA-01666: control file is for a standby database
                • 6. Re: ORA-10458: standby database requires recovery
                  saurabh
                  You are using wrong command to open your database. You should not use " ALTER DATABASE OPEN RESETLOGS" instead of it you should use "ALTER DATABASE ACTIVATE STANDBY DATABASE" this will open your standby database in read write mode and using resetlogs.
                  • 7. Re: ORA-10458: standby database requires recovery
                    CKPT
                    ORA-01109: database not open
                    Why you are trying to logswitch? it is applicable only on Primary database not an Physical standby database.
                    ORA-01666: control file is for a standby database
                    Why you trying to do open resetlogs? it is not applicable either. Because by default when recovery failure then Oracle give alternate options which will work out. But you are in Physical standby so please ignore.
                    ORA-01666: control file is for a standby database
                    Where you trying to perform recovery, Check it is standby or primary?

                    To start MRP, Simply you can use below command instead of applying manually as
                    SQL> alter database recover managed standby database disconnect from session;


                    You are doing completely wrong which supposed not to do. So start recovery as above and monitor all the archives been transferred and applied on standby database.
                    If it is not synchronized then use below script and post in coded format from primary and standby databases.

                    http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
                    • 8. Re: ORA-10458: standby database requires recovery
                      985892
                      thank you all.

                      I found solution Doc ID 733089.1 on oracle support.

                      solution : Standby datafiles and control file needs to be sync for opening read only.