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的操作就完成了。

发布了29 篇原创文章 · 获赞 9 · 访问量 8118
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览