This discussion is archived
13 Replies Latest reply: Apr 2, 2013 9:29 AM by 977635 RSS

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

977635 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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