Tom, I'm still getting ORA-16000 error. What could be wrong?
Target dn is up and running in read-write mode.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> set transaction read only;

Transaction set.

SQL> select * from employees@tamizh_dblk;
select * from employees@tamizh_dblk
              *
ERROR at line 1:
ORA-16000: database open for read-only access


SQL> 

Hi Tom,
The resonse was very informative, however, This raises a question -
Why SYS may not initiate a READ ONLY transaction?
Since SYS account should not be used for any normal transaction processing, I would assume that it would be more important for tranactions under SYS be marked READ ONLY, to safeguard against any accidental changes.

Dear TOM, Yes i did login as SYS account. Thanks you so much for your outstanding answer.

How in the world, Tom, could you ever figure out that SYS was the account logged in? Hats off!

this sequence of events:

SQL> startup nomount
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> set transaction read only;

Transaction set.

SQL> select * from employees@tamizh_dblk;
select * from employees@tamizh_dblk
              *
ERROR at line 1:
ORA-16000: database open for read-only access


SQL> 

<b>the startup gave it away.  without any other connects after that - you KNOW they were in as SYSDBA/SYSOPER or internal (8i).  they were sys.</b>
 

Tnx....oops just forgot that you have acronyms! Thanks a lot for the information. :)

Please read 'have' as 'hate' in my previous message

Tom,
Your first posting mentions that
>>distributed stuff starts a transaction "just in case".
Why would a dbLink start a transcation, if it is just a select statement why should it start a transcation?

Thank you

Tom,

I have a situation where I need to insert the results of the query into the table.

select * from test@a into b;

In this case I can't set the transaction to be read only can I?

yes, I typed in the query wrong...sorry

so do you see any way around it, besides redesigning my process? Right now, I was just running the inserts from the stored proc. Looks like (my source is changing to read only db) I'll have to dump the query from the standby database into the flat file and then use external tables to load.

Tom

According to your post, the "set transaction read only" resolves ORA-16000 error when LOCAL read-only database dblinks to REMOTE database (normal open).

Does it also solve the ORA-16000 problem where LOCAL (normal open) database dblinks to REMOTE read-only database ?

I am referring to execution of "set transaction read only" via a LOGON trigger at the REMOTE read-only database.

Thanks for the answer.

- Alex

Tom

Thank you for the quick response.

Yes, I have a test system to perform my experinments on. (without the real data, objects, queries and environment - due to disks capacity, and some internal policies regarding shared data).


But I have difficulty in reproducing the "ORA-16000" on the test read-only database to start with. I was experimenting
with built tables of made-up data from ALL_OBJECTS, etc.

I do need some help in creating a scenario that reproduces the error on test.


Regarding the "ORA-16000" problem on the Production itself, some ad-hoc query users say (and I have experienced it too) that involving an indexed-column in the SQL gets rid of the problem (sometimes) ...

The ORA-16000 problem occurs intermittenly, especially during "heavy peak usage", according the Production DBA (the vendors).

Question 1
----------
What occurs inside the Oracle database engine during "heavy peak usage" that prompts it to want to write something (hence ORA-16000 database open for read only) , that it does NOT do during 'normal' usage ? (Other than TEMP tablespace).

The Oracle version is 9i.



Question 2
----------
Now why is the "indexed-column work-around", as described above, works ... your insights of the intricacies of the Oracle database engine is invaluable here.


Anyway, back to trying to reproduce the "ORA-16000" on my test read-only database, I thought that running a heavy query with a small sort_area_size, involving plenty of sorting and forcing the query to hit (write to) the TEMP tablespace would help me reproduce the error, but so far had been unsuccessful.


Question 3
----------
Related to Question 1, Tom, if you can think of an SQL task/scenarios that would help trigger this infamous ORA-16000 error for the test, I sure would like to know.


The Production environment is an Oracle Discoverer AS (that has the local database containing the EULs - End user Layer) supporting dozens of ad-hoc query users. It dblinks to a REMOTE read-only database for the data.

