11 Restoring Fault Tolerance

11.2.1 Recovering Service Availability

After a failed node has been brought back into the cluster and its instance has been started, RAC's Cluster Ready Services (CRS) automatically manages the virtual IP address used for the node and the services supported by that instance automatically. A particular service may or may not be started for the restored instance. The decision by CRS to start a service on the restored instance depends on how the service is configured and whether the proper number of instances are currently providing access for the service. A service is not relocated back to a preferred instance if the service is still being provided by an available instance to which it was moved by CRS when the initial failure occurred. CRS restarts services on the restored instance if the number of instances that are providing access to a service across the cluster is less than the number of preferred instances defined for the service. After CRS restarts a service on a restored instance, CRS notifies registered applications of the service change.

For example, suppose the HR service is defined with instances A and B as preferred and instances C and D as available in case of a failure. If instance B fails and CRS starts up the HR service on C automatically, then when instance B is restarted, the HR service remains at instance C. CRS does not automatically relocate a service back to a preferred instance.

Suppose a different scenario in which the HR service is defined with instances A, B, C, and D as preferred and no instances defined as available, spreading the service across all nodes in the cluster. If instance B fails, then the HR service remains available on the remaining three nodes. CRS automatically starts the HR service on instance B when it rejoins the cluster because it is running on fewer instances than configured. CRS notifies the applications that the HR service is again available on instance B.

11.2.2 Considerations for Client Connections After Restoring a RAC Instance

After a RAC instance has been restored, additional steps may be required, depending on the current resource utilization and performance of the system, the application configuration, and the network load balancing that has been implemented.

Existing connections (which may have failed over or started as a new session) on the surviving RAC instances, are not automatically redistributed or failed back to an instance that has been restarted. Failing back or redistributing users may or may not be necessary, depending on the current resource utilization and the capability of the surviving instances to adequately handle and provide acceptable response times for the workload. If the surviving RAC instances do not have adequate resources to run a full workload or to provide acceptable response times, then it may be necessary to move (disconnect and reconnect) some existing user connections to the restarted instance.

New connections are started as they are needed, on the least-used node, assuming connection load balancing has been configured. Therefore, the new connections are automatically load-balanced over time.

An application service can be:

• Partitioned with services running on a subset of RAC instances

• Nonpartitioned so that all services run equally across all nodes

This is valuable for modularizing application and database form and function while still maintaining a consolidated data set. For the cases where an application is partitioned or has a combination of partitioning and non-partitioning, the response time and availability aspects for each service should be considered. If redistribution or failback of connections for a particular service is required, then you can rebalance workloads manually with the DBMS_SERVICE.disconnect_session PL/SQL procedure. You can use this procedure to disconnect sessions associated with a service while the service is running.

For load-balancing application services across multiple RAC instances, Oracle Net connect-time failover and connection load balancing are recommended. This feature does not require changes or modifications for failover or restoration. It is also possible to use hardware-based load balancers. However, there may be limitations in distinguishing separate application services (which is understood by Oracle Net Services) and restoring an instance or a node. For example, when a node or instance is restored and available to start receiving new connections, a manual step may be required to include the restored node or instance in the hardware-based load balancer logic, whereas Oracle Net Services does not require manual reconfiguration.

Table 11-2 summarizes the considerations for new and existing connections after an instance has been restored. The considerations differ depending on whether the application services are partitioned, nonpartitioned, or have a combination of each type. The actual redistribution of existing connections may or may not be required depending on the resource utilization and response times.

Figure 11-1 shows a 2-node partitioned RAC database. Each instance services a different portion of the application (HR and Sales). Client processes connect to the appropriate instance based on the service they require.

Figure 11-1 Partitioned 2-Node RAC Database

Description of the illustration maxav015.gif

Figure 11-2 shows what happens when one RAC instance fails.

Figure 11-2 RAC Instance Failover in a Partitioned Database

Description of the illustration maxav016.gif

If one RAC instance fails, then the service and existing client connections can be automatically failed over to another RAC instance. In this example, the HR and Sales services are both supported by the remaining RAC instance. In addition, new client connections for the Sales service can be routed to the instance now supporting this service.

After the failed instance has been repaired and restored to the state shown in Figure 11-1 and the Sales service is relocated to the restored instance failed-over clients and any new clients that had connected to the Sales service on the failed-over instance may need to be identified and failed back. New client connections, which are started after the instance has been restored, should automatically connect back to the original instance. Therefore, over time, as older connections disconnect, and new sessions connect to the Sales service, the client load migrates back to the restored instance. Rebalancing the load immediately after restoration depends on the resource utilization and application response times.

