728x90
반응형
Redo log 파일
리두로그란 오라클에서 일어나는 트랜잭션의 변경내용을 기록하는 파일이다. Instance가 비정상적으로 종료된다던지, 디스크에 손상이 생겼을 때 기록된 내용으로 복구가 가능하다.
-목차-
1. 미러링 계획
2. 미러링 실습
3. 미러링 마무리
1. 미러링 계획
목표
/app/ora19c/oradata/disk4/redo01.log
/app/ora19c/oradata/disk5/redo01.log
/app/ora19c/oradata/disk4/redo02.log
/app/ora19c/oradata/disk5/redo02.log
/app/ora19c/oradata/disk4/redo03.log
/app/ora19c/oradata/disk5/redo03.log
실습 순서
① 리두로그파일 확인
② 스위치, 체크포인트 강제발생
③ Redo log group 추가와 삭제
④Redo log member 추가와 삭제
⑤Redo log file 재배치
⑥ 미러링 상태 확인
2. 미러링 실습
리두로그파일 확인
SQL>
SELECT a.group#, a.member, b.bytes, b.status, b.sequence#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
----------------------------------------------------------------------------------------------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 7
2 /app/ora19c/oradata/DB19/redo02.log 209715200 CURRENT 8
3 /app/ora19c/oradata/DB19/redo03.log 209715200 INACTIVE 6
===============================================================================================
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /app/ora19c/19c/dbs/arch
Oldest online log sequence 6
Current log sequence 8
그룹1,3은 inactive이고 그룹2는 current인걸 조회하고, noarchie mode라 의미는 없지만 관련 내용을 조회한다.
스위치, 체크포인트 강제발생
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance DB19 (thread 1)
ORA-00312: online log 2 thread 1: '/app/ora19c/oradata/DB19/redo02.log' //삭제불가
==================================================================================
SQL> alter system switch logfile; ///log switch 강제발생
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 7
2 /app/ora19c/oradata/DB19/redo02.log 209715200 ACTIVE 8
3 /app/ora19c/oradata/DB19/redo03.log 209715200 CURRENT
=====================================================================================
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 7
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
3 /app/ora19c/oradata/DB19/redo03.log 209715200 CURRENT 9
=======================================================================================
SQL> !vi switch.sql
SQL> !cat 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 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
3 /app/ora19c/oradata/DB19/redo03.log 209715200 INACTIVE그룹2는 current 상태라 삭제가 안된다. alter system 명령어로 switch를 강제발생시키고 조회하니 2번그룹은 current에서 active으로 변화했고, 3번그룹은 inactive에서 current로 변화했다. 여기서 checkpoint를 강제로 발생시켰더니 2번그룹의 상태가 active에서 inactive으로 변화했다.
Redo log group 추가와 삭제
SQL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence# //log 운영상황 조회
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 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
3 /app/ora19c/oradata/DB19/redo03.log 209715200 INACTIVE
========================================================================================
SQL> ALTER DATABASE DROP LOGFILE GROUP 3; //그룹3 로그파일 삭제
Database altered.
=========================================================================================
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 //그룹4 로그파일 생성
2 '/app/ora19c/oradata/DB19/redo04.log' SIZE 50M;
Database altered.
QL> SELECT a.group#, a.member, b.bytes, b.status, b.sequence# //unused
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 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
4 /app/ora19c/oradata/DB19/redo04.log 52428800 UNUSED
SQL> @switch //switch
System altered.
System altered.
SQL>
SELECT a.group#, a.member, b.bytes, b.status, b.sequence# //current, sequence11
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# MEMBER BYTES STATUS SEQUENCE#
---------- ---------------- -----------------------------------------------------------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
4 /app/ora19c/oradata/DB19/redo04.log 52428800 CURRENT 11
============================================================================================
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 //그룹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 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
4 /app/ora19c/oradata/DB19/redo04.log 52428800 CURRENT 11
5 /app/ora19c/oradata/DB19/redo05.log 52428800 UNUSED 0
QL> @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 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
4 /app/ora19c/oradata/DB19/redo04.log 52428800 INACTIVE 11
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 12그룹3 리두로그파일을 삭제하고 그룹4 로그파일을 생성했다. 막 생성해서 status가 unused였는데 스위치를 하고나니 current가 되었다. 그룹5도 똑같이 current로 만들었다.
Redo log member 추가와 삭제
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;
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 10
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 8
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 11
4 /app/ora19c/oradata/DB19/redo04.log 52428800 INACTIVE 11
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 CURRENT 12
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 12
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 14
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 13
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 15
4 /app/ora19c/oradata/DB19/redo04.log 52428800 INACTIVE 15
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 CURRENT 16
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 16
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 14
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 13
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 15
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 CURRENT 16
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 16ALTER DATABASE ADD LOGFILE MEMBER 명령어로 redo04_2.log파일을 그룹4번, redo05_2.log파일을 그룹5번에 미러링을 하고 current상태인걸 확인했다. inactive인 redo04.log파일과 redo01.log파일을 DROP했는데 동기화가 안되고 멤버가 1이라 ORA-00362에러가 발생했다. 그래서 스위치를하고 다시 redo04.log를 DROP했다.
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;
GROUP# MEMBER BYTES TATUS SEQUENCE#
---------- ----------------------------------------------------------------------------------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 CURRENT 18
2 /app/ora19c/oradata/DB19/redo02.log 209715200 INACTIVE 17
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 15
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 INACTIVE 16
5 /app/ora19c/oradata/DB19/redo05.log 52428800 INACTIVE 16
===============================================================================================
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance DB19 (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/app/ora19c/oradata/DB19/redo01.log'
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 TATUS SEQUENCE#
---------- ----------------------------------------------------------------------------------
1 /app/ora19c/oradata/DB19/redo01.log 209715200 INACTIVE 18
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 CURRENT 19
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 INACTIVE 16
5 /app/ora19c/oradata/DB19/redo05.log 52428800 INACTIVE 16
============================================================================================
SQL> @switch
System altered.
System altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
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 TATUS SEQUENCE#
---------- ----------------------------------------------------------------------------------
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 19
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 CURRENT 20
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 20
SQL> !ls /app/ora19c/oradata/
DB19 disk1 disk2 disk3 disk4 disk5
SQL> ALTER DATABASE ADD LOGFILE GROUP 1
2 ('/app/ora19c/oradata/disk4/redo01.log',
3 '/app/ora19c/oradata/disk5/redo01.log') SIZE 50M;
Database altered.현재상화을 조회해보고 그룹 1,2,4,5를 DROP했다. 그룹 4,5는 필요가 없고 1,2도 디렉토리를 바꿔야해서 삭제했다. 그룹1은 currunt상태라 스위치를하고 삭제했다.
===============================================================================================
SQL> ALTER DATABASE ADD LOGFILE GROUP 2
2 ('/app/ora19c/oradata/disk4/redo02.log',
3 '/app/ora19c/oradata/disk5/redo02.log') SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 3
2 ('/app/ora19c/oradata/disk4/redo03.log',
3 '/app/ora19c/oradata/disk5/redo03.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 TATUS SEQUENCE#
---------- ----------------------------------------------------------------------------------
1 /app/ora19c/oradata/disk4/redo01.log 52428800 UNUSED 0
1 /app/ora19c/oradata/disk5/redo01.log 52428800 UNUSED 0
2 /app/ora19c/oradata/disk4/redo02.log 52428800 UNUSED 0
2 /app/ora19c/oradata/disk5/redo02.log 52428800 UNUSED 0
3 /app/ora19c/oradata/disk4/redo03.log 52428800 UNUSED 0
3 /app/ora19c/oradata/disk5/redo03.log 52428800 UNUSED 0
4 /app/ora19c/oradata/DB19/redo04_2.log 52428800 INACTIVE 19
5 /app/ora19c/oradata/DB19/redo05.log 52428800 CURRENT 20
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 CURRENT 20
9 rows selected.
==========================================================================================
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 5
*
ERROR at line 1:
ORA-01623: log 5 is current log for instance DB19 (thread 1) - cannot drop
ORA-00312: online log 5 thread 1: '/app/ora19c/oradata/DB19/redo05.log'
ORA-00312: online log 5 thread 1: '/app/ora19c/oradata/DB19/redo05_2.log'
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 TATUS SEQUENCE#
---------- ----------------------------------------------------------------------------------
1 /app/ora19c/oradata/disk5/redo01.log 52428800 CURRENT 21
1 /app/ora19c/oradata/disk4/redo01.log 52428800 CURRENT 21
2 /app/ora19c/oradata/disk4/redo02.log 52428800 UNUSED 0
2 /app/ora19c/oradata/disk5/redo02.log 52428800 UNUSED 0
3 /app/ora19c/oradata/disk4/redo03.log 52428800 UNUSED 0
3 /app/ora19c/oradata/disk5/redo03.log 52428800 UNUSED 0
5 /app/ora19c/oradata/DB19/redo05_2.log 52428800 INACTIVE 20
5 /app/ora19c/oradata/DB19/redo05.log 52428800 INACTIVE 20
8 rows selected.
================================================================================================
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
Database altered.다시 운영상황을 조회해보고 group 1,2,3을 미러링하여 그룹과 멤버를 생성한다. group1,2,3에 멤버가 두개씩 생성되었다. gorup5는 current상태라 삭제가 안돼서 switch를 해주고 다시 조회했다. inactive상태가 되어서 다시 DROP했다.
미러링 확인
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 TATUS SEQUENCE#
---------- ----------------------------------------------------------------------------------
1 /app/ora19c/oradata/disk4/redo01.log 52428800 CURRENT 21
1 /app/ora19c/oradata/disk5/redo01.log 52428800 CURRENT 21
2 /app/ora19c/oradata/disk4/redo02.log 52428800 UNUSED 0
2 /app/ora19c/oradata/disk5/redo02.log 52428800 UNUSED 0
3 /app/ora19c/oradata/disk4/redo03.log 52428800 UNUSED 0
3 /app/ora19c/oradata/disk5/redo03.log 52428800 UNUSED 0
6 rows selected.그룹5가 잘 삭제되었고, 1장에서 계획한대로 출력되었다.
3. 마무리
SQL> !ls /app/ora19c/oradata/DB19
redo01.log redo04.log redo05.log sysaux01.dbf temp01.dbf users01.dbf
redo02.log redo04_2.log redo05_2.log system01.dbf undotbs01.dbf
SQL> !rm -rf /app/ora19c/oradata/DB19/*.log
SQL> !ls /app/ora19c/oradata/DB19
sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf쓸모없는걸 지웟다.
728x90
반응형
'데이터베이스' 카테고리의 다른 글
| [ORACLE] ORA-00942: table or view does not exist 에러 원인, 해결법 (1) | 2022.12.02 |
|---|---|
| [ORACLE] MOUNT 상태에서 data file 이동하기 (0) | 2022.11.18 |
| [ORACLE] control file 다중화하기 (0) | 2022.11.18 |
| [ORACLE] oracle19c 설치하기 (0) | 2022.11.18 |