This discussion is archived
8 Replies Latest reply: Jan 28, 2013 1:41 AM by 985892 RSS

ORA-10458: standby database requires recovery

985892 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points