单实例数据库DataGuard主库与备库切换
数据库环境
物理备库：
主库 SHDB
备库 BJDB
1、查看当前主备库角色、状态信息
主库(SHDB)
10:48:37 SYS@ SHDB>col name for a10 10:54:03 SYS@ SHDB>col database_role for a20 10:54:03 SYS@ SHDB>col protection_mode for a20 10:54:04 SYS@ SHDB>col switchover_status for a20 10:54:04 SYS@ SHDB>select name,protection_mode,database_role,switchover_status from v$database; NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------- -------------------- -------------------- -------------------- BJDB MAXIMUM PERFORMANCE PRIMARY TO STANDBY
备库(BJDB)
01:12:20 SYS@ BJDB>col name for a10 01:16:50 SYS@ BJDB>col database_role for a20 01:16:50 SYS@ BJDB>col protection_mode for a20 01:16:51 SYS@ BJDB>col switchover_status for a20 01:16:51 SYS@ BJDB>select name,protection_mode,database_role,switchover_status from v$database; NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------- -------------------- -------------------- -------------------- BJDB MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
2、主库切换成物理备库
10:54:04 SYS@ SHDB>alter database commit to switchover to standby; Database altered.
注意：如果执行上述SQL命令失败，如下述现象，说明有其他的SESSION(除当前的SYS用户之外)在登陆ORACLE；当然如果直接执行成功，下面的情况就可以不用再执行了.
23:25:58 SYS@ BJDB>alter database commit to switchover to standby; alter database commit to switchover to standby * ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
解决办法:
23:32:38 SYS@ BJDB>alter database commit to switchover to standby with session shutdown; Database altered. 23:35:10 SYS@ BJDB>
2.1、执行完成切换之后，数据库处于关闭状态，需要手工启动到mount状态；
11:03:06 SYS@ SHDB>select open_mode from v$database; select open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted 11:03:25 SYS@ SHDB>shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. 11:04:05 SYS@ SHDB>startup mount; ORACLE instance started. Total System Global Area 849530880 bytes Fixed Size 1339824 bytes Variable Size 499125840 bytes Database Buffers 343932928 bytes Redo Buffers 5132288 bytes Database mounted. 11:04:17 SYS@ SHDB>select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED 11:04:26 SYS@ SHDB>
2.2、查看当前数据库(SHDB)角色为PHYSICAL STANDBY
11:04:17 SYS@ SHDB>select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED 11:04:26 SYS@ SHDB>col name for a10 11:05:45 SYS@ SHDB>col database_role for a20 11:05:45 SYS@ SHDB>col protection_mode for a20 11:05:45 SYS@ SHDB>col switchover_status for a20 11:05:45 SYS@ SHDB>select name,protection_mode,database_role,switchover_status from v$database; NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------- -------------------- -------------------- -------------------- BJDB MAXIMUM PERFORMANCE PHYSICAL STANDBY TO PRIMARY
3、备库(BJDB)切换为主库
01:16:51 SYS@ BJDB>col name for a10 01:30:48 SYS@ BJDB>col database_role for a20 01:30:48 SYS@ BJDB>col protection_mode for a20 01:30:48 SYS@ BJDB>col switchover_status for a20 01:30:48 SYS@ BJDB>select name,protection_mode,database_role,switchover_status from v$database; NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------- -------------------- -------------------- -------------------- BJDB MAXIMUM PERFORMANCE PHYSICAL STANDBY SESSIONS ACTIVE
此时我们注意到一个奇怪的现象，主库(SHDB)与备库(BJDB)此时此刻都处于PHYSICAL STANDBY状态。实际上这是一个中间状态.
3.1、执行备库(BJDB)切换为主库
01:30:48 SYS@ BJDB>alter database commit to switchover to primary; alter database commit to switchover to primary * ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected 01:32:42 SYS@ BJDB>alter database commit to switchover to primary with session shutdown; Database altered.
3.2、查看备库(BJDB)已经处于PRIMARY状态，也就是说备库切换为主库成功了
01:32:42 SYS@ BJDB>alter database commit to switchover to primary with session shutdown; Database altered. 01:33:22 SYS@ BJDB>col name for a10 01:33:55 SYS@ BJDB>col database_role for a20 01:33:55 SYS@ BJDB>col protection_mode for a20 01:33:55 SYS@ BJDB>col switchover_status for a20 01:33:56 SYS@ BJDB>select name,protection_mode,database_role,switchover_status from v$database; NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS ---------- -------------------- -------------------- -------------------- BJDB MAXIMUM PERFORMANCE PRIMARY NOT ALLOWED
3.3、切换完成数据库(SHDB)之后，数据库处于mounted状态，需要手工切换至open
01:35:33 SYS@ BJDB>select status from v$instance; STATUS ------------------------------------ MOUNTED 01:35:41 SYS@ BJDB>alter database open; Database altered. 01:35:50 SYS@ BJDB>select status from v$instance; STATUS ------------------------------------ OPEN
4、监控主库(BJDB)启动进程
01:36:03 SYS@ BJDB>select process,status from v$managed_standby; PROCESS STATUS --------------------------- ------------------------------------ ARCH CLOSING ARCH CLOSING ARCH CLOSING LNS WRITING
5、监控备库(SHDB)启动进程.
11:05:46 SYS@ SHDB>select process,status from v$managed_standby; PROCESS STATUS --------------------------- ------------------------------------ ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED RFS IDLE RFS IDLE RFS IDLE 6 rows selected.
注意：备库上才有RFS进程的
6、至此主库与备库的角色已经成功切换完成.接下来可以在监控主库与备库的告警日志、归档日志信息的同时，主库产生一些新数据，备库接收并应用归档日志.
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 华丽的分割线 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
#############################################################################################################################
试验：验证备库接收应用归档日志
1、主库产生新数据
01:38:36 SYS@ BJDB>conn test/test Connected. 01:49:49 TEST@ BJDB>select count(*) from emp; COUNT(*) ---------- 54784 01:49:56 TEST@ BJDB>insert into emp select * from emp; 54784 rows created. 01:50:24 TEST@ BJDB>commit; Commit complete. 01:50:28 TEST@ BJDB>select count(*) from emp; COUNT(*) ---------- 109568 01:50:33 TEST@ BJDB>
2、主库发生日志切换
01:50:59 TEST@ BJDB>conn / as sysdba Connected. 01:51:04 SYS@ BJDB>alter system switch logfile; System altered.
3、备库应用归档日志(备库库处于mount状态)
11:29:57 SYS@ SHDB>select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED 11:30:48 SYS@ SHDB>select sequence#,name,applied from v$archived_log; SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 119 /dsk4/arch_shdb/arch_1_119_833385608.log YES 120 /dsk4/arch_shdb/arch_1_120_833385608.log YES 121 /dsk4/arch_shdb/arch_1_121_833385608.log YES 122 /dsk4/arch_shdb/arch_1_122_833385608.log YES 123 /dsk4/arch_shdb/arch_1_123_833385608.log YES 123 BJDB YES 124 /dsk4/arch_shdb/arch_1_124_833385608.log YES 124 BJDB YES 125 BJDB YES 125 /dsk4/arch_shdb/arch_1_125_833385608.log YES 126 /dsk4/arch_shdb/arch_1_126_833385608.log YES 126 BJDB YES 127 /dsk4/arch_shdb/arch_1_127_833385608.log YES 127 BJDB YES 128 BJDB YES 128 /dsk4/arch_shdb/arch_1_128_833385608.log YES 129 BJDB YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 129 /dsk4/arch_shdb/arch_1_129_833385608.log YES 130 BJDB YES 130 /dsk4/arch_shdb/arch_1_130_833385608.log YES 131 /dsk4/arch_shdb/arch_1_131_833385608.log YES 131 BJDB YES 132 /dsk4/arch_shdb/arch_1_132_833385608.log YES 132 BJDB YES 133 /dsk4/arch_shdb/arch_1_133_833385608.log YES 134 /dsk4/arch_shdb/arch_1_134_833385608.log YES 135 /dsk4/arch_shdb/arch_1_135_833385608.log YES 136 /dsk4/arch_shdb/arch_1_136_833385608.log YES 137 /dsk4/arch_shdb/arch_1_137_833385608.log YES 138 /dsk4/arch_shdb/arch_1_138_833385608.log YES 139 /dsk4/arch_shdb/arch_1_139_833385608.log YES 140 /dsk4/arch_shdb/arch_1_140_833385608.log YES 141 /dsk4/arch_shdb/arch_1_141_833385608.log YES 142 /dsk4/arch_shdb/arch_1_142_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 143 /dsk4/arch_shdb/arch_1_143_833385608.log YES 144 /dsk4/arch_shdb/arch_1_144_833385608.log YES 145 /dsk4/arch_shdb/arch_1_145_833385608.log YES 146 /dsk4/arch_shdb/arch_1_146_833385608.log YES 148 /dsk4/arch_shdb/arch_1_148_833385608.log YES 147 /dsk4/arch_shdb/arch_1_147_833385608.log YES 166 /dsk4/arch_shdb/arch_1_166_833385608.log YES 149 /dsk4/arch_shdb/arch_1_149_833385608.log YES 150 /dsk4/arch_shdb/arch_1_150_833385608.log YES 151 /dsk4/arch_shdb/arch_1_151_833385608.log YES 152 /dsk4/arch_shdb/arch_1_152_833385608.log YES 154 /dsk4/arch_shdb/arch_1_154_833385608.log YES 153 /dsk4/arch_shdb/arch_1_153_833385608.log YES 155 /dsk4/arch_shdb/arch_1_155_833385608.log YES 156 /dsk4/arch_shdb/arch_1_156_833385608.log YES 157 /dsk4/arch_shdb/arch_1_157_833385608.log YES 158 /dsk4/arch_shdb/arch_1_158_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 159 /dsk4/arch_shdb/arch_1_159_833385608.log YES 160 /dsk4/arch_shdb/arch_1_160_833385608.log YES 161 /dsk4/arch_shdb/arch_1_161_833385608.log YES 162 /dsk4/arch_shdb/arch_1_162_833385608.log YES 163 /dsk4/arch_shdb/arch_1_163_833385608.log YES 165 /dsk4/arch_shdb/arch_1_165_833385608.log YES 164 /dsk4/arch_shdb/arch_1_164_833385608.log YES 167 /dsk4/arch_shdb/arch_1_167_833385608.log YES 168 /dsk4/arch_shdb/arch_1_168_833385608.log YES 169 /dsk4/arch_shdb/arch_1_169_833385608.log YES 170 /dsk4/arch_shdb/arch_1_170_833385608.log YES 171 /dsk4/arch_shdb/arch_1_171_833385608.log YES 172 /dsk4/arch_shdb/arch_1_172_833385608.log YES 173 /dsk4/arch_shdb/arch_1_173_833385608.log YES 174 /dsk4/arch_shdb/arch_1_174_833385608.log YES 175 /dsk4/arch_shdb/arch_1_175_833385608.log YES 176 /dsk4/arch_shdb/arch_1_176_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 177 /dsk4/arch_shdb/arch_1_177_833385608.log YES 178 /dsk4/arch_shdb/arch_1_178_833385608.log YES 179 /dsk4/arch_shdb/arch_1_179_833385608.log YES 180 /dsk4/arch_shdb/arch_1_180_833385608.log NO 180 BJDB YES 181 BJDB YES 181 /dsk4/arch_shdb/arch_1_181_833385608.log NO 182 BJDB YES 182 /dsk4/arch_shdb/arch_1_182_833385608.log NO 183 BJDB YES 183 /dsk4/arch_shdb/arch_1_183_833385608.log NO 184 /dsk4/arch_shdb/arch_1_184_833385608.log NO 184 BJDB NO 185 /dsk4/arch_shdb/arch_1_185_833385608.log YES 185 BJDB NO 186 /dsk4/arch_shdb/arch_1_186_833385608.log NO 187 /dsk4/arch_shdb/arch_1_187_833385608.log NO SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 188 /dsk4/arch_shdb/arch_1_188_833385608.log NO 86 rows selected.
11:32:35 SYS@ SHDB>alter database recover managed standby database disconnect from session; Database altered.
再次查看v$archived_log已经归开始陆续应用
11:34:31 SYS@ SHDB>select sequence#,name,applied from v$archived_log; SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 119 /dsk4/arch_shdb/arch_1_119_833385608.log YES 120 /dsk4/arch_shdb/arch_1_120_833385608.log YES 121 /dsk4/arch_shdb/arch_1_121_833385608.log YES 122 /dsk4/arch_shdb/arch_1_122_833385608.log YES 123 /dsk4/arch_shdb/arch_1_123_833385608.log YES 123 BJDB YES 124 /dsk4/arch_shdb/arch_1_124_833385608.log YES 124 BJDB YES 125 BJDB YES 125 /dsk4/arch_shdb/arch_1_125_833385608.log YES 126 /dsk4/arch_shdb/arch_1_126_833385608.log YES 126 BJDB YES 127 /dsk4/arch_shdb/arch_1_127_833385608.log YES 127 BJDB YES 128 BJDB YES 128 /dsk4/arch_shdb/arch_1_128_833385608.log YES 129 BJDB YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 129 /dsk4/arch_shdb/arch_1_129_833385608.log YES 130 BJDB YES 130 /dsk4/arch_shdb/arch_1_130_833385608.log YES 131 /dsk4/arch_shdb/arch_1_131_833385608.log YES 131 BJDB YES 132 /dsk4/arch_shdb/arch_1_132_833385608.log YES 132 BJDB YES 133 /dsk4/arch_shdb/arch_1_133_833385608.log YES 134 /dsk4/arch_shdb/arch_1_134_833385608.log YES 135 /dsk4/arch_shdb/arch_1_135_833385608.log YES 136 /dsk4/arch_shdb/arch_1_136_833385608.log YES 137 /dsk4/arch_shdb/arch_1_137_833385608.log YES 138 /dsk4/arch_shdb/arch_1_138_833385608.log YES 139 /dsk4/arch_shdb/arch_1_139_833385608.log YES 140 /dsk4/arch_shdb/arch_1_140_833385608.log YES 141 /dsk4/arch_shdb/arch_1_141_833385608.log YES 142 /dsk4/arch_shdb/arch_1_142_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 143 /dsk4/arch_shdb/arch_1_143_833385608.log YES 144 /dsk4/arch_shdb/arch_1_144_833385608.log YES 145 /dsk4/arch_shdb/arch_1_145_833385608.log YES 146 /dsk4/arch_shdb/arch_1_146_833385608.log YES 148 /dsk4/arch_shdb/arch_1_148_833385608.log YES 147 /dsk4/arch_shdb/arch_1_147_833385608.log YES 166 /dsk4/arch_shdb/arch_1_166_833385608.log YES 149 /dsk4/arch_shdb/arch_1_149_833385608.log YES 150 /dsk4/arch_shdb/arch_1_150_833385608.log YES 151 /dsk4/arch_shdb/arch_1_151_833385608.log YES 152 /dsk4/arch_shdb/arch_1_152_833385608.log YES 154 /dsk4/arch_shdb/arch_1_154_833385608.log YES 153 /dsk4/arch_shdb/arch_1_153_833385608.log YES 155 /dsk4/arch_shdb/arch_1_155_833385608.log YES 156 /dsk4/arch_shdb/arch_1_156_833385608.log YES 157 /dsk4/arch_shdb/arch_1_157_833385608.log YES 158 /dsk4/arch_shdb/arch_1_158_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 159 /dsk4/arch_shdb/arch_1_159_833385608.log YES 160 /dsk4/arch_shdb/arch_1_160_833385608.log YES 161 /dsk4/arch_shdb/arch_1_161_833385608.log YES 162 /dsk4/arch_shdb/arch_1_162_833385608.log YES 163 /dsk4/arch_shdb/arch_1_163_833385608.log YES 165 /dsk4/arch_shdb/arch_1_165_833385608.log YES 164 /dsk4/arch_shdb/arch_1_164_833385608.log YES 167 /dsk4/arch_shdb/arch_1_167_833385608.log YES 168 /dsk4/arch_shdb/arch_1_168_833385608.log YES 169 /dsk4/arch_shdb/arch_1_169_833385608.log YES 170 /dsk4/arch_shdb/arch_1_170_833385608.log YES 171 /dsk4/arch_shdb/arch_1_171_833385608.log YES 172 /dsk4/arch_shdb/arch_1_172_833385608.log YES 173 /dsk4/arch_shdb/arch_1_173_833385608.log YES 174 /dsk4/arch_shdb/arch_1_174_833385608.log YES 175 /dsk4/arch_shdb/arch_1_175_833385608.log YES 176 /dsk4/arch_shdb/arch_1_176_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 177 /dsk4/arch_shdb/arch_1_177_833385608.log YES 178 /dsk4/arch_shdb/arch_1_178_833385608.log YES 179 /dsk4/arch_shdb/arch_1_179_833385608.log YES 180 /dsk4/arch_shdb/arch_1_180_833385608.log YES 180 BJDB YES 181 BJDB YES 181 /dsk4/arch_shdb/arch_1_181_833385608.log YES 182 BJDB YES 182 /dsk4/arch_shdb/arch_1_182_833385608.log YES 183 BJDB YES 183 /dsk4/arch_shdb/arch_1_183_833385608.log YES 184 /dsk4/arch_shdb/arch_1_184_833385608.log YES 184 BJDB NO 185 /dsk4/arch_shdb/arch_1_185_833385608.log YES 185 BJDB NO 186 /dsk4/arch_shdb/arch_1_186_833385608.log YES 187 /dsk4/arch_shdb/arch_1_187_833385608.log YES SEQUENCE# NAME APPLIED ---------- ------------------------------------------------------------ --------------------------- 188 /dsk4/arch_shdb/arch_1_188_833385608.log YES 86 rows selected.
4、备库取消应用归档日志
11:34:32 SYS@ SHDB>alter database recover managed standby database cancel; Database altered.
5、备库开启到open(read only)状态
11:35:58 SYS@ SHDB>alter database open; Database altered. 11:36:18 SYS@ SHDB>select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ READ ONLY
6、备库应用归档日志
11:36:27 SYS@ SHDB>alter database recover managed standby database disconnect from session; Database altered.
7、备库查看产生的新数据
11:37:27 SYS@ SHDB>select count(*) from test.emp; COUNT(*) ---------- 109568
8、在主库与备库都处于open状态下，模拟生产数据，备库应用归档日志同时对外提供查询服务
主库truncate掉test.emp表中的10万多条数据，并发生一次切换日志
01:51:11 SYS@ BJDB>conn test/test Connected. 02:01:27 TEST@ BJDB>truncate table emp; Table truncated. 02:01:38 TEST@ BJDB>select count(*) from emp; COUNT(*) ---------- 0 02:01:49 TEST@ BJDB>conn / as sysdba Connected. 02:01:54 SYS@ BJDB>alter system switch logfile; System altered.
备库查看test.emp表中的数据
11:37:57 SYS@ SHDB>select count(*) from test.emp; COUNT(*) ---------- 0
9、成功
