oracle dataguard环境下修改主备库的standby log方法

最近在给oracle数据库搭建备库时发现主库的standby log和online redo log的大小不一致，并且使用的线程号也不一样，为了保证最佳实践，修改主备库的standby log大小。

具体的操作步骤如下：

（1）查看归档进程使用的是线程号

column destination format a50 
column process format a7
column ID format 99
column mid format 99
SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

（2） 先取备库的实时应用

alter database recover managed standby database cancel;

（3）主备库都设置standby_file_management参数为manual

alter system set standby_file_management='manual';
show parameter standby_file_management

（4）删除主库的standby log

alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

 （5）添加主库的standby redo log

alter database add  standby logfile thread 1 group 5 ('/U01/app/oracle/oradata/test/stdndbyredo05_01.log') size 50M reuse;
alter database add  standby logfile thread 1 group 6 ('/U01/app/oracle/oradata/test/stdndbyredo06_01.log') size 50M reuse;
alter database add  standby logfile thread 1 group 7 ('/U01/app/oracle/oradata/test/stdndbyredo07_01.log') size 50M reuse;

（6） 添加完成后进行确认

set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; 

（7）删除备库的standby redo log

alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

 （8）重建standby redo log

alter database add  standby logfile thread 1 group 5 ('/U01/app/oracle/oradata/test/stdndbyredo05_01.log') size 50M reuse;
alter database add  standby logfile thread 1 group 6 ('/U01/app/oracle/oradata/test/stdndbyredo06_01.log') size 50M reuse;
alter database add  standby logfile thread 1 group 7 ('/U01/app/oracle/oradata/test/stdndbyredo07_01.log') size 50M reuse;

 （9）确认是否添加成功

set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; 

（10）打开备库的实时应用，并设置主备库的standby_file_management为auto

alter database recover managed standby database using current logfile disconnect;
alter system set standby_file_management='AUTO';

 （11）查看主备库日志同步详情（可以切换几个日志然后查看下alert日志的情况）

SELECT AL.THRD "Thread",
       ALMAX   "Last Seq Received",
       LHMAX   "Last Seq Applied"
  FROM (SELECT THREAD# THRD, MAX(SEQUENCE#) ALMAX
          FROM V$ARCHIVED_LOG
         WHERE RESETLOGS_CHANGE# =
               (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
         GROUP BY THREAD#) AL,
       (SELECT THREAD# THRD, MAX(SEQUENCE#) LHMAX
          FROM V$LOG_HISTORY
         WHERE RESETLOGS_CHANGE# =
               (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
         GROUP BY THREAD#) LH
 WHERE AL.THRD = LH.THRD;

至此，主备库修改standby redo log的操作就完成了。