Tablespace File 이동

1. redo log 이동

 a. db shutdown

  - SQL>shutdown immediate;

 b. redo log file 이동

  - $cp /app/oracle/disk4/redo01_a.log /app/oracle/disk1/redo01_a.log

    $cp /app/oracle/disk4/redo02_a.log /app/oracle/disk1/redo02_a.log

    $cp /app/oracle/disk4/redo03_a.log /app/oracle/disk1/redo03_a.log

    $cp /app/oracle/disk5/redo01_b.log /app/oracle/disk2/redo01_b.log

    $cp /app/oracle/disk5/redo02_b.log /app/oracle/disk2/redo02_b.log

    $cp /app/oracle/disk5/redo03_b.log /app/oracle/disk2/redo03_b.log

 c. db mount

  - startup mount;

 d. 위치 정보 수정

  - SQL>alter database rename file '/app/oracle/disk4/redo01_a.log' to '/app/oracle/disk1/redo01_a.log';

    SQL>alter database rename file '/app/oracle/disk4/redo02_a.log' to '/app/oracle/disk1/redo02_a.log';

    SQL>alter database rename file '/app/oracle/disk4/redo03_a.log' to '/app/oracle/disk1/redo03_a.log';

    SQL>alter database rename file '/app/oracle/disk5/redo01_b.log' to '/app/oracle/disk2/redo01_b.log';

    SQL>alter database rename file '/app/oracle/disk5/redo02_b.log' to '/app/oracle/disk2/redo02_b.log';

    SQL>alter database rename file '/app/oracle/disk5/redo03_b.log' to '/app/oracle/disk2/redo03_b.log';

 e. db open

  - alter database open;

 f. 확인

  - SQL>select member from v$logfile;

MEMBER

---------------------------------------------

/app/oracle/disk1/redo03_a.log

/app/oracle/disk1/redo02_a.log

/app/oracle/disk1/redo01_a.log

/app/oracle/disk2/redo01_b.log

/app/oracle/disk2/redo02_b.log

/app/oracle/disk2/redo03_b.log

2. control01, control02, control03 이동

 a. 현재 control file 조회

  - SQL>select name from v$controlfile;

NAME

--------------------------------

/home/oracle/disk4/control01.ctl

/home/oracle/disk5/control02.ctl

/home/oracle/disk6/control03.ctl

 b. pfile 또는 spfile에 정보 수정

  - spfile일 경우

    - SQL>alter system set control_files='/app/oracle/disk1/control01.ctl', /app/oracle/disk2/control02.ctl', /app/oracle/disk3/control03.ctl' scope=spfile;

  - pfile일 경우(DB 먼저 shutdown)

    - pfile에서 경로 수정 후 저장

 c. db shutdown

  - SQL>shutdown immediate

 d. 파일 이동

  - $cp /home/oracle/disk4/control01.ctl /app/oracle/disk1/control01.ctl

    $cp /home/oracle/disk5/control02.ctl /app/oracle/disk2/control02.ctl

    $cp /home/oracle/disk6/control03.ctl /app/oracle/disk3/control03.ctl

 e. db open

  - SQL>startup;

* 현재 pfile 인지 spfile인지 확인

 - SQL>show parameter pfile;

SQL> show parameter pfile;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile     string

 - VALUE에 경로가 있으면 spfile, 없으면 pfile

3. system01, undotbs01, sysaux01 이동

 a. db shutdown

  - SQL>shutdown immediate;

 b. db mount

  - startup mount;

 c. 파일 이동

  - $cp /app/oracle/disk3/system01.dbf /app/oracle/disk4/system01.dbf

    $cp /app/oracle/oradata/testdb/undotbs01.dbf /app/oracle/disk3/undotbs01.dbf

    $cp /app/oracle/oradata/testdb/sysaux01.dbf /app/oracle/disk4/sysaux01.dbf

 d. 정보 확인 및 수정

  - select name from v$datafile;

NAME

----------------------------------------

/app/oracle/disk3/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf

  - SQL>alter database rename file '/app/oracle/disk3/system01.dbf' to '/app/oracle/disk4/system01.dbf';

    SQL>alter database rename file '/app/oracle/oradata/testdb/undotbs01.dbf' to '/app/oracle/disk3/undotbs01.dbf';

    SQL>alter database rename file '/app/oracle/oradata/testdb/sysaux01.dbf' to '/app/oracle/disk4/sysaux01.dbf';

 e. db open

  - SQL>alter database open;

4. users01, example01 이동

 a. tablespace offline

  - SQL>alter tablespace users offline;

    SQL>alter tablespace example offline;

 b. 파일 이동

  - $cp /app/oracle/oradata/testdb/users01.dbf /app/oracle/disk5/users01.dbf

    $cp /app/oracle/oradata/testdb/example01.dbf /app/oracle/disk5/example01.dbf

 c. control 파일 정보 수정

  - SQL>alter tablespace users rename datafile '/app/oracle/oradata/testdb/users01.dbf' to '/app/oracle/disk5/users01.dbf';

    SQL>alter tablespace example rename datafile '/app/oracle/oradata/testdb/example01.dbf' to '/app/oracle/disk5/example01.dbf';

 d. tablespace online

  - SQL>alter tablespace users online;

    SQL>alter tablespace example online;

*tablespace file 확인

SQL>select name from v$datafile;

NAME

----------------------------------------

/app/oracle/disk3/system01.dbf

/app/oracle/oradata/testdb/sysaux01.dbf

/app/oracle/oradata/testdb/undotbs01.dbf

/app/oracle/oradata/testdb/users01.dbf

/app/oracle/oradata/testdb/example01.dbf