CODEWITHSUNDEEP
oracle-databasefailover
Mikhail T.
Mikhail T.

Reputation: 3997

Detecting, when an Oracle server stops being "PRIMARY"

Our application needs to (attempt to) shut down and begin failing over to the backup location, when the Oracle database begins to do the same.

The query to check the Oracle-server's status is:

select DATABASE_ROLE from v$database

When that returns anything other than PRIMARY, we want to start packing.

Though the check is easy enough, I'd like to implement it without polling -- that is, without repeatedly performing the above query. Oracle's own example shows, how to run a stored procedure upon a table change, but I don't think, that'll help me. It is also not obvious, the mechanism will work for the the special table (view?) like v$database.

How can an Oracle-client program learn, that the server it is connected to is no longer primary -- without polling it?

I'm also curious, how to learn, when your server becomes primary -- but I presume, the mechanism will be the same...

@SayanMalakshinov below explains, that there is a "system event" generated automatically, when the role changes -- great. But how would a client-side program (rather than a server-side stored procedure) learn about it happening?

Upvotes: 0

Views: 364

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

I'm not sure what exactly do you want to do automatically, but you can read about Role Transition Triggers, ie System level trigger AFTER DB_ROLE_CHANGE

https://docs.oracle.com/database/121/SBYDB/role_management.htm#SBYDB4772

9.1.5 Role Transition Triggers The DB_ROLE_CHANGE system event is signaled whenever a role transition occurs. This system event is signaled immediately if the database is open when the role transition occurs, or the next time the database is opened if it is closed when a role transition occurs.

The DB_ROLE_CHANGE system event can be used to fire a trigger that performs a set of actions whenever a role transition occurs.

Upvotes: 2

Related Questions