종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)

아래와 같이 데이터베이스 구성하기 (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>