Home » Server Options » Data Guard » ORA-16000
ORA-16000 [message #238474] Fri, 18 May 2007 04:08 Go to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member

Hi Dear's

I have create phyical standby database using oracle r2 in windows env.

SQL> SELECT NAME,DATABASE_ROLE FROM V$DATABASE;

NAME DATABASE_ROLE
--------- ----------------
KEYSTONE PHYSICAL STANDBY

SQL> ALTER USER SCOTT ACCOUNT UNLOCK;
ALTER USER SCOTT ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access


How to solve this problem. Also, here i need to work in DDL and DML cmd in this database.

And, I can't able configure EMCA. this is my log file

oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-16000: database open for read-only access
ORA-06512: at line 1

at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1467)
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeSql(SQLEngine.java:1708)
at oracle.sysman.emcp.EMReposConfig.unlockAccounts(EMReposConfig.java:566)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:235)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:124)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:142)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)
May 18, 2007 2:40:55 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Failed to unlock all EM-related accounts
May 18, 2007 2:40:55 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Failed to unlock all EM-related accounts
Refer to the log file at F:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\KEYSTONESTDY\emca_2007-05-18_02-39-22-PM.log for more details.
May 18, 2007 2:40:55 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Failed to unlock all EM-related accounts
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:239)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:124)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:142)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:46


Thanks for your advice.

Babu B

[Updated on: Fri, 18 May 2007 04:14]

Report message to a moderator

Re: ORA-16000 [message #238594 is a reply to message #238474] Fri, 18 May 2007 11:58 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Hi,
You using this query on primary or standby??

SQL> ALTER USER SCOTT ACCOUNT UNLOCK;
ALTER USER SCOTT ACCOUNT UNLOCK
*
Re: ORA-16000 [message #238927 is a reply to message #238594] Mon, 21 May 2007 00:55 Go to previous messageGo to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member


Yes i have used physical standby database.

Re: ORA-16000 [message #238936 is a reply to message #238927] Mon, 21 May 2007 01:02 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ORA-00604: error occurred at recursive SQL level string
Cause: An error occurred while processing a recursive SQL statement (a
statement applying to internal dictionary tables).
Action: If the situation described in the next error on the stack can be corrected,
do so; otherwise contact Oracle Support.
Re: ORA-16000 [message #238938 is a reply to message #238936] Mon, 21 May 2007 01:04 Go to previous messageGo to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member


Ok. Thanks for your reply.
Re: ORA-16000 [message #238940 is a reply to message #238936] Mon, 21 May 2007 01:05 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ORA-16000
R u sure your database is not in readonly mode??

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.
Re: ORA-16000 [message #238944 is a reply to message #238940] Mon, 21 May 2007 01:10 Go to previous messageGo to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member


Yes my database is read only mode.

Now E:\>SQLPLUS "/AS SYSDBA"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 21 11:38:37 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>
SQL> alter user scott account unlock;
alter user scott account unlock
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access



SQL> select log_mode,open_mode , database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ ---------- ----------------
ARCHIVELOG READ ONLY PHYSICAL STANDBY

SQL>
Re: ORA-16000 [message #239056 is a reply to message #238944] Mon, 21 May 2007 06:25 Go to previous messageGo to next message
asadaslam18
Messages: 16
Registered: May 2007
Location: nine
Junior Member

Have u defined any thing in ur spfile kindly create pfile from ur running spfile and paste it here.
Re: ORA-16000 [message #239075 is a reply to message #239056] Mon, 21 May 2007 07:32 Go to previous messageGo to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member

My standby db pfile :

KEYSTONESTDY.__db_cache_size=180355072
KEYSTONESTDY.__java_pool_size=4194304
KEYSTONESTDY.__large_pool_size=4194304
KEYSTONESTDY.__shared_pool_size=92274688
KEYSTONESTDY.__streams_pool_size=0
*.audit_file_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/adump'
*.background_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\product\10.2.0/oradata/KEYSTONESTDY/\control01.ctl'
*.core_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.db_name='KEYSTONE'
db_unique_name='KEYSTONESTDY'
*.db_recovery_file_dest='F:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KEYSTONEXDB)'
*.fal_client='KEYSTONESTDY'
*.fal_server='KEYSTONE'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY'
*.log_archive_format='ARC%S_%R.%T'
*.log_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
service_names='KEYSTONESTDY'
*.sga_target=287309824
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/udump'


standby db spfile :

keystonestdy.__java_pool_size=4194304
keystonestdy.__large_pool_size=4194304
keystonestdy.__shared_pool_size=92274688
keystonestdy.__streams_pool_size=0
*.audit_file_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/adump'
*.background_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\product\10.2.0/oradata/KEYSTONESTDY/\control01.ctl'
*.core_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.db_name='KEYSTONE'
*.db_recovery_file_dest='F:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='KEYSTONESTDY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KEYSTONEXDB)'
*.fal_client='KEYSTONESTDY'
*.fal_server='KEYSTONE'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY'
*.log_archive_format='ARC%S_%R.%T'
*.log_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='KEYSTONESTDY'
*.sga_target=287309824
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/udump'

Re: ORA-16000 [message #239173 is a reply to message #238944] Mon, 21 May 2007 12:05 Go to previous messageGo to next message
asadaslam18
Messages: 16
Registered: May 2007
Location: nine
Junior Member

I am sorry i saw ur this message right now

SQL> select log_mode,open_mode , database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ ---------- ----------------
ARCHIVELOG READ ONLY PHYSICAL STANDBY


this is ur physical standby and is in read only mode u cant issue any alter user and such kind of statements in ur physical standby what u can do is that issue

alter user scott account unlock;

in ur primary database which is in READ WRITE mode .



Re: ORA-16000 [message #239192 is a reply to message #239173] Mon, 21 May 2007 13:06 Go to previous messageGo to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member


Can you give me your clarification about this issue ...

Babu
Re: ORA-16000 [message #239568 is a reply to message #239192] Tue, 22 May 2007 12:26 Go to previous messageGo to next message
asadaslam18
Messages: 16
Registered: May 2007
Location: nine
Junior Member

kindly check ur primary database it shuld be like this
see the open_mode field must be READ WRITE so u can issue the statements here.



SQL> select log_mode,open_mode , database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ ---------- ----------------
ARCHIVELOG READ WRITE PRIMARY


but as standby is in read only mode u cannot issue any statement.
Re: ORA-16000 [message #239596 is a reply to message #239568] Tue, 22 May 2007 13:20 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Read only mean only for selecting purpose, if you wants to make any changes in your database, just change mode to read and write mode change your database than recreate your standby database. Else fire that command on primary node, that will reflect on stanby node.
Re: ORA-16000 [message #239857 is a reply to message #239596] Wed, 23 May 2007 08:23 Go to previous message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member


Thanks for all....

Babu
Previous Topic: TNS protocol adapter error / buffer space
Next Topic: Multiple Listerners setup
Goto Forum:
  


Current Time: Wed Oct 01 21:46:47 CDT 2014

Total time taken to generate the page: 0.09368 seconds