ITPub博客

首页 > 数据库 > Oracle > maximum performance 转换 maximum protection

maximum performance 转换 maximum protection

原创 Oracle 作者:大鲨鱼o0O 时间:2018-01-13 12:26:56 0 删除 编辑



这部分需要添加standby redo log,添加日志相关操作请参照与DG physical DB 转 snapshot DB第一部分,
以下内容摘自physical DB 转 snapshot DB第一部分

在备库设置快速恢复区,大小,路径,创建4组standby redo log

  1. [root@sink ~]# su - oracle
  2. [oracle@sink ~]$ echo $ORACLE_SID
  3. sink
  4. [oracle@sink ~]$ export ORACLE_SID=gotime
  5. [oracle@sink ~]$ echo $ORACLE_SID
  6. gotime
  7. [oracle@sink ~]$ !sql
  8. sqlplus / as sysdba
  9. SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 12 10:25:51 2018
  10. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  11. Connected to:
  12. Oracle Database 11g Enterprise Edition Release 11.2.0.4.- 64bit Production
  13. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  14. 10:25:51 SYS @ gotime >select status from v$instance;

  15. STATUS
  16. ------------
  17. MOUNTED

  18. row selected.

  19. Elapsed: 00:00:00.01
  20. 10:26:03 SYS @ gotime >select name,database_role,protection_mode,open_mode from v$database;
  21. NAME     DATABASE_ROLE PROTECTION_MODE    OPEN_MODE
  22. --------- ---------------- -------------------- --------------------
  23. SLOW     PHYSICAL STANDBY MAXIMUM PERFORMANCE    MOUNTED
  24. row selected.
  25. Elapsed: 00:00:00.02
  26. 10:26:54 SYS @ gotime >show parameter recover
  27. NAME                 TYPE     VALUE
  28. ------------------------------------ ----------- ------------------------------
  29. db_recovery_file_dest         string
  30. db_recovery_file_dest_size     big integer 0
  31. db_unrecoverable_scn_tracking     boolean     TRUE
  32. recovery_parallelism         integer     0
  33. 10:29:42 SYS @ gotime >recover managed standby database cancel;
  34. ORA-16136: Managed Standby Recovery not active
  35. 10:30:31 SYS @ gotime >alter system set db_recovery_file_dest_size=4g;
  36. System altered.
  37. Elapsed: 00:00:00.00
  38. 10:32:25 SYS @ gotime >edit
  39. Wrote file afiedt.buf
  40.   1* alter system set db_recovery_file_dest='/dsk1'
  41. 10:32:36 SYS @ gotime >r
  42.   1* alter system set db_recovery_file_dest='/dsk1'
  43. System altered.
  44. Elapsed: 00:00:00.00
  45. 10:32:37 SYS @ gotime >show parameter recover;
  46. NAME                 TYPE     VALUE
  47. ------------------------------------ ----------- ------------------------------
  48. db_recovery_file_dest         string     /dsk1
  49. db_recovery_file_dest_size     big integer 4G
  50. db_unrecoverable_scn_tracking     boolean     TRUE
  51. recovery_parallelism         integer     0
  52. 10:39:51 SYS @ gotime >edit
  53. Wrote file afiedt.buf
  54.   1* alter system set db_recovery_file_dest_size=6g
  55. 10:40:14 SYS @ gotime >r
  56.   1* alter system set db_recovery_file_dest_size=6g
  57. System altered.
  58. Elapsed: 00:00:00.00
  59. 10:40:15 SYS @ gotime >alter database convert to snapshot standby;
  60. alter database convert to snapshot standby
  61. *
  62. ERROR at line 1:
  63. ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/12/2018 10:40:26'.
  64. ORA-38788: More standby database recovery is needed
  65. Elapsed: 00:00:00.00
  66. ----------------意思是提示备库没有创建standby redo log所以下面创建4组-------------------
  67. 13:16:50 SYS @ gotime >alter system set db_recovery_file_dest_size=4g;
  68. System altered.
  69. Elapsed: 00:00:00.01
  70. 13:18:31 SYS @ gotime >edit
  71. Wrote file afiedt.buf
  72.   1* select group#,thread#,sequence#,archived,status from v$standby_log
  73. 13:18:55 SYS @ gotime >r
  74.   1* select group#,thread#,sequence#,archived,status from v$standby_log
  75. no rows selected
  76. Elapsed: 00:00:00.00
  77. 13:18:55 SYS @ gotime >select member from v$logfile;
  78. MEMBER
  79. ------------------------
  80. /u01/app/oracle/oradata/gotime/redo01a.log
  81. /u01/app/oracle/oradata/gotime/redo01b.log
  82. /u01/app/oracle/oradata/gotime/redo02a.log
  83. /u01/app/oracle/oradata/gotime/redo02b.log
  84. /u01/app/oracle/oradata/gotime/redo03a.log
  85. /u01/app/oracle/oradata/gotime/redo03b.log
  86. rows selected.
  87. Elapsed: 00:00:00.01
 

