想到当时我做这个实验的时候,由于在dg_broker工具使用switchover切换主备库时候,忘记了具体语法,
官方文档找没有讲到, 网上找也完全没有太多的文章讲到dg_broker工具管理DG的,有了文章,都是很粗糙的文章,
很难看的明白,关键是参照敲了语句上去,也是报错,还一直嫌疑自己配错了参数文件 CONFIGURATION,
试了几个语句都不行, 都切换不了。由于是凌晨了,心想放到明天再更改参数文件再试一试。第二天,继续这个测试,
心想着,要是弄出来了,一定要把switchover的 测试过程放上去我的博客里。第二天更换了参数文件还是不行,
想着额,难道是库名那里有问题,试了把switchover to 备库名 的库名用单引号括起,奇迹出现了,主备库切换了。
后面问了一些人说这个还是看版本的,说 他当时做也是弄了很久。 一下是测试过程 使用DG_broker工具管理DG:
--主库配置dg broker监听并打开:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ENMO_DGMGRL.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ENMO))
(SID_DESC=
(GLOBAL_DBNAME=ORA11GR2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORA11GR2))
)
SQL> alter system set dg_broker_start=true;
System altered.
--备库配置dg broker监听并打开:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD_DGMGRL.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD))
)
SQL> alter system set dg_broker_start=true;
System altered.
--登录DG——broker工具:
[oracle@oracle admin]$ export ORACLE_SID=ENMO
[oracle@oracle admin]$ dgmgrl #登录使用方法与rman相似
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> connect sys/oracle
Connected.
--首先把之前创建的参数文件删除REMOVE:
DGMGRL> REMOVE CONFIGURATION;
Removed configuration
DGMGRL> SHOW CONFIGURATION;
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>
--创建新的参数文件CONFIGURATION:
DGMGRL> CREATE CONFIGURATION 'ENMO' as PRIMARY DATABASE IS 'ENMO' CONNECT IDENTIFIER IS ENMO;
Configuration "ENMO" created with primary database "ENMO"
--查看参数文件的状态:
DGMGRL> SHOW CONFIGURATION;
Configuration - ENMO
Protection Mode: MaxAvailability
Databases:
ENMO - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
--添加备库:
DGMGRL> ADD DATABASE 'PROD' AS CONNECT IDENTIFIER IS ENMO;
Error: ORA-16662: network timeout when contacting a database
Failed.
添加备库错误。
--正确的添加方法:
DGMGRL> ADD DATABASE 'PROD' AS CONNECT IDENTIFIER IS PROD;
Database "PROD" added
--启用参数文件:
DGMGRL> ENABLE CONFIGURATION;
Enabled.
启用成功。
--查看参数文件状态:
DGMGRL> SHOW CONFIGURATION;
Configuration - ENMO
Protection Mode: MaxAvailability
Databases:
ENMO - Primary database
PROD - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
已经使用。
--启用备库PROD:
DGMGRL> ENABLE DATABASE 'PROD'
Enabled.
DGMGRL>
启用成功。
--查看备库的状态:
DGMGRL> SHOW CONFIGURATION;
Configuration - ENMO
Protection Mode: MaxAvailability
Databases:
ENMO - Primary database
PROD - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--查看详细信息:
DGMGRL> SHOW DATABASE 'PROD';
Database - PROD
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 895.00 KByte/s
Real Time Query: ON
Instance(s):
PROD
Database Status:
SUCCESS
--查看主库或者备库更详细的信息:
DGMGRL> SHOW DATABASE VERBOSE 'ENMO'; #同样去查看另外一个库的状态,只需修改唯一库名
Database - ENMO
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ENMO
Properties:
DGConnectIdentifier = 'enmo'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'PROD, ENMO'
LogFileNameConvert = 'PROD, ENMO'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'ENMO'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ENMO_DGMGRL.oracle.com)(INSTANCE_NAME=ENMO)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/home/oracle/arch/ENMO/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
--切换主备库:
DGMGRL> switchover to 'PROD'; #这里特别强调:to备库名,有些版本不需加单引号括起,有些版本需要括起。
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "PROD"
Connecting to instance "PROD"...
Connected.
New primary database "PROD" is opening...
Operation requires startup of instance "ENMO" on database "ENMO"
Starting instance "ENMO"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PROD"
--切换之后,查看新的主库与备库信息:
--查看主库状态:
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
--查看备库的状态:
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
--切换保护模式:
----重新调至最大可用模式:
DGMGRL> EDIT DATABASE 'PROD' SET PROPERTY 'LogXptMode'='ASYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'ENMO' SET PROPERTY 'LogXptMode'='ASYNC';
Error: ORA-16805: change of LogXptMode property violates overall protection mode
Failed.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - ENMO
Protection Mode: MaxPerformance
Databases:
PROD - Primary database
ENMO - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
最大可用模式切换成功。
官方文档找没有讲到, 网上找也完全没有太多的文章讲到dg_broker工具管理DG的,有了文章,都是很粗糙的文章,
很难看的明白,关键是参照敲了语句上去,也是报错,还一直嫌疑自己配错了参数文件 CONFIGURATION,
试了几个语句都不行, 都切换不了。由于是凌晨了,心想放到明天再更改参数文件再试一试。第二天,继续这个测试,
心想着,要是弄出来了,一定要把switchover的 测试过程放上去我的博客里。第二天更换了参数文件还是不行,
想着额,难道是库名那里有问题,试了把switchover to 备库名 的库名用单引号括起,奇迹出现了,主备库切换了。
后面问了一些人说这个还是看版本的,说 他当时做也是弄了很久。 一下是测试过程 使用DG_broker工具管理DG:
--主库配置dg broker监听并打开:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ENMO_DGMGRL.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ENMO))
(SID_DESC=
(GLOBAL_DBNAME=ORA11GR2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORA11GR2))
)
SQL> alter system set dg_broker_start=true;
System altered.
--备库配置dg broker监听并打开:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD_DGMGRL.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD))
)
SQL> alter system set dg_broker_start=true;
System altered.
--登录DG——broker工具:
[oracle@oracle admin]$ export ORACLE_SID=ENMO
[oracle@oracle admin]$ dgmgrl #登录使用方法与rman相似
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> connect sys/oracle
Connected.
--首先把之前创建的参数文件删除REMOVE:
DGMGRL> REMOVE CONFIGURATION;
Removed configuration
DGMGRL> SHOW CONFIGURATION;
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>
--创建新的参数文件CONFIGURATION:
DGMGRL> CREATE CONFIGURATION 'ENMO' as PRIMARY DATABASE IS 'ENMO' CONNECT IDENTIFIER IS ENMO;
Configuration "ENMO" created with primary database "ENMO"
--查看参数文件的状态:
DGMGRL> SHOW CONFIGURATION;
Configuration - ENMO
Protection Mode: MaxAvailability
Databases:
ENMO - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
--添加备库:
DGMGRL> ADD DATABASE 'PROD' AS CONNECT IDENTIFIER IS ENMO;
Error: ORA-16662: network timeout when contacting a database
Failed.
添加备库错误。
--正确的添加方法:
DGMGRL> ADD DATABASE 'PROD' AS CONNECT IDENTIFIER IS PROD;
Database "PROD" added
--启用参数文件:
DGMGRL> ENABLE CONFIGURATION;
Enabled.
启用成功。
--查看参数文件状态:
DGMGRL> SHOW CONFIGURATION;
Configuration - ENMO
Protection Mode: MaxAvailability
Databases:
ENMO - Primary database
PROD - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
已经使用。
--启用备库PROD:
DGMGRL> ENABLE DATABASE 'PROD'
Enabled.
DGMGRL>
启用成功。
--查看备库的状态:
DGMGRL> SHOW CONFIGURATION;
Configuration - ENMO
Protection Mode: MaxAvailability
Databases:
ENMO - Primary database
PROD - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--查看详细信息:
DGMGRL> SHOW DATABASE 'PROD';
Database - PROD
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 895.00 KByte/s
Real Time Query: ON
Instance(s):
PROD
Database Status:
SUCCESS
--查看主库或者备库更详细的信息:
DGMGRL> SHOW DATABASE VERBOSE 'ENMO'; #同样去查看另外一个库的状态,只需修改唯一库名
Database - ENMO
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ENMO
Properties:
DGConnectIdentifier = 'enmo'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'PROD, ENMO'
LogFileNameConvert = 'PROD, ENMO'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'ENMO'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ENMO_DGMGRL.oracle.com)(INSTANCE_NAME=ENMO)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/home/oracle/arch/ENMO/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
--切换主备库:
DGMGRL> switchover to 'PROD'; #这里特别强调:to备库名,有些版本不需加单引号括起,有些版本需要括起。
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "PROD"
Connecting to instance "PROD"...
Connected.
New primary database "PROD" is opening...
Operation requires startup of instance "ENMO" on database "ENMO"
Starting instance "ENMO"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PROD"
--切换之后,查看新的主库与备库信息:
--查看主库状态:
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
--查看备库的状态:
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
--切换保护模式:
----重新调至最大可用模式:
DGMGRL> EDIT DATABASE 'PROD' SET PROPERTY 'LogXptMode'='ASYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'ENMO' SET PROPERTY 'LogXptMode'='ASYNC';
Error: ORA-16805: change of LogXptMode property violates overall protection mode
Failed.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - ENMO
Protection Mode: MaxPerformance
Databases:
PROD - Primary database
ENMO - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
最大可用模式切换成功。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2126922/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2126922/