This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 4, 2013 6:02 AM by 981760 RSS

Physical Standby Database not opening in Read Only Mode

981760 Newbie
Currently Being Moderated
Hello All,

I have created the physical standby database on a different server by transferring all the archivelogs and datafiles and all the directories from the primary server.

Dirctory structure is same between the primary and standby database.

I used standby control file to mount the standby database and also pfile from the primary database. I did all the changes required in the pfile from the primary database.

Below is the link I followed to create my phyical standby database.

http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php


Now, the standby and primary databases are in sync. Please see below.

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

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

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

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


Standby .

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

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

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

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


Also, I can see the archive logs transferring in the respective directory from the primary to the standby as soon as I do a log switch over.

Issue, I am facing is , I am not able to open the standby database in READ ONLY Mode. When ever I try to open it in read only mode, alert log file shows the below message.

"Media Recovery Waiting for thread 1 sequence 367 (in transit)"

When I see in the primary database, this sequence number is not archived and its a current log file. When I tried archiving this one, my standby also gets the archive file but the alert log jumps into next current sequence number from the primary and again it starts waiting on the new ones. Is this expected behaviour in Dataguard ?

BUt I understand it should allow me to open the database in read only mode.

Database version i am using is 11.2.0.3. I am not understanding why the standby database is waiting on the current logfile from the primary which is not archived in the primary itself ?

However, this is the procedure I am following.

SQLPLUS > startup mount

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

SQL > alter database open read only


AFter this it just hangs and when I kill this process it shows me the below error.
SQL> alter database open read only;

ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/usr/local/oracle/data/tvapa10/mcada/system01.dbf'


Any help is greatly appreciated and let me know if any other information is needed.

