#ADG環(huán)境 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
#?？焖俟收限D(zhuǎn)移功能
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
#啟動快速故障轉(zhuǎn)移功能
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.查看數(shù)據(jù)庫或者實例?
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.數(shù)據(jù)庫監(jiān)控狀態(tài)查看
broker會自動的收集同一個配置之下的所有數(shù)據(jù)庫的健康狀態(tài)，管理員只需要通過簡單的show命令就可以查看數(shù)據(jù)庫的狀態(tài)了。命令格式如下
show database db_unique_name statue_name可用的狀態(tài)命令列表如下：
StatusReport? 顯示所有broker檢查到的問題
LogXptStatus? 顯示日志傳送的狀態(tài)
InconsistentProperties? 顯示不一致的數(shù)據(jù)庫屬性
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的時候，參數(shù)（備庫）LogXptMode設置為async/sync
c.使用Maximum protection、Maximum availability的時候，參數(shù)（備庫）LogXptMode要設置為sync。
不同的保護模式對broker操作的影響
1). 當升級保護模式的時候，broker會自動的重啟primary數(shù)據(jù)庫。降低保護模式級別的時候是不需要重啟數(shù)據(jù)庫的。(11g、12c更改保護模式都不用重啟數(shù)據(jù)庫更改保護模式都不用重啟數(shù)據(jù)庫)
2). switchover操作不會改變當前的保護模式。
3). 做手工的failover之后，如果原來保護模式是MaxProtection的話會被自動降級為MaxPerformance；如果是其他模式的話則保持不變。
4). fast-start failover所作的自動的failover操作不會改變數(shù)據(jù)庫的保護模式。
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
#設置備庫數(shù)據(jù)庫只讀。?
------------------------------
#停止主庫到從庫的日志傳送 （最大性能模式下支持）
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.備庫狀態(tài)管理
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;
--發(fā)現(xiàn)備庫狀態(tài)為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)
---------------------------------------------------
--發(fā)現(xiàn)狀備庫態(tài)為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狀態(tài)
SQL> startup mount;
--發(fā)現(xiàn)備庫已經(jīng)正常
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)
--這時可以發(fā)現(xiàn)備庫已經(jīng)自動應用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之前的版本。物理備庫處于日志應用狀態(tài)時，是無法從備庫讀取數(shù)據(jù)的。如果想開庫，需停止日志應用，備庫可以開到read only狀態(tài)。如果物理備庫從read only狀態(tài)回到日志應用狀態(tài)，要先關掉物理備庫，再將庫啟到mount狀態(tài)，最后重新應用日志。
這樣要從備庫讀數(shù)據(jù)，日志應用就必須停掉。無法實現(xiàn)邊應用日志、邊讀取數(shù)據(jù)。11g 可以使用active standby，實現(xiàn)日志應用和查詢同時進行。即Real-Time Apply + Real-Time Query.
--直接打開備庫，成功
SQL> alter database open;
--此時發(fā)現(xiàn) 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后，可以發(fā)現(xiàn)redo日志一樣在傳輸
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
? 10.配置及啟用Fast-Start Failover
1.配置每個數(shù)據(jù)庫Failover的目標，這一步是決定當數(shù)據(jù)庫出問題之后會自動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壞了多長時間之后會執(zhí)行自動的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';
