ITPub博客

首页 > 数据库 > Oracle > switchover physical DB 互换 primary DB

switchover physical DB 互换 primary DB

原创 Oracle 作者:大鲨鱼o0O 时间:2018-01-13 21:43:35 0 删除 编辑
PS:这一些实验是建立在我之前博客实验的基础上的

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

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

  1. 20:53:33 SYS @ slow >select name,database_role,protection_mode,switchover_status from v$database;

  2. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
  3. --------- ---------------- -------------------- --------------------
  4. SLOW     PRIMARY     MAXIMUM AVAILABILITY TO STANDBY

  5. 1 row selected.

  6. Elapsed: 00:00:00.00
  7. 20:55:15 SYS @ slow >edit
  8. Wrote file afiedt.buf

  9.   1* select username,sid,serial# from v$session where username is not null
  10. 20:55:24 SYS @ slow >r
  11.   1* select username,sid,serial# from v$session where username is not null

  12. USERNAME             SID SERIAL#
  13. ------------------------------ ---------- ----------
  14. SYS                    1     5

  15. 1 row selected.

  16. Elapsed: 00:00:00.01
  17. 20:55:25 SYS @ slow >r
  18.   1* select username,sid,serial# from v$session where username is not null

  19. USERNAME             SID SERIAL#
  20. ------------------------------ ---------- ----------
  21. SYS                    1     5
  22. SCOTT                 41     47

  23. 2 rows selected.

  24. Elapsed: 00:00:00.00
  25. 20:56:25 SYS @ slow >alter database commit to switchover to standby;

  26. Database altered.

  27. Elapsed: 00:00:01.89
  28. 20:57:05 SYS @ slow >select name,database_role,protection_mode,switchover_status from v$database;
  29. select name,database_role,protection_mode,switchover_status from v$database
  30. *
  31. ERROR at line 1:
  32. ORA-01034: ORACLE not available
  33. Process ID: 5713
  34. Session ID: 1 Serial number: 5


  35. Elapsed: 00:00:00.00
  36. 20:58:08 SYS @ slow >select status from v$instance;
  37. select status from v$instance
  38. *
  39. ERROR at line 1:
  40. ORA-01034: ORACLE not available
  41. Process ID: 5713
  42. Session ID: 1 Serial number: 5


  43. Elapsed: 00:00:00.00
  44. 20:58:46 SYS @ slow >startup
  45. ORACLE instance started.

  46. Total System Global Area 521936896 bytes
  47. Fixed Size         2254824 bytes
  48. Variable Size         377489432 bytes
  49. Database Buffers     138412032 bytes
  50. Redo Buffers         3780608 bytes
  51. Database mounted.
  52. Database opened.
  53. 21:07:19 SYS @ slow >select name,open_mode,database_role,protection_mode,switchover_status from v$database;

  54. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
  55. --------- -------------------- ---------------- -------------------- --------------------
  56. SLOW     READ ONLY     PHYSICAL STANDBY MAXIMUM AVAILABILITY TO PRIMARY

  57. 1 row selected.

  58. Elapsed: 00:00:00.01
  59. 21:07:55 SYS @ slow >recover managed standby database disconnect;
  60. Media recovery complete.
  61. 21:08:35 SYS @ slow >select name,open_mode,database_role,protection_mode,switchover_status from v$database;

  62. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
  63. --------- -------------------- ---------------- -------------------- --------------------
  64. SLOW     READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY TO PRIMARY

  65. 1 row selected.

  66. Elapsed: 00:00:00.00
  67. 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. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
  3. --------- ---------------- -------------------- --------------------
  4. SLOW     PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED

  5. 1 row selected.

  6. Elapsed: 00:00:00.01
  7. 20:53:12 SYS @ gotime >r
  8.   1* select name,database_role,protection_mode,switchover_status from v$database

  9. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
  10. --------- ---------------- -------------------- --------------------
  11. SLOW     PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED

  12. 1 row selected.

  13. Elapsed: 00:00:00.00
  14. 20:59:16 SYS @ gotime >select username,sid from v$session where username is not null;

  15. USERNAME             SID
  16. ------------------------------ ----------
  17. SYS                 24

  18. 1 row selected.

  19. Elapsed: 00:00:00.01
  20. 21:02:26 SYS @ gotime >alter database commit to switchover to primary with session shutdown;
  21. alter database commit to switchover to primary with session shutdown
  22. *
  23. ERROR at line 1:
  24. ORA-16139: media recovery required


  25. Elapsed: 00:00:00.01
  26. 21:03:18 SYS @ gotime >recover managed standby database disconnect;
  27. Media recovery complete.
  28. 21:03:51 SYS @ gotime >alter database commit to switchover to primary with session shutdown;

  29. Database altered.

  30. Elapsed: 00:00:02.04
  31. 21:05:00 SYS @ gotime >select name,database_role,protection_mode,switchover_status from v$database;

  32. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
  33. --------- ---------------- -------------------- --------------------
  34. SLOW     PRIMARY     MAXIMUM AVAILABILITY NOT ALLOWED

  35. 1 row selected.

  36. Elapsed: 00:00:00.01
  37. 21:05:12 SYS @ gotime >alter database open;

  38. Database altered.

  39. Elapsed: 00:00:00.58
  40. 21:05:45 SYS @ gotime >select name,database_role,protection_mode,switchover_status from v$database;

  41. NAME     DATABASE_ROLE PROTECTION_MODE    SWITCHOVER_STATUS
  42. --------- ---------------- -------------------- --------------------
  43. SLOW     PRIMARY     MAXIMUM AVAILABILITY FAILED DESTINATION

  44. 1 row selected.

  45. Elapsed: 00:00:00.01
  46. 21:06:13 SYS @ gotime >

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

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

  1. [oracle@slow ~]$ lsnrctl stop

  2. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:12

  3. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  4. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
  5. TNS-12541: TNS:no listener
  6.  TNS-12560: TNS:protocol adapter error
  7.   TNS-00511: No listener
  8.    Linux Error: 111: Connection refused
  9. [oracle@slow ~]$ lsnrctl start

  10. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:20

  11. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  12. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

  13. TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  14. System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
  15. Log messages written to /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
  16. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))

  17. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
  18. STATUS of the LISTENER
  19. ------------------------
  20. Alias LISTENER
  21. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  22. Start Date 13-JAN-2018 21:31:20
  23. Uptime 0 days 0 hr. 0 min. 0 sec
  24. Trace Level off
  25. Security ON: Local OS Authentication
  26. SNMP OFF
  27. Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
  28. Listener Log File /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
  29. Listening Endpoints Summary...
  30.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))
  31. The listener supports no services
  32. The command completed successfully
  33. [oracle@slow ~]$ lsnrctl status

  34. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:23

  35. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  36. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slow)(PORT=1521)))
  37. STATUS of the LISTENER
  38. ------------------------
  39. Alias LISTENER
  40. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  41. Start Date 13-JAN-2018 21:31:20
  42. Uptime 0 days 0 hr. 0 min. 2 sec
  43. Trace Level off
  44. Security ON: Local OS Authentication
  45. SNMP OFF
  46. Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
  47. Listener Log File /u01/app/oracle/diag/tnslsnr/slow/listener/alert/log.xml
  48. Listening Endpoints Summary...
  49.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slow)(PORT=1521)))
  50. The listener supports no services
  51. 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. Total System Global Area 521936896 bytes
  8. Fixed Size         2254824 bytes
  9. Variable Size         377489432 bytes
  10. Database Buffers     138412032 bytes
  11. Redo Buffers         3780608 bytes
  12. Database mounted.
  13. 21:29:08 SYS @ slow >

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

  1. [oracle@slow ~]$ lsnrctl status

  2. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:32:05

  3. Copyright (c) 1991, 2013, Oracle. All rights reserved.

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


