近期需要增大生产的联机日志的大小,为防止在生产库出现纰漏,做以下储备
一、redo的设计原则
1、redo的大小
redo的大小主要取决于业务量的大小,即数据库中DML的多少,一般是10~15分钟切换一次最为合适
2、redo的位置
在数据库中一般默认设置3组以上的日志且在同一存储上,若条件允许可以实现:
将每一组日志设置2个member成员,为避免因为存储坏掉而丢失数据,将成员存放在不同的存储上,或至少在同一存储的不同RAID上;
二、redo的操作
在redo的修改中,需要注意几个参数的设置
DB_CREATE_ONLINE_LOG_DEST_n 若设置该参数,在创建日志的时候可以不指定日志的地址,若没有设置DB_CREATE_ONLINE_LOG_DEST_n,需要检查DB_CREATE_FILE_DEST和DB_RECOVERY_FILE_DEST,效果和DB_CREATE_ONLINE_LOG_DEST_n一致
三、redo的管理(摘自官档)
Adding Redo Log File Groups: Examples The following statement adds a redo log file group with two members and identifies it with a GROUP parameter value of 3:
ALTER DATABASE
ADD LOGFILE GROUP 3
('diska:log3.log' ,
'diskb:log3.log') SIZE 50K;
The following statement adds a redo log file group containing two members to thread 5 (in a Real Application Clusters environment) and assigns it a GROUP parameter value of 4:
ALTER DATABASE
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log',
'diskb:log4:log');
Adding Redo Log File Group Members: Example The following statement adds a member to the redo log file group added in the previous example:
ALTER DATABASE
ADD LOGFILE MEMBER 'diskc:log3.log'
TO GROUP 3;
Dropping Log File Members: Example The following statement drops one redo log file member added in the previous example:
ALTER DATABASE
DROP LOGFILE MEMBER 'diskb:log3.log';
The following statement drops all members of the redo log file group 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
Renaming a Log File Member: Example The following statement renames a redo log file member:
ALTER DATABASE
RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';
The preceding statement only changes the member of the redo log group from one file to another. The statement does not actually change the name of the file diskc:log3.log to diskb:log3.log. Before issuing this statement, you must change the name of the file through your operating system.
Clearing a Log File: Example The following statement clears a log file:
ALTER DATABASE
CLEAR LOGFILE 'diskc:log3.log';
2、RAC的redo
必须声明thread,详见官档
3、DG的redo
alter database add standby logfile group n '' size 1000M;
注意细节,摘自惜分飞http://www.xifenfei.com/4388.html
在DG的环境中,配置的是实时同步,需要增大主库的redo大小和数量,本来很简单的一件事情:
先增大备库的standby redo,删除老的standby redo,然后主库增加redo删除老的redo,备库增加新redo删除老redo,最后主库增加standby redo。
但实施的过程中,遇到了一些细节性的问题,主要是学习到了log_file_name_convert如果不配置,将导致备库redo文件不能删除。
增加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健壮性.
如果在RAC中出现
SQL> alter database mount;
ERROR at line 1:
ORA-01618: redo thread 3 is not enabled - cannot mount
则说明thread 3 的redo log没有启用 可以通过下面的方式启用
alter database enable thread 3;
本次实施步骤
查看当前日志文件组的状态
SQL> select GROUP#,MEMBER,STATUS from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATADG/czyjdb/onlinelog/group1_1
1 +REDODG/czyjdb/onlinelog/group1_2
2 +DATADG/czyjdb/onlinelog/group2_1
2 +REDODG/czyjdb/onlinelog/group2_2
5 +DATADG/czyjdb/onlinelog/group5_1
5 +REDODG/czyjdb/onlinelog/group5_2
6 +DATADG/czyjdb/onlinelog/group6_1
6 +REDODG/czyjdb/onlinelog/group6_2
7 +DATADG/czyjdb/onlinelog/group7_1
7 +REDODG/czyjdb/onlinelog/group7_2
8 +DATADG/czyjdb/onlinelog/group8_1
8 +REDODG/czyjdb/onlinelog/group8_2
3 +DATADG/czyjdb/onlinelog/group3_1
3 +REDODG/czyjdb/onlinelog/group3_2
4 +DATADG/czyjdb/onlinelog/group4_1
4 +REDODG/czyjdb/onlinelog/group4_2
9 +DATADG/czyjdb/onlinelog/group9_1
9 +REDODG/czyjdb/onlinelog/group9_2
10 +DATADG/czyjdb/onlinelog/group10_1
10 +REDODG/czyjdb/onlinelog/group10_2
11 +DATADG/czyjdb/onlinelog/group11_1
11 +REDODG/czyjdb/onlinelog/group11_2
12 +DATADG/czyjdb/onlinelog/group12_1
12 +REDODG/czyjdb/onlinelog/group12_2
13 +DATADG/czyjdb/onlinelog/group13_1
13 +REDODG/czyjdb/onlinelog/group13_2
14 +DATADG/czyjdb/onlinelog/group14_1
14 +REDODG/czyjdb/onlinelog/group14_2
15 +DATADG/czyjdb/onlinelog/group15_1
15 +REDODG/czyjdb/onlinelog/group15_2
16 +DATADG/czyjdb/onlinelog/group16_1
16 +REDODG/czyjdb/onlinelog/group16_2
17 +DATADG/czyjdb/onlinelog/group17_1
17 +REDODG/czyjdb/onlinelog/group17_2
18 +DATADG/czyjdb/onlinelog/group18_1
18 +REDODG/czyjdb/onlinelog/group18_2
19 +DATADG/czyjdb/onlinelog/group19_1
19 +REDODG/czyjdb/onlinelog/group19_2
20 +DATADG/czyjdb/onlinelog/group20_1
20 +REDODG/czyjdb/onlinelog/group20_2
21 +DATADG/czyjdb/onlinelog/group21_1
21 +REDODG/czyjdb/onlinelog/group21_2
22 +DATADG/czyjdb/onlinelog/group22_1
22 +REDODG/czyjdb/onlinelog/group22_2
23 +DATADG/czyjdb/onlinelog/group23_1
23 +REDODG/czyjdb/onlinelog/group23_2
24 +DATADG/czyjdb/onlinelog/group24_1
24 +REDODG/czyjdb/onlinelog/group24_2
25 +DATADG/czyjdb/onlinelog/group25_1
25 +REDODG/czyjdb/onlinelog/group25_2
26 +DATADG/czyjdb/onlinelog/group26_1
26 +REDODG/czyjdb/onlinelog/group26_2
27 +DATADG/czyjdb/onlinelog/group27_1
27 +REDODG/czyjdb/onlinelog/group27_2
28 +DATADG/czyjdb/onlinelog/group28_1
28 +REDODG/czyjdb/onlinelog/group28_2
29 +DATADG/czyjdb/onlinelog/group29_1
29 +REDODG/czyjdb/onlinelog/group29_2
30 +DATADG/czyjdb/onlinelog/group30_1
30 +REDODG/czyjdb/onlinelog/group30_2
31 +DATADG/czyjdb/onlinelog/group31_1
31 +REDODG/czyjdb/onlinelog/group31_2
32 +DATADG/czyjdb/onlinelog/group32_1
32 +REDODG/czyjdb/onlinelog/group32_2
1、创建2个新的日志组
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 33 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 34 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 35 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 36 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 37 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 38 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 39 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 40 ('+DATADG','+REDODG') size 1024M;
2、切换当前日志到新的日志组
不要使用alter system switch logfile;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
3、删除旧的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
alter database drop logfile group 30
alter database drop logfile group 31;
alter database drop logfile group 32;
4、操作系统下删除原日志组中的物理文件
su - grid
asmcmd
cd +REDODG/CZYJDB/ONLINELOG
rm -rf group1*
rm -rf group2*
rm -rf group_1*
rm -rf group_2*
rm -rf group30*
rm -rf group31*
rm -rf group32*
rm -rf group_30*
rm -rf group_31*
rm -rf group_32*
cd +DATADG/CZYJDB/ONLINELOG
rm -rf group1*
rm -rf group2*
rm -rf group_1*
rm -rf group_2*
rm -rf group30*
rm -rf group31*
rm -rf group32*
rm -rf group_30*
rm -rf group_31*
rm -rf group_32*
5、重建日志组1、2、3
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 2 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 3 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 4 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 7 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 8 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 9 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 11 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 12 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 13 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 14 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 15 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 16 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 17 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 18 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 19 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 20 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 21 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 22 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 23 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 24 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 25 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 26 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 27 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 28 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 29 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 30 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 31 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 32 ('+DATADG','+REDODG') size 1024M;
6、切换日志组
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
7、删除中间过渡用的日志组4、5
alter database drop logfile group 33;
alter database drop logfile group 34;
alter database drop logfile group 35;
alter database drop logfile group 36;
alter database drop logfile group 37;
alter database drop logfile group 38;
alter database drop logfile group 39;
alter database drop logfile group 40;
8、到操作系统下删除原日志组4、5中的文件
su - grid
asmcmd
cd +REDODG/CZYJDB/ONLINELOG
rm -rf group33*
rm -rf group34*
rm -rf group35*
rm -rf group36*
rm -rf group37*
rm -rf group38*
rm -rf group39*
rm -rf group40*
rm -rf group_33*
rm -rf group_34*
rm -rf group_35*
rm -rf group_36*
rm -rf group_37*
rm -rf group_38*
rm -rf group_39*
rm -rf group_40*
cd +DATADG/CZYJDB/ONLINELOG
rm -rf group33*
rm -rf group34*
rm -rf group35*
rm -rf group36*
rm -rf group37*
rm -rf group38*
rm -rf group39*
rm -rf group40*
rm -rf group_33*
rm -rf group_34*
rm -rf group_35*
rm -rf group_36*
rm -rf group_37*
rm -rf group_38*
rm -rf group_39*
rm -rf group_40*
9、备份当前的最新的控制文件
SQL> alter database backup controlfile to trace resetlogs
一、redo的设计原则
1、redo的大小
redo的大小主要取决于业务量的大小,即数据库中DML的多少,一般是10~15分钟切换一次最为合适
2、redo的位置
在数据库中一般默认设置3组以上的日志且在同一存储上,若条件允许可以实现:
将每一组日志设置2个member成员,为避免因为存储坏掉而丢失数据,将成员存放在不同的存储上,或至少在同一存储的不同RAID上;
二、redo的操作
在redo的修改中,需要注意几个参数的设置
DB_CREATE_ONLINE_LOG_DEST_n 若设置该参数,在创建日志的时候可以不指定日志的地址,若没有设置DB_CREATE_ONLINE_LOG_DEST_n,需要检查DB_CREATE_FILE_DEST和DB_RECOVERY_FILE_DEST,效果和DB_CREATE_ONLINE_LOG_DEST_n一致
三、redo的管理(摘自官档)
Adding Redo Log File Groups: Examples The following statement adds a redo log file group with two members and identifies it with a GROUP parameter value of 3:
ALTER DATABASE
ADD LOGFILE GROUP 3
('diska:log3.log' ,
'diskb:log3.log') SIZE 50K;
The following statement adds a redo log file group containing two members to thread 5 (in a Real Application Clusters environment) and assigns it a GROUP parameter value of 4:
ALTER DATABASE
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log',
'diskb:log4:log');
Adding Redo Log File Group Members: Example The following statement adds a member to the redo log file group added in the previous example:
ALTER DATABASE
ADD LOGFILE MEMBER 'diskc:log3.log'
TO GROUP 3;
Dropping Log File Members: Example The following statement drops one redo log file member added in the previous example:
ALTER DATABASE
DROP LOGFILE MEMBER 'diskb:log3.log';
The following statement drops all members of the redo log file group 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
Renaming a Log File Member: Example The following statement renames a redo log file member:
ALTER DATABASE
RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';
The preceding statement only changes the member of the redo log group from one file to another. The statement does not actually change the name of the file diskc:log3.log to diskb:log3.log. Before issuing this statement, you must change the name of the file through your operating system.
Clearing a Log File: Example The following statement clears a log file:
ALTER DATABASE
CLEAR LOGFILE 'diskc:log3.log';
2、RAC的redo
必须声明thread,详见官档
3、DG的redo
alter database add standby logfile group n '' size 1000M;
注意细节,摘自惜分飞http://www.xifenfei.com/4388.html
在DG的环境中,配置的是实时同步,需要增大主库的redo大小和数量,本来很简单的一件事情:
先增大备库的standby redo,删除老的standby redo,然后主库增加redo删除老的redo,备库增加新redo删除老redo,最后主库增加standby redo。
但实施的过程中,遇到了一些细节性的问题,主要是学习到了log_file_name_convert如果不配置,将导致备库redo文件不能删除。
增加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健壮性.
如果在RAC中出现
SQL> alter database mount;
ERROR at line 1:
ORA-01618: redo thread 3 is not enabled - cannot mount
则说明thread 3 的redo log没有启用 可以通过下面的方式启用
alter database enable thread 3;
本次实施步骤
查看当前日志文件组的状态
SQL> select GROUP#,MEMBER,STATUS from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
1 +DATADG/czyjdb/onlinelog/group1_1
1 +REDODG/czyjdb/onlinelog/group1_2
2 +DATADG/czyjdb/onlinelog/group2_1
2 +REDODG/czyjdb/onlinelog/group2_2
5 +DATADG/czyjdb/onlinelog/group5_1
5 +REDODG/czyjdb/onlinelog/group5_2
6 +DATADG/czyjdb/onlinelog/group6_1
6 +REDODG/czyjdb/onlinelog/group6_2
7 +DATADG/czyjdb/onlinelog/group7_1
7 +REDODG/czyjdb/onlinelog/group7_2
8 +DATADG/czyjdb/onlinelog/group8_1
8 +REDODG/czyjdb/onlinelog/group8_2
3 +DATADG/czyjdb/onlinelog/group3_1
3 +REDODG/czyjdb/onlinelog/group3_2
4 +DATADG/czyjdb/onlinelog/group4_1
4 +REDODG/czyjdb/onlinelog/group4_2
9 +DATADG/czyjdb/onlinelog/group9_1
9 +REDODG/czyjdb/onlinelog/group9_2
10 +DATADG/czyjdb/onlinelog/group10_1
10 +REDODG/czyjdb/onlinelog/group10_2
11 +DATADG/czyjdb/onlinelog/group11_1
11 +REDODG/czyjdb/onlinelog/group11_2
12 +DATADG/czyjdb/onlinelog/group12_1
12 +REDODG/czyjdb/onlinelog/group12_2
13 +DATADG/czyjdb/onlinelog/group13_1
13 +REDODG/czyjdb/onlinelog/group13_2
14 +DATADG/czyjdb/onlinelog/group14_1
14 +REDODG/czyjdb/onlinelog/group14_2
15 +DATADG/czyjdb/onlinelog/group15_1
15 +REDODG/czyjdb/onlinelog/group15_2
16 +DATADG/czyjdb/onlinelog/group16_1
16 +REDODG/czyjdb/onlinelog/group16_2
17 +DATADG/czyjdb/onlinelog/group17_1
17 +REDODG/czyjdb/onlinelog/group17_2
18 +DATADG/czyjdb/onlinelog/group18_1
18 +REDODG/czyjdb/onlinelog/group18_2
19 +DATADG/czyjdb/onlinelog/group19_1
19 +REDODG/czyjdb/onlinelog/group19_2
20 +DATADG/czyjdb/onlinelog/group20_1
20 +REDODG/czyjdb/onlinelog/group20_2
21 +DATADG/czyjdb/onlinelog/group21_1
21 +REDODG/czyjdb/onlinelog/group21_2
22 +DATADG/czyjdb/onlinelog/group22_1
22 +REDODG/czyjdb/onlinelog/group22_2
23 +DATADG/czyjdb/onlinelog/group23_1
23 +REDODG/czyjdb/onlinelog/group23_2
24 +DATADG/czyjdb/onlinelog/group24_1
24 +REDODG/czyjdb/onlinelog/group24_2
25 +DATADG/czyjdb/onlinelog/group25_1
25 +REDODG/czyjdb/onlinelog/group25_2
26 +DATADG/czyjdb/onlinelog/group26_1
26 +REDODG/czyjdb/onlinelog/group26_2
27 +DATADG/czyjdb/onlinelog/group27_1
27 +REDODG/czyjdb/onlinelog/group27_2
28 +DATADG/czyjdb/onlinelog/group28_1
28 +REDODG/czyjdb/onlinelog/group28_2
29 +DATADG/czyjdb/onlinelog/group29_1
29 +REDODG/czyjdb/onlinelog/group29_2
30 +DATADG/czyjdb/onlinelog/group30_1
30 +REDODG/czyjdb/onlinelog/group30_2
31 +DATADG/czyjdb/onlinelog/group31_1
31 +REDODG/czyjdb/onlinelog/group31_2
32 +DATADG/czyjdb/onlinelog/group32_1
32 +REDODG/czyjdb/onlinelog/group32_2
1、创建2个新的日志组
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 33 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 34 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 35 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 36 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 37 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 38 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 39 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 40 ('+DATADG','+REDODG') size 1024M;
2、切换当前日志到新的日志组
不要使用alter system switch logfile;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
3、删除旧的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;
alter database drop logfile group 27;
alter database drop logfile group 28;
alter database drop logfile group 29;
alter database drop logfile group 30
alter database drop logfile group 31;
alter database drop logfile group 32;
4、操作系统下删除原日志组中的物理文件
su - grid
asmcmd
cd +REDODG/CZYJDB/ONLINELOG
rm -rf group1*
rm -rf group2*
rm -rf group_1*
rm -rf group_2*
rm -rf group30*
rm -rf group31*
rm -rf group32*
rm -rf group_30*
rm -rf group_31*
rm -rf group_32*
cd +DATADG/CZYJDB/ONLINELOG
rm -rf group1*
rm -rf group2*
rm -rf group_1*
rm -rf group_2*
rm -rf group30*
rm -rf group31*
rm -rf group32*
rm -rf group_30*
rm -rf group_31*
rm -rf group_32*
5、重建日志组1、2、3
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 2 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 3 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 4 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 7 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 8 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 9 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 11 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 12 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 13 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 14 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 15 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 16 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 17 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 18 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 19 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 20 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 21 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 22 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 23 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 24 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 25 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 26 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 27 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 28 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 29 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 30 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 31 ('+DATADG','+REDODG') size 1024M;
ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 32 ('+DATADG','+REDODG') size 1024M;
6、切换日志组
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
7、删除中间过渡用的日志组4、5
alter database drop logfile group 33;
alter database drop logfile group 34;
alter database drop logfile group 35;
alter database drop logfile group 36;
alter database drop logfile group 37;
alter database drop logfile group 38;
alter database drop logfile group 39;
alter database drop logfile group 40;
8、到操作系统下删除原日志组4、5中的文件
su - grid
asmcmd
cd +REDODG/CZYJDB/ONLINELOG
rm -rf group33*
rm -rf group34*
rm -rf group35*
rm -rf group36*
rm -rf group37*
rm -rf group38*
rm -rf group39*
rm -rf group40*
rm -rf group_33*
rm -rf group_34*
rm -rf group_35*
rm -rf group_36*
rm -rf group_37*
rm -rf group_38*
rm -rf group_39*
rm -rf group_40*
cd +DATADG/CZYJDB/ONLINELOG
rm -rf group33*
rm -rf group34*
rm -rf group35*
rm -rf group36*
rm -rf group37*
rm -rf group38*
rm -rf group39*
rm -rf group40*
rm -rf group_33*
rm -rf group_34*
rm -rf group_35*
rm -rf group_36*
rm -rf group_37*
rm -rf group_38*
rm -rf group_39*
rm -rf group_40*
9、备份当前的最新的控制文件
SQL> alter database backup controlfile to trace resetlogs