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 nullif you want to add member for redolog,please set value for it.
pro
pro
pro this script drop redo log group didn''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 ||'
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 nullif you want to add member for redolog,please set value for it.


this script drop redo log group didn''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 200REUSE
alter database add  logfile thread 1 GROUP 5 ('/iisdb/data/redo05.log' ) size 200REUSE
alter database add  logfile thread 1 GROUP 6 ('/iisdb/data/redo06.log' ) size 200REUSE
alter database add  logfile thread 1 GROUP 7 ('/iisdb/data/redo07.log' ) size 200REUSE
alter database add  logfile thread 1 GROUP 8 ('/iisdb/data/redo08.log' ) size 200REUSE
alter database add  logfile thread 1 GROUP 9 ('/iisdb/data/redo09.log' ) size 200REUSE

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 nullif you want to add member for redolog,please set value for it.


this script drop redo log group didn''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 128REUSE
alter database add  logfile thread 1 GROUP 8 ('/iisdb/data/redo08.log' ) size 128REUSE
alter database add  logfile thread 1 GROUP 9 ('/iisdb/data/redo09.log' ) size 128REUSE
alter database add  logfile thread 1 GROUP 10 ('/iisdb/data/redo10.log' ) size 128REUSE
alter database add  logfile thread 1 GROUP 11 ('/iisdb/data/redo11.log' ) size 128REUSE
alter database add  logfile thread 1 GROUP 12 ('/iisdb/data/redo12.log' ) size 128REUSE
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>
扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.