现在的主库gotime这边重启listener

  1. [grid@sink ~]$ lsnrctl stop

  2. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:41

  3. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  4. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sink)(PORT=1521)))
  5. The command completed successfully
  6. [grid@sink ~]$ lsnrctl start

  7. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2018 21:31:46

  8. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  9. Starting /u01/11.2.0/grid/bin/tnslsnr: please wait...

  10. TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  11. System parameter file is /u01/11.2.0/grid/network/admin/listener.ora
  12. Log messages written to /u01/app/grid/diag/tnslsnr/sink/listener/alert/log.xml
  13. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sink)(PORT=1521)))

  14. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sink)(PORT=1521)))
  15. STATUS of the LISTENER
  16. ------------------------
  17. Alias LISTENER
  18. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  19. Start Date 13-JAN-2018 21:31:46
  20. Uptime 0 days 0 hr. 0 min. 0 sec
  21. Trace Level off
  22. Security ON: Local OS Authentication
  23. SNMP OFF
  24. Listener Parameter File /u01/11.2.0/grid/network/admin/listener.ora
  25. Listener Log File /u01/app/grid/diag/tnslsnr/sink/listener/alert/log.xml
  26. Listening Endpoints Summary...
  27.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sink)(PORT=1521)))
  28. Services Summary...
  29. Service "gotime" has 1 instance(s).
  30.   Instance "gotime", status UNKNOWN, has 1 handler(s) for this service...
  31. The command completed successfully
  32. [grid@sink ~]$

gotime主库重新启动,到open

  1. 21:32:12 SYS @ gotime >startup force;
  2. ORACLE instance started.

  3. Total System Global Area 521936896 bytes
  4. Fixed Size         2254824 bytes
  5. Variable Size         377489432 bytes
  6. Database Buffers     138412032 bytes
  7. Redo Buffers         3780608 bytes
  8. Database mounted.
  9. Database opened.
  10. 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. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
  5. --------- -------------------- ---------------- -------------------- --------------------
  6. SLOW     MOUNTED     PHYSICAL STANDBY MAXIMUM AVAILABILITY NOT ALLOWED

  7. 1 row selected.

  8. Elapsed: 00:00:00.01
  9. 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. NAME     OPEN_MODE     DATABASE_ROLE    PROTECTION_MODE SWITCHOVER_STATUS
  4. --------- -------------------- ---------------- -------------------- --------------------
  5. SLOW     READ WRITE     PRIMARY        MAXIMUM AVAILABILITY TO STANDBY

  6. 1 row selected.

  7. Elapsed: 00:00:00.02
  8. 21:32:47 SYS @ gotime >

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





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

请登录后发表评论 登录
全部评论

注册时间:2016-10-26

  • 博文量
    32
  • 访问量
    5232