This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 17, 2012 3:51 AM by 744052 RSS

Unable to open standby database

744052 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    alter database recover managed standby database cancel;
    alter database open;
  • 5. Re: Unable to open standby database
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Try
     SQL> RECOVER STANDBY DATABASE;
  • 7. Re: Unable to open standby database
    744052 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Did you try to recover standby by command i posted
  • 11. Re: Unable to open standby database
    Shivananda Rao Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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