데이터 파일 이동

일반 TableSpace의 Data File 이동

 

1) TableSpace Offline -> Offline은 헤더가 닫힌 상태

2) 물리적 이동 -> mv이지만 안전하게 cp로 이동한다.

3) Rename(논리적 rename) -> control file과 Dictionary View 경로가 바뀌었다고 알려준다.

4) TableSpace Online

5) 원본 파일 삭제

 

 

0) 먼저 mv_ts01.dbf의 tablespace를 생성한다.

SQL> create tablespace mv_ts datafile '/u01/oradata/ORCL/mv_ts01.dbf' size 100m;

Tablespace created.

SQL> @df

TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------
USERS            /u01/oradata/ORCL/users01.dbf
UNDOTBS1      /u01/oradata/ORCL/undotbs01.dbf
SYSAUX          /u01/oradata/ORCL/sysaux01.dbf
SYSTEM          /u01/oradata/ORCL/system01.dbf
EXAMPLE        /u01/oradata/ORCL/example01.dbf
TS_SAPCE        /u01/oradata/ORCL/ts_space01.dbf
RESUM_TEST    /u01/oradata/resum_test01.dbf
FKTS              /u01/oradata/ORCL/fkts01.dbf
MV_TS           /u01/oradata/ORCL/mv_ts01.dbf

 

1) Tablespace를 OFFLINE으로 만든다.

SQL> alter tablespace mv_ts offline;

Tablespace altered.

 

SQL> select tablespace_name, status
  2  from dba_tablespaces;

TABLESPACE_NAME STATUS
--------------- ---------
SYSTEM         ONLINE
SYSAUX         ONLINE
UNDOTBS1     ONLINE
TEMP            ONLINE
USERS           ONLINE
EXAMPLE        ONLINE
TEMP2           ONLINE
TS_SAPCE       ONLINE
FKTS             ONLINE
RESUM_TEST  ONLINE
MV_TS          OFFLINE

2) Data File 물리적 이동

SQL> !cp /u01/oradata/ORCL/mv_ts01.dbf /u01/oradata/mv_ts01.dbf

SQL> !ls /u01/oradata/mv_ts01.dbf
/u01/oradata/mv_ts01.dbf

 

3) 논리적 Rename

 

 

SQL> @df

TABLESPACE_NAME     FILE_NAME
---------------             ----------------------------------------
USERS  /u01/oradata/ORCL/users01.dbf
UNDOTBS1 /u01/oradata/ORCL/undotbs01.dbf
SYSAUX  /u01/oradata/ORCL/sysaux01.dbf
SYSTEM  /u01/oradata/ORCL/system01.dbf
EXAMPLE  /u01/oradata/ORCL/example01.dbf
TS_SAPCE /u01/oradata/ORCL/ts_space01.dbf
RESUM_TEST /u01/oradata/resum_test01.dbf
FKTS  /u01/oradata/ORCL/fkts01.dbf
MV_TS  /u01/oradata/ORCL/mv_ts01.dbf

9 rows selected.

SQL> alter tablespace mv_ts rename
  2  datafile '/u01/oradata/ORCL/mv_ts01.dbf'
  3  to '/u01/oradata/mv_ts01.dbf';

Tablespace altered.

SQL> @df

 

TABLESPACE_NAME     FILE_NAME
---------------             ----------------------------------------
USERS                      /u01/oradata/ORCL/users01.dbf
UNDOTBS1                /u01/oradata/ORCL/undotbs01.dbf
SYSAUX                    /u01/oradata/ORCL/sysaux01.dbf
SYSTEM                    /u01/oradata/ORCL/system01.dbf
EXAMPLE                  /u01/oradata/ORCL/example01.dbf
TS_SAPCE /                /u01/oradata/ORCL/ts_space01.dbf
RESUM_TEST              /u01/oradata/resum_test01.dbf
FKTS                        /u01/oradata/ORCL/fkts01.dbf
MV_TS                      /u01/oradata/mv_ts01.dbf

 

 

