Redo Log File 관리 하기

SQL>!vi redolog.sql

set line 200

col group# for 999

col mb for 999

col member for a45

col seq# for 999

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

/

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 CURRENT NO

     3 /app/oracle/oradata/testdb/redo03.log       50    3 INACTIVE NO

SQL> alter database add logfile group 4

  2  '/app/oracle/oradata/testdb/redo04_a.log

  3  ' size 5M;

Database altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 CURRENT NO

     3 /app/oracle/oradata/testdb/redo03.log       50    3 INACTIVE NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    0 UNUSED YES

SQL> alter database add logfile member

  2  '/app/oracle/oradata/testdb/redo04_b.log' to group 4;

Database altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 CURRENT NO

     3 /app/oracle/oradata/testdb/redo03.log       50    3 INACTIVE NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    0 UNUSED YES

     4 /app/oracle/oradata/testdb/redo04_b.log 5    0 UNUSED YES

SQL> alter system switch logfile;

System altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 ACTIVE NO

     3 /app/oracle/oradata/testdb/redo03.log       50    3 INACTIVE NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    6 CURRENT NO

     4 /app/oracle/oradata/testdb/redo04_b.log 5    6 CURRENT NO

SQL> alter system checkpoint;

System altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 INACTIVE NO

     3 /app/oracle/oradata/testdb/redo03.log       50    3 INACTIVE NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    6 CURRENT NO

     4 /app/oracle/oradata/testdb/redo04_b.log 5    6 CURRENT NO

SQL> alter system switch logfile;

System altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 INACTIVE NO

     3 /app/oracle/oradata/testdb/redo03.log       50    7 CURRENT NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    6 ACTIVE NO

     4 /app/oracle/oradata/testdb/redo04_b.log 5    6 ACTIVE NO

SQL> alter system checkpoint;

System altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 INACTIVE NO

     3 /app/oracle/oradata/testdb/redo03.log       50    7 CURRENT NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    6 INACTIVE NO

     4 /app/oracle/oradata/testdb/redo04_b.log 5    6 INACTIVE NO

SQL> alter database drop logfile member '/app/oracle/oradata/testdb/redo04_b.log';

Database altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 INACTIVE NO

     3 /app/oracle/oradata/testdb/redo03.log       50    7 CURRENT NO

     4 /app/oracle/oradata/testdb/redo04_a.log 5    6 INACTIVE NO

SQL> !ls /app/oracle/oradata/testdb/redo04_b.log

/app/oracle/oradata/testdb/redo04_b.log

SQL> !rm /app/oracle/oradata/testdb/redo04_b.log

SQL> alter database drop logfile member '/app/oracle/oradata/testdb/redo04_a.log';

alter database drop logfile member '/app/oracle/oradata/testdb/redo04_a.log'

*

ERROR at line 1:

ORA-00360: not a logfile member: /app/oracle/oradata/testdb/redo04_a.log

SQL> alter database drop logfile group 4;

Database altered.

SQL> @redolog

GROUP# MEMBER       MB SEQ# STATUS ARC

------ --------------------------------------------- ---- ---- -------- -----

     1 /app/oracle/oradata/testdb/redo01.log       50    4 INACTIVE NO

     2 /app/oracle/oradata/testdb/redo02.log       50    5 INACTIVE NO

     3 /app/oracle/oradata/testdb/redo03.log       50    7 CURRENT NO

SQL> !ls /app/oracle/oradata/testdb/redo04_a.log

ls: cannot access /app/oracle/oradata/testdb/redo04_a.log: 그런 파일이나 디렉터리가 없습니다

SQL> !ls /app/oracle/oradata/testdb/redo04_a.log?

/app/oracle/oradata/testdb/redo04_a.log?

SQL> !rm -rf /app/oracle/oradata/testdb/redo04_a.log?