Home » SQL & PL/SQL » SQL & PL/SQL » how to dynamically switch accessing a table from one db to another
how to dynamically switch accessing a table from one db to another [message #228102] Sat, 31 March 2007 12:32 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Hi,
we have two databases in our production system.
these two databases gets loaded from the data from mainframes data everyday.
Sometimes, data loading fails on one database and we switch traffic to other database even if one table loading fails.

I would like to know if there is any way to address this:

In DB1 if one tables loading is failed, I want to configure in such a way that any sql statement hitting DB1 should be automatically routed to DB2. Other statements querying other tables should come to DB1 itself.


Is this approach possible:

1) DB1 table loading failed and DB2 table loading is successful
2) From DB2 we will grant access on the Table T to DB1 and create a public synonym so that DB1 can access without the link.
3) Revoke the select privileges on the DB1 table to the user in DB1 so that select on T table wont select from local table, instead goes to the DB2 table.


Please advice.
Thanks
Giridhar


Re: how to dynamically switch accessing a table from one db to another [message #228120 is a reply to message #228102] Sat, 31 March 2007 22:22 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I can't begin to describe my revulsion to the horrific convoluted environment you described.

On the surface I suggest a slightly different implementation.
1) Control access via DNS.
2) Switch to the "new" database only after SUCCESSFUL load.
Previous Topic: Are not all the system related users created on the creation of the database?
Next Topic: materialized views
Goto Forum:
  


Current Time: Sun Dec 11 00:04:05 CST 2016

Total time taken to generate the page: 0.04075 seconds