Figure 11-3 shows a nonpartitioned application. Services are evenly distributed across both active instances. Each instance has a mix of client connections for both HR and Sales.

Figure 11-3 Nonpartitioned RAC Instances

Description of the illustration maxav017.gif

If one RAC instance fails, then CRS moves the services that were running on the failed instance. In addition, new client connections are routed only to the available RAC instances that offer that service.

After the failed instance has been repaired and restored to the state shown in Figure 11-3, some clients may need to be moved back to the restored instance. For nonpartitioned applications, identifying appropriate services is not required for rebalancing the client load among all available instances. Also, this is necessary only if a single instance is not able to adequately service the requests.

New client connections that are started after the instance has been restored should automatically connect back to the restored instance because it has a smaller load. Therefore, over time, as older connections disconnect and new sessions connect to the restored instance, the client load will again evenly balance across all available RAC instances. Rebalancing the load immediately after restoration depends on the resource utilization and application response times.

11.3.1 Restoring a Physical Standby Database After a Failover

The following steps are required to restore a physical standby database after a failover. The steps assume that archived redo logs and sufficient flashback log data are available.

• Step 1P: Retrieve STANDBY_BECAME_PRIMARY_SCN

• Step 2P: Flash Back the Previous Production Database

• Step 3P: Mount New Standby Database From Previous Production Database

• Step 4P: Archive to New Standby Database From New Production Database

• Step 5P: Start Managed Recovery

SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

SHUTDOWN IMMEDIATE; /*if necessary */
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN standby_became_primary_scn;

ALTER DATABASE FLASHBACK OFF;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS controlfile_name;
    SHUTDOWN IMMEDIATE;

STARTUP MOUNT;
 

LSNRCTL STAT list_name;

SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR, SRL
    FROM V$ARCHIVE_DEST_STATUS;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

ALTER SYSTEM SWITCH LOGFILE;
SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR, SRL
    FROM V$ARCHIVE_DEST_STATUS;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

SELECT * FROM V$MANAGED_STANDBY;

11.3.2 Restoring a Logical Standby Database After a Failover

The following steps are required to restore a logical standby database after a failover:

• Step 1L: Retrieve END_PRIMARY_SCN

• Step 2L: Flash Back the Previous Production Database

• Step 3L: Open New Logical Standby Database and Start SQL Apply

SELECT VALUE AS TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM DBA_LOGSTDBY_PARAMETERS
WHERE NAME = 'END_PRIMARY_SCN';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN standby_became_primary_scn;
ALTER DATABASE GUARD [ALL | STANDBY | NONE];

ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY dblink;

CREATE PUBLIC DATABASE LINK dblink
  CONNECT TO system IDENTIFIED BY password
  USING 'service_name_of_new_primary_database';

11.4.1 Step 1: Start the Standby Database

You may have to restore the standby database from local backups, local tape backups, or from the primary site backups if the data in the secondary site has been damaged. Re-create the standby database from the new production database by following the steps for creating a standby database in Oracle Data Guard Concepts and Administration.

After the standby database has been reestablished, start the standby database.

11.4.2 Step 2: Start Recovery

11.4.3 Step 3: Verify Log Transport Services on Production Database

You may have to reenable the production database remote archive destination. Query the V$ARCHIVE_DEST_STATUS view first to see the current state of the archive destinations:

SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR, SRL 
FROM V$ARCHIVE_DEST_STATUS;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
ALTER SYSTEM SWITCH LOGFILE;

Verify log transport services between the production and standby databases by checking for errors. Query V$ARCHIVE_DEST and V$ARCHIVE_DEST_STATUS views.

SELECT STATUS, TARGET, LOG_SEQUENCE, TYPE, PROCESS, REGISTER, ERROR 
    FROM V$ARCHIVE_DEST;
SELECT * FROM V$ARCHIVE_DEST_STATUS WHERE STATUS!='INACTIVE';

11.4.4 Step 4: Verify that Recovery is Progressing on Standby Database

For a physical standby database, verify that there are no errors from the managed recovery process and that the recovery has applied the archived redo logs.

