Recreate failovered primary database using Flashback Database

简介:
很多朋友一直对DataGuard的fast-start failover持怀疑的态度;通过observer的观察,(Fast-Start Failover)FSFO提供了一种在primary数据库不可用情况下自动故障切换到standby数据库的能力。造成很多朋友不愿意使用FSFO的原因之一是故障切换后不得不重建原primary数据库,不过如果我们能配合使用10g中的闪回数据库特性的话,这一重建数据库的工作便也可以一劳永逸了。
?
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
195
196
197
198
199
200
201
202
203
204
205
206
207
/* 启用fast_start failover的前提之一是在 primary 和standby库上都启用flashback  database  */
 
SQL>  select  from  v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle  Database  11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS  for  Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
/* 当前的 primary 库情况 */
 
SQL>  select  open_mode ,database_role,flashback_on  from  v$ database ;
OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ  WRITE            PRIMARY           YES
 
 
SQL>  alter  session  set  nls_date_format= 'YYYY-MM-DD hh24:mi:ss' ;
 
Session altered.
 
SQL>  select  OLDEST_FLASHBACK_TIME   from  v$flashback_database_log;
 
OLDEST_FLASHBACK_TI
-------------------
2011-02-19 22:40:39
 
/* 当前的standby库情况 */
SQL>  select  open_mode ,database_role,flashback_on  from  v$ database ;
 
OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ  ONLY  WITH  APPLY PHYSICAL STANDBY YES
 
SQL>  alter  session  set  nls_date_format= 'YYYY-MM-DD hh24:mi:ss' ;
 
Session altered.
SQL>  select  OLDEST_FLASHBACK_TIME   from  v$flashback_database_log;
 
OLDEST_FLASHBACK_TI
-------------------
2011-02-19 22:34:56
 
 
DGMGRL> edit configuration  set  property faststartfailoverthreshold=10;
Property  "faststartfailoverthreshold"  updated
 
DGMGRL> show configuration verbose;
 
Configuration - pro
 
   Protection Mode: MaxAvailability
   Databases:
     sbdb -  Primary  database
     prod - Physical standby  database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
/* 另开一个终端启动observer */
 
DGMGRL> start observer
Observer started
 
DGMGRL> show fast_start failover
 
Fast-Start Failover: DISABLED
 
   Threshold:        10 seconds
   Target:           (none)
   Observer:         rh3.oracle.com
   Lag Limit:        30 seconds
   Shutdown  Primary TRUE
   Auto-reinstate:    TRUE
 
Configurable Failover Conditions
   Health Conditions:
     Corrupted Controlfile          YES
     Corrupted Dictionary           YES
     Inaccessible Logfile             NO
     Stuck Archiver                   NO
     Datafile Offline               YES
 
   Oracle Error Conditions:
     (none)
 
 
/* 启动快速故障切换  */
 
DGMGRL> enable fast_start failover
Enabled.
 
/* 记录实际切换前的时间 */
 
[maclean@rh3 ~]$  date
Sat Feb 19 23:35:44 CST 2011
 
[maclean@rh2 ~]$ ps -ef|grep pmon|grep -v grep
maclean  25165     1  0 22:58 ?        00:00:00 ora_pmon_SBDB
maclean  26080     1  0 23:04 ?        00:00:00 ora_pmon_PROD
 
[maclean@rh2 ~]$ kill -9 25165
 
/* 通过kill pmon进程造成主库crash,引发fast-start failover */
 
/* observer观察到 primary 库意外终止后,实施了快速故障切换 */
 
23:37:27.92  Saturday, February 19, 2011
Initiating Fast-Start Failover  to  database  "prod" ...
Performing failover NOW, please wait...
Failover succeeded, new  primary  is  "prod"
23:37:33.69  Saturday, February 19, 2011
23:39:17.98  Saturday, February 19, 2011
Initiating reinstatement  for  database  "sbdb" ...
Reinstating  database  "sbdb" , please wait...
Error: ORA-16653: failed  to  reinstate  database
 
Failed.
Reinstatement  of  database  "sbdb"  failed
23:39:26.30  Saturday, February 19, 2011
 
23:40:05.00  Saturday, February 19, 2011
Initiating reinstatement  for  database  "sbdb" ...
Reinstating  database  "sbdb" , please wait...
Error: ORA-16653: failed  to  reinstate  database
 
Failed.
Reinstatement  of  database  "sbdb"  failed
23:40:09.24  Saturday, February 19, 2011
 
/* 接下来我们通过闪回数据库来将失败切换后的 primary 库flashback到faliover之前以便重用*/
 
SQL> startup mount;
 
SQL> flashback  database  to  timestamp  to_timestamp( '2011-02-19 23:30:44' , 'YYYY-MM-DD hh24:mi:ss' );
flashback  database  to  timestamp  to_timestamp( '2011-02-19 23:35:44' , 'YYYY-MM-DD hh24:mi:ss' )
*
ERROR  at  line 1:
ORA-38754: FLASHBACK  DATABASE  not  started; required redo log  is  not  available
ORA-38762: redo logs needed  for  SCN 1143987  to  SCN  End - of -Redo
ORA-38761: redo log  sequence  10  in  thread 1, incarnation 3 could  not  be
accessed
 
