求助SHELL实现DG SWITCHOVER自动切换

jasperjohn

oracle11g r1 如何实现DG SWITCHOVER自动切换                                                                                                                                                                                                                                                                                                                        数据库为单实例；数据库版本：oracle 11g r1,
需要写一个shell脚本；实现primarydb to standbydb switchover 自动切换！
哪位仁兄有好的建议或者有类似的shell脚本，请添加我的个人QQ（251411209）！
非常感谢！

Maclean Liu(刘相兵

参考 Data Guard Switchover Unix shell script  http://gavinsoorma.com/2009/07/870/


On the machine where the Primary Database is running we need to run the following scripts

1. $ cd /var/opt/oracle/dataguard
   
2. $ ./pre_switchover_check.sh
   
3. $ ./make_me_standby.sh
   
4. 
   
5. On the machine where the Standby Database is running we need to run the following script:
   
6. 
   
7. $ cd /var/opt/oracle/dataguard
   
8. $ ./make_me_primary.sh
   
9. 
   
10. After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:
    
11. 
    
12. $ cd /var/opt/oracle/dataguard
    
13. $ ./start_recovery.sh
    
14. 
    
15. pre_switchover_check.sh
    
16. 
    
17. !/bin/ksh
    
18. 
    
19. if [ "$1" = "" ]
    
20. then
    
21. echo ""
    
22. echo "##################################################################"
    
23. echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT     #"
    
24. echo "#      SCRIPT USAGE : pre_switchover_check.sh        #"
    
25. echo "##################################################################"
    
26. echo ""
    
27. exit
    
28. fi
    
29. 
    
30. ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;
    
31. 
    
32. ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
    
33. if [ $? != 0 ]
    
34. then
    
35. echo ""
    
36. echo "##################################################################"
    
37. echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
    
38. echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
    
39. echo "##################################################################"
    
40. echo ""
    
41. exit
    
42. fi
    
43. 
    
44. . /var/opt/oracle/cronjobs/set$ORACLE_SID
    
45. 
    
46. DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;
    
47. 
    
48. echo ""
    
49. echo "##################################################################"
    
50. echo "#            PERFORMING PRE-SWITCHOVER CHECKS FOR $DB            #"
    
51. echo "##################################################################"
    
52. echo ""
    
53. 
    
54. sqlplus  -s /nolog |&
    
55. print -p  "connect / as sysdba"
    
56. print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off "
    
57. print -p  "set sqlprompt ''"
    
58. read  -p   JUNK
    
59. print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
    
60. read  -p   DBROLE
    
61. print -p  "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"
    
62. read  -p   FILESTAT
    
63. print -p  "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"
    
64. read  -p   BKPSTAT
    
65. print -p  "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP
    
66. FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
    
67. FROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
    
68. AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
    
69. FROM V\$ARCHIVED_LOG)),
    
