Oracle脚本分享:db_auto_add_logfile
在Oracle数据库中，有时候需要根据实际情况调整redo log的大小，增加redo log的日志组的数量，或者增加日志组成员，以及redo log的size大小不合适时，需要调整redo log的大小(删除redo log，重新添加redo log)，一般情况下，都是手工写脚本，如果多台数据库实例，就必须写很多脚本，那么可不可以用一个脚本自动生成相关脚本，自动解决这些问题。这个就是脚本db_auto_add_logfile.sql出现的原因，有了这个脚本，你在维护redo log时就轻松方便很多。当然，这个脚本也许有不足或不完善的地方。另外，此脚本只适用于Linux或Unix平台，不适用于Windows平台,另外，脚本需要在SQL*Plus下调用执行。
/*************************************************************************************************
--ScriptName : db_auto_add_logfile.sql
--Author : 潇湘隐者
--CreateDate : 2022-04-15
--Description : 用于在创建数据库后，添加日志文件组，需要指定增加redolog组个数
指定日志文件大小，会自动把日志文件不同于指定大小的日志组删除。
**************************************************************************************************
--Parameters : 参数说明
**************************************************************************************************
&redo_log_size redo log的大小(单位为M)
&redo_group_number 增加redo log的组数量
&redo_log_new_path redo组成员从一组变成2组,如果已经存在两组成员，忽略其值。
**************************************************************************************************
注意事项：
脚本经过大量测试，但是不能保证所有场景&环境都没有问题，请先测试验证
**************************************************************************************************
Modified Date Modified User Version Modified Reason
**************************************************************************************************
2022-04-15 潇湘隐者 V1.0 新建脚本，脚本参考了"认真就输"的db_add_logfile.sql脚本
2022-05-02 潇湘隐者 V1.1 修改脚本，增加查看redo log详细信息，修改i_logfile_size
单位，增加更多的条件判断。
2022-05-19 潇湘隐者 V1.2 添加redo文件时，数字格式化0x,例如redo3.log格式为redo03.log
2022-06-03 潇湘隐者 V1.3 如果redo log的组成员为2个(多路复用),增加两个redolog
2023-10-20 潇湘隐者 V1.4 增加参数&redo_log_new_path，用来控制redo log组成员增加情况
2023-11-06 潇湘隐者 V1.5 非归档模式，执行脚本alter system archive log current报错问题解决
**************************************************************************************************/
pro
pro **************************************************************************************************
pro
pro check the redo log group information:
pro
pro **************************************************************************************************
SET LINESIZE 200
SET PAGESIZE 1000 HEADING ON VERIFY OFF SERVEROUTPUT ON
col 'first_change#' for 99999999999999
COL "GROUP#" FOR 999999
COL "THREAD#" FOR 9999999
COL "FIRST_CHANGE#" FOR 99999999999999
COL MEMBERS FOR 999999
COL FIRST_TIME FOR A20
COL STATUS FOR A8
SELECT GROUP#
,THREAD#
,SEQUENCE#
,BYTES/1024/1024 AS LOG_SIZE_MB
,MEMBERS
,ARCHIVED
,STATUS
,FIRST_CHANGE#
,TO_CHAR(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') AS FIRST_TIME
FROM V$LOG;
pro **************************************************************************************************
pro
pro Note:The redo log file size information detail:
pro
pro **************************************************************************************************
--查看redo文件信息以及相关属性
SET LINESIZE 850;
SET PAGESIZE 200;
COL "GROUP#" FOR 999999
COL "THREAD#" FOR 9999999
COL STATUS FOR A12
COL MEMBER FOR A48
COL STATUS FOR A8
SELECT A.GROUP#
, A.THREAD#
, A.SEQUENCE#
, B.MEMBER
, A.BLOCKSIZE
, A.STATUS
, A.BYTES/1024/1024 AS SIZE_MB
, A.ARCHIVED
FROM V$LOG A, V$LOGFILE B
WHERE A.GROUP#=B.GROUP#
ORDER BY A.GROUP#;
pro
pro **************************************************************************************************
pro The parameter description are as follows:
pro
pro Note:
pro redo_log_size:The redo log size unit is mb.
pro redo_group_number:The number of redo group log need to added.
pro redo_log_new_path: the default value is null, if you want to add member for redolog,please set value for it.
pro
pro
pro this script drop redo log group didn''t drop the phyical file ,please delete these files manually.
pro **************************************************************************************************
pro
pro
pro Press any key to continue ...
pause
set lines 200
set pages 1000 heading on verify off serveroutput on
DECLARE
path_type VARCHAR2 (200);
log_path_name VARCHAR2 (200);
i_group_current NUMBER;
i_logfile_size NUMBER;
i_group_number NUMBER;
-- i_max_group_number NUMBER;
i_log_number NUMBER;
i_sql VARCHAR2 (1000);
i_sql_arch VARCHAR2 (200) := 'alter system archive log current';
i_sql_swith VARCHAR2 (200) := 'alter system switch logfile';
i_group_status VARCHAR2 (200);
i_curr_log_size NUMBER;
i_log_path_sql VARCHAR2 (600) :='';
i_group_members NUMBER;
i_redo_log_new_path VARCHAR(100) :='';
i_log_mode VARCHAR2(16) :='ARCHIVELOG';
CURSOR cur_log_path IS
SELECT DISTINCT
CASE
WHEN REGEXP_REPLACE (MEMBER, '[^\+]', '') = '+'
THEN
SUBSTR (x.MEMBER,
1,
REGEXP_INSTR (x.MEMBER,
'[/]',
1,
1)
- 1)
ELSE
SUBSTR (x.MEMBER,
1,
REGEXP_INSTR (x.MEMBER,
'[/]',
1,
REGEXP_COUNT (x.MEMBER, '[/]')))
END AS log_path_name
FROM v$logfile x ;
BEGIN
i_logfile_size :='&redo_log_size'; --parameter setting
i_group_number :='&redo_group_number'; --parameter setting
i_redo_log_new_path :=TRIM('&redo_log_new_path'); --parameter setting
DBMS_OUTPUT.put_line('The sql script will be executed:');
SELECT MAX(BYTES/1024/1024) INTO i_curr_log_size
FROM V$LOG;
--允许调整redo log size大小(如果减小当前redo log size,只是给与提示)
IF i_curr_log_size < i_logfile_size
THEN
DBMS_OUTPUT.put_line('The parameter redo_log_size less than current redo log size:' || TO_CHAR(i_curr_log_size));
END IF;
SELECT MAX(MEMBERS) INTO i_group_members
FROM V$LOG;
IF i_group_members >=2 AND LENGTH(i_redo_log_new_path) >=1
THEN
DBMS_OUTPUT.put_line('############################################');
DBMS_OUTPUT.put_line('The redolog group alread has two members!');
DBMS_OUTPUT.put_line('############################################');
DBMS_OUTPUT.put_line('it''s over now, pleaes check the parameter!');
RETURN;
END IF;
SELECT CASE
WHEN REGEXP_REPLACE (MEMBER, '[^\+]', '') = '+'
THEN
'ASM'
WHEN SUBSTR (x.MEMBER,
1,
REGEXP_INSTR (x.MEMBER,
'[/]',
1,
2)
- 1) = '/dev'
THEN
'/dev'
ELSE
'FS'
END
AS path_type
INTO path_type
FROM v$logfile x
WHERE ROWNUM = 1;
FOR c_thread IN (SELECT DISTINCT thread# FROM v$log)
LOOP
--改成从日志组1开始，是为了解决redo log组删除的
i_group_current :=1;
WHILE i_group_number > 0
LOOP
--判断日志组是否存在
SELECT COUNT (*)
INTO i_log_number
FROM v$log
WHERE thread# = c_thread.thread# AND GROUP# = i_group_current;
--如果redo log group存在就结束后续操作，进入下一个循环
IF i_log_number >= 1
THEN
i_group_current := i_group_current+1;
CONTINUE;
END IF;
IF path_type = 'ASM'
THEN
OPEN cur_log_path;
LOOP
FETCH cur_log_path INTO log_path_name;
EXIT WHEN cur_log_path%notfound;
i_log_path_sql := i_log_path_sql ||CHR(39) || log_path_name || CHR(39)||',';
END LOOP;
CLOSE cur_log_path;
IF LENGTH(i_redo_log_new_path) >=1
THEN
i_log_path_sql := i_log_path_sql ||CHR(39) || i_redo_log_new_path || CHR(39)||',';
END IF;
SELECT SUBSTR(i_log_path_sql,1,LENGTH(i_log_path_sql)-1) INTO i_log_path_sql FROM DUAL;
--DBMS_OUTPUT.put_line (i_log_path_sql);
i_sql :=
'alter database add logfile thread '
|| c_thread.thread#
|| ' '
|| 'GROUP '
|| TO_CHAR(i_group_current)
|| ' ('
|| log_path_name
|| ' )'
|| ' size '
|| i_logfile_size ||'M';
ELSIF path_type = 'FS'
THEN
OPEN cur_log_path;
LOOP
FETCH cur_log_path INTO log_path_name;
EXIT WHEN cur_log_path%notfound;
i_log_path_sql := i_log_path_sql ||CHR(39) || log_path_name || 'redo'|| LPAD(i_group_current,2,'0') || '.log' || CHR(39)||',';
END LOOP;
CLOSE cur_log_path;
IF LENGTH(i_redo_log_new_path) >=1
THEN
i_log_path_sql := i_log_path_sql ||CHR(39) || i_redo_log_new_path || 'redo'|| LPAD(i_group_current,2,'0') || '.log' || CHR(39)||',';
END IF;
SELECT SUBSTR(i_log_path_sql,1,LENGTH(i_log_path_sql)-1) INTO i_log_path_sql FROM DUAL;
--DBMS_OUTPUT.put_line (i_log_path_sql);
i_sql :=
'alter database add logfile thread '
|| c_thread.thread#
|| ' '
|| 'GROUP '
|| TO_CHAR(i_group_current)
|| ' ('
|| i_log_path_sql
|| ' )'
|| ' size '
|| i_logfile_size ||'M REUSE';
END IF;
EXECUTE IMMEDIATE i_sql;
DBMS_OUTPUT.put_line(i_sql);
i_group_current := i_group_current + 1;
i_group_number := i_group_number - 1;
i_log_path_sql :=''; --清空变量的值
END LOOP;
FOR i_delete_group
IN (SELECT group#
FROM v$log
WHERE thread# = c_thread.thread# AND bytes != i_logfile_size *1024*1024)
LOOP
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
SELECT LOG_MODE INTO i_log_mode FROM V$DATABASE;
IF i_log_mode ='ARCHIVELOG' THEN
WHILE i_group_status IN ('ACTIVE', 'CURRENT')
LOOP
EXECUTE IMMEDIATE i_sql_arch;
DBMS_OUTPUT.put_line (i_sql_arch);
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
END LOOP;
ELSE
IF i_group_status ='CURRENT' THEN
EXECUTE IMMEDIATE i_sql_swith;
DBMS_OUTPUT.put_line (i_sql_swith);
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
END IF;
WHILE i_group_status IN ('ACTIVE')
LOOP
EXECUTE IMMEDIATE 'alter system checkpoint';
DBMS_OUTPUT.put_line('alter system checkpoint');
DBMS_LOCK.SLEEP(2);
DBMS_OUTPUT.put_line('DBMS_LOCK.SLEEP(2)...');
SELECT status
INTO i_group_status
FROM v$log
WHERE group# = i_delete_group.group#;
END LOOP;
END IF;
i_sql :=
'alter database drop logfile group ' || i_delete_group.group#;
EXECUTE IMMEDIATE i_sql;
--print the sql
DBMS_OUTPUT.put_line (i_sql);
END LOOP;
END LOOP;
END;
/
pro **************************************************************************************************
pro
pro Note:The redo log information:
pro
pro **************************************************************************************************
SET LINESIZE 850;
COL "GROUP#" FOR 999999
COL "THREAD#" FOR 9999999
COL "FIRST_CHANGE#" FOR 99999999999999
COL MEMBERS FOR 999999
COL FIRST_TIME FOR A20
COL STATUS FOR A8
SELECT GROUP#
,THREAD#
,SEQUENCE#
,BYTES/1024/1024 AS LOG_SIZE_MB
,MEMBERS
,ARCHIVED
,STATUS
,FIRST_CHANGE#
,TO_CHAR(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') AS FIRST_TIME
FROM V$LOG;
pro **************************************************************************************************
pro
pro Note:The redo log file size information detail:
pro
pro **************************************************************************************************
--查看redo文件信息以及相关属性
SET LINESIZE 850;
SET PAGESIZE 200;
COL "GROUP#" FOR 999999
COL "THREAD#" FOR 9999999
COL STATUS FOR A12
COL MEMBER FOR A48
COL STATUS FOR A8
SELECT A.GROUP#
, A.THREAD#
, A.SEQUENCE#
, B.MEMBER
, A.BLOCKSIZE
, A.STATUS
, A.BYTES/1024/1024 AS SIZE_MB
, A.ARCHIVED
FROM V$LOG A, V$LOGFILE B
WHERE A.GROUP#=B.GROUP#
ORDER BY A.GROUP#;
下面介绍几个例子，我需要再增加6组redo log,如下所示：
SQL> @db_auto_add_logfile.sql
**************************************************************************************************
check the redo log group information:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# LOG_SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------- -------- ---------- ----------- ------- --- -------- --------------- --------------------
1 1 22 200 1 NO INACTIVE 3149180 2023-11-05 10:06:53
2 1 23 200 1 NO CURRENT 3231372 2023-11-06 02:00:48
3 1 21 200 1 NO INACTIVE 3076367 2023-11-04 21:00:50
**************************************************************************************************
Note:The redo log file size information detail:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# MEMBER BLOCKSIZE STATUS SIZE_MB ARC
------- -------- ---------- ------------------------------------------------ ---------- -------- ---------- ---
1 1 22 /iisdb/data/redo01.log 512 INACTIVE 200 NO
2 1 23 /iisdb/data/redo02.log 512 CURRENT 200 NO
3 1 21 /iisdb/data/redo03.log 512 INACTIVE 200 NO
**************************************************************************************************
The parameter description are as follows:
Note:
redo_log_size:The redo log size unit is mb.
redo_group_number:The number of redo group log need to added.
redo_log_new_path: the default value is null, if you want to add member for redolog,please set value for it.
this script drop redo log group didn''t drop the phyical file ,please delete these files manually.
**************************************************************************************************
Press any key to continue ...
Enter value for redo_log_size: 200
Enter value for redo_group_number: 6
Enter value for redo_log_new_path:
alter database add logfile thread 1 GROUP 4 ('/iisdb/data/redo04.log' ) size 200M REUSE
alter database add logfile thread 1 GROUP 5 ('/iisdb/data/redo05.log' ) size 200M REUSE
alter database add logfile thread 1 GROUP 6 ('/iisdb/data/redo06.log' ) size 200M REUSE
alter database add logfile thread 1 GROUP 7 ('/iisdb/data/redo07.log' ) size 200M REUSE
alter database add logfile thread 1 GROUP 8 ('/iisdb/data/redo08.log' ) size 200M REUSE
alter database add logfile thread 1 GROUP 9 ('/iisdb/data/redo09.log' ) size 200M REUSE
PL/SQL procedure successfully completed.
**************************************************************************************************
Note:The redo log information:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# LOG_SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------- -------- ---------- ----------- ------- --- -------- --------------- --------------------
1 1 22 200 1 NO INACTIVE 3149180 2023-11-05 10:06:53
2 1 23 200 1 NO CURRENT 3231372 2023-11-06 02:00:48
3 1 21 200 1 NO INACTIVE 3076367 2023-11-04 21:00:50
4 1 0 200 1 YES UNUSED 0
5 1 0 200 1 YES UNUSED 0
6 1 0 200 1 YES UNUSED 0
7 1 0 200 1 YES UNUSED 0
8 1 0 200 1 YES UNUSED 0
9 1 0 200 1 YES UNUSED 0
9 rows selected.
**************************************************************************************************
Note:The redo log file size information detail:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# MEMBER BLOCKSIZE STATUS SIZE_MB ARC
------- -------- ---------- ------------------------------------------------ ---------- -------- ---------- ---
1 1 22 /iisdb/data/redo01.log 512 INACTIVE 200 NO
2 1 23 /iisdb/data/redo02.log 512 CURRENT 200 NO
3 1 21 /iisdb/data/redo03.log 512 INACTIVE 200 NO
4 1 0 /iisdb/data/redo04.log 512 UNUSED 200 YES
5 1 0 /iisdb/data/redo05.log 512 UNUSED 200 YES
6 1 0 /iisdb/data/redo06.log 512 UNUSED 200 YES
7 1 0 /iisdb/data/redo07.log 512 UNUSED 200 YES
8 1 0 /iisdb/data/redo08.log 512 UNUSED 200 YES
9 1 0 /iisdb/data/redo09.log 512 UNUSED 200 YES
9 rows selected.
SQL>
假设redo log的size不合适，需要调整为128M大小，redo log组调整为6组
SQL> @db_auto_add_logfile.sql
**************************************************************************************************
check the redo log group information:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# LOG_SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------- -------- ---------- ----------- ------- --- -------- --------------- --------------------
1 1 36 200 1 NO CURRENT 3307341 2023-11-06 15:10:14
2 1 0 200 1 YES UNUSED 0
3 1 0 200 1 YES UNUSED 0
4 1 0 200 1 YES UNUSED 0
5 1 0 200 1 YES UNUSED 0
6 1 0 200 1 YES UNUSED 0
6 rows selected.
**************************************************************************************************
Note:The redo log file size information detail:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# MEMBER BLOCKSIZE STATUS SIZE_MB ARC
------- -------- ---------- ------------------------------------------------ ---------- -------- ---------- ---
1 1 36 /iisdb/data/redo01.log 512 CURRENT 200 NO
2 1 0 /iisdb/data/redo02.log 512 UNUSED 200 YES
3 1 0 /iisdb/data/redo03.log 512 UNUSED 200 YES
4 1 0 /iisdb/data/redo04.log 512 UNUSED 200 YES
5 1 0 /iisdb/data/redo05.log 512 UNUSED 200 YES
6 1 0 /iisdb/data/redo06.log 512 UNUSED 200 YES
6 rows selected.
**************************************************************************************************
The parameter description are as follows:
Note:
redo_log_size:The redo log size unit is mb.
redo_group_number:The number of redo group log need to added.
redo_log_new_path: the default value is null, if you want to add member for redolog,please set value for it.
this script drop redo log group didn''t drop the phyical file ,please delete these files manually.
**************************************************************************************************
Press any key to continue ...
Enter value for redo_log_size: 128
Enter value for redo_group_number: 6
Enter value for redo_log_new_path:
alter database add logfile thread 1 GROUP 7 ('/iisdb/data/redo07.log' ) size 128M REUSE
alter database add logfile thread 1 GROUP 8 ('/iisdb/data/redo08.log' ) size 128M REUSE
alter database add logfile thread 1 GROUP 9 ('/iisdb/data/redo09.log' ) size 128M REUSE
alter database add logfile thread 1 GROUP 10 ('/iisdb/data/redo10.log' ) size 128M REUSE
alter database add logfile thread 1 GROUP 11 ('/iisdb/data/redo11.log' ) size 128M REUSE
alter database add logfile thread 1 GROUP 12 ('/iisdb/data/redo12.log' ) size 128M REUSE
alter system switch logfile
alter system checkpoint
DBMS_LOCK.SLEEP(2)...
alter database drop logfile group 1
alter system switch logfile
alter system checkpoint
DBMS_LOCK.SLEEP(2)...
alter database drop logfile group 2
alter system switch logfile
alter system checkpoint
DBMS_LOCK.SLEEP(2)...
alter database drop logfile group 3
alter system switch logfile
alter system checkpoint
DBMS_LOCK.SLEEP(2)...
alter database drop logfile group 4
alter system switch logfile
alter system checkpoint
DBMS_LOCK.SLEEP(2)...
alter database drop logfile group 5
alter system switch logfile
alter system checkpoint
DBMS_LOCK.SLEEP(2)...
alter database drop logfile group 6
PL/SQL procedure successfully completed.
**************************************************************************************************
Note:The redo log information:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# LOG_SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------- -------- ---------- ----------- ------- --- -------- --------------- --------------------
7 1 42 128 1 NO CURRENT 3307569 2023-11-06 15:11:24
8 1 0 128 1 YES UNUSED 0
9 1 0 128 1 YES UNUSED 0
10 1 0 128 1 YES UNUSED 0
11 1 0 128 1 YES UNUSED 0
12 1 0 128 1 YES UNUSED 0
6 rows selected.
**************************************************************************************************
Note:The redo log file size information detail:
**************************************************************************************************
GROUP# THREAD# SEQUENCE# MEMBER BLOCKSIZE STATUS SIZE_MB ARC
------- -------- ---------- ------------------------------------------------ ---------- -------- ---------- ---
7 1 42 /iisdb/data/redo07.log 512 CURRENT 128 NO
8 1 0 /iisdb/data/redo08.log 512 UNUSED 128 YES
9 1 0 /iisdb/data/redo09.log 512 UNUSED 128 YES
10 1 0 /iisdb/data/redo10.log 512 UNUSED 128 YES
11 1 0 /iisdb/data/redo11.log 512 UNUSED 128 YES
12 1 0 /iisdb/data/redo12.log 512 UNUSED 128 YES
6 rows selected.
SQL>