|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Role Transitions (Role managment services)
Is a reversible role transition between the primary database and a standby database. No data is lost in a switchover operation.
Oracle Corp. recommends to preferably switch over a primary database with a physical standby database.
A switchover consists of two fundamental steps: first convert the primary database into a standby database; secondly, convert a standby database into a primary database.
Preparations for a switchover
The preparations for a switchover are the same for a physical and a logical standby database.
The following points must met:
On a physical standby environment
Verify that the primary database instance is open. The standby database is ideally mounted, although it can also be opened for read only access (in which case the switch over takes longer).
Step 1: converting the primary database into a standby database.
Make sure that switchover_status on the primary database in v$database is equal to TO STANDBY. If this is not the case, something is wrong with the environment and it should not be proceeded.
alter database commit to switchover to physical standby; shutdown immediate startup nomount alter database mount standby database;
If the spfile or the pfile is not at its default location, the startup command must be given the pfile argument as well.
Oracle's (9iR2) documentation says that the switchover_status in v$database on the target physical standby database should be equal to SWITCHOVER_PENDING. However, I found that not to be true.
Step 2: turning the target database into a primary database.
alter database commit to switchover to primary; shutdown startup
On the new physical standby database
alter database recover managed standby database disconnect from session;
On the new primary database:
alter system archive log current;
A failover converts a standby database into a primary database. Failovers are executed when the primary database is destroyed. Depending on the protection mode in place, a failover might loose some data (namely if the protection mode is not maximum protection).
On a physical standby environment
A database running in maximum protection mode cannot be failed over to. Therefore, a maximum protection mode standby database must be set to maximum performance:
alter database set standby database to maximize performance;
If there are any redo log gaps (see v$archive_gap), copy them from another standby database and register them:
alter database register physical logfile 'some logfile name';
Find other missing archived redo logs (v$archived_log).
Copy these archived redo logs from other databases and register them as well.
Now, there might still be gaps. So find them and repeat.
Also consult the end of the alert.log if there is an instruction that goes like:
Of course, that's what we do.
If the target standby database was configured with standby redo logs and there are no manually registered partial archived redo logs:
alter database recover managed standby database finish;
alter database recover managed standby database finish skip standby logfile;
Now, transform the standby database to the primary role:
alter database commit to switchover to primary;
Altough it would be nice to have an automatic failover, there is (almost by definition) no such thing. A machine would try to failover with a probablity close to 100% even if there were no need. This is because a machine can never reliably tell if another machine is dead. Only human intervention can do that. All a machine can do is to detect that it cannot reach another machine, bat that, by itself, does not mean the other machine is dead.