70. (SELECT MAX(SEQUENCE#) LOG_APPLIED
    
71. FROM V\$ARCHIVED_LOG WHERE DEST_ID=2
    
72. AND APPLIED='YES'
    
73. AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"
    
74. read  -p   LOGGAP
    
75. print -p  "EXIT"
    
76. 
    
77. echo "CHECKING CURRENT DATABASE ROLE..."
    
78. if [ "$DBROLE" = "PHYSICAL STANDBY"  ]
    
79. then
    
80. echo ""
    
81. echo "##################################################################"
    
82. echo "# ERROR ! ! ! NOT  A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY  #"
    
83. echo "##################################################################"
    
84. echo ""
    
85. exit
    
86. else
    
87. echo ""
    
88. echo "   OK   "
    
89. echo ""
    
90. fi
    
91. 
    
92. echo "CHECKING LOG GAP BETWEEN PRIMARY & STANDBY..."
    
93. if [ $LOGGAP -ne 0 ]
    
94. then
    
95. echo ""
    
96. echo "######################################################################"
    
97. echo "# ERROR ! ! !  PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP  #"
    
98. echo "######################################################################"
    
99. echo ""
    
100. exit
     
101. else
     
102. echo ""
     
103. echo "   OK   "
     
104. echo ""
     
105. fi
     
106. 
     
107. echo "CHECKING FILES OFFLINE OR RECOVER STATUS..."
     
108. if [ $FILESTAT -ne 0 ]
     
109. then
     
110. echo ""
     
111. echo "##################################################################"
     
112. echo "#  ERROR ! ! !  ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS  #"
     
113. echo "##################################################################"
     
114. echo ""
     
115. exit
     
116. else
     
117. echo ""
     
118. echo "   OK   "
     
119. echo ""
     
120. fi
     
121. 
     
122. echo "CHECKING FILES IN BACKUP MODE..."
     
123. if [ $BKPSTAT -ne 0 ]
     
124. then
     
125. echo ""
     
126. echo "##################################################################"
     
127. echo "#      ERROR ! ! !  ONE OR MORE FILE/S ARE IN BACKUP MODE        #"
     
128. echo "##################################################################"
     
129. echo ""
     
130. exit
     
131. else
     
132. echo ""
     
133. echo "   OK   "
     
134. echo ""
     
135. fi
     
136. 
     
137. echo ""
     
138. echo "##################################################################"
     
139. echo "# ALL PRE-SWITCHOVER CHECKS SUCCEEDED... PROCEED WITH SWITCHOVER #"
     
140. echo "##################################################################"
     
141. echo ""
     
142. 
     
143. make_me_standby.sh
     
144. 
     
145. #!/bin/ksh
     
146. 
     
147. if [ "$1" = "" ]
     
148. then
     
149. echo ""
     
150. echo "##################################################################"
     
151. echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT     #"
     
152. echo "#        SCRIPT USAGE : make_me_standby.sh           #"
     
153. echo "##################################################################"
     
154. echo ""
     
155. exit
     
156. fi
     
157. 
     
158. ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;
     
159. 
     
160. ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
     
161. if [ $? != 0 ]
     
162. then
     
163. echo ""
     
164. echo "##################################################################"
     
165. echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
     
166. echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
     
167. echo "##################################################################"
     
168. echo ""
     
169. exit
     
170. fi
     
171. 
     
172. . /var/opt/oracle/cronjobs/set$ORACLE_SID
     
173. 
     
174. DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;
     
175. 
     
176. sqlplus  -s /nolog |&
     
177. print -p  "connect / as sysdba"
     
178. print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
     
179. print -p  "set sqlprompt ''"
     
180. read  -p   JUNK
     
181. print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
     
182. read  -p   DBROLE
     
183. print -p  "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"
     
184. read  -p   FILESTAT
     
185. print -p  "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"
     
186. read  -p   BKPSTAT
     
187. print -p  "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP
     
188. FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
     
189. FROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
     
190. AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
     
191. FROM V\$ARCHIVED_LOG)),
     