Thanks,
  • 1. Re: Physical Standby Database not opening in Read Only Mode
    mseberg Guru
    Currently Being Moderated
    Hello;

    Can you post the COMPATIBLE parameter for both databases?

    From my test system
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    
    Database altered.
    
    SQL> alter database open read only ;
    
    Database altered.
    
    SQL> 
    Also can you confirm this file exists on the Standby side.

    /usr/local/oracle/data/tvapa10/mcada/system01.dbf


    Best Regards

    mseberg

    Edited by: mseberg on Feb 28, 2013 9:10 AM
  • 2. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    Thanks for the reply .

    Here it is,

    Primary database,

    SQL> show parameter compatible;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    compatible string 11.2.0.3.0
    SQL>


    Standby,

    SQL> show parameter compatible

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    compatible string 11.2.0.3.0
    SQL>
  • 3. Re: Physical Standby Database not opening in Read Only Mode
    mseberg Guru
    Currently Being Moderated
    Hello again;

    They match! So much for the simple fix. I would double check all the datafiles on the Standby side and the check the standby alert log for additional errors or clues.

    If you find them, please post.

    Best Regards

    mseberg
  • 4. Re: Physical Standby Database not opening in Read Only Mode
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    What happens when you shutdown the standby database and then try to open it directly ? If you are permitted to do so, then
    1. Make sure that the standby is in sync with primary.
    2. Cancel the MRP on standby and shut it down.
    3. Standby: Just give startup
    4. Later start the MRP on it
    Regards,
    Shivananda
  • 5. Re: Physical Standby Database not opening in Read Only Mode
    CKPT Guru
    Currently Being Moderated
    If your standby is in Sync with primary database, Then you must able to open database after cancel the recovery.
    But ensure you haven't terminated MRP process unexpectedly.
    If you see, Both primary and standby is in sync. I suggest you to perform couple of log switch. Let them apply completely and then cancel MRP.
    These all steps so far up to mount status, Now open database.

    If you have still issues, post here results. Before that please update have you performed any restoration of missing/new data files or any changes?
    and have you ever opened database earlier?
  • 6. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    Ok. I have checked my datafiles .Infact I trasnferred all my datafiles again from primary to standby. But still it does not allow me to open the standby database.


    I also tried the method Shivanand Sir was talking about and when i startup it just sits on this below command in the alert and never actually completes opening the database.
    Media Recovery of Online Log [Thread=1, Seq=380]
    Recovery of Online Redo Log: Thread 1 Group 5 Seq 380 Reading mem 0
    Mem# 0: /usr/local/oracle/data/tvapa10/mcada/redo/redo2.log
    Thu Feb 28 11:00:50 2013
    NSV0 started with pid=58, OS id=12584
    Thu Feb 28 11:00:54 2013
    RSM0 started with pid=59, OS id=13616
    ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='mcada';
    ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' SCOPE=SPFILE SID='mcada';
    Data Guard: Redo Apply Services will be started after instance open completes


    In between, I dont see any errors in the alert log files which is driving me crazy, and the logs are in synchronised between my primary and standby. I am really going blank on this one. One more thing if I do switch over of the database then the standby database opens with no problem.
    I am trying to understand why is it waiting on the current logfile from my primary which is not archived in the primary itself ? and just shows me in the alert log files this message.

    Media Recovery Waiting for thread 1 sequence 380 (in transit)

    And this sequence number changes when I do log switch in my primary which makes me feel that both are in sync but some how not able to open the database because of this current log files, I also tried recovering the database which also compalins about missing archive log which infact is never present in the primary itself.
  • 7. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    I tried transferring of the datafiles from the primary to standby but I never was able to open this standby database.
  • 8. Re: Physical Standby Database not opening in Read Only Mode
    mseberg Guru
    Currently Being Moderated
    Hello;

    With all due respect I have some doubt about this statement :
    and the logs are in synchronised between my primary and standby
    Could you confirm this by running this SQL from your primary :

    http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

    And posting the results.

    I think you may transfer logs, but I have some doubt they are applying.

    Best Regards

    mseberg
  • 9. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    No Problem Sir,

    Please find the results below.

    DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    CADA SHAPAN1 382 382 28-FEB/11:53 0
  • 10. Re: Physical Standby Database not opening in Read Only Mode
    mseberg Guru
    Currently Being Moderated
    OK.

    I believe. Frankly I'm not sure what to tell you.

    h3. Later

    This note offers some hope.



    Unable To open Standby Database READ ONLY after Creation [ID 733089.1]

    Cannot say I like the solution much.


    Best Regards

    mseberg

    Edited by: mseberg on Feb 28, 2013 11:16 AM
  • 11. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    I will try to get some questions answered so that I trouble shoot more by understanding it.

    Tell me about this,

    Primary database is writing all the changes to the redo.dbf files and I can see it up to date in my directories.

    Same redo.dbf files are not getting updated in my standby database but they are getting updated in my standby log file directories which I created while creating standby database and with the extension of redo.log.


    For example, I can see redo.dbf in primary showing me the uptodate time in the directory and same uptodate time in my standby log files in standby database.

    Is this the expected behaviour ? Also, my primary database is not using the standby log files , its just using the redo.dbf files.

    I am pretty sure I have something messed up in the redo or standby logfiles so trying to go in that direction.
  • 12. Re: Physical Standby Database not opening in Read Only Mode
    mseberg Guru
    Currently Being Moderated
    Yes.

    The simple answer is a database in Standby mode does not use Redo, it uses standby redo logs.

    A database in Primary mode does not use standby redo logs, it only uses redo.

    However you want them both available for switchover or failover.

    You saw the MOS note in my prior post right?

    Best Regards

    mseberg

    Edited by: mseberg on Feb 28, 2013 11:49 AM
  • 13. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    Thank you . That makes sense.

    I will try to trouble shoot more and update it when I find something.
  • 14. Re: Physical Standby Database not opening in Read Only Mode
    981760 Newbie
    Currently Being Moderated
    Hello All,

    I resolved my issue.

    Here is the thing,

    I DID NOT Create online log files in my Primary database .

    I learnt about this when I did switch over and when I could open my new standby database in read only mode.

    Then I noticed that all the current transactions has been written to online log files.

    But in my earlier setup , since I did not had online log files, Primary database was writing changes to the REDO.DBF files ,But I am going to assume, since it was writing to the REDO.DBF , my standby database was not receving the current files unless it was archived ? and that might be the reason why it was asking me the current logfiles archive log ? I appreciate if any one of you confirm this concept for me

    But as soon as I created my online log files, primary database started writing transactions to the online log files and I could open the standby database as read only.

    So, in simple words , we need the below things

    1) Online Redo log files on both primary and standby
    2) StandBy REDO log files on both primary and standby .

    I say on both primary and standby because you are going to need it in primary if you switchover and ofcourse thats the main feature of Dataguard

    Thanks a lot everyone for helping out here. This is my first ever OTN Discussion and I cant say it in words that how much great work you guys are doing by replying to the forum. YOu guys have inspired me and I will also try to visit forum often and help with what I can .


    Thanks a lot again . Please anyone confirm if my understanding was right regarding the online and Standby REdo log files.
1 2 Previous Next

Legend

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