DBA Hub

📋Steps in this guide1/7

Failover with DG Broker (No-Flashback)

Failover with DG Broker (No-Flashback) Overview: Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database 0. Enviroment 1. Primary Database Crashed 2. Steps to perform Failover with Physical Standby database 3. Reinstate database old primary (w5005pr) … Continue reading Failover with DG Broker

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Overview: Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database

Code/Command (click line numbers to comment):

1
Note: Flashback database is not enabled on both primary and standby (in our case)
2

Section 2

Note: Flashback database is not enabled on both primary and standby (in our case) Do you need to recreate all the Data Guard infrastructure once the failover has been performed? Ans: 1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary to recreate standby from scratch. 2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch. 0. Enviroment
3

Section 3

0. Enviroment 1. Primary Database Crashed w5005pr - Primary database w5005prg - Physical standby database

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
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

DGMGRL>
show configuration;
Configuration - w5005pr

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

Configuration Status:
SUCCESS

DGMGRL>


[oracle@rac1 ~]$ ps -ef | grep pmon | grep -v grep
oracle 3540 1 0 Jan11 ? 00:00:02 asm_pmon_+ASM
[oracle@rac1 ~]$


Tue Jan 12 00:00:22 2016
CKPT (ospid: 2499): terminating the instance due to error 472
Tue Jan 12 00:00:23 2016
License high water mark = 5
Instance terminated by CKPT, pid = 2499
USER (ospid: 3029): terminating the instance
Instance terminated by USER, pid = 3029 <---- Instance terminated
4

Section 4

Instance terminated by USER, pid = 3029 <---- Instance terminated 2. Steps to perform Failover with Physical Standby database Configuration Status: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ORA-16625: cannot reach database "w5005pr" DGM-17017: unable to determine configuration status failover to w5005prg;

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
[oracle@rac2 ~]$
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@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005pr - Primary database
w5005prg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "w5005pr"
DGM-17017: unable to determine configuration status
DGMGRL>
DGMGRL>
failover to w5005prg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "w5005prg"
<-- Succeeded
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
5

Section 5

3. Reinstate database old primary rac1 4. Rebuild Standby database for new primary (w5005prg)

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
DGMGRL>
reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

/*
Failing it would have needed to configure flashback database to be able to do so automatically…

If you try to start w5005pr database in MOUNT state and issue again a request to reinstate you will now get clear error message
*/


[oracle@
rac1
~]$
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL>
startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
Database mounted.
SQL>


DGMGRL>
reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16827: Flashback Database is disabled
<-- Actual error
Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

SQL>
shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL>
startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
SQL>
6

Section 6

rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr duplicate target database for standby from active database nofilenamecheck; 5. Enable old primary database

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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
[oracle@rac2 ~]$
rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 12 00:22:53 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: W5005PR (DBID=4158252169)
connected to auxiliary database: W5005PR (not mounted)

RMAN>
duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 12-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' ;
}
executing Memory Script

Starting backup at 12-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK
Finished backup at 12-JAN-16

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/w5005pr/controlfile/current.268.900456457';
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005prg.f tag=TAG20160112T002322 RECID=5 STAMP=900894203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/w5005prg/datafile/system.264.900546429
output file name=+DATA/w5005pr/datafile/system.274.900894219 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/w5005prg/datafile/sysaux.265.900546465
output file name=+DATA/w5005pr/datafile/sysaux.275.900894265 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/w5005prg/datafile/undotbs1.266.900546499
output file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/w5005prg/datafile/users.267.900546507
output file name=+DATA/w5005pr/datafile/users.277.900894303 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=900894305 file name=+DATA/w5005pr/datafile/system.274.900894219
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=900894305 file name=+DATA/w5005pr/datafile/sysaux.275.900894265
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=900894305 file name=+DATA/w5005pr/datafile/undotbs1.276.900894299
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=900894305 file name=+DATA/w5005pr/datafile/users.277.900894303
Finished Duplicate Db at 12-JAN-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

[oracle@
rac2
~
]
$ 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@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database (disabled)
ORA-16795: the standby database needs to be re-created
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>
enable database w5005pr;
Enabled.
DGMGRL> show configuration;

Configuration - w5005pr

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

Configuration Status:
SUCCESS

DGMGRL>
7

Section 7

rac2 ~ 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!