René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Role Transitions (Role managment services)

Switchover

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:
  • Network connection must be established between primary and standby database
    Use tnsping to verify
  • No users must be connected
    Use v$session to verify
  • In a RAC environment: all instances but one instance (on the primary and the standby) smust be shut down.
  • The standby database to become the new primary database must be placed in archivelog mode.
  • Redo application must not have a delay.

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;
Verify the environment.

Failover

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). 
select unique thread#, max(sequence#) over (partition by thread#) as last from 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:
Latent archivelog 'e:\some\path\ARC00108.001'
If you wish to failover to this standby databse, you should use the
following command to manually register the archivelog for recovery
ALTER DATABASE REGISTER LOGFILE 'e:\some\path\ARC00108.001';
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;
Otherwise: 
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;

Automatic failover

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.