[오라클]Redo Log Resize 방법

*. REDO LOG group은 3개 이상 권장, member는 2개 권장

   (member는 물리적으로 서로 다른 위치에 분산 권장)
*. 평상시 REDO LOG 스위치가 약 20분 이상 유지할 수 있는 Size 권장
   (alert_TESTDB.log에서 지속적으로 모니터링해서 필요시 REDO LOG 크기를 증가시킬 것)

*. 체크포인트 간격 조절(initTESTDB.ora파라미터에서) fast_start_mttr_target = 600

*. DB Startup 상태에서 작업 가능

1. sqlplus 접속(Internal로 접속)
   $> sqlplus '/ as sysdba'
   OR
   $> sqlplus system/manager

 

2. currunt REDO LOG 조회

   SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         43  104857600          2 YES INACTIVE               8485439 01-SEP-03
         2          1         44  104857600          2 YES INACTIVE               8585874 02-SEP-03
         3          1         45  104857600          2 NO  CURRENT                8756665 03-SEP-03

   STATUS가 Inactive인 경우만 작업 가능

 

3. REDO LOG file을 switch
   SQL> alter system switch logfile;
   SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         46  104857600          2 NO  CURRENT                8988801 03-SEP-03
         2          1         44  104857600          2 YES INACTIVE               8585874 02-SEP-03
         3          1         45  104857600          2 YES ACTIVE                 8756665 03-SEP-03

   SQL> alter system switch logfile;
   SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         46  104857600          2 YES ACTIVE                 8988801 03-SEP-03
         2          1         47  104857600          2 NO  CURRENT                8990631 03-SEP-03
         3          1         45  104857600          2 YES INACTIVE               8756665 03-SEP-03

 

4. acitive 한 REDO LOG group을 inactive로 변경
   SQL> alter system checkpoint;

 

5. REDO LOG Drop
   SQL> alter database drop logfile group 2;

6. REDO LOG logfile 삭제
   SQL> !rm /oradata1/redo2a.dbf;
   SQL> !rm /oradata2/redo2b.dbf;

 

7. REDO LOG logfile 추가
   1) 멤버가 없는 경우
      SQL> alter database add logfile group 2 '/oradata/redo2.dbf' size 100M;

   2) 멤버가 있는 경우
      SQL> alter database add logfile member  '/oradata/redo2b.dbf' to group 2;

      SQL> alter database add logfile
                 group 2 ('/oradata1/redo2a','/oradata2/redo2b') size 100M reuse;
   3) 여러개의 REDO LOG 추가할 경우
      SQL> alter database add logfile
                 group 4 ('/oradata1/redo4a','/oradata2/redo4b') size 100M,
                 group 5 ('/oradata1/redo5a','/oradata2/redo5b') size 100M,
                 group 6 ('/oradata1/redo6a','/oradata2/redo6b') size 100M;