Oracle オンラインREDOログ サイズ変更メモ 


## 内容

オンラインREDOログのサイズ変更時のメモ
50M → 100Mに変更する。



## 手順

1. 現状確認

    ```sql:
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;

        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1         79         50          2 CURRENT
             2          1         77         50          2 INACTIVE
             3          1         78         50          2 INACTIVE
    
    SQL> select group#, member from v$logfile order by 1,2;

        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             1 /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
             1 /u01/app/oracle/oradata/TEST01/redo01.log
             2 /u01/app/oracle/fast_recovery_area/TEST01/redo02.log
             2 /u01/app/oracle/oradata/TEST01/redo02.log
             3 /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
             3 /u01/app/oracle/oradata/TEST01/redo03.log
    ```


1. GROUP#2のオンラインREDOログサイズを50M→100Mに変更

    ```sql:
    SQL> alter database drop logfile group 2;
    
    データベースが変更されました。
    
    SQL> !rm /u01/app/oracle/oradata/TEST01/redo02.log
    SQL> !rm /u01/app/oracle/fast_recovery_area/TEST01/redo02.log

    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1         79         50          2 CURRENT
             3          1         78         50          2 INACTIVE
    
    SQL> select group#, member from v$logfile order by 1,2;
    
        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             1 /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
             1 /u01/app/oracle/oradata/TEST01/redo01.log
             3 /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
             3 /u01/app/oracle/oradata/TEST01/redo03.log
    
    SQL> alter database add logfile group 2 (
    '/u01/app/oracle/oradata/TEST01/redo02.log' , 
    '/u01/app/oracle/fast_recovery_area/TEST01/redo02.log'
    ) size 100M;
    
    データベースが変更されました。
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1         79         50          2 CURRENT
             2          1          0        100          2 UNUSED
             3          1         78         50          2 INACTIVE
    
    SQL> select group#, member from v$logfile order by 1,2;
    
        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             1 /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
             1 /u01/app/oracle/oradata/TEST01/redo01.log
             2 /u01/app/oracle/fast_recovery_area/TEST01/redo02.log
             2 /u01/app/oracle/oradata/TEST01/redo02.log
             3 /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
             3 /u01/app/oracle/oradata/TEST01/redo03.log
    ```


1. ログスイッチ

    ``` sql:
    SQL> alter system archive log current;
    
    システムが変更されました。
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1         79         50          2 ACTIVE
             2          1         80        100          2 CURRENT
             3          1         78         50          2 INACTIVE
    ```
    
    GROUP#1のSTATUSがACTIVEのため、checkpointを実施
    
    ``` sql
    SQL> alter system checkpoint;
    
    システムが変更されました。
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1         79         50          2 INACTIVE
             2          1         80        100          2 CURRENT
             3          1         78         50          2 INACTIVE
    ```


1. GROUP#1,3のオンラインREDOログサイズを50M→100Mに変更

    GROUP#1
    
    ``` sql:
    SQL> alter database drop logfile group 1;
    
    データベースが変更されました。
    
    SQL> !rm /u01/app/oracle/oradata/TEST01/redo01.log
    SQL> !rm /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             2          1         80        100          2 CURRENT
             3          1         78         50          2 INACTIVE
    
    SQL> select group#, member from v$logfile order by 1,2;
    
        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             2 /u01/app/oracle/fast_recovery_area/TEST01/redo02.log
             2 /u01/app/oracle/oradata/TEST01/redo02.log
             3 /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
             3 /u01/app/oracle/oradata/TEST01/redo03.log
    
    SQL> alter database add logfile group 1 (
    '/u01/app/oracle/oradata/TEST01/redo01.log' , 
    '/u01/app/oracle/fast_recovery_area/TEST01/redo01.log'
    ) size 100M;
    
    データベースが変更されました。
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1          0        100          2 UNUSED
             2          1         80        100          2 CURRENT
             3          1         78         50          2 INACTIVE
    
    SQL> select group#, member from v$logfile order by 1,2;
    
        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             1 /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
             1 /u01/app/oracle/oradata/TEST01/redo01.log
             2 /u01/app/oracle/fast_recovery_area/TEST01/redo02.log
             2 /u01/app/oracle/oradata/TEST01/redo02.log
             3 /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
             3 /u01/app/oracle/oradata/TEST01/redo03.log
    
    6行が選択されました。
    
    ```
    
    GROUP#3
    
    ``` sql
    SQL> alter database drop logfile group 3;
    
    データベースが変更されました。
    
    !rm /u01/app/oracle/oradata/TEST01/redo03.log
    !rm /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1          0        100          2 UNUSED
             2          1         80        100          2 CURRENT
    
    SQL> select group#, member from v$logfile order by 1,2;
    
        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             1 /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
             1 /u01/app/oracle/oradata/TEST01/redo01.log
             2 /u01/app/oracle/fast_recovery_area/TEST01/redo02.log
             2 /u01/app/oracle/oradata/TEST01/redo02.log
    
    SQL> alter database add logfile group 3 (
    '/u01/app/oracle/oradata/TEST01/redo03.log' , 
    '/u01/app/oracle/fast_recovery_area/TEST01/redo03.log'
    ) size 100M;
    
    データベースが変更されました。
    
    SQL> select group#, thread#, sequence#, bytes/1024/1024 M_bytes, members, status 
    from v$log order by 1,2;
    
        GROUP#    THREAD#  SEQUENCE#    M_BYTES    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------------
             1          1          0        100          2 UNUSED
             2          1         80        100          2 CURRENT
             3          1          0        100          2 UNUSED
    
    SQL> select group#, member from v$logfile order by 1,2;
    
        GROUP# MEMBER
    ---------- ------------------------------------------------------------
             1 /u01/app/oracle/fast_recovery_area/TEST01/redo01.log
             1 /u01/app/oracle/oradata/TEST01/redo01.log
             2 /u01/app/oracle/fast_recovery_area/TEST01/redo02.log
             2 /u01/app/oracle/oradata/TEST01/redo02.log
             3 /u01/app/oracle/fast_recovery_area/TEST01/redo03.log
             3 /u01/app/oracle/oradata/TEST01/redo03.log
    ```