Skip Headers

Oracle Data Guard Concepts and Administration
Release 2 (9.2)

Part Number A96653-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Troubleshooting the Standby Database

This appendix provides help troubleshooting a standby database. This appendix contains the following sections:

A.1 Problems During Standby Database Preparation

If you encounter a problem during standby database preparation, it will probably be one of the following:

A.1.1 The Standby Archive Destination Is Not Defined Properly

If the STANDBY_ARCHIVE_DEST initialization parameter is not defined as a valid directory name on the standby site, the Oracle database server will not be able to determine the directory in which to store the archived redo logs. Check the DESTINATION and ERROR columns in the V$ARCHIVE_DEST view. For example, enter:


Make sure the destination is valid.

A.1.2 The Standby Site Does Not Receive Logs Archived by the Primary Database

If the standby site is not receiving the logs, the first thing you should do is obtain information about the archiving status of the primary database by querying the V$ARCHIVE_DEST view. Check especially for error messages. For example, enter the following query:

  2> STATUS "DB_status",
  3> DESTINATION "Archive_dest",
  4> ERROR "Error"

ID DB_status Archive_dest                   Error   
-- --------- ------------------------------ ------------------------------------
 1  VALID    /vobs/oracle/work/arc_dest/arc                          
 2  ERROR    standby1                       ORA-16012: Archivelog standby database identifier mismatch  
 3  INACTIVE                            
 4  INACTIVE                    
 5  INACTIVE                                           
5 rows selected.

If the output of the query does not help you, check the following list of possible issues. If any of the following conditions exist, the primary database will fail to archive to the standby site:

A.1.3 You Cannot Mount the Physical Standby Database

If any of the following conditions exist, you cannot mount the physical standby database:

A.2 Log Destination Failures

If you specify REOPEN for an OPTIONAL destination, it is possible for the Oracle database server to reuse online redo logs even if there is an error. If you specify REOPEN for a MANDATORY destination, the log transport services component stalls the primary database when it cannot successfully archive redo logs.

The REOPEN attribute is required when you use the MAX_FAILURE attribute. Example A-1 shows how to set a retry time of 5 seconds and limit retries to 3 times.

Example A-1 Setting a Retry Time and Limit


Using the ALTERNATE attribute of the LOG_ARCHIVE_DEST_n parameter, you can specify alternate archive destinations. An alternate archive destination can be used when the archiving of an online redo log to a standby site fails. If archiving fails and the NOREOPEN attribute has been specified, or the MAX_FAILURE attribute threshold has been exceeded, log transport services will attempt to archive redo logs to the alternate destination on the next archiving operation.

Use the NOALTERNATE attribute to prevent the original archive destination from automatically changing to an alternate archive destination when the original archive destination fails.

Example A-2 shows how to set the initialization parameter file so that a single, mandatory, local destination will automatically fail over to a different destination if any error occurs.

Example A-2 Specifying an Alternate Destination


If the LOG_ARCHIVE_DEST_1 destination fails, the archiving process will automatically switch to the LOG_ARCHIVE_DEST_2 destination at the next log switch on the primary database.

A.3 Ignoring Logical Standby Database Failures

An important skip tool is DBMS_LOGSTDBY.SKIP_ERROR. Depending on how important a table is, you might want to do one of the following:

Taking one of these actions prevents the SQL apply operations from stopping. Later, you can query the DBA_LOGSTDBY_EVENTS view to find and correct any problems that exist.

A.4 Problems Switching Over to a Standby Database

If you encounter a problem switching over from a primary database to a standby database, it will probably be one of the following:

A.4.1 Switchover Fails

ALTER DATABASE COMMIT TO SWITCHOVER failed with ORA-01093 error "Alter database close only permitted with no sessions connected."

This error occurs because the COMMIT TO SWITCHOVER statement implicitly closed the database and, if there are any other user sessions connected to the database, the close fails.

Action: Make sure all user sessions are disconnected from the database. You can query the V$SESSION fixed view to see what sessions are still around. For example:


