Performing a Role Transition Using Failover

---

Hopefully, we find you reading this section at your leisure and not with a disaster occurring around you. Ideally, everything mentioned in this section should be a part of your disaster recovery plan and it should have been tested to the nth degree. A failover implies that the primary database is unavailable and that, depending on our protection mode, the possibility of data loss exists. A failover is where being prepared means more than anything else in the world.

The decision to failover, in most cases (and depending on your company structure), will be made along with management. The role of the DBA is to provide the best option, that being one that incurs the least amount of interruption and data loss. For instance, if the primary database fails, it might be faster to restart the primary database and perform crash recovery than to perform a failover to a disaster site. If the primary is unable to be restarted and multiple standbys exist, the DBA will have to decide which one is the best option. In a configuration that contains both a physical and logical standby, we should always consider the physical standby as the best option, as a logical standby can contain a subset of the primary database's data. The DBA must also consider which standby is transactionally closest to the primary database, thus taking the least amount of time to failover.

Failover First Steps

Prior to performing the actual failover commands that convert the standby into a primary, we need to first perform some actions that will ensure that we have as much data as possible from the primary. We also need to prepare the standby to assume the role of a primary, making the transition smoother. Items that we should address are

• First and foremost, attempt to get all unapplied data off of the primary host and onto the standby host. This could include any archivelogs that did not get transferred.

• Ensure that any temporary tablespaces on the standby are populated with tempfiles.

• If the standby will become a RAC primary, make sure that all but one instance are down.

• Remove any delay setting for recovery of redo from the primary.

• Change the protection mode of the standby database to maximum performance.

HA Workshop: Failover to a Physical Standby

Workshop Notes

The following steps are to be used when performing a failover to a physical standby.

Step 1.  Resolve any gaps that may exist on the standby. Query the V$ARCHIVE_GAP view on the standby database to identify any gaps. Attempt to resolve the gaps by copying archivelogs from the primary host or from other standbys. Copy any archivelogs to the standby host that have a higher sequence number than the last one to arrive at the standby chosen for failover.

Step 2.  Any archivelogs that have been copied to resolve a gap need to be registered in the standby controlfile. Register the archivelogs with the following command on the standby:

alter database register physical logfile '/database/10gDR/arch/1_217.arc';

Step 3.  Perform terminal recovery on the standby by issuing managed recovery with the FINISH keyword.  The following command is to be used if you have standby redo logs that are configured and active:

alter database recover managed standby database finish;

If you do not have standby redo logs, or they are not active, you must enter the following command:

alter database recover managed standby database finish skip standby logfile;

Step 4.  Once the terminal recovery command completes, convert the standby into a primary database by entering the following command:

alter database commit to switchover to primary;

Step 5.  Restart the new primary database.

shutdown immediate; startup;

Step 6.  Back up the new primary database.

HA Workshop: Failover to a Logical Standby

Workshop Notes

It's important to note that when failing over to a logical standby, the old primary and any physical standby will no longer be a part of the Data Guard configuration. However, in most cases, any other logical standby databases in the configuration can still participate in the configuration. The example below illustrates how to failover to a logical standby that has no other logical standbys within the configuration.

Step 1.  Use the DBA_LOGSTDBY_LOG view to determine if the logical standby is missing any archivelogs. Copy missing archive logs or archivelogs that are higher than the last sequence received by the logical standby to the standby host.

Step 2.  Register any missing archivelogs that have been copied to the standby host with the following command:

alter database register logical logfile '/database/10gDR/arch/1_127.arc';

Step 3.  Query the DBA_LOGSTDBY_PROGRESS view to determine when all available transactions have been applied. When the NEWEST_SCN value equals the APPLIED_SCN value, all data has been applied.

select applied_scn, newest_scn from dba_logstdby_progress;

Step 4.  Activate the logical standby, making it the new primary.

alter database stop logical standby apply; alter database activate logical standby database;

Step 5.  Back up the new primary database.

Using Flashback After a Failover

Prior to Oracle Database 10g when a failover occurred, the old primary database could no longer be a member of the Data Guard configuration and had to be re-created. This was an expensive operation, as it involved at lot of time and resources. By using Flashback Database, we can bring the old primary back in time to the point just before where the failure occurred and bring it back into the configuration as a standby. Then, using switchover, we can change roles, thus bringing the primary database back to the original site.

HA Workshop: Performing a Flashback After a Failover

Workshop Notes

The following steps assume that the old primary database had flashback enabled and that you have performed a failover to a physical standby.

Step 1.  On the new primary, run the following query to determine at what SCN it became the new primary:

select to_char(standby_became_primary_scn) from v$database;

Step 2.  Bring the old primary database to the mount state and flashback to the SCN retrieved in Step 1.

flashback database to scn <SCN obtained in step 1>;

Step 3.  On the old primary, disable Flashback Database, as the flashback logs will no longer be valid.

alter database flashback off;

Step 4.  On the old primary, create a standby controlfile.

alter database create standby controlfile as '/tmp/standby.ctl';

Step 5.  Using an operating system command, copy the standby control to the location that is specified by the initialization parameter CONTROL_FILES.

Step 6.  Mount the old primary/new standby.

startup mount;

Step 7.  Enable Flashback Database.

alter database flashback on;

Step 8.  On the new primary, enable an archive destination that points to the new primary.

alter system set log_archive_dest_state_2=enable;

Step 9.  On the new standby/old primary, start managed recovery.

alter database recover managed standby database disconnect;

Once the new standby is in the configuration, you can use the switchover process to bring the primary database back to the original site.

---