DBA Hub

📋Steps in this guide1/5

Performing Switchover with DG Broker

Performing Switchover with DG Broker Overview: A switchover is a role reversal between the primary database and one of its standby  databases.  A switchover operation guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the … Continue reading Swi

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Overview: A switchover is a role reversal between the primary database and one of its standby  databases.  A switchover operation guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to recreate either database. 0. Enviroment
2

Section 2

1. Pre-Switchover tasks 2. Vefify TNS Entries

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Primary:
Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
Standby:
Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

- Verify that there is network connectivity between the primary and standby locations. 
- Always recommened test the switchover in your testing system befre working on production.
- Verify primary database READ WRITE and standby instance mounted.
- Verify there are no active users connected to database.
- Make sure last redo data transmitted from primary to standby and applied.
- Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
	alter system set job_queue_processes=0 ;
	alter system set aq_tm_processes = 0 ;
	alter system set dbwr_io_slaves = 0 ;
3

Section 3

Primary Standby 3. Steps to perform switchover with Physical Standby databases

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
[oracle@rac1 ~]$
cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005pr_dgmgrl)
                (SID_NAME = w5005pr)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

[oracle@rac1 ~]$

[oracle@rac1 ~]$
cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac1 ~]$

[oracle@rac1 ~]$
/u01/app/11.2.0/grid/bin/lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2016 00:37:10

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-JAN-2016 17:25:42
Uptime                    0 days 7 hr. 11 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005pr" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005prXDB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005pr_DGB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005pr_dgmgrl" has 1 instance(s).
  Instance "w5005pr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$

[oracle@rac2 ~]$
cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005prg_dgmgrl)
                (SID_NAME = w5005prg)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

[oracle@rac2 ~]$


[oracle@rac2 ~]$
cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac2 ~]$

[oracle@rac2 ~]$
/u01/app/11.2.0/grid/bin/lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2016 00:37:27

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-JAN-2016 17:25:53
Uptime                    0 days 7 hr. 11 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005prg" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prgXDB" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prg_DGB" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prg_dgmgrl" has 1 instance(s).
  Instance "w5005prg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$
4

Section 4

switchover to w5005prg; 4. Switch back to old primary (If required) Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
[oracle@rac1 ~]$
dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>
connect sys/sys@w5005pr
Connected.
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
w5005pr  - Primary database
w5005prg - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL> s
how database verbose w5005pr
Database - w5005pr

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    w5005pr

  Properties:
    DGConnectIdentifier             = 'w5005pr'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'w5005pr'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=w5005pr_DGMGRL)(INSTANCE_NAME=w5005pr)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> 



DGMGRL>
show configuration
Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
w5005pr  - Primary database
w5005prg - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
switchover to w5005prg;
Performing switchover NOW, please wait...
New primary database "w5005prg" is opening...
Operation requires shutdown of instance "w5005pr" on database "w5005pr"
Shutting down instance "w5005pr"...
ORACLE instance shut down.
Operation requires startup of instance "w5005pr" on database "w5005pr"
Starting instance "w5005pr"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "w5005prg"
DGMGRL>
DGMGRL>
show configuration
Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
w5005prg - Primary database
w5005pr  - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL>
switchover to w5005pr;
Performing switchover NOW, please wait...
New primary database "w5005pr" is opening...
Operation requires shutdown of instance "w5005prg" on database "w5005prg"
Shutting down instance "w5005prg"...
ORACLE instance shut down.
Operation requires startup of instance "w5005prg" on database "w5005prg"
Starting instance "w5005prg"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "w5005pr"
DGMGRL>
DGMGRL>
show configuration
Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
w5005pr  - Primary database
w5005prg - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
5

Section 5

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!