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:

**************************************************************************************************