I know the error is not Discoverer AS-related, since I can pretty much SQL PLus directly to Discover's local database, run a database link SQL query to the REMOTE read-only and hit the error (intermittenly).

Unfortunately I do not have access to create the logon triggers or anything on this REMOTE Production read-only database.


Thanks again for your insight.

- Alex

I have an issue on import an exported dump files from a read only database. The source database is in read only mode. The target is in read write mode. But I got these errors:
"CREATE DATABASE LINK "GES_COMPASS.CISCO.COM" CONNECT TO "GES_CARE_COMPASS" "
"IDENTIFIED BY "X456DFERG" USING 'gesprod'"
IMP-00003: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 16000:
"BEGIN DBMS_DISTRIBUTED_TRUST_ADMIN.allow_all; END;"
IMP-00003: ORACLE error 16000 encountered
ORA-16000: database open for read-only access
ORA-06512: at "SYS.DBMS_DISTRIBUTED_TRUST_ADMIN", line 25
ORA-06512: at line 1

I seems to me I am not be able to import any thing. I search both metalink and goole and didn't find answer. Is there way to import an exported dump file from a read only database?

thanks

it seems the import utility change the mode. After I bounced the database. it works.

thanks

Hi Tom,
Is it possible to have a db link to physical standby database in read-only mode. My DW database server will need to access the tables of a physical standby database when opened in read-only mode. So i will create a db link pointing to physical standy database and will use it only when the physical standby database is opened for read operations.

Hi ! Tom
When we activate standby database as primary,then why we have to create standby database again,I don't understand the concept.I hav read u r 'Expert One On One' but there I cann't got anything about backup & disaster recovery.

Tome one curious question.

When oracle say, one will have to recreate standby DB after failover to the new primary DB; Won't recreating the standby control file from the new primary DB and copying it over to the old primary server and starting the DB in standby mode won't work ? (provided one was able to get all the datafiles of the old primary to the point of time of the failover)

Thanks in advance.

I am confused now. Won’t the FAL_SERVER and FAL_CLIENT take care of it ?

I did the following test

I have two unix servers 

salem   (Primary DB)
gamera (Physical Standby DB)

Protection mode is Maximum Availability
----------------------------------------------------------------
On salem 
----------------------------------------------------------------
SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PRIMARY          MAXIMUM AVAILABILITY


--------------------------------------------------------------
On Gamera
--------------------------------------------------------------
SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PHYSICAL STANDBY MAXIMUM AVAILABILITY


ORIONGAMERA is TNS alias on salem to connect to orion standby DB on gamera
ORIONSALEM    is TNS alias on gamera to connect to orion primary DB on salem

----------------------------------------------------------------------------------------------------
Data Guard specific initialization parameters for primary DB on salem
----------------------------------------------------------------------------------------------------
############################################
# Archive process parameters
############################################
log_archive_start = true
log_archive_max_processes = 4
log_archive_dest_1 = "location=/db01/9.2.0/oradata/orion/arch MANDATORY"
log_archive_dest_2 = "location=/db02/9.2.0/oradata/orion/arch OPTIONAL"
log_archive_format = arch_%t_%s.arc
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_min_succeed_dest=1
#######################################################################################
# Common parameters for DG which won't impact the DB whether in Primary or Standby Role
#######################################################################################
DG_BROKER_START=TRUE
log_archive_dest_3 = "SERVICE=ORIONGAMERA LGWR SYNC AFFIRM"
standby_archive_dest='/db01/9.2.0/oradata/orion/arch/stndby'
standby_file_management=AUTO
FAL_SERVER = ORIONGAMERA
FAL_CLIENT = ORIONSALEM
############################################
# Un Comment all for Primary Role
############################################
log_archive_dest_state_3=enable
remote_archive_enable=SEND
############################################
# Un Comment all for StandBY Role
############################################
#log_archive_dest_state_3=defer
#remote_archive_enable=RECEIVE