备库上依据redo log的路径建立4组standby redo log

  1. 13:21:44 SYS @ gotime >edit
  2. Wrote file afiedt.buf
  3.   1 alter database add standby logfile group 4
  4.   2* ('/u01/app/oracle/oradata/gotime/&a') size 200m
  5. 13:22:33 SYS @ gotime >r
  6.   1 alter database add standby logfile group 4
  7.   2* ('/u01/app/oracle/oradata/gotime/&a') size 200m
  8. Enter value for a: standby04.log
  9. old 2: ('/u01/app/oracle/oradata/gotime/&a') size 200m
  10. new 2: ('/u01/app/oracle/oradata/gotime/standby04.log') size 200m
  11. Database altered.
  12. Elapsed: 00:00:01.74
  13. 13:23:02 SYS @ gotime >edit
  14. Wrote file afiedt.buf
  15.   1* alter database add standby logfile group 4
  16. 13:23:12 SYS @ gotime >edit
  17. Wrote file afiedt.buf
  18.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b') size 200m
  19. 13:23:58 SYS @ gotime >r
  20.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b') size 200m
  21. Enter value for a: 5
  22. Enter value for b: standby05.log
  23. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b')size 200m
  24. new 1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/gotime/standby05.log') size 200m
  25. Database altered.
  26. Elapsed: 00:00:02.02
  27. 13:24:16 SYS @ gotime >r
  28.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b') size 200m
  29. Enter value for a: 6
  30. Enter value for b: standby06.log
  31. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b')size 200m
  32. new 1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/gotime/standby06.log') size 200m

  33. Database altered.

  34. Elapsed: 00:00:01.98
  35. 13:24:35 SYS @ gotime >r
  36.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b') size 200m
  37. Enter value for a: 7
  38. Enter value for b: standby07.log
  39. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/gotime/&b')size 200m
  40. new 1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/gotime/standby07.log') size 200m

  41. Database altered.

  42. Elapsed: 00:00:01.69
  43. 13:24:50 SYS @ gotime >select group#,thread#,sequence#,archived,status from v$standby_log
  44. 13:25:15 2 ;

  45.     GROUP# THREAD# SEQUENCE# ARC STATUS
  46. ---------- ---------- ---------- --- ----------
  47.      4 0 0 YES UNASSIGNED
  48.      5 0 0 YES UNASSIGNED
  49.      6 0 0 YES UNASSIGNED
  50.      7 0 0 YES UNASSIGNED

  51. rows selected.

  52. Elapsed: 00:00:00.00
  53. 13:25:16 SYS @ gotime >

打开主库,设置快速恢复区大小,路径,

  1. 10:24:42 SYS @ slow >startup
  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. 10:24:57 SYS @ slow >select name,database_role,protection_mode,open_mode from v$database;

  11. NAME     DATABASE_ROLE PROTECTION_MODE    OPEN_MODE
  12. --------- ---------------- -------------------- --------------------
  13. SLOW     PRIMARY     MAXIMUM PERFORMANCE    READ WRITE

  14. row selected.

  15. Elapsed: 00:00:00.02
  16. 10:28:35 SYS @ slow >show parameter recover;

  17. NAME                 TYPE     VALUE
  18. ------------------------------------ ----------- ------------------------------
  19. db_recovery_file_dest         string
  20. db_recovery_file_dest_size     big integer 0
  21. db_unrecoverable_scn_tracking     boolean     TRUE
  22. recovery_parallelism         integer     0
  23. 10:37:21 SYS @ slow >alter system set db_recovery_file_dest_size=4g;

  24. System altered.

  25. Elapsed: 00:00:00.00
  26. 10:37:54 SYS @ slow >alter system set db_recovery_file_dest='/dsk1';

  27. System altered.

  28. Elapsed: 00:00:00.01
  29. 10:38:19 SYS @ slow >select member from v$logfile;

  30. MEMBER
  31. -----------------------------------------
  32. /u01/app/oracle/oradata/slow/redo01a.log
  33. /u01/app/oracle/oradata/slow/redo01b.log
  34. /u01/app/oracle/oradata/slow/redo02a.log
  35. /u01/app/oracle/oradata/slow/redo02b.log
  36. /u01/app/oracle/oradata/slow/redo03a.log
  37. /u01/app/oracle/oradata/slow/redo03b.log

  38. rows selected.

  39. Elapsed: 00:00:00.01

