最简单的11g Active DataGuard(ADG)搭建配置过程（项目步骤）
一、环境介绍:
我在db01和db02两台Linux虚拟机上首先分别安装了一套数据库软件，在db01主机上创建了名为woo的数据库；我们这次的实验是要搭建了一套Oracle 11g Active DataGuard；目的是为了实现数据库同步的功能，并且了解Oracle 11g DG的基本功能。
db01:192.168.1.50
db02:192.168.1.51
二、11g ADG部署：
1、pri端和sty端配置静态监听
[python] view plain copy print ?
1. [oracle@sty admin]$ cat listener.ora
2. # listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
3. # Generated by Oracle configuration tools.
4.
5. SID_LIST_LISTENER =
6. (SID_LIST =
7. (SID_DESC =
8. (SID_NAME = PLSExtProc)
9. 11.2.4/dbhome_1)
10. (PROGRAM = extproc)
11. )
12. (SID_DESC =
13. (SID_NAME = Woo )
14. 11.2.4/dbhome_1)
15. )
16. )
17.
18. [oracle@sty admin]$cat tnsname.ora
19. # tnsnames.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
20. # Generated by Oracle configuration tools.
21.
22. STY =
23. (DESCRIPTION =
24. (ADDRESS_LIST =
25. 192.168.1.51)(PORT = 1521))
26. )
27. (CONNECT_DATA =
28. (SERVICE_NAME = woo)
29. )
30. )
31.
32. PRI =
33. (DESCRIPTION =
34. (ADDRESS_LIST =
35. 192.168.1.50)(PORT = 1521))
36. )
37. (CONNECT_DATA =
38. (SERVICE_NAME = woo)
39. )
40. )
2、修改primary端初始化参数文件
[sql] view plain copy print ?
1. startup mount;
2. alter database archivelog;
3. alter database force logging;
4. alter database open;
5. alter system set log_archive_config = 'DG_CONFIG=(pri,sty)' scope=spfile;
6. alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;
7. alter system set log_archive_dest_2 = 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty' scope=spfile;
8. alter system set log_archive_dest_state_1 = ENABLE;
9. alter system set log_archive_dest_state_2 = ENABLE;
10. alter system set fal_server=sty scope=spfile;
11. alter system set fal_client=pri scope=spfile;
12. alter system set standby_file_management=AUTO scope=spfile;
3、在primary端pfile参数文件和密码文件，并且拷贝到standby段相应位置
[python] view plain copy print ?
1. SQL> create pfile from spfile;
2.
3. File created.
4.
5. [oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs
6. oracle@192.168.1.51's password:
7. initwoo.ora 100% 1260 1.2KB/s 00:00
8. orapwwoo 100% 1536 1.5KB/s 00:00
9.
10. [oracle@db01 oracle]$ scp -r admin/ diag/ fast_recovery_area/ oradata/ 192.168.1.51:$ORACLE_BASE
11. oracle@192.168.1.51's password:
12. init.ora.512201522543 100% 1778 1.7KB/s 00:01
13. dp.log 100% 116 0.1KB/s 00:00
14. ........
4、修改standby端的监听文件及初始化参数文件
[sql] view plain copy print ?
1. --修改监听文件
2. [oracle@db02 ~]$ cd $ORACLE_HOME/network/admin
3. [oracle@db02 admin]$ vi listener.ora
4. # listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
5. # Generated by Oracle configuration tools.
6.
7. LISTENER =
8. (DESCRIPTION_LIST =
9. (DESCRIPTION =
10. KEY = EXTPROC1521))
11. (ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521))
12. )
13. )
14.
15. SID_LIST_LISTENER =
16. (SID_LIST =
17. (SID_DESC =
18. (SID_NAME = PLSExtProc)
19. (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
20. (PROGRAM = extproc)
21. )
22.
23. (SID_DESC =
24. (GLOBAL_DBNAME = woo)
25. (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
26. (SID_NAME = woo)
27. )
28.
29. )
30.
31. ADR_BASE_LISTENER = /DBSoft/oracle
32.
33. --启动监听
34. [oracle@db02 dbs]$ lsnrctl start
35.
36. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:29:57
37.
38. Copyright (c) 1991, 2013, Oracle. All rights reserved.
39.
40. Starting /DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...
41.
42. TNSLSNR for Linux: Version 11.2.0.4.0 - Production
43. System parameter file is /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
44. Log messages written to /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
45. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
46. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
47.
48. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
49. STATUS of the LISTENER
50. ------------------------
51. Alias LISTENER
52. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
53. Start Date 17-JUN-2015 21:29:57
54. Uptime 0 days 0 hr. 0 min. 1 sec
55. Trace Level off
56. Security ON: Local OS Authentication
57. SNMP OFF
58. Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
59. Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
60. Listening Endpoints Summary...
61. KEY=EXTPROC1521)))
62. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
63. Services Summary...
64. Service "PLSExtProc" has 1 instance(s).
65. "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
66. Service "woo" has 1 instance(s).
67. "woo", status UNKNOWN, has 1 handler(s) for this service...
68. The command completed successfully
69.
70. --查看监听状态
71. [oracle@db02 dbs]$ lsnrctl status
72.
73. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:30:02
74.
75. Copyright (c) 1991, 2013, Oracle. All rights reserved.
76.
77. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
78. STATUS of the LISTENER
79. ------------------------
80. Alias LISTENER
81. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
82. Start Date 17-JUN-2015 21:29:57
83. Uptime 0 days 0 hr. 0 min. 4 sec
84. Trace Level off
85. Security ON: Local OS Authentication
86. SNMP OFF
87. Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
88. Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
89. Listening Endpoints Summary...
90. KEY=EXTPROC1521)))
91. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
92. Services Summary...
93. Service "PLSExtProc" has 1 instance(s).
94. "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
95. Service "woo" has 1 instance(s).
96. "woo", status UNKNOWN, has 1 handler(s) for this service...
97. The command completed successfully
98. [oracle@db02 dbs]$
99.
100.
101. --修改参数文件
102. [oracle@db02 ~]$ sqlplus / as sysdba
103.
104. SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 17 21:35:54 2015
105.
106. Copyright (c) 1982, 2013, Oracle. All rights reserved.
107.
108. Connected to an idle instance.
109.
110. SQL> startup nomount;
111. ORACLE instance started.
112.
113. Total System Global Area 1188511744 bytes
114. Fixed Size 1364228 bytes
115. Variable Size 754978556 bytes
116. Database Buffers 419430400 bytes
117. Redo Buffers 12738560 bytes
118.
119. SQL> create spfile from pfile='/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo.ora';
120.
121. File created.
122.
123. SQL>
124. SQL> shutdown abort;
125. ORACLE instance shut down.
126. SQL> startup nomount;
127. ORACLE instance started.
128.
129. Total System Global Area 1188511744 bytes
130. Fixed Size 1364228 bytes
131. Variable Size 754978556 bytes
132. Database Buffers 419430400 bytes
133. Redo Buffers 12738560 bytes
134. SQL>
135.
136. alter system set db_unique_name=sty scope=spfile;
137. alter system set log_archive_config='DG_CONFIG=(pri,dg)' scope=spfile;
138. alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' scope=spfile;
139. alter system set log_archive_dest_2 = 'SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=spfile;
140. alter system set fal_server=pri scope=spfile;
141. alter system set fal_client=sty scope=spfile;
142.
143. SQL> shutdown abort;
144. ORACLE instance shut down.
145. SQL> startup nomount
146. ORACLE instance started.
147.
148. Total System Global Area 1188511744 bytes
149. Fixed Size 1364228 bytes
150. Variable Size 754978556 bytes
151. Database Buffers 419430400 bytes
152. Redo Buffers 12738560 bytes
153. SQL>
154. SQL>
5、在primary端通过Rman Duplicate创建备库,在db01上执行如下命令
rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
duplicate target database for standby from active database nofilenamecheck;
[python] view plain copy print ?
1. [oracle@db01 ~]$ rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
2.
3. Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 17 22:33:54 2015
4.
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
6.
7. connected to target database: WOO (DBID=4221729487)
8. using target database control file instead of recovery catalog
9. connected to auxiliary database: WOO (not mounted)
10.
11. RMAN>
12.
13. RMAN> duplicate target database for standby from active database nofilenamecheck;
14.
15. Starting Duplicate Db at 17-JUN-15
16. allocated channel: ORA_AUX_DISK_1
17. channel ORA_AUX_DISK_1: SID=19 device type=DISK
18.
19. contents of Memory Script:
20. {
21. backup as copy reuse
22. '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo' auxiliary format
23. '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo' ;
24. }
25. executing Memory Script
26.
27. Starting backup at 17-JUN-15
28. allocated channel: ORA_DISK_1
29. channel ORA_DISK_1: SID=44 device type=DISK
30. Finished backup at 17-JUN-15
31.
32. contents of Memory Script:
33. {
34. for standby auxiliary format '/DBSoft/oracle/oradata/woo/control01.ctl';
35. '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from
36. '/DBSoft/oracle/oradata/woo/control01.ctl';
37. }
38. executing Memory Script
39.
40. Starting backup at 17-JUN-15
41. using channel ORA_DISK_1
42. channel ORA_DISK_1: starting datafile copy
43. copying standby control file
44. output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150617T223502 RECID=1 STAMP=882657308
45. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
46. Finished backup at 17-JUN-15
47.
48. Starting restore at 17-JUN-15
49. using channel ORA_AUX_DISK_1
50.
51. channel ORA_AUX_DISK_1: copied control file copy
52. Finished restore at 17-JUN-15
53.
54. contents of Memory Script:
55. {
56. 'alter database mount standby database';
57. }
58. executing Memory Script
59.
60. sql statement: alter database mount standby database
61.
62. contents of Memory Script:
63. {
64. for tempfile 1 to
65. "/DBSoft/oracle/oradata/woo/temp01.dbf";
66. switch clone tempfile all;
67. for datafile 1 to
68. "/DBSoft/oracle/oradata/woo/system01.dbf";
69. for datafile 2 to
70. "/DBSoft/oracle/oradata/woo/sysaux01.dbf";
71. for datafile 3 to
72. "/DBSoft/oracle/oradata/woo/undotbs01.dbf";
73. for datafile 4 to
74. "/DBSoft/oracle/oradata/woo/users01.dbf";
75. backup as copy reuse
76. 1 auxiliary format
77. "/DBSoft/oracle/oradata/woo/system01.dbf" datafile
78. 2 auxiliary format
79. "/DBSoft/oracle/oradata/woo/sysaux01.dbf" datafile
80. 3 auxiliary format
81. "/DBSoft/oracle/oradata/woo/undotbs01.dbf" datafile
82. 4 auxiliary format
83. "/DBSoft/oracle/oradata/woo/users01.dbf" ;
84. 'alter system archive log current';
85. }
86. executing Memory Script
87.
88. executing command: SET NEWNAME
89.
90. renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file
91.
92. executing command: SET NEWNAME
93.
94. executing command: SET NEWNAME
95.
96. executing command: SET NEWNAME
97.
98. executing command: SET NEWNAME
99.
100. Starting backup at 17-JUN-15
101. using channel ORA_DISK_1
102. channel ORA_DISK_1: starting datafile copy
103. input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
104. output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150617T223532
105. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:58
106. channel ORA_DISK_1: starting datafile copy
107. input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
108. output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150617T223532
109. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:30
110. channel ORA_DISK_1: starting datafile copy
111. input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
112. output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150617T223532
113. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
114. channel ORA_DISK_1: starting datafile copy
115. input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
116. output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150617T223532
117. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
118. Finished backup at 17-JUN-15
119.
120. sql statement: alter system archive log current
121.
122. contents of Memory Script:
123. {
124. switch clone datafile all;
125. }
126. executing Memory Script
127.
128. datafile 1 switched to datafile copy
129. input datafile copy RECID=1 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/system01.dbf
130. datafile 2 switched to datafile copy
131. input datafile copy RECID=2 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
132. datafile 3 switched to datafile copy
133. input datafile copy RECID=3 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
134. datafile 4 switched to datafile copy
135. input datafile copy RECID=4 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/users01.dbf
136. Finished Duplicate Db at 17-JUN-15#至此已经恢复完成
6、在primary 和standby端添加standby日志
[sql] view plain copy print ?
1. SQL> alter database add standby logfile
2. group 4 ('/DBSoft/oracle/oradata/woo/styredo04.log') size 50m,
3. group 5 ('/DBSoft/oracle/oradata/woo/styredo05.log') size 50m,
4. group 6 ('/DBSoft/oracle/oradata/woo/styredo06.log') size 50m,
5. group 7 ('/DBSoft/oracle/oradata/woo/styredo07.log') size 50m;
6.
7. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
8.
9. GROUP# THREAD# SEQUENCE# ARC STATUS
10. ---------- ---------- ---------- --- ----------
11. 4 0 0 YES UNASSIGNED
12. 5 0 0 YES UNASSIGNED
13. 6 0 0 YES UNASSIGNED
14. 7 0 0 YES UNASSIGNED
7、在standby端开启实时日志应用
[sql] view plain copy print ?
1. SQL> recover managed standby database using current logfile disconnect from session;
2. Media recovery complete.
3. SQL>
三、开始测试ADG
8、执行日志切换测试（在pri端切换归档，在节点二上检查是否也发生了切换）
[sql] view plain copy print ?
1. --primary执行日志切换
2.
3. SQL> archive log list;
4. Database log mode Archive Mode
5. Automatic archival Enabled
6. Archive destination /DBBackup/Archive
7. Oldest online log sequence 21
8. Next log sequence to archive 23
9. Current log sequence 23
10. SQL> alter system switch logfile;
11.
12. System altered.
13.
14. SQL> archive log list;
15. Database log mode Archive Mode
16. Automatic archival Enabled
17. Archive destination /DBBackup/Archive
18. Oldest online log sequence 22
19. Next log sequence to archive 23
20. Current log sequence 24
21.
22. #standby查看日志的sequence号也跟着变了
23. SQL> archive log list;
24. Database log mode Archive Mode
25. Automatic archival Enabled
26. Archive destination /DBBackup/Archive
27. Oldest online log sequence 22
28. Next log sequence to archive 0
29. Current log sequence 23
30. SQL> archive log list;
31. Database log mode Archive Mode
32. Automatic archival Enabled
33. Archive destination /DBBackup/Archive
34. Oldest online log sequence 22
35. Next log sequence to archive 0
36. Current log sequence 24
37. SQL>
9、查看standby启动的DG进程
[sql] view plain copy print ?
1. SQL> select process,client_process,sequence#,status from v$managed_standby;
2.
3. PROCESS CLIENT_P SEQUENCE# STATUS
4. --------- -------- ---------- ------------
5. ARCH ARCH 23 CLOSING
6. ARCH ARCH 0 CONNECTED //归档进程
7. ARCH ARCH 21 CLOSING
8. ARCH ARCH 0 CONNECTED
9. RFS ARCH 0 IDLE
10. RFS UNKNOWN 0 IDLE
11. RFS LGWR 24 IDLE //归档传输进程
12. RFS UNKNOWN 0 IDLE
13. MRP0 N/A 24 APPLYING_LOG //日志应用进程
14.
15. 9 rows selected.
10、查看数据库的保护模式：
[sql] view plain copy print ?
1. #primary 端查看，我们可以看到数据库的保护模式为最大性能
2. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
3.
4. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
5. ---------------- -------------------- -------------------- --------------------
6. PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
7.
8. #standby 端查看，也是一样的。
9. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
10.
11. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
12. ---------------- -------------------- -------------------- --------------------
13. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED
11、查看DG的日志信息
[sql] view plain copy print ?
1. SQL> select * from v$dataguard_status;
2.
3. FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
4. ------------------------ ------------- ---------- ----------- ---------- --- --------- --------------------------------------------------------------------------------
5. Log Transport Services Informational 0 1 0 NO 17-JUN-15 ARC0: Archival started
6. Log Transport Services Informational 0 2 0 NO 17-JUN-15 ARC1: Archival started
7. Log Transport Services Informational 0 3 0 NO 17-JUN-15 ARC2: Archival started
8. Log Transport Services Informational 0 4 0 NO 17-JUN-15 ARC2: Becoming the 'no FAL' ARCH
9. Log Transport Services Informational 0 5 0 NO 17-JUN-15 ARC2: Becoming the 'no SRL' ARCH
10. Log Transport Services Informational 0 6 0 NO 17-JUN-15 ARC1: Becoming the heartbeat ARCH
11. Log Transport Services Control 0 7 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 15 (882580-883734)
12. Log Transport Services Informational 0 8 0 NO 17-JUN-15 ARC3: Archival started
13. Log Transport Services Control 0 9 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 15 (882580-883734)
14. Log Transport Services Control 0 10 0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 17
15. Log Transport Services Control 0 11 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 16 (883734-883788)
16. Log Transport Services Control 0 12 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 16 (883734-883788)
17. Log Transport Services Warning 2 13 3113 NO 17-JUN-15 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
18. Log Transport Services Warning 2 14 3113 NO 17-JUN-15 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
19. Log Transport Services Error 2 15 3113 YES 17-JUN-15 Error 3113 for archive log file 2 to 'sty'
20. Log Transport Services Error 2 16 1041 YES 17-JUN-15 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'sty'
21.
22. Log Transport Services Warning 2 17 3113 NO 17-JUN-15 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
23. Log Transport Services Error 0 18 1034 YES 17-JUN-15 Error 1034 received logging on to the standby
24. Log Transport Services Error 2 19 1034 YES 17-JUN-15 ARC1: Error 1034 attaching to RFS for reconnect
25. Log Transport Services Error 2 20 3113 YES 17-JUN-15 PING[ARC1]: Error 3113 when pinging standby sty.
26. Log Transport Services Error 2 21 16058 YES 17-JUN-15 PING[ARC1]: Heartbeat failed to connect to standby 'sty'. Error is 16058.
27. Log Transport Services Warning 0 22 3113 NO 17-JUN-15 LGWR: Failed to archive log 2 thread 1 sequence 17 (3113)
28. Log Transport Services Control 0 23 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 17 (883788-884545)
29. Log Transport Services Control 0 24 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 17 (883788-884545)
30. Log Transport Services Control 0 25 0 YES 17-JUN-15 ARCH: Beginning to archive thread 1 sequence 18 (884545-884856)
31. Log Transport Services Control 0 26 0 YES 17-JUN-15 ARCH: Completed archiving thread 1 sequence 18 (884545-884856)
32. Log Transport Services Control 0 27 0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 20
33. Log Transport Services Control 0 28 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 19 (884856-884904)
34. Log Transport Services Control 0 29 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 19 (884856-884904)
35. Log Transport Services Informational 2 30 0 NO 17-JUN-15 ARC0: Archive log rejected (thread 1 sequence 19) at host 'sty'
36. Log Transport Services Informational 0 31 0 NO 17-JUN-15 ARC4: Archival started
37. Log Transport Services Informational 0 32 0 NO 17-JUN-15 ARCH shutting down
38. Log Transport Services Informational 0 33 0 NO 17-JUN-15 ARC4: Archival stopped
39. Log Transport Services Control 0 34 0 YES 17-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 20
40. Log Transport Services Warning 1 35 0 NO 17-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 21 for destination LOG _ARCHIVE_DEST_2
41. Log Transport Services Control 0 36 0 YES 17-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 21
42. Log Transport Services Control 0 37 0 YES 17-JUN-15 ARC0: Beginning to archive thread 1 sequence 20 (884904-885855)
43. Log Transport Services Control 0 38 0 YES 17-JUN-15 ARC0: Completed archiving thread 1 sequence 20 (884904-885855)
44. Log Transport Services Control 0 39 0 YES 17-JUN-15 LGWR: Completed archiving log 3 thread 1 sequence 21
45. Log Transport Services Warning 1 40 0 NO 17-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 22 for destination LOG _ARCHIVE_DEST_2
46. Log Transport Services Control 0 41 0 YES 17-JUN-15 LGWR: Beginning to archive log 1 thread 1 sequence 22
47. Log Transport Services Control 0 42 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 21 (885855-886131)
48. Log Transport Services Control 0 43 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 21 (885855-886131)
49. Log Transport Services Control 0 44 0 YES 18-JUN-15 LGWR: Completed archiving log 1 thread 1 sequence 22
50. Log Transport Services Warning 1 45 0 NO 18-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 23 for destination LOG _ARCHIVE_DEST_2
51. Log Transport Services Control 0 46 0 YES 18-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 23
52. Log Transport Services Control 0 47 0 YES 18-JUN-15 ARC3: Beginning to archive thread 1 sequence 22 (886131-927736)
53. Log Transport Services Control 0 48 0 YES 18-JUN-15 ARC3: Completed archiving thread 1 sequence 22 (886131-927736)
54. Log Transport Services Control 0 49 0 YES 18-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 23
55. Log Transport Services Warning 1 50 0 NO 18-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 24 for destination LOG _ARCHIVE_DEST_2
56. Log Transport Services Control 0 51 0 YES 18-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 24
57. Log Transport Services Control 0 52 0 YES 18-JUN-15 ARC0: Beginning to archive thread 1 sequence 23 (927736-961263)
58. Log Transport Services Control 0 53 0 YES 18-JUN-15 ARC0: Completed archiving thread 1 sequence 23 (927736-961263)
59.
60. 53 rows selected.
12、Open Read Only standby数据库并且开启实时日志应用
[sql] view plain copy print ?
1. SQL> shutdown immediate
2. ORA-01109: database not open
3.
4.
5. Database dismounted.
6. ORACLE instance shut down.
7. SQL> startup
8. ORACLE instance started.
9.
10. Total System Global Area 1188511744 bytes
11. Fixed Size 1364228 bytes
12. Variable Size 754978556 bytes
13. Database Buffers 419430400 bytes
14. Redo Buffers 12738560 bytes
15. Database mounted.
16. Database opened.
17. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
18.
19. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
20. ---------------- -------------------- -------------------- --------------------
21. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY
22.
23. SQL> select process,client_process,sequence#,status from v$managed_standby;
24.
25. PROCESS CLIENT_P SEQUENCE# STATUS
26. --------- -------- ---------- ------------
27. ARCH ARCH 0 CONNECTED
28. ARCH ARCH 0 CONNECTED
29. ARCH ARCH 0 CONNECTED
30. ARCH ARCH 26 CLOSING
31. RFS ARCH 0 IDLE
32. RFS UNKNOWN 0 IDLE
33. RFS LGWR 27 IDLE
34.
35. 7 rows selected.
36.
37. SQL> recover managed standby database using current logfile disconnect from session;
38. Media recovery complete.
39.
40. SQL> select process,client_process,sequence#,status from v$managed_standby;
41.
42. PROCESS CLIENT_P SEQUENCE# STATUS
43. --------- -------- ---------- ------------
44. ARCH ARCH 0 CONNECTED
45. ARCH ARCH 0 CONNECTED
46. ARCH ARCH 0 CONNECTED
47. ARCH ARCH 26 CLOSING
48. RFS ARCH 0 IDLE
49. RFS UNKNOWN 0 IDLE
50. RFS LGWR 27 IDLE
51. MRP0 N/A 27 APPLYING_LOG
52.
53. 8 rows selected.
13、解锁scott用户，添加数据，验证数据是否能同步：
[sql] view plain copy print ?
1. #在primary端创建解锁scott用户并创建测试表，插入10000行数据
2. #primary 端操作如下内容
3. SQL> set line 200
4. SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';
5.
6. USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS
7. ------------------------------ ------------------------------ --------------------------------
8. SCOTT USERS EXPIRED & LOCKED
9.
10. SQL> alter user scott account unlock;
11.
12. User altered.
13.
14. SQL> conn scott/tiger;
15. ERROR:
16. ORA-28001: the password has expired
17.
18.
19. Changing password for scott
20. New password:
21. Retype new password:
22. Password changed
23. Connected.
24.
25. SQL> show user
26. USER is "SCOTT"
27. SQL> select * from tab;
28.
29. TNAME TABTYPE CLUSTERID
30. ------------------------------ ------- ----------
31. BONUS TABLE
32. DEPT TABLE
33. EMP TABLE
34. SALGRADE TABLE
35.
36. SQL>
37.
38. SQL> create table test001 (id number(10),name varchar2(20));
39.
40. Table created.
41.
42. SQL> begin
43. for i in 1..10000 loop
44. insert into test001 values (1,'ww');
45. end loop;
46. end;
47. 6 /
48.
49. PL/SQL procedure successfully completed.
50.
51. SQL> commit;
52.
53. Commit complete.
54.
55. #standby端查询scott用户是否解锁，以及test001表是否创建并且插入了10000行数据：
56. SQL> conn scott/tiger;
57. Connected.
58.
59. SQL> select * from tab;
60.
61. TNAME TABTYPE CLUSTERID
62. ------------------------------ ------- ----------
63. BONUS TABLE
64. DEPT TABLE
65. EMP TABLE
66. SALGRADE TABLE
67. TEST001 TABLE
68.
69. SQL> select count(*) from test001;
70.
71. COUNT(*)
72. ----------
73. 10000
74.
75. SQL>#至此Oracle 11g ADG就已经配置完成了
四、ADG三种模式切换及介绍
14、#ADG有三种(PROTECTION|AVAILABILITY|PERFORMANCE)模式，具体参考:探索Oracle11gR2 之 DataGuard_03 三种保护模式
[sql] view plain copy print ?
1. --primary操作步骤也就是命令之差：
2. SQL> select database_role,protection_mode,protection_level from v$database; ----当前为最大性能
3.
4. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
5. ---------------- -------------------- --------------------
6. PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
7.
8. SQL>
9. SQL> alter database set standby database to maximize availability; ----切换为最大可用
10.
11. Database altered.
12.
13. SQL> select database_role,protection_mode,protection_level from v$database;
14.
15. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
16. ---------------- -------------------- --------------------
17. PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
18.
19. SQL> alter database set standby database to maximize protection; ----切换为最大保护
20.
21. Database altered.
22.
23. SQL> select database_role,protection_mode,protection_level from v$database;
24.
25. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
26. ---------------- -------------------- --------------------
27. PRIMARY MAXIMUM PROTECTION MAXIMUM PROTECTION
28.
29. SQL>
30.
31.
32. --#standby端切换到最大保护是需要重启数据库到mount模式的:
33. SQL> select database_role,protection_mode,protection_level from v$database; ----当前为最大性能
34.
35. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
36. ---------------- -------------------- --------------------
37. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
38.
39. SQL> alter database set standby database to maximize availability; ----切换为最大可用
40.
41. Database altered.
42.
43. SQL> select database_role,protection_mode,protection_level from v$database;
44.
45. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
46. ---------------- -------------------- --------------------
47. PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
48.
49. SQL> alter database set standby database to maximize protection; ----切换为最大保护模式报错，需要将standby端启动到mount状态切换.
50. alter database set standby database to maximize protection
51. *
52. ERROR at line 1:
53. ORA-01126: database must be mounted in this instance and not open in any instance
54.
55.
56. SQL> shutdown immediate ----将数据库启动到mount状态
57. Database closed.
58. Database dismounted.
59. ORACLE instance shut down.
60.
61. SQL> startup mount;
62. ORACLE instance started.
63.
64. Total System Global Area 1188511744 bytes
65. Fixed Size 1364228 bytes
66. Variable Size 754978556 bytes
67. Database Buffers 419430400 bytes
68. Redo Buffers 12738560 bytes
69. Database mounted.
70.
71. SQL> alter database set standby database to maximize protection; ----再次切换为最大可用，成功。
72.
73. Database altered.
74.
75. SQL> recover managed standby database using current logfile disconnect from session;
76. Media recovery complete.
77.
78. SQL> select database_role,protection_mode,protection_level from v$database;
79.
80. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
81. ---------------- -------------------- --------------------
82. PHYSICAL STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION
五、切换测试
15、ADG做(switchover)切换测试
[sql] view plain copy print ?
1. --primary 做如下操作
2.
3. SQL> alter database commit to switchover to physical standby;
4.
5. Database altered.
6.
7. SQL> shutdown immediate
8. ORA-01012: not logged on
9. SQL> conn / as sysdba
10. Connected to an idle instance.
11.
12. SQL> startup
13. ORACLE instance started.
14.
15. Total System Global Area 1188511744 bytes
16. Fixed Size 1364228 bytes
17. Variable Size 754978556 bytes
18. Database Buffers 419430400 bytes
19. Redo Buffers 12738560 bytes
20. Database mounted.
21. Database opened.
22.
23. SQL> alter database recover managed standby database disconnect from session;
24.
25. Database altered.
26.
27. SQL> select database_role,switchover_status from v$database;
28.
29. DATABASE_ROLE SWITCHOVER_STATUS
30. ---------------- --------------------
31. PHYSICAL STANDBY TO PRIMARY
32.
33. SQL>
34.
35. --standby 端做如下操作
36.
37. SQL> alter database commit to switchover to primary;
38.
39. Database altered.
40.
41. SQL> shutdown immediate
42. ORA-01109: database not open
43.
44.
45. Database dismounted.
46. ORACLE instance shut down.
47.
48. SQL> startup
49. ORACLE instance started.
50.
51. Total System Global Area 1188511744 bytes
52. Fixed Size 1364228 bytes
53. Variable Size 754978556 bytes
54. Database Buffers 419430400 bytes
55. Redo Buffers 12738560 bytes
56. Database mounted.
57. Database opened.
58.
59. SQL> alter system switch logfile;
60.
61. System altered.
62.
63. SQL> select database_role,switchover_status from v$database;
64.
65. DATABASE_ROLE SWITCHOVER_STATUS
66. ---------------- --------------------
67. PRIMARY SESSIONS ACTIVE
68.
69. SQL>
16、ADG做(fail over)切换测试
[sql] view plain copy print ?
1. --standby 端检查状态
2. SQL> select open_mode from v$database;
3.
4. OPEN_MODE
5. --------------------
6. READ ONLY WITH APPLY
7.
8. --我们通过shutdown abort方式人工模拟primary奔溃，直接关闭:
9.
10. SQL> select open_mode from v$database;
11.
12. OPEN_MODE
13. --------------------
14. READ WRITE
15.
16. SQL> shutdown abort
17. ORACLE instance shut down.
18. SQL>
19.
20. --在standby端执行如下操作
21.
22. SQL> startup mount;
23. ORACLE instance started.
24.
25. Total System Global Area 1188511744 bytes
26. Fixed Size 1364228 bytes
27. Variable Size 754978556 bytes
28. Database Buffers 419430400 bytes
29. Redo Buffers 12738560 bytes
30. Database mounted.
31.
32. SQL> alter system flush redo to 'pri';
33.
34. System altered.
35.
36. SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
37.
38. no rows selected
39.
40. --如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在standby端，要进行关闭apply和结束应用动作。
41.
42. SQL> alter database recover managed standby database cancel;
43. Database altered.
44.
45.
46. SQL> alter database recover managed standby database finish;
47. Database altered
48.
49.
50. SQL> select open_mode, switchover_status from v$database;
51. OPEN_MODE SWITCHOVER_STATUS
52. -------------------- --------------------
53. READ ONLY TO PRIMARY本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
