Oracle standby redo log配置及注意事项

1.关于standby redo log配置

Standby Log用于最大保护和最高可用模式,Lgwr async模式就是推荐的方式：
 

/*
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.
You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
*/

2.添加standby log及注意事项:

1)确保主备logfile大小一致
--Ensure log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. 
For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also 
have log file sizes of 200K.

2)估算备库standby log数量
备库中的standby log数量预估:
       standby logs=(max(log members +1) + max (threads)

/*
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads
Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.
Note:
Logical standby databases may require more standby redo log files (or additional ARCn processes) depending on the workload. This is because logical standby databases also write to online redo log files, which take precedence over standby redo log files. Thus, the standby redo log files may not be archived as quickly as the online redo log files. Also, see Section 5.7.3.1.
*/

3)检查数据库参数设置
检查主备相关的参数限制,

-MAXLOGFILES
-MAXLOGMEMBERS
/*
Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.
See Oracle Database SQL Reference and your operating system specific Oracle documentation for the default and legal values of the MAXLOGFILES and MAXLOGMEMBERS clauses.
*/

4)创建standby redo log组

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
  2> ('/oradata/orcl/log1c.rdo','/oradata/orcl/log2c.rdo') SIZE 500M;


5)验证standby log组创建完成  

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP#     THREAD#    SEQUENCE#  ARC STATUS     
---------- ---------- ---------- --- ----------
         3          1         16 NO  ACTIVE    
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED

3.DG主备查询

--查看standby 进程状态
select process,client_process,status,thread#,group#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#;

--查看MRP0进程信息
select process,client_process,status,thread#,group#,sequence#,block#,blocks,delay_mins from v$managed_standby where process='MRP0';


--根据thread号查看哪些日志还没应用

select sequence#,first_time,next_time,applied  from v$archived_log where thread#=3;


--日志gap查询
select * from v$archive_gap;


--查看dataguard状态信息
select * from  v$dataguard_stats