1：Introduction to Redo Transport Services 日志传输简介一个数据库能够传输至少30分日志到不同的目的地。每个传输目的都单独配置。每个日志传输可以通过两种方式：1、Synchronous 同步传输 事务不能提交直到所有同步传输日志传输完成。2、Asynchronous 异步传输 事务可以提交不必等所有同步传输日志传输完成。 传输目的地一般分为三种：1、本地数据库重做日志存储路径2、远程数据库归档日志存储路径3、远程数据库重做日志存储路径
2：Configuring Redo Transport Services 日志传输服务配置1、Redo transport uses Oracle Net sessions to transport redo data.传输用户采用SSL协议或者远程登陆密码文件2：采用SSL需要： 需要相同OID 保证LOG_ARCHIVE_DEST_n和FAL_SERVER 的Oracle Net connect配置符合3：采用远程登陆密码文件必须从主库复制一份密码文件到备库
3：Configuring an Oracle Database to Send Redo Data 配置主库发送日志数据LOG_ARCHIVE_DEST_n 指定本地归档日志存储路径或者指定传输日志目的地LOG_ARCHIVE_DEST_STATE_n 是和LOG_ARCHIVE_DEST_n对应的：有三个数值：ENABLE 允许发送DEFER 不允许发送ALTERNATELOG_ARCHIVE_DEST_n 有以下参数：SERVICE /LOCATION 远程/本地SYNC/ASYNC 同步/异步NET_TIMEOUT LGWR进程等待远程传输日志成功到确认的时间，数值1-1200 默认30秒，oracle建议配置AFFIRM /NOAFFIRM AFFIRM ：指定直到接收从原库传来的日志并写到备库日志中才确定。NOAFFIRM ： received redo is acknowledged（确认） without waiting for received redo to be written to the standby redo logDB_UNIQUE_NAME 必须是LOG_ARCHIVE_CONFIG中DG_CONFIG listVALID_FOR=(redo_log_type, database_role) 默认VALID_FOR=(ALL_LOGFILES, ALL_ROLES)其中：redo_log_type 有三种：ONLINE_LOGFILE 目的地可用当在线日志正在归档STANDBY_LOGFILE 目的地可用当备库在线日志正在归档ALL_LOGFILES 其中：database_role 有三种：PRIMARY_ROLE 目的地可用当数据库是主库STANDBY_ROLE 目的地可用当数据库是备库ALL_ROLESREOPEN try to reopen a failed destination的秒数 默认300秒COMPRESSION 传输日志是否压缩 默认DISABLE范例：DB_UNIQUE_NAME=orcllog_archive_dest_1='LOCATION=+ARCHDG/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_standby)' LOG_ARCHIVE_DEST_2='SERVICE=standby_orcl ARCH NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby' LOG_ARCHIVE_DEST_STATE_2='ENABLE'详细可以查看：select * from V$ARCHIVE_DEST
4： Configuring an Oracle Database to Receive Redo Data 配置备库接收日志数据1、Creating and Managing a Standby Redo Log创建和管理备库日志The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log. A standby redo log is used to store redo received from another Oracle database.备库日志用来存储接收到的日志Redo received from another Oracle database via redo transport is written to the current standby redo log group by an RFS foreground process. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an ARCn foreground process.接收到的日志通过RFS后台进程写到当前备库日志组中。当主库发生日志切换，传输过来的日志会写到备库的下一个日志组中，之前使用的备库日志组通过ARCn前台进程归档。 The process of sequentially filling and then archiving redo log file groups at a redo source database is mirrored at each redo transport destination by the sequential filling and archiving of standby redo log groups.主库会镜像日主组。 Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database. For administrative ease, Oracle recommends that all redo log files in the redo log at the redo source database and the standby redo log at a redo transport destination be of the same size.每个备库日志文件必须至少和主库日志文件相同。oracle建议两库日志组大小设置相同大小。 The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the V$LOG view to determine how many redo log groups are in the redo log at the redo source database and query the V$THREAD view to determine how many redo threads exist at the redo source database.备库每个线程的日志组必须至少表主库每个线程多一个日志组。查看v$log 和v$thread。SQL> SELECT GROUP#, BYTES FROM V$LOG; --查询备库日志SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;--查询备库日志Oracle recommends that a standby redo log be created on the primary database in a Data Guard configuration so that it is immediately ready to receive redo data following a switchover to the standby role.oracle建议在主库中创建备库日志组。备库可以立即收到在切换备库角色时。当然我们如果不切换角色，不必要再主库创建备库组日志。注意，备库日志在备库创建。 For example, assume that the redo log on the redo source database has two redo log groups and that each of those contain one 500 MB redo log file. In this case, the standby redo log should have at least 3 standby redo log groups to satisfy the requirement that a standby redo log must have at least one more redo log group than the redo log at the redo source database.主库有两个日志组，每个500M。备库至少需要三个日志组，每个500MSQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog3.rdo') SIZE 500M;If the redo source database is an Oracle Real Applications Cluster (Oracle RAC) or Oracle One Node database, query the V$LOG view at the redo source database to determine how many redo threads exist and specify the corresponding thread numbers when adding redo log groups to the standby redo log.如果主库是两节点RAC，通过查询每个线程有2个日志组，每个日志组500M。那么备库每个线程需要3个日志组。SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;注意：如果主库增加日志，备库必须也要增加日志2、Configuring Standby Redo Log ArchivalSQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG;由于备库是主库采用rman备份的，那么在备库肯定是开启归档的。log_archive_dest_1='LOCATION=+ARCHDG/orcl_standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_standby'LOG_ARCHIVE_DEST_STATE_1=ENABLEVALID_FOR建议使用STANDBY_LOGFILE,STANDBY_ROLE以下是备库ASM中的归档：2_57_896639616.dbf 这些事主库传输归档的归档日志2_58_896639616.dbf2_59_896639616.dbf2_60_896639616.dbf2_61_896639616.dbfARCHIVELOG/ 这是备库本地归档路径ASMCMD> cd ARCHIVELOGASMCMD> ls2015_11_25/2015_11_26/2015_11_27/2015_12_08/ASMCMD> pwd+archdg/orcl_standby/ARCHIVELOGASMCMD> cd 2015_12_08ASMCMD> lsthread_1_seq_79.364.897905531thread_2_seq_60.365.897905599thread_2_seq_61.366.897905601ASMCMD>
5：Cascaded Redo Transport Destinations 级联日志传输到多个终点就是说采用多层传输模式：主库--->备库---->备库的备库实验：Primary Database 主库DB_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'
6：Monitoring Redo Transport 监控日志传输1、Monitoring Redo Transport Status 监控日志传输状态Step 1 Determine the most recently archived redo log file.在主库查询最近的日志序列号在主库节点1上：SQL> show parameter instance_name NAME TYPE VALUE------------------------------------ ----------- ------------------------------instance_name string orcl1SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination +ARCHDG/orclOldest online log sequence 81Next log sequence to archive 82Current log sequence 82SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD#-------------- ---------- 81 1 62 2在主库节点2上：SQL> show parameter instance_name NAME TYPE VALUE------------------------------------ ----------- ------------------------------instance_name string orcl2SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination +ARCHDG/orclOldest online log sequence 62Next log sequence to archive 63Current log sequence 63SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD#-------------- ---------- 81 1 62 2Step 2 Determine the most recently archived redo log file at each redo transport destination. SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; 查看主库最近归档日志文件，只要有一个节点的ARCHIVED_SEQ一致说明传输称成功主库节点1的：SQL> show parameter instance_name NAME TYPE VALUE------------------------------------ ----------- ------------------------------instance_name string orcl1SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; 2 3 DESTINATION--------------------------------------------------------------------------------STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#--------- ---------------- -------------+ARCHDG/orclVALID 1 81 standby_orclVALID 2 62主库节点2的：SQL> show parameter instance_name NAME TYPE VALUE------------------------------------ ----------- ------------------------------instance_name string orcl2SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; 2 3 DESTINATION--------------------------------------------------------------------------------STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#--------- ---------------- -------------+ARCHDG/orclVALID 2 62 standby_orclVALID 1 62 只要有一个节点的ARCHIVED_SEQ一致说明传输称成功，如果想验证的话，可以去备库的归档存储路径查看主库的归档是否传输过来。
7：Redo Gap Detection and Resolution 日志断裂和解决方案日志传输断裂和解决方法1： Manual Gap Resolution例如在备库查询到以下结果：SQL> SELECT * FROM V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# ----------- ------------- -------------- 1 7 10表示 备库当前缺失 thread 1的 sequence 7 to sequence 10。这时候需要到主库查询归档：SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;/primary/thread1_dest/arcr_1_7.arc /primary/thread1_dest/arcr_1_8.arc /primary/thread1_dest/arcr_1_9.arc这时候需要手动将这些归档日志复制到physical standby database 然后使用ALTER DATABASE REGISTER LOGFILE注册到备库数据库中。SQL> ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_7.arc';SQL> ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_8.arc';SQL> ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_9.arc';以上是物理备库的解决办法，如果是逻辑备库的话：SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L WHERE NEXT_CHANGE# NOT IN - (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#) ORDER BY THREAD#, SEQUENCE#; 1 6 /disk1/oracle/dbs/log-1292880008_6.arc 1 10 /disk1/oracle/dbs/log-1292880008_10.arc使用以下方法注册：SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_7.arc'; SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_8.arc'; SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_9.arc';