Re: Cannot Open Physical Standby Read Only
From: ddf <oratune_at_msn.com>
Date: Tue, 7 Dec 2010 22:20:53 -0800 (PST)
Message-ID: <6077a28f-bf92-499b-86f5-3a35195a96a7_at_m20g2000prc.googlegroups.com>
On Dec 7, 2:55 pm, Myles <myl..._at_gmail.com> wrote:
> 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_at_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_at_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_at_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_at_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_at_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_at_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
Date: Tue, 7 Dec 2010 22:20:53 -0800 (PST)
Message-ID: <6077a28f-bf92-499b-86f5-3a35195a96a7_at_m20g2000prc.googlegroups.com>
On Dec 7, 2:55 pm, Myles <myl..._at_gmail.com> wrote:
> 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_at_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_at_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_at_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_at_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_at_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_at_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
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 Received on Wed Dec 08 2010 - 00:20:53 CST