A
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:

SQL> SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;

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:

SQL> SELECT DEST_ID "ID",
  2> STATUS "DB_status",
  3> DESTINATION "Archive_dest",
  4> ERROR "Error"
  5> FROM V$ARCHIVE_DEST;

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:

The service name for the standby instance is not configured correctly in the tnsnames.ora file at the primary site.
The service name listed in the LOG_ARCHIVE_DEST_n parameter of the primary initialization parameter file is incorrect.
The LOG_ARCHIVE_DEST_STATE_n parameter specifying the state of the standby archiving destination has the value DEFER.
The listener.ora file has not been configured correctly at the standby site.
The listener is not started.
The standby instance is not started.
You have added a standby archiving destination to the primary initialization parameter file, but have not yet enabled the change.
You used an invalid backup as the basis for the standby database (for example, you used a backup from the wrong database, or did not create the standby control file using the correct method).

A.1.3 You Cannot Mount the Physical Standby Database

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

The standby instance is not started in NOMOUNT mode. You must first start the instance and then mount the database.
The standby control file was not created with the ALTER DATABASE CREATE STANDBY CONTROLFILE ... statement or RMAN. You cannot use the following types of control file backups:
- An operating system-created backup
- A backup created using an ALTER DATABASE statement without the STANDBY option

A.2 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.2.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:

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

       SID PROCESS   PROGRAM
---------- --------- ------------------------------------------------
         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.2.2 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.2.3 Archived Redo Logs Are Not Applied to the Standby Database After Switchover

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

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

Action:

Check the tnsnames.ora file at the primary site and the listener.ora file at the standby site. There should be entries for a listener at the standby site and a corresponding tnsname at the primary site.
Start the listener at the standby site if it has not been started.
Check if the LOG_ARCHIVE_DEST_n initialization parameter has been set to properly archive logs from the primary site to standby site. For example, query the V$ARCHIVE_DEST fixed view at the primary site as follows:
```
SQL> SELECT DEST_ID, STATUS, DESTINATION FROM V$ARCHIVE_DEST;
```
If you do not see an entry corresponding to the standby site, you need to set LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n initialization parameters.
Set the STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT initialization parameters correctly at the standby site so that the archived redo logs are applied to the desired location.
At the standby site, set the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters. Set the STANDBY_FILE_MANAGEMENT initialization parameter to auto if you want the standby site to automatically add new datafiles that are created at the primary site.

A.2.4 Switchover Fails in a Real Application Clusters Configuration

When your database is using Real Application Clusters, active instances prevent a switchover from being performed. When other instances are active, an attempt to switch over fails with the following error message:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; 
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY * 
ORA-01105: mount is incompatible with mounts by other instances

Action: Query the GV$INSTANCE view as follows to determine which instances are causing the problem:

SQL> SELECT INSTANCE_NAME, HOST_NAME FROM GV$INSTANCE 
  2> WHERE INST_ID <> (SELECT INSTANCE_NUMBER FROM V$INSTANCE);
INSTANCE_NAME HOST_NAME 
------------- --------- 
INST2         standby2

In the previous example, the identified instance must be manually shut down before the switchover can proceed. You can connect to the identified instance from your instance and issue the SHUTDOWN statement remotely, for example:

SQL> CONNECT SYS/CHANGE_ON_INSTALL@standby2 AS SYSDBA 
SQL> SHUTDOWN;
SQL> EXIT

A.2.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 may prevent a switchover from being processed. Active SQL sessions may include other Oracle processes.

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

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY * 
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:

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION    
  2> WHERE TYPE = 'USER'
  3>  AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);
SID        PROCESS   PROGRAM 
---------  --------  ------------------------------------------------ 
        7      3537  oracle@nhclone2 (CJQ0)
       10
       14
       16
       19
       21
 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:

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; 
NAME                           TYPE      VALUE
------------------------------ -------   -------------------- 
job_queue_processes            integer   5

Then set the parameter to 0. For example:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
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
CJQ0	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.
QMN0	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.
DBSNMP	The Oracle Enterprise Manager Intelligent Agent	Issue the `agentctl stop` command from the operating system prompt.

A.3 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 Troubleshooting the Standby Database