ITPub博客

首页 > 数据库 > Oracle > Oracle primary DB 到 snapshot DB 第一部分

Oracle primary DB 到 snapshot DB 第一部分

原创 Oracle 作者:大鲨鱼o0O 时间:2018-01-12 14:52:51 0 删除 编辑

\

在备库设置快速恢复区,大小,路径,创建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 &a ('/u01/app/oracle/oradata/gotime/&b') size 200m
  19. 13:23:58 SYS @ gotime >r
  20.   1* alter database add standby logfile group &a ('/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 &a ('/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 &a ('/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 &a ('/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 &a ('/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 &a ('/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. 4 rows selected.

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


打开主库,设置快速恢复区大小,路径,为主库添加4组standby日志,方便之后的主库互换

  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. 1 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. 6 rows selected.

  39. Elapsed: 00:00:00.01
  40. 13:26:06 SYS @ slow >edit
  41. Wrote file afiedt.buf

  42.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  43. 13:27:02 SYS @ slow >r
  44.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  45. Enter value for a: 4
  46. Enter value for b: standby04.log
  47. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  48. new 1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/slow/standby04.log') size 200m

  49. Database altered.

  50. Elapsed: 00:00:02.73
  51. 13:27:19 SYS @ slow >r
  52.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  53. Enter value for a: 5
  54. Enter value for b: standby05.log
  55. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  56. new 1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/slow/standby05.log') size 200m

  57. Database altered.

  58. Elapsed: 00:00:03.50
  59. 13:27:44 SYS @ slow >r
  60.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  61. Enter value for a: 6
  62. Enter value for b: standby06.log
  63. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  64. new 1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/slow/standby06.log') size 200m

  65. Database altered.

  66. Elapsed: 00:00:02.04
  67. 13:28:03 SYS @ slow >r
  68.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  69. Enter value for a: 7
  70. Enter value for b: standby07.log
  71. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
  72. new 1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/slow/standby07.log') size 200m

  73. Database altered.

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

  76.     GROUP# THREAD# SEQUENCE# ARC STATUS
  77. ---------- ---------- ---------- --- ----------
  78.      4     0     0 YES UNASSIGNED
  79.      5     0     0 YES UNASSIGNED
  80.      6     0     0 YES UNASSIGNED
  81.      7     0     0 YES UNASSIGNED

  82. 4 rows selected.

  83. Elapsed: 00:00:00.00


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

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

注册时间:2016-10-26

  • 博文量
    32
  • 访问量
    5210