#ADG环境 DGMGRL管理
1.登录
[oracle@XAG126 ~]$ dgmgrl sys/123456
[oracle@XAG126 ~]$ dgmgrl sys/123456@sl
[oracle@XAG126 ~]$ dgmgrl
DGMGRL> connect sys/123456
DGMGRL> connect sys/123456@sl
#停快速故障轉移功能
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
#啟動快速故障轉移功能
DGMGRL>enable fast_start failover;
DGMGRL>start observer
2.查看命令帮助
DGMGRL> help
The following commands are available:
@ Execute DGMGRL script file
! Host operating system command
/ Repeat the last command
-- Comment to be ignored by DGMGRL
add Adds a member to the broker configuration
connect Connects to an Oracle database instance
convert Converts a database from one type to another
create Creates a broker configuration
disable Disables a configuration, a member, or fast-start failover
edit Edits a configuration or a member
enable Enables a configuration, a member, or fast-start failover
exit Exits the program
failover Changes a standby database to be the primary database
help Displays description and syntax for a command
host Host operating system command
migrate Migrate a pluggable database from one configuration to another.
quit Exits the program
reinstate Changes a database marked for reinstatement into a viable standby
rem Comment to be ignored by DGMGRL
remove Removes a configuration or a member
set Set a property to a specified value
show Displays information about a configuration or a member
shutdown Shuts down a currently running Oracle database instance
spool store input and output of DGMGRL CLI in a file
sql Executes a SQL statement
start Starts the fast-start failover observer
startup Starts an Oracle database instance
stop Stops the fast-start failover observer
switchover Switches roles between a primary and standby database
validate Performs an exhaustive set of validations for a database
Use "help <command>" to see syntax for individual commands
3.检查配置
DGMGRL> show configuration
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 36 seconds ago)
DGMGRL> show configuration verbose
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - (*) Physical standby database
(*) Fast-Start Failover target
Properties:
FastStartFailoverThreshold = '60'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'ORACLE12_CFG'
Fast-Start Failover: ENABLED
Threshold: 60 seconds
Target: DG126
Observer: XAG126.COM
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS
4.查看数据库或者实例
DGMGRL> show database 'DG125'
Database - DG125
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORACLE12CDG
Database Status:
SUCCESS
DGMGRL> show database 'DG126'
Database - DG126
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 22.00 KByte/s
Real Time Query: ON
Instance(s):
ORACLE12CDG
Database Status:
SUCCESS
DGMGRL> show database verbose 'DG125'
edit database 'DG125' set property 'LogArchiveFormat' = '%t_%s_%r.dbf';
edit database 'DG126' set property 'LogArchiveFormat' = '%t_%s_%r.dbf';
5.DGMGRL 日志
日志drcdb_unique_name.log里面看到，日志文件存放位置和alert.log一样
[oracle@XAG126 trace]$ pwd
/u01/app/oracle/diag/rdbms/dg126/ORACLE12CDG/trace
[oracle@XAG126 trace]$ ls drc*
drcORACLE12CDG.log
6.数据库监控状态查看
broker会自动的收集同一个配置之下的所有数据库的健康状态，管理员只需要通过简单的show命令就可以查看数据库的状态了。命令格式如下
show database db_unique_name statue_name可用的状态命令列表如下：
StatusReport 显示所有broker检查到的问题
LogXptStatus 显示日志传送的状态
InconsistentProperties 显示不一致的数据库属性
InconsistentLogXptProps 显示不一致的日志传送设定
举例：
DGMGRL> show database 'DG125' statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database 'DG126' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database 'DG125' InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL> show database 'DG125' InconsistentLogXptProps
INCONSISTENT LOG TRANSPORT PROPERTIES
INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE
DGMGRL> show database 'DG125' LogXptStatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
ORACLE12CDG DG126
DGMGRL> show database 'DG126' LogXptStatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
7.配置保护模式与日志传递方式
LogXptMode这个属性设置的是LOG_ARCHIVE_DEST_n中传送模式那部分
SYNC：相当于LGWR, SYNC, AFFIRM
ASYNC：相当于LGWR, ASYNC, NOAFFIRM
ARCH：相当于ARCH
#切换到最大性能模式
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
DGMGRL> edit configuration set protection mode as MaxPerformance;
DGMGRL> edit database 'DG125' set property LogXptMode ='ASYNC';
DGMGRL> edit database 'DG126' set property LogXptMode ='ASYNC';
DGMGRL>enable fast_start failover;
DGMGRL>start observer
#切换到最大可用模式
DGMGRL> edit database 'DG125' set property LogXptMode ='SYNC';
DGMGRL> edit database 'DG126' set property LogXptMode ='SYNC';
DGMGRL> edit configuration set protection mode as MaxAvailability;
b.使用Maximum performance的时候，参数（备库）LogXptMode设置为async/sync
c.使用Maximum protection、Maximum availability的时候，参数（备库）LogXptMode要设置为sync。
不同的保护模式对broker操作的影响
1). 当升级保护模式的时候，broker会自动的重启primary数据库。降低保护模式级别的时候是不需要重启数据库的。(11g、12c更改保护模式都不用重启数据库更改保护模式都不用重启数据库)
2). switchover操作不会改变当前的保护模式。
3). 做手工的failover之后，如果原来保护模式是MaxProtection的话会被自动降级为MaxPerformance；如果是其他模式的话则保持不变。
4). fast-start failover所作的自动的failover操作不会改变数据库的保护模式。
5). 做disable操作或remove database之前，broker会检查disable之后是否还能保证满足当前的保护模式，如果不能的话disable/remove会失败。
6). fast-start failover启用的是很不能做disable configuration操作。
8.暂停和启动dg应用
有如下TRANSPORT-ON、TRANSPORT-OFF、APPLY-ON、APPLY-OFF四种。分别表示传输日志开关、应用日志开关。
#暂停备库日志应用：
DGMGRL> show database 'DG126'
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
DGMGRL> edit database 'DG126' set state="APPLY-OFF";
DGMGRL> show database 'DG126';
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
------------------------------
#启动备库日志应用：
DGMGRL> edit database 'DG126' set state="APPLY-ON";
DGMGRL> show database 'DG126'
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
#设置备库数据库只读。
------------------------------
#停止主库到从库的日志传送 （最大性能模式下支持）
DGMGRL> edit configuration set protection mode as MaxPerformance;
DGMGRL> edit database 'DG125' set state='TRANSPORT-OFF';
DGMGRL> show database 'DG125';
Role: PRIMARY
Intended State: TRANSPORT-OFF
#启动主库到从库的日志传送 （最大性能模式下支持）
DGMGRL> edit database 'DG125' set state='TRANSPORT-ON';
DGMGRL> show database 'DG125';
Role: PRIMARY
Intended State: TRANSPORT-ON
9.备库状态管理
DGMGRL> show configuration
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 1 second ago)
#备库shutdown
[oracle@XAG126 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
--发现备库状态为disabled
DGMGRL> show configuration
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
DG126 - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 6 seconds ago)
---------------------------------------------------
--发现状备库态为OFFLINE和SHUTDOWN
DGMGRL> show database 'DG126'
Database - DG126
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ORACLE12CDG
Database Status:
SHUTDOWN
---------------------------------------------------
--启动备库到mount状态
SQL> startup mount;
--发现备库已经正常
DGMGRL> show configuration
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 11 seconds ago)
--这时可以发现备库已经自动应用redo日志，不需要在sqlplus中输入alter database recover managed standby database ...类似的语句,但Real Time Query: OFF
DGMGRL> show database 'DG126'
Database - DG126
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 6.00 KByte/s
Real Time Query: OFF
Instance(s):
ORACLE12CDG
Database Status:
SUCCESS
--通过如下语句也可以查看redo日志正在传输中
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
--Oracle11g新特性read-time apply
在Oracle11g之前的版本。物理备库处于日志应用状态时，是无法从备库读取数据的。如果想开库，需停止日志应用，备库可以开到read only状态。如果物理备库从read only状态回到日志应用状态，要先关掉物理备库，再将库启到mount状态，最后重新应用日志。
这样要从备库读数据，日志应用就必须停掉。无法实现边应用日志、边读取数据。11g 可以使用active standby，实现日志应用和查询同时进行。即Real-Time Apply + Real-Time Query.
--直接打开备库，成功
SQL> alter database open;
--此时发现 Real Time Query: ON
DGMGRL> show database 'DG126'
Database - DG126
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: ON
Instance(s):
ORACLE12CDG
Database Status:
SUCCESS
--mount阶段直接open后，可以发现redo日志一样在传输
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
10.配置及启用Fast-Start Failover
1.配置每个数据库Failover的目标，这一步是决定当数据库出问题之后会自动failover到那个standby。
DGMGRL> show database 'DG125' FastStartFailoverTarget
FastStartFailoverTarget = 'DG126'
DGMGRL> show database 'DG126' FastStartFailoverTarget
FastStartFailoverTarget = 'DG125'
DGMGRL> edit database 'DG125' set property FastStartFailoverTarget='DG126';
DGMGRL> show database 'DG125' FastStartFailoverTarget
FastStartFailoverTarget = 'DG126'
--设定FastStartFailoverThreshold值，这个设置是决定了primary坏了多长时间之后会执行自动的failover操作，这里我们设置的是30s。
DGMGRL> show database 'DG125' nettimeout
NetTimeout = '30'
DGMGRL> show database 'DG126' nettimeout
NetTimeout = '30'
DGMGRL> edit database 'DG125' set property 'NetTimeout'=30;
Property "NetTimeout" updated
DGMGRL> show database 'DG125' nettimeout
NetTimeout = '30'
--启用Fast-Start Failover。
DGMGRL> enable fast_start failover
#修改 log_archive_dest_1 到 USE_DB_RECOVERY_FILE_DEST 上
#DG125
SQL> show parameter log_archive_dest_1;
log_archive_dest_1 location=/u01/app/oracle/fast_recovery_area/DG/DG/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=DG125
#修改
SQL>alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=DG125' scope=both;
DGMGRL> show database 'DG125' StandbyArchiveLocation
StandbyArchiveLocation = '/u01/app/oracle/fast_recovery_area/DG/DG/archivelog'
DGMGRL> edit database 'DG125' set property StandbyArchiveLocation='USE_DB_RECOVERY_FILE_DEST';
#DG126
SQL> show parameter log_archive_dest_1;
log_archive_dest_1 location=/u01/app/oracle/fast_recovery_area/DG/DG/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=DG126
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=DG126' scope=both;
DGMGRL> show database 'DG126' StandbyArchiveLocation
StandbyArchiveLocation = '/u01/app/oracle/fast_recovery_area/DG/DG/archivelog'
DGMGRL> edit database 'DG126' set property StandbyArchiveLocation='USE_DB_RECOVERY_FILE_DEST';
