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 ID11.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 ID11.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 ID1.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 ID2.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 ID3.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 ID4.1) 2010-12-07 11:09:27.169
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level

On Dec 7, 2:55*pm, Myles <myl... (AT) gmail (DOT) com> wrote:
Why are you still running 9.2.0.1? Forget for the moment that you're
now four releases behind; the fact that you're running an unpatched,
known-to-be buggy release of Oracle could be the source of your
problems. Have you checked My Oracle Support (formerly Metalink) for
descriptions and possible solutions?


David Fitzjarrell

On Dec 7, 11:55*am, Myles <myl... (AT) gmail (DOT) com> wrote:


You can set the 604 event to find out what it is failing on, check the
trace file.
alter system set events '604 trace name errorstack level 10';

First thing that pops up in MOS is a profile has been set for users.
In the Primary, a user failed a login. When a user that has failed a
login later succeeds, there is a failed login counter that is reset.
So in a standby opened read-only, a successful login attempts to write
to this counter, but throws 604 because - well, it should be obvious!

Another obvious thing is a on-logon trigger that does something DML.

And so on...

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...gation-sempra/