Oracle 10g Standby Database not opening readonly
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Oracle 10g Standby Database not opening readonly

  1. #1
    Join Date
    May 2008
    Posts
    12

    Exclamation Oracle 10g Standby Database not opening readonly

    Hiii,

    I have created Oracle 10g Primary database where control file locations are as follows:

    *control_files='P:\oracle\product\10.1.0\oradata\tceng\control01.ctl','C:\oradata\tceng\control02.ct l','E:\oradata\tceng\control03.ctl'

    Then I have created standby database in standby server where the control file locations are as follows:

    *control_files='C:\oracle\product\10.1.0\oradata\tceng\control01.ctl','D:\oradata\tceng\control02.ct l','D:\oradata\tceng\control03.ctl'

    Now if I give command 'alter system switch logfile;' no. of times in Primary server then check it in Standby by 'SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE', its showing the increasing SEQUENCE# properly.

    But if I want to open the standby database by the command 'alter database open read only;' then its showing error as

    SQL> alter database open read only;
    alter database open read only
    *
    ERROR at line 1:
    ORA-16004: backup database requires recovery
    ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
    ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'

    Can anyone tell me where I have made the mistake?

    Regards,

    nil

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Talking

    Maybe you are not using the magic word: "Please"?
    Kinda like:
    Code:
    SQL> alter database open read only; -- Please
    Or you may need to shutdown first and then startup mount and open read...
    .
    Last edited by LKBrwn_DBA; 05-21-2008 at 06:04 PM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2008
    Posts
    12
    Hii,

    I have tried all. But same error is coming. I think there is some trick.

    Can anybody please help.


    Regards,

    nil

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    does that file eeven exist on the standby server, i'm guessing not

  5. #5
    Join Date
    May 2008
    Posts
    12
    Yahh,

    The path of the file 'SYSTEM01.DBF' in standby server is 'C:\oracle\product\10.1.0\oradata\tceng'

    That is the error.B'coz in standby server it should search the file in the location mentioned above i.e. in C drive only.
    But as per error it is searching as in primary server i. e. in P drive.

    I have to define the path 'C:\oracle\product\10.1.0\oradata\tceng' for the file 'SYSTEM01.DBF' in standby server.

    Please go through the different location of control files in Primary and Standby server as

    *control_files='P:\oracle\product\10.1.0\oradata\tceng\control01.ctl','C:\oradata\tceng\control02.ct l','E:\oradata\tceng\control03.ctl' --- i.e. in P, C and E drives in Primary server

    *control_files='C:\oracle\product\10.1.0\oradata\tceng\control01.ctl','D:\oradata\tceng\control02.ct l','D:\oradata\tceng\control03.ctl' ---- i.e. in C and D drives in Standby server

    Please advice where to define the path properly??

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    have you even created the standby correctly

  7. #7
    Join Date
    May 2008
    Posts
    12
    If I give command 'alter system switch logfile;' no. of times in Primary server then check it in Standby by 'SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE', its showing the increasing SEQUENCE# properly.

    Please advice any other process to check standby server.

  8. #8
    Join Date
    May 2008
    Posts
    12
    I have done some test. Please go through it.

    In primary Server :

    Microsoft Windows [Version 5.2.3790]
    (C) Copyright 1985-2003 Microsoft Corp.

    C:\Documents and Settings\Administrator>set oracle_home=P:\oracle\product\10.1.0
    \db_1

    C:\Documents and Settings\Administrator>set oracle_sid=tceng

    C:\Documents and Settings\Administrator>sqlplus /nolog

    SQL*Plus: Release 10.1.0.2.0 - Production on Thu May 22 16:38:56 2008

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

    SQL> connect sys/infodba as sysdba
    Connected to an idle instance.
    SQL> startup;
    ORACLE instance started.

    Total System Global Area 612368384 bytes
    Fixed Size 790096 bytes
    Variable Size 170914224 bytes
    Database Buffers 440401920 bytes
    Redo Buffers 262144 bytes
    Database mounted.
    Database opened.
    SQL> alter system switch logfile;

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system archive log cuurent;
    alter system archive log cuurent
    *
    ERROR at line 1:
    ORA-01974: Illegal archive option


    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

    System altered.

    SQL>


    In Standby server :

    Microsoft Windows [Version 5.2.3790]
    (C) Copyright 1985-2003 Microsoft Corp.

    C:\Documents and Settings\Administrator>set oracle_home=C:\oracle\product\10.1.0\db_1

    C:\Documents and Settings\Administrator>set oracle_sid=stand

    C:\Documents and Settings\Administrator>sqlplus /nolog

    SQL*Plus: Release 10.1.0.2.0 - Production on Thu May 22 16:40:47 2008

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

    SQL> connect sys/infodba as sysdba
    Connected to an idle instance.
    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area 612368384 bytes
    Fixed Size 790096 bytes
    Variable Size 170652080 bytes
    Database Buffers 440401920 bytes
    Redo Buffers 524288 bytes
    SQL> alter database mount standby database;

    Database altered.

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

    Database altered.

    SQL> recover managed standby database cancel;
    ORA-16136: Managed Standby Recovery not active


    SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

    SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
    67 20-MAY-08 20-MAY-08
    68 20-MAY-08 20-MAY-08
    69 20-MAY-08 20-MAY-08
    70 20-MAY-08 20-MAY-08
    71 20-MAY-08 20-MAY-08
    72 20-MAY-08 20-MAY-08
    73 20-MAY-08 20-MAY-08
    74 20-MAY-08 20-MAY-08
    75 20-MAY-08 20-MAY-08
    76 20-MAY-08 21-MAY-08
    77 21-MAY-08 21-MAY-08

    SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
    78 21-MAY-08 21-MAY-08

    12 rows selected.

    SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

    SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
    67 20-MAY-08 20-MAY-08
    68 20-MAY-08 20-MAY-08
    69 20-MAY-08 20-MAY-08
    70 20-MAY-08 20-MAY-08
    71 20-MAY-08 20-MAY-08
    72 20-MAY-08 20-MAY-08
    73 20-MAY-08 20-MAY-08
    74 20-MAY-08 20-MAY-08
    75 20-MAY-08 20-MAY-08
    76 20-MAY-08 21-MAY-08
    77 21-MAY-08 21-MAY-08

    SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
    78 21-MAY-08 21-MAY-08

    12 rows selected.

    SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

    SEQUENCE# APP
    ---------- ---
    67 NO
    68 NO
    69 NO
    70 NO
    71 NO
    72 NO
    73 NO
    74 NO
    75 NO
    76 NO
    77 NO

    SEQUENCE# APP
    ---------- ---
    78 NO

    12 rows selected.

    SQL>


    Please suggest.

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    none of the arhives are aplied - im guessing you havent create dyour standby correctly as nothing appears to be working

  10. #10
    Join Date
    May 2008
    Posts
    12
    Hii,

    Thanks for the reply.

    Yahh.Archives are not applied.

    When I create standby database in such manner that control files in Primary server are :

    *control_files='P:\oracle\product\10.1.0\oradata\tceng\control01.ctl','C:\oradata\tceng\control02.ct l','E:\oradata\tceng\control03.ctl' --- i.e. in P, C and E drives in Primary server

    and control files in Standby server are :

    *control_files='P:\oracle\product\10.1.0\oradata\tceng\control01.ctl','C:\oradata\tceng\control02.ct l','E:\oradata\tceng\control03.ctl' --- i.e. in P, C and E drives in Primary server

    i.e path are same then its working fine.

    But when we give path of control files in different locations in Primary and Standby servers i.e. P, C and E drives in Primary server and C and D drives in Standby server then only the error occurs.

    My query is, should we make any change in CLI method for this special case for creation of standby??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width