-------------------------------------------------------------------------------------------------------
Data Guard specific initialization parameters for standby DB on gamera
-------------------------------------------------------------------------------------------------------
############################################
# Archive process parameters
############################################
log_archive_start = true
log_archive_max_processes = 4
log_archive_dest_1 = "location=/db01/9.2.0/oradata/orion/arch MANDATORY"
log_archive_dest_2 = "location=/db02/9.2.0/oradata/orion/arch OPTIONAL"
log_archive_format = arch_%t_%s.arc
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_min_succeed_dest=1
#######################################################################################
# Common parameters for DG which won't impact the DB whether in Primary or Standby Role
#######################################################################################
DG_BROKER_START=TRUE
log_archive_dest_3 = "SERVICE=ORIONSALEM LGWR SYNC AFFIRM"
standby_archive_dest='/db01/9.2.0/oradata/orion/arch/stndby'
standby_file_management=AUTO
FAL_SERVER = ORIONSALEM
FAL_CLIENT = ORIONGAMERA
############################################
# Un Comment all for Primary Role
############################################
#log_archive_dest_state_3=enable
#remote_archive_enable=SEND
############################################
# Un Comment all for StandBY Role
############################################
log_archive_dest_state_3=defer
remote_archive_enable=RECEIVE


On salem (primary DB) the max(sequence# ) from  v$archived_log is  1301

SQL> select name, sequence#,standby_dest,applied from v$archived_log where sequence# =(select max(sequence#) from v$archived_log );

NAME                        SEQUENCE#    STANDBY_DEST    APPLIED
--------------------------------------------------------------------    --------------------    --------------------------    --------------
ORIONGAMERA                             1301    YES            YES
/db01/9.2.0/oradata/orion/arch/arch_1_1301.arc                 1301    NO            NO
/db02/9.2.0/oradata/orion/arch/arch_1_1301.arc                 1301    NO            NO

On gamera (standby DB) the same query returns expected results

SQL> select name, sequence#,standby_dest,applied from v$archived_log where sequence# =(select max(sequence#) from v$archived_log );

NAME                        SEQUENCE#    STANDBY_DEST    APPLIED
--------------------------------------------------------------------    --------------------    --------------------------    --------------
/db01/9.2.0/oradata/orion/arch/arch_1_1301.arc                 1301    NO            NO
/db02/9.2.0/oradata/orion/arch/arch_1_1301.arc                 1301    NO            YES

Also a check on standby redolog status on gamera (standby DB) says sequence#  1302  in progress

SQL> select group#,sequence#,status from v$standby_log ;

GROUP#    SEQUENCE#     STATUS
--------------    --------------------    -------------
            4                            0    UNASSIGNED
            5                       1302    ACTIVE
            6                            0    UNASSIGNED
            7                            0    UNASSIGNED


Everything is working fine at this point.

I simulated a crash for primary DB (salem) by killing PMON, SMON, LGWR, DMON from the unix prompt and verified instance aborted.


To make the current standby DB on gamera to fail over to Primary, did the following on gamera

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING

SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PHYSICAL STANDBY MAXIMUM AVAILABILITY


Changed protection mode from MAXIMUM AVAILIBILITY to MAXIMUM PERFORMANCE

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> alter database recover managed standby database finish;

Database altered.

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

Modified the following initialization parameters on gamera  as follows

############################################
# Un Comment all for Primary Role
############################################
log_archive_dest_state_3=enable
remote_archive_enable=SEND
############################################
# Un Comment all for StandBY Role
############################################
#log_archive_dest_state_3=defer
#remote_archive_enable=RECEIVE

Started database on gamera as new primary DB

SQL> startup
ORACLE instance started.

Total System Global Area  185685128 bytes
Fixed Size                   455816 bytes
Variable Size              83886080 bytes
Database Buffers          100663296 bytes
Redo Buffers                 679936 bytes
Database mounted.
Database opened.
SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PRIMARY          MAXIMUM PERFORMANCE

Did some transactions 

SQL> select count(*) from scott.allobj;

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

SQL> insert into scott.allobj select * from all_objects;

6166 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select unique thread#, max(sequence#) over (partition by thread#) as last from v$archived_log;

   THREAD#       LAST
---------- ----------
         1       1307

Now to prepare the crashed old primary on salem to become the new standby DB, created a standby control file from the new primary DB on gamera as follows

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/export/home/oracle/StndbyOrion.ctl';

Database altered.

Shutdown the new primary DB on gamera

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Copied the standby controlfile to salem to replace existing controlfiles on salem

$ pwd
/export/home/oracle
$ rcp StndbyOrion.ctl salem:/db01/9.2.0/oradata/orion/control01.ctl
$ rcp StndbyOrion.ctl salem:/db02/9.2.0/oradata/orion/control02.ctl
$ rcp StndbyOrion.ctl salem:/opt/oracle/oradata/orion/control03.ctl

Copied the password file to salem

$ cd $ORACLE_HOME/dbs
$ pwd
/opt/oracle/OraHome1/9.2.0/dbs
$ rcp orapworion salem:/opt/oracle/OraHome1/9.2.0/dbs/orapworion

On salem (the crashed old primary DB) , modified the initialization parameters to make it the new standby DB as follows

############################################
# Un Comment all for Primary Role
############################################
#log_archive_dest_state_3=enable
#remote_archive_enable=SEND
############################################
# Un Comment all for StandBY Role
############################################
log_archive_dest_state_3=defer
remote_archive_enable=RECEIVE


Started database on salem as standby DB  with the new standby controlfile copied over from gamera as follows

SQL> startup nomount
ORACLE instance started.

Total System Global Area  185685128 bytes
Fixed Size                   455816 bytes
Variable Size              83886080 bytes
Database Buffers          100663296 bytes
Redo Buffers                 679936 bytes

SQL> alter database mount standby database;

Database altered.


On gamera started the new primary DB

SQL> startup
ORACLE instance started.

Total System Global Area  185685128 bytes
Fixed Size                   455816 bytes
Variable Size              83886080 bytes
Database Buffers          100663296 bytes
Redo Buffers                 679936 bytes
Database mounted.
Database opened.

On salem started managed recovery as follows

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select group#,sequence#,archived, status from v$standby_log ;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------
         4       1308 YES ACTIVE
         5          0 YES UNASSIGNED
         6          0 YES UNASSIGNED
         7          0 YES UNASSIGNED

SQL> select unique thread#, max(sequence#) over (partition by thread#) as last from v$archived_log;

   THREAD#       LAST
---------- ----------
         1       1307

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
---------- ---
      1303 YES
      1304 YES
      1305 YES
      1306 YES
      1307 YES


Checked the protection mode on gamera (new primary DB)


SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PRIMARY          MAXIMUM PERFORMANCE


Checked the protection mode on salem (new standby DB)


SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PHYSICAL STANDBY MAXIMUM PERFORMANCE


Changed protection mode on new primary DB (gamera) to maximum availability

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  185685128 bytes
Fixed Size                   455816 bytes
Variable Size              83886080 bytes
Database Buffers          100663296 bytes
Redo Buffers                 679936 bytes
Database mounted.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PRIMARY          MAXIMUM AVAILABILITY


Verified the protection mode on new standby DB (salem) again


SQL> select name, database_role, protection_mode from v$database;

NAME      DATABASE_ROLE    PROTECTION_MODE
--------- ---------------- --------------------
ORION     PHYSICAL STANDBY MAXIMUM AVAILABILITY


Am I just got lucky ?

Thanks
Shobhanan
 

Sorry, no bother. That was the only way I could provide you a test case.

Thanks anyway.
Shobhanan

Hi Tom,
As per your instructions i have set the command "set transaction read only"

It works. My only concern is that can we set it as session level ,so when a new session get initiated,invoke this command automatically.

Thanks...

thanks for the infor. I'm genarating/refreshing all dblink related reports using reporting tool (Business Objects ). there I can only write sql queries and not set commands .

once the reports refresh batch process is done then I should be able to revoke the set command.

Thanks
Kanchana

Kanchana: you could use a trigger on LOGON to enforce new sessions are auto-pushed into a read only "transaction" (i.e. no transaction at all).

If you have a physical standby you'll want to put some sort of condition that ensures the trigger only works for the standby -your users wouldn't appreciate being unable to DML in your primary DB!

Tom: he wants to do this because he's connected to an instance which has the DB as "open read only", and needs queries involving remote objects to work straight out of the box.

? from your initial response... what is "dual@ora9i@aria" ? a db link to another db link?

I wondered if it might be an exception to my earlier question: "can you call a database link over a database link"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:688972400346782850

Hi Tom,
Thanks so much for continuous support for we oracle developers.

When we have the database in read-only mode, and if someone tried logging in with wrong password multiple times then
1. Will the account get locked? I am asking because I assume for registering account as locked, oracle will try to fire internal dml statements and it may not be allowed as database is in read-only.
2. First time when user gives wrong password, will oracle give error specific to wrong username/password or it will give ora-16000? (Here I assume that Oracle must be internally logging in number of attempts)

Regards
Rupali


Regards,
Shrikant

Tom,

I am getting ORA-16000 on read only (standby) db at times. Later I figured out that it was because if there was any failed login attempt on primary database, it will increment LCOUNT in user$ that will get propagated to standby. So now when we try to login on standby read only db, it errors out with ORA-16000. This will continue until a successful attempt is made on Primary db.

Now my question is how can I avoid this from happening? Only way I can think of is avoiding incrementing LCOUNT for failed logins. Can there be any workaround/fix for my problem?

Thanks in advance.

Thanks Tom for quick response.

I have gone through this note and solution mentioned is:

"For the users to always successfully connect to the read only database, the LCOUNT should never be > 0.
This can possibly be worked around either by disabling the profiles for the users, or by removing the FAILED_LOGIN_ATTEMPTS or setting it to unlimited"

I already have FAILED_LOGIN_ATTEMPTS set to unlimited but that does not seem to be helping. I am not sure how can I remove the FAILED_LOGIN_ATTEMPTS or disable the profile. Can you please tell me how can I do that?

I have tried resource_limit to false on primary/standby with no luck. I am not able to avoid the lcount being incremented with failed attempt on primary and having logon fail at standby.

Hi Tom,

Addition to the same question. Some time while connecting to read only databases gives this error. This happens with few users only and all the users.

Brgds,

Sanjay

From time to time, I'm getting an ORA-26786 (row exists but columns conflict) error that is stopping the apply process. The table is too large to re instantiate, so I perform an export in the primary, ship the file to the logical standby, and then replace the row in the standby. While this could cause more ORA-26786 errors if there were multiple updates to the same row, our records show this has not been a problem. I want to automate this process which led to the ora-16000 error on the logical standby.

I created a procedure that will export the row on the primary and ship the .dmp file to the logical standby. The procedure works fine when invoked on the primary. The problem occurs when I try to invoke the procedure remotely from the logical standby.
Here's a test invocation on the logical standby of the export a row in the primary.
exec crt_t216_ext@dbp('1234')
and here's the result.
ORA-16000: database open for read-only access
ORA-06512: at "CRT_T216_EXT", line 24
ORA-06512: at line 1

The logical standby is in "guard standby" so that users can read and write it. So neither primary nor logical standby are in read only mode.
How should I change the invocation?

Hi Tom,
im trying to use a SQL SERVER dblink and it doesnt work with the "set transaction read only". It does work for another oracle databases but it doenst work for a dblink pointing to a SQl server Database.

set transaction read only;

select * from CLDRCCI@RIESGO;--ok
select * from SYTREMI@SQL;--

How can i make it work.