LoginSignup
8
13

More than 5 years have passed since last update.

@sowd(sow d)

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

Last updated at Posted at 2015-04-05

内容

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

手順

  1. 現状確認

    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

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

    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

  3. ログスイッチ

    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> 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

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

    GROUP#1

    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> 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
8
13
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
13