12 Replies Latest reply: Apr 17, 2012 9:25 AM by mseberg RSS

    Not allowed status in primary db switch_over status

    926275
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            >
                            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-Oracle
                              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
                                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