ASM - 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.

 

출처: https://dinggur.tistory.com/201?category=460192 [아무도없는세계]