修改dataguard主库redo组数和大小

发表于 2013 年 5 月 14 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：修改dataguard主库redo组数和大小

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在一个dg环境中，配置的是实时同步,需要增加主库的redo大小和组数,本来是一个很简单的问题,解决思路是：先备库增加standby redo删除老standby redo,然后主库增加redo删除老redo,备库增加新redo删除老redo,最后主库增加standby redo。但是在实施过程中，遇到了一些细节性的问题，主要是学习到了log_file_name_convert如果不配置,将导致备库redo 文件不能被删除

standby redo log管理
增加standby redo log

SQL> alter database add standby logfile group 8 ('/data/oradata/wasudb/st_redo08.log') size 200M; 
alter database add standby logfile group 8 ('/data/oradata/wasudb/st_redo08.log') size 200M
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 9 ('/data/oradata/wasudb/st_redo09.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 10 ('/data/oradata/wasudb/st_redo10.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 11 ('/data/oradata/wasudb/st_redo11.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 12 ('/data/oradata/wasudb/st_redo12.log') size 200M; 

Database altered.

删除standby redo log

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/data/oradata/wasudb/st_redo04.log'


SQL> alter database drop logfile group 5;;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

--在主库多次执行switch logfile

SQL>  alter database drop logfile group 4;

Database altered.

主库redo log 管理
增加redo log

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除redo log

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.
--这里涉及到多次switch logfile,需要确定redo是inactive才能够删除

备库redo log管理
增加redo log

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m; 
alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m
*
ERROR at line 1:
ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic.


SQL>  alter system set standby_file_management=manual;

System altered.

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除redo log

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL>  ALTER DATABASE CLEAR LOGFILE GROUP 1;
 ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL> show parameter NAME_CONVERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string

SQL> alter system set log_file_name_convert='/data/oradata/wasudb','/data/oradata/wasudb' scope=spfile;

System altered.

SQL>  alter system set db_file_name_convert='/data/oradata/wasudb','/data/oradata/wasudb' scope=spfile;

System altered.

--重启数据库

SQL> show parameter file_name_convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /data/oradata/wasudb, /data/or
                                                 adata/wasudb
log_file_name_convert                string      /data/oradata/wasudb, /data/or
                                                 adata/wasudb

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

主库standby redo log管理
增加standby redo

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除standby redo

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

后续工作

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

至此修改dataguard环境中的redo大小和增加redo组数的操作基本完成.在这里主要修正了自己以前对log_file_name_convert的认识，我以前以为如果我的主备库redo对应的目录一致不用配置该参数,今天通过查询MOS发现从10.2开始数据库为了能实现dg的快速切换在mrp启动的时候会去尝试清理备库redo,如果备库没有redo,或者log_file_name_convert配置不正确导致不能正常执行这个清理工作,数据库就会报ORA-19527,特别是在mrp进程启动之时.对于本次出现执行CLEAR LOGFILE命令也出现该问题,确实有点过犹不及了.这里也就是提醒我们:就算redo file,datafile主备位置相同,也建议配置log_file_name_convert和db_file_name_convert参数,提高dg健壮性.

此条目发表在 Data Guard 分类目录。将固定链接加入收藏夹。

《修改dataguard主库redo组数和大小》有 10 条评论

较新评论 →
  1. 惜分飞 说：
    2014 年 3 月 24 日11:40

    Johnny,
    我这边的库没有standby redo log

    没有使用standby redo log，那你们可能就是使用的是archivelog模式，这个可能导致主备库延迟时间比较长，切换日志后才会同步到备库

  2. Johnny 说：
    2014 年 3 月 21 日15:00

    Johnny,
    我这边的库没有standby redo log

  3. Johnny 说：
    2014 年 3 月 21 日14:14

    惜分飞,
    飞总，您好，设置standby_file_management参数后，只会影响主机新建数据文件的同步吗，是否会对主机现有表数据的同步造成影响，如果修改容灾时，主机表内有update操作，该部分是否不受之前参数的影响，谢谢指教

  4. 惜分飞 说：
    2013 年 5 月 14 日20:28

    查询standby redo log相关视图

    SQL> select group# from v$log;

    GROUP#
----------
         1
         3
         2

SQL> select group# from v$standby_log;

    GROUP#
----------
         4
         5
         6
         7


SQL>  select member from v$logfile;

MEMBER
----------------------------------------------------------
/data/oradata/wasudb/redo03.log
/data/oradata/wasudb/redo02.log
/data/oradata/wasudb/redo01.log
/data/oradata/wasudb/st_redo04.log
/data/oradata/wasudb/st_redo05.log
/data/oradata/wasudb/st_redo06.log
/data/oradata/wasudb/st_redo07.log
  5. 惜分飞 说：
    2013 年 5 月 14 日20:27

    如果不配置log_file_name_convert,启动MRP进程时出现如下警告

    Tue May 14 11:20:21 GMT+08:00 2013alter database recover managed standby database using current logfile disconnect from session
Tue May 14 11:20:21 GMT+08:00 2013Attempt to start background Managed Standby Recovery process (wasudb)
MRP0 started with pid=19, OS id=9633806
Tue May 14 11:20:21 GMT+08:00 2013MRP0: Background Managed Standby Recovery process started (wasudb)
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 7 processes
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 31233229749
Tue May 14 11:20:27 GMT+08:00 2013Waiting for all non-current ORLs to be archived...
Clearing online redo logfile 1 /data/oradata/wasudb/redo01.log
Clearing online log 1 of thread 1 sequence number 37168
Tue May 14 11:20:27 GMT+08:00 2013Errors in file /oracle/admin/wasudb/bdump/wasudb_mrp0_9633806.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 37174 (in transit)
Tue May 14 11:20:27 GMT+08:00 2013Recovery of Online Redo Log: Thread 1 Group 9 Seq 37174 Reading mem 0
  Mem# 0: /data/oradata/wasudb/st_redo09.log
Tue May 14 11:20:27 GMT+08:00 2013Completed: alter database recover managed standby database using current logfile disconnect from session
较新评论 →