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
