Home » Server Options » Data Guard » Cannot Open Physical Standby Read Only (Oracle 9i EE 9.2.0.1.0 AIX 5.2)
Cannot Open Physical Standby Read Only [message #485334] Tue, 07 December 2010 10:53 Go to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
Suddenly I can't open any of my physical standby databases read only. Alert log snippet and trace files follow post. I'm running 9.2.0.1.0 on all hosts, which are running AIX 5.2. I've successfully opened all physical standby databases read only numerous times in the past. Can anyone help me solve this? Is it possible that these standby databases cannot be switched over to primary should the need arise?

Here's how I typically open a physical standby database read only:

alter database recover managed standby database cancel;
alter database open read only;

Thanks!
Myles

Errors in file /ora/product/9.2.0.1.0/rdbms/log/icps1_ora_27382.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Tue Dec 7 11:09:26 2010
Errors in file /ora/product/9.2.0.1.0/rdbms/log/icps1_pmon_26074.trc:
ORA-00604: error occurred at recursive SQL level
Tue Dec 7 11:09:26 2010
Errors in file /ora/product/9.2.0.1.0/rdbms/log/icps1_dbw0_25816.trc:
ORA-00604: error occurred at recursive SQL level
Tue Dec 7 11:09:26 2010
Errors in file /ora/product/9.2.0.1.0/rdbms/log/icps1_lgwr_20666.trc:
ORA-00604: error occurred at recursive SQL level
Tue Dec 7 11:09:27 2010
Errors in file /ora/product/9.2.0.1.0/rdbms/log/icps1_ckpt_20396.trc:
ORA-00604: error occurred at recursive SQL level
Tue Dec 7 11:09:36 2010
Instance terminated by USER, pid = 27382
ORA-1092 signalled during: alter database open read only...

/ora/product/9.2.0.1.0/rdbms/log/icps1_ora_27382.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/product/9.2.0.1.0
System name: AIX
Node name: db-3
Release: 2
Version: 5
Machine: 00C53E8C4C00
Instance name: ICPS1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 12
Unix process pid: 27382, image: oracle@db-3 (TNS V1-V3)

*** 2010-12-07 11:09:00.985
*** SESSION ID:(11.1) 2010-12-07 11:09:00.979
(blkno = 0x94, size = 276, max = 1, in-use = 1, last-recid= 0)
(blkno = 0x95, size = 56, max = 145, in-use = 1, last-recid= 1)
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

/ora/product/9.2.0.1.0/rdbms/log/icps1_ora_27382.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/product/9.2.0.1.0
System name: AIX
Node name: db-3
Release: 2
Version: 5
Machine: 00C53E8C4C00
Instance name: ICPS1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 12
Unix process pid: 27382, image: oracle@db-3 (TNS V1-V3)

*** 2010-12-07 11:09:00.985
*** SESSION ID:(11.1) 2010-12-07 11:09:00.979
(blkno = 0x94, size = 276, max = 1, in-use = 1, last-recid= 0)
(blkno = 0x95, size = 56, max = 145, in-use = 1, last-recid= 1)
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

/ora/product/9.2.0.1.0/rdbms/log/icps1_pmon_26074.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/product/9.2.0.1.0
System name: AIX
Node name: db-3
Release: 2
Version: 5
Machine: 00C53E8C4C00
Instance name: ICPS1
Redo thread mounted by this instance: 1
Oracle process number: 2
Unix process pid: 26074, image: oracle@db-3 (PMON)

*** 2010-12-07 11:09:26.774
*** SESSION ID:(1.1) 2010-12-07 11:09:26.765
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level

/ora/product/9.2.0.1.0/rdbms/log/icps1_dbw0_25816.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/product/9.2.0.1.0
System name: AIX
Node name: db-3
Release: 2
Version: 5
Machine: 00C53E8C4C00
Instance name: ICPS1
Redo thread mounted by this instance: 1
Oracle process number: 3
Unix process pid: 25816, image: oracle@db-3 (DBW0)

*** 2010-12-07 11:09:26.852
*** SESSION ID:(2.1) 2010-12-07 11:09:26.840
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level

/ora/product/9.2.0.1.0/rdbms/log/icps1_lgwr_20666.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/product/9.2.0.1.0
System name: AIX
Node name: db-3
Release: 2
Version: 5
Machine: 00C53E8C4C00
Instance name: ICPS1
Redo thread mounted by this instance: 1
Oracle process number: 4
Unix process pid: 20666, image: oracle@db-3 (LGWR)

*** 2010-12-07 11:09:26.927
*** SESSION ID:(3.1) 2010-12-07 11:09:26.922
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level

/ora/product/9.2.0.1.0/rdbms/log/icps1_ckpt_20396.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/product/9.2.0.1.0
System name: AIX
Node name: db-3
Release: 2
Version: 5
Machine: 00C53E8C4C00
Instance name: ICPS1
Redo thread mounted by this instance: 1
Oracle process number: 5
Unix process pid: 20396, image: oracle@db-3 (CKPT)

*** 2010-12-07 11:09:27.172
*** SESSION ID:(4.1) 2010-12-07 11:09:27.169
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level
Re: Cannot Open Physical Standby Read Only [message #485346 is a reply to message #485334] Tue, 07 December 2010 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if you have any startup trigger that tries, for instance, to log something.

ORA-16000: database open for read-only access
 *Cause:  The database has been opened for read-only access.  Attempts to
          modify the database using inappropriate DML or DDL statements
            generate this error.
 *Action: In order to modify the database, it must first be shut down and
          re-opened for read-write access.


Regards
Michel
Re: Cannot Open Physical Standby Read Only [message #485348 is a reply to message #485346] Tue, 07 December 2010 12:37 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
Michel,

Thanks for the advice. I'll check to see if there are any startup triggers.

Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485358 is a reply to message #485346] Tue, 07 December 2010 12:52 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
Michel,

Forgive my ignorance. How do I determine if I have startup triggers in place?

Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485360 is a reply to message #485358] Tue, 07 December 2010 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1* select TRIGGERING_EVENT, count(*) from dba_triggers where owner = 'SYS' group by TRIGGERING_EVENT order by 1
SQL> /

TRIGGERING_EVENT		 COUNT(*)
------------------------------ ----------
ALTER					1
CREATE					2
DROP					2
DROP OR TRUNCATE			1
RENAME					1
SHUTDOWN				1
STARTUP 				1
TRUNCATE				1

8 rows selected.



query DBA_TRIGGERS
Re: Cannot Open Physical Standby Read Only [message #485362 is a reply to message #485360] Tue, 07 December 2010 13:20 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
Thanks for the help. No startup triggers were found. Any other ideas?

Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485363 is a reply to message #485362] Tue, 07 December 2010 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure?
How did you check it?

Regards
Michel
Re: Cannot Open Physical Standby Read Only [message #485364 is a reply to message #485363] Tue, 07 December 2010 13:26 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
I used your query.

Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485365 is a reply to message #485364] Tue, 07 December 2010 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where?

Regards
Michel
Re: Cannot Open Physical Standby Read Only [message #485366 is a reply to message #485365] Tue, 07 December 2010 13:37 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
I ran the query on the primary database. Should I run it on the standby?

Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485367 is a reply to message #485366] Tue, 07 December 2010 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you use SQL*Plus and copy and paste your session as blackSwan did it.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Cannot Open Physical Standby Read Only [message #485369 is a reply to message #485367] Tue, 07 December 2010 13:54 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
SQL> select TRIGGERING_EVENT, count(*) from dba_triggers where owner = 'SYS' group by TRIGGERING_EVENT order by 1;

no rows selected

SQL>
Re: Cannot Open Physical Standby Read Only [message #485373 is a reply to message #485369] Tue, 07 December 2010 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use
select count(*) from dba_triggers where TRIGGERING_EVENT like 'STARTUP%';

Regards
Michel

[Updated on: Tue, 07 December 2010 14:14]

Report message to a moderator

Re: Cannot Open Physical Standby Read Only [message #485375 is a reply to message #485373] Tue, 07 December 2010 14:20 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
SQL> select count(*) from dba_triggers where TRIGGERING_EVENT = 'STARTUP';

  COUNT(*)
----------
         0


I should also mention that the standby database crashes after attempting to open it read only. I don't think that was obvious from the alert log.

Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485377 is a reply to message #485375] Tue, 07 December 2010 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have modified my query, use "like 'STARTUP%'" not "= 'STARTUP'".

Regards
Michel
Re: Cannot Open Physical Standby Read Only [message #485378 is a reply to message #485377] Tue, 07 December 2010 14:27 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
SQL> select count(*) from dba_triggers where TRIGGERING_EVENT like '%STARTUP%';

  COUNT(*)
----------
         0


Best regards,
Myles
Re: Cannot Open Physical Standby Read Only [message #485388 is a reply to message #485377] Tue, 07 December 2010 17:12 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
I found something that might be relevant. I think I may have hit against an Oracle bug (2399093). This post details a work around. To be honest, I'm afraid to shutdown the primary instances for fear they won't start again. Do you think it's safe to shutdown the primaries?

Best regards,
Myles
Solved! [message #485898 is a reply to message #485334] Sat, 11 December 2010 01:01 Go to previous messageGo to next message
Selym
Messages: 11
Registered: December 2010
Location: New York, New York, USA
Junior Member
I'm happy to report that I can once again open my standby databases read only. Oracle support confirmed that this was a result of bug 2399093. The trick to getting more information out of the trace files was the following:


1. Stop the MRP

alter database recover managed standby database cancel;


2. Set the events

alter system set events '604 trace name errorstack level 10';
alter system set events '16000 trace name errorstack level 10'; 

3. Disconnect and open a new session.

4. Attempt to open the database in READ ONLY mode.

alter database open read only;


5. Identify the trace file(s) generated by the session and examine it(them).



This revealed the offending query that was causing the abort on the standby:


delete from idl_ub1$ where obj#=:1




Oracle's fix is patching to at least 9.2.0.3, but the quick fix was shutting down the primaries, starting them, and performing a log switch. Once the redo was applied at the standbys, I was able to cancel managed recovery and open them read only.



Thanks again for all your help!

Myles
Re: Solved! [message #485902 is a reply to message #485898] Sat, 11 December 2010 01:39 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the detailed feedback.

Regards
Michel
Previous Topic: FAL_SERVER and FAL_CLIENT (merged)
Next Topic: disaster recovery site
Goto Forum:
  


Current Time: Thu Mar 28 15:21:06 CDT 2024