switchover physical DB 互换 primary DB

PS：这一些实验是建立在我之前博客实验的基础上的

为什么要先alter 先切主库  再切备库，
因为如果先切备库的话，那么主库的一些日志可能备库收不到，导致不一致的情况发生

先切主库------>standby DB 切换之后主库是断开close的，重新open后查看其状态

1. 20:53:33 SYS @ slow >select name,database_role,protection_mode,switchover_status from v$database;
   
2. 
   
3. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
   
4. --------- ---------------- -------------------- --------------------
   
5. SLOW     PRIMARY     MAXIMUM AVAILABILITY TO STANDBY
   
6. 
   
7. 1 row selected.
   
8. 
   
9. Elapsed: 00:00:00.00
10. 20:55:15 SYS @ slow >edit
    
11. Wrote file afiedt.buf
    
12. 
    
13.   1* select username,sid,serial# from v$session where username is not null
    
14. 20:55:24 SYS @ slow >r
    
15.   1* select username,sid,serial# from v$session where username is not null
    
16. 
    
17. USERNAME             SID SERIAL#
    
18. ------------------------------ ---------- ----------
    
19. SYS                    1     5
    
20. 
    
21. 1 row selected.
    
22. 
    
23. Elapsed: 00:00:00.01
    
24. 20:55:25 SYS @ slow >r
    
25.   1* select username,sid,serial# from v$session where username is not null
    
26. 
    
27. USERNAME             SID SERIAL#
    
28. ------------------------------ ---------- ----------
    
29. SYS                    1     5
    
30. SCOTT                 41     47
    
31. 
    
32. 2 rows selected.
    
33. 
    
34. Elapsed: 00:00:00.00
    
35. 20:56:25 SYS @ slow >alter database commit to switchover to standby;
    
36. 
    
37. Database altered.
    
38. 
    
39. Elapsed: 00:00:01.89
    
40. 20:57:05 SYS @ slow >select name,database_role,protection_mode,switchover_status from v$database;
    
41. select name,database_role,protection_mode,switchover_status from v$database
    
42. *
    
43. ERROR at line 1:
    
44. ORA-01034: ORACLE not available
    
45. Process ID: 5713
    
46. Session ID: 1 Serial number: 5
    
47. 
    
48. 
    
49. Elapsed: 00:00:00.00
    
50. 20:58:08 SYS @ slow >select status from v$instance;
    
51. select status from v$instance
    
52. *
    
53. ERROR at line 1:
    
54. ORA-01034: ORACLE not available
    
55. Process ID: 5713
    
56. Session ID: 1 Serial number: 5
    
57. 
    
58. 
    
59. Elapsed: 00:00:00.00
    
60. 20:58:46 SYS @ slow >startup
    
61. ORACLE instance started.
    
62. 
    
63. Total System Global Area 521936896 bytes
    
64. Fixed Size         2254824 bytes
    
65. Variable Size         377489432 bytes
    
66. Database Buffers     138412032 bytes
    
67. Redo Buffers         3780608 bytes
    
68. Database mounted.
    
69. Database opened.
70. 21:07:19 SYS @ slow >select name,open_mode,database_role,protection_mode,switchover_status from v$database;
    
71. 
    
72. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
    
73. --------- -------------------- ---------------- -------------------- --------------------
    
74. SLOW     READ ONLY     PHYSICAL STANDBY MAXIMUM AVAILABILITY TO PRIMARY
    
75. 
    
76. 1 row selected.
    
77. 
    
78. Elapsed: 00:00:00.01
    
79. 21:07:55 SYS @ slow >recover managed standby database disconnect;
    
80. Media recovery complete.
    
81. 21:08:35 SYS @ slow >select name,open_mode,database_role,protection_mode,switchover_status from v$database;
    
82. 
    
83. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
    
84. --------- -------------------- ---------------- -------------------- --------------------
    
85. SLOW     READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY TO PRIMARY
    
86. 
    
87. 1 row selected.
    
88. 
    
89. Elapsed: 00:00:00.00
    
90. 21:09:02 SYS @ slow >

再切换备库----->primary DB  备库mount状态下操作  成功！！！

1. 20:52:21 SYS @ gotime >select name,database_role,protection_mode,switchover_status from v$database;
   
2. 
   
3. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
   
4. --------- ---------------- -------------------- --------------------
   
5. SLOW     PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED
   
6. 
   
7. 1 row selected.
   
8. 
   
9. Elapsed: 00:00:00.01
   
10. 20:53:12 SYS @ gotime >r
    
11.   1* select name,database_role,protection_mode,switchover_status from v$database
    
12. 
    
13. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
    
