使用DGMGRL管理DataGuard.doc

使用DGMGRL管理DataGuard1. 使用DGMGRL管理DataGuard的前提条件有1) Primary DB和Standby DB必须事先已建好2) Primary DB和Standby DB都是用spfile文件这两个条件都满足，下面开始对一个手工搭建的Physical DataGuard环境使用DGMGRL管理。目前需要管理的DataGuard环境在同一个Linux Server上，Primary DB的DB_UNIQUEUE_NAME为pri，Standby的DB_UNIQUEUE_NAME为stdby。2. Pirmary DB 和 Standby DB上设置参数DG_BROKER_START为TRUE。可以观察到后台进程dmon的启动。PRI show parameter instance_nameNAME TYPE VALUE- - -instance_name string orclPRI show parameter db_unique_nameNAME TYPE VALUE- - -db_unique_name string priPRI show parameter dg_broker_startNAME TYPE VALUE- - -dg_broker_start boolean FALSEPRI !ps -ef|grep dmonoracle 1600 1476 0 23:06 pts/2 00:00:00 /bin/bash -c ps -ef|grep dmonoracle 1602 1600 0 23:06 pts/2 00:00:00 grep dmonPRI alter system set dg_broker_start=true;System altered.PRI !ps -ef|grep dmonoracle 1604 1 0 23:06 ? 00:00:00 ora_dmon_orcloracle 1611 1476 0 23:07 pts/2 00:00:00 /bin/bash -c ps -ef|grep dmonoracle 1613 1611 0 23:07 pts/2 00:00:00 grep dmonPRISTD show parameter instance_nameNAME TYPE VALUE- - -instance_name string stdbySTD show parameter db_uniNAME TYPE VALUE- - -db_unique_name string stdbySTD show parameter spfileNAME TYPE VALUE- - -spfile string /u01/app/oracle/product/10.2.0 /db_1/dbs/spfilestdby.oraSTD show parameter dg_broker_startNAME TYPE VALUE- - -dg_broker_start boolean FALSESTD !ps -ef|grep dmonoracle 1604 1 0 23:06 ? 00:00:00 ora_dmon_orcloracle 1614 1481 0 23:09 pts/1 00:00:00 /bin/bash -c ps -ef|grep dmonoracle 1616 1614 0 23:09 pts/1 00:00:00 grep dmonSTD alter system set dg_broker_start=true;System altered.STD !ps -ef|grep dmonoracle 1604 1 0 23:06 ? 00:00:00 ora_dmon_orcloracle 1621 1 11 23:10 ? 00:00:00 ora_dmon_stdbyoracle 1622 1481 0 23:10 pts/1 00:00:00 /bin/bash -c ps -ef|grep dmonoracle 1624 1622 0 23:10 pts/1 00:00:00 grep dmonSTD3. 创建一个Configuration。oraclebs1 oracle$ dgmgrlDGMGRL for Linux: Version .0 - ProductionCopyright (c) 2000, 2005, Oracle. All rights reserved.Welcome to DGMGRL, type help for information.DGMGRL connect sys/oraclepriConnected.DGMGRL CREATE CONFIGURATION DR1 AS primary database is pri connect identifier is pri;Configuration DR1 created with primary database priDGMGRL helpThe following commands are available:add Add a standby database to the broker configurationconnect Connect to an Oracle instancecreate Create a broker configurationdisable Disable a configuration, a database, or Fast-Start Failoveredit Edit a configuration, database, or instanceenable Enable a configuration, a database, or Fast-Start Failoverexit Exit the programfailover Change a standby database to be the primary databasehelp Display description and syntax for a commandquit Exit the programreinstate Change a disabled database into a viable standby databaserem Comment to be ignored by DGMGRLremove Remove a configuration, database, or instanceshow Display information about a configuration, database, or instanceshutdown Shutdown a currently running Oracle instancestart Start Fast-Start Failover observerstartup Start an Oracle database instancestop Stop Fast-Start Failover observerswitchover Switch roles between the primary database and a standby databaseUse help to see syntax for individual commandsDGMGRL SHOW CONFIGURATION;Configuration Name: DR1 Enabled: NO Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: pri - Primary databaseCurrent status for DR1:DISABLEDDGMGRL add database stdby as connect identifier is stdby maintained as physical;Database stdby addedDGMGRL SHOW CONFIGURATION;Configuration Name: DR1 Enabled: NO Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: pri - Primary database stdby - Physical standby databaseCurrent status for DR1:DISABLEDDGMGRL4. 通过DGMGRL改变数据库属性如查询 pri 有关DataGuard的属性DGMGRL show database pri verbose;Error: ORA-16606: unable to find property verboseError: ORA-16501: the Data Guard broker operation failedDGMGRL show database verbose pri;Database Name: pri Role: PRIMARY Enabled: NO Intended State: OFFLINE Instance(s): orcl Properties: InitialConnectIdentifier = pri 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 = 5 LogArchiveMinSucceedDest = 1 DbFileNameConvert = /u01/app/oracle/oradata/stdby/, /u01/app/oracle/oradata/orcl/ LogFileNameConvert = /u01/app/oracle/oradata/stdby/, /u01/app/oracle/oradata/orcl/ FastStartFailoverTarget = StatusReport = (monitor) InconsistentProperties = (monitor) InconsistentLogXptProps = (monitor) SendQEntries = (monitor) LogXptStatus = (monitor) RecvQEntries = (monitor) HostName = bs1 SidName = orcl LocalListenerAddress = (ADDRESS=(PROTOCOL=tcp)(HOST=bs1)(PORT=1521) StandbyArchiveLocation = /home/oracle/arch1/pri/ AlternateLocation = LogArchiveTrace = 0 LogArchiveFormat = %d_%t_%s_%r.arc LatestLog = (monitor) TopWaitEvents = (monitor)Current status for pri:DISABLED从SQLPlus中也可以查到Primary DB的归档日志格式设置PRI show parameter log_archive_formatNAME TYPE VALUE- - -log_archive_format string %d_%t_%s_%r.arc通过DGMGRL改变归档日志格式为 log_%t_%s_%r_%d.arcDGMGRL edit database pri set property LogArchiveFormat=log_%t_%s_%r_%d.arc;Property logarchiveformat updatedDGMGRL show database verbose pri;Database Name: pri Role: PRIMARY Enabled: NO Intended State: OFFLINE Instance(s): orcl Properties: InitialConnectIdentifier = pri 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 = 5 LogArchiveMinSucceedDest = 1 DbFileNameConvert = /u01/app/oracle/oradata/stdby/, /u01/app/oracle/oradata/orcl/ LogFileNameConvert = /u01/app/oracle/oradata/stdby/, /u01/app/oracle/oradata/orcl/ FastStartFailoverTarget = StatusReport = (monitor) InconsistentProperties = (monitor) InconsistentLogXptProps = (monitor) SendQEntries = (monitor) LogXptStatus = (monitor) RecvQEntries = (monitor) HostName = bs1 SidName = orcl LocalListenerAddress = (ADDRESS=(PROTOCOL=tcp)(HOST=bs1)(PORT=1521) StandbyArchiveLocation = /home/oracle/arch1/pri/ AlternateLocation = LogArchiveTrace = 0 LogArchiveFormat = log_%t_%s_%r_%d.arc LatestLog = (monitor) TopWaitEvents = (monitor)Current status for pri:DISABLEDDGMGRL此时，通过SQLPlus观察 Log_archive_format参数为PRI show parameter log_archive_formatNAME TYPE VALUE- - -log_archive_format string %d_%t_%s_%r.arc可以看出，DGMGRL的修改还没有同步到数据库中去，原因是DGMGRL创建的configuration还没有enable。5. 将configuration设置为enable状态如果希望通过DGMGRL来管理DataGuard，则需要启用configuration。现在启用configuation：DGMGRL show configuration;Configuration Name: DR1 Enabled: NO Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: pri - Primary database stdby - Physical standby databaseCurrent status for DR1:DISABLEDDGMGRL show database pri;Database Name: pri Role: PRIMARY Enabled: NO Intended State: OFFLINE Instance(s): orclCurrent status for pri:DISABLEDDGMGRL show database stdby;Database Name: stdby Role: PHYSICAL STANDBY Enabled: NO Intended State: OFFLINE Instance(s): stdbyCurrent status for stdby:DISABLEDDGMGRL enable configuration;Enabled.DGMGRL show configuration;Configuration Name: DR1 Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: pri - Primary database stdby - Physical standby databaseCurrent status for DR1:Warning: ORA-16608: one or more databases have warningsDGMGRL show database pri;Database Name: pri Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): orclCurrent status for pri:Warning: ORA-16792: configuration property value is inconsistent with database settingDGMGRL show database verbose pri;Database Name: pri Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): orcl Properties: InitialConnectIdentifier = pri 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 = 5 LogArchiveMinSucceedDest = 1 DbFileNameConvert = /u01/app/oracle/oradata/stdby/, /u01/app/oracle/oradata/orcl/ LogFileNameConvert = /u01/app/oracle/oradata/stdby/, /u01/app/oracle/oradata/orcl/ FastStartFailoverTarget = StatusReport = (monitor) InconsistentProperties = (monitor) InconsistentLogXptProps = (monitor) SendQEntries = (monitor) LogXptStatus = (monitor) RecvQEntries = (monitor) HostName = bs1 SidName = orcl LocalListenerAddress = (ADDRESS=(PROTOCOL=tcp)(HOST=bs1)(PORT=1521) StandbyArchiveLocation = /home/oracle/arch1/pri/ AlternateLocation = LogArchiveTrace = 0 LogArchiveFormat = log_%t_%s_%r_%d.arc LatestLog = (monitor) TopWaitEvents = (monitor)Current status for pri:Warning: ORA-16792: configuration property value is inconsistent with database settingDGMGRL show database stdby;Database Name: stdby Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): stdbyCurrent status for stdby:SUCCESSDGMGRL此时观察Primary DB的归档日志格式：PRI show parameter log_archive_formatNAME TYPE VALUE- - -log_archive_format string %d_%t_%s_%r.arcPRI通过DGMGRL再重新同步设置属性DGMGRL edit database pri set property LogArchiveFormat=log_%t_%s_%r.arc;Property logarchiveformat updatedDGMGRL此时观察Primary DB的归档日志格式，发现仍然没有更改：PRI show parameter log_archive_formatNAME TYPE VALUE- - -log_archive_format string %d_%t_%s_%r.arcPRI重启Primary DB后，发现同步成功：PRI show parameter log_archive_formatNAME TYPE VALUE- - -log_archive_format string log_%t_%s_%r.arc造成这种情况的原因为 log_archive_format 参数为静态参数，数据库启动阶段不能动态修改。而对于数据库的动态参数，用DGMGRL修改后会立即反映出来。如下所示：PRI show parameter max_proNAME TYPE VALUE- - -log_archive_max_processes integer 5PRI show parameter max_pro。DGMGRL edit database pri set property LogArchiveMaxProcesses=6;Property logarchivemaxprocesses updatedDGMGRL。NAME TYPE VALUE- - -log_archive_max_processes integer 6PRI6. 通过DGMGRL改变Standby DB的状态 （在read only模式和recovery mode之间转换）。以下查询说明Standby DB处于 recovery modeSTD SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKSfrom V$MANAGED_STANDBY ; 2PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS- - - - - -ARCH CONNECTED 0 0 0 0ARCH CONNECTED 0 0 0 0ARCH CONNECTED 0 0 0 0ARCH CONNECTED 0 0 0 0ARCH CONNECTED 0 0 0 0MRP0 WAIT_FOR_LOG 1 90 0 0RFS IDLE 0 0 0 0RFS IDLE 0 0 0 08 rows selected.STD select name,paddr from v$bgprocess where name like MRP%;NAME PADDR- -MRP0 29E1A138STD select database_role,open_mode from v$database;DATABASE_ROLE OPEN_MODE- -PHYSICAL STANDBY MOUNTEDSTD将Standby DB切换到 read only 模式下DGMGRL show configuration;Configuration Name: DR1 Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: pri - Primary database stdby - Physical standby databaseCurrent status for DR1:SUCCESSDGMGRL show database stdby;Database Name: stdby Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): stdbyCurrent status for stdby:SUCCESSDGMGRL EDIT DATABASE stdby SET STATE=READ-ONLY;Succeeded.DGMGRL show database stdby;Database Name: stdby Role: PHYSICAL STANDBY Enabled: YES Intended State: READ-