Friday, March 25, 2016

RDBMS -- Dataguard performing a "Switch Over" on a Standby environment which is consisting of 6 nodes

We have seen how to create a standby environment consisting of 6 nodes in the previous post: http://ermanarslan.blogspot.com.tr/2016/03/rdbms-dataguard-physical-standby.html

In this post, we will see how to perform a failover on that physical standby environment.
I will give you the switchover instructions and initialization parameters for this operation.

So lets recall our current (source) and target(after switchover) standby topologies;

The current topology is as follows;

DB1(primary)--->DB2--->DB3---->DB4
 |                                              | ----->DB5
 |
 |--->DB6


The target topology in case of a switchover scenario which may be implemented for planned downtime is as follows;

DB5
|
|
|->DB3(primary)------->DB1----->DB2
      |                                   |    
      | ->DB4                       |------->DB6


SWITCH OVER CONSIDERATIONS: 


  • Ensure there is no delay in applying redo on the standby database which is planned to be the new primary environment.
  • Ensure that the initialization parameters defined in the primary database is appropriate for the possbile future role as a standby database in the context of the overall protection mode.
  • Ensure that standby redo log files are configured on the primary database.
  • For each temporary table, verify that temporary files associated with that table on the primary database also exist on the standby database.
  • Before performing a switchover from an Oracle RAC primary database to a physical standby database, shut down all but one primary database instance. Any primary database instances shut down at this time can be started after the switchover completes.
  • Before performing a switchover or a failover to an Oracle RAC physical standby database, shut down all but one standby database instance. Any standby database instances shut down at this time can be restarted after the role transition completes.



SWITCH OVER PLAN: 


  • Ensure DB3 init.ora/spfile as it configured to transport redo to DB1, DB4 and DB5 (in standby role)
  • Ensure DB1 init.ora/spfile to transport redo to DB2 and DB6  (in standby role)
  • Change log_archive_dest_2 on DB1 from SYNC to ASYNC. 
  • DISABLE Dest3 on DB2 -> LOG_ARCHIVE_DEST_3= 'SERVICE=DB3  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)  DB_UNIQUE_NAME=DB3'
  • Ensure DB4 and DB5 is in sync with DB1 and stop application services.
  • Stop managed recovery on DB1 to stop transporting redo to DB2 and DB6.
  • Switch over DB1 with DB3 and make DB3 the new primary.
  • Control all the standby databases in the configuration and ensure they are in sync with the new primary (DB3)

[oracle@demoorcl ~]$ . setDB1.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 15:37:28 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select switchover_status from v$database;

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

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL>shutdown immediate
SQL>startup nomount
SQL>alter database mount standby database;

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

System altered.

SQL> alter system set log_archive_dest_state_6=defer scope=memory;

System altered.

[oracle@demoorcl ~]$ . setDB3.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 15:48:02 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select switchover_status from v$database;

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

[oracle@demoorcl ~]$ . setDB3.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 15:48:02 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select switchover_status from v$database;

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

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             184550744 bytes
Database Buffers          432013312 bytes
Redo Buffers                7532544 bytes
Database mounted.
Database opened.
SQL> exit


[oracle@demoorcl ~]$ . setDB1.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 DB3
demoorcl.dardanel.com
11.2.0.3.0        22-MAR-16 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@demoorcl trace]$ cd
[oracle@demoorcl ~]$ . setDB1.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 16:11:28 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY


DB3 INIT ORA:
----------------
LOG_ARCHIVE_DEST_1='LOCATION=/u01/ERMAN/db3_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DB3'

log_archive_dest_2='SERVICE=DB1 LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=DB1'

log_archive_dest_5='SERVICE=DB5 LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=DB5'

log_archive_dest_4='SERVICE=DB4 LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=DB4'

LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(DB1,DB2,DB3,DB4,DB5,DB6)'
fal_client=''
fal_server=''

DB4 INIT ORA:
----------------
LOG_ARCHIVE_DEST_1='LOCATION=/u01/ERMAN/db4_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DB4'

LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(DB1,DB2,DB3,DB4,DB5,DB6)'

FAL_SERVER=DB3

FAL_CLIENT=DB4

DB5 INIT ORA:
----------------
LOG_ARCHIVE_DEST_1='LOCATION=/u01/ERMAN/db5_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DB5'

LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(DB1,DB2,DB3,DB4,DB5,DB6)'

FAL_SERVER=DB3

FAL_CLIENT=DB5

DB1 INIT ORA:
----------------
log_archive_dest_6='SERVICE=DB6  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)  DB_UNIQUE_NAME=DB6'

log_archive_dest_2='SERVICE=DB2  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)  DB_UNIQUE_NAME=DB2'

log_archive_dest_1='LOCATION=/u01/ERMAN/db1_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DB1'

log_archive_config='DG_CONFIG=(DB3,DB1,DB2,DB4,DB5,DB6)

fal_server=DB3

fal_client=DB1

DB2 INIT ORA:
----------------
LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(DB1,DB2,DB3,DB4,DB5,DB6)'
log_archive_dest_1='LOCATION=/u01/ERMAN/db2_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DB2'
FAL_SERVER=DB1
FAL_CLIENT=DB2

DB6 INIT ORA:
----------------
log_archive_config='DG_CONFIG=(DB3,DB1,DB2,DB4,DB5,DB6)'
log_archive_dest_1 ='LOCATION=/u01/ERMAN/db6_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DB6'
FAL_SERVER=DB1
FAL_CLIENT=DB6


CHECK IF LOG APPLY SERVICES WORKS:
----------------------------------------------------------------

[oracle@demoorcl ~]$ . setDB3.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 15:11:06 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> !sleep 30;

SQL>
SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;SQL>   2    3    4    5    6    7

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             82

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@demoorcl ~]$ . setDB1.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 15:12:13 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;  2    3    4    5    6    7    8    9   10

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                82               82

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@demoorcl ~]$ . setDB5.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 15:13:06 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;  2    3    4    5    6    7    8    9   10

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                82               82

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@demoorcl ~]$ . setDB4.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 15:13:12 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;  2    3    4    5    6    7    8    9   10

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                82               82

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@demoorcl ~]$ . setDB2.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 15:13:46 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;  2    3    4    5    6    7    8    9   10

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                82               82

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@demoorcl ~]$ . setDB6.env
[oracle@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 15:13:53 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;  2    3    4    5    6    7    8    9   10

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                82               82

No comments :

Post a Comment