14. --------- ---------------- -------------------- --------------------
    
15. SLOW     PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED
    
16. 
    
17. 1 row selected.
    
18. 
    
19. Elapsed: 00:00:00.00
    
20. 20:59:16 SYS @ gotime >select username,sid from v$session where username is not null;
    
21. 
    
22. USERNAME             SID
    
23. ------------------------------ ----------
    
24. SYS                 24
    
25. 
    
26. 1 row selected.
    
27. 
    
28. Elapsed: 00:00:00.01
    
29. 21:02:26 SYS @ gotime >alter database commit to switchover to primary with session shutdown;
    
30. alter database commit to switchover to primary with session shutdown
    
31. *
    
32. ERROR at line 1:
    
33. ORA-16139: media recovery required
    
34. 
    
35. 
    
36. Elapsed: 00:00:00.01
    
37. 21:03:18 SYS @ gotime >recover managed standby database disconnect;
    
38. Media recovery complete.
    
39. 21:03:51 SYS @ gotime >alter database commit to switchover to primary with session shutdown;
    
40. 
    
41. Database altered.
    
42. 
    
43. Elapsed: 00:00:02.04
    
44. 21:05:00 SYS @ gotime >select name,database_role,protection_mode,switchover_status from v$database;
    
45. 
    
46. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
    
47. --------- ---------------- -------------------- --------------------
    
48. SLOW     PRIMARY     MAXIMUM AVAILABILITY NOT ALLOWED
    
49. 
    
50. 1 row selected.
    
51. 
    
52. Elapsed: 00:00:00.01
    
53. 21:05:12 SYS @ gotime >alter database open;
    
54. 
    
55. Database altered.
    
56. 
    
57. Elapsed: 00:00:00.58
    
58. 21:05:45 SYS @ gotime >select name,database_role,protection_mode,switchover_status from v$database;
    
59. 
    
60. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
    
61. --------- ---------------- -------------------- --------------------
    
62. SLOW     PRIMARY     MAXIMUM AVAILABILITY FAILED DESTINATION
    
63. 
    
64. 1 row selected.
    
65. 
    
66. Elapsed: 00:00:00.01
    
67. 21:06:13 SYS @ gotime >

-------------------------------------- 但是，switchover_status是failed destination 状态------------------------------------------

switchover之后的备库slow，重新启动listener

1. [oracle@slow ~]$ lsnrctl stop
   
2. 
   
3. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:12
   
4. 
   
5. Copyright (c) 1991, 2013, Oracle. All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
   
8. TNS-12541: TNS:no listener
   
9.  TNS-12560: TNS:protocol adapter error
   
10.   TNS-00511: No listener
    
11.    Linux Error: 111: Connection refused
    
12. [oracle@slow ~]$ lsnrctl start
    
13. 
    
14. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:20
    
15. 
    
16. Copyright (c) 1991, 2013, Oracle. All rights reserved.
    
17. 
    
18. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
    
19. 
    
20. TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    
21. System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    
22. Log messages written to /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
    
23. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))
    
24. 
    
25. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
    
26. STATUS of the LISTENER
    
27. ------------------------
    
28. Alias LISTENER
    
29. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    
30. Start Date 13-JAN-2018 21:31:20
    
31. Uptime 0 days 0 hr. 0 min. 0 sec
    
32. Trace Level off
    
33. Security ON: Local OS Authentication
    
34. SNMP OFF
    
35. Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    
36. Listener Log File /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
    
37. Listening Endpoints Summary...
    
38.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))
    
39. The listener supports no services
    
40. The command completed successfully
    
41. [oracle@slow ~]$ lsnrctl status
    
42. 
    
43. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:23
    
44. 
    
45. Copyright (c) 1991, 2013, Oracle. All rights reserved.
    
46. 
    
47. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
    
48. STATUS of the LISTENER
    
49. ------------------------
    
50. Alias LISTENER
    
51. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    
52. Start Date 13-JAN-2018 21:31:20
    
53. Uptime 0 days 0 hr. 0 min. 2 sec
    
54. Trace Level off
    
55. Security ON: Local OS Authentication
    
56. SNMP OFF
    
57. Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    
58. Listener Log File /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
    
59. Listening Endpoints Summary...
    
60.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))
    
61. The listener supports no services
    
62. The command completed successfully

关闭slow备库，再打开mount状态

1. 21:28:36 SYS @ slow >shutdown immediate;
   
2. Database closed.
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. 21:28:53 SYS @ slow >startup mount;
   
6. ORACLE instance started.
   
7. 
   
8. Total System Global Area 521936896 bytes
   
9. Fixed Size         2254824 bytes
   
