DBA Tips Archive for Oracle

  


Activating the Standby Database - (10g, Logical Standby)

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

The tasks involved in creating and managing a Data Guard configuration are fairly straightforward. Once the standby database is put into operation, you are happy, your customer is happy, management is happy - everyone is happy with the piece of mind that their data is being replicated to their disaster recovery site. But the time will eventually come where the inevitable will occur and the primary database becomes unavailable. You are now faced with failing over production activities to an available standby database. Other circumstances can also arise where scheduled maintenance needs to occur on the primary database and database operations need to be switched over to the standby database. In either case, the role of the primary database and the standby database will need to be changed. This is known as Role Transition and is the subject of this article.

It is assumed that a primary and one logical standby database is already configured in order to perform the role transition steps described in this guide. The examples used in this guide will make use of the Oracle Data Guard configuration described in the article below:

The following tables summarize the Oracle Data Guard configuration that will be used in this guide:

Primary Database
Oracle Release Oracle 10g Release 2 — (10.2.0.5)
Host Name vmlinux1.idevelopment.info — (192.168.1.160)
Operating System Red Hat Linux 5 — (CentOS 5.5)
Database Name - (db_name) modesto
Database Domain - (db_domain) idevelopment.info
Oracle SID modesto
Database Unique Name - (db_unique_name) modesto
TNS Alias modesto.idevelopment.info
Service Names modesto.idevelopment.info, modesto
Database Files - (db_create_file_dest) /u02/oradata
Flash Recovery Area - (db_recovery_file_dest) /u03/flash_recovery_area
Local Online Redo Log Files - (log_archive_dest_1) location=use_db_recovery_file_dest — (online_logfiles,all_roles)
Remote Archive Destination - (log_archive_dest_2) service=turlock — (online_logfiles,primary_role)
Redo Log Files Received From Primary - (log_archive_dest_3) location=/u04/oracle/oraarch/MODESTO — (standby_logfiles,standby_role)

Logical Standby Database
Oracle Release Oracle 10g Release 2 — (10.2.0.5)
Host Name vmlinux2.idevelopment.info — (192.168.1.162)
Operating System Red Hat Linux 5 — (CentOS 5.5)
Database Name - (db_name) turlock
Database Domain - (db_domain) idevelopment.info
Oracle SID turlock
Database Unique Name - (db_unique_name) turlock
TNS Alias turlock.idevelopment.info
Service Names turlock.idevelopment.info, turlock
Database Files - (db_create_file_dest) /u02/oradata
Flash Recovery Area - (db_recovery_file_dest) /u03/flash_recovery_area
Local Online Redo Log Files - (log_archive_dest_1) location=use_db_recovery_file_dest — (online_logfiles,all_roles)
Remote Archive Destination - (log_archive_dest_2) service=modesto — (online_logfiles,primary_role)
Redo Log Files Received From Primary - (log_archive_dest_3) location=/u04/oracle/oraarch/TURLOCK — (standby_logfiles,standby_role)

Role Transition

Role transition plays an important part in Data Guard by providing an interface that allows the DBA to activate a standby database to take over as the primary database. There are two types of role transitions supported in Oracle 10g Data Guard:

Which Role Transition Operation Should I Use?

When faced with the decision on which role transition is best for the given situation, you need to always choose one that best reduces downtime and has the least potential for data loss. Also to consider is how the change will affect any other standby database in the configuration. You should consider the following when making the decision on which operation to use:

The following decision tree can be used to assist when making this critical decision as to which operation to perform:

One key point to consider is that if it would be faster to repair the primary database (from failure or a simple planned hardware/software upgrade), the most efficient method would be to perform the tasks and then to bring up the primary database as quickly as possible and not perform any type of role transition. This method can impose less risk to the system and does not require any client software to be re-configured.

Another consideration involves a Data Guard configuration which includes a logical standby database. A switchover operation can be performed using either a physical or logical standby database. Take note, however, of the following issues you may run in to regarding physical and logical standby configurations. If the configuration includes a primary, a physical standby, and a logical standby, and a switchover is performed on the logical standby, the physical standby will no longer be a part of the configuration and must be rebuilt. In the same scenario, if a switchover operation is performed on the physical standby, the logical standby remains in the Data Guard configuration and does not need to be rebuilt. Obviously, a physical standby is a better option to be a switchover candidate than a logical standby when multiple standby types exist in a given configuration.

The sections that follow describe how to perform both switchover and failover operations.

Implement Role Switchover Operation