5) Tablespace Online

 

SQL> alter tablespace mv_ts online;

Tablespace altered.

SQL> select tablespace_name, status
  2  from dba_tablespaces;

 

TABLESPACE_NAME     STATUS
---------------             ---------
SYSTEM                    ONLINE
SYSAUX                    ONLINE
UNDOTBS1               ONLINE
TEMP                       ONLINE
USERS                      ONLINE
EXAMPLE                  ONLINE
TEMP2                     ONLINE
TS_SAPCE                  ONLINE
FKTS                       ONLINE
RESUM_TEST             ONLINE
MV_TS                     ONLINE

6) 원본 파일 삭제

SQL> !rm /u01/oradata/ORCL/mv_ts01.dbf

 

 

 

 

System TableSpace Data File 이동

 

1) DB를 내린다.(shutdown)

2) 물리적이동

3) DB를 MOUNT 단계까지 설정

4) Rename

5) DB OPEN

6) 원본 파일 삭제

 

** 명령어를 잘못 입력하면 DB가 깨질 수도 있다.

 

 

 

 

 

-- 먼저 SYSTEM Tablespace의 경로를 조회

SQL> @df

TABLESPACE_NAME     FILE_NAME
---------------             ----------------------------------------
USERS                      /u01/oradata/ORCL/users01.dbf
UNDOTBS1               /u01/oradata/ORCL/undotbs01.dbf
SYSAUX                    /u01/oradata/ORCL/sysaux01.dbf
SYSTEM                    /u01/oradata/ORCL/system01.dbf
EXAMPLE                  /u01/oradata/ORCL/example01.dbf
TS_SAPCE                 /u01/oradata/ORCL/ts_space01.dbf
RESUM_TEST             /u01/oradata/resum_test01.dbf
FKTS                        /u01/oradata/ORCL/fkts01.dbf
MV_TS                     /u01/oradata/mv_ts01.dbf

1) DB를 Shutdown한다.

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2) 물리적 이동

SQL> !cp /u01/oradata/ORCL/system01.dbf /u01/oradata/system01.dbf

 

3) DB MOUNT

 

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size      2253784 bytes
Variable Size   1006636072 bytes
Database Buffers   637534208 bytes
Redo Buffers      7094272 bytes
Database mounted.

4) 논리적 Rename

 

SQL> alter database rename
  2  file '/u01/oradata/ORCL/system01.dbf'
  3  to '/u01/oradata/system01.dbf';

Database altered.

 

** 일반 파일의 Rename과는 명령어가 다르다.

   alter database rename의 명령어를 사용한다.

 

5) DB OPEN

SQL> alter database open;

Database altered.

 

SQL> @df

TABLESPACE_NAME     FILE_NAME
---------------             ----------------------------------------
USERS                       /u01/oradata/ORCL/users01.dbf
UNDOTBS1                 /u01/oradata/ORCL/undotbs01.dbf
SYSAUX                     /u01/oradata/ORCL/sysaux01.dbf
SYSTEM                     /u01/oradata/system01.dbf            -> 경로가 바뀜
EXAMPLE                   /u01/oradata/ORCL/example01.dbf
TS_SAPCE                   /u01/oradata/ORCL/ts_space01.dbf
RESUM_TEST               /u01/oradata/resum_test01.dbf
FKTS                         /u01/oradata/ORCL/fkts01.dbf
MV_TS                       /u01/oradata/mv_ts01.dbf

 

6) 원본 파일 삭제

 

SQL> !rm /u01/oradata/ORCL/system01.dbf

SQL> !ls /u01/oradata/ORCL          
control01.ctl  fkts01.dbf  redo03.log  temp2.dbf  users01.dbf
control02.ctl  redo01.log  sysaux01.dbf  ts_space01.dbf
example01.dbf  redo02.log  temp01.dbf  undotbs01.dbf