검색결과 리스트
#ora-00361에 해당되는 글 1건
- 2015.07.16 6-3. Redo Log 관리하기 - 실습
글
1. 주요명령어 정리
1-1) 신규 Group 생성하기
SQL> alter database add logfile group 4 2 '/u01/app/oracle/oradata/PROD/redo04_a.log' size 5M;
|
1-2) Member 추가하기
SQL> alter database add logfile member 2 '/u01/app/oracle/oradata/PROD/redo04_b.log' to group 4;
SQL> !ls -ltr total 1301600 -rw-r----- 1 oracle dba 7847936 Jun 17 17:30 control01.ctl -rw-r----- 1 oracle dba 104858112 Jun 17 23:43 redo02a.log -rw-r----- 1 oracle dba 104865792 Jun 23 01:18 temp01.dbf -rw-r----- 1 oracle dba 104858112 Jun 23 01:18 redo03a.log -rw-r----- 1 oracle dba 52436992 Jun 23 01:28 users01.dbf -rw-r----- 1 oracle dba 104865792 Jun 23 01:28 example01.dbf -rw-r----- 1 oracle dba 5243392 Jun 23 16:51 redo04_a.log -rw-r----- 1 oracle dba 419438592 Jun 23 16:52 system01.dbf -rw-r----- 1 oracle dba 209723392 Jun 23 16:59 undotbs01.dbf -rw-r----- 1 oracle dba 209723392 Jun 23 16:59 sysaux01.dbf -rw-r----- 1 oracle dba 5243392 Jun 23 17:00 redo04_b.log -rw-r----- 1 oracle dba 104858112 Jun 23 17:00 redo01a.log |
1-3) Member 삭제하기
SQL> alter database drop logfile member 2 '/u01/app/oracle/oradata/PROD/redo04_b.log';
SQL> !ls -ltr total 1301600 -rw-r----- 1 oracle dba 7847936 Jun 17 17:30 control01.ctl -rw-r----- 1 oracle dba 104858112 Jun 17 23:43 redo02a.log -rw-r----- 1 oracle dba 104865792 Jun 23 01:18 temp01.dbf -rw-r----- 1 oracle dba 104858112 Jun 23 01:18 redo03a.log -rw-r----- 1 oracle dba 52436992 Jun 23 01:28 users01.dbf -rw-r----- 1 oracle dba 104865792 Jun 23 01:28 example01.dbf -rw-r----- 1 oracle dba 5243392 Jun 23 16:51 redo04_a.log -rw-r----- 1 oracle dba 419438592 Jun 23 16:52 system01.dbf -rw-r----- 1 oracle dba 209723392 Jun 23 16:59 undotbs01.dbf -rw-r----- 1 oracle dba 209723392 Jun 23 16:59 sysaux01.dbf -rw-r----- 1 oracle dba 5243392 Jun 23 17:00 redo04_b.log -rw-r----- 1 oracle dba 104858112 Jun 23 17:01 redo01a.log
* ASM 기반이 아닐경우 삭제과정을 수행해도 실제 파일은 지워지지않는다. Control file 안에 있는 해당 그룹과 멤버의 정보만 지워진다. ASM 경우 파일까지 지워진다. |
1-4) Group 삭제하기.
SQL> alter database drop logfile group 4;
Database altered.
|
1-5) Log Switch 발생시키기
터미널 1. SQL 접속 SQL> !date Tue Jun 23 17:04:58 KST 2015
SQL> alter system switch logfile;
System altered. 터미널 2. Alert log 확인 Tue Jun 23 17:05:12 2015 Thread 1 advanced to log sequence 11 (LGWR switch) Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/PROD/redo02a.log |
1-6) Check Point 발생시키기
SQL> alter system checkpoint;
System altered. |
1-7) Redo Log File 상태 확인
상태확인 스크립트
[oracle@edydr1p0-PROD script]$ pwd
/home/oracle/script [oracle@edydr1p0-PROD script]$ vi log.sql set line 200 col group# for 999 col mb for 999 col member for a45 col sql# 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 /
|
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 13 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 CURRENT |
CURRENT : LGWR이 내용을 기록하고있는 상태
ACTIVE : Redo log file의 내용이 아직 DB Buffer Cache에서 Data file로 저장이 안되서 지워지면 안되는 상태
INACTIVE : Redo log file의 내용이 Data file로 저장되어 삭제되어도 된다는 의미.
** DBA가 리두로그 파일을 삭제할 경우 INACTIVE 상태로 변경 후 삭제해야하며, 삭제할 때 절대 OS 명령어로 먼저 삭제해서는 안된다. (ACTIVE 상태일때도 삭제는 가능하므로 주의!!)
2. 실습
- 초기상태
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 13 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 CURRENT |
2-1)신규 그룹 / 멤버 추가하기.
* 그룹추가 SQL> alter database add logfile group 4 2 '/u01/app/oracle/oradata/PROD/redo04_a.log' size 5M;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 13 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 CURRENT NO 4 /u01/app/oracle/oradata/PROD/redo04_a.log 5 0 UNUSED YES * 멤버추가 SQL> alter database add logfile member 2 '/u01/app/oracle/oradata/PROD/redo04_b.log' to group 4;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 13 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 CURRENT NO 4 /u01/app/oracle/oradata/PROD/redo04_a.log 5 0 UNUSED YES 4 /u01/app/oracle/oradata/PROD/redo04_b.log 5 0 UNUSED YES * Log Switch 후 상태확인 SQL> alter system switch logfile 2 ;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 13 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 ACTIVE NO 4 /u01/app/oracle/oradata/PROD/redo04_a.log 5 16 CURRENT NO 4 /u01/app/oracle/oradata/PROD/redo04_b.log 5 16 CURRENT NO * Check point 명령으로 ACTIVE -> INACTIVE SQL> alter system checkpoint ;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 13 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 INACTIVE NO 4 /u01/app/oracle/oradata/PROD/redo04_a.log 5 16 CURRENT NO 4 /u01/app/oracle/oradata/PROD/redo04_b.log 5 16 CURRENT NO * Logfile 삭제하기 - current 상태에서도 삭제는 된다. 하지만 Redo log file의 내용이 아직 DB Buffer Cache에서 Data file로 저장되지 않았으므로 하면안됨. SQL> alter database drop logfile member 2 '/u01/app/oracle/oradata/PROD/redo04_b.log';
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 17 CURRENT NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 14 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 INACTIVE NO
4 /u01/app/oracle/oradata/PROD/redo04_a.log 5 16 INACTIVE NO SQL> alter database drop logfile member 2 '/u01/app/oracle/oradata/PROD/redo_04_a.log'; alter database drop logfile member * ERROR at line 1:
ORA-00360: not a logfile member: /u01/app/oracle/oradata/PROD/redo_04_a.log => Log file group의 멤버는 최소 1개이기 때문에, member가 지워지지 않는다. 그룹을 지움으로써 삭제해야함. SQL> alter database drop logfile group 4;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 17 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 18 CURRENT NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 15 INACTIVE NO
* 데이터베이스에서는 지웠지만, OS 파일에서는 여전히 있는상태. OS 명령어로 삭제해야함. [oracle@edydr1p0-PROD PROD]$ pwd /u01/app/oracle/oradata/PROD [oracle@edydr1p0-PROD PROD]$ ls -ltr redo* -rw-r----- 1 oracle dba 104858112 Jun 23 17:54 redo03a.log -rw-r----- 1 oracle dba 5243392 Jun 23 18:14 redo04_b.log -rw-r----- 1 oracle dba 5243392 Jun 23 18:14 redo04_a.log -rw-r----- 1 oracle dba 104858112 Jun 23 18:21 redo01a.log -rw-r----- 1 oracle dba 104858112 Jun 23 18:28 redo02a.log
[oracle@edydr1p0-PROD PROD]$ rm -f redo04_b.log [oracle@edydr1p0-PROD PROD]$ rm -f redo04_a.log [oracle@edydr1p0-PROD PROD]$ ls -ltr redo* -rw-r----- 1 oracle dba 104858112 Jun 23 17:54 redo03a.log -rw-r----- 1 oracle dba 104858112 Jun 23 18:21 redo01a.log -rw-r----- 1 oracle dba 104858112 Jun 23 18:28 redo02a.log |
'Oracle 참고서적 정리 > 오라클 관리실무' 카테고리의 다른 글
7. Tablespace와 Data File 관리하기. (0) | 2015.07.16 |
---|---|
6. Redo Log 관리하기 - SCN과 Checkpoint (0) | 2015.07.16 |
6-2. Redo Log File 구성 및 관리하기. (0) | 2015.06.18 |
6-1 Redo Log 관리하기(Redo Log 생성원리) (0) | 2015.06.18 |
5. Control File 관리하기 (0) | 2015.06.17 |
RECENT COMMENT