Добавить / Удалить redo log groups in Oracle RAC

Просмотр ONLINE и STANDBY redo logs

set lines 1000
column MEMBER format a50

select lf.group#,
       l_type.log_type as type,
       lf.member,
       l_type.size_mb
 from v$logfile lf
 join (
   select group#,'ONLINE' as log_type, bytes/1024/1024 as size_mb from v$log
   union
   select group#,'STANDBY' as log_type, bytes/1024/1024 as size_mb from v$standby_log) l_type
on lf.group#=l_type.group#
order by lf.group#;



Добавить / удалить логи

online

alter database add logfile group  1 '+DATA' size 512M;
alter database add logfile group  6 '+DATA' size 512M;

alter database drop logfile group 1;
alter database drop logfile group 6;




standby

alter database add standby logfile group  7 '+DATA' size 512M;
alter database add standby logfile group 13 '+DATA' size 512M;

alter database drop logfile group  7;
alter database drop logfile group 13;
 


Просмотр текущих redo log groups:


set lines 1000
column REDOLOG_FILE_NAME format a50

SELECT a.GROUP#,
       a.THREAD#,
       a.SEQUENCE#,
       a.ARCHIVED,
       a.STATUS,
       b.MEMBER AS REDOLOG_FILE_NAME,
       (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;



Добавляем:

alter database add logfile thread 1 group 40 ('+REDO_B') size 5120m;
alter database add logfile thread 1 group 42 ('+REDO_B') size 5120m;
alter database add logfile thread 1 group 44 ('+REDO_B') size 5120m;
alter database add logfile thread 1 group 46 ('+REDO_B') size 5120m;
alter database add logfile thread 1 group 48 ('+REDO_B') size 5120m;

alter database add logfile thread 2 group 41 ('+REDO_B') size 5120m;
alter database add logfile thread 2 group 43 ('+REDO_B') size 5120m;
alter database add logfile thread 2 group 45 ('+REDO_B') size 5120m;
alter database add logfile thread 2 group 47 ('+REDO_B') size 5120m;
alter database add logfile thread 2 group 49 ('+REDO_B') size 5120m;

Мультиплексируем:

ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 40;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 41;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 42;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 43;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 44;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 45;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 46;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 47;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 48;
ALTER DATABASE ADD LOGFILE MEMBER '+REDO_B' TO GROUP 49;

Удаляем:

alter database drop logfile group 30;
alter database drop logfile group 31;
alter database drop logfile group 32;
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;


Примеры:

SELECT a.GROUP#,
       a.THREAD#,
       a.SEQUENCE#,
       a.ARCHIVED,
       a.STATUS,
       b.MEMBER AS REDOLOG_FILE_NAME,
       (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

GROUP#  THREAD# SEQUENCE#  ARCHIVED STATUS      REDOLOG_FILE_NAME                       SIZE_MB

30    1    82494    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_30_1_1    5120
30    1    82494    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_30_1_2    5120

31    2    62915    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_31_2_1    5120
31    2    62915    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_31_2_2    5120

32    1    82495    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_32_1_1    5120
32    1    82495    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_32_1_2    5120

33    2    62916    YES    ACTIVE            +REDO_FLASH/rt2/onlinelog/group_33_2_1    5120
33    2    62916    YES    ACTIVE            +REDO_FLASH/rt2/onlinelog/group_33_2_2    5120

34    1    82496    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_34_1_1    5120
34    1    82496    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_34_1_2    5120

35    2    62917    NO    CURRENT            +REDO_FLASH/rt2/onlinelog/group_35_2_1    5120
35    2    62917    NO    CURRENT            +REDO_FLASH/rt2/onlinelog/group_35_2_2    5120

36    1    82497    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_36_1_1    5120
36    1    82497    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_36_1_2    5120

37    2    62913    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_37_2_1    5120
37    2    62913    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_37_2_2    5120

38    1    82498    NO    CURRENT            +REDO_FLASH/rt2/onlinelog/group_38_1_1    5120
38    1    82498    NO    CURRENT            +REDO_FLASH/rt2/onlinelog/group_38_1_2    5120

39    2    62914    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_39_2_1    5120
39    2    62914    YES    INACTIVE    +REDO_FLASH/rt2/onlinelog/group_39_2_2    5120


Добавление redo log groups:

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 40 ('+REDO_FLASH/rt2/onlinelog/group_40_1_1') SIZE 5120m,
GROUP 40 ('+REDO_FLASH/rt2/onlinelog/group_40_1_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 41 ('+REDO_FLASH/rt2/onlinelog/group_41_2_1') SIZE 5120m,
GROUP 41 ('+REDO_FLASH/rt2/onlinelog/group_41_2_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 42 ('+REDO_FLASH/rt2/onlinelog/group_42_1_1') SIZE 5120m,
GROUP 42 ('+REDO_FLASH/rt2/onlinelog/group_42_1_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 43 ('+REDO_FLASH/rt2/onlinelog/group_43_2_1') SIZE 5120m,
GROUP 43 ('+REDO_FLASH/rt2/onlinelog/group_43_2_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 44 ('+REDO_FLASH/rt2/onlinelog/group_44_1_1') SIZE 5120m,
GROUP 44 ('+REDO_FLASH/rt2/onlinelog/group_44_1_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 45 ('+REDO_FLASH/rt2/onlinelog/group_45_2_1') SIZE 5120m,
GROUP 45 ('+REDO_FLASH/rt2/onlinelog/group_45_2_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 46 ('+REDO_FLASH/rt2/onlinelog/group_46_1_1') SIZE 5120m,
GROUP 46 ('+REDO_FLASH/rt2/onlinelog/group_46_1_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 47 ('+REDO_FLASH/rt2/onlinelog/group_47_2_1') SIZE 5120m,
GROUP 47 ('+REDO_FLASH/rt2/onlinelog/group_47_2_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 48 ('+REDO_FLASH/rt2/onlinelog/group_48_1_1') SIZE 5120m,
GROUP 48 ('+REDO_FLASH/rt2/onlinelog/group_48_1_2') SIZE 5120m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 49 ('+REDO_FLASH/rt2/onlinelog/group_49_2_1') SIZE 5120m,
GROUP 49 ('+REDO_FLASH/rt2/onlinelog/group_49_2_2') SIZE 5120m;



Или так:

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 40 '+REDO_FLASH/rt2/onlinelog/group_40_1_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 40 '+REDO_FLASH/rt2/onlinelog/group_40_1_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 41 '+REDO_FLASH/rt2/onlinelog/group_41_2_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 41 '+REDO_FLASH/rt2/onlinelog/group_41_2_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 42 '+REDO_FLASH/rt2/onlinelog/group_42_1_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 42 '+REDO_FLASH/rt2/onlinelog/group_42_1_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 43 '+REDO_FLASH/rt2/onlinelog/group_43_2_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 43 '+REDO_FLASH/rt2/onlinelog/group_43_2_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 44 '+REDO_FLASH/rt2/onlinelog/group_44_1_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 44 '+REDO_FLASH/rt2/onlinelog/group_44_1_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 45 '+REDO_FLASH/rt2/onlinelog/group_45_2_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 45 '+REDO_FLASH/rt2/onlinelog/group_45_2_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 46 '+REDO_FLASH/rt2/onlinelog/group_46_1_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 46 '+REDO_FLASH/rt2/onlinelog/group_46_1_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 47 '+REDO_FLASH/rt2/onlinelog/group_47_2_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 47 '+REDO_FLASH/rt2/onlinelog/group_47_2_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 48 '+REDO_FLASH/rt2/onlinelog/group_48_1_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 48 '+REDO_FLASH/rt2/onlinelog/group_48_1_2' SIZE 5120m;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 49 '+REDO_FLASH/rt2/onlinelog/group_49_2_1' SIZE 5120m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 49 '+REDO_FLASH/rt2/onlinelog/group_49_2_2' SIZE 5120m;



Удалять можно  inactive / unused redo log group:

SQL> select group#,thread#,members,status from v$log;

group#  thread# members status

30    1    2    INACTIVE
31    2    2    INACTIVE
32    1    2    CURRENT
33    2    2    INACTIVE
34    1    2    INACTIVE
35    2    2    INACTIVE
36    1    2    INACTIVE
37    2    2    INACTIVE
38    1    2    INACTIVE
39    2    2    CURRENT


SQL> alter database drop logfile group 30;

SQL> select group#,thread#,members,status from v$log;




Ещё пример:

SQL> column REDOLOG_FILE_NAME format a50;

SQL> set lines 1000
SQL>
SELECT a.GROUP#,
       a.THREAD#,
       a.SEQUENCE#,
       a.ARCHIVED,
       a.STATUS,
       b.MEMBER AS REDOLOG_FILE_NAME,
       (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;


    GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NE              SIZE_MB
   ------- ---------- ---------- --- ---------------- --------------------------- ----------
         1          1          3 YES ACTIVE           +REDO/proddb/redo01.log      100
         2          1          4 NO  CURRENT          +REDO/proddb/redo02.log      100
         3          2          1 YES INACTIVE         +REDO/proddb/redo03.log      100
         4          2          2 NO  CURRENT          +REDO/proddb/redo04.log      100



SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('+REDO/proddb/redo05.log') SIZE 100m,
GROUP 6 ('+REDO/proddb/redo06.log') SIZE 100m;

SQL>
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 ('+REDO/proddb/redo07.log') SIZE 100m,
GROUP 8 ('+REDO/proddb/redo08.log') SIZE 100m;


или так:

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 '+REDO' SIZE 100m;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 '+REDO' SIZE 100m;

Проверяем:

SQL> column REDOLOG_FILE_NAME format a50;

SQL> set lines 1000
SQL> SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
 a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NAME                                     SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
         1          1          3 YES INACTIVE         +REDO/proddb/redo01.log                                 100
         2          1          4 NO  CURRENT          +REDO/proddb/redo02.log                                 100
         3          2          1 YES INACTIVE         +REDO/proddb/redo03.log                                 100
         4          2          2 NO  CURRENT          +REDO/proddb/redo04.log                                 100
         5          1          0 YES UNUSED           +REDO/proddb/redo05.log                                 100
         6          1          0 YES UNUSED           +REDO/proddb/redo06.log                                 100
         7          2          0 YES UNUSED           +REDO/proddb/redo07.log                                 100
         8          2          0 YES UNUSED           +REDO/proddb/redo08.log                                 100



Удаляем только  INACTIVE илиr UNUSED:

SQL>  select group#,thread#,members,status from v$log;

    GROUP#    THREAD#    MEMBERS     STATUS
---------- ---------- ---------- ----------------
         1          1          2    INACTIVE
         2          1          2    INACTIVE
         3          2          2    CURRENT
         4          2          2    INACTIVE
         5          1          2    INACTIVE
         6          1          2    INACTIVE
         7          2          2    INACTIVE
         8          2          2    CURRENT


SQL> аlter database drop logfile group 1;

Проверяем:

SQL>  select group#,thread#,members,status from v$log;