Ghost's Oracle Database !

아래와 같이 데이터베이스 구성하기 (10GB 디스크 5개 추가)
/disk1/system01.dbf, sysaux01.dbf
/disk2/undotbs01.dbf
/disk3/users01.dbf, insa01.dbf, example01.dbf
/disk4/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/disk5/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log



#디스크 추가 방법은 LiNUX 카테고리의 "RHEL4 디스크 추가" 글의 내용을 참고하기 바란다.

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 28 19:51:09 2010

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

Connected to an idle instance.

#startup한 후 데이터 파일, 리두 로그 그룹/멤버를 확인한다.
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 name format a50
SQL> col member format a50
SQL> col tablespace_name format a20
SQL> col file_name format a50
SQL> select tablespace_name, bytes, file_name
  2  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  INACTIVE
         1 /home/oracle/disk2/redo01_b.log                            50 NO  INACTIVE
         2 /home/oracle/disk1/redo02_a.log                            50 NO  CURRENT
         2 /home/oracle/disk2/redo02_b.log                            50 NO  CURRENT
         3 /home/oracle/disk1/redo03_a.log                            50 NO  INACTIVE
         3 /home/oracle/disk2/redo03_b.log                            50 NO  INACTIVE

6 rows selected.

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

#파리미터 파일(inittestdb.ora) 수정
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/disk4/control01.ctl',
                '/disk5/control02.ctl'

#파리미터 파일에서 수정한 디렉토리로 파일 이동
SQL> !cp /home/oracle/disk1/control01.ctl /disk4/control01.ctl

SQL> !cp /home/oracle/disk1/control01.ctl /disk5/control02.ctl

#연습문제 내용과 같은 디렉토리로 각 파일 이동
SQL> !mv /home/oracle/disk3/system01.dbf /disk1/system01.dbf

SQL> !mv /home/oracle/disk4/sysaux01.dbf /disk1/sysaux01.dbf

SQL> !mv /home/oracle/disk4/undotbs01.dbf /disk2/undotbs01.dbf

SQL> !mv /home/oracle/disk5/users01.dbf /disk3/users01.dbf

SQL> !mv /home/oracle/disk5/example01.dbf /disk3/example01.dbf

SQL> !mv /home/oracle/disk1/redo01_a.log /disk4/redo01_a.log

SQL> !mv /home/oracle/disk2/redo01_b.log /disk5/redo01_b.log

SQL> !mv /home/oracle/disk1/redo02_a.log /disk4/redo02_a.log

SQL> !mv /home/oracle/disk2/redo02_b.log /disk5/redo02_b.log

SQL> !mv /home/oracle/disk1/redo03_a.log /disk4/redo03_a.log

SQL> !mv /home/oracle/disk2/redo03_b.log /disk5/redo03_b.log

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.
#Control file 내용 수정
SQL> alter database rename
  2  file '/home/oracle/disk3/system01.dbf'
  3  to '/disk1/system01.dbf';

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo01_a.log'
  3  to '/disk4/redo01_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo01_b.log'
  3  to '/disk5/redo01_b.log';

Database altered.

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

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo02_b.log'
  3  to '/disk5/redo02_b.log';

Database altered.

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

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo03_b.log'
  3  to '/disk5/redo03_b.log';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

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

SQL> select tablespace_name, bytes, file_name
  2  from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /disk3/users01.dbf
SYSAUX                251658240 /disk1/sysaux01.dbf
UNDOTBS1               36700160 /disk2/undotbs01.dbf
SYSTEM                503316480 /disk1/system01.dbf
EXAMPLE               104857600 /disk3/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 /disk4/redo01_a.log                                        50 NO  INACTIVE
         1 /disk5/redo01_b.log                                        50 NO  INACTIVE
         2 /disk4/redo02_a.log                                        50 NO  CURRENT
         2 /disk5/redo02_b.log                                        50 NO  CURRENT
         3 /disk4/redo03_a.log                                        50 NO  INACTIVE
         3 /disk5/redo03_b.log                                        50 NO  INACTIVE

6 rows selected.

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>

아래와 같이 재구성하기!!!
/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>