Oracle-Data-Guard概念与管理.doc

内部学习资料 软件安装与配置Oracle Data Guard公司：厦门智业软件工程公司作者：徐志峰创建日期：2010年9月8日修改日期：2010年2月10日版本：1.0参考文档文档控制修改记录日期作者版本2010-9-8徐志峰1.0审阅 日期姓名职位分发 拷贝姓名单位123文档控制II目标1读者11Data Guard简介11.1Data Guard 配置11.2Data Guard服务11.3Data Guard保护模式22开始Data Guard22.1物理Standby库32.2逻辑Standby库32.3Data Guard使用前提32.4Standby数据库位置与目录32.5在线redo log，归档redo log和Standby redo log43建立物理备份库54建立逻辑备份库74.1逻辑备份库支持与不支持的数据类型74.2逻辑备份库忽略的语句84.3确保表记录唯一性85日志传输服务（Log Transport Services）95.1Log Transport Services支持的几种目标类型105.2LOG_ARCHIVE_DEST_n参数配置简介105.3如何发送redo data115.3.1使用ARCn进程115.3.2使用Log Write Process（LGWR）135.3.3使用VALID_FOR决定目标角色属性145.3.4为主数据库和Standby数据库指定唯一名称145.3.5如何控制传输错误155.4设置数据保护模式155.4.1最大保护模式155.4.2最大可用模式155.4.3最大性能模式155.4.4Standby redo log文件155.4.5Data Guard数据保护模式165.5管理日志文件165.5.1管理Standby redo log 文件175.6计划控制文件的增长和重用175.7多个Standby数据库共享一个日志文件目标175.8管理归档间断175.8.1使用Fetch Archive Log（FAL）进程解决归档间断185.8.2手工解决归档间断185.9检查校验195.9.1监视日志文件归档信息195.9.2监视Log Transport Services性能196Log Apply Services206.1使用real-time Apply立刻应用redo日志206.2为归档redo log文件应用设置延时216.3应用redo到物理Standby数据库216.3.1在物理Standby数据库上监视Log Apply Services216.4应用日志到逻辑备份库226.4.1在逻辑Standby数据库上监视Log Apply Services226.5为物理Standby数据库调整Log Apply Rate237角色管理237.1角色转换简介237.1.1影响角色转换的因素237.1.2Switchovers转换247.1.3Failovers转换247.2物理Standby数据库的角色转换247.3逻辑Standby数据库的角色转换268管理物理Standby数据库278.1启动和停止物理Standby数据库278.1.1启动物理Standby数据库278.1.2停止物理Standby数据库278.2打开Standby数据库在read-only模式288.3管理主数据库影响Standby数据库的事件288.3.1增加数据文件或建立表空间288.3.2删除表空间和数据文件308.3.3在物理备份库上使用传输表空间308.3.4在数据库上重命名数据文件3083.5增加或删除在线日志文件318.3.6NOLOGGING 或Unrecoverable 操作318.4监视主数据库和Standby数据库318.4.1数据库能够监视到的事件318.4.2警告日志328.4.3动态性能视图328.4.4监视恢复进程328.4.5在物理Standby数据库上监视Log Apply服务338.5为物理备份库调整日志应用速度348.5.1设置Standby主机的并行恢复度为CPU数的两倍348.5.2为Redo Apply设置DB_BLOCK_CHECKING=FALSE348.5.3设置PARALLEL_EXECUTION_MESSAGE_SIZE = 4096348.5.4调整Disk I/O349管理逻辑Standby数据库359.1SQL Apply体系结构概况359.1.1SQL Apply的几方面考虑359.2逻辑备份库的管理与监视369.2.1DBA_LOGSTDBY_EVENTS视图：369.2.2DBA_LOGSTDBY_LOG视图3610d36- V -目标此文档包含DataGuard概念与管理读者此学习资料供以下读者使用： 智业软件1 Data Guard简介Data Guard提供企业数据的高可用性、数据保护以及灾难恢复。1.1 Data Guard 配置 Data Guard可配置成一个产品数据库多个Standby数据库，分布于不同的物理位置并使用Oracle Net互相通信。主数据库：即产品数据库，应用运行的数据库。数据库也可以是RAC数据库。Standby数据库：最多可建立个Standby数据库。Standby数据库可以是物理Standby数据库，也可以是逻辑Standby数据库。物理Standby数据库在结构上与主数据库完全一致，使用recovery redo log的方式与主数据库同步。逻辑Standby数据库保含了主数据库的逻辑信息，在物理结构上可以与主数据库不同，使用从redo log中获取的SQL语句与主数据库同步。逻辑Standby数据库可随时打开用于查询和统计。典型的Data Guard配置1.2 Data Guard服务Log Transport Service：控制redo log 自动从主数据库传输的多个归档路径。 根据配置传输redo data从主系统到Standby 系统； 管理并解决由于网络失败引起的任何间断； 执行数据库保护模式； 在Standby数据库上自动察觉丢失或是损坏的归档redo log文件，并从主数据库或是其它Standby数据库上自动重传Log Apply Services：应用redo data到Standby数据库保持与主数据库事务同步，redo data从归档redo log 文件中应用，或者，当real-time 应用使能时，可以从Standby redo log文件中应用在Standby redo log文件满时，不必等到redo data被归档。 传输的redo data被应用到Standby redo log文件中，归档应用的归档redo log文件中； 自动维护与主数据库的一致性； 允许只读访问数据； 物理备份库（REDO APPLY MRP）与逻辑备份库（SQL APPLY LSP）主要的不同就在于redo data的应用方式。Role Management Services：使用switchover或failover选项改变Standby数据库到主数据库或是主数据到Standby数据库。 switchover能在主数据库和Standby数据库之间转换； switchover保证不丢失任何数据； switchover一般做法将主数据库转换为Standby数据库，然后将Standby数据库转换为主数据库 failover用于当主数据库不可用时； failover切换是不可逆的； 正确的配置可以使failover不丢失数据。1.3 Data Guard保护模式最大保护模式：最大保护模式确保数据库失败是不丢失任何数据。在这种保护级别下，每个事务提交之前redo data必须已经同时写入本地redo log文件和至少一个redo log文件。如果写入远程Standby redo log文件失败，那么主数据库将被shutdown。最大可用模式：这种保护模式工作方式与最大保护模式相同。但是在写入远程Standby redo log失败时，主数据库不会被shutdown而是切换为最大性能模式。当写入远程Standby redo log失败被解决，并且所有间断的redo log 文件被处理后，主数据库自动转换为最大保护模式。最大性能模式：这种保护模式是默认的。2 开始Data Guard 一个Standby数据库是主数据库的一个事务一致性拷贝，它从主数据库的一个备份初始化建立。一旦Standby数据库建立和配置，Oracle自动维护Standby 数据库：传输redo data到Standby 系统，应用redo data到Standby 数据库。2.1 物理Standby库REDO APPLY：物理Standby库使用归档redo log文件或是Standby redo log文件应用redo data。当数据库正在应用时不能被打开。Open read-only：物理Standby库可以以read-only的方式打开用于查询和统计。当物理Standby打开时redo data 传输，但不应用。2.2 逻辑Standby库逻辑Standby库也是由主数据库的一个备份建立： 建立之后可以改变为不同的结构； 使用SQL语句进行更新； 可以在任何时候用户查询和报表统计2.3 Data Guard使用前提硬件和操作系统： 操作系统平台的体系结构必须相同 硬件配置不必相同 操作系统发行版本不必相同Oracle软件： Data Guard仅可用于Oracle企业版； Oracle 10.1.0.n版本之后可以使用rolling upgrade； 主数据库必须是归档模式； 主数据库和Standby数据库都可以为单实例模式或是RAC； 主数据库和Standby数据库必须有自己的控制文件； 如果主数据库和Standby数据库位于同一系统，归档路径必须不同； 为了防止未记录的直接写，在建立Standby数据库之前主数据库和Standby数据库都必须配置为force logging； 管理用户必须有sysdba权限；2.4 Standby数据库位置与目录Standby SystemDirectory StructureConsequencesSame as primary systemDifferent than primary system (required) You must set the DB_UNIQUE_NAME initialization parameter. You can either manually rename files or set up the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters on the standby database to automatically update the path names for primary database datafiles and archived redo log files and standby redo log files in the standby database control file. You must explicitly set up unique service names for the primary and standby databases with the SERVICE_NAMES initialization parameter. The standby database does not protect against disasters that destroy the system on which the primary and standby databases reside, but it does provide switchover capabilities for planned maintenance.Separate systemSame as primary system You do not need to rename primary database files, archived redo log files, and standby redo log files in the standby database control file, although you can still do so if you want a new naming scheme (for example, to spread the files among different disks). By locating the standby database on separate physical media, you safeguard the data on the primary database against disasters that destroy the primary system.Separate systemDifferent than primary system You can either manually rename files or set up the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters on the standby database to automatically rename the datafiles. By locating the standby database on separate physical media, you safeguard the data on the primary database against disasters that destroy the primary system.2.5 在线redo log，归档redo log和Standby redo log在Data Guard恢复操作中在线redo log，归档redo log和Standby redo log是最重要的结构。redo data从数据库传输并由RFS（remote file server）接受。RFS写redo data到归档日志文件或是Standby日志文件。redo data在写入归档日志文件或是Standby日志文件后被应用。当real-time 应用使能并且Standby redo log文件满时，从Standby redo log文件中应用redo data，不必等到redo data被归档。在线redo log：主数据库和逻辑Standby数据库都关联有在线redo log，物理Standby数据库没有关联在线redo log。归档redo log：主数据库，逻辑Standby数据库和物理Standby数据库都存在归档redo log。默认情况下Oracle使用ARCn进行日志归档。Standby redo log：Standby redo log和在线redo log完全类似，除了仅在Standby数据库上使用这点。用于接受主数据库传输的redo data。在下列情况下必须实现Standby redo log： 最大保护和最大可用模式； Real-time Apply； Cascade redo log 目的。3 建立物理备份库见（Data Guard服务器安装与配置10g）初始化参数简要说明：ParameterRecommended SettingDB_NAMESpecify an 8-character name. Use the same name for all standby databases.DB_UNIQUE_NAMESpecify a unique name for each database. This name stays with the database and does not change, even if the primary and standby databases reverse roles.SERVICE_NAMESSpecify a service name for this standby database that is unique from the primary database service name. If you do not explicitly specify unique service names and the primary and standby databases are located on the same system, the same default global name (consists of the database name, DB_NAME, and domain name, DB_DOMAIN, parameters) will be in effect for both databasesLOG_ARCHIVE_CONFIGSpecify the DG_CONFIG attribute on this parameter to list the DB_UNIQUE_NAME of the primary and standby databases in the Data Guard configuration; this enables the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability mode. By default, the LOG_ARCHIVE_CONFIG parameter enables the database to send and receive redo; after a role transition, you may need to specify these settings again using the SEND, NOSEND, RECEIVE, or NORECEIVE keywords.CONTROL_FILESSpecify the path name for the control files on the primary database. shows how to do this for two control files. It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control fileLOG_ARCHIVE_DEST_nSpecify where the redo data is to be archived on the primary and standby systems.Note: If a flash recovery area was configured (with the DB_RECOVERY_FILE_DEST initialization parameter) and you have not explicitly configured a local archiving destination with the LOCATION attribute Data Guard automatically uses the LOG_ARCHIVE_DEST_10 initialization parameter as the default destination for local archiving. LOG_ARCHIVE_DEST_STATE_nSpecify ENABLE to allow log transport services to transmit redo data to the specified destination.REMOTE_LOGIN_PASSWORDFILESet the same password for SYS on both the primary and standby databases. The recommended setting is either EXCLUSIVE or SHARED.LOG_ARCHIVE_FORMATSpecify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%r). FAL_SERVERSpecify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to automatically send the missing log files. FAL_CLIENTSpecify the Oracle Net service name of the Chicago database. The FAL server (Boston) copies missing archived redo log files to the Chicago standby database.DB_FILE_NAME_CONVERTSpecify the path name and filename location of the primary database datafiles followed by the standby location. This parameter converts the path names of the primary database datafiles to the standby datafile path names. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is required. Note that this parameter is used only to convert path names for physical standby databases.LOG_FILE_NAME_CONVERTSpecify the location of the primary database online redo log files followed by the standby location. This parameter converts the path names of the primary database log files to the path names on the standby database. If the standby database is on the same system as the primary database or if the directory structure where the log files are located on the standby system is different from the primary system, then this parameter is required.STANDBY_FILE_MANAGEMENTSet to AUTO so when datafiles are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.4 建立逻辑备份库见（Data Guard服务器安装与配置10g）4.1 逻辑备份库支持与不支持的数据类型支持表的数据类型和存储属性CHAR NCHAR VARCHAR2 and VARCHAR NVARCHAR2 NUMBER DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND RAW CLOB (including both fixed-width and variable-width character sets) NCLOB BLOB LONG LONG RAW BINARY_FLOAT BINARY_DOUBLE Index-organized tables (without overflows and without LOB columns)不支持表的数据类型BFILE ROWID UROWID user-defined types object types REFs varrays nested tables XMLType不支持的表，序列，视图Most schemas that ship with the Oracle database are skipped by SQL Apply Tables with unsupported datatypes Tables using table compression 详细的schema忽略信息查看dba_logstdby_skip视图详细的不被支持的对象信息查询dba_logstdby_unsupported视图4.2 逻辑备份库忽略的语句ALTER DATABASE ALTER SESSION ALTER MATERIALIZED VIEW ALTER MATERIALIZED VIEW LOG ALTER SYSTEM CREATE CONTROL FILE CREATE DATABASE CREATE DATABASE LINK CREATE PFILE FROM SPFILE CREATE SCHEMA AUTHORIZATION CREATE MATERIALIZED VIEW CREATE MATERIALIZED VIEW LOG CREATE SPFILE FROM PFILE DROP DATABASE LINK DROP MATERIALIZED VIEW DROP MATERIALIZED VIEW LOG EXPLAIN LOCK TABLE SET CONSTRAINTS SET ROLE SET TRANSACTION4.3 确保表记录唯一性Primary key Unique index通过下列查询判断SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUEWHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);DBA_LOGSTDBY_NOT_UNIQUE视图说明：The value of the BAD_COLUMN column will be either Y or N, as described in the following list:Y -Indicates a table column is defined using an unbounded datatype, such as CLOB or BLOB. SQL Apply attempts to maintain these tables, but you must ensure the application provides uniqueness in bounded columns only. Note that if two rows in the table match except for rows in the LOB column, then the table cannot be maintained properly and SQL Apply will stop.N -Indicates the table contains enough column information to maintain the table in a logical standby databaseAdd a disabled primary key rely constraint.If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database. See Oracle Database SQL Reference for ALTER TABLE statement syntax and usage information.To create a disabled RELY constraint on a primary database table, use the ALTER TABLE statement with a RELY DISABLE clause. The following example creates a disabled RELY constraint on a table named mytab where rows can be uniquely identified using the id and name columns:SQL ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;The RELY constraint tells the system to assume the rows are unique. Be careful to select columns for the disabled RELY constraint that will uniquely identify a row. If the columns selected for the RELY constraint do not uniquely identify the row, SQL Apply fails to apply data from the archived redo log file or standby redo log file to the logical standby database.To improve the performance of SQL Apply, add an index to the columns that uniquely identify the row on the logical standby database. Failure to do this results in full table scans.4 使能supplemental loggingSupplemental logging must be enabled on the primary database to support a logical standby database. Because an Oracle Database only logs the columns that were modified, this is not always suffi