主库上依据redo log的路径建立4组standby redo log

  1. 13:26:06 SYS @ slow >edit
  2. Wrote file afiedt.buf

  3.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size 200m
  4. 13:27:02 SYS @ slow >r
  5.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size 200m
  6. Enter value for a: 4
  7. Enter value for b: standby04.log
  8. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size200m
  9. new 1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/slow/standby04.log') size 200m

  10. Database altered.

  11. Elapsed: 00:00:02.73
  12. 13:27:19 SYS @ slow >r
  13.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size 200m
  14. Enter value for a: 5 
  15. Enter value for b: standby05.log
  16. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size200m
  17. new 1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/slow/standby05.log') size 200m

  18. Database altered.

  19. Elapsed: 00:00:03.50
  20. 13:27:44 SYS @ slow >r
  21.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size 200m
  22. Enter value for a: 6
  23. Enter value for b: standby06.log
  24. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size200m
  25. new 1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/slow/standby06.log') size 200m

  26. Database altered.

  27. Elapsed: 00:00:02.04
  28. 13:28:03 SYS @ slow >r
  29.   1* alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size 200m
  30. Enter value for a: 7 
  31. Enter value for b: standby07.log
  32. old 1: alter database add standby logfile group &('/u01/app/oracle/oradata/slow/&b') size200m
  33. new 1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/slow/standby07.log') size 200m

  34. Database altered.

  35. Elapsed: 00:00:02.33
  36. 13:28:19 SYS @ slow >select group#,thread#,sequence#,archived,status from v$standby_log;

  37.     GROUP# THREAD# SEQUENCE# ARC STATUS
  38. ---------- ---------- ---------- --- ----------
  39.      4     0     0 YES UNASSIGNED
  40.      5     0     0 YES UNASSIGNED
  41.      6     0     0 YES UNASSIGNED
  42.      7     0     0 YES UNASSIGNED

  43. rows selected.

  44. Elapsed: 00:00:00.00



主库修改传输方式sync affirm ,下面备库也将跟着修改

  1. 13:55:51 SYS @ slow >r
  2.   1* alter system set log_archive_dest_2='SERVICE=gotime OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gotime' scope=spfile

  3. System altered.

  4. Elapsed: 00:00:00.00


备库没有设置spfile,是以pfile打开库的,所以创建spifle,以spfile打开,才能修改

  1. 13:57:43 SYS @ gotime >edit
  2. Wrote file afiedt.buf

  3.   1* alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
  4. 13:58:01 SYS @ gotime >r
  5.   1* alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
  6. alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
  7. *
  8. ERROR at line 1:
  9. ORA-32001: write to SPFILE requested but no SPFILE is in use


  10. Elapsed: 00:00:00.00
  11. 13:58:02 SYS @ gotime >create pfile from spfile;
  12. create pfile from spfile
  13. *
  14. ERROR at line 1:
  15. ORA-01565: error in identifying file '?/dbs/spfile@.ora'
  16. ORA-27037: unable to obtain file status
  17. Linux-x86_64 Error: 2: No such file or directory
  18. Additional information: 3


  19. Elapsed: 00:00:00.00
  20. 13:59:06 SYS @ gotime >create spfile from pfile;

  21. File created.

  22. Elapsed: 00:00:00.02
  23. 14:00:45 SYS @ gotime >alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile;
  24. alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
  25. *
  26. ERROR at line 1:
  27. ORA-32001: write to SPFILE requested but no SPFILE is in use


  28. Elapsed: 00:00:00.00
  29. 14:01:06 SYS @ gotime >shutdown immediate;
  30. ORA-01109: database not open


  31. Database dismounted.
  32. ORACLE instance shut down.
  33. 14:01:37 SYS @ gotime >startup mount;
  34. ORACLE instance started.

  35. Total System Global Area 521936896 bytes
  36. Fixed Size         2254824 bytes
  37. Variable Size         377489432 bytes
  38. Database Buffers     138412032 bytes
  39. Redo Buffers         3780608 bytes
  40. Database mounted.
  41. 14:01:57 SYS @ gotime >alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile;

  42. System altered.

  43. Elapsed: 00:00:00.01
  44. 14:02:09 SYS @ gotime >


主库开始转换,成功的从maximum performance 到 maximum protection模式

  1. 13:55:52 SYS @ slow >startup mount 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. 14:09:15 SYS @ slow >select status from v$instance;

  10. STATUS
  11. ------------
  12. MOUNTED

  13. 1 row selected.

  14. Elapsed: 00:00:00.01
  15. 14:09:29 SYS @ slow >select name,database_role,protection_mode from v$database;

  16. NAME     DATABASE_ROLE PROTECTION_MODE
  17. --------- ---------------- --------------------
  18. SLOW     PRIMARY     MAXIMUM PERFORMANCE

  19. 1 row selected.

  20. Elapsed: 00:00:00.01
  21. 14:10:02 SYS @ slow >alter database set standby database to maximize protection;

  22. Database altered.

  23. Elapsed: 00:00:00.01
  24. 14:10:52 SYS @ slow >alter database open;

  25. Database altered.

  26. Elapsed: 00:00:03.83
  27. 14:11:20 SYS @ slow >select name,database_role,protection_mode from v$database;

  28. NAME     DATABASE_ROLE PROTECTION_MODE
  29. --------- ---------------- --------------------
  30. SLOW     PRIMARY     MAXIMUM PROTECTION

  31. 1 row selected.

  32. Elapsed: 00:00:00.00
  33. 14:11:45 SYS @ slow >

主库转换成功之后,备库的状态也随着改变了

  1. 14:14:09 SYS @ gotime >select status from v$instance;

  2. STATUS
  3. ------------
  4. MOUNTED

  5. 1 row selected.

  6. Elapsed: 00:00:00.01
  7. 14:14:38 SYS @ gotime >select name,database_role,protection_mode from v$database;

  8. NAME     DATABASE_ROLE PROTECTION_MODE
  9. --------- ---------------- --------------------
  10. SLOW     PHYSICAL STANDBY MAXIMUM PROTECTION

  11. 1 row selected.

  12. Elapsed: 00:00:00.00
  13. 14:15:03 SYS @ gotime >alter database open;

  14. Database altered.

  15. Elapsed: 00:00:00.41
  16. 14:15:10 SYS @ gotime >select name,database_role,protection_mode from v$database;

  17. NAME     DATABASE_ROLE PROTECTION_MODE
  18. --------- ---------------- --------------------
  19. SLOW     PHYSICAL STANDBY MAXIMUM PROTECTION

  20. 1 row selected.

  21. Elapsed: 00:00:00.01



主库做一些DML操作,但是最后一步没有提交

  1. 14:18:31 SYS @ slow >select * from t2017;

  2.     DEPTNO DNAME     LOC
  3. ---------- -------------- -------------
  4.     10 ACCOUNTING     NEW YORK
  5.     20 RESEARCH     DALLAS
  6.     30 SALES     CHICAGO
  7.     40 OPERATIONS     BOSTON

  8. 4 rows selected.

  9. Elapsed: 00:00:00.00
  10. 14:18:46 SYS @ slow >insert into t2017 select * from t2017;

  11. 4 rows created.

  12. Elapsed: 00:00:00.00
  13. 14:19:29 SYS @ slow >select * from t2017;

  14.     DEPTNO DNAME     LOC
  15. ---------- -------------- -------------
  16.     10 ACCOUNTING     NEW YORK
  17.     20 RESEARCH     DALLAS
  18.     30 SALES     CHICAGO
  19.     40 OPERATIONS     BOSTON
  20.     10 ACCOUNTING     NEW YORK
  21.     20 RESEARCH     DALLAS
  22.     30 SALES     CHICAGO
  23.     40 OPERATIONS     BOSTON

  24. 8 rows selected.

  25. Elapsed: 00:00:00.00
  26. 14:19:43 SYS @ slow >commit;

  27. Commit complete.

  28. Elapsed: 00:00:00.00
  29. 14:19:45 SYS @ slow >insert into t2017 select * from t2017;

  30. 8 rows created.

  31. Elapsed: 00:00:00.00
  32. 14:20:42 SYS @ slow >select * from t2017;

  33.     DEPTNO DNAME     LOC
  34. ---------- -------------- -------------
  35.     10 ACCOUNTING     NEW YORK
  36.     20 RESEARCH     DALLAS
  37.     30 SALES     CHICAGO
  38.     40 OPERATIONS     BOSTON
  39.     10 ACCOUNTING     NEW YORK
  40.     20 RESEARCH     DALLAS
  41.     30 SALES     CHICAGO
  42.     40 OPERATIONS     BOSTON
  43.     10 ACCOUNTING     NEW YORK
  44.     20 RESEARCH     DALLAS
  45.     30 SALES     CHICAGO

  46.     DEPTNO DNAME     LOC
  47. ---------- -------------- -------------
  48.     40 OPERATIONS     BOSTON
  49.     10 ACCOUNTING     NEW YORK
  50.     20 RESEARCH     DALLAS
  51.     30 SALES     CHICAGO
  52.     40 OPERATIONS     BOSTON

  53. 16 rows selected.

  54. Elapsed: 00:00:00.00
  55. 14:21:18 SYS @ slow >alter system switch logfile;

  56. System altered.

  57. Elapsed: 00:00:00.12
  58. 14:23:28 SYS @ slow >


备库重新mount并应用日志(media recover)查询信息,查不到最后没有提交的信息,正常!

  1. 14:23:38 SYS @ gotime >startup mount 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. 14:24:22 SYS @ gotime >recover managed standby database disconnect;
  10. Media recovery complete.
  11. 14:24:51 SYS @ gotime >select * from t2017;
  12. select * from t2017
  13.               *
  14. ERROR at line 1:
  15. ORA-01219: database not open: queries allowed on fixed tables/views only


  16. Elapsed: 00:00:00.00
  17. 14:25:07 SYS @ gotime >alter database open;
  18. alter database open
  19. *
  20. ERROR at line 1:
  21. ORA-10456: cannot open standby database; media recovery session may be in progress


  22. Elapsed: 00:00:00.00
  23. 14:25:18 SYS @ gotime >recover managed standby database cancel;
  24. Media recovery complete.
  25. 14:25:42 SYS @ gotime >alter database open;

  26. Database altered.

  27. Elapsed: 00:00:00.21
  28. 14:25:47 SYS @ gotime >select * from t2017;

  29.     DEPTNO DNAME     LOC
  30. ---------- -------------- -------------
  31.     10 ACCOUNTING     NEW YORK
  32.     20 RESEARCH     DALLAS
  33.     30 SALES     CHICAGO
  34.     40 OPERATIONS     BOSTON
  35.     10 ACCOUNTING     NEW YORK
  36.     20 RESEARCH     DALLAS
  37.     30 SALES     CHICAGO
  38.     40 OPERATIONS     BOSTON

  39. 8 rows selected.

  40. Elapsed: 00:00:00.01
  41. 14:25:57 SYS @ gotime >

这个时候我们把备库的网络切断,然后再备库提交试试看...

  1. [root@sink ~]# ifconfig
  2. eth0 Link encap:Ethernet HWaddr 08:00:27:03:A5:94
  3.           inet addr:192.168.10.6 Bcast:192.168.10.255 Mask:255.255.255.0
  4.           UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
  5.           RX packets:29770 errors:0 dropped:0 overruns:0 frame:0
  6.           TX packets:13633 errors:0 dropped:0 overruns:0 carrier:0
  7.           collisions:0 txqueuelen:1000
  8.           RX bytes:32088544 (30.6 MiB) TX bytes:1186858 (1.1 MiB)

  9. lo Link encap:Local Loopback
  10.           inet addr:127.0.0.1 Mask:255.0.0.0
  11.           UP LOOPBACK RUNNING MTU:16436 Metric:1
  12.           RX packets:4899 errors:0 dropped:0 overruns:0 frame:0
  13.           TX packets:4899 errors:0 dropped:0 overruns:0 carrier:0
  14.           collisions:0 txqueuelen:0
  15.           RX bytes:3552488 (3.3 MiB) TX bytes:3552488 (3.3 MiB)

  16. [root@sink ~]# ifconfig eth0 down

  17. Connection closed by foreign host.

  18. Disconnected from remote host(sink_root) at 14:26:42.

  19. Type `help


主库探知备库down,大约5~6分钟之后主库自己也选择自杀了(shutdown abort)

  1. 14:26:28 SYS @ slow >commit;
  2. commit
  3. *
  4. ERROR at line 1:
  5. ORA-03113: end-of-file on communication channel
  6. Process ID: 4226
  7. Session ID: 1 Serial number: 5


  8. Elapsed: 00:05:22.15
  9. 14:31:54 SYS @ slow >select status from v$instance;
  10. ERROR:
  11. ORA-03114: not connected to ORACLE


  12. Elapsed: 00:00:00.00
  13. 14:34:54 SYS @ slow >


主库最后的告警日志信息

  1. ***********************************************************************

  2. Fatal NI connect error 12543, connecting to:
  3.  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sink)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=gotime)(CID=(PROGRAM=oracle)(HOST=slow)(USER=oracle))))

  4.   VERSION INFORMATION:
  5.     TNS for Linux: Version 11.2.0.4.0 - Production
  6.     TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  7.   Time: 13-JAN-2018 14:31:54
  8.   Tracing not turned on.
  9.   Tns error struct:
  10.     ns main err code: 12543
  11.     
  12. TNS-12543: TNS:destination host unreachable
  13.     ns secondary err code: 12560
  14.     nt main err code: 513
  15.     
  16. TNS-00513: Destination host unreachable
  17.     nt secondary err code: 113
  18.     nt OS err code: 0
  19. Error 12543 received logging on to the standby
  20. Sat Jan 13 14:31:54 2018
  21. LGWR: Error 12543 attaching to RFS for reconnect
  22. Error 16198 for archive log file 3 to 'gotime'
  23. Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
  24. LGWR: All standby destinations have failed
  25. ******************************************************
  26. WARNING: All standby database destinations have failed
  27. WARNING: Instance shutdown required to protect primary
  28. ******************************************************
  29. LGWR (ospid: 4180): terminating the instance due to error 16098
  30. Sat Jan 13 14:31:54 2018
  31. System state dump requested by (instance=1, osid=4180 (LGWR)), summary=[abnormal instance termination].
  32. System State dumped to trace file /u01/app/oracle/diag/rdbms/slow/slow/trace/slow_diag_4160_20180113143154.trc
  33. Dumping diagnostic data in directory=[cdmp_20180113143154], requested by (instance=1, osid=4180 (LGWR)), summary=[abnormal instance termination].
  34. Instance terminated by LGWR, pid = 4180


最后我们去备库虚拟机的界面,进入后ifconfig eth0 up




接着通过Xshell连接到备库的虚拟主机,查看状态恢复正常

  1. Connecting to 192.168.10.6:22...
  2. Connection established.
  3. To escape to local shell, press 'Ctrl+Alt+]'.

  4. Last login: Sat Jan 13 14:37:06 2018
  5. [root@sink ~]# su - oracle
  6. [oracle@sink ~]$ echo $ORACLE_SID
  7. sink
  8. [oracle@sink ~]$ export ORACLE_SID=gotime
  9. [oracle@sink ~]$ echo $ORACLE_SID
  10. gotime
  11. [oracle@sink ~]$ !sql
  12. sqlplus / as sysdba

  13. SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 13 14:56:19 2018

  14. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  15. Connected to:
  16. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  17. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  18. 14:56:19 SYS @ gotime >select status from v$instance;

  19. STATUS
  20. ------------
  21. OPEN

  22. 1 row selected.

  23. Elapsed: 00:00:00.01
  24. 14:56:33 SYS @ gotime >select name,database_role,protection_mode from v$database;

  25. NAME     DATABASE_ROLE PROTECTION_MODE
  26. --------- ---------------- --------------------
  27. SLOW     PHYSICAL STANDBY MAXIMUM PROTECTION

  28. 1 row selected.

  29. Elapsed: 00:00:00.00
  30. 14:57:21 SYS @ gotime >

主库再启动到open,因为是自杀(shutdown abort)所以会比较就一点,好了,一切正常!成功!

  1. 14:34:54 SYS @ slow >select status form v$instance;
  2. ERROR:
  3. ORA-03114: not connected to ORACLE


  4. Elapsed: 00:00:00.00
  5. 14:58:55 SYS @ slow >startup
  6. ORA-24324: service handle not initialized
  7. ORA-01041: internal error. hostdef extension doesn't exist
  8. 14:58:59 SYS @ slow >exit
  9. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  11. [oracle@slow ~]$ !sql
  12. sqlplus '/as sysdba



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

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

注册时间:2016-10-26

  • 博文量
    32
  • 访问量
    5182