在Oracle資料庫中,有時候需要根據實際情況調整redo log的大小,增加redo log的日誌組的數量,或者增加日誌組成員,以及redo log的size大小不合適時,需要調整redo log的大小(刪除redo log,重新添加redo log),一般情況下,都是手工寫腳本,如果多台資料庫實 ...
在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:
**************************************************************************************************