-
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"?
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 05:04 PM.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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??
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|