/* 缺少最近的归档日志而无法完成闪回,需要到目前的 primary 库中查找 */
 
SQL>  select  name  from  v$archived_log  where  FIRST_CHANGE#<1143987  and  NEXT_CHANGE#>1143987;
 
NAME
--------------------------------------------------------------------------------
/s01/fast_recovery_area/PROD/archivelog/2011_02_19/o1_mf_1_10_6ozpzh9c_.arc
 
SQL>  alter  database  register physical logfile  '/s01/fast_recovery_area/PROD/archivelog/2011_02_19/o1_mf_1_10_6ozpzh9c_.arc' ;
Database  altered.
 
 
SQL> flashback  database  to  timestamp  to_timestamp( '2011-02-19 23:30:44' , 'YYYY-MM-DD hh24:mi:ss' );
Flashback complete.
 
/* 成功闪回后,observer将主动去尝试reinstate这个目前可用的standby库,如以下日志*/
 
23:46:20.16  Saturday, February 19, 2011
Initiating reinstatement  for  database  "sbdb" ...
Reinstating  database  "sbdb" , please wait...
Operation requires shutdown  of  instance  "SBDB"  on  database  "sbdb"
Shutting down instance  "SBDB" ...
ORA-01109:  database  not  open
 
Database  dismounted.
ORACLE instance shut down.
Operation requires startup  of  instance  "SBDB"  on  database  "sbdb"
Starting instance  "SBDB" ...
ORACLE instance started.
Database  mounted.
Continuing  to  reinstate  database  "sbdb"  ...
Reinstatement  of  database  "sbdb"  succeeded
23:47:13.49  Saturday, February 19, 2011
 
DGMGRL> show configuration verbose
 
Configuration - pro
 
   Protection Mode: MaxAvailability
   Databases:
     prod -  Primary  database
     sbdb - (*) Physical standby  database
 
   (*) Fast-Start Failover target
 
Fast-Start Failover: ENABLED
 
   Threshold:        10 seconds
   Target:           sbdb
   Observer:         rh3.oracle.com
   Lag Limit:        30 seconds ( not  in  use)
   Shutdown  Primary TRUE
   Auto-reinstate:    TRUE
 
Configuration Status:
SUCCESS
 
/*可以通过show configuration命令监控到当前 primary 与standby库都处于可用状态,
    且fast_start failover也为启用状态 */
相关文章
|
5月前
|
SQL 移动开发 Java
“\r\n### Error updating database. ,解决问题的思路在于认真参考给的错误提示,看错误提示,这里我的数据表,没有写primary key 导致的
“\r\n### Error updating database. ,解决问题的思路在于认真参考给的错误提示,看错误提示,这里我的数据表,没有写primary key 导致的
|
SQL Java 关系型数据库
spring boot集成mybatis只剩两个sql 并提示 Cannot obtain primary key information from the database, generated objects may be incomplete
spring boot集成mybatis只剩两个sql 并提示 Cannot obtain primary key information from the database, generated objects may be incomplete
156 0
spring boot集成mybatis只剩两个sql 并提示 Cannot obtain primary key information from the database, generated objects may be incomplete
|
数据库
Recover standby database after primary resetlogs
一般在主库需要恢复并open resetlogs 方式打开的情况下,备库需要重做来构建主备同步架构,但有些情况其实不必大费周章,只需要备库开启flashback功能,就可以在较短的时间内恢复主备同步进程。
1557 0
|
SQL 监控 关系型数据库
mysql主从复制错误:Last_SQL_Error: Error &#39;Duplicate entry &#39;327&#39; for key &#39;PRIMARY&#39;&#39; on query. Default database: &#39;xxx&#39;. Query:
这个算不算解决,我都不太清楚,因为我感觉网上的说法,只是把错误忽略了,不表示以后用从库时不会出问题!!! 解决的办法是在从库上执行: mysql> slave stop; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> slave s...
1306 0
|
7月前
|
SQL Oracle 关系型数据库
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database (
96 2
|
7月前
|
Oracle 关系型数据库
19c 开启Oracle Database Vault
19c 开启Oracle Database Vault
166 1
|
7月前
|
SQL Oracle 关系型数据库
Connect to Autonomous Database Using Oracle Database Tools
Connect to Autonomous Database Using Oracle Database Tools
63 1
|
6月前
|
Oracle 关系型数据库 Linux
Requirements for Installing Oracle Database/Client 19c on OL8 or RHEL8 64-bit (x86-64) (Doc ID 2668780.1)
Requirements for Installing Oracle Database/Client 19c on OL8 or RHEL8 64-bit (x86-64) (Doc ID 2668780.1)
52 0