종합 연습문제 (Controlfile, Redo log file, Tablespace)

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