A switchover operation is a reversible role transition between the primary database and one of its standby databases. With a switchover, there is no data loss, and the old primary database will remain in the Data Guard configuration as a standby database. This operation is generally used to reduce primary database downtime during a scheduled outage. This may include outages related to operating system or hardware upgrades. After the required tasks for the planned outage are completed, the original roles for the primary and standby database can be transitioned back to normal.

This example uses the same two-node Data Guard configuration that was created in the guide Data Guard Configuration Example - (Oracle 10g, Logical Standby) — a primary database (modesto on host vmlinux1.idevelopment.info) sending redo to a single logical standby database (turlock on host vmlinux2.idevelopment.info). The Data Guard protection mode for the primary database is set to maximum performance which means the standby database does not receive any redo data until the primary database fills its current online redo and archives it. Under normal operation, the data received and applied to the standby database is only as current as the last archived redo log sent from the primary database.

In this section, I will be performing a role transition where the modesto database running on host vmlinux1 will transition its role from primary to logical standby while the turlock database on host vmlinux2 will transition its role from logical standby to primary using the graceful switchover operation. This will all occur without any loss of data!

Although I have already stated this and knowing that it doesn't apply to the Data Guard configuration described in this article, it is worth mentioning again. If the configuration includes a primary, a physical standby, and a logical standby, and a switchover is performed on the logical standby, the physical standby will no longer be a part of the configuration and must be rebuilt. In the same scenario, if a switchover operation is performed on the physical standby, the logical standby remains in the Data Guard configuration and does not need to be rebuilt.

There are three methods that can be used to perform a switchover: SQL*Plus, the Data Guard Broker, and Grid Control. The method used in this example will use only SQL*Plus.

A switchover operation takes place in two phases:

  1. The existing primary database is transitioned to a standby role. After this first phase is implemented, the Data Guard configuration temporarily has two standby databases.

  2. A standby database (in this example, there was only one logical standby database) is transitioned to the primary role.

Switchover Preparation

The key to a successful switchover is planning and adequate testing. It is highly recommended to test any type of architectural change of this magnitude in a test environment before attempting it on a production system!

The following is a list of checks that should be completed prior to performing a switchover operation.

Initialization Parameters

One of the most important checks involves verifying that the initialization parameters for the primary and standby support both roles.

For example, make certain that the VALID_FOR attribute for the LOG_ARCHIVE_DEST_n parameter (dest_id=2 in my configuration) is configured to assume the primary role when its role is changed. Additionally, verify that the service and db_unique_name are valid for each definition.


[ PRIMARY ] log_archive_dest_2='service=turlock.idevelopment.info valid_for=(online_logfiles,primary_role) db_unique_name=turlock' [ STANDBY ] log_archive_dest_2='service=modesto.idevelopment.info valid_for=(online_logfiles,primary_role) db_unique_name=modesto'

If the Data Guard configuration contains other standby databases (in this case, only logical databases need apply!), verify that the new primary database contains a LOG_ARCHIVE_DEST_n definition for each logical standby database. This ensures each logical standby database will continue to receive redo data from the new primary database.

TNS Alias

Verify that the primary and standby host each have valid TNS aliases that point to one another. In addition, make certain that each of the TNS aliases are the ones used in the LOG_ARCHIVE_DEST_n parameter(s).

For the purpose of this example, the following TNS aliases have been defined and tested on both the primary and standby host:


MODESTO.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = modesto.idevelopment.info) ) ) TURLOCK.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux2.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = turlock.idevelopment.info) ) )

Verify Tempfiles on Primary and Standby

Verify that both the primary and standby database temporary tablespaces are defined with tempfiles:


