New Features in Oracle Data Guard 11.21：New 11.2 Features Common to Redo Apply and SQL Apply 1： fal_client 参数被弃用 2：standby_archive_dest 参数被弃用 3：如果目的库 compression 可用，primary发送压缩日志 4：处于mount状态的primary可以执行ALTER SYSTEM FLUSH REDO，将没 有发送到standby的日志发送完成零数据丢失2：New 11.2 Features Specific to Redo Apply 1：physical standby database执行ALTER SESSION SYNC WITH PRIMARY 可以同步主库数据 2：V$STANDBY_EVENT_HISTOGRAM 查看物理备库MRP事件3：New 11.2 Features Specific to SQL Apply 1：Logical standby 支持压缩、lob字段、加密
New Features in Oracle Data Guard 11.11：New 11.1 Features Common to Redo Apply and SQL Apply 1：SYNC redo transport 参数 LOG_ARCHIVE_DEST_n 支 持NET_TIMEOUT 2：11g data guard必须确保 primary和standby 的DB_UNIQUE_NAME 不同，show parameter name 查看DB_UNIQUE_NAME 2：New 11.1 Features Specific to Redo Apply 1：hysical standby database支持read only并且不停止Redo Apply进程3：New 11.1 Features Specific to SQL Apply 1：physical standby database支持read write 并且不停止 SQL Apply
Introduction to Oracle Data Guard（包括standby类型）1：Primary Database data guard 包含一套生产数据库，其中涉及到primary database，primary database扮演primary role，上面部署了生产应用，primary database 可以是single-instance Oracle database 或者Oracle Real Application Clusters (Oracle RAC) database.2：Standby Databases standby database 内容完全复制primary database。standby database 可以是single-instance Oracle database 或者Oracle Real Application Clusters (Oracle RAC) database. standby database 类型： 1：physical standby database physical standby database和primary database 完全一致， physical standby database使用Media Recovery技术，在数据块级别上进行恢复，通过Redo Apply 接收primary database redo 并且applies the redo to the physical standby database来保持和primary database 数据同步。 physical standby database（11.1g） can receive and apply redo while it is open for read-only access. 所以physical standby database可以做灾备和查询业务。 2：Logical standby database logical standby database 数据库组织和结构可能和primary不一致，但是logical information是相同的， logical standby database 采用Logminer日志分析技术，通过SQL Apply把日志内容还原成sql语句并executes sql语句， logical standby database 可以read write. 3：Snapshot Standby Database unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is 转换成 a physical standby database, after firsates made to the snapshot standby datt discarding any local updabase
Data Guard Protection Modes--Maximum AvailabilityTransactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to the standby redo log on at least one synchronized standby database. primary database每个事务必须等待事务写入本地联机日志和standby database 日志才能提交If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.如果primary database 日志流不能同步到备库日志中，primary database 会自动切换到maximum performance mode 来保护primary database 直到故障解决This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.最大可用模式保证数据不会丢失Maximum Availability的LOG_ARCHIVE_DEST_2参数 设置log_archive_dest_2='SERVICE=备库Oracle_Net LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=备库 db_unique_name'
Data Guard Protection Modes--Maximum Protection the redo data needed to recover a transaction must be written to both the online redo log and to the standby redo log on at least one synchronized standby database before the transaction commits. primary database每个事务必须等待事务写入本地联机日志和standby database 日志才能提交To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.如果primary database 日志流不能同步到备库日志中，primary database 直接自动关闭Transactions on the primary are considered protected as soon as Data Guard has written the redo data to persistent storage in a standby redo log file. Once that is done, acknowledgment is quickly made back to the primary database so that it can proceed to the next transaction. This minimizes the impact of synchronous transport on primary database throughput and response time. To fully benefit from complete Data Guard validation at the standby database, be sure to operate in real-time apply mode so that redo changes are applied to the standby database as fast as they are received. Data Guard signals any corruptions that are detected so that immediate corrective action can be taken.Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.建议至少配置两个备库来确保日志同步失败导致primary database 自动关闭照成的麻烦Maximum Protection的LOG_ARCHIVE_DEST_2参数 设置log_archive_dest_2='SERVICE=备库Oracle_Net LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=备库 db_unique_name'
Data Guard Protection Modes--Maximum PerformanceThis is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. primary database事务只需要写入背地联机日志中就可提交Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).Primary Database的Redo流可以异步的发送到Standby Database，所以性能不受standby database影响This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.This is the default protection mode.如果primary database 发生故障，联机日志中的数据可能会丢失Maximum Protection的LOG_ARCHIVE_DEST_2参数 设置log_archive_dest_2='SERVICE=备库Oracle_Net LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=备库 db_unique_name'或者采用 arch进程 发送日志log_archive_dest_2='SERVICE=备库Oracle_Net VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=备库 db_unique_name'
Data Guard Protection Modes4、5、6是data guard三种保护模式1：主库查看保护模式SELECT PROTECTION_MODE FROM V$DATABASE; 2：主库设置模式（默认为Maximum Performance）SQL> ALTER DATABASE - > SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION}; 3：模式中涉及到的参数设置ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(主库db_unique_name,备库 db_unique_name)';
Redo Transport Services1：Introduction to Redo Transport Services 1：日志接收目的地包括 standby database Archived Log srepository（备库归档日志库） standby database redo logs备（备库redo日志） 2：日志传送方式有两种 Synchronous（主库事务必须等待该事务所有日志传输到备库才能够提交，Maximum Protection和Maximum Availability 就是采用该模式） Asynchronous（主库事务不必等待该事务所有日志传输到备库就可以提交，Maximum Performance 采用这种模式）2：Configuring Redo Transport Services 1：Redo Transport Security（日志传输完整性） 日志通过Oracle Net sessions 来传输日志，而这些会话会自动使用SSL 协议或者 远程登录密码文件来传输。 数据库的 domain都相同并且配置了LOG_ARCHIVE_DEST_n、FAL_SERVER这两个参数就会自动采用 SSL协议来传输，如果以上条件不符，就会采用远程登录密码文件来传输。采用远程登录密码文件传输时，physical 和snapshot standby database 必须复制primary database 的密码文件 2：Configuring an Oracle Database to Send Redo Data DB_UNIQUE_NAME=BOSTON (主库和备库都要DB_UNIQUE_NAME，且必须不相同) LOG_ARCHIVE_CONFIG='DG_CONFIG=(BOSTON,CHICAGO,HARTFORD)' LOG_ARCHIVE_DEST_STATE_2='ENABLE' -------------------->表示LOG_ARCHIVE_DEST_2启用 LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE) REOPEN=60 COMPRESSION=ENABLE DB_UNIQUE_NAME=CHICAGO'--------------------->SERVICE表示日志传输到ORACLE Net为CHICAGO的目标库；传输方式为ASYNC NOAFFIRM ,其中ASYNC表示异步，OAFFIRM为日志传输到目标库后不必等待所有信息写入到standby redo log ;确保目标库唯一，加上DB_UNIQUE_NAME，表示目标库的db_unique_name为CHICAGO LOG_ARCHIVE_DEST_STATE_3='ENABLE' --------------------->表示LOG_ARCHIVE_DEST_3启用 LOG_ARCHIVE_DEST_3='SERVICE=HARTFORD SYNC AFFIRM NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) REOPEN=60 COMPRESSION=ENABLE DB_UNIQUE_NAME=HARTFORD'--------------------->SERVICE表示日志传输到ORACLE Net为CHICAGO的目标库；传输方式为SYNC AFFIRM ,其中SYNC 表示同步，AFFIRM 为日志传输到目标库后必须等待所有信息写入到standby redo log ，NET_TIMEOUT表示LGWR process 多久接收同步日志，单位是秒，同步模式建议使用这个参数;确保目标库唯一，加上DB_UNIQUE_NAME，表示目标库的db_unique_name为HARTFORD V$ARCHIVE_DEST 可用来查询 3：Configuring an Oracle Database to Receive Redo Data 如果是lgwr发送过来的日志，没有必要建standby redo log ，备库online log 如果采用lgwr发送日志，就需要创建standby redo log，涉及到的视图（ v$log v$logfile v$thread V$STANDBY_LOG） primary database lgwr 传输过来的日志通过备库的前台进程RFS当前备库standby redo log中，当primary database 发生日志切换，传送过来的日志写入到备库当前的standby redo log中，而备库之前使用的redo log会归档到本地 Oracle建议 主库和备库所有standby redo log文件的大小设置相同 如果主库是2个节点的rac，一个实例一个thread ，每个实例有2个日志组，那么备库是单实例，就需要创建2个thread，每个thread 3个日志组（多余的一个thread 是之后的切换）SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 50M;3：Cascaded Redo Transport Destinations 采用Standby Redo Log 归档配置 1：Standby Redo Log Archival to a fast recovery area LOG_ARCHIVE_DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)' LOG_ARCHIVE_DEST_STATE_2=ENABLE 2：Standby Redo Log Archival to a Local FIle System LocationLOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)' LOG_ARCHIVE_DEST_STATE_2=ENABLE 3：Cases Where Redo Is Written Directly To an Archived Redo Log FileRedo received by a standby database is written directly to an archived redo log file if a standby redo log group is not available or if the redo was sent to resolve a redo gap.如果standby redo log组不可用或者日志发送缺失，接收过来的日志直接归档 When this occurs, redo is written to the location specified by the LOCATION attribute of one LOG_ARCHIVE_DEST_n parameter that is valid for archiving redo received from another database. 4：Monitoring Redo Transport Services 传输服务相关参数Primary DatabaseDB_UNIQUE_NAME=boston FAL_SERVER=boston2 LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,boston2,denver)' LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston' LOG_ARCHIVE_DEST_2='SERVICE=boston2 SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston2'Cascading Physical Standby Database 考虑主备库切换DB_UNIQUE_NAME=boston2 FAL_SERVER=boston LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(boston,boston2,denver)' LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston2' LOG_ARCHIVE_DEST_2= 'SERVICE=denver VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver'Cascaded Physical Standby Database 考虑主备库切换DB_UNIQUE_NAME=denver FAL_SERVER=boston2 LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,boston2,denver)' LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=denver'5：Tuning Redo TransportSELECT * FROM V$ARCHIVE_GAP; 查看是否有裂痕裂痕涉及到的参数LOG_ARCHIVE_DEST_n （COMPRESSION MAX_CONNECTIONS ）
Apply Services1：Introduction to Apply Servicesapply services waits for a standby redo log file to be archived before applying the redo that it contains. However, you can enable real-time apply, which allows apply services to apply the redo in the current standby redo log file as it is being filled. apply services 必须等待 standby redo log file归档后才能将 standby redo log中的内容重演成数据到备库，但是可以通过时时应用允许apply services在当前日志组中standby redo log file写满就可以直接重演数据到备库。2：Apply Services Configuration Options 1：Using Real-Time Apply to Apply Redo Data ImmediatelyIf the real-time apply feature is enabled, apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.Use the ALTER DATABASE statement to enable the real-time apply feature, as follows如果备库real-time apply开启， apply services无需等待standby redo log归档就能apply redo data，这个常用在switchover 和failover 中时时应用设置：物理备库通过ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE 设置逻辑备库通过ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE 设置Real-time apply requires a standby database that is configured with a standby redo log and that is in ARCHIVELOG mode.Real-time apply需要备库配置standby redo log并且备库归档开启如果时时应用设置之后还是出现延时：物理备库通过ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;设置逻辑备库通过ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;设置3：Applying Redo Data to Physical Standby DatabasesBy default, the redo data is applied from archived redo log files. When performing Redo Apply, a physical standby database can use the real-time apply feature to apply redo directly from the standby redo log files as they are being written by the RFS process日志数据重演默认使用归档日志，如果设置了real-time apply，可以直接将RFS写入到standby redo log files 的日志来重演日志1：Starting Redo ApplyTo start Redo Apply in the foreground, issue the following SQL statement:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;2：Stopping Redo ApplyTo stop Redo Apply, issue the following SQL statement:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;4：Applying Redo Data to Logical Standby DatabasesSQL Apply converts the data from the archived redo log or standby redo log into SQL statements and then executes these SQL statements on the logical standby database. Because the logical standby database remains open, tables that are maintained can be used simultaneously for other tasks such as reporting, summations, and queries.SQL Apply通过archived redo log或者standby redo log转换成sql语句在logical standby database执行1：Starting SQL Apply To start SQL Apply, start the logical standby database and issue the following statement:SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;To start real-time apply on the logical standby database to immediately apply redo data from the standby redo log files on the logical standby database, include the IMMEDIATE keyword as shown in the following statement:SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;2：Stopping SQL Apply on a Logical Standby DatabaseSQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;