Ghost's Oracle Database !

현재 데이터베이스의 데이터 파일과 테이블스페이스 상태 조회

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 27 20:37:46 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> set line 200
SQL> col tablespace_name format a16
SQL> col file_name format a50
SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;

TABLESPACE_NAME  STATUS    CONTENTS  EXTENT_MAN SEGMEN
---------------- --------- --------- ---------- ------
SYSTEM           ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1         ONLINE    UNDO      LOCAL      MANUAL
SYSAUX           ONLINE    PERMANENT LOCAL      AUTO
TEMP             ONLINE    TEMPORARY LOCAL      MANUAL
USERS            ONLINE    PERMANENT LOCAL      AUTO
EXAMPLE          ONLINE    PERMANENT LOCAL      AUTO

6 rows selected.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
USERS               5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf

SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME       BYTES FILE_NAME
---------------- ---------- --------------------------------------------------
TEMP               20971520 /home/oracle/oradata/testdb/temp01.dbf

SQL>

■ Data file 및 Tablespace 조회
SQL> select tablespace_name, bytes, file_name from dba_data_files;

SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;


■ Tablespace 생성
- 일반 Tablespace
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  segment space management auto;

SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  extent space management local;

- UNDO Tablespace
SQL> create undo tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

- TEMP Tablespace
SQL> create temporary tablespace tablespace_name
  2  tempfile 'path/data_file_name' size 크기;

- Tablespace Block 지정
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  blocksize [2K | 4K | 8K | 16K | 32K]
  4  segment space management auto;


■ Tablespace 확장
SQL> alter tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

SQL> alter database datafile 'path/data_file_name' resize 크기;


■ Tablespace 관리
SQL> alter tablespace tablespace_name offline;
SQL> alter tablespace tablespace_name online;

SQL> alter tablespace tablespace_name rename
  2  datafile 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';

SQL> alter database rename
  2  file 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';


■ Tablespace 삭제
SQL> drop tablespace tablespace_name
  2  including contents and datafiles cascade constraints;

아래와 같이 재구성하기!!!
/home/oracle/disk1/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/home/oracle/disk2/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
/home/oracle/disk3/control03.ctl, redo01_c.log, redo02_c.log, redo03_c.log



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora
*.control_files='/home/oracle/disk1/control01.ctl','/home/oracle/disk2/control02.ctl','/home/oracle/disk3/control03.ctl'

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk1/control01.ctl
/home/oracle/disk2/control02.ctl
/home/oracle/disk3/control03.ctl

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                            10 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                            10 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                            10 NO  INACTIVE
         2 /home/oracle/disk5/redo02_b.log                            10 NO  INACTIVE
         3 /home/oracle/disk4/redo03_a.log                            10 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                            10 NO  INACTIVE

6 rows selected.

SQL> alter database add logfile group 4 '/home/oracle/disk4/redo04.log' size 10m;

Database altered.

SQL> alter database add logfile group 5 '/home/oracle/disk5/redo05.log' size 10m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                            10 NO  INACTIVE
         1 /home/oracle/disk5/redo01_b.log                            10 NO  INACTIVE
         2 /home/oracle/disk4/redo02_a.log                            10 NO  INACTIVE
         2 /home/oracle/disk5/redo02_b.log                            10 NO  INACTIVE
         3 /home/oracle/disk4/redo03_a.log                            10 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                            10 NO  INACTIVE
         4 /home/oracle/disk4/redo04.log                              10 NO  CURRENT
         5 /home/oracle/disk5/redo05.log                              10 YES UNUSED

8 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/disk4/redo04.log                              10 NO  CURRENT
         5 /home/oracle/disk5/redo05.log                              10 YES UNUSED

SQL> alter database add logfile group 1
  2  ('/home/oracle/disk1/redo01_a.log',
  3   '/home/oracle/disk2/redo01_b.log',
  4   '/home/oracle/disk3/redo01_c.log')
  5  size 10m;

Database altered.

SQL> alter database add logfile group 2
  2  ('/home/oracle/disk1/redo02_a.log',
  3   '/home/oracle/disk2/redo02_b.log',
  4   '/home/oracle/disk3/redo02_c.log')
  5  size 10m;

Database altered.

SQL> alter database add logfile group 3
  2  ('/home/oracle/disk1/redo03_a.log',
  3   '/home/oracle/disk2/redo03_b.log',
  4   '/home/oracle/disk3/redo03_c.log')
  5  size 10m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01_a.log                            10 NO  CURRENT
         1 /home/oracle/disk2/redo01_b.log                            10 NO  CURRENT
         1 /home/oracle/disk3/redo01_c.log                            10 NO  CURRENT
         2 /home/oracle/disk1/redo02_a.log                            10 YES UNUSED
         2 /home/oracle/disk2/redo02_b.log                            10 YES UNUSED
         2 /home/oracle/disk3/redo02_c.log                            10 YES UNUSED
         3 /home/oracle/disk1/redo03_a.log                            10 YES UNUSED
         3 /home/oracle/disk2/redo03_b.log                            10 YES UNUSED
         3 /home/oracle/disk3/redo03_c.log                            10 YES UNUSED
         4 /home/oracle/disk4/redo04.log                              10 NO  INACTIVE
         5 /home/oracle/disk5/redo05.log                              10 YES UNUSED

11 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk1/control01.ctl
/home/oracle/disk2/control02.ctl
/home/oracle/disk3/control03.ctl

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01_a.log                            10 NO  CURRENT
         1 /home/oracle/disk2/redo01_b.log                            10 NO  CURRENT
         1 /home/oracle/disk3/redo01_c.log                            10 NO  CURRENT
         2 /home/oracle/disk1/redo02_a.log                            10 YES UNUSED
         2 /home/oracle/disk2/redo02_b.log                            10 YES UNUSED
         2 /home/oracle/disk3/redo02_c.log                            10 YES UNUSED
         3 /home/oracle/disk1/redo03_a.log                            10 YES UNUSED
         3 /home/oracle/disk2/redo03_b.log                            10 YES UNUSED
         3 /home/oracle/disk3/redo03_c.log                            10 YES UNUSED

9 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk1/control01.ctl
/home/oracle/disk2/control02.ctl
/home/oracle/disk3/control03.ctl

SQL>

아래와 같이 재구성하기!!!
SQL> !mkdir /home/oracle/disk1
SQL> !mkdir /home/oracle/disk2
SQL> !mkdir /home/oracle/disk3
SQL> !mkdir /home/oracle/disk4
SQL> !mkdir /home/oracle/disk5

/home/oracle/disk4/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/home/oracle/disk5/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log



[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 25 10:15:52 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> create pfile from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

#pfile의 내용을 아래와 같이 수정한다.
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora
*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl'

#startup시 pfile이 적용되도록 spfile은 삭제 또는 파일명을 변경한다.
SQL> !mv $ORACLE_HOME/dbs/spfiletestdb.ora $RACLE_HOME/dbs/spfiletestdb.ora.old

SQL> startup     
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              88082000 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 NO  INACTIVE
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  INACTIVE
         2 /home/oracle/disk1/redo02a.rdo                              5 NO  CURRENT
         2 /home/oracle/disk2/redo02b.rdo                              5 NO  CURRENT
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED

6 rows selected.

SQL> col name for a50
SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl

SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/redo05.log' size 5m;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 NO  INACTIVE
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  INACTIVE
         2 /home/oracle/disk1/redo02a.rdo                              5 NO  CURRENT
         2 /home/oracle/disk2/redo02b.rdo                              5 NO  CURRENT
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04.log                      5 YES UNUSED
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

8 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 NO  INACTIVE
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  INACTIVE
         2 /home/oracle/disk1/redo02a.rdo                              5 NO  INACTIVE
         2 /home/oracle/disk2/redo02b.rdo                              5 NO  INACTIVE
         3 /home/oracle/disk1/redo03a.rdo                              5 NO  CURRENT
         3 /home/oracle/disk2/redo03b.rdo                              5 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04.log                      5 YES UNUSED
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

8 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 NO  INACTIVE
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  INACTIVE
         2 /home/oracle/disk1/redo02a.rdo                              5 NO  INACTIVE
         2 /home/oracle/disk2/redo02b.rdo                              5 NO  INACTIVE
         3 /home/oracle/disk1/redo03a.rdo                              5 NO  INACTIVE
         3 /home/oracle/disk2/redo03b.rdo                              5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  CURRENT
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

8 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  CURRENT
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

SQL> alter database add logfile group 1
  2  ('/home/oracle/disk4/redo01_a.log',
  3   '/home/oracle/disk5/redo01_b.log')
  4  size 10m;

Database altered.

SQL> alter database add logfile group 2
  2  ('/home/oracle/disk4/redo02_a.log',
  3   '/home/oracle/disk5/redo02_b.log')
  4  size 10m;

Database altered.

SQL> alter database add logfile group 3
  2  ('/home/oracle/disk4/redo03_a.log',
  3   '/home/oracle/disk5/redo03_b.log')
  4  size 10m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                            10 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                            10 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                            10 YES UNUSED
         2 /home/oracle/disk5/redo02_b.log                            10 YES UNUSED
         3 /home/oracle/disk4/redo03_a.log                            10 YES UNUSED
         3 /home/oracle/disk5/redo03_b.log                            10 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

8 rows selected.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                            10 NO  INACTIVE
         1 /home/oracle/disk5/redo01_b.log                            10 NO  INACTIVE
         2 /home/oracle/disk4/redo02_a.log                            10 NO  CURRENT
         2 /home/oracle/disk5/redo02_b.log                            10 NO  CURRENT
         3 /home/oracle/disk4/redo03_a.log                            10 YES UNUSED
         3 /home/oracle/disk5/redo03_b.log                            10 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

8 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                            10 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                            10 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                            10 NO  INACTIVE
         2 /home/oracle/disk5/redo02_b.log                            10 NO  INACTIVE
         3 /home/oracle/disk4/redo03_a.log                            10 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                            10 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  INACTIVE

8 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                            10 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                            10 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                            10 NO  INACTIVE
         2 /home/oracle/disk5/redo02_b.log                            10 NO  INACTIVE
         3 /home/oracle/disk4/redo03_a.log                            10 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                            10 NO  INACTIVE

6 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

내 나이 33을 바라보고 있는 이 상황에서 아르바이트 경험은 많지 않다.

첫 번째는 고등학교 2학년때부터 대학생까지 했던 백화점 판매 아르바이트

두 번째는 군대 전역 후 1일 12시간씩 일했던 PC방 아르바이트

두 가지의 아르바이트는 나에겐 후회되지 않는 아르바이트였다.

그리고 세 번째는 3개월간 일했던 둔촌 고등학교의 전산 보조원이다.

이 덕분일까?

네 번째 아르바이트를 서울특별시 교육청에서 상주하는 파견직으로 하게 되었다.

하지만 네 번째 아르바이트는 곧 첫 직장이 되어버렸다.

그 이후엔 아르바이트 경험이 없다.

그리고는 게임회사에서 줄곧 일해왔다.

회사명을 말하면 대부분은 모른다. 하지만 게임명을 말하면 다 알고 있는 회사들이다.

그동안 게임이 좋았고 나름대로의 보람도 있었지만 지금 되돌아보면 비전이 있어보이지는 않았다.

주변 친구들이 내 성격과 행동들을 보고 경영 기획팀 쪽 일이 맞을거라 한다.

하지만 관련 부서에 취업할 가능성은 있을까?

그보다 내가 관심이 있고 적성에 맞을듯한 분야, 직업을 찾는것이 빠를듯 싶다.

그것이 오라클 데이터베이스이다.

처음엔 단순 데이터베이스에 관심을 갖었다.

그것이 고등학교 2학년때 잠시 접해본 로터스 1-2-3 이었던가? 기억도 가물가물하다...

내 기억에 첫 느낌이란... "이게 뭐하는 것일까? 어려운것 같다." 정도 였다.

그리고 쉽게 접할 수 있는 mysql이다.

물론 DBA가 아닌 클라이언트 사용자 입장이지만 전체적인 관리를 제외하고 계정내에서는 나름대로 관리를 해줘야 한다.

회사 생활을 하면서 직업상 수 많은 데이터를 관리하게 되었다.

직/간접적으로 그게 내 업무의 일부였으니 자연스레 관심이 갈 수 밖에 없었다.

그래서 시작한지 약 4개월째인데 내가 알고 있는건 2% 부족한게 아니라 2% 정도는 알고 있는걸까?

정신없이 달려왔고 앞으로를 위해 지난날을 되돌아보며 열심히 했다. 그리고 열심히 하고 있으며, 열심히 할 것이다.

리두 로그 그룹을 아래와 같이 구성해본다.
　　　　　group1　　　group2　　　group3
Disk1　　member1　　member2　　member3
--------------------------------------------
Disk2　　member1　　member2　　member3


#현재 리두 로그 그룹 및 멤버와 상태를 확인한다.
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE

#위와 같이 리두 로그 그룹을 구성하기 위한 임시 리두 로그 그룹 4, 5를 추가한다.
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/log/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/log/redo05.log' size 5m;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 YES UNUSED
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 YES UNUSED

#리두 로그 그룹 1, 2, 3을 삭제하기 위해 CURRENT를 리두 로그 그룹 4, 5 둘 중 하나로 이동시킨다.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#리두 로그 그룹 1, 2, 3 삭제를 위해 강제로 체크포인트를 발생시켜 INACTIVE 상태로 만든다.
SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  INACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#리두 로그 그룹 1, 2, 3을 재구성하기 위해 삭제한다.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#재구성할 디렉토리를 생성해준다. (실무에서는 새로 추가한 디스크의 경로를 입력하면 된다.)
SQL> !mkdir -p $ORACLE_BASE/disk1/

SQL> !mkdir -p $ORACLE_BASE/disk2/

#리두 로그 그룹 1의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 1
  2  ( '$ORACLE_BASE/disk1/redo01a.rdo',
  3    '$ORACLE_BASE/disk2/redo01b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 2의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 2
  2  ( '$ORACLE_BASE/disk1/redo02a.rdo',
  3    '$ORACLE_BASE/disk2/redo02b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 3의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 3
  2  ( '$ORACLE_BASE/disk1/redo03a.rdo',
  3    '$ORACLE_BASE/disk2/redo03b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 1, 2, 3은 각각 멤버 2개로 구성되어 있으며, 각 멤버는 안전성을 위해 disk1과 disk2로 나뉘어 사용된다.
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 YES UNUSED
         1 /home/oracle/disk2/redo01b.rdo                              5 YES UNUSED
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

8 rows selected.

#임시 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 NO  CURRENT
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  CURRENT
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

8 rows selected.

#임시로 생성한 리두 로그 그룹 4, 5를 삭제한다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

#처음 의도한 바와 같이 3개의 그룹에 각각 2개의 멤버가 존재하며 각각의 멤버는 서로 다른 디스크에 할당된 것을 확인할 수 있다.
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01a.rdo                              5 NO  CURRENT
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  CURRENT
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED

6 rows selected.

SQL>

리두 로그 그룹 추가/삭제를 통해 디렉토리 및 사이즈를 변경해본다.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

#새로운 리두 로그 그룹 4, 5를 추가한다.
#리두 로그 그룹 1, 2, 3을 삭제하기 위해 리두 로그 그룹은 최소 2개가 존재해야 한다.
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/redo05.log' size 5m;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 YES UNUSED
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

#리두 로그 그룹 4, 5를 추가한 후 CURRENT를 새로 추가한 리두 로그 그룹으로 변경한다.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

#리두 로그 그룹 1, 2, 3을 삭제하기 위해 수동으로 checkpoint를 발생시킨다.
SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#리두 로그 그룹 5를 CURRENT 상태로 만든 후 리두 로그 그룹 1, 2, 3을 삭제한다.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#새로운 리두 로그 그룹 1, 2, 3을 추가하기 위한 새로운 디렉토리를 생성한다.
#디렉토리를 생성하지 않고 추가할 경우 디렉토리 문제로 에러가 발생한다.
SQL> !mkdir -p /home/oracle/oradata/testdb/log

#새로운 디렉토리에 리두 로그 그룹 1, 2, 3을 추가한다.
SQL> alter database add logfile group 1
  2  '/home/oracle/oradata/testdb/log/redo01.log' size 5m;

Database altered.

SQL> alter database add logfile group 2
  2  '/home/oracle/oradata/testdb/log/redo02.log' size 5m;

Database altered.

SQL> alter database add logfile group 3
  2  '/home/oracle/oradata/testdb/log/redo03.log' size 5m;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 YES UNUSED
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 YES UNUSED
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#기존 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  CURRENT
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  ACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#강제 로그 스위치 발생 경과로 보아 UNUSED에서 바로 CURRENT가 되지 않는다.
#CURRENT의 순서는 4 -> 5 -> 4 -> 1 순이며 세번째 4가 CURRENT일때 1, 2, 3의 상태는 ACTIVE가 된다.
SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  ACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  ACTIVE

#기존 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 checkpoint를 발생시킨다.
SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  INACTIVE

#기존 리두 로그 그룹 4, 5를 삭제한다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE

#SQL에서 리두 로그 그룹을 DROP 하더라도 실제 OS 파일은 삭제되지 않는다.
#따라서 확인한 후 해당 리두 로그 파일을 삭제한다.
SQL> !ls /home/oracle/oradata/testdb/
control01.ctl  example01.dbf  redo02.log  redo05.log    temp01.dbf
control02.ctl  log            redo03.log  sysaux01.dbf  undotbs01.dbf
control03.ctl  redo01.log     redo04.log  system01.dbf  users01.dbf

SQL> !rm -rf /home/oracle/oradata/testdb/redo*.log

SQL> !ls /home/oracle/oradata/testdb/
control01.ctl  control03.ctl  log           system01.dbf  undotbs01.dbf
control02.ctl  example01.dbf  sysaux01.dbf  temp01.dbf    users01.dbf

SQL>

[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 24 20:13:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

#Redo log files 상태 조회
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT

#리두 로그 그룹 4 추가
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 10m;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04.log                     10 YES UNUSED

#리두 로그 그룹 4에 새 멤버 추가
SQL> alter database add logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log' to group 4;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04.log                     10 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 YES UNUSED

#다음 리두 로그 그룹을 CURRENT 상태로 만들기 위해 수동으로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 NO  CURRENT

#리두 로그 그룹 4의 멤버 삭제시 에러 발생 예제
#현재 사용중(CURRENT 상태)이기 때문에 멤버 삭제가 불가능하다.
SQL> alter database drop logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log';
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04.log'
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04_b.log'

#리두 로그 그룹 4의 그룹 및 멤버를 삭제하기 위해 리두 로그 그룹 1로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 NO  ACTIVE

#리두 로그 그룹 4는 멤버가 2개이기 때문에 ACTIVE 상태에 관계 없이 1개의 멤버는 삭제가 가능하다.
SQL> alter database drop logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log';

Database altered.

#리두 로그 그룹 4 삭제시 에러 발생 예제
#리두 로그 그룹 4가 ACTIVE 상태이기 때문에 삭제가 불가능하다.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance testdb (thread 1)
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04.log'

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  ACTIVE

#리두 로그 그룹 4의 상태를 ACTIVE에서 INACTIVE 상태로 변경한다.
#CURRENT 로그 그룹을 제외한 모든 그룹 및 멤버는 INACTIVE 상태가 된다.
SQL> alter system checkpoint;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  INACTIVE

#리두 로그 그룹 4는 INACTIVE 상태이기 때문에 에러가 발생하지 않고 삭제가 가능하다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

SQL>

Redo log group은 최소 2개, member 는 그룹당 최소 1개이다.

■ 수동 로그 스위치
SQL> alter system switch logfile;

■ 수동 checkpoint
SQL> alter system checkpoint;

■ 리두 로그 그룹 추가
SQL> alter database add logfile group 그룹번호
  2  '경로/파일명.log' size 크기;

■ 리두 로그 그룹별 멤버 추가
SQL> alter database add logfile member
  2  '경로/파일명.log' to group 그룹번호;

■ 리두 로그 그룹 삭제
SQL> alter database drop logfile group 그룹번호;

■ 리두 로그 그룹의 멤버 삭제
SQL> alter database drop logfile member
  2  '경로/파일명_b.log';

■ log file 상태 조회 (그룹번호, 멤버, 용량, 상태)
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

SQL>