10. Variable Size         377489432 bytes
    
11. Database Buffers     138412032 bytes
    
12. Redo Buffers         3780608 bytes
    
13. Database mounted.
    
14. 21:29:08 SYS @ slow >

等slow备库重新启动之后，状态为ready了

1. [oracle@slow ~]$ lsnrctl status
   
2. 
   
3. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:32:05
   
4. 
   
5. Copyright (c) 1991, 2013, Oracle. All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
   
8. STATUS of the LISTENER
   
9. ------------------------
   
10. Alias LISTENER
    
11. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    
12. Start Date 13-JAN-2018 21:31:20
    
13. Uptime 0 days 0 hr. 0 min. 44 sec
    
14. Trace Level off
    
15. Security ON: Local OS Authentication
    
16. SNMP OFF
    
17. Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    
18. Listener Log File /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
    
19. Listening Endpoints Summary...
    
20.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))
    
21. Services Summary...
    
22. Service "slow" has 1 instance(s).
    
23.   Instance "slow", status READY, has 1 handler(s) for this service...
    
24. The command completed successfully
    
25. [oracle@slow ~]$

现在的主库gotime这边重启listener

1. [grid@sink ~]$ lsnrctl stop
   
2. 
   
3. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:41
   
4. 
   
5. Copyright (c) 1991, 2013, Oracle. All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sink)(PORT=1521)))
   
8. The command completed successfully
   
9. [grid@sink ~]$ lsnrctl start
   
10. 
    
11. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:46
    
12. 
    
13. Copyright (c) 1991, 2013, Oracle. All rights reserved.
    
14. 
    
15. Starting /u01/11.2.0/grid/bin/tnslsnr: please wait...
    
16. 
    
17. TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    
18. System parameter file is /u01/11.2.0/grid/network/admin/listener.ora
    
19. Log messages written to /u01/app/grid/diag/tnslsnr/sink/listener/alert/log.xml
    
20. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sink)(PORT=1521)))
    
21. 
    
22. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sink)(PORT=1521)))
    
23. STATUS of the LISTENER
    
24. ------------------------
    
25. Alias LISTENER
    
26. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    
27. Start Date 13-JAN-2018 21:31:46
    
28. Uptime 0 days 0 hr. 0 min. 0 sec
    
29. Trace Level off
    
30. Security ON: Local OS Authentication
    
31. SNMP OFF
    
32. Listener Parameter File /u01/11.2.0/grid/network/admin/listener.ora
    
33. Listener Log File /u01/app/grid/diag/tnslsnr/sink/listener/alert/log.xml
    
34. Listening Endpoints Summary...
    
35.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sink)(PORT=1521)))
    
36. Services Summary...
    
37. Service "gotime" has 1 instance(s).
    
38.   Instance "gotime", status UNKNOWN, has 1 handler(s) for this service...
    
39. The command completed successfully
    
40. [grid@sink ~]$

gotime主库重新启动，到open

1. 21:32:12 SYS @ gotime >startup force;
   
2. ORACLE instance started.
   
3. 
   
4. Total System Global Area 521936896 bytes
   
5. Fixed Size         2254824 bytes
   
6. Variable Size         377489432 bytes
   
7. Database Buffers     138412032 bytes
   
8. Redo Buffers         3780608 bytes
   
9. Database mounted.
   
10. Database opened.
    
11. 21:32:35 SYS @ gotime >

switchover之后的备库slow，查询其状态，not allowed，正常了！！

1. 21:29:08 SYS @ slow >recover managed standby database disconnect;
   
2. Media recovery complete.
   
3. 21:29:32 SYS @ slow >select name,open_mode,database_role,protection_mode,switchover_status from v$database;
   
4. 
   
5. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
   
6. --------- -------------------- ---------------- -------------------- --------------------
   
7. SLOW     MOUNTED     PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED
   
8. 
   
9. 1 row selected.
   
10. 
    
11. Elapsed: 00:00:00.01
    
12. 21:33:16 SYS @ slow >

在查询状态，为to standby了，正常了！！

1. 21:32:35 SYS @ gotime >select name,open_mode,database_role,protection_mode,switchover_status from v$database
   
2. 21:32:45 2 ;
   
3. 
   
4. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
   
5. --------- -------------------- ---------------- -------------------- --------------------
   
6. SLOW     READ WRITE     PRIMARY        MAXIMUM AVAILABILITY TO STANDBY
   
7. 
   
8. 1 row selected.
   
9. 
   
10. Elapsed: 00:00:00.02
    
11. 21:32:47 SYS @ gotime >

---------------到这里就成功了！！！--------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31405474/viewspace-2150119/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31405474/viewspace-2150119/