SELECT MAX(SEQUENCE#), THREAD# FROM V$LOG_HISTORY GROUP BY THREAD;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, CLIENT_PROCESS
    FROM V$MANAGED_STANDBY;

For a logical standby database, verify that there are no errors from the logical standby process and that the recovery has applied the archived redo logs.

SELECT THREAD#, SEQUENCE# SEQ# 
    FROM DBA_LOGSTDBY_LOG LOG, DBA_LOGSTDBY_PROGRESS PROG 
    WHERE PROG.APPLIED_SCN BETWEEN LOG.FIRST_CHANGE# AND LOG.NEXT_CHANGE# 
    ORDER BY NEXT_CHANGE#;

11.4.5 Step 5: Restore Production Database Protection Mode

If you had to change the protection mode of the production database from maximum protection to either maximum availability or maximum performance because of the standby database outage, then change the production database protection mode back to maximum protection depending on your business requirements.

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE [PROTECTION | AVAILABILITY];

11.5.1 Step 1: Fix the Cause of the Outage

The root cause of the outage should be investigated and action taken to prevent the problem from occurring again.

11.5.2 Step 2: Restore the Backup of Affected Datafiles

Only the affected datafiles need to be restored on to the standby site.

11.5.3 Step 3: Restore Required Archived Redo Log Files

Archived redo log files may need to be restored to recover the restored data files up to the configured lag.

For physical standby databases:

• If the archived redo logs required for recovery are available on the standby system in a configured archive destination, then the managed recovery process automatically finds and applies them as needed. No restoration is necessary.

• If the required archived redo logs have been deleted from the standby system but are still available on the production system, then the fetch archive log (FAL) process is automatically invoked to transfer them to the standby system. No restoration is necessary.

For logical standby databases, initiate complete media recovery for the affected files. Consider the following:

• If the archived redo logs required for recovery are available on the standby system in a configured archive destination, then the recovery process automatically finds and applies them as needed. No restoration is necessary.

• If the required archived redo logs have been deleted from the standby system, then they must be restored to the standby system. Complete media recovery for the affected files after the necessary archived redo logs are available.

11.5.4 Step 4: Start the Standby Database

After the standby database has been reestablished, start the standby database.

11.5.5 Step 5: Start Recovery or Apply

11.5.6 Step 6: Verify Log Transport Services On the Production Database

Verify log transport services on the new production database by checking for errors when querying V$ARCHIVE_DEST and V$ARCHIVE_DEST_STATUS.

SELECT STATUS, TARGET, LOG_SEQUENCE, TYPE, PROCESS, REGISTER, ERROR FROM V$ARCHIVE_DEST;

SELECT * FROM V$ARCHIVE_DEST_STATUS WHERE STATUS != 'INACTIVE';

11.5.7 Step 7: Verify that Recovery or Apply Is Progressing On the Standby Database

For a physical standby database, verify that there are no errors from the managed recovery process and that the recovery has applied archived redo logs.

SELECT MAX(SEQUENCE#), THREAD# FROM V$LOG_HISTORY GROUP BY THREAD;
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, CLIENT_PROCESS
    FROM V$MANAGED_STANDBY;

For a logical standby database, verify that there are no errors from the logical standby process and that the recovery has applied archived redo logs.

SELECT THREAD#, SEQUENCE# SEQ# 
    FROM DBA_LOGSTDBY_LOG LOG, DBALOGSTDBY_PROGRESS PROG
    WHERE PROG.APPLIED_SCN BETWEEN LOG.FIRST_CHANGE# AND LOG.NEXT_CHANGE#
    ORDER BY NEXT_CHANGE#;

11.5.8 Step 8: Restore Production Database Protection Mode

If you had to change the protection mode of the production database from maximum protection to either maximum availability or maximum performance because of the standby database outage, then change the production database protection mode back to maximum protection depending on your business requirements.

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE [PROTECTION | AVAILABILITY];

11.6.1 Scenario 1: SCN on Standby is Behind Resetlogs SCN on Production

11.6.2 Scenario 2: SCN on Standby is Ahead of Resetlogs SCN on Production

SHUTDOWN IMMEDIATE; /* if necessary */
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN resetlogs_change#_minus_2;

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SELECT APPLIED_SCN FROM DBA_LOGSTDBY_PROGRESS;

SHUTDOWN;
STARTUP MOUNT EXCLUSIVE;
FLASHBACK DATABASE TO TIMESTAMP time_of_primary_database_flashback;
ALTER DATABASE OPEN READ ONLY;
SELECT APPLIED_SCN FROM DBA_LOGSTDBY_PROGRESS;

SHUTDOWN;
STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE OPEN RESETLOGS;

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER DATABASE START LOGICAL STANDBY APPLY;