时间：2021-07-01 10:21:17 帮助过：19人阅读
? (DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.202)(PORT = 1521))
??? )
??? (CONNECT_DATA = (SERVICE_NAME =DBSALVE_DGMGRL.shark.com)
??? )
? )
?
MABDGMGR =
? (DESCRIPTION =
??? (ADDRESS_LIST =
????? (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1521))
??? )
??? (CONNECT_DATA =
????? (SERVICE_NAME = DBMAST_DGMGRL.shark.com)
??? )
? )
??
备库监听
[oracle@DB-MASTER ~]lsnrctl status
?
LSNRCTL for Linux: Version 11.2.0.4.0 -Production on 20-4月 -2016 01:05:28
?
Copyright (c) 1991, 2013, Oracle.? All rights reserved.
?
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DB-MASTER)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名????????????????????? LISTENER
版本号????????????????????? TNSLSNRfor Linux: Version 11.2.0.4.0 - Production
启动日期????????????????? 19-4月 -201615:06:59
正常运行时间????????????? 0 天 9 小时 58 分 29 秒
跟踪级别????????????????? off
安全性??? ????????????????ON: Local OS Authentication
SNMP????????????????????? OFF
监听程序參数文件?????????/u01/app/software/oracle/product/11.2.0/db_1/network/admin/listener.ora
监听程序日志文件?????????/u01/app/software/oracle/diag/tnslsnr/DB-MASTER/listener/alert/log.xml
监听端点概要...
?(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
服务摘要..
服务 "DBMAST" 包括 1 个实例。
? 实例"DBMAST", 状态 READY, 包括此服务的 1 个处理程序...
服务 "DBMASTXDB" 包括 1 个实例。
? 实例"DBMAST", 状态 READY, 包括此服务的 1 个处理程序...
服务 "DBMAST_DGMGRL.shark.com" 包括 1 个实例。
? 实例"DBMAST", 状态 UNKNOWN, 包括此服务的 1 个处理程序...
命令运行成功
?
[oracle@DB-MASTER admin]cat listener.ora
# listener.ora Network Configuration File:/u01/app/software/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
?
SID_LIST_LISTENER =
?(SID_LIST =
???(SID_DESC =
?????(GLOBAL_DBNAME = DBMAST_DGMGRL.shark.com)
?????(ORACLE_HOME = /u01/app/software/oracle/product/11.2.0/db_1)
?????(SID_NAME = DBMAST)
??? )
? )
?
LISTENER =
?(DESCRIPTION =
???(ADDRESS = (PROTOCOL = TCP)(HOST = DB-MASTER)(PORT = 1521))
? )
?
ADR_BASE_LISTENER =/u01/app/software/oracle
?
主库监听
[oracle@DB-Salve admin]cat listener.ora
# listener.ora Network Configuration File:/u01/app/software/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
?
SID_LIST_LISTENER =
?(SID_LIST =
??? (SID_DESC =
?????(GLOBAL_DBNAME = DBSALVE_DGMGRL.shark.com)
????? (ORACLE_HOME =/u01/app/software/oracle/product/11.2.0/db_1)
????? (SID_NAME = DBSALVE)
??? )
???(SID_DESC =
?????(GLOBAL_DBNAME = DBSALVE)
?????(ORACLE_HOME = /u01/app/software/oracle/product/11.2.0/db_1)
?????(SID_NAME = DBSALVE)
??? )
? )
?
?
LISTENER =
?(DESCRIPTION =
???(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.202)(PORT = 1521))
? )
?
ADR_BASE_LISTENER =/u01/app/software/oracle
?
[oracle@DB-Salve admin]lsnrctl status
?
LSNRCTL for Linux: Version 11.2.0.4.0 -Production on 20-4月 -2016 01:10:45
?
Copyright (c) 1991, 2013, Oracle.? All rights reserved.
?
正在连接到(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.202)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名????????????????????? LISTENER
版本号????????????????????? TNSLSNRfor Linux: Version 11.2.0.4.0 - Production
启动日期????????????????? 19-4月 -201615:04:18
正常运行时间????????????? 0 天 10 小时 6 分 26 秒
跟踪级别????????????????? off
安全性??????????????????? ON: LocalOS Authentication
SNMP???????????? ?????????OFF
监听程序參数文件?????????/u01/app/software/oracle/product/11.2.0/db_1/network/admin/listener.ora
监听程序日志文件?????????/u01/app/software/oracle/diag/tnslsnr/DB-Salve/listener/alert/log.xml
监听端点概要...
?(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.202)(PORT=1521)))
服务摘要..
服务 "DBSALVE" 包括 1 个实例。
? 实例"DBSALVE", 状态 UNKNOWN, 包括此服务的 1 个处理程序...
服务 "DBSALVE_DGMGRL.shark.com" 包括 1 个实例。
? 实例"DBSALVE", 状态 UNKNOWN, 包括此服务的 1 个处理程序...
命令运行成功
?
主备都要使用SPFILE启动
SQL> setlinesize 1000
SQL> showparameter spfile
?
NAME?????????????????????????????????????????? ???? TYPE????????????????????????????????? ?????? VALUE
--------------------------------------------------------------------- ------------------------------------------------------------------------------------------
spfile????????????????????????????????????????????? ???? string??????????????????????????????? /u01/app/software/oracle/product/11.2.0/db_1/dbs/spfileDBSALVE.ora
?
注意主备库的唯一命名 而且db_unique_name?大写和小写敏感
SQL> showparameter db_unique_name
?
NAME?????????????????????????????????????????? ???? TYPE????????????????????????????????? ?????? VALUE
--------------------------------------------------------------------- ------------------------------
db_unique_name?????????????????????????????????? ???? string??????????????????????????????? ?????? DBSALVE
?
SQL> show? parameter db_unique_name
?
NAME?????????????????????????????????????????? ???? TYPE????????????????????????????????? ?????? VALUE
--------------------------------------------------------------------- ------------------------------
db_unique_name?????????????????????????????????? ???? string??????????????????????????????? ?????? DBMAST
?
主备启动DG BROKER 进程
Alter system set dg_broker_start=truescope=both;
?
SQL> show parameter dg
NAME????????????????? ???? TYPE???????????? ?????? VALUE
--------------------------------------------------------------------- ------------------------------
dg_broker_config_file1????? ????string????? /u01/app/software/oracle/product/11.2.0/db_1/dbs/dr1DBMAST.dat
dg_broker_config_file2????? ????string????? /u01/app/software/oracle/product/11.2.0/db_1/dbs/dr2DBMAST.dat
dg_broker_start ????? ??????????boolean???? TRUE
?
主备要处于最大可用模式
Set linesize 1000
col db_unique_nameformat ?a15
col open_modeformat ?a20
col flashback_onformat a15
col database_roleformat a20
coldataguard_broker format a20
col protection_modeformat a25
colswitchover_status format a25
?
主库
SQL>
selectDB_UNIQUE_NAME,open_mode,FLASHBACK_ON,DATABASE_ROLE,DATAGUARD_BROKER,PROTECTION_MODE,SWITCHOVER_STATUSfrom v$database;
?
DB_UNIQUE_NAME? OPEN_MODE?????????????? FLASHBACK_ON????????? DATABASE_ROLE??????? DATAGUARD_BROKER????PROTECTION_MODE????????????? ?????? SWITCHOVER_STATUS
------------------------------ --------------- --------------- --------------------------------------------- -------------------------
DBSALVE ??????? READ WRITE?? NO????????????????????? PRIMARY?????? DISABLED??????? ???? MAXIMUMAVAILABILITY????? SESSIONS ACTIVE
?
备库
SQL> selectDB_UNIQUE_NAME,open_mode,FLASHBACK_ON,DATABASE_ROLE,DATAGUARD_BROKER,PROTECTION_MODE,SWITCHOVER_STATUSfrom v$database;
?
DB_UNIQUE_NAME? OPEN_MODE?????????????? ???? FLASHBACK_ON??? DATABASE_ROLE???????? ?DATAGUARD_BROKER????PROTECTION_MODE?????????????????????? SWITCHOVER_STATUS
----------------------------------- --------------- -------------------- --------------------------------------------- -------------------------
DBMAST??????????????????????? READ ONLY WITH APPLY NO ??????????? ?????PHYSICAL STANDBY???? ? DISABLED?? ??????MAXIMUM AVAILABILITY????????????? NOT ALLOWED
?
SQL> showparameter log_archive_
?
NAME?????????????????????????????????????????? ???? TYPE????????????????????????????????? ?????? VALUE
--------------------------------------------------------------------- ------------------------------
log_archive_config????????????????? ???? string??????????????????????????????? ?????? dg_config=(DBMAST,DBSALVE,DBSALVE2)
log_archive_dest????????????????????? ???? string
log_archive_dest_1???????????????? ???? string??????????????????????????????? ?????? LOCATION=/u05/arch_backup
log_archive_dest_2???????????????? ???? string??????????????????????????????? ?????? SERVICE=DBMAST lgwr sync affirmvalid_for=(online_logfiles,primary_role) db_unique_name=DBMAST
?
备库日志传输
NAME?????????????????????????????????????????? ???? TYPE????????????????????????????????? ?????? VALUE
--------------------------------------------------------------------- ------------------------------
log_archive_config????????????????? ???? string??????????????????????????????? ?????? dg_config=(DBMAST,DBSALVE,DBSALVE2)
log_archive_dest????????????????????? ???? string
log_archive_dest_1???????????????? ???? string??????????????????????????????? ??????LOCATION=/u05/arch_backup
log_archive_dest_2???????????????? ???? string??????????????????????????????? ?????? SERVICE=DBSALVE lgwr sync affirmvalid_for=(online_logfiles, primary_role) db_unique_name=DBSALVE
?
进入DG MGR 管理接口
[oracle@DB-Salve~]dgmgrl
DGMGRL for Linux:Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000,2009, Oracle. All rights reserved.
欢迎使用 DGMGRL, 要获取有关信息请键入 "help"。
DGMGRL>
?
连接主库
DGMGRL> connectsys/oracle@DBSALVE;
已连接。
?
创建配置文件
语法:
?
? CREATE CONFIGURATION <configurationname> AS
??? PRIMARY DATABASE IS <database name>?? --主库的DB_UNIQUE_NAME
CONNECT IDENTIFIER IS <connect identifier>; --主库的TNS_NAME
?
DGMGRL> CREATECONFIGURATION ‘DG_BROKER_SALVE‘ AS PRIMARY DATABASE IS ‘DBSALVE‘ CONNECTIDENTIFIER IS STBDGMGR;
已创建配置 "DG_BROKER_SALVE", 当中主数据库为 "DBSALVE"
DGMGRL> showconfiguration;
配置 - DG_BROKER_SALVE
? 保护模式:???MaxAvailability
? 数据库:
??? DBSALVE - 主数据库
高速启动故障转移: DISABLED
配置状态:
DISABLED
?
加入standby database到配置
DGMGRL> ADDDATABASE ‘DBMAST‘ AS CONNECT IDENTIFIER IS?‘MABDGMGR‘ MAINTAINED AS PHYSICAL;
已加入数据库 "DBMAST"
?
显示数据库属性
DGMGRL> SHOWDATABASE VERBOSE ‘DBMAST‘;
?
数据库 - DBMAST
?
? 角色:????????? PHYSICAL STANDBY
? 预期状态:??? OFFLINE
? 传输滞后:??? (未知)
? 应用滞后:??? (未知)
? 应用速率:??? (未知)
? 实时查询:??? OFF
? 实例:
??? DBMAST
?
? 属性:
??? DGConnectIdentifier???????????? = ‘MABDGMGR‘
??? 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????????? = ‘4‘
??? LogArchiveMinSucceedDest??????? = ‘1‘
??? DbFileNameConvert?????????????? = ‘‘
??? LogFileNameConvert????????????? = ‘‘
??? FastStartFailoverTarget???????? = ‘‘
??? InconsistentProperties????????? = ‘(monitor)‘
??? InconsistentLogXptProps???????? = ‘(monitor)‘
??? SendQEntries??????????????????? = ‘(monitor)‘
??? LogXptStatus??????????????????? = ‘(monitor)‘
??? RecvQEntries??????????????????? = ‘(monitor)‘
??? ApplyLagThreshold?????????????? = ‘0‘
??? TransportLagThreshold?????????? = ‘0‘
??? TransportDisconnectedThreshold? = ‘30‘
??? SidName???????????????????????? = ‘DBMAST‘
??? StaticConnectIdentifier???????? =‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB-MASTER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMAST_DGMGRL)(INSTANCE_NAME=DBMAST)(SERVER=DEDICATED)))‘
??? StandbyArchiveLocation????????? = ‘/u05/arch_backup‘
??? AlternateLocation?????? ????????= ‘‘
??? LogArchiveTrace???????????????? = ‘0‘
??? LogArchiveFormat??????????????? = ‘%t_%s_%r.archlog‘
??? TopWaitEvents?????????????????? = ‘(monitor)‘
?
数据库状态:
DISABLED
?
编辑数据库连接属性
?
DGMGRL> EDITDATABASE ‘DBMAST‘ SET PROPERTY
‘StaticConnectIdentifier‘=‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMAST_DGMGRL.shark.com)(INSTANCE_NAME=DBMAST)(SERVER=DEDICATED)))‘;
已更新属性"StaticConnectIdentifier"
?
EDIT DATABASE‘DBSALVE‘ SET PROPERTY
‘StaticConnectIdentifier‘=‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.202)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSALVE_DGMGRL.shark.com)(INSTANCE_NAME=DBSALVE)(SERVER=DEDICATED)))‘;
?
有效配置文件 (须要一段时间)
DGMGRL>? ENABLE CONFIGURATION;
已启用。
開始连接到备库
DGMGRL> connectsys/oracle@DBMAST;
已连接。
DGMGRL> showconfiguration;
配置 - DG_BROKER_SALVE
?
? 保护模式:???MaxAvailability
? 数据库:
??? DBSALVE - 主数据库
??? DBMAST?- 物理备用数据库
?
高速启动故障转移: DISABLED
?
配置状态:
SUCCESS
注意 这里会报非常多错误具体见FAQ
DGMGRL>switchover to ‘DBMAST‘;
马上运行切换, 请稍候...
新的主数据库 "DBMAST" 正在打开...
操作要求启动实例 "DBSALVE" (在数据库 "DBSALVE" 上)
正在启动实例 "DBSALVE"...
ORA-32004: obsoleteor deprecated parameter(s) specified for RDBMS instance
ORACLE 例程已经启动。
数据库装载完毕。
数据库已经打开。
切换成功, 新的主数据库为 "DBMAST"
DGMGRL> showconfiguration;
?
配置 - DG_BROKER_SALVE
?
? 保护模式:???MaxAvailability
? 数据库:
??? DBMAST?- 主数据库
??? DBSALVE - 物理备用数据库
?
高速启动故障转移: DISABLED
?
配置状态:
SUCCESS
?
?
?
FAQ
1
DGMGRL>switchover to ‘DBMAST‘;
马上运行切换, 请稍候...
新的主数据库 "DBMAST" 正在打开...
操作要求启动实例 "DBSALVE" (在数据库 "DBSALVE" 上)
正在启动实例 "DBSALVE"...
无法连接到数据库
ORA-12521: TNS: 监听程序当前无法识别连接描写叙述符中请求的实例
?
失败。
警告: 您不再连接到 ORACLE。
?
请运行下面步骤以完毕切换:
????????????? 启动实例 "DBSALVE" (属于数据库 "DBSALVE")
?
这个问题应该属于DBSALVE 连接属性 的INSTAN_NAME写错了
?
2
DGMGRL> show configuration;
配置 - dg_broker_mast
?保护模式:???MaxPerformance
? 数据库:
??? dbmast? - 主数据库
??? dbsalve - 物理备用数据库 (禁用)
高速启动故障转移: DISABLED
配置状态:
SUCCESS
DGMGRL> enable database dbsalve;
已启用。
DGMGRL> show configuration;
配置 - dg_broker_mast
? 保护模式:???MaxPerformance
? 数据库:
??? dbmast? - 主数据库
??? dbsalve - 物理备用数据库
????? 错误: ORA-16664: 无法从数据库接收结果
高速启动故障转移: DISABLED
配置状态:
ERROR
要连接到备库 CONNECT SYS/ORACLE@DBSALVE
?
3
DGMGRL> enable configuration;
已启用。
DGMGRL> show configuration
配置 - dg_borker_mast_conf
? 保护模式:???MaxAvailability
? 数据库:
??? dbmast - 主数据库
????? 警告: ORA-16629: 数据库报告的保护级别与保护模式的保护级别不同
高速启动故障转移: DISABLED
配置状态:
WARNING
?
alter system set log_archive_dest_2
=‘SERVICE=DBSALVE lgwr sync affirmvalid_for=(online_logfiles,primary_role) db_unique_name=DBSALVE‘ scope=both;
改动对应的參数为:LGWR? SYNC? AFFIRM
?
4
DGMGRL> show configuration
配置 - dg_borker_mast_conf
? 保护模式:???MaxAvailability
? 数据库:
??? dbmast? - 主数据库
??? dbsalve - 物理备用数据库
????? 警告: ORA-16792: 可配置属性的值与数据库设置不一致
高速启动故障转移: DISABLED
配置状态:
WARNING
?
DGMGRL> show database dbsalve statusreport;
STATUS REPORT
?????? INSTANCE_NAME?? SEVERITY ERROR_TEXT
???????????? DBSALVE??? WARNING ORA-16714: 属性 ArchiveLagTarget 的值与数据库设置不一致
???????????? DBSALVE??? WARNING ORA-16714: 属性 LogArchiveMaxProcesses 的值与数据库设置不一致
???????????? DBSALVE??? WARNING ORA-16714: 属性 LogArchiveMinSucceedDest 的值与数据库设置不一致
? ???????????DBSALVE??? WARNING ORA-16714: 属性 LogArchiveTrace 的值与数据库设置不一致
???????????? DBSALVE??? WARNING ORA-16714: 属性 LogArchiveFormat 的值与数据库设置不一致
显示下备库眼下的属性值,竟然跟数据库參数一直的,Why 还要报错呢?
DGMGRL> show database dbsalve ArchiveLagTarget
? ??????????? ArchiveLagTarget = ‘0‘
DGMGRL> show database dbsalve LogArchiveMaxProcesses
? ??????????? LogArchiveMaxProcesses = ‘4‘
DGMGRL> show database dbsalve LogArchiveFormat
? ??????????? LogArchiveFormat = ‘%t_%s_%r.archlog‘
DGMGRL> show database dbsalve LogArchiveMinSucceedDest
? ??????????? LogArchiveMinSucceedDest = ‘1‘
?
编辑属性,同步下使得配置文件跟数据库參数一致
DGMGRL> edit database?dbsalve set property ArchiveLagTarget=‘0‘;
已更新属性 "archivelagtarget"
?
?
5 切换演练
DGMGRL> switchover to dbsalve;
马上运行切换, 请稍候...
操作要求连接实例 "DBSALVE" (在数据库"dbsalve" 上)
正在连接实例 "DBSALVE"...
无法连接到数据库
ORA-12514: TNS: 监听程序当前无法识别连接描写叙述符中请求的服务
失败。
警告: 您不再连接到 ORACLE。
???? 连接到实例 "DBSALVE" (属于数据库 "dbsalve")
?
这主要是TNS_NAME里的配置不合理.要在TNS_NAME里加入个特别的TNS
比方说DBSALVEBR= SEVRICE_NAMES=(DBSALVE_DGMGRL.SHARK.COM)
同一时候要更新配置里的数据库属性的
DGConnectIdentifier????????????= ‘dbsalve‘
DGConnectIdentifier????????????=’DBSALVEBR’
?
?
6 切换失败
DGMGRL> showconfiguration;
配置 - DG_BROKER_SALVE
? 保护模式:???MaxAvailability
? 数据库:
??? DBSALVE - 主数据库
??? DBMAST?- 物理备用数据库
高速启动故障转移: DISABLED
配置状态:
ORA-16597: DataGuard 中介检測到两个或多个主数据库
ORA-16625: 无法訪问数据库 "DBSALVE"
DGM-17017: 无法确定配置状态
?
你发现它已经切换了主备的角色,当备库变成主库的时候,还是MOUNT状态, 而主库变备库的时候,shutdown后无法startup.
然后手工把主备起来. 而配置文件依然无法从新的主备中获取状态.
之所以无法成功 由于StaticConnectIdentifier写错了实列名.
?
配置状态无法继续,也无法无效,无法REMOVE. 那么关闭主备的DGBROKER进程. 同一时候把主备的两个配置文件移走.
又一次启动DG BROKER进程.又一次配,配置文件.
?
7 DB_UNQIUE_NAME 大写的时候
?1加入 主备库的时候要用单引號 引起 ‘DBMAST’
2 使用配置数据库名的时候也要单引號引起 ?
switchover to ‘DBSALVE’
show database ‘DBSALVE’ statusreport;
ORACLE 11G R2 DG_BROKER 之SWITCH OVER
标签：success protect unique color copyright war tool arc status