DBA Hub

📋Steps in this guide1/5

Failover with No DG Broker using Flashback Database

Failover with No DG Broker using Flashback 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 Do you need to recreate all the Data Guard infrastructure once the failover has been performed? Ans: 1. If … Continue reading Failover with No DG Broker using Flashback →

oracle clusteringintermediate
by OracleDba
11 views
1

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

Section 2

0. Enviroment On Primary On Standby 1. Pre-quesites

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
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
On Primary
SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005pr          OPEN         PRIMARY

SQL> select switchover_status from v$database;

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

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
On Standby
SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005prg         OPEN         PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES
SQL>
3

Section 3

click here 2. Simulate failover: Bring down the primary database w5005pr DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production. 3. Steps to perform Failover with Physical Standby database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
- Configre Physical Standby database. Please click below for step by step configuration.
Please
click here
- Make sure that flashback database is enabled on both primary and standby database.

DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
4

Section 4

3. Steps to perform Failover with Physical Standby database On Standby (w5005prg) 1618441 On OLD Primary (W5005PR)

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
SQL>
alter database recover managed standby database cancel;
Database altered.

SQL>
alter database recover managed standby database finish force;
Database altered.

SQL>
Note: If Any error from above step then only follow below command.

alter database activate physical standby database;
SQL> select switchover_status from v$database;

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

SQL>
alter database commit to switchover to primary;
Database altered.

SQL>
alter database open;
Database altered.

SQL>  select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE
PRIMARY
w5005prg
SQL>
Find SCN on the new primary database (w5005prg)
SQL>
select standby_became_primary_scn from v$database;
STANDBY_BECAME_PRIMARY_SCN
--------------------------
1618441
SQL>
On OLD Primary (W5005PR)
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> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> 

SQL> select name,INSTANCE_NAME from v$database,v$instance;

NAME       INSTANCE_NAME
--------- ----------------
W5005PR
w5005pr
SQL>
flashback database to scn 1618441;  <---
Flashback complete.

SQL>
alter database convert to physical standby;
Database altered.

SQL>
shutdown immediate;
ORA-01507: database not mounted


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>
alter database mount standby database;
Database altered.

SQL>
alter database open;
Database altered.

SQL>
alter database recover managed standby database disconnect from session using current logfile;
Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR
READ ONLY WITH APPLY PHYSICAL STANDBY
w5005pr
SQL>

SQL>
select process,status,sequence# from v$managed_standby;
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

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      CLOSING               4
ARCH      CONNECTED             0
ARCH      CLOSING               5
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING               6
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
MRP0      APPLYING_LOG          7 <--
RFS       IDLE                  7
RFS       IDLE                  0

37 rows selected.

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