189 8069 5689
关于Oracle DataGuard Broker的体系结构和详细说明，请参阅另外的文章,本小节是对一个已经完全配置好的DG环境中使用 Data Guard 命令行界面 (DGMGRL)控制和监视 Data Guard 配置。通过 DGMGRL 在配置中执行管理和监视数据库所要求的大部分活动。本文所展示的是通过DGMGRL来实现DG环境中各种保护模式之间的切换和主备之间的SwitchOver
网站建设哪家好，找创新互联建站！专注于网页设计、网站建设、微信开发、微信小程序定制开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了广河免费建站欢迎大家使用！
为了能够比较清楚的看清楚后面的说明，我这里先声明一些东西：
| 数据库 | DB_NAME | DB_UNIQUE_NAME | INSTANCE_NAME |
| 主数据库 | DG_DEMO | Primary | Primary |
| 物理备用数据库 | DG_DEMO | Standby | Standby |
以下测试是在Windows XP + Oracle 10.2.0.4单机环境中完成的！
1、提前条件
你已经创建好了一个没有问题的DG环境（这里可以参见我为了本次实验而专门搭建的DG环境说明和详细的配置步骤一文）
2、创建Broker配置
这里面涉及到一些相关的参数和网络配置，这部分比较简单，也不说了，下面从create configuration开始
SQL> show parameter dg NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string D:ORACLE10GDATABASEDR1PRIMARY.DAT dg_broker_config_file2 string D:ORACLE10GDATABASEDR2PRIMARY.DAT dg_broker_start boolean TRUE SQL> show parameter local_listener; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_DGDEMO SQL>
先熟悉一下DGMGLR的命令
DGMGRL> help 可使用以下命令: add 在中介配置中添加备用数据库 connect 连接 Oracle 实例 create 创建中介配置 disable 禁用配置, 数据库或快速启动故障转移 edit 编辑配置, 数据库或实例 enable 启用配置, 数据库或快速启动故障转移 exit 退出程序 failover 将备用数据库更改为主数据库 help 显示命令的说明和语法 quit 退出程序 reinstate 将已禁用的数据库更改为可行的备用数据库 rem DGMGRL 会忽略注释 remove 删除配置, 数据库或实例 show 显示有关配置, 数据库或实例的信息 shutdown 关闭当前正在运行的 Oracle 实例 start 启动快速启动故障转移观察器 startup 启动 Oracle 数据库实例 stop 停止快速启动故障转移观察器 switchover 在主数据库和备用数据库之间切换角色 使用 "help " 可以查看各个命令的语法 DGMGRL>
1）通过DGMGRL创建一个配置
D:oracle10g>dgmgrl DGMGRL for 32-bit Windows: Version 10.2.0.4.0 - Production Copyright (c) 2000, 2005, Oracle. All rights reserved. 欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。 DGMGRL> connect sys/admin@primary 已连接。 DGMGRL> create configuration dgcfg1 as --dgcfg1是自己定义的一个标识符 > primary database is primary --primary是主库，tns连接串也是primary > connect identifier is primary; 已创建配置 "dgcfg1", 其中主数据库为 "primary"
DGMGRL> add database standby as --将备节点加进来 > connect identifier is standby --备节点的intance_name和tns连接串均为standbhy > maintained as physical; 已添加数据库 "standby"
DGMGRL> show configuration --显示配置信息
Configuration
Name: dgcfg1
Enabled: NO --还没有enable，在后面第三步里实现enable的动作
Protection Mode: MaxPerformance --最开始我的保护模式是最大性能模式
Fast-Start Failover: DISABLED
Databases:
primary - Primary database
standby - Physical standby database
"dgcfg1" 的当前状态: DISABLED
2）查看上面的配置信息,如果和你期望的不一样，可以做适当的编辑
DGMGRL> show database verbose primary
Database
Name: primary
Role: PRIMARY
Enabled: NO
Intended State: OFFLINE
Instance(s): primary
Properties:
InitialConnectIdentifier = 'primary'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'd:oracle10goradatastandby, d:oracle10goradataprimary'
LogFileNameConvert = 'd:oracle10goradatastandby, d:oracle10goradataprimary'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'zhangrp-cn'
SidName = 'primary'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)(PORT=1527))'
StandbyArchiveLocation = 'dgsby_primary'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'dg_demo_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
"primary" 的当前状态:DISABLED
比如我上面这个色的地方，我想把他明确改到如下的路径中：
DGMGRL> edit database primary set property StandbyArchiveLocation ='d:oracle10goradataprimaryarch_std';
已更新属性 "standbyarchivelocation"
DGMGRL> show database verbose primary
Database
Name: primary
Role: PRIMARY
Enabled: NO
Intended State: OFFLINE
Instance(s): primary
Properties:
InitialConnectIdentifier = 'primary'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'd:oracle10goradatastandby, d:oracle10goradataprimary'
LogFileNameConvert = 'd:oracle10goradatastandby, d:oracle10goradataprimary'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'zhangrp-cn'
SidName = 'primary'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)(PORT=1527))'
StandbyArchiveLocation = 'd:oracle10goradataprimaryarch_std' --改过来了
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'dg_demo_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
"primary" 的当前状态:DISABLED再看一下备库上是不是有需要调整的地方：
DGMGRL> show database verbose standby
Database
Name: standby
Role: PHYSICAL STANDBY Enabled: NO
Intended State: OFFLINE
Instance(s): standby
Properties:
InitialConnectIdentifier = 'standby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '300'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'd:oracle10goradataprimary, d:oracle10goradatastandby'
LogFileNameConvert = 'd:oracle10goradataprimary, d:oracle10goradatastandby'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'zhangrp-cn'
SidName = 'standby'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)(PORT=1527))'
StandbyArchiveLocation = 'd:oracle10goradatastandbyarch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'dg_demo_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
"standby" 的当前状态: DISABLED
好，备库不需要做任何调整，那么继续往下走。
3、启用Broker配置
1）enable Broker的配置和相关的数据库
DGMGRL> enable configuration;
已启用。
DGMGRL> show configuration;
Configuration
Name: dgcfg1
Enabled: YES --enable成功
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
primary - Primary database
standby - Physical standby database
"dgcfg1" 的当前状态:SUCCESS
DGMGRL> show database verbose primary;
Database
Name: primary
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s): primary
Properties:
InitialConnectIdentifier = 'primary'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'd:oracle10goradatastandby, d:oracle10goradataprimary'
LogFileNameConvert = 'd:oracle10goradatastandby, d:oracle10goradataprimary'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'zhangrp-cn'
SidName = 'primary'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)(PORT=1527))'
StandbyArchiveLocation = 'd:oracle10goradataprimaryarch_std'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'dg_demo_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
"primary" 的当前状态:SUCCESS
DGMGRL> show database verbose standby;
Database
Name: standby
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s): standby
Properties:
InitialConnectIdentifier = 'standby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '300'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'd:oracle10goradataprimary, d:oracle10goradatastandby'
LogFileNameConvert = 'd:oracle10goradataprimary, d:oracle10goradatastandby'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'zhangrp-cn'
SidName = 'standby'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)(PORT=1527))'
StandbyArchiveLocation = 'd:oracle10goradatastandbyarch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'dg_demo_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
"standby" 的当前状态:SUCCESS
2）看一下enable的过程中系统做了些什么
大家在配置这个东西的过程中，一定要实时的观察alert日志的信息，不要让自己走了弯路，如果遇到问题提前修复！
主库(instance_name=primary)上的日志信息：
Sun Mar 08 16:42:56 2009
ARCH shutting down
ARC2: Archival stopped
NSV1 started with pid=26, OS id=412
RSM0 started with pid=28, OS id=5752
Sun Mar 08 16:45:51 2009
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=zhangrp-cn)(PORT=1527)))(CONNECT_DATA=(SERVICE_NAME=standby_XPT)(INSTANCE_NAME=STANDBY)
(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
reopen=300 db_unique_name="standby" register net_timeout=300 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Sun Mar 08 16:45:51 2009
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Sun Mar 08 16:45:51 2009
ALTER SYSTEM SET standby_archive_dest='' SCOPE=BOTH SID='primary';
Sun Mar 08 16:45:51 2009
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='primary';
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET log_archive_format='dg_demo_%t_%s_%r.arc' SCOPE=SPFILE SID='primary';
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET db_file_name_convert='d:oracle10goradatastandby','d:oracle10goradataprimary' SCOPE=SPFILE;
Sun Mar 08 16:45:52 2009
ALTER SYSTEM SET log_file_name_convert='d:oracle10goradatastandby','d:oracle10goradataprimary' SCOPE=SPFILE;
Sun Mar 08 16:45:52 2009
ALTER SYSTEM ARCHIVE LOG
Sun Mar 08 16:45:53 2009
Thread 1 advanced to log sequence 141 (LGWR switch)
Current log# 3 seq# 141 mem# 0: D:ORACLE10GORADATAPRIMARYREDO03.LOG
Sun Mar 08 16:45:55 2009
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 141 for destination LOG_ARCHIVE_DEST_2
Sun Mar 08 16:46:06 2009
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=zhangrp-cn)(PORT=1527)))(CONNECT_DATA=(SERVICE_NAME=standby_XPT)(INSTANCE_NAME=STANDBY)
(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
reopen=300 db_unique_name="standby" register net_timeout=300 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Sun Mar 08 16:46:06 2009
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Sun Mar 08 16:46:06 2009
ALTER SYSTEM ARCHIVE LOG
Sun Mar 08 16:46:07 2009
Thread 1 advanced to log sequence 142 (LGWR switch)
Current log# 1 seq# 142 mem# 0: D:ORACLE10GORADATAPRIMARYREDO01.LOG
Sun Mar 08 16:46:08 2009
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 142 for destination LOG_ARCHIVE_DEST_2
Sun Mar 08 16:46:56 2009
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=zhangrp-cn)(PORT=1527)))(CONNECT_DATA=(SERVICE_NAME=standby_XPT)(INSTANCE_NAME=STANDBY)
(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1
reopen=300 db_unique_name="standby" register net_timeout=300 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
Sun Mar 08 16:46:56 2009
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Sun Mar 08 16:46:56 2009
ALTER SYSTEM ARCHIVE LOG
Sun Mar 08 16:46:56 2009
Thread 1 cannot allocate new log, sequence 143
Checkpoint not complete
Current log# 1 seq# 142 mem# 0: D:ORACLE10GORADATAPRIMARYREDO01.LOG
Sun Mar 08 16:46:58 2009
Thread 1 advanced to log sequence 143 (LGWR switch)
Current log# 2 seq# 143 mem# 0: D:ORACLE10GORADATAPRIMARYREDO02.LOG
Sun Mar 08 16:46:59 2009
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 143 for destination LOG_ARCHIVE_DEST_2
备库(instance_name=standby)上的日志信息：
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 4972
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: 'D:ORACLE10GORADATASTANDBYSTDREDO01.LOG'
Sun Mar 08 16:46:09 2009
Media Recovery Waiting for thread 1 sequence 142 (in transit)
Sun Mar 08 16:46:10 2009
Recovery of Online Redo Log: Thread 1 Group 11 Seq 142 Reading mem 0
Mem# 0: D:ORACLE10GORADATASTANDBYSTDREDO01.LOG
Sun Mar 08 16:46:25 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 1464
RFS[6]: Identified database type as 'physical standby'
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_archive_dest_1='location="d:oracle10goradatastandbyarch"',
'valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='standby';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='standby';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET standby_archive_dest='d:oracle10goradatastandbyarch' SCOPE=BOTH SID='standby';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='standby';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_archive_format='dg_demo_%t_%s_%r.arc' SCOPE=SPFILE SID='standby';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET db_file_name_convert='d:oracle10goradataprimary','d:oracle10goradatastandby' SCOPE=SPFILE;
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET log_file_name_convert='d:oracle10goradataprimary','d:oracle10goradatastandby' SCOPE=SPFILE;
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)
(PORT=1527)))(CONNECT_DATA=(SERVICE_NAME=primary_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Sun Mar 08 16:46:46 2009
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zhangrp-cn)
(PORT=1527)))(CONNECT_DATA=(SERVICE_NAME=standby_XPT)(INSTANCE_NAME=STANDBY)(SERVER=dedicated)))' SCOPE=BOTH;
Sun Mar 08 16:46:46 2009
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sun Mar 08 16:46:47 2009
MRP0: Background Media Recovery cancelled with status 16037
Sun Mar 08 16:46:47 2009
Errors in file d:adminstandbybdumpstandby_mrp0_4788.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 691632
Sun Mar 08 16:46:48 2009
Errors in file d:adminstandbybdumpstandby_mrp0_4788.trc:
ORA-16037: user requested cancel of managed recovery operation
Sun Mar 08 16:46:48 2009
Waiting for MRP0 pid 4788 to terminate
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sun Mar 08 16:46:50 2009
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
MRP0 started with pid=20, OS id=6012
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Sun Mar 08 16:46:55 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 142 (in transit)
Sun Mar 08 16:46:56 2009
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Sun Mar 08 16:46:56 2009
Recovery of Online Redo Log: Thread 1 Group 11 Seq 142 Reading mem 0
Mem# 0: D:ORACLE10GORADATASTANDBYSTDREDO01.LOG
Sun Mar 08 16:47:00 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 4372
RFS[7]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 11 thread 1 sequence 142
Primary database is in MAXIMUM PERFORMANCE mode
Sun Mar 08 16:47:01 2009
Media Recovery Waiting for thread 1 sequence 143 (in transit)
Sun Mar 08 16:47:01 2009
RFS[7]: Successfully opened standby log 11: 'D:ORACLE10GORADATASTANDBYSTDREDO01.LOG'
Sun Mar 08 16:47:06 2009
Recovery of Online Redo Log: Thread 1 Group 11 Seq 143 Reading mem 0
Mem# 0: D:ORACLE10GORADATASTANDBYSTDREDO01.LOG
Sun Mar 08 16:47:26 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 5208
RFS[8]: Identified database type as 'physical standby'OK,到这里Broker就配置成功了，其实很简单是不是（成功与否的关键是看你的DG环境是不是规划的很好）下面我们体验一下吧
4、保护模式切换体验
我最初配置的DG的保护模式是最大性能模式（MAXIMUM PERFORMANCE），在下面的体验中我分别完成如下模式的切换
最大性能-->最大保护
最大保护-->最大可用
最大可用-->最大保护
最大保护-->最大性能
1）最大性能到最大保护模式的切换
DGMGRL> edit database primary set property LogXptMode ='SYNC'; --为什么改为SYNC 大家很清楚吧 已更新属性 "logxptmode" DGMGRL> edit database standby set property LogXptMode ='SYNC'; 已更新属性 "logxptmode" DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE ASMAXPROTECTION; --这个restart的过程是自动的 操作要求关闭实例 "primary" (在数据库 "primary" 上) 正在关闭实例 "primary"... 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 操作要求启动实例 "primary" (在数据库 "primary" 上) 正在启动实例 "primary"... ORACLE 例程已经启动。 数据库装载完毕。 DGMGRL>
--切换完成。
在主库(instance_name=primary)查看一下 SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database; NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE --------- ------------ ---------- -------------------- ---------------- DG_DEMO ARCHIVELOG READ WRITE MAXIMUM PROTECTION PRIMARY 在备库(instance_name=standby)查看一下 SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database; NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE --------- ------------ ---------- -------------------- ---------------- DG_DEMO ARCHIVELOG MOUNTED MAXIMUM PROTECTION PHYSICAL STANDBY 注：自己在学习的时候，要注意观察alert日志的信息
2）最大保护到最大可用模式的切换
这个动作比较简单。
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE ASMAXAVAILABILITY;
成功。
在主库(instance_name=primary)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG READ WRITE MAXIMUM AVAILABILITY PRIMARY
SQL>
在备库(instance_name=standby)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG MOUNTED MAXIMUM AVAILABILITY PHYSICAL STANDBY
SQL>注：自己在学习的时候，要注意观察alert日志的信息
3）最大可用到最大保护模式的切换
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE ASMAXPROTECTION; 操作要求关闭实例 "primary" (在数据库 "primary" 上) 正在关闭实例 "primary"... 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 操作要求启动实例 "primary" (在数据库 "primary" 上) 正在启动实例 "primary"... ORACLE 例程已经启动。 数据库装载完毕。 DGMGRL> 在主库(instance_name=primary)查看一下 SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database; NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE --------- ------------ ---------- -------------------- ---------------- DG_DEMO ARCHIVELOG READ WRITE MAXIMUM PROTECTION PRIMARY 在备库(instance_name=standby)查看一下 SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database; NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE --------- ------------ ---------- -------------------- ---------------- DG_DEMO ARCHIVELOG MOUNTED MAXIMUM PROTECTION PHYSICAL STANDBY
注：自己在学习的时候，要注意观察alert日志的信息
4）最大保护到最大性能模式的切换
在测试的最后，我再从最大保护模式切回到最初的最大性能模式
DGMGRL> edit database primary set property LogXptMode ='ASYNC';
已更新属性 "logxptmode"
DGMGRL> edit database standby set property LogXptMode ='ASYNC';
错误: ORA-16805: LogXptMode 属性的更改违反了全局保护模式
失败。
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE ASMAXPERFORMANCE;
成功。
DGMGRL> edit database standby set property LogXptMode ='ASYNC';
已更新属性 "logxptmode"
DGMGRL>
DGMGRL> show configuration verbose
Configuration
Name: dgcfg1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
primary - Primary database
standby - Physical standby database
"dgcfg1" 的当前状态: SUCCESS
DGMGRL>
在主库(instance_name=primary)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY
SQL>
在备库(instance_name=standby)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL>
注：自己在学习的时候，要注意观察alert日志的信息5、SwitchOver切换体验
在这次实验中，我一并做了SwitchOver的切换体验，整个测试工作非常的顺利。
DGMGRL> switchover to standby; --这个standby是我的环境中的备库的名字
立即执行切换, 请稍候...
操作要求关闭实例 "primary" (在数据库 "primary" 上)
正在关闭实例 "primary"...
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
操作要求关闭实例 "standby" (在数据库 "standby" 上)
正在关闭实例 "standby"...
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
操作要求启动实例 "primary" (在数据库 "primary" 上)
正在启动实例 "primary"...
ORACLE 例程已经启动。
数据库装载完毕。
操作要求启动实例 "standby" (在数据库 "standby" 上)
正在启动实例 "standby"...
ORACLE 例程已经启动。
数据库装载完毕。
切换成功, 新的主数据库为 "standby"
DGMGRL> 到这里切换完成。下面再分别连接到原来的主备库上查看一下
在原主库(instance_name=primary)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY --已经变成备库
SQL>
在原备库(instance_name=standby)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY --现在变成主库了
SQL>
DGMGRL> show database primary;
Database
Name: primary
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s): primary
"primary" 的当前状态: SUCCESS
DGMGRL> show database standby;
Database
Name: standby
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s): standby
"standby" 的当前状态:SUCCESS
DGMGRL>
切换成功！
再切换回来吧(我后面还有其他操作需要)
DGMGRL> switchover to primary
立即执行切换, 请稍候...
操作要求关闭实例 "standby" (在数据库 "standby" 上)
正在关闭实例 "standby"...
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
操作要求关闭实例 "primary" (在数据库 "primary" 上)
正在关闭实例 "primary"...
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
操作要求启动实例 "standby" (在数据库 "standby" 上)
正在启动实例 "standby"...
ORACLE 例程已经启动。
数据库装载完毕。
操作要求启动实例 "primary" (在数据库 "primary" 上)
正在启动实例 "primary"...
ORACLE 例程已经启动。
数据库装载完毕。
切换成功, 新的主数据库为 "primary"
DGMGRL> show database primary;
Database
Name: primary
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s): primary
"primary" 的当前状态:SUCCESS
DGMGRL> show database standby;
Database
Name: standby
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s): standby
"standby" 的当前状态:SUCCESS
DGMGRL>
在实验最初的主库(instance_name=primary)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PRIMARY
SQL>
在实验最初的备库(instance_name=standby)查看一下
SQL> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------ ---------- -------------------- ----------------
DG_DEMO ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL>
好了，今天的体验就到这里了！