This discussion is archived
12 Replies Latest reply: Apr 17, 2012 7:25 AM by mseberg RSS

Not allowed status in primary db switch_over status

926275 Newbie
Currently Being Moderated
I have set up data guard in mumbai server..

When i check switch over status for primary database, it shows "Not allowed" status.. but in the standby db, it shows "To Primary" status..is there any problem with the data guard setup..can i perform switch over and fail over procedures in those two servers? please answer me..how do i change the primary database switch_over status as "To Standby"...
  • 1. Re: Not allowed status in primary db switch_over status
    CKPT Guru
    Currently Being Moderated
    13021986 wrote:
    I have set up data guard in mumbai server..

    When i check switch over status for primary database, it shows "Not allowed" status.. but in the standby db, it shows "To Primary" status..is there any problem with the data guard setup..can i perform switch over and fail over procedures in those two servers? please answer me..how do i change the primary database switch_over status as "To Standby"...
    Hi,

    Can you check both primary & standby are in sync?
    Query the switchover_status column of the v$database view on the primary database to determine whether the primary database can be switched over to the standby.
    
    
    SQL> select switchover_status from v$database;
    
    SWITCHOVER_STATUS
    --------------------
    TO STANDBY
    A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.
  • 2. Re: Not allowed status in primary db switch_over status
    926275 Newbie
    Currently Being Moderated
    Switchover_status is "not allowed" on primary.. is there anything wrong on the dataguard setup i've configured? what should i do?
  • 3. Re: Not allowed status in primary db switch_over status
    CKPT Guru
    Currently Being Moderated
    13021986 wrote:
    Switchover_status is "not allowed" on primary.. is there anything wrong on the dataguard setup i've configured? what should i do?
    What is the status of SYNC?

    From Primary:-
    SQL> select thread#,max(sequence#) from v$archived_log group by thread#;


    From Standby:-
    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

    Check any status in standby alert log file as "media recovery *transit* " ?
    If its in SYNC, status is still same, Bounce primary and then check status again.
  • 4. Re: Not allowed status in primary db switch_over status
    926275 Newbie
    Currently Being Moderated
    What is the status of SYNC?

    From Primary:-
    SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

    it gives output that 7 rows selected

    From Standby:-
    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

    the result comes as "no rows selected"... so please suggest..
  • 5. Re: Not allowed status in primary db switch_over status
    CKPT Guru
    Currently Being Moderated
    13021986 wrote:
    What is the status of SYNC?

    From Primary:-
    SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

    it gives output that 7 rows selected

    From Standby:-
    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

    the result comes as "no rows selected"... so please suggest..
    can you post output of the queries?
    As per your update, it shows no archives are applied on standby, Have you configured standby recently.
    To analysis your configuration please use below script to troubleshoot
    http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
  • 6. Re: Not allowed status in primary db switch_over status
    926275 Newbie
    Currently Being Moderated
    Now i'm not in a client place.. I will post the output as soon as possible..

    I've configured the Dataguard on friday only..Thanks for ur valuable replies.. I need ur support further..
  • 7. Re: Not allowed status in primary db switch_over status
    688970 Journeyer
    Currently Being Moderated
    Hi,

    I think your logs are not shipped to standby destination and as a result they are not applied to standby database.
    As suggested by CKPT
    post the results of the queries.

    Also post the results of the following on primary:

    SQL> show parameter log_archive_dest


    Regards

    Edited by: jazz81 on 16-Apr-2012 10:28
  • 8. Re: Not allowed status in primary db switch_over status
    mseberg Guru
    Currently Being Moderated
    Hello;

    Some additional Troubleshooting might be needed. I'm thinking redo is not applying on your Standby.

    Run these queries to shake out the issue :


    Standby archive destination is not defined properly :
     
     set linesize 200
     col DESTINATION format a50
     col ERROR format a70
     SELECT 
        DESTINATION, 
        ERROR 
      FROM 
       V$ARCHIVE_DEST;
     
    Results from mine :
     
     DESTINATION                                        ERROR                                                                 
     -------------------------------------------------- ------------------------
     USE_DB_RECOVERY_FILE_DEST                                                                                                
     STANDBY                                                
     
    Standby database does not receive redo data from the primary database
     
     SELECT 
      set linesize 200
      col DESTINATION format a50
       col ERROR format a70
      SELECT 
         DEST_ID "ID",
         STATUS "DB_status",
         DESTINATION ,
         ERROR "Error"
       FROM 
        V$ARCHIVE_DEST 
       WHERE 
       DEST_ID <=5;
     
    Results from mine :
     
     
             ID DB_status DESTINATION                            Error                                                                 
     ---------- --------- -------------------------------------- --------------------------------------
              1 VALID     USE_DB_RECOVERY_FILE_DEST                                                                                                
              2 VALID     STANDBY                                                                                                                  
              3 INACTIVE                                                                                                                           
              4 INACTIVE                                                                                                                           
              5 INACTIVE                                                                                                                           
     
    5 rows selected.
    Next check your last sequnece from the Primary :
    clear screen
    set linesize 100
     
    column STANDBY format a20
    column applied format a10
     
    
    
    SELECT  
      NAME AS STANDBY, 
      SEQUENCE#, 
      APPLIED, 
      COMPLETION_TIME 
    FROM 
      V$ARCHIVED_LOG 
    WHERE  
      DEST_ID = 2 
    AND 
     NEXT_TIME > SYSDATE -1;
    and compare to the Max sequnece
    SELECT 
      MAX(SEQUENCE#) 
    FROM 
      V$ARCHIVED_LOG 
    WHERE 
      NEXT_TIME > SYSDATE -1;
    Hopefully this will give us the clue we need to find the issue.

    Best Regards

    mseberg
  • 9. Re: Not allowed status in primary db switch_over status
    926275 Newbie
    Currently Being Moderated
    and thanks CKPT & mseberg


    this is my dg_primary_output log:
    =====================


    SQL> set feedback off
    SQL> set trimspool on
    SQL> set line 500
    SQL> set pagesize 50
    SQL> column name for a30
    SQL> column display_value for a30
    SQL> column ID format 99
    SQL> column "SRLs" format 99
    SQL> column active format 99
    SQL> col type format a4
    SQL> column ID format 99
    SQL> column "SRLs" format 99
    SQL> column active format 99
    SQL> col type format a4
    SQL> col PROTECTION_MODE for a20
    SQL> col RECOVERY_MODE for a20
    SQL> col db_mode for a15
    SQL> SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;

    NAME DISPLAY_VALUE
    ------------------------------ ------------------------------
    db_file_name_convert G:\app\Administrator\oradata\,
    G:\app\Administrator\oradata\

    db_name cannetdc
    db_unique_name cannetdc
    dg_broker_config_file1 G:\APP\ADMINISTRATOR\PRODUCT\1
    1.2.0\DBHOME_1\DATABASE\DR1CAN
    NETDC.DAT

    dg_broker_config_file2 G:\APP\ADMINISTRATOR\PRODUCT\1
    1.2.0\DBHOME_1\DATABASE\DR2CAN
    NETDC.DAT

    dg_broker_start FALSE
    fal_client cannetdc
    fal_server cannetdr
    local_listener
    log_archive_config
    log_archive_dest_2 SERVICE=cannetdr ASYNC VALID_F
    OR=(ONLINE_LOGFILES,PRIMARY_RO
    LE) DB_UNIQUE_NAME=cannetdr

    log_archive_dest_state_2 DEFER
    log_archive_max_processes 4
    log_file_name_convert G:\app\Administrator\oradata\,
    G:\app\Administrator\oradata\

    remote_login_passwordfile EXCLUSIVE
    standby_archive_dest %ORACLE_HOME%\RDBMS
    standby_file_management AUTO
    SQL> col name for a10
    SQL> col DATABASE_ROLE for a10
    SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;

    NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE SWITCHOVER_STATUS
    ---------- ------------------------------ -------------------- ---------- -------------------- --------------------
    CANNETDC cannetdc MAXIMUM PERFORMANCE PRIMARY READ WRITE NOT ALLOWED
    SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

    THREAD# MAX(SEQUENCE#)
    ---------- --------------
    1 37
    SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
    2 FROM
    3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
    4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
    5 WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
    ---------- ---------------------- --------------------- ----------
    1 37 37 0
    SQL> col severity for a15
    SQL> col message for a70
    SQL> col timestamp for a20
    SQL> select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;
    SQL> select ds.dest_id id
    2 , ad.status
    3 , ds.database_mode db_mode
    4 , ad.archiver type
    5 , ds.recovery_mode
    6 , ds.protection_mode
    7 , ds.standby_logfile_count "SRLs"
    8 , ds.standby_logfile_active active
    9 , ds.archived_seq#
    10 from v$archive_dest_status ds
    11 , v$archive_dest ad
    12 where ds.dest_id = ad.dest_id
    13 and ad.status != 'INACTIVE'
    14 order by
    15 ds.dest_id;

    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------- --------------- ---- -------------------- -------------------- ---- ------ -------------
    1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 37
    2 DEFERRED UNKNOWN LGWR IDLE MAXIMUM PERFORMANCE 0 0 0
    SQL> column FILE_TYPE format a20
    SQL> col name format a60
    SQL> select name
    2 , floor(space_limit / 1024 / 1024) "Size MB"
    3 , ceil(space_used / 1024 / 1024) "Used MB"
    4 from v$recovery_file_dest
    5 order by name;

    NAME Size MB Used MB
    ------------------------------------------------------------ ---------- ----------
    G:\app\Administrator\flash_recovery_area\backupset 3912 2099
    SQL> spool off




    this is my dg_standby_output log:
    =====================


    SQL> set feedback off
    SQL> set trimspool on
    SQL> set line 500
    SQL> set pagesize 50
    SQL> set linesize 200
    SQL> column name for a30
    SQL> column display_value for a30
    SQL> col value for a10
    SQL> col PROTECTION_MODE for a15
    SQL> col DATABASE_Role for a15
    SQL> SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;

    NAME DISPLAY_VALUE
    ------------------------------ ------------------------------
    db_file_name_convert G:\app\Administrator\oradata\,
    G:\app\Administrator\oradata\

    db_name cannetdc
    db_unique_name cannetdr
    dg_broker_config_file1 G:\APP\ADMINISTRATOR\PRODUCT\1
    1.2.0\DBHOME_1\DATABASE\DR1CAN
    NETDR.DAT

    dg_broker_config_file2 G:\APP\ADMINISTRATOR\PRODUCT\1
    1.2.0\DBHOME_1\DATABASE\DR2CAN
    NETDR.DAT

    dg_broker_start FALSE
    fal_client cannetdr
    fal_server cannetdc
    local_listener
    log_archive_config
    log_archive_dest_2 SERVICE=cannetdc ASYNC VALID_F
    OR=(ONLINE_LOGFILES,PRIMARY_RO
    LE) DB_UNIQUE_NAME=cannetdc

    log_archive_dest_state_2 DEFER
    log_archive_max_processes 4
    log_file_name_convert G:\app\Administrator\oradata\,
    G:\app\Administrator\oradata\

    remote_login_passwordfile EXCLUSIVE
    standby_archive_dest %ORACLE_HOME%\RDBMS
    standby_file_management AUTO
    SQL> col name for a10
    SQL> col DATABASE_ROLE for a10
    SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;

    NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE
    ---------- ------------------------------ --------------- ---------- --------------------
    CANNETDC cannetdr MAXIMUM PERFORM PHYSICAL S MOUNTED
    ANCE TANDBY

    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
    SQL> select process, status,thread#,sequence# from v$managed_standby;

    PROCESS STATUS THREAD# SEQUENCE#
    --------- ------------ ---------- ----------
    ARCH CONNECTED 0 0
    ARCH CONNECTED 0 0
    ARCH CONNECTED 0 0
    ARCH CONNECTED 0 0
    SQL> col name for a30
    SQL> select * from v$dataguard_stats;

    NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
    ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
    transport lag day(2) to second(0) interval 04/17/2012 11:46:22
    apply lag day(2) to second(0) interval 04/17/2012 11:46:22
    apply finish time day(2) to second(3) interval 04/17/2012 11:46:22
    estimated startup time 6 second 04/17/2012 11:46:22
    SQL> select * from v$archive_gap;
    SQL> col name format a60
    SQL> select name
    2 , floor(space_limit / 1024 / 1024) "Size MB"
    3 , ceil(space_used / 1024 / 1024) "Used MB"
    4 from v$recovery_file_dest
    5 order by name;

    NAME Size MB Used MB
    ------------------------------------------------------------ ---------- ----------
    G:\app\Administrator\flash_recovery_area\backupset 3912 0
    SQL> spool off


    please suggest me the correct solution....
  • 10. Re: Not allowed status in primary db switch_over status
    CKPT Guru
    Currently Being Moderated
    >
    log_archive_dest_2 SERVICE=cannetdr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cannetdr
    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------------- ---- -------------------- ---- -------------
    1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 37
    2 "DEFERRED" UNKNOWN LGWR IDLE MAXIMUM PERFORMANCE 0 0 0
    >

    So it points to standby, but see the status below
    log_archive_dest_state_2 DEFER
    DEFER, so it wont send any archives to primary. :-)
    SQL> alter system set log_archive_dest_state_2='enable' scope=both sid='*';
    & then check for the latest update in primary & standby log file.
  • 11. Re: Not allowed status in primary db switch_over status
    UweHesse Expert
    Currently Being Moderated
    The first question that comes to my mind here is: Why do you not use the Data Guard Broker as it is recommended?

    It would
    1) Simply prevent you from making mistakes that may restrict the switchover
    2) Make the switchover itself much easier

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com
  • 12. Re: Not allowed status in primary db switch_over status
    mseberg Guru
    Currently Being Moderated
    please suggest me the correct solution



    On the database in the Primary role :


    Thanks for the fantastic details!!


    ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;


    Wait a minute and do a log switch.


    Then check results.


    Best Regards

    mseberg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points