새소식

Oracle 10g

[ORACLE] Database 관리 : Redo log file 관리

  • -
1. 상태 확인

SQL> select * from v$log;
redo log file의 상태를 확인
v$log 의 status 컬럼

UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.

ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.


SQL> select * from v$logfile;
logfile의 위치를 확인



2. 그룹 추가

SQL> alter database add logfile('/u01/app/oracle/oradata/devdb/redo03_a.log',
                                            '/u01/app/oracle/oradata/devdb/redo03_b.log') size 10m;
그룹번호를 을 입력하지 않고 추가

SQL> alter database add logfile group 4(
                            '/u01/app/oracle/oradata/devdb/redo04_a.log',
                            '/u01/app/oracle/oradata/devdb/redo04_b.log') size 10m;
그룹번호를 입력하고 추가

SQL> alter system switch logfile;
추가한 로그파일을 active로 변경하기 위해 강제로 로그스위칭해준다(모두 바뀔때까지)

SQL> alter system checkpoint;
checkpoint를 줘서 로그를 모두 내려쓴다. (모두 inactive 상태로 변환)



3. 그룹 삭제

SQL> select * from v$logfile
         where group# = (select max(group#) from v$log where status = 'INACTIVE');
INACTIVE한 상태의 로그파일을 확인

SQL> alter database drop logfile group 4;
위에서 확인한 4번 그룹을 삭제 (테이블에서만 삭제 되고 파일은 그대로 남아 있다.)

[oracle@oracle devdb]$ rm /u01/app/oracle/oradata/devdb/redo04_a.log
[oracle@oracle devdb]$ rm /u01/app/oracle/oradata/devdb/redo04_b.log
파일 삭제



4. 맴버 추가

alter database add logfile member
                  '/u01/app/oracle/oradata/devdb/redo01_c.log' to group 1,
                  '/u01/app/oracle/oradata/devdb/redo02_c.log' to group 2,
                  '/u01/app/oracle/oradata/devdb/redo03_c.log' to group 3;
맴버 추가

select member from v$logfile order by group#, member;
확인



5. 맴버 삭제

Current, Active 그룹의 멤버는 삭제 되지 않는다. 강제 스위칭이나 checkpoint를 설정하여 Inactive 상태를 만들어서 맴버를 삭제 하도록한다.

alter database drop logfile member '/u01/app/oracle/oradata/devdb/redo01_c.log';
alter database drop logfile member '/u01/app/oracle/oradata/devdb/redo02_c.log';
alter database drop logfile member '/u01/app/oracle/oradata/devdb/redo03_c.log';
맴버 삭제

!rm /u01/app/oracle/oradata/devdb/redo01_c.log
!rm /u01/app/oracle/oradata/devdb/redo02_c.log
!rm /u01/app/oracle/oradata/devdb/redo03_c.log
파일 삭제


6. 맴버 위치 이동

mkdir /u01/app/oracle/oradata/devdb/redo_a
mkdir /u01/app/oracle/oradata/devdb/redo_b
이동할 디렉토리 생성

cp /u01/app/oracle/oradata/devdb/r*a.log /u01/app/oracle/oradata/devdb/redo_a/
cp /u01/app/oracle/oradata/devdb/r*b.log /u01/app/oracle/oradata/devdb/redo_b/
파일 이동(복사한 경우 이므로 이전 파일을 지움)

startup mount
mount 상태로 startup, OPEN까지 startup 하면 이전의 경로를 찾게 된다.

alter database rename file
            '/u01/app/oracle/oradata/devdb/r1a.log',
            '/u01/app/oracle/oradata/devdb/r2a.log',
            '/u01/app/oracle/oradata/devdb/r3a.log'
        to
            '/u01/app/oracle/oradata/devdb/redo_a/r1a.log',
            '/u01/app/oracle/oradata/devdb/redo_a/r2a.log',
            '/u01/app/oracle/oradata/devdb/redo_a/r3a.log';


alter database rename file
            '/u01/app/oracle/oradata/devdb/r1b.log',
            '/u01/app/oracle/oradata/devdb/r2b.log',
            '/u01/app/oracle/oradata/devdb/r3b.log'
        to
            '/u01/app/oracle/oradata/devdb/redo_b/r1b.log',
            '/u01/app/oracle/oradata/devdb/redo_b/r2b.log',
            '/u01/app/oracle/oradata/devdb/redo_b/r3b.log';
경로 변경

rm /u01/app/oracle/oradata/devdb/*.log
이전 경로의 파일 삭제
Contents

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.