192. (SELECT MAX(SEQUENCE#) LOG_APPLIED
     
193. FROM V\$ARCHIVED_LOG WHERE DEST_ID=2
     
194. AND APPLIED='YES'
     
195. AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"
     
196. read  -p   LOGGAP
     
197. print -p  "EXIT"
     
198. 
     
199. if [ "$DBROLE" = "PHYSICAL STANDBY"  ]
     
200. then
     
201. echo ""
     
202. echo "##################################################################"
     
203. echo "# ERROR ! ! ! NOT  A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY  #"
     
204. echo "##################################################################"
     
205. echo ""
     
206. exit
     
207. fi
     
208. 
     
209. if [ $LOGGAP -ne 0 ]
     
210. then
     
211. echo ""
     
212. echo "######################################################################"
     
213. echo "# ERROR ! ! !  PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP  #"
     
214. echo "######################################################################"
     
215. echo ""
     
216. exit
     
217. fi
     
218. 
     
219. echo "CHECKING FILES OFFLINE OR RECOVER STATUS..."
     
220. if [ $FILESTAT -ne 0 ]
     
221. then
     
222. echo ""
     
223. echo "##################################################################"
     
224. echo "#  ERROR ! ! !  ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS  #"
     
225. echo "##################################################################"
     
226. echo ""
     
227. exit
     
228. fi
     
229. 
     
230. if [ $BKPSTAT -ne 0 ]
     
231. then
     
232. echo ""
     
233. echo "##################################################################"
     
234. echo "#      ERROR ! ! !  ONE OR MORE FILE/S ARE IN BACKUP MODE        #"
     
235. echo "##################################################################"
     
236. echo ""
     
237. exit
     
238. fi
     
239. 
     
240. echo ""
     
241. echo "##################################################################"
     
242. echo "#              ALL PRE-SWITCHOVER CHECKS SUCCEEDED...            #"
     
243. echo "       SWITCHING $DB TO STANDBY ROLE, PLEASE CONFIRM ...          "
     
244. echo "##################################################################"
     
245. echo ""
     
246. echo ""
     
247. echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
     
248. read ans
     
249. if [ "$ans" = 'Y' -o "$ans" = 'y' ]
     
250. then
     
251. sqlplus -s  /nolog < /tmp/make_me_standby.log
     
252. connect / as sysdba;
     
253. startup force;
     
254. alter database commit to switchover to standby with session shutdown;
     
255. shutdown immediate;
     
256. startup nomount;
     
257. alter database mount standby database;
     
258. select database_role from v\$database;
     
259. EOF
     
260. cat /tmp/make_me_standby.log
     
261. $ORACLE_HOME/bin/lsnrctl stop PRIMARY_$DB
     
262. else
     
263. echo "Quitting ....."
     
264. exit
     
265. fi
     
266. 
     
267. echo ""
     
268. echo "####################################################################"
     
269. echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "STANDBY"      #"
     
270. echo "#                                                                  #"
     
271. echo "# On OLD STANDBY Host please run the following script:         #"
     
272. echo "# /var/opt/oracle/dataguard/make_me_primary.sh                #"
     
273. echo "####################################################################"
     
274. echo ""
     
275. 
     
276. make_me_primary.sh
     
277. 
     
278. #!/bin/ksh
     
279. 
     
280. if [ "$1" = "" ]
     
281. then
     
282. echo ""
     
283. echo "##################################################################"
     
284. echo "#  PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT    #"
     
285. echo "#         SCRIPT USAGE : make_me_primary.sh          #"
     
286. echo "##################################################################"
     
287. echo ""
     
288. exit
     
289. fi
     
290. 
     
291. ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;
     
292. 
     
293. ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
     
294. if [ $? != 0 ]
     
295. then
     
296. echo ""
     
297. echo "##################################################################"
     
298. echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
     
299. echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
     
300. echo "##################################################################"
     
301. echo ""
     
302. exit
     
303. fi
     
304. 
     
305. . /var/opt/oracle/cronjobs/set$ORACLE_SID
     
306. 
     
307. DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;
     
308. 
     
309. sqlplus  -s /nolog |&
     
310. print -p  "connect / as sysdba"
     
311. print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
     
312. print -p  "set sqlprompt ''"
     
313. read  -p   JUNK
     
314. print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
     
315. read  -p   DBROLE
     
316. print -p  "exit"
     
317. 
     
318. if [ "$DBROLE" = "PRIMARY"  ]
     
319. then
     
320. echo ""
     
321. echo "##################################################################"
     
322. echo "#   ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY   #"
     
323. echo "##################################################################"
     
324. echo ""
     
325. exit
     
326. fi
     
327. 
     
328. echo ""
     
329. echo "##################################################################"
     
330. echo "#         SWITCHING $DB TO PRIMARY ROLE, PLEASE CONFIRM ...      #"
     
331. echo "##################################################################"
     
332. echo ""
     
333. echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
     
334. read ans
     
335. if [ "$ans" = 'Y' -o "$ans" = 'y' ]
     
336. then
     
337. sqlplus -s /nolog < /tmp/make_me_primary.log
     
338. connect / as sysdba;
     
339. alter database commit to switchover to primary with session shutdown;
     
340. shutdown immediate;
     
341. startup mount;
     
342. alter system set log_archive_dest_state_2=enable scope=both;
     
343. alter database set standby database to maximize performance;
     
344. alter database open;
     
345. select database_role from v\$database;
     
346. EOF
     
347. cat /tmp/make_me_primary.log
     
348. $ORACLE_HOME/bin/lsnrctl start PRIMARY_$DB
     
349. else
     
350. echo "Quitting ....."
     
351. exit
     
352. fi
     
353. 
     
354. echo "####################################################################"
     
355. echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "PRIMARY"      #"
     
356. echo "#                                                                  #"
     
357. echo "# On NEW STANDBY Host please run the following script:             #"
     
358. echo "# /var/opt/oracle/dataguard/start_recovery.sh                 #"
     
359. echo "####################################################################"
     
360. 
     
361. start_recovery.sh
     
362. 
     
363. #!/bin/ksh
     
364. 
     
365. if [ "$1" = "" ]
     
366. then
     
367. echo ""
     
368. echo "##################################################################"
     
369. echo "#   PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT   #"
     
370. echo "#      SCRIPT USAGE : start_recovery.sh              #"
     
371. echo "##################################################################"
     
372. echo ""
     
373. exit
     
374. fi
     
375. 
     
376. ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;
     
377. 
     
378. ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
     
379. if [ $? != 0 ]
     
380. then
     
381. echo ""
     
382. echo "##################################################################"
     
383. echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
     
384. echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
     
385. echo "##################################################################"
     
386. echo ""
     
387. exit
     
388. fi
     
389. 
     
390. . /var/opt/oracle/cronjobs/set$ORACLE_SID
     
391. 
     
392. DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;
     
393. 
     
394. sqlplus  -s /nolog |&
     
395. print -p  "connect / as sysdba"
     
396. print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
     
397. print -p  "set sqlprompt ''"
     
398. read  -p   JUNK
     
399. print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
     
400. read  -p   DBROLE
     
401. print -p  "exit"
     
402. 
     
403. if [ "$DBROLE" = "PRIMARY"  ]
     
404. then
     
405. echo ""
     
406. echo "##################################################################"
     
407. echo "#   ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY   #"
     
408. echo "##################################################################"
     
409. echo ""
     
410. exit
     
411. fi
     
412. 
     
413. echo ""
     
414. echo "##################################################################"
     
415. echo "#     STARTING RECOVERY FOR $DB  STANDBY, PLEASE CONFIRM ...     #"
     
416. echo "##################################################################"
     
417. echo ""
     
418. echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
     
419. read ans
     
420. if [ "$ans" = 'Y' -o "$ans" = 'y' ]
     
421. then
     
422. sqlplus -s /nolog < /tmp/start_recovery.log
     
423. connect / as sysdba;
     
424. recover managed standby database disconnect;
     
425. alter system set log_archive_dest_state_2=defer scope=both;
     
426. EOF
     
427. cat /tmp/start_recovery.log
     
428. ps -ef | grep ora_mrp0_$ORACLE_SID |grep -v grep > /dev/null
     
429. if [ $? != 0 ]
     
430. then
     
431. echo ""
     
432. echo "##################################################################"
     
433. echo "#           RECOVERY PROCESS NOT RUNNING... PLEASE CHECK         #"
     
434. echo "##################################################################"
     
435. echo ""
     
436. exit
     
437. else
     
438. echo "##################################################################"
     
439. echo "#           MRP PROCESS SUCESSFULLY STARTED                      #"
     
440. echo "##################################################################"
     
441. echo ""
     
442. echo "####################################################################"
     
443. echo "#                 SWITCHOVER COMPLETE                              #"
     
444. echo ""
     
445. echo "* Perform Database Post-Switchover Checklist!                      "
     
446. echo "####################################################################"
     
447. fi
     
448. else
     
449. echo "Quitting ....."
     
450. exit
     
451. fi

复制代码