[4] Tablespace 관리하기
1. tablespace 생성하기 (ts_new 생성하기)
① 조회
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
② 생성
SQL> create tablespace ts_new datafile size 10M;
SQL>
set line 200
col tablespace_name for a10
col mb for 999
col file_name for a60
select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +DATA/testdb/datafile/system.260.776365321
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW 10 +DATA/testdb/datafile/ts_new.266.776453331 ← 새로 추가됨
2. 데이터 파일 추가하기
SQL> alter tablespace ts_new add datafile size 5M;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +DATA/testdb/datafile/system.260.776365321
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW 10 +DATA/testdb/datafile/ts_new.266.776453331
TS_NEW 5 +DATA/testdb/datafile/ts_new.267.776453579
※ 참고
Cf. 테이블스페이스나 데이터파일을 추가할때, 따로 데이터파일을 지정하지 않는다.
- 기존 file system 에서
: SQL> create tablespace ts_new datafile '/DIR/ts_new01.dbf' size 10M;
SQL> alter tablespace ts_new add datafile '/DIR/ts_new02.dbf' size 5M;
- ASM 에서
: SQL> create tablespace ts_new datafile size 10M;
SQL> alter tablespace ts_new add datafile size 5M;
▶ ASM에서 파일을 관리해준다.
3. offline 되는 데이터 파일 이동하기
: ts_new 를 +DATA 에서 +FRA 로 이동시킴
① 현재 디스크 그룹 확인하기
+ASM> select name, state, type, total_mb, free_mb from v$asm_diskgroup;
NAME STATE TYPE TOTAL_MB
FREE_MB
------------------------------------------------------------ ---------------------- ------------ ---------- ----------
DATA MOUNTED EXTERN 9209
8309
FRA MOUNTED EXTERN 3074
2847
NEW_ASM MOUNTED EXTERN 2560 2510
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +DATA/testdb/datafile/system.260.776365321
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW 10 +DATA/testdb/datafile/ts_new.266.776453331
TS_NEW 5 +DATA/testdb/datafile/ts_new.267.776453579
6 rows selected.
② 해당 테이블스페이스(ts_new) offline
SQL> alter tablespace ts_new offline;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +DATA/testdb/datafile/system.260.776365321
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW +DATA/testdb/datafile/ts_new.266.776453331 ← 용량 안보임
TS_NEW +DATA/testdb/datafile/ts_new.267.776453579 ← offline 완료
③ RMAN으로 데이터 파일 복사하기
$ rman target /
rman: can't open target
※ 참고
10g에서 RMAN 실행 시키면 아래의 에러가 나는경우
$ rman target /
rman: can't open target
: 원래 실행되는 RMAN 실행 파일은 $ORACLE_HOME/bin/rman 인데 리눅스의 경우 oracle을 설치 한 후 RMAN 파일을 조회해보면,
다른 경로에 있는 경우가 있다. 아래처럼 조치해 주면됨 (못쓰는 RMAN을 쓸수있는 RMAN으로 덮어 씌운다)
$ su -
Password:
# which rman
/usr/X11R6/bin/rman
[root@server110 ~]# cp /home/oracle/product/10g/bin/rman /usr/X11R6/bin/
cp: overwrite `/usr/X11R6/bin/rman'? y
[root@server110 ~]# exit
logout
$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 28 17:50:37 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=2559693415)
RMAN> copy datafile '+DATA/testdb/datafile/ts_new.266.776453331' to '+FRA';
Starting backup at 28-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DATA/testdb/datafile/ts_new.266.776453331
output filename=+FRA/testdb/datafile/ts_new.260.776454711 tag=TAG20120228T175149 recid=1 stamp=776454711
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-FEB-12
RMAN> copy datafile '+DATA/testdb/datafile/ts_new.267.776453579' to '+FRA';
Starting backup at 28-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/testdb/datafile/ts_new.267.776453579
output filename=+FRA/testdb/datafile/ts_new.261.776454737 tag=TAG20120228T175217 recid=2 stamp=776454738
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-FEB-12
※ 참고 - 주의!!!!!
: RMAN에서 복사해주면 경로만 바뀌는게아니라 파일이름도 함께바뀐다.
SQL> alter database rename file '+DATA/testdb/datafile/ts_new.266.776453331'
2 to '+FRA/testdb/datafile/ts_new.266.776453331';
alter database rename file '+DATA/testdb/datafile/ts_new.266.776453331'
*
ERROR at line 1: ← 그런파일 없다고 에러난다.
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 5 - new file '+FRA/testdb/datafile/ts_new.266.776453331' not found
ORA-01110: data file 5: '+DATA/testdb/datafile/ts_new.266.776453331'
ORA-17503: ksfdopn:2 Failed to open file +FRA/testdb/datafile/ts_new.266.776453331
ORA-15173: entry 'ts_new.266.776453331' does not exist in directory 'datafile'
▶ 위의 copy과정에 진하게 표시해 놓은 부분을 보면 +FRA로 경로가 이동된 파일의 파일 이름도 달라 졌음을 확인할 수 있다.
④ 내용 바꿔주고 테이블스페이스(ts_new) online
SQL>
alter database rename file '+DATA/testdb/datafile/ts_new.266.776453331'
to '+FRA/testdb/datafile/ts_new.260.776454711';
Database altered.
SQL>
alter database rename file '+DATA/testdb/datafile/ts_new.267.776453579'
to '+FRA/testdb/datafile/ts_new.261.776454737';
SQL> alter tablespace ts_new online;
⑤ 경로바뀌었나 조회해 보기
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +DATA/testdb/datafile/system.260.776365321
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW 10 +FRA/testdb/datafile/ts_new.260.776454711
TS_NEW 5 +FRA/testdb/datafile/ts_new.261.776454737
6 rows selected.
4. offline 안되는 데이터 파일 이동하기
: SYSTEM 을 +FRA 로 이동하기
① 현재 상태 확인
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +DATA/testdb/datafile/system.260.776365321
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW 10 +FRA/testdb/datafile/ts_new.260.776454711
TS_NEW 5 +FRA/testdb/datafile/ts_new.261.776454737
6 rows selected.
② DB 종료 후 RMAN 으로 복사하기
SQL> shutdown immediate;
SQL> exit
$ rman target /
RMAN> startup mount;
RMAN> copy datafile '+DATA/testdb/datafile/system.260.776365321' to '+FRA';
Starting backup at 28-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/testdb/datafile/system.260.776365321
output filename=+FRA/testdb/datafile/system.262.776456133 tag=TAG20120228T181532 recid=3 stamp=776456152
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 28-FEB-12
③ 내용 바꿔주고 DB open
$ sqlplus / as sysdba
SQL>
alter database rename file '+DATA/testdb/datafile/system.260.776365321'
to '+FRA/testdb/datafile/system.262.776456133';
SQL> alter database open;
④ 경로바뀌었나 조회해 보기
SQL>
set line 200
col tablespace_name for a10
col mb for 999
col file_name for a60
select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
TABLESPACE MB FILE_NAME
---------- ---- ------------------------------------------------------------
SYSTEM 300 +FRA/testdb/datafile/system.262.776456133
UNDOTBS1 200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS 5 +DATA/testdb/datafile/users.264.776365391
TS_NEW 10 +FRA/testdb/datafile/ts_new.260.776454711
TS_NEW 5 +FRA/testdb/datafile/ts_new.261.776454737
6 rows selected.
'Oracle > Oracle - 백업&복구' 카테고리의 다른 글
| 백업 & 복구 40 - ASM : RMAN으로 백업과 복구 (0) | 2012.02.29 |
|---|---|
| 백업 & 복구 Extra - ASM : pfile과 spfile 의문점 (4) | 2012.02.29 |
| 백업 & 복구 38 - ASM : 기본 관리법 - disk 추가삭제 / disk group 추가삭제 / asmcmd (6) | 2012.02.28 |
| 백업 & 복구 37 - ASM : 개념 (0) | 2012.02.28 |
| 백업 & 복구 36 : ASM(Automatic Storage Management) - 설치 (4) | 2012.02.27 |