[ PRIMARY ] SQL> select ts.name as "Tablespace", tf.name as "Tempfile", tf.status as "Status" 2 from v$tablespace ts join v$tempfile tf using (ts#); Tablespace Tempfile Status ----------- ------------------------------------------------------- ------- TEMP /u02/oradata/MODESTO/datafile/o1_mf_temp_6hc6v3jd_.tmp ONLINE [ STANDBY ] SQL> select ts.name as "Tablespace", tf.name as "Tempfile", tf.status as "Status" 2 from v$tablespace ts join v$tempfile tf using (ts#); Tablespace Tempfile Status ----------- ------------------------------------------------------- ------- TEMP /u02/oradata/TURLOCK/datafile/o1_mf_temp_6lwcr08r_.tmp ONLINE

Verify Redo is Current with Primary

Make certain that the standby (physical or logical) is applying changes from the primary. Also verify that the application of redo is current with the primary.

From the primary database, perform a log switch and then verify the transmissions of the archived redo log file was successful. If the transmission was successful, the status of the destination will be VALID as shown below.


SQL> alter system switch logfile; System altered. SQL> select status, error from v$archive_dest where dest_id = 2; STATUS ERROR --------- --------------------------------------------------------- VALID

When running in one of the zero data loss modes (Maximum Availability or Maximum Protection), you should verify that the target standby database that will be used in the switchover is synchronized with the primary by examining the V$ARCHIVE_DEST_STATUS view on the primary database.

Note: The Data Guard configuration in this guide is not running in one of the zero data loss modes. In the following query, I decided to synthesize the target standby record for turlock to show what it would display if the configuration was running in either Maximum Availability or Maximum Protection mode.


SQL> select db_unique_name, protection_mode, synchronization_status, synchronized 2 from v$archive_dest_status; DB_UNIQUE_NAME PROTECTION_MODE SYNCHRONIZATION_STATUS SYNCHRONIZED -------------------- -------------------- ---------------------- ------------ modesto MAXIMUM PERFORMANCE CHECK CONFIGURATION NO turlock MAXIMUM AVAILABILITY CHECK CONFIGURATION YES modesto MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO NONE MAXIMUM PERFORMANCE CHECK CONFIGURATION NO 10 rows selected.

Using the above listing, you would be able to safely assume that the target standby database (actually, the only standby database) turlock is synchronized with the primary database when running in one of the zero data loss modes. Just this information alone would be sufficient as verification that the target standby has received all of its redo. However, if SYNCHRONIZED does not say YES or you are running in Maximum Performance mode (like the configuration used in this guide), you need to perform some additional work to verify the redo status. To start, determine the last archived redo log sequence number by executing the following SQL on the primary database:


SQL> select thread#, sequence#, status from v$log; THREAD# SEQUENCE# STATUS ---------- ---------- ------------ 1 6283 INACTIVE 1 6284 CURRENT 1 6282 INACTIVE

After determining the last archived redo log sequence number from the primary (6284), query the V$MANAGED_STANDBY view from the target standby database to determine the current sequence that the primary is sending.


SQL> select client_process, process, sequence#, status 2 from v$managed_standby; CLIENT_PROCESS PROCESS SEQUENCE# STATUS -------------- --------- ---------- ------------ ARCH ARCH 2777 CLOSING ARCH ARCH 6282 CLOSING ARCH ARCH 2778 CLOSING ARCH ARCH 6283 CLOSING UNKNOWN RFS 0 IDLE UNKNOWN RFS 0 IDLE UNKNOWN RFS 0 IDLE 7 rows selected.

The CLIENT_PROCESS / PROCESS columns of V$MANAGED_STANDBY will show either [ARCH to ARCH] or [LGWR to RFS] depending on how Log Transport Services is configured on the primary. The Data Guard configuration in this guide was set up to use the ARCH process for Log Transport Services. Verify that the last archived redo log sequence number on the target standby (the maximum SEQUENCE#) is no more than 1 less than the last archived redo log sequence number on the primary. If the target standby is not current and not receiving the current redo, then a switchover is not possible.

Note that if primary database is RAC, you should see multiple [ARCH to ARCH] or [LGWR to RFS] connections; one for each thread from the primary. You must validate that each thread from the primary is caught up on the target standby database before attempting the switchover.

The last check to verify that the logical standby is caught up with the primary is to query the V$LOGSTDBY_PROGRESS view from the target standby database.


SQL> select applied_scn, latest_scn from v$logstdby_progress; APPLIED_SCN LATEST_SCN ----------- ---------- 3093982 3093982

If the APPLIED_SCN is in sync with the LATEST_SCN, then the logical standby is caught up and it is safe to start the switchover process. However, if the APPLIED_SCN is behind the LATEST_SCN, it's possible that there is a gap in the redo sequence. You can check for a gap by running the following statement on the target logical standby database:


SQL> select status from v$logstdby_process where type = 'READER'; STATUS -------------------------------------------------------------- ORA-16240: Waiting for logfile (thread# 1, sequence# 6292)

If a gap does exist, this will need to be resolved before attempting the switchover.

In order ensure an efficient switchover process, the target logical standby database should be open and SQL Apply should be started and caught up with the current redo stream:


[ STANDBY ] SQL> startup SQL> alter database start logical standby apply immediate;

Verify Standby Redo Log (SRL) Files on the Primary

The logical standby database used in this example is configured with standby redo log (SRL) files. Verify that SRL files have been created on the current primary database so that it can receive the redo when it becomes a standby.


SQL> select group#, status, type, count(*) as "Members" 2 from v$logfile 3 group by group#, status, type 4 order by group#; GROUP# STATUS TYPE Members ---------- ------- ------- ---------- 1 ONLINE 2 2 ONLINE 2 3 ONLINE 2 4 STANDBY 2 5 STANDBY 2 6 STANDBY 2 7 STANDBY 2

Cancel Jobs and Backups

Cancel any running jobs on the primary database and disable any new ones from starting. This can include RMAN backups, maintenance jobs, etc. If RMAN backups are configured to run from the standby database, stop them as well as they can interfere with the switchover process. Use the following query to identify any RMAN backups (and the process ID) running on the primary or standby database:


SQL> select s.process, r.operation, r.status, r.mbytes_processed as "PCT", s.status 2 from v$rman_status r join v$session s using (sid); PROCESS OPERATION STATUS PCT STATUS -------- --------------- ------- ---------- -------- 4033 RMAN RUNNING 0 INACTIVE 4033 BACKUP RUNNING 33.96 INACTIVE

Oracle Real Application Clusters (RAC)

If the primary or standby database is configured using Real Application Clusters (RAC), verify that all but the one primary database instance and/or one standby instance are shut down. When using Oracle Database 10g Release 2 (10.2), and the COMPATIBLE initialization parameter is set to 10.2.0.2 or higher (10.2.0.x), only one primary database instance and only one logical standby instance can be up while performing the switchover operation. All other instances need to be shutdown before attempting the switchover. If COMPATIBLE is not set to 10.2.0.2 or you are using a prior version of Oracle, then not only do you need to shut down the auxiliary instances, but you must also disable their threads on both the primary database and the target logical standby database. Once the switchover is complete, you can re-enable all threads (if the threads needed to be disabled) and restart the instances.

Perform Switchover Operation with a Logical Standby

Use the following steps to perform a switchover (role transition) between the current primary database and logical standby database.

Unlike a role transition involving a physical standby database, the primary database and any logical standby databases in the Data Guard configuration (including the logical standby database involved in the role transition) do not have to be shut down and restarted throughout the switchover process. In addition, it is not required to use the WITH SESSION SHUTDOWN on either the current primary or on the logical standby database which is targeted to become the new primary database because there is no need to log users off to perform a switchover with a logical standby database.

  1. Verify it is possible to perform a switchover on the primary database.

    On the primary database, query the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database to verify it is possible to perform a switchover.


    SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE

    A value of TO STANDBY or SESSIONS ACTIVE in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the logical standby role. If one of these values is not displayed, then verify the Data Guard configuration is functioning correctly as described in the Switchover Preparation section to this guide.

  2. Prepare the current primary database for the switchover.

    Before you can perform a switchover with a logical standby, you must first prepare the two databases for the operation. The ability to prepare the primary and logical standby for a switchover was first introduced in Oracle Database 10g and was added to help in reducing the time it takes to complete the switchover operation and to prevent data loss. Unlike a physical standby database, which is an exact block-for-block copy of the primary database, a logical standby database could be different, and once the roles have been reversed, the new logical standby database needs to know what the new primary looks like so SQL Apply can process the redo stream.

    Issue the following SQL statement on the primary database to prepare the current primary database for the logical standby database role:


    SQL> alter database prepare to switchover to logical standby; Database altered.

    The above statement notifies the current primary database that a role transition could occur and that it will soon switch to the logical standby role and begin receiving redo data from a new primary database. You perform this step on the primary database in preparation to receive the LogMiner Multiversioned Data Dictionary to be recorded in the redo stream of the current logical standby database, as described in the next step.

    If the preparation operation is successful, the value PREPARING SWITCHOVER is displayed in the SWITCHOVER_STATUS column of the V$DATABASE view indicating that the primary is ready.


    SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- PREPARING SWITCHOVER

  3. Prepare the target logical standby database for the switchover.

    The next step is performed on the logical standby database which is targeted to become the new primary database and tell it to send the preparation information to the primary in its redo stream. Use the following statement to build a LogMiner Multiversioned Data Dictionary on the current logical standby database that is the target of the switchover:


    SQL> alter database prepare to switchover to primary; Database altered.

    This statement also starts redo transport services on the logical standby database that begins transmitting its redo data to the current primary database and to other standby databases in the Data Guard configuration. The sites receiving redo data from this logical standby database accept the redo data but they do not apply it.

    Take note that you do not need to tell the logical standby where to send the redo because it knows where the primary is and only one primary database can exist in a Data Guard configuration.

    Depending on the work to be done and the size of the database, the switchover can take some time to complete.

    The SWITCHOVER_STATUS column of the V$DATABASE view on the logical standby database initially shows PREPARING DICTIONARY while the LogMiner Multiversioned Data Dictionary is being recorded in the redo stream. Once this has completed successfully, the SWITCHOVER_STATUS column shows PREPARING SWITCHOVER.


    SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- PREPARING SWITCHOVER

     

    The preparation done so far is necessary to guarantee the safety of your data if a failure were to occur after the switchover but before the new logical standby database can process new transactions. The new logical standby database needs to know how to apply the redo from the new primary the moment new primary transactions start to generate redo. By skipping the preparatory steps explained in this guide, you will be generating redo from business transactions that will be sent to the new logical standby database. The new LogMiner Multiversioned Data Dictionary would then be behind that redo, and if you had a failure of the new primary database before the dictionary was sent to the new logical standby, a failover would result in data loss.

  4. Ensure the current primary database is ready for the future primary database's redo stream.

    Before you can complete the role transition of the primary database to the logical standby role, verify the LogMiner Multiversioned Data Dictionary was received by the primary database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database. Without the receipt of the LogMiner Multiversioned Data Dictionary, the switchover cannot proceed, because the current primary database will not be able to interpret the redo records sent from the future primary database. The SWITCHOVER_STATUS column shows the progress of the switchover.

    When the query returns the TO LOGICAL STANDBY value, you can proceed with the next step. For example:


    SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO LOGICAL STANDBY

     

    At this point, you are not entirely committed to the switchover operation as it is still possible to cancel the prepare phase. The switchover operation can be cancelled by issuing the following statements in the following order:

    1. Cancel switchover on the primary database:

      SQL> alter database prepare to switchover cancel;
    2. Cancel the switchover on the logical standby database:

      SQL> alter database prepare to switchover cancel;

    The above statements will rewind everything that the prepare has done and put the primary database back into its normal TO STANDBY or SESSIONS ACTIVE state.

  5. Switch the primary database to the logical standby database role.

    To complete the role transition of the primary database to a logical standby database, issue the SQL statement below:


    SQL> alter database commit to switchover to logical standby; Database altered.

    If many users are performing long running read/write transactions on the the primary database when you issue the ALTER DATABASE statement above, you may see a significant stall to the end users and the time it takes to complete the switchover operation. This statement waits for all current transactions on the primary database to end and prevents any new users from starting new transactions, and establishes a point in time where the switchover will be committed.

    Executing this statement will also prevent users from making any changes to the data being maintained in the logical standby database. To ensure faster execution, verify the primary database is in a quiet state with no update activity before issuing the switchover statement (for example, have all users temporarily log off the primary database). You can query the V$TRANSACTIONS view for information about the status of any current in-progress transactions that could delay execution of this statement.

    The primary database has now undergone a role transition to run in the standby database role.

    When a primary database undergoes a role transition to a logical standby database role, you do not have to shut down and restart the database.

     

    SQL Apply GUARD

    When the read/write transactions have all committed, the switchover completes on the primary and Data Guard enables the SQL Apply GUARD to prevent any further updates to the data, as this is now a logical standby database.

    After the switchover, the SQL Apply GUARD is enabled to its highest level (ALL) which prevents any user other than SYSDBA from updating any data on the logical standby database, not just the tables SQL Apply is maintaining from the primary database. If users need to modify data on the new logical standby database and had previously lowered the SQL Apply guard to STANDBY or NONE, you will have to manually set the GUARD to either STANDBY or NONE on the new logical standby.

    Click here for further information on protecting replicated tables on a logical standby from user modifications.

  6. Ensure all available redo has been applied to the target logical standby database that is about to become the new primary database.

    After you complete the role transition of the primary database to the logical standby role and the switchover notification is received by the standby databases in the configuration, you should verify the switchover notification was processed by the target logical standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the target standby database. Once all available redo records are applied to the logical standby database, SQL Apply automatically shuts down in anticipation of the expected role transition.

    The SWITCHOVER_STATUS value is updated to show progress during the switchover. When the status is TO PRIMARY, you can proceed with the next step.

    For example:


    SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY

  7. Switch the target logical standby database to the primary database role.

    On the logical standby database that you want to switch to the primary role, use the following SQL statement to switch the logical standby database to the primary role:


    SQL> alter database commit to switchover to primary; Database altered.

    There is no need to shut down and restart any logical standby databases that are in the Data Guard configuration. Other existing logical standby databases will continue to function normally after a switchover completes. All existing physical standby databases, however, are rendered unable to participate in the Data Guard configuration after the switchover and will need to be rebuilt (if needed).

  8. Start SQL Apply on the new logical standby database.

    On the new logical standby database, start SQL Apply:


    SQL> alter database start logical standby apply immediate; Database altered.

    If either the primary or standby databases were configured with Oracle RAC, all auxiliary instances can now be started.

    If any user objects where added to the original logical standby database (which is now the primary) outside of those being maintained by SQL Apply, they obviously will not exist in the new logical standby, and their redo would be skipped until you create and instantiate them again in the new logical standby database.

Implement Role Failover Operation

A failover operation transitions a standby database to the primary role in response to a failure on the primary database. During a failover operation, one of the standby databases in the Data Guard configuration will transition its role to the primary database and the old primary database is rendered unable to participate in the configuration. That's not to say that the old primary database can never be a part of the Data Guard configuration. It can certainly be rebuilt and entered back into the configuration as a standby database. Depending on the size of the database, rebuilding the old primary and entering it back into the Data Guard configuration could be an expensive operation as it would potentially involve a lot of time and resources. Prior to Oracle Database 10g, however, this was the only option available.

Starting with Oracle Database 10g and the introduction of Flashback Database, it is now possible to rewind the old primary database back in time to the point just before where the failure occurred and from there, bring it back into the configuration as a standby. After the Flashback Database operation, the roles can be changed using switchover, thus bringing the primary database (and the Data Guard configuration) back to its original state.

 

Use the following guide to learn more about using Flashback Database after a failover operation:

Using Flashback Database After a Failover - (Oracle 10g)

You would typically choose the failover method only when the primary database becomes unavailable and there is no possibility of restoring it to a service within a reasonable amount of time. As a DBA, your chief responsibility is to provide the best option that results in the least amount of interruption and data loss. For example, if the primary database is down as the result of a failure, it may be possible to restart the primary and perform crash recovery faster than it would be to perform a failover to a disaster site. With most companies, the decision to perform a failover will be made by management along with the advice from the DBA. As part of any disaster recovery plan, the steps discussed in this section should be tested at a minimum on a quarterly basis within an organization.

The amount of data loss incurred by a failover operation is predicated on the protection mode under which the old primary database was operating under. A failover can be performed after all or most of the data was last propagated to the standby database after the primary database became unavailable.

During a failover operation involving a logical standby database:

This example uses the same two-node Data Guard configuration that was created in the guide Data Guard Configuration Example - (Oracle 10g, Logical Standby) — a primary database (modesto on host vmlinux1.idevelopment.info) sending redo to a single logical standby database (turlock on host vmlinux2.idevelopment.info). The Data Guard protection mode for the primary database is set to maximum performance which means the standby database does not receive any redo data until the primary database fills its current online redo and archives it. Under normal operation, the data received and applied to the logical standby database is only as current as the last archived redo log sent from the primary database.

In this section, I will be performing a role transition where the modesto database running on host vmlinux1 (the current primary database) is considered unavailable and rendered unusable while the turlock database on vmlinux2 (the current logical standby database) will transition its role from standby to primary using a failover operation. Since the primary database was configured for maximum performance, there will always be a good likelihood that data loss will occur when transitioning the logical standby database to the primary role in this configuration.

Failover Preparation

The key to a successful failover is planning and adequate testing. It is highly recommended to test any type of architectural change of this magnitude in a test environment before attempting it on a production system!

The following is a list of checks that should be completed prior to performing a failover operation.

Initialization Parameters

One of the first steps before attempting a failover operation is to identify all initialization parameters that must be changed to complete the role transition. For example, make certain that the VALID_FOR attribute for the LOG_ARCHIVE_DEST_n parameter is configured to assume the primary role when its role is changed. Additionally, verify that the service and db_unique_name are valid for each definition.

When a failover operation occurs and the standby database being transitioned was configured for maximum protection mode, you must ensure that all other standby databases in the Data Guard configuration that were also set up for maximum protection continue to receive logs from the new primary database (with the exception of any physical standby databases as they cannot participate in the Data Guard configuration after a role transition using a logical standby). With the configuration being implemented in this guide there are no other standby databases and most of the initialization parameters are already in place for the selected standby database to assume the primary role. Given the fact that the old primary database will be assumed unavailable after the failover, I set the state of log_archive_dest_2 to defer on the logical standby database selected for role transition:


... log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=turlock' log_archive_dest_2='service=modesto.idevelopment.info valid_for=(online_logfiles,primary_role) db_unique_name=modesto' log_archive_dest_3='location=/u04/oracle/oraarch/TURLOCK valid_for=(standby_logfiles,standby_role) db_unique_name=turlock' log_archive_dest_state_1='ENABLE' log_archive_dest_state_2='DEFER' log_archive_dest_state_3='ENABLE' ...

 

If the Data Guard configuration contains other logical standby databases configured for maximum protection mode, verify that the new primary database contains a valid LOG_ARCHIVE_DEST_n definition for each logical standby database. This ensures each logical standby database will continue to receive redo data from the new primary database.

Recover Any Un-applied Redo

Before performing any failover operation, transfer as much available and un-applied redo data as possible from the primary database to the standby database(s) in order to reduce the amount of data loss.

Oracle Real Application Clusters (RAC)

If the primary or standby database is configured using Real Application Clusters (RAC), verify that all but the one primary database instance and/or one standby instance are shut down. When using Oracle Database 10g Release 2 (10.2), and the COMPATIBLE initialization parameter is set to 10.2.0.2 or higher (10.2.0.x), only one primary database instance and only one logical standby instance can be up while performing the failover operation. All other instances need to be shutdown before attempting the failover. If COMPATIBLE is not set to 10.2.0.2 or you are using a prior version of Oracle, then not only do you need to shut down the auxiliary instances, but you must also disable their threads on both the primary database and the target logical standby database. Once the failover is complete, you can re-enable all threads (if the threads needed to be disabled) and restart the instances.

Perform Failover Operation to a Logical Standby

Use the following steps to perform a failover to a logical standby database. As with a logical standby switchover, you do not have to shut down and restart the logical standby database which is targeted to become the new primary database nor do you need to terminate any user sessions who might be attached to the logical standby when performing a failover.

  1. Copy and register any missing archived redo log files to the target logical standby database slated to become the new primary database.

    Depending on the condition of the components in the configuration, you might have access to the archived redo log files on the primary database or other standby databases in the Data Guard configuration. If so, perform the following:

    1. Determine if any archived redo log files are missing on the logical standby database. From the logical standby database targeted to become the new primary database, query the DBA_LOGSTDBY_LOG view to determine if the logical standby is missing any archived redo log files.


      SQL> select thread#, sequence#, applied from dba_logstdby_log; THREAD# SEQUENCE# APPLIED ---------- ---------- -------- 1 6482 CURRENT

    2. Copy any missing archived redo log files or log files that are higher than the last sequence received from the primary database (if available) or other standby databases in the Data Guard configuration to the target logical standby database.


      [oracle@vmlinux2 ~]$ cd /u03/flash_recovery_area/TURLOCK/archivelog/2011_02_16 [oracle@vmlinux2 2011_02_16]$ scp vmlinux1:/u03/flash_recovery_area/MODESTO/archivelog/2011_02_16/o1_mf_1_648[345]*.arc . o1_mf_1_6483_6or2qw02_.arc 100% 51KB 51.0KB/s 00:00 o1_mf_1_6484_6or2qxbd_.arc 100% 1024 1.0KB/s 00:00 o1_mf_1_6485_6or2r0sr_.arc 100% 1536 1.5KB/s 00:00

    3. Register the copied archived log files with the logical standby database by issuing the following statement.


      SQL> alter database register logical logfile '/u03/flash_recovery_area/TURLOCK/archivelog/2011_02_16/o1_mf_1_6483_6or2qw02_.arc'; Database altered. SQL> alter database register logical logfile '/u03/flash_recovery_area/TURLOCK/archivelog/2011_02_16/o1_mf_1_6484_6or2qxbd_.arc'; Database altered. SQL> alter database register logical logfile '/u03/flash_recovery_area/TURLOCK/archivelog/2011_02_16/o1_mf_1_6485_6or2r0sr_.arc'; Database altered.

       

      About Partial Archived Redo Logs

      It is possible to copy over and register what is known as a partial archived redo log file. A partial archived redo log file contains all of the primary database redo data received by the standby database when the primary database fails, but the archived redo log is not automatically registered in the standby database.

      When you register a partial archived redo log, it prevents the recovery of the standby redo logs (if they exist). Therefore, whether or not you have registered a partial archived redo log determines which failover command will be necessary to run (next step).

      You will know if you registered a partial archived redo log if you get receive the following message when attempting to register the archived redo log:

      Register archivelog 'filespec1' was created due to a network disconnect;
      archivelog contents are valid but missing subsequent data

  2. Ensure all available archived redo log files were applied.

    On the logical standby database you are transitioning to the primary role, verify all available archived redo log files were applied by querying the V$LOGSTDBY_PROGRESS view.


    SQL> select applied_scn, latest_scn from v$logstdby_progress; APPLIED_SCN LATEST_SCN ----------- ---------- 3176153 3176153

    When the APPLIED_SCN and LATEST_SCN values are equal, all attainable data is applied and the logical standby database now contains as much data as possible from the primary database.

     

    If SQL Apply is not active on the target logical standby database, issue the following statement on the target standby database to start SQL Apply:

    SQL> alter database start logical standby apply finish;
    
    Database altered.

  3. Enable remote destinations.

    As mentioned in the Failover Preparation section to this guide, identify the initialization parameters that correspond to the remote logical standby destinations for the new primary database, and manually enable archiving of redo data for each of these destinations.

  4. Activate the new primary database.

    Issue the following statement on the target logical standby database (that you are transitioning to the new primary role):


    SQL> alter database activate logical standby database finish apply; Database altered.

    This statement stops the RFS process, applies remaining redo data in the standby redo log file before the logical standby database becomes a primary database, stops SQL Apply, converts the control file to a primary control file, removes the GUARD so that all users can update all data in the database as normal, and activates the database in the primary database role.

    If the FINISH APPLY clause is not specified, then un-applied redo from the current standby redo log file will not be applied before the standby database becomes the primary database. Omitting the FINISH APPLY clause should only be used to prevent all the redo from being applied at the time of the failover, after a FLASHBACK DATABASE for example.

  5. Prepare to recover the other standby databases.

    Depending on how much redo data you were able to apply to the new primary database, you might be able to add other existing logical standby databases back into the Data Guard configuration to serve as standby databases for the new primary database. Perform the following steps on each logical standby database to prepare to add it back into the Data Guard configuration:

    1. Create a database link on each logical standby database.

      Use the ALTER SESSION DISABLE GUARD statement to bypass the database guard and allow modifications to the tables in the logical standby database. For example, the following creates a database link to the primary database turlock:


      SQL> alter session disable guard; SQL> create database link turlock 2 connect to <username> identified by <password> using 'turlock.idevelopment.info'; SQL> alter session enable guard;

      The database user account specified in the CREATE DATABASE LINK statement must have the SELECT_CATALOG_ROLE role granted to it on the primary database.

       

      You must perform the dictionary build operation after the primary database has been opened but before any DDL statements have been executed. If any DDL statements are executed before the dictionary build operation is performed, the backup will be invalidated as a source for creating a logical standby database.

    2. Verify the database link.

      On the logical standby database, verify the database link was configured correctly by executing the following query using the database link:


      SQL> select * from dba_logstdby_parameters@turlock;

      If the query succeeds, then that confirms the database link created in Step 1 can be used during role transitions.

  6. Start SQL Apply.

    Start SQL Apply on any other logical standby database in your Data Guard configuration.

    For example, the following statement starts SQL Apply on a logical standby database that points to the new primary database (turlock):


    SQL> alter database start logical standby apply new primary turlock; Database altered.

    When this statement completes, all remaining archived redo log files will have been applied. Depending on the work to be done, this operation can take some time to complete.

    If the ORA-16109 error is returned, you must re-create the logical standby database from a backup copy of the new primary database, and then add it to the Data Guard configuration.

    The following example shows a failed attempt to start SQL Apply on a logical standby database in the new configuration where turlock is the service name that points to the new primary database:


    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY turlock; ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY turlock * ERROR at line 1: ORA-16109: failed to apply log data from previous primary

  7. Back up the new primary database.

    Back up the new primary database immediately after the Data Guard database failover. Immediately performing a backup is a necessary safety measure, because you cannot recover changes made after the failover without a complete backup copy of the database.

  8. Restore the failed primary database.

    After performing a failover, you can optionally restore the failed primary database as a new standby database using one of the following methods:

    Once the failed primary database has been restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles.

Further Reading

Additional information on Oracle 10g Release 2 Data Guard can be found in the Data Guard Concepts and Administration. This guide is available from the Oracle Documentation Library website located at the following address http://download.oracle.com/docs/cd/B19306_01/server.102/b14239.pdf.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 17-Apr-2014 02:42:54 EDT
Page Count: 1084