데이터베이스
오라클 DB - REDO LOG FILE
slcry
2022. 11. 9. 17:39
로그 스위치 - LGWR는 온라인 리두 로그 파일에 순차적으로 기록합니다. 현재 온라인 리두 로그 파일 그
룹이 가득 찼을 때 LGWR는 다음 그룹에 기록하기 시작합니다.
로그 스위치 및 체크포인트 시행
ALTER / SYSTEM / SWITCH / LOGFILE - 다음명령으로 로그 스위치 실행
FAST_START_MTTR_TARGET 매개변수 설정
FAST_START_MTTR_TARGET = 600
ALTER SYSTEM CHECKPOINT 명령
ALTER SYSTEM CHECKPOINT;
온라인 리두 로그 파일 그룹 추가
ALTER DATABASE ADD LOGFILE GROUP 3
('$HOME/ORADATA/u01/log3a.rdo',
'$HOME/ORADATA/u02/log3b.rdo')
SIZE 1M; --------------- > 미러셋이라 크기까지 같은 1M이다.
온라인 리두 로그 파일 멤버 추가
ALTER DATABASE ADD LOGFILE MEMBER
'$HOME/ORADATA/u04/log1c.rdo' TO GROUP 1,
'$HOME/ORADATA/u04/log2c.rdo' TO GROUP 2,
'$HOME/ORADATA/u04/log3c.rdo' TO GROUP 3;
온라인 리두 로그 파일 그룹 삭제
ALTER DATABASE DROP LOGFILE GROUP 3;
온라인 리두 로그 파일 멤버 삭제
ALTER DATABASE DROP LOGFILE MEMBER
'$HOME/ORADATA/u04/log3c.rdo';
Redo log file 관리
실습 4.1 Redo log file 확인 (필)
사용 명령어
SQL> SELECT A.GROUP#, A.MEMBER, B.BYTES, B.STATUS, B.SEQUENCE#
2 FROM V$LOGFILE A, V$LOG B
3 WHERE A.GROUP# = B.GROUP#
4 ORDER BY 1;- 현재 log 그룹과 member의 운영 상황을 조회한다.
- GROUP# : 그룹 번호
- MEMBER : 멤버 파일의 경로 및 파일명
- BYTES : 멤버 파일의 크기
- STATUS : 그룹의 상태 [표 4.1]을참고 한다.
SQL> ARCHIVE LOG LIST
- Archive log 관련 내용을 조회한다.
- Archive log mode가 아닌 경우 조회된 내용은 별 의미가 없다.SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
---------- -------------------------------------- ---------- ---------------- ----------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 1
2 /app/ora19c/oradata/DB19/redo02.log 209715200 CURRENT 2
3 /app/ora19c/oradata/DB19/redo03.log 209715200 UNUSED 0
SQL> ARCHIVE LOG LIST
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /app/ora19c/19c/dbs/arch
Oldest online log sequence 1
Current log sequence 2실습 4.2 Log switch와 checkpoint (필)
사용 명령어
SQL> ALTER SYSTEM SWITCH LOGFILE;
- Log switch를 강제로 발생 시킨다.
SQL> ALTER SYSTEM CHECKPOINT;
- Check point를 강제로 발생 시킨다.SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
---------- -------------------------------------- ---------- --------- ----------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 1
2 /app/ora19c/oradata/DB19/redo02.log 209715200 CURRENT 2
3 /app/ora19c/oradata/DB19/redo03.log 209715200 UNUSED 0
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance DB19 (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/app/ora19c/oradata/DB19/redo02.log'
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
---------- -------------------------------------- ---------- ---------- ----------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 1
2 /app/ora19c/oradata/DB19/redo02.log 209715200 ACTIVE 2
3 /app/ora19c/oradata/DB19/redo03.log 209715200 CURRENT 3
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------- -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 1
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
3 /app/ora19c/oradata/DB19/redo03.log 209715200 CURRENT 3
SQL> !vi switch.sql
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
SQL> @switch
System altered.
System altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 CURRENT 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
3 /app/ora19c/oradata/DB19/redo03.log 209715200 INACTIVE 3실습 4.3 Redo log group 추가와 삭제 (필)
사용 명령어
SQL> ALTER DATABASE DROP LOGFILE GROUP [group number];
- 지정한 redo log group을 삭제한다.
SQL> ALTER DATABASE ADD LOGFILE GROUP [group number]
2 (['member file1', 'member file2', ... ]) SIZE [크기];
- redo log group을 추가한다. [group number]는 생략 가능하다.SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 CURRENT 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
3 /app/ora19c/oradata/DB19/redo03.log 209715200 INACTIVE 3
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 CURRENT 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
SQL> !ls /app/ora19c/oradata/DB19/
redo01.log redo03.log system01.dbf undotbs01.dbf
redo02.log sysaux01.dbf temp01.dbf users01.dbf
SQL> !rm /app/ora19c/oradata/DB19/redo03.log
SQL> ALTER DATABASE ADD LOGFILE GROUP 4
2 '/app/ora19c/oradata/DB19/redo04.log' SIZE 50M;
Database altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------- -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 CURRENT 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
4 /app/ora19c/oradata/DB19/redo04.log 52428800 UNUSED 0
SQL> @switch
System altered.
System altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
4 /app/ora19c/oradata/DB19/redo04.log 52428800 CURRENT 5
SQL> ALTER DATABASE ADD LOGFILE GROUP 5
2 '/app/ora19c/oradata/DB19/redo05.log' SIZE 50M;
Database altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ----------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
4 /app/ora19c/oradata/DB19/redo04.log 52428800 CURRENT 5
5 /app/ora19c/oradata/DB19/redo05.log 52428800 UNUSED 0
SQL> @switch
System altered.
System altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------- -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
4 /app/ora19c/oradata/DB19/redo04.log 52428800 INACTIVE 5
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 6실습 4.4 Redo log member 추가와 삭제 (필)
사용 명령어
SQL> ALTER DATABASE DROP LOGFILE MEMBER
2 ['member file1', 'member file2', ...]
- 지정한 멤버 파일을 삭제한다.
SQL> ALTER DATABASE ADD LOGFILE MEMBER
2 ['member file'] TO GROUP [group nember],
3 ... ;
- redo log 멤버 파일을 지정한 그룹에 추가한다.SQL> ALTER DATABASE ADD LOGFILE MEMBER
2 '/app/ora19c/oradata/DB19/redo04_2.log' TO GROUP 4,
3 '/app/ora19c/oradata/DB19/redo05_2.log' TO GROUP 5;
System altered.SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------- -------------------------------------- ---------- ---------------- ----------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 4
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 2
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 5
4 /app/ora19c/oradata/DB19/redo04.log 52428800 INACTIVE 5
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 CURRENT 6
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 6
6 rows selected.
SQL> ALTER DATABASE DROP LOGFILE MEMBER
2 '/app/ora19c/oradata/DB19/redo04.log';
ALTER DATABASE DROP LOGFILE MEMBER
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 4
ORA-01517: log member: '/app/ora19c/oradata/DB19/redo04.log'
SQL> ALTER DATABASE DROP LOGFILE MEMBER
2 '/app/ora19c/oradata/DB19/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER
*
ERROR at line 1:
ORA-00361: cannot remove last log member /app/ora19c/oradata/DB19/redo01.log for group 1
SQL> @switch
System altered.
System altered.
SQL> @switch
System altered.
System altered.
SQL> @switch
System altered.
System altered.
SQL> @switch
System altered.
System altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ---------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 8
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 7
4 /app/ora19c/oradata/DB19/redo04_2.log 5242880 INACTIVE 9
4 /app/ora19c/oradata/DB19/redo04.log 5242880 INACTIVE 9
5 /app/ora19c/oradata/DB19/redo05_2.log 5242880 CURRENT 10
5 /app/ora19c/oradata/DB19/redo05.log 5242880 CURRENT 10
6 rows selected.
SQL> ALTER DATABASE DROP LOGFILE MEMBER
2 '/app/ora19c/oradata/DB19/redo04.log';
Database altered.
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
2 FROM v$logfile a, v$log b
3 WHERE a.group# = b.group#
4 ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
------ -------------------------------------- ---------- ---------------- ----------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 8
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 7
4 /app/ora19c/oradata/DB19/redo04_2.log 5242880 INACTIVE 9
5 /app/ora19c/oradata/DB19/redo05_2.log 5242880 CURRENT 10
5 /app/ora19c/oradata/DB19/redo05.log 5242880 CURRENT 10