Tuesday, 29 November 2011

Problems which can be Occur in Switching Over to a Standby Database

 Below are some common problem arrives when we commit switch over the 
most common problems are as follows

Switchover Fails Because Redo Data Was Not Transmitted


If the switchover does not complete successfully, you can query the SEQUENCE# column in the V$ARCHIVED_LOG view to see if the last redo data transmitted from the original primary database was applied on the standby database. If the last redo data was not transmitted to the standby database, you can manually copy the archived redo log file containing the redo data from the original primary database to the old standby database and register it with the SQL ALTER DATABASE REGISTER LOGFILE file_specification statement. If you then start log apply services, the archived redo log file will be applied automatically. Query the
SWITCHOVER_STATUS column in the V$DATABASE view. The TO PRIMARY value in the SWITCHOVER_STATUS column verifies switchover to the primary role is now possible.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 
SWITCHOVER_STATUS 
----------------- 
TO PRIMARY 
1 row selected 
 

Switchover Fails Because SQL Sessions Are Still 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 Database 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 earlier instance, the JOB_QUEUE_PROCESSES factor corresponds to the CJQ0 method entry. Because the job row method is a customer method, it is counted as a SQL assembly that halts switchover from taking place. The submissions with no method or program knowledge are ideas commenced by the job row controller.

Verify the JOB_QUEUE_PROCESSES factor is set employing the subsequent 
 
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 factor, you can change the worth and have the change take issue straight away without having to restart the instance. You can now retry the switchover procedure.

Do not alter the factor in your initialization factor file. After you fastened down the case and restart it after the switchover discharges, the factor will be reset to the primary value. This is applicable to both main and bodily standby databases
 
summarizes the common processes that prevent switchover 
and what corrective action you need to take. 
 
CJQ0

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

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

Oracle Enterprise Manager Management Agent

Issue the agentctl stop command from the operating system prompt.
 

Switchover Fails Because User Sessions Are Still Active


If the switchover bungles and revisits the wrongdoing ORA-01093 "Alter database close only sanctioned with no assemblies connected" it is commonly because the ALTER DATABASE COMMIT TO SWITCHOVER assertion implicitly blocked the database, and if there are any other customer assemblies bound to the database, the close fails.

If you accept this wrongdoing, disconnect any customer assemblies that are still bound to the database. To perform this, query the V$SESSION fastened scenery to observe which assemblies are still energetic as shown in the subsequent 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 this instance, the first seven assemblies are all Oracle Database setting processes. Among the two SQL*Plus assemblies, one is the prevailing SQL*Plus assembly distributing the query, and the other is an surplus assembly that should be disconnected before you re-attempt the switchover.