---------- --------- ------------------------------------------------
         1 26900     oracle@dbuser-sun (PMON)
         2 26902     oracle@dbuser-sun (DBW0)
         3 26904     oracle@dbuser-sun (LGWR)
         4 26906     oracle@dbuser-sun (CKPT)
         5 26908     oracle@dbuser-sun (SMON)
         6 26910     oracle@dbuser-sun (RECO)
         7 26912     oracle@dbuser-sun (ARC0)
         8 26897     sqlplus@dbuser-sun (TNS V1-V3)
        11 26917     sqlplus@dbuser-sun (TNS V1-V3)

9 rows selected.

In the previous example, the first seven sessions are all server background processes. Among the two SQL*Plus sessions, one is the current SQL*Plus session issuing the query, and the other is an extra session that should be disconnected before the switchover operation.

A.4.2 Recovering After An Unsuccessful Switchover Operation

In most cases, following the steps described in Section 7.2.1 will result in a successful switchover operation. However, if the switchover operation is initially unsuccessful, you might still be able to use one of the following recovery options to complete the switchover operation successfully.

Option 1: Continue the current switchover operation.

If the switchover operation does not complete successfully, you can query the SEQUENCE# column in the V$ARCHIVED_LOG view to see if the last archived log was archived and applied on the old physical standby database. If the last log was not archived to the old physical standby database, you can manually copy the archived log from the old primary database to the old physical standby database and register it with the SQL ALTER DATABASE REGISTER LOGFILE filespec statement. If you then start up the managed recovery process, the archived log will be applied automatically. Query the SWITCHOVER_STATUS column in the V$DATABASE view. The TO PRIMARY value in the SWITCHOVER_STATUS column verifies that switchover to the primary role is now possible.

1 row selected 
See Also:

Chapter 14 for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view

To continue with the switchover operation, return to Section 7.2.1 Step 5, and try again to switch the target physical standby database to the primary role.

Option 2: Rollback the unsuccessful switchover operation and start over.

In situations where an error has occurred and it is not possible to continue with the switchover operation, it might still be possible to revert the new physical standby back to the primary role by using the following steps:

  1. When the switchover operation to change the role from primary to standby was initiated, a trace file was written in the log directory. This trace file contains the SQL statements required to re-create the original primary control file. Locate the trace file and extract the SQL statements into a temporary file. Execute the temporary file from SQL*Plus. This will revert the new physical standby database back to the primary role.
  2. Shut down the original physical standby database.
  3. Create a new physical standby control file. This is necessary to resynchronize the primary database and physical standby database. Copy the standby control file to the original physical standby site.

    See Also:

    Section 3.2.3 for information about creating a standby control file.

  4. Restart the original physical standby instance.

    If this procedure is successful and archive gap management is enabled, the FAL processes will start and re-archive any missing archived redo logs to the physical standby database. Force a log switch on the primary database and examine the alert logs on both the primary database and physical standby database to ensure that the archived redo log sequence numbers are correct.

    See Also:

    Section 6.4 for information about archive gap management and Section 6.7 for information about locating the trace files.

  5. Try the switchover operation again.

    At this point, the Data Guard configuration has been rolled back to its initial state, and you can try the switchover operation again (after correcting any problems that might have led to the initial unsuccessful switchover operation).

A.4.3 Startup of Second Physical Standby Database Fails

Suppose the standby database and the primary database reside on the same site. After both the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY and the ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY statements are successfully executed, shut down and restart the physical standby database and the primary database. However, the startup of the second database fails with ORA-01102 error "cannot mount database in EXCLUSIVE mode."

This could happen during the switchover if you forget to set the LOCK_NAME_SPACE parameter in the initialization parameter file that is used by the standby database (that is, the original primary database). If the LOCK_NAME_SPACE parameter of the standby database is not set, the standby and the primary databases both use the same mount lock and cause the ORA-01102 error during the startup of the second database.

Action: Add LOCK_NAME_SPACE=unique_lock_name to the initialization parameter file used by the physical standby database and shut down and restart both the standby and the primary databases.

A.4.4 Archived Redo Logs Are Not Applied After a Switchover

The archived redo logs are not applied to the standby database after the switchover.

This might happen because some environment or initialization parameters have not been properly set after the switchover.


A.4.5 Switchover Fails When SQL Sessions Are Active

If you do not include the WITH SESSION SHUTDOWN clause as a part of the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement, active SQL sessions might prevent a switchover from being processed. Active SQL sessions can include other Oracle processes.

When sessions are active, an attempt to switch over fails with the following error message:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Action: Query the V$SESSION view to determine which processes are causing the error. For example:

---------  --------  ------------------------------------------------ 
        7      3537  oracle@nhclone2 (CJQ0)
 6 rows selected.

In the previous example, the JOB_QUEUE_PROCESSES parameter corresponds to the CJQ0 process entry. Because the job queue process is a user process, it is counted as a SQL session that prevents switchover from taking place. The entries with no process or program information are threads started by the job queue controller.

Verify that the JOB_QUEUE_PROCESSES parameter is set using the following SQL statement:

NAME                           TYPE      VALUE
------------------------------ -------   -------------------- 
job_queue_processes            integer   5

Then, set the parameter to 0. For example:

Statement processed.

Because JOB_QUEUE_PROCESSES is a dynamic parameter, you can change the value and have the change take effect immediately without having to restart the instance. You can now retry the switchover procedure.

Do not modify the parameter in your initialization parameter file. After you shut down the instance and restart it after switchover has completed, the parameter will be reset to the original value. This applies to both primary and physical standby databases.

Table A-1 summarizes the common processes that prevent switchover and what corrective action you need to take.

Table A-1 Common Processes That Prevent Switchover
Type of Process Process Description Corrective Action


The Job Queue Scheduler Process

Change the JOB_QUEUE_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.


The Advanced Queue Time Manager

Change the AQ_TM_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.


The Oracle Enterprise Manager Intelligent Agent

Issue the agentctl stop command from the operating system prompt.

A.5 What to Do If SQL Apply Operations to a Logical Standby Database Stop

Log apply services cannot apply unsupported DML statements, DDL statements, and Oracle supplied packages to a logical standby database in SQL apply mode.

When an unsupported statement or package is encountered, SQL apply operations stop. You can take the actions described in Table A-2 to correct the situation and start applying SQL statements to the logical standby database again.

Table A-2 Fixing Typical SQL Apply Operations Errors
If... Then...

You suspect an unsupported statement or Oracle supplied package was encountered

Find the last statement in the DBA_LOGSTDBY_EVENTS view. This will indicate the statement and error that caused SQL apply operations to fail. If an incorrect SQL statement caused SQL apply operations to fail, transaction information, as well as the statement and error information, can be viewed. The transaction information can be used with other Oracle9i LogMiner tools to understand the cause of the problem.

An error requiring database management occurred, such as running out of space in a particular tablespace

Fix the problem and resume SQL apply operations using the ALTER DATABASE START LOGICAL STANDBY APPLY statement.

An error occurred because a SQL statement was entered incorrectly, such as an incorrect standby database filename being entered in a tablespace command

Enter the correct SQL statement and use the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure to ensure that the incorrect statement is ignored the next time SQL apply operations are run. Then restart SQL apply operations using the ALTER DATABASE START LOGICAL STANDBY APPLY statement.

An error occurred because skip parameters were incorrectly set up, such as specifying that all DML for a given table be skipped but CREATE, ALTER, and DROP TABLE statements were not specified to be skipped

Issue a DBMS_LOGSTDBY.SKIP('TABLE','schema_name','table_name',null) call, then restart SQL apply operations.

See Also:

Chapter 14 for information about querying the DBA_LOGSTDBY_EVENTS view to determine the cause of failures

A.6 Network Tuning for Redo Log Transmission

The process of archiving redo logs involves reading a buffer from the redo log and writing it to the archive log location. When the destination is remote, the buffer is written to the archive log location over the network using Oracle Net services.

The default archive log buffer size is 1 megabyte. The default transfer buffer size for Oracle Net is 2 kilobytes. Therefore, the archive log buffer is divided into units of approximately 2 kilobytes for transmission. These units could get further divided depending on the maximum transmission unit (MTU) of the underlying network interface.

The Oracle Net parameter that controls the transport size is session data unit (SDU). This parameter can be adjusted to reduce the number of network packets that are transmitted. This parameter allows a range of 512 bytes to 32 kilobytes.

For optimal performance, set the Oracle Net SDU parameter to 32 kilobytes for the associated SERVICE destination parameter.

The following example shows a database initialization parameter file segment that defines a remote destination netserv:


The following example shows the definition of that service name in the tnsnames.ora file:

netserv=(DESCRIPTION=(SDU=32768)(ADDRESS=(PROTOCOL=tcp)(HOST=host) (PORT=1521)) 

The following example shows the definition in the listener.ora file:



If you archive to a remote site using high-latency/high-bandwidth connections, you can improve performance by increasing the TCP send and receive window sizes. Use caution, however, because this might adversely affect networked applications that do not exhibit the same characteristics as archiving. This method consumes a large amount of system resources.

See Also:

Oracle9i Net Services Administrator's Guide

You can also use cascade standby databases to off-load network processing from the primary database to a standby database. See Appendix D for more information.

A.7 Managing Data Guard Network Timeout

For any given Oracle Data Guard network connection, there are two processes communicating with each other. When the network connection is unexpectedly broken, how these processes react differs greatly. This is a discussion of what actually occurs when a network connection is broken, and how it affects the Data Guard environment and configuration. This discussion applies to both physical and logical standby databases.

Data Guard uses a peer-to-peer connection protocol, whereby a primary database process, if it is the Log Writer (LGWR) or the Archiver (ARCH), establishes a network connection to the standby database. As a result of the network connection request, the listener on the standby site creates a separate process on the standby database - called the Remote File Server (RFS) process. This RFS process uses network messages from the primary database; it reads from the network and sends an acknowledgement message back to the primary when it is done processing the request.

During normal Data Guard operations, when redo data is transmitted from the primary to the standby, network messages are initiated from the primary database (the network `client'), and always acknowledged by the standby database (the network `server'). In this case, the LGWR and ARCH processes are the network clients, and the RFS process is the network server.

Consider the simple scenario where the network between the primary and standby systems is disconnected. This results in what is known as a dead connection. A dead connection indicates that there is no physical connection, but the connection appears to still be there to the processes on each system.

When the LGWR process attempts to send a new message to the RFS process over the dead connection, the LGWR process receives an error from Oracle Net, after a TCP timeout, indicating that the connection has been broken. In this way, the LGWR is able to establish that network connectivity has been lost, and take corrective action. The Data Guard attributes [NO]MAX_FAILURE, [NO]REOPEN and [NO]NET_TIMEOUT, which are options for the LOG_ARCHIVE_DEST_n parameter, provide LGWR with the desired flexibility to control the timeout intervals and number of retries associated with a network connection that is not responding.

In contrast to the LGWR process, the RFS process on the standby database is always synchronously waiting for a new message to arrive from the primary database. The RFS process that is doing the network read operation is blocked until some data arrives to its reading buffer, or until the underlying network software determines the dead connection is no longer valid.

Oracle Net periodically sends a network probe to verify that a client/server connection is still active. This ensures that connections are not left open indefinitely due to an abnormal client termination. If the probe finds a dead connection or a connection that is no longer in use, it returns an error that causes the RFS process to exit.

You can use the Oracle Net parameter SQLNET.EXPIRE_TIME to specify the time interval, expressed in seconds, when to send a probe to verify that the network session is active. Setting this parameter to a small value allows for more timely detections of dead connections. Connections that do not respond to this probe signal are disconnected. This parameter should be set up for the standby database, as well as the primary, to prepare it for future switchover scenarios.

Limitations on using the dead connection detection feature are:

Once the RFS process receives notification of the dead network connection, it will terminate itself. However, until such time as the RFS process terminates itself, it will retain lock information on the archivelog on the standby site, or the standby redo log, whose redo information was being received from the primary database. During this interval, no new RFS processes can receive redo information from the primary database for the same archived redo log (or the standby redo log).

The dead network connection detection timer expiration value can also be controlled using the TCP/IP keepalive parameter that specifies the number of seconds to wait before verifying the network connection is valid. Note that the value of the TCP/IP keepalive parameter defaults on most system to two hours, which means that in the default case the RFS process will wait for 2 hours before timing out on a dead network connection.

Therefore, Oracle Corporation recommends setting the Oracle Net SQLNET.EXPIRE_TIME parameter and the TCP/IP keepalive parameter to 60 seconds. This is a reasonable value for most systems, and setting the parameter to a small value does not significantly impact production systems.

Once the network problem is resolved, and the primary database processes are again able to establish network connections to the standby database, a new RFS process will automatically be spawned on the standby database for each new network connection. These new RFS processes will resume the reception of redo data from the primary database.