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

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

1. 10:24:42 SYS @ slow >startup
   
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. 10:24:57 SYS @ slow >select name,database_role,protection_mode,open_mode from v$database;
    
12. 
    
13. NAME     DATABASE_ROLE PROTECTION_MODE    OPEN_MODE
    
14. --------- ---------------- -------------------- --------------------
    
15. SLOW     PRIMARY     MAXIMUM PERFORMANCE    READ WRITE
    
16. 
    
17. 1 row selected.
    
18. 
    
19. Elapsed: 00:00:00.02
    
20. 10:28:35 SYS @ slow >show parameter recover;
    
21. 
    
22. NAME                 TYPE     VALUE
    
23. ------------------------------------ ----------- ------------------------------
    
24. db_recovery_file_dest         string
    
25. db_recovery_file_dest_size     big integer 0
    
26. db_unrecoverable_scn_tracking     boolean     TRUE
    
27. recovery_parallelism         integer     0
    
28. 10:37:21 SYS @ slow >alter system set db_recovery_file_dest_size=4g;
    
29. 
    
30. System altered.
    
31. 
    
32. Elapsed: 00:00:00.00
    
33. 10:37:54 SYS @ slow >alter system set db_recovery_file_dest='/dsk1';
    
34. 
    
35. System altered.
    
36. 
    
37. Elapsed: 00:00:00.01
    
38. 10:38:19 SYS @ slow >select member from v$logfile;
    
39. 
    
40. MEMBER
    
41. -----------------------------------------
    
42. /u01/app/oracle/oradata/slow/redo01a.log
    
43. /u01/app/oracle/oradata/slow/redo01b.log
    
44. /u01/app/oracle/oradata/slow/redo02a.log
    
45. /u01/app/oracle/oradata/slow/redo02b.log
    
46. /u01/app/oracle/oradata/slow/redo03a.log
    
47. /u01/app/oracle/oradata/slow/redo03b.log
    
48. 
    
49. 6 rows selected.
    
50. 
    
51. Elapsed: 00:00:00.01
    
52. 13:26:06 SYS @ slow >edit
    
53. Wrote file afiedt.buf
    
54. 
    
55.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
56. 13:27:02 SYS @ slow >r
    
57.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
58. Enter value for a: 4
    
59. Enter value for b: standby04.log
    
60. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
61. new 1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/slow/standby04.log') size 200m
    
62. 
    
63. Database altered.
    
64. 
    
65. Elapsed: 00:00:02.73
    
66. 13:27:19 SYS @ slow >r
    
67.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
68. Enter value for a: 5
    
69. Enter value for b: standby05.log
    
70. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
71. new 1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/slow/standby05.log') size 200m
    
72. 
    
73. Database altered.
    
74. 
    
75. Elapsed: 00:00:03.50
    
76. 13:27:44 SYS @ slow >r
    
77.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
78. Enter value for a: 6
    
79. Enter value for b: standby06.log
    
80. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
81. new 1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/slow/standby06.log') size 200m
    
82. 
    
83. Database altered.
    
84. 
    
85. Elapsed: 00:00:02.04
    
86. 13:28:03 SYS @ slow >r
    
87.   1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
88. Enter value for a: 7
    
89. Enter value for b: standby07.log
    
90. old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
    
91. new 1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/slow/standby07.log') size 200m
    
92. 
    
93. Database altered.
    
94. 
    
95. Elapsed: 00:00:02.33
    
96. 13:28:19 SYS @ slow >select group#,thread#,sequence#,archived,status from v$standby_log;
    
97. 
    
98.     GROUP# THREAD# SEQUENCE# ARC STATUS
    
99. ---------- ---------- ---------- --- ----------
    
100.      4     0     0 YES UNASSIGNED
     
101.      5     0     0 YES UNASSIGNED
     
102.      6     0     0 YES UNASSIGNED
     
103.      7     0     0 YES UNASSIGNED
     
104. 
     
105. 4 rows selected.
     
106. 
     
107. Elapsed: 00:00:00.00

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

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