[Tech] oracle logfile 그룹/멤버 추가, 삭제

OS : CentOS7.9 (64bit)

DB : Oracle Database 19c ( Release - 19.21 )

 

 

1. logfile 그룹 추가

(1) group 4 추가

SYS@orcl19>alter database add logfile group 4
  2  '/oracle/app/oracle/oradata/ORCL19/redo04.log' size 100m;

Database altered.

 

(2) 추가된 그룹 확인

SYS@orcl19> set line 200
col group# for 99999
col mb for 99999
col member for a40
col seq# for 99999
col status for a8
col arc for a5

select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" FROM v$logfile a, v$log b where a.group#=b.group# order by 1,2;


GROUP# MEMBER                                                         MB SEQ# STATUS   ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
     1 /oracle/app/oracle/oradata/ORCL19/redo01.log                  200   58 INACTIVE NO
     2 /oracle/app/oracle/oradata/ORCL19/redo02.log                  200   59 CURRENT  NO
     3 /oracle/app/oracle/oradata/ORCL19/redo03.log                  200   57 INACTIVE NO
     4 /oracle/app/oracle/oradata/ORCL19/redo04.log                  100    0 UNUSED   YES

 

 

2. logfile 그룹 삭제

(1) group 4 삭제

SYS@orcl19>alter database drop logfile group 4;

Database altered.

 

(2) 삭제된 그룹 확인

SYS@orcl19> set line 200
col group# for 99999
col mb for 99999
col member for a40
col seq# for 99999
col status for a8
col arc for a5

select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" FROM v$logfile a, v$log b where a.group#=b.group# order by 1,2;


GROUP# MEMBER                                                         MB SEQ# STATUS   ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
     1 /oracle/app/oracle/oradata/ORCL19/redo01.log                  200   62 ACTIVE   NO
     2 /oracle/app/oracle/oradata/ORCL19/redo02.log                  200   63 CURRENT  NO
     3 /oracle/app/oracle/oradata/ORCL19/redo03.log                  200   61 ACTIVE   NO

 

* 삭제 중 발생하는 에러

  현재 4번 로그파일 그룹이 사용 중이라 발생하는 오류

SYS@orcl19>alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance orcl19 (thread 1)
ORA-00312: online log 4 thread 1: '/oracle/app/oracle/oradata/ORCL19/redo04.log'

 

  강제 로그 스위치 한 후 정상 삭제

SYS@orcl19>alter system switch logfile;

System altered.


SYS@orcl19>alter system switch logfile;

System altered.

SYS@orcl19>alter system checkpoint;

System altered.

SYS@orcl19>alter database drop logfile group 4;

Database altered.

 

(3) 물리적인 파일 수동 삭제

[orcl19]ejdb:/oracle/app/oracle/oradata/ORCL19> :ls -lart *.log
-rw-r----- 1 oracle dba 104858112 11월 28 12:13 redo04.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:24 redo03.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:24 redo01.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:25 redo02.log


[orcl19]ejdb:/oracle/app/oracle/oradata/ORCL19> :rm redo04.log

 

3. logfile 멤버 추가

(1) 현재 logfile 그룹 및 멤버 확인

SYS@orcl19>select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" FROM v$logfile a, v$log b where a.group#=b.group# order by 1,2;

GROUP# MEMBER                                                         MB SEQ# STATUS   ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
     1 /oracle/app/oracle/oradata/ORCL19/redo01.log                  200   62 INACTIVE NO
     2 /oracle/app/oracle/oradata/ORCL19/redo02.log                  200   63 INACTIVE NO
     3 /oracle/app/oracle/oradata/ORCL19/redo03.log                  200   64 CURRENT  NO

 

(2) 그룹 3 새로운 멤버 추가

SYS@orcl19>alter database add logfile member '/oracle/app/oracle/oradata/ORCL19/redo03_b.log' to group 3;

Database altered.

 

(3) 추가된 멤버 확인

SYS@orcl19>select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" FROM v$logfile a, v$log b where a.group#=b.group# order by 1,2;

GROUP# MEMBER                                                         MB SEQ# STATUS   ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
     1 /oracle/app/oracle/oradata/ORCL19/redo01.log                  200   62 INACTIVE NO
     2 /oracle/app/oracle/oradata/ORCL19/redo02.log                  200   63 INACTIVE NO
     3 /oracle/app/oracle/oradata/ORCL19/redo03.log                  200   64 CURRENT  NO
     3 /oracle/app/oracle/oradata/ORCL19/redo03_b.log                200   64 CURRENT  NO

 

 

4. logfile 멤버 삭제

(1) 추가된 멤버 삭제

SYS@orcl19>alter database drop logfile member '/oracle/app/oracle/oradata/ORCL19/redo03_b.log';

Database altered.

 

(2) 삭제된 멤버 확인

SYS@orcl19>select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" FROM v$logfile a, v$log b where a.group#=b.group# order by 1,2;

GROUP# MEMBER                                                         MB SEQ# STATUS   ARC
------ ------------------------------------------------------------ ---- ---- -------- -----
     1 /oracle/app/oracle/oradata/ORCL19/redo01.log                  200   65 ACTIVE   NO
     2 /oracle/app/oracle/oradata/ORCL19/redo02.log                  200   66 CURRENT  NO
     3 /oracle/app/oracle/oradata/ORCL19/redo03.log                  200   64 ACTIVE   NO

 

* 삭제 중 발생하는 에러

  현재 4번 로그파일 그룹이 사용 중이라 발생하는 오류

SYS@orcl19>alter database drop logfile member '/oracle/app/oracle/oradata/ORCL19/redo03_b.log';
alter database drop logfile member '/oracle/app/oracle/oradata/ORCL19/redo03_b.log'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '/oracle/app/oracle/oradata/ORCL19/redo03.log'
ORA-00312: online log 3 thread 1: '/oracle/app/oracle/oradata/ORCL19/redo03_b.log'

 

강제 로그 스위치 한 후 정상 삭제

SYS@orcl19>alter system switch logfile;

System altered.


SYS@orcl19>alter system switch logfile;

System altered.


SYS@orcl19>alter database drop logfile member '/oracle/app/oracle/oradata/ORCL19/redo03_b.log';

Database altered.

 

(3) 물리적인 파일 수동 삭제

[orcl19]ejdb:/oracle/app/oracle/oradata/ORCL19> :ls -lart *.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:22 redo03_b.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:24 redo03.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:24 redo01.log
-rw-r----- 1 oracle dba 209715712 11월 29 15:25 redo02.log

[orcl19]ejdb:/oracle/app/oracle/oradata/ORCL19> :rm redo03_b.log