13 Replies Latest reply: Apr 2, 2013 11:29 AM by 977635 RSS

    after switchover to standby as primary, now how to switch back

    977635
      Hello DG experts:

      I'm running Oracle 11.2 on Linux 5.8.
      I am in the process of (trying) to mimic our existing production DG environment for testing purposes.

      So far...
      I've managed to set up a primary and standby database and ensured all logs apply properly.
      My database name is DGTEST (on both primary and standby).
      My db_unique_name is DGTEST_DG1 on primary, and DGTEST_DG2 on standby.

      I performed a switchover from primary to standby, and opened the standby database as primary.
      But, first thing is that when I check the switchover_status from v$database, it says: NOT ALLOWED.
      :-(

      Okay, so now what? Do I need to have another pfile to set specifically for when I am in primary or standby modes?

      Here are relevant commands I have checked already:
      on new Primary (db_unique_name = DGTEST_DG2)
      
      SQL> select switchover_status from v$database;
      
      SWITCHOVER_STATUS
      --------------------
      NOT ALLOWED
      
      SQL> select sequence#, applied from v$archived_log order by 1;
      
            SEQUENCE# APPLIED
      --------------- ---------
                  291 YES
                  292 YES
                  293 YES
                    . . .
                  398 YES
                  399 NO
                  400 NO
                  401 NO
                  402 NO
      
      112 rows selected.
      
      SQL> select destination, status, error from v$archive_dest;
      
      DESTINATION                    STATUS    ERROR
      ------------------------------ --------- -----------------------------------------------------------------
      /arch/DGTEST/archivelogs       VALID
      /arch/DGTEST_STBY              VALID
                                     INACTIVE
                                     INACTIVE
                                      . . .
                                     INACTIVE
      
      31 rows selected.
      on new Standby (db_unique_name = DGTEST_DG1)
      
      SQL> select switchover_status from v$database;
      
      SWITCHOVER_STATUS
      --------------------
      TO PRIMARY
      
      1 row selected.
      
      SQL> select sequence#, applied from v$archived_log order by 1;
      
            SEQUENCE# APPLIED
      --------------- ---------
                  276 YES
                  277 YES
                  278 YES
                   . . .
                  397 NO
                  397 YES
                  398 YES
                  398 NO
      
      231 rows selected.
      
      SQL>  select destination, status, error from v$archive_dest;
      
      DESTINATION                    STATUS    ERROR
      ------------------------------ --------- -----------------------------------------------------------------
      /arch/DGTEST                   VALID
      DGTEST_DG2                     VALID
      /arch/DGTEST_STBY              DEFERRED
                                     INACTIVE
                                     INACTIVE
                                       . . .
      Please keep in mind that I have made this test environment as close as possible (I think) to our production environment.
      One of the objectives is to confirm we can switchover and back in our production environment.
      So if there is something seriously wrong with this environment and it is the same in our production environment, that would be one of my primary objectives to find out about.

      Only significant difference is that we are also using DGBroker in production and I did create configuration yet until I got the switchover working manually first.
        • 1. Re: after switchover to standby as primary, now how to switch back
          mseberg
          Hello;

          You show "DEFERRED" on the new primary. No I have that wrong. But you are not applying on the new Standby as you know.

          It looks like a failed switchover to me.
           Do I need to have another pfile to set specifically for when I am in primary or standby modes?
          No.


          Double check against my notes here :

          http://www.visi.com/~mseberg/data_guard/Data_Guard_switchover.html

          This is odd
          My db_unique_name is DGTEST_DG1 on primary, and DGTEST_DG2 on standby
          I would expect db_unique_name on the Primary to be the same as the DB_NAME. However as long as its a unique value I think its OK.

          This tells me the Old primary thinks its a Standby :
          SWITCHOVER_STATUS
          --------------------
          TO PRIMARY
          What do the alert logs show?

          In any event until the databases SYNC you will not be able to switch back.


          Best Regards

          mseberg

          Edited by: mseberg on Apr 1, 2013 1:31 PM
          • 2. Re: after switchover to standby as primary, now how to switch back
            879152
            Hi,
            Follow This Steps:

            How to Switchover from Primary to Standby Database?

            MY Configuration:

            RAC is configured on dcpdb1(node1) and dcpdb2(node2).
            DB Name: dcpdb
            db_unique_name: dcpdb
            Instance Name: dcpdb1 on node1
            Instance Name: dcpdb2 on node2

            Active Data Guard is configured on drpdb1
            DB Name: dcpdb
            db_unique_name: drpdb
            Instance Name: drpdb


            Process:

            On the primary server, check the latest archived redo log and force a log switch.


            *########### Login dcpdb1 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time
            FROM v$archived_log
            ORDER BY next_time;
            SQL> ALTER SYSTEM SWITCH LOGFILE;


            Check the new archived redo log has arrived at the standby server and been applied.

            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time, applied
            FROM v$archived_log
            ORDER BY next_time ;

            *########### Login dcpdb2 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time
            FROM v$archived_log
            ORDER BY next_time ;

            SQL> ALTER SYSTEM SWITCH LOGFILE;

            Check the new archived redo log has arrived at the standby server and been applied.


            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time, applied
            FROM v$archived_log
            ORDER BY next_time ;

            *########### Login dcpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO STANDBY

            *########### Login dcpdb2 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO STANDBY

            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            NOT ALLOWED


            *########### Login dcpdb2 as Oracle user #########*

            SQL> shutdown immediate
            Database closed.
            Database dismounted.
            ORACLE instance shut down.
            SQL>

            *########### Login dcpdb1 as Oracle user #########*

            SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

            Database altered.

            SQL>
            SQL> shutdown immediate
            ORA-01507: database not mounted
            ORACLE instance shut down.
            SQL>

            SQL> startup mount
            ORACLE instance started.
            Total System Global Area 1.5400E+10 bytes
            Fixed Size 2184872 bytes
            Variable Size 7751076184 bytes
            Database Buffers 7616856064 bytes
            Redo Buffers 29409280 bytes
            Database mounted.
            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO PRIMARY

            SQL>

            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            SESSIONS ACTIVE

            SQL> alter database commit to switchover to primary with session shutdown;

            Database altered.

            SQL> shutdown immediate
            ORA-01109: database not open
            Database dismounted.
            ORACLE instance shut down.
            SQL>
            SQL> startup
            ORACLE instance started.
            Total System Global Area 1.5400E+10 bytes
            Fixed Size 2184872 bytes
            Variable Size 7717521752 bytes
            Database Buffers 7650410496 bytes
            Redo Buffers 29409280 bytes
            Database mounted.
            Database opened.


            *########### Login dcpdb1 as Oracle user #########*

            SQL> alter database open read only;

            Database altered.

            SQL> alter database recover managed standby database using current logfile disconnect;

            Database altered.


            *########### Login dcpdb2 as Oracle user #########*

            SQL> startup mount
            ORACLE instance started.

            Total System Global Area 1.5400E+10 bytes
            Fixed Size 2184872 bytes
            Variable Size 7751076184 bytes
            Database Buffers 7616856064 bytes
            Redo Buffers 29409280 bytes
            Database mounted.
            SQL> alter database open read only;

            Database altered.

            SQL> alter database recover managed standby database using current logfile disconnect ;

            Database altered.

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            NOT ALLOWED

            SQL>


            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO STANDBY

            *########### Login dcpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            NOT ALLOWED

            SQL>


            *########### Login dcpdb2 as Oracle user #########*


            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            NOT ALLOWED

            SQL>


            *############### End of Switchover from Primary to Standby Database ##########*

            How to Switchback from New Primary(drpdb) to old Standby(drdb) Database and New Standby(dcpdb to Old Primary(dcpdb)?

            Process:

            On the New primary server, check the latest archived redo log and force a log switch.

            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time
            FROM v$archived_log
            ORDER BY next_time ;

            SQL>ALTER SYSTEM SWITCH LOGFILE;

            Check the new archived redo log has arrived at the standby server and been applied.

            *########### Login dcpdb1 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time, applied
            FROM v$archived_log
            ORDER BY next_time ;

            *########### Login dcpdb2 as Oracle user #########*

            SQL> SELECT sequence#, first_time, next_time, applied
            FROM v$archived_log
            ORDER BY next_time ;

            *########### Login drpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO STANDBY

            *########### Login dcpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            NOT ALLOWED

            *########### Login dcpdb2 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            NOT ALLOWED

            *########### Login drpdb1 as Oracle user #########*

            SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

            Database altered.
            SQL> shutdown immediate
            ORA-01507: database not mounted
            ORACLE instance shut down.

            SQL> startup mount
            ORACLE instance started.
            Total System Global Area 1.5400E+10 bytes
            Fixed Size 2184872 bytes
            Variable Size 7717521752 bytes
            Database Buffers 7650410496 bytes
            Redo Buffers 29409280 bytes
            Database mounted.
            SQL>
            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO PRIMARY

            SQL>


            *########### Login dcpdb2 as Oracle user #########*

            SQL> shutdown immediate
            Database closed.
            Database dismounted.
            ORACLE instance shut down.
            SQL>


            *########### Login dcpdb1 as Oracle user #########*

            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            SESSIONS ACTIVE

            OR

            SWITCHOVER_STATUS
            --------------------
            SWITCHOVER PENDING

            SQL> alter database commit to switchover to primary with session shutdown;

            Database altered.

            SQL>
            SQL> shutdown immediate
            ORA-01109: database not open
            Database dismounted.
            ORACLE instance shut down.
            SQL> startup
            ORACLE instance started.

            Total System Global Area 1.5400E+10 bytes
            Fixed Size 2184872 bytes
            Variable Size 7751076184 bytes
            Database Buffers 7616856064 bytes
            Redo Buffers 29409280 bytes
            Database mounted.
            Database opened.
            SQL>


            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO STANDBY

            SQL>

            *########### Login dcpdb2 as Oracle user #########*

            SQL> startup
            ORACLE instance started.

            Total System Global Area 1.5400E+10 bytes
            Fixed Size 2184872 bytes
            Variable Size 7751076184 bytes
            Database Buffers 7616856064 bytes
            Redo Buffers 29409280 bytes
            Database mounted.
            Database opened.
            SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

            SWITCHOVER_STATUS
            --------------------
            TO STANDBY

            SQL>

            *########### Login drpdb1 as Oracle user #########*

            SQL> alter database open read only;

            Database altered.

            SQL> alter database recover managed standby database using current logfile disconnect;

            Database altered.

            SQL>

            *####### End of Switchback from New Primary(drpdb) to old Standby(drdb) Database and New Standby(dcpdb to Old Primary(dcpdb) #########*

            Thanks
            Solaiman

            Edited by: 876149 on Apr 1, 2013 11:56 AM
            • 3. Re: after switchover to standby as primary, now how to switch back
              977635
              Hi again mseberg. Thank you for taking the time to look at my posting.

              I actually did use your document as a guide to creating the entire setup.

              To answer some of your comments:
              It looks like a failed switchover to me. 
              How can I tell?
              The alert log doesn't show errors as of the last time I restarted the database.
              I would expect db_unique_name on the Primary to be the same as the DB_NAME. However as long as its a unique value I think its OK.
              This tells me the Old primary thinks its a Standby :
              yeah, I don't like it this way, but that is the way they configured production (before my time).
              The Old primary "should" be standby at this point.
              I have not tried to switch back to it yet, so it would/should still be standby.
              What do the alert logs show?
              The alert log is pretty big, so not sure what to include here.
              Here are some parts that might be of concern and/or interest.
              On the original primary db:
              ==================
              Mon Apr 01 13:33:49 2013
              alter database commit to switchover to standby with session shutdown
              ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 10299] (DGTEST)
              Mon Apr 01 13:33:49 2013
              Thread 1 advanced to log sequence 398 (LGWR switch)
                Current log# 2 seq# 398 mem# 0: /redo1/DGTEST/redo02a.rdo
                Current log# 2 seq# 398 mem# 1: /redo2/DGTEST/redo02b.rdo
              Mon Apr 01 13:33:49 2013
              Stopping background process CJQ0
              Stopping background process QMNC
              All dispatchers and shared servers shutdown
              CLOSE: killing server sessions.
              . . .
              CLOSE: all sessions shutdown successfully.
              . . .
              Final check for a synchronized target standby. Check will be made once.
              LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
              Active, synchronized target has been identified
              Target has also received all redo
              . . .
              Mon Apr 01 13:35:43 2013
              Performing implicit shutdown abort due to switchover to physical standby
              Shutting down instance (abort)
              License high water mark = 4
              USER (ospid: 10299): terminating the instance
              Instance terminated by USER, pid = 10299
              Mon Apr 01 13:35:44 2013
              Instance shutdown complete
              As you see above, the original switchover from primary looks okay (at least to me).
              On original primary  (after starting back up)
              ===========
              Mon Apr 01 13:37:09 2013
              Starting ORACLE instance (normal)
              . . .
              Mon Apr 01 13:40:07 2013
              ARCa started with pid=31, OS id=10570
              Completed: alter database mount standby database
              . . .
              Mon Apr 01 13:40:24 2013
              Using STANDBY_ARCHIVE_DEST parameter default value as /arch/DGTEST
              ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=BOTH;
              Mon Apr 01 13:46:22 2013
              ALTER DATABASE RECOVER  managed standby database disconnect
              Attempt to start background Managed Standby Recovery process (DGTEST)
              Mon Apr 01 13:46:22 2013
              MRP0 started with pid=52, OS id=10670
              MRP0: Background Managed Standby Recovery process started (DGTEST)
               started logmerger process
              Mon Apr 01 13:46:27 2013
              Managed Standby Recovery not using Real Time Apply
              . . .
              Media Recovery Waiting for thread 1 sequence 399
              Completed: ALTER DATABASE RECOVER  managed standby database disconnect
              Now, for the new primary side:
              This is on the new primary (original standby)
              ===========================
              
              first, shutdown and started up the standby database (startup)
              Mon Apr 01 13:44:17 2013
              Starting ORACLE instance (normal)
              . . .
              Mon Apr 01 13:44:43 2013
              QMNC started with pid=52, OS id=2768
              LOGSTDBY: Validating controlfile with logical metadata
              LOGSTDBY: Validation complete
              Completed: ALTER DATABASE OPEN
              . . .
              Mon Apr 01 13:44:44 2013
              CJQ0 started with pid=54, OS id=2796
              ARCt: Archival started
              ARC0: STARTING ARCH PROCESSES COMPLETE
              Mon Apr 01 13:45:35 2013
              Using STANDBY_ARCHIVE_DEST parameter default value as /arch/DGTEST_STBY
              ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
              . . .
              Mon Apr 01 13:49:44 2013
              Starting background process SMCO
              Mon Apr 01 13:49:44 2013
              SMCO started with pid=53, OS id=3214
              Mon Apr 01 13:51:13 2013
              Thread 1 advanced to log sequence 400 (LGWR switch)
                Current log# 2 seq# 400 mem# 0: /redo1/DGTEST/redo02a.rdo
                Current log# 2 seq# 400 mem# 1: /redo2/DGTEST/redo02b.rdo
              Mon Apr 01 13:51:13 2013
              Archived Log entry 109 added for thread 1 sequence 399 ID 0xe099f432 dest 1:
              Thread 1 advanced to log sequence 401 (LGWR switch)
                Current log# 3 seq# 401 mem# 0: /redo1/DGTEST/redo03a.rdo
                Current log# 3 seq# 401 mem# 1: /redo2/DGTEST/redo03b.rdo
              Mon Apr 01 13:51:15 2013
              Archived Log entry 110 added for thread 1 sequence 400 ID 0xe099f432 dest 1:
              Thread 1 cannot allocate new log, sequence 402
              Checkpoint not complete
                Current log# 3 seq# 401 mem# 0: /redo1/DGTEST/redo03a.rdo
                Current log# 3 seq# 401 mem# 1: /redo2/DGTEST/redo03b.rdo
              Thread 1 advanced to log sequence 402 (LGWR switch)
                Current log# 1 seq# 402 mem# 0: /redo1/DGTEST/redo01a.rdo
                Current log# 1 seq# 402 mem# 1: /redo2/DGTEST/redo01b.rdo
              Mon Apr 01 13:51:15 2013
              Archived Log entry 111 added for thread 1 sequence 401 ID 0xe099f432 dest 1:
              Thread 1 advanced to log sequence 403 (LGWR switch)
                Current log# 2 seq# 403 mem# 0: /redo1/DGTEST/redo02a.rdo
                Current log# 2 seq# 403 mem# 1: /redo2/DGTEST/redo02b.rdo
              Mon Apr 01 13:51:16 2013
              Archived Log entry 112 added for thread 1 sequence 402 ID 0xe099f432 dest 1:
              The commands I used on the new primary to make it primary are:
              SQL> startup
              ORACLE instance started.
              
              Total System Global Area       417546240 bytes
              Fixed Size                       2227072 bytes
              Variable Size                  335545472 bytes
              Database Buffers                71303168 bytes
              Redo Buffers                     8470528 bytes
              Database mounted.
              Database opened.
              SQL>
              SQL>
              SQL>
              SQL> alter system set log_archive_dest_state_3=enable;
              
              System altered.
              
              SQL> alter system switch logfile;
              
              System altered.
              
              SQL> /
              
              System altered.
              
              SQL> /
              
              System altered.
              
              SQL> /
              
              System altered.
              
              SQL> select switchover_status from v$database;
              
              SWITCHOVER_STATUS
              --------------------
              NOT ALLOWED
              
              1 row selected.
              Hope this helps...

              Edited by: 974632 on Apr 1, 2013 12:58 PM
              • 4. Re: after switchover to standby as primary, now how to switch back
                mseberg
                Hello again;

                Thanks for the great details!
                As you see above, the original switchover from primary looks okay
                Yes, I agree. That's good news.


                New Standby waiting on old archive
                Media Recovery Waiting for thread 1 sequence 399
                New Primary adding archive but showing no errors
                Archived Log entry 112 added for thread 1 sequence 402 ID 0xe099f432 dest 1:
                Any chance this should be log_archive_dest_state_2 or another number?
                ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
                Can you post log_archive_dest_2 and log_archive_dest_3 for the current Primary?

                Or at least review for issues?

                Best Regards

                mseberg
                • 5. Re: after switchover to standby as primary, now how to switch back
                  977635
                  Thanks mseberg... that sounds at least a little promising.
                  Now to just get this switched back.

                  It seems strange that log_archive_dest_3 is empty.
                  It seems this should contain a location since the dest_3_state is ENABLE.
                  On new Primary:
                  ===========
                  log_archive_dest_1                   string      location="/arch/DGTEST/archive
                                                                   logs",  valid_for=(ONLINE_LOGF
                                                                   ILE,ALL_ROLES)
                  log_archive_dest_2                   string      location="/arch/DGTEST_STBY",
                                                                    valid_for=(STANDBY_LOGFILE,ST
                                                                   ANDBY_ROLE)
                  log_archive_dest_3                   string
                  log_archive_dest_state_3             string      ENABLE
                  On old Primary (new standby)
                  ====================
                  log_archive_dest_1                   string      LOCATION=/arch/DGTEST
                  log_archive_dest_2                   string      service="DGTEST_DG2", LGWR ASY
                                                                   NC NOAFFIRM delay=0 optional c
                                                                   ompression=disable max_failure
                                                                   =0 max_connections=1 reopen=30
                                                                   0 db_unique_name="DGTEST_DG2"
                                                                   net_timeout=30, valid_for=(all
                                                                   _logfiles,primary_role)
                  log_archive_dest_3                   string      LOCATION="/arch/DGTEST_STBY",
                                                                   valid_for=(STANDBY_LOGFILE,STA
                                                                   NDBY_ROLE)
                  log_archive_dest_state_3             string      DEFER
                  I confirmed that our production is also blank for dest_3 (on the standby database)
                  • 6. Re: after switchover to standby as primary, now how to switch back
                    mseberg
                    So on the new Primary it appears log_archive_dest_2 has the log_archive_dest_3 and log_archive_dest_3 is empty.

                    In any event once you fix this and SYNC up you should be able to switch back.

                    Maybe you have this already :

                    http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html


                    Best Regards

                    mseberg
                    • 7. Re: after switchover to standby as primary, now how to switch back
                      977635
                      Hello again mseberg,

                      I've read both of your referenced links - thank you.
                      Below is my status.
                      Do you have any suggestions how can I get these sync'd up from this point?
                      I've tried several different things, but now I'm running out of options (or procedurally, I'm doing something wrong).

                      new Primary:
                      =========
                      SQL> @monitor_DG_transport.sql
                      
                      DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
                      ---------- -------------- ------------ ----------- -------------- -------
                      DGTEST     BRAHE                   412         398 01-APR/13:33        14
                      new Standby:
                      =========
                      SQL>  @monitor_DG_transport.sql
                      
                      DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
                      ---------- -------------- ------------ ----------- -------------- -------
                      DGTEST     KEPLER                  398         396 01-APR/12:15         2
                      Problems I see:

                      1) logs are not being shipped to new standby
                      2) after manually copying logs from new primary to new standby, logs are not being applied

                      Any help you can offer would be appreciated.
                      Thx
                      • 8. Re: after switchover to standby as primary, now how to switch back
                        mseberg
                        Hello;

                        Can you run this on the current Standby and post the results?
                        select process,status,sequence# from v$managed_standby;
                        And this on the new primary ? Change ID to match your setup.
                        SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
                        Best Regards

                        mseberg
                        • 9. Re: after switchover to standby as primary, now how to switch back
                          977635
                          Here's what I have:
                          on new Standby:
                          ===========
                          SQL> l
                            1* select process,status,sequence# from v$managed_standby
                          SQL> /
                          
                          PROCESS   STATUS                SEQUENCE#
                          --------- --------------- ---------------
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          ARCH      CONNECTED                     0
                          MRP0      WAIT_FOR_LOG                399
                          
                          31 rows selected.
                          On new Primary, the archive logs are being written to: /arch/DGTEST/archivelogs (which is dest_1)
                          On new Primary:
                          ===========
                          
                          SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=1;
                          
                          DESTINATION                    STATUS    ERROR
                          ------------------------------ --------- -----------------------------------------------------------------
                          /arch/DGTEST/archivelogs       VALID
                          
                          1 row selected.
                          
                          SQL> SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
                          
                          DESTINATION                    STATUS    ERROR
                          ------------------------------ --------- -----------------------------------------------------------------
                          /arch/DGTEST_STBY              VALID
                          
                          1 row selected.
                          • 10. Re: after switchover to standby as primary, now how to switch back
                            mseberg
                            OK

                            On the current Primary can you create a Pfile and then post

                            log_archive_dest_1
                            log_archive_dest_2
                            log_archive_dest_3

                            From it?

                            It appears the archive is not doing a transfer. But recovery seems OK so once that is fixed we should be gold.

                            Best Regards

                            mseberg

                            Edited by: mseberg on Apr 2, 2013 9:11 AM
                            • 11. Re: after switchover to standby as primary, now how to switch back
                              977635
                              Here you go. One question I don't understand is what is the purpose of the duplicate entries for these parameters?
                              One entry will have '*' for the SID, and the duplicate entry has the actual SID.
                              (Also, i may have screwed it up already because I changed the log_archive_format a couple of hours ago to match production (making them both the same for prim and stby).
                              See below:
                              pfile from new Primary:
                              ===============
                              DGTEST.__db_cache_size=67108864
                              DGTEST.__java_pool_size=4194304
                              DGTEST.__large_pool_size=4194304
                              DGTEST.__oracle_base='/oracle/product'
                              DGTEST.__pga_aggregate_target=209715200
                              DGTEST.__sga_target=209715200
                              DGTEST.__shared_io_pool_size=0
                              DGTEST.__shared_pool_size=121634816
                              DGTEST.__streams_pool_size=0
                              *.archive_lag_target=0
                              *.audit_file_dest='/oracle/product/admin/DGTEST/adump'
                              *.audit_trail='db'
                              *.compatible='11.2.0.0.0'
                              *.control_files='/u01/oradata/DGTEST/control01.ctl','/u01/oradata/DGTEST/control02.ctl'
                              *.db_block_size=8192
                              *.db_domain=''
                              *.db_name='DGTEST'
                              *.db_recovery_file_dest_size=5218762752
                              *.db_recovery_file_dest='/FAR/primary_flashback'
                              *.DB_UNIQUE_NAME='DGTEST_DG2'
                              *.dg_broker_start=TRUE
                              *.diagnostic_dest='/oracle/product'
                              *.dispatchers='(PROTOCOL=TCP) (SERVICE=DGTESTXDB)'
                              *.fal_server='DGTEST_DG1'
                              *.log_archive_config='DG_CONFIG=(DGTEST_DG2,DGTEST_DG1)'
                              
                              DGTEST.log_archive_dest_1='location="/arch/DGTEST/archivelogs"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
                              *.log_archive_dest_1='LOCATION="/arch/DGTEST"'
                              DGTEST.log_archive_dest_2='location="/arch/DGTEST_STBY"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
                              DGTEST.log_archive_dest_state_1='ENABLE'
                              DGTEST.log_archive_dest_state_2='ENABLE'
                              *.log_archive_dest_state_2='ENABLE'
                              
                              *.log_archive_format='%t_%s_%r.dbf'
                              DGTEST.log_archive_format='%t_%s_%r.dbf'
                              *.log_archive_max_processes=30
                              *.log_archive_min_succeed_dest=1
                              DGTEST.log_archive_trace=0
                              *.memory_target=419430400
                              *.open_cursors=300
                              *.processes=150
                              *.remote_login_passwordfile='EXCLUSIVE'
                              *.sec_case_sensitive_logon=FALSE
                              *.STANDBY_FILE_MANAGEMENT='AUTO'
                              *.undo_tablespace='UNDOTBS1'
                              You may want to look at the pfile from the new standby as well.
                              pfile from new standby:
                              ================
                              
                              DGTEST.__db_cache_size=67108864
                              DGTEST.__java_pool_size=4194304
                              DGTEST.__large_pool_size=4194304
                              DGTEST.__oracle_base='/oracle/product'
                              DGTEST.__pga_aggregate_target=209715200
                              DGTEST.__sga_target=209715200
                              DGTEST.__shared_io_pool_size=0
                              DGTEST.__shared_pool_size=121634816
                              DGTEST.__streams_pool_size=0
                              *.audit_file_dest='/oracle/product/admin/DGTEST/adump'
                              *.audit_trail='db'
                              *.compatible='11.2.0.0.0'
                              *.control_files='/u01/oradata/DGTEST/control01.ctl','/u01/oradata/DGTEST/control02.ctl'
                              *.db_block_size=8192
                              *.db_domain=''
                              *.db_name='DGTEST'
                              *.db_recovery_file_dest_size=5218762752
                              *.db_recovery_file_dest='/FAR/primary_flashback'
                              *.DB_UNIQUE_NAME='DGTEST_DG1'
                              *.dg_broker_start=TRUE
                              *.diagnostic_dest='/oracle/product'
                              *.dispatchers='(PROTOCOL=TCP) (SERVICE=DGTESTXDB)'
                              *.log_archive_config='DG_CONFIG=(DGTEST_DG1,DGTEST_DG2)'
                              
                              *.log_archive_dest_1='LOCATION=/arch/DGTEST'
                              *.log_archive_dest_2='service="DGTEST_DG2"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="DGTEST_DG2" net_timeout=30','valid_for=(all_logfiles,primary_role)'
                              *.log_archive_dest_3='LOCATION="/arch/DGTEST_STBY", valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
                              *.log_archive_dest_state_2='ENABLE'
                              *.log_archive_dest_state_3='ENABLE'
                              *.log_archive_format='ARC%S_%R.%T'
                              
                              *.LOG_ARCHIVE_MAX_PROCESSES=30
                              *.log_archive_min_succeed_dest=1
                              *.log_archive_trace=0
                              *.memory_target=419430400
                              *.open_cursors=300
                              *.processes=150
                              *.remote_login_passwordfile='EXCLUSIVE'
                              *.sec_case_sensitive_logon=FALSE
                              *.STANDBY_FILE_MANAGEMENT='AUTO'
                              Edited by: 974632 on Apr 2, 2013 7:53 AM
                              • 12. Re: after switchover to standby as primary, now how to switch back
                                mseberg
                                Not sure about the Duplicate entries but your issue ( assuming no Data Guard Broker ) is
                                DGTEST.log_archive_dest_1='location="/arch/DGTEST/archivelogs"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
                                *.log_archive_dest_1='LOCATION="/arch/DGTEST"'
                                DGTEST.log_archive_dest_2='location="/arch/DGTEST_STBY"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
                                Neither of these will transfer back to the New Standby so you can never Sync

                                Would expect an entry more like this
                                log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
                                If I strip down what you post for the working site it is ( so you need a similar entry back with that SERVICE and that DB_UNIQUE_NAME ) I assume the tnsnames and listener are correct.
                                service="DGTEST_DG2", LGWR ASYNC VALID_FOR=(all_logfiles,primary_role) db_unique_name="DGTEST_DG2"
                                I think the simple thing to do is add
                                log_archive_dest_3=
                                LOG_ARCHIVE_DEST_STATE_3=ENABLE
                                But you need a SERVICE for transfer.

                                I would set this to false since mixing Broker and SQL will give you endless headaches.
                                dg_broker_start=TRUE
                                Does this make sense?

                                mseberg
                                • 13. Re: after switchover to standby as primary, now how to switch back
                                  977635
                                  Hi mseberg.

                                  yes, what you wrote makes sense, and I made the recommended changes.
                                  What doesn't make sense is why it still isn't working.

                                  yes, my listeners and tnsnames are working and I can tnsping DGTEST, DGTEST_DG1, DGTEST_DG2 from both servers.

                                  I think at this point, I'm going to scrap it all again and start over.
                                  Once I get it up and working normally (before switching over or anything else), I'll hit you back up on a new thread.

                                  Thanks again for all your help.
                                  My main objective here is to reverse engineer our production environment and get a working test environment that mimics production.
                                  Question remains if our production environment will work. So far, my inclination is that it will not.
                                  I may be able to switch over from primary to standby, but I dont' think it will work going back to primary.
                                  That is what I need to test and confirm.

                                  Also, our production environment uses DG broker, so I assume I just need to get primary and standby up and running first, then create a configuration in the DG Broker.
                                  Not sure which I should do first... that will be the title of my next thread.
                                  Thanks again.