Ghost's Oracle Database !

실습 #1 현재 Temporary Tablespace 설정 확인
[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 2 10:12:04 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.
SQL> col file_name format a50
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> col property_name format a30
SQL> col property_value format a20
SQL> col description format a40
SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace

SQL>



실습 #2 Temporary Tablespace 생성과 Default Temporary Tablespace 지정
SQL> create temporary tablespace tmp
  2  tempfile '/home/oracle/oradata/testdb/tmp01.dbf' size 10m
  3  autoextend on;

Tablespace created.

SQL> !ls -al /home/oracle/oradata/testdb/* | grep mp
-rw-r-----  1 oracle dba 20979712  2월  4 11:46 /home/oracle/oradata/testdb/temp01.dbf
-rw-r-----  1 oracle dba 10493952  3월  2 11:54 /home/oracle/oradata/testdb/tmp01.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
TMP                              10485760 /home/oracle/oradata/testdb/tmp01.dbf

SQL> alter database default temporary tablespace tmp;

Database altered.

SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP                  Name of default temporary tablespace

SQL>



실습 #3 Temporary Tablespace 삭제
SQL> drop tablespace tmp;
drop tablespace tmp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace


SQL> drop tablespace temp;

Tablespace dropped.

SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
TMP                              10485760 /home/oracle/oradata/testdb/tmp01.dbf

SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP                  Name of default temporary tablespace

SQL>

아래와 같이 데이터베이스 구성하기.
/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, system01.dbf
/home/oracle/disk4/sysaux01.dbf, undotbs01.dbf
/home/oracle/disk5/users01.dbf, example01.dbf



[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 27 21:17:14 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              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col tablespace_name format a20
SQL> col file_name format a50
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> col member format a50
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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

#데이터베이스 startup시 pfile 을 적용시키기 위해 spfile의 파일명을 수정(혹은 삭제)
#startup시 파라미터 적용 우선 순위 : spfile<SID>.ora -> 기본 spfile -> init<SID>.ora
SQL> !mv /home/oracle/product/10g/dbs/spfiletestdb.ora /home/oracle/product/10g/dbs/spfiletestdb.ora.old

#Control file 경로 수정
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora

testdb.__db_cache_size=184549376
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__shared_pool_size=88080384
testdb.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/testdb/adump'
*.background_dump_dest='/home/oracle/admin/testdb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/disk1/control01.ctl',
                '/home/oracle/disk2/control02.ctl',
                '/home/oracle/disk3/control03.ctl'
*.core_dump_dest='/home/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb'
*.db_recovery_file_dest='/home/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.job_queue_processes=10
*.local_listener='LISTENER_TESTDB'
*.open_cursors=300
*.pga_aggregate_target=94371840
"~/product/10g/dbs/inittestdb.ora" 29L, 1038C written

#연습문제와 같이 구성하기 위해 각 디스크 디렉토리 생성(디스크 추가, 마운트 및 재구성은 다음 연습문제에서...)
SQL> !mkdir -p /home/oracle/disk1/ /home/oracle/disk2/ /home/oracle/disk3/ /home/oracle/disk4/ /home/oracle/disk5/

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/disk1/control01.ctl

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/disk2/control02.ctl

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/disk3/control03.ctl

#데이터베이스 재 구성을 위해 mount 단계로 startup
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
#각 Data file을 연습문제 내용과 같이 해당 디렉토리로 이동
SQL> !mv /home/oracle/oradata/testdb/system01.dbf /home/oracle/disk3/system01.dbf

SQL> !mv /home/oracle/oradata/testdb/sysaux01.dbf /home/oracle/disk4/sysaux01.dbf

SQL> !mv /home/oracle/oradata/testdb/undotbs01.dbf /home/oracle/disk4/undotbs01.dbf

SQL> !mv /home/oracle/oradata/testdb/users01.dbf /home/oracle/disk5/users01.dbf

SQL> !mv /home/oracle/oradata/testdb/example01.dbf /home/oracle/disk5/example01.dbf

#Control file에 이동시킨 각 Data file의 경로를 새로 지정
SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/home/oracle/disk3/system01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/sysaux01.dbf'
  3  to '/home/oracle/disk4/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/undotbs01.dbf'
  3  to '/home/oracle/disk4/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/users01.dbf'
  3  to '/home/oracle/disk5/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/example01.dbf'
  3  to '/home/oracle/disk5/example01.dbf';

Database altered.

#리두 로그 그룹 1, 2, 3을 재 구성을 위한 디렉토리로 이동
#각 그룹당 멤버가 한개이기 때문에 *_a.log 파일명으로 이동
SQL> !cp /home/oracle/oradata/testdb/redo01.log /home/oracle/disk1/redo01_a.log

SQL> !cp /home/oracle/oradata/testdb/redo02.log /home/oracle/disk1/redo02_a.log

SQL> !cp /home/oracle/oradata/testdb/redo03.log /home/oracle/disk1/redo03_a.log

#Control file에 이동시킨 각 리두 로그 그룹의 경로를 새로 지정
SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo01.log'
  3  to '/home/oracle/disk1/redo01_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo02.log'
  3  to '/home/oracle/disk1/redo02_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo03.log'
  3  to '/home/oracle/disk1/redo03_a.log';

Database altered.

SQL> alter database open;

Database altered.

#연습문제 내용과 같이 리두 로그 그룹 및 멤버를 구성하기 위해 각 그룹당 멤버 한 개씩 추가
SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo01_b.log' to group 1;

Database altered.

SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo02_b.log' to group 2;

Database altered.

SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo03_b.log' to group 3;

Database altered.

SQL> select name from v$controlfile;

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

SQL> select tablespace_name, bytes, file_name from dba_data_files;

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

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                            50 NO  CURRENT
         1 /home/oracle/disk2/redo01_b.log                            50 NO  CURRENT
         2 /home/oracle/disk1/redo02_a.log                            50 NO  INACTIVE
         2 /home/oracle/disk2/redo02_b.log                            50 NO  INACTIVE
         3 /home/oracle/disk1/redo03_a.log                            50 NO  INACTIVE
         3 /home/oracle/disk2/redo03_b.log                            50 NO  INACTIVE

6 rows selected.

SQL>

테이블스페이스의 삭제

SQL> drop tablespace insa
  2  including contents and datafiles cascade constraints;

Tablespace dropped.

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/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf

SQL>

Mount 상태에서 Data file 이동

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mv /home/oracle/oradata/testdb/system01.dbf /home/oracle/oradata/system01.dbf

SQL> startup mount;
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.
SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/home/oracle/oradata/system01.dbf';

Database altered.

SQL> alter database open;

Database altered.

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/system01.dbf
EXAMPLE           104857600 /home/oracle/oradata/testdb/example01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf
INSA               20971520 /home/oracle/oradata/insa02.dbf

7 rows selected.

SQL>

OPEN 상태에서 Data file 이동

SQL> alter tablespace insa offline;

Tablespace altered.

SQL> !mv /home/oracle/oradata/testdb/insa02.dbf /home/oracle/oradata/

SQL> alter tablespace insa rename
  2  datafile '/home/oracle/oradata/testdb/insa02.dbf'
  3  to '/home/oracle/oradata/insa02.dbf';

Tablespace altered.

SQL> alter tablespace insa online;

Tablespace altered.

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
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf
INSA               20971520 /home/oracle/oradata/insa02.dbf

7 rows selected.

SQL>

사용자용 테이블스페이스 확장

SQL> alter database datafile '/home/oracle/oradata/testdb/insa01.dbf' resize 20m;

Database altered.

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
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf

6 rows selected.

SQL> alter tablespace insa
  2  add datafile '/home/oracle/oradata/testdb/insa02.dbf' size 20m;

Tablespace altered.

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
INSA               20971520 /home/oracle/oradata/testdb/insa01.dbf
INSA               20971520 /home/oracle/oradata/testdb/insa02.dbf

7 rows selected.

SQL>

사용자용 테이블스페이스 생성

SQL> create tablespace insa
  2  datafile '/home/oracle/oradata/testdb/insa01.dbf' size 10m
  3  segment space management auto;

Tablespace created.

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
INSA               10485760 /home/oracle/oradata/testdb/insa01.dbf

6 rows selected.

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
INSA             ONLINE    PERMANENT LOCAL      AUTO

7 rows selected.

SQL>

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

[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>

리두 로그 그룹을 아래와 같이 구성해본다.
　　　　　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>