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

Maybe you are not using the magic word: "Please"? :rolleyes:
Kinda like:

Code:

---

SQL> alter database open read only; -- Please

---

Or you may need to shutdown first and then startup mount and open read...;)
.

Hii,

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

Can anybody please help.


Regards,

nil

does that file eeven exist on the standby server, i'm guessing not

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??

have you even created the standby correctly

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.

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.

none of the arhives are aplied - im guessing you havent create dyour standby correctly as nothing appears to be working

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??

look into db_filename_convert