Switchover not happening in Oracle Data guard

Usually when we are trying to switchover, problems come when sessions are active, that is why session shutdown option is there in oracle, so in case some sessions are

active, make sure you wait for them to end or kill them, also do the following workaround in order to make sure the a clean switchover happens.


So like i said When sessions are active we should not try to switchover, even if we do 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 WHERE TYPE = 'USER' 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 above output and example, the JOB_QUEUE_PROCESSES parameter corresponds to the CJQ0 process entry.JOB_QUEUE_PROCESSES parameter is used for lot of things in

oracle like for example refresh support for materialized views or may be advanced queus using job queues for message propagation. Oracle says 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.

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per

instance (J000, ... J999).


Verify 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;


Some more sessions can also be active and below processes are very critical to stop in order for a clean switchover to take place.

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

## Issue the emctl stop agent command from the operating system prompt.