Skip to Main Content
  • Questions
  • ORA-16000 database link read only query fails

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 29, 2003 - 4:09 pm UTC

Last updated: May 23, 2011 - 11:36 am UTC

Version: 9014

Viewed 50K+ times! This question is

You Asked

Dear Tom,
We started up a database in read only mode,all users are able to run read only queries .

One user who has a database link to another database cannot run a read only query and do a minus operation.

The target database is read-write.

The source database temp data files are read-write.

Could you please enlighten us on what is going wrong?

Thanks
~ StanR


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


SQL> select * from user_synonyms where synonym_name like 'QA_TEST_XXXXX';

SYNONYM_NAME TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
DB_LINK
--------------------------------------------------------------------------------
QA_TEST_XXXXX
QA_TEST_XXXXX
XXXXX_XXX4.XXX.STATE.XX.US



and Tom said...

distributed stuff starts a transaction "just in case". If you set your transaction read only:

scott@ORA920> select * from dual@ora9i@aria;
select * from dual@ora9i@aria
*
ERROR at line 1:
ORA-16000: database open for read-only access


scott@ORA920> commit;

Commit complete.


scott@ORA920> set transaction read only;

Transaction set.

scott@ORA920> select * from dual@ora9i@aria;

D
-
X

scott@ORA920>

scott@ORA920> select * from dual minus select * from dual@ora9i@aria;

no rows selected


that should circumvent that.

Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Still getting ORA-16000

Saminathan Seerangan, April 19, 2003 - 12:25 am UTC

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> 

Tom Kyte
April 19, 2003 - 12:01 pm UTC

who are you logged in as.

looks like SYS. SYS cannot do read only transactions -- the "transaction set" is lying to you.

use a real user, one that you would actually use. never do things as sys, system, et. al. they are "our" accounts.

Why SYS can't have READ ONLY transactions?

Kamal, April 19, 2003 - 1:33 pm UTC

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.

Tom Kyte
April 19, 2003 - 5:10 pm UTC

SYS is special.
SYS is ours

SYS is used to do recursive SQL -- it would be deadly for SYS to be in a real only transaction. SYS must NOT be prevented from transactional processing -- that would be bad.

SYS should not be used by you. That is how to prevent bad things from happening.

We can get answer for this type of questions ONLY at "asktom"

Saminathan Seerangan, April 20, 2003 - 12:59 am UTC

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

Wow...that was amazing.

DeeeBeee Crazeee, April 21, 2003 - 3:32 am UTC

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

Tom Kyte
April 21, 2003 - 7:25 am UTC

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>
 

Thats really cool

DeeeBeee Crazeee, April 21, 2003 - 8:20 am UTC

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

Tom Kyte
April 21, 2003 - 9:47 am UTC

I don't hate acronyms -- when they make sense.

SQL

for example.

It is when people use instant messaging techniques in a discussion -- using U for You and UR for Your and so on. I find it very hard to read personally (and in a multi-language world, clarity is best).

If they want me to take the time to answer, they can at least take the time to talk to me properly.

Correction :(

DeeeBeee Crazeee, April 21, 2003 - 8:21 am UTC

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

Re: ORA-16000 database link read only query fails

A reader, July 06, 2003 - 10:04 am UTC

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 Kyte
July 06, 2003 - 5:04 pm UTC

well, it actually won't always -- depends on versions. later releases don't.

What about this?

daniel, November 02, 2004 - 9:04 am UTC

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?

Tom Kyte
November 02, 2004 - 10:02 am UTC

well, it would be insert into b select * from test@a but correct, you would not be "read only" at that point.

ouch...

daniel, November 02, 2004 - 10:14 am UTC

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 Kyte
November 02, 2004 - 10:55 am UTC

that is different from a read only transaction? did you try it out?

ORA-16000 database link read only query fails

Alex, March 29, 2005 - 3:33 pm UTC

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 Kyte
March 29, 2005 - 4:45 pm UTC

I'd have to test it to be sure, but I'm inclined to say "no" -- since by the time you got into the trigger, you would already be in a remote transaction if one were to be there I would think.


But -- do you have a system to test one? If you remind me later, I can give it a try.

ORA-16000 database link read only query fails

Alex, March 30, 2005 - 12:46 pm UTC

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

Tom Kyte
March 30, 2005 - 1:05 pm UTC

are any of the indexes "monitoring" enabled in the remote database?

how are the tablespaces created? (assm or manual)

do you run anonymous plsql blocks?

have you filed a tar with support, I see a couple of "3 things together" type of cases that could possibley apply.

How to import an exported dump file from a read only database?

Yong Wu, November 19, 2005 - 12:10 pm UTC

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

Tom Kyte
November 19, 2005 - 1:43 pm UTC

this looks like the database you are importing into is READ ONLY, are you sure?

A reader, November 21, 2005 - 4:58 pm UTC

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

thanks

ORA-16000 database link read only query fails

Amar, May 05, 2006 - 4:28 am UTC

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.

Tom Kyte
May 05, 2006 - 6:53 am UTC

because opening a database link tries to start a transaction on the remote site.


I showed how to "fix that" above.

activation of standby database if primary db fails.

Rahul Katturwar, July 04, 2006 - 7:29 am UTC

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.

Tom Kyte
July 07, 2006 - 8:21 pm UTC

"u r" ??? what the heck is that? major keyboard malfunction perhaps? (need that failover keyboard...)


When you failed over, you basically cut the production instance (that failed) "lose". You said "it is very dead, game over, need to fail over to secondary".

Now, secondary needs protection, it needs a failover. Hence you need to build a new standby for it.

Even if production comes back, the "new production site" did things the "old production site" is just totally UNAWARE OF. To catch it up, you make it become a new standby site for "the new production site" and then do a controlled switchover back to it (if you want that failed production site to become production again)

Making old primary DB to standby DB after Failover

Shobhanan Pisharody, August 28, 2006 - 2:39 pm UTC

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.

Tom Kyte
August 28, 2006 - 4:41 pm UTC

no, not really, think about the syncronization that sort of has to take place here.

Making old primary DB to standby DB after Failover

Shobhanan Pisharody, August 29, 2006 - 2:17 pm UTC

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
 

Tom Kyte
August 29, 2006 - 4:42 pm UTC

this is way way too large for a review followup. sorry

Making old primary DB to standby DB after Failover

Shobhanan Pisharody, August 30, 2006 - 12:24 pm UTC

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

Thanks anyway.
Shobhanan

Kanchana, February 22, 2008 - 3:01 am UTC

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...

Tom Kyte
February 22, 2008 - 7:11 am UTC

why would you even consider making all transactions read only.

I gave no instructions, I only demonstrated how to do something.

Kanchana, February 22, 2008 - 8:24 am UTC

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

For Kanchana

A reader, June 02, 2009 - 5:31 pm UTC

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.
Tom Kyte
June 02, 2009 - 6:52 pm UTC

"A reader"

you wrote: 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.

I want to know where you read that? I didn't


and you cannot use the trigger as you suggest

a) the session is already begun (that could be a problem)
b) the command is set transaction that we were talking about, that is not session


"dual@ora9i@aria" is what?

Duke Ganote, June 03, 2009 - 6:22 pm UTC

? from your initial response... what is "dual@ora9i@aria" ? a db link to another db link?
Tom Kyte
June 04, 2009 - 2:37 pm UTC

create database line foo@bar@hello@world connect to....

@ is valid in a dblink name


when using global names - the first component must be the global database name, then rest is called a connection qualifier


I many times use "databasename@loopback" to create a "loopback" link to the same database to demonstrate dblink stuff with as well. @loopback is just a connection qualifier.

thank you... I wondered if I was seeing an exception...

Duke Ganote, June 05, 2009 - 10:05 am UTC

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

Account locking on Stand By server (read-only)

Rupal, January 23, 2010 - 4:57 pm UTC

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

ORA-16000 on read only db while logging in

Rajiv Aggarwal, May 29, 2010 - 1:01 am UTC

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.
Tom Kyte
May 29, 2010 - 8:44 am UTC

see Note 461653.1 in metalink

ORA-16000 on read only db while logging in

Rajiv Aggarwal, June 01, 2010 - 1:06 am UTC

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?


Tom Kyte
June 08, 2010 - 8:09 am UTC

please utilize support for that question. If their suggestion did not work - they would be best equipped to explain why or the setup necessary.

all setup of course would be in the production box (disabling the profiles would be rather straight forward, if you turned them on, you would know how to turn them off).

Basically they are saying "you cannot use that specific feature (failed login attempts) unless you are willing to lock them out of the standby" - which makes sense to me.

If they failed on production, they should be locked out until they clear themselves on production - since the standby doesn't have the facility to protect against failed attempts.

ORA-16000 on read only db while logging in

Rajiv Aggarwal, June 01, 2010 - 12:46 pm UTC

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.

Tom Kyte
June 08, 2010 - 8:46 am UTC

did you let the successful logon propagate over - just switching it off won't do it, you need to "reset" the counter on the failover site

One more addition

SanjayS, June 18, 2010 - 6:33 am UTC

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

ORA-16000 on logical standby

Cory, December 09, 2010 - 1:23 pm UTC

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?

ora 16000 dblink SQL Server

Erika, May 20, 2011 - 10:44 am UTC

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.
Tom Kyte
May 23, 2011 - 11:36 am UTC

not every transactional control statement works against every database, they do not all have the same features.

read only transactions, and their ability to return consistent result sets for all statements, is something Oracle does.