7. Tablespace와 Datafile 관리하기 - Data file 이동 / 테이블스페이스 삭제

Tablespace와 Datafile 관리하기

1. Data File  이동

- 특정 디스크에 있는 데이터 파일들의 용량이 점점 증가하여 다른 더 큰 용량의 디스크를 설치 한 후 Data file을 이동시 사용.

- 절대 Datafile을 사용중일 때는 절대로 이동시키거나 복사하면 안된다.

* Tablespace를 Offline 하거나, Shutdown 한 후 작업.

1-1 Tablespace Offline 후 경로변경.

- 기존 경로 : /u01/app/oracle/oradata/PROD/

- 변경 경로 : /home/oracle/datamovedest1

                  /home/oracle/datamovedest2

1. 디렉토리 생성 [oracle@edydr1p0-PROD ~]$ pwd /home/oracle [oracle@edydr1p0-PROD ~]$ mkdir datamovedest1 [oracle@edydr1p0-PROD ~]$ mkdir datamovedest2 2. DB 접속 후 테이블 스페이스 Off Line 변경.   SYS@PROD>@tbs_auto_status   TABLESPACE   MB FILE_NAME  AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM   400 /u01/app/oracle/oradata/PROD/system01.dbf  NO  SYSTEM SYSAUX   200 /u01/app/oracle/oradata/PROD/sysaux01.dbf  NO  ONLINE UNDOTBS1   200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO  ONLINE USERS   50 /u01/app/oracle/oradata/PROD/users01.dbf NO  ONLINE EXAMPLE   100 /u01/app/oracle/oradata/PROD/example01.dbf NO  ONLINE HAKSA   20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA   20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO  ONLINE   7 rows selected.   SYS@PROD>alter tablespace haksa offline;   Tablespace altered.   SYS@PROD>@tbs_auto_status   TABLESPACE   MB FILE_NAME  AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM   400 /u01/app/oracle/oradata/PROD/system01.dbf  NO  SYSTEM SYSAUX   200 /u01/app/oracle/oradata/PROD/sysaux01.dbf  NO  ONLINE UNDOTBS1   200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO  ONLINE USERS   50 /u01/app/oracle/oradata/PROD/users01.dbf NO  ONLINE EXAMPLE   100 /u01/app/oracle/oradata/PROD/example01.dbf NO  ONLINE HAKSA       /u01/app/oracle/oradata/PROD/haksa01.dbf     OFFLINE HAKSA       /u01/app/oracle/oradata/PROD/haksa02.dbf     OFFLINE   7 rows selected.   3. Off Line 이후 해당 옮기는 경로로 데이터파일 복사   [oracle@edydr1p0-PROD script]$ cp /u01/app/oracle/oradata/PROD/haksa01.dbf /home/oracle/datamovedest1   [oracle@edydr1p0-PROD script]$ cp /u01/app/oracle/oradata/PROD/haksa02.dbf /home/oracle/datamovedest2 4. DB에서 테이블스페이스 설정경로 확인 및 변경.   SYS@PROD>select name from v$datafile;   NAME --------------------------------------------------------------- /u01/app/oracle/oradata/PROD/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /u01/app/oracle/oradata/PROD/haksa01.dbf /u01/app/oracle/oradata/PROD/haksa02.dbf   7 rows selected.  => Haksa data file기존경로     SYS@PROD>alter tablespace haksa rename     datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf'     to '/home/oracle/datamovedest1/haksa01.dbf';   Tablespace altered.   SYS@PROD>alter tablespace haksa rename    datafile '/u01/app/oracle/oradata/PROD/haksa02.dbf'     to '/home/oracle/datamovedest2/haksa02.dbf';   Tablespace altered.   SYS@PROD>select name from v$datafile;   NAME ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /home/oracle/datamovedest1/haksa01.dbf /home/oracle/datamovedest2/haksa02.dbf   7 rows selected. => 경로 설정 변경된것 확인   4. 해당 Tablespace 다시 Online으로 변경. SYS@PROD>@tbs_auto_status   TABLESPACE   MB FILE_NAME  AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM   400 /u01/app/oracle/oradata/PROD/system01.dbf  NO  SYSTEM SYSAUX   200 /u01/app/oracle/oradata/PROD/sysaux01.dbf  NO  ONLINE UNDOTBS1   200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO  ONLINE USERS   50 /u01/app/oracle/oradata/PROD/users01.dbf NO  ONLINE EXAMPLE   100 /u01/app/oracle/oradata/PROD/example01.dbf NO  ONLINE HAKSA       /home/oracle/datamovedest1/haksa01.dbf     OFFLINE HAKSA       /home/oracle/datamovedest2/haksa02.dbf     OFFLINE   7 rows selected.   SYS@PROD>alter tablespace haksa online;   Tablespace altered.   SYS@PROD>@tbs_auto_status   TABLESPACE   MB FILE_NAME  AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM   400 /u01/app/oracle/oradata/PROD/system01.dbf  NO  SYSTEM SYSAUX   200 /u01/app/oracle/oradata/PROD/sysaux01.dbf  NO  ONLINE UNDOTBS1   200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO  ONLINE USERS   50 /u01/app/oracle/oradata/PROD/users01.dbf NO  ONLINE EXAMPLE   100 /u01/app/oracle/oradata/PROD/example01.dbf NO  ONLINE HAKSA   20 /home/oracle/datamovedest1/haksa01.dbf YES ONLINE HAKSA   20 /home/oracle/datamovedest2/haksa02.dbf NO  ONLINE   7 rows selected.   5. 정보조회가 가능한지 확인. SYS@PROD>select count(*) from scott.iphak;     COUNT(*) ----------     650000

 

1-2 Offline 안되는 테이블스페이스의 Datafile이동

- System Tablespace, 사용중인 Undo Tablespace, default temporary tablespace 해당 테이블스페이스는 Offline이 불가능 하므로, DB 종료 후 진행해야한다.

- Off Line  불가능 확인

SYS@PROD>alter tablespace system offline; alter tablespace system offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary     SYS@PROD>alter tablespace undotbs1 offline; alter tablespace undotbs1 offline * ERROR at line 1: ORA-30042: Cannot offline the undo tablespace

- 실습

1. DB종료 및 Mount 단계까지 기동.   SYS@PROD>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD>startup mount   ORACLE instance started.   Total System Global Area  417546240 bytes Fixed Size     2213936 bytes Variable Size   327157712 bytes Database Buffers   83886080 bytes Redo Buffers     4288512 bytes Database mounted. 2. 변경할 디렉토리 생성 및 System datafile 복사 SYS@PROD>!mkdir /home/oracle/systemdest    SYS@PROD>!cp /u01/app/oracle/oradata/PROD/system01.dbf /home/oracle/systemdest   SYS@PROD>select instance_name from v$intance; select instance_name from v$intance                           * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only * MOUNT 상태기 때문에 조회불가능.    3. 기존 Data file 경로 확인 및 Data file 경로변경. SYS@PROD>select name from v$datafile;   NAME --------------------------------------------------------------- /u01/app/oracle/oradata/PROD/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /home/oracle/datamovedest1/haksa01.dbf /home/oracle/datamovedest2/haksa02.dbf   7 rows selected.   SYS@PROD>alter database rename     2  file '/u01/app/oracle/oradata/PROD/system01.dbf'   3  to '/home/oracle/systemdest/system01.dbf';   Database altered.   4. 경로변경 확인  및 데이터베이스 OPEN SYS@PROD>select name from v$datafile;   NAME -------------------------------------------------------------- /home/oracle/systemdest/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /home/oracle/datamovedest1/haksa01.dbf /home/oracle/datamovedest2/haksa02.dbf   7 rows selected.   SYS@PROD>alter database open;   Database altered.

 

1-3 Redo Log File  이동

- Redo Log File 또한 Offline 불가능 하므로 Database를 Mount 상태로 변경 후 작업해야함.

1. Redo log file 경로 확인 및 DB MOUNT 상태로 변경  SYS@PROD>@log          GROUP# MEMBER       MB SEQ# STATUS       ARC ------ --------------------------------------------- ---- ---------- ---------------- -----      1 /u01/app/oracle/oradata/PROD/redo01a.log       100   27 CURRENT       NO      2 /u01/app/oracle/oradata/PROD/redo02a.log       100   25 INACTIVE       YES      3 /u01/app/oracle/oradata/PROD/redo03a.log       100   26 INACTIVE       NO   SYS@PROD>select status from v$instance;   STATUS ------------ OPEN   SYS@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD>startup mount ORACLE instance started.   Total System Global Area  417546240 bytes Fixed Size     2213936 bytes Variable Size   327157712 bytes Database Buffers   83886080 bytes Redo Buffers     4288512 bytes !Database mounted. 2. 경로 이동할 디렉토리 생성 및 Redo log file  복사 SYS@PROD>! mkdir /home/oracle/redodest   SYS@PROD>! rmdir /home/oracle/redodest   SYS@PROD>! mkdir /home/oracle/redodest1   SYS@PROD>! mkdir /home/oracle/redodest2 SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo01a.log cp: missing destination file operand after `/u01/app/oracle/oradata/PROD/redo01a.log' Try `cp --help' for more information.   SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo01a.log /home/oracle/redodest1/redolog01_a.log   SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo02a.log /home/oracle/redodest1/redolog02_b.log   SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo03a.log /home/oracle/redodest1/redolog03_c.log 3. Redolog file 경로 변경. SYS@PROD>alter database rename   2  file '/u01/app/oracle/oradata/PROD/redo01a.log'   3  to '/home/oracle/redodest1/redolog01_a.log';   Database altered.   SYS@PROD>alter database rename   2  file '/u01/app/oracle/oradata/PROD/redo02a.log'     3  to '/home/oracle/redodest1/redolog02_b.log'   4  ;   Database altered. SYS@PROD>alter database rename   2  file '/u01/app/oracle/oradata/PROD/redo03a.log'   3  to '/home/oracle/redodest1/redolog03_c.log';   Database altered. 4. Redo log 경로 변경 확인   SYS@PROD>select member from v$logfile;   MEMBER --------------------------------------------- /home/oracle/redodest1/redolog01_a.log /home/oracle/redodest1/redolog02_b.log /home/oracle/redodest1/redolog03_c.log   SYS@PROD>@log   GROUP# MEMBER       MB SEQ# STATUS       ARC ------ --------------------------------------------- ---- ---------- ---------------- -----      1 /home/oracle/redodest1/redolog01_a.log       100   27 CURRENT       NO      2 /home/oracle/redodest1/redolog02_b.log       100   25 INACTIVE       YES      3 /home/oracle/redodest1/redolog03_c.log       100   26 INACTIVE       NO

 

1-4 Tablespace 삭제하기.

- 테이블스페이스에 테이블이 하나라도 있으면 그냥 안지워진다.

 - including contents and datafiles 옵션을 통해 삭제.

SYS@PROD>drop tablespace haksa; drop tablespace haksa * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option     SYS@PROD>drop tablespace haksa including contents and datafiles;   Tablespace dropped.   SYS@PROD>@tbs_auto_status   TABLESPACE   MB FILE_NAME   AUT ONLINE_ ---------- ---- -------------------------------------------------- --- ------- SYSTEM     400 /home/oracle/systemdest/system01.dbf   NO  SYSTEM SYSAUX     200 /u01/app/oracle/oradata/PROD/sysaux01.dbf   NO  ONLINE UNDOTBS1    200 /u01/app/oracle/oradata/PROD/undotbs01.dbf   NO  ONLINE USERS     50 /u01/app/oracle/oradata/PROD/users01.dbf   NO  ONLINE EXAMPLE     100 /u01/app/oracle/oradata/PROD/example01.dbf   NO  ONLINE => 학사 테이블스페이스 제거 확인.