231218 Linux_데이터 이관 작업

□ offline 설정할 수 없는 테이블스페이스 

1. system

2. undo

3.temp

alter tablespace system offline;
alter tablespace undo offline;
alter tablespace temp offline;

오류발생

 

■ 모든 데이터 파일 이관작업

SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf
/u01/app/oracle/oradata/ora11g/undo1.dbf

 

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

 

 

# 데이터 이관작업( ★ 순서 중요!★)

1. 오라클 데이터베이스를 정상적인 종료

SQL> shutdown immediate

 

2. 모든 데이터파일을 새로운 디스크 위치로 이동

SELECT 'mv -v ' || name || ' /home/oracle/userdata/' FROM v$datafile;
SELECT 'mv -v ' || name || ' /home/oracle/userdata/' FROM v$tempfile;

 

# 한꺼번에 이동시, 연결연산자로 변경해주자!

mv -v /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/userdata
mv -v  /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/userdata
mv -v  /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/userdata
mv -v  /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/userdata
mv -v  /u01/app/oracle/oradata/ora11g/undo1.dbf /home/oracle/userdata

mv -v /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/userdata

 

 

# 이동된 파일 확인.

[oracle@oracle ~]$ ls -l /home/oracle/userdata/
total 1773028
-rw-r-----. 1 oracle oinstall 363077632 Dec 17 21:13 example01.dbf
-rw-r-----. 1 oracle oinstall 629153792 Dec 17 21:13 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 786440192 Dec 17 21:13 system01.dbf
-rw-r-----. 1 oracle oinstall  30416896 Dec 17 20:16 temp01.dbf
-rw-r-----. 1 oracle oinstall  25305088 Dec 17 21:13 undo1.dbf
-rw-r-----. 1 oracle oinstall   5251072 Dec 17 21:13 users01.dbf

 

3. 오라클 데이터베이스를 mount 까지만 열기.

- db open을 mount 까지만 열기(control file까지만 open)

SQL> startup mount

SQL> select status from v$instance;

 

4. 기존 데이터파일을 새로운 데이터파일로 수정

문법 : ALTER DATABASE RENAME FILE 'old' TO 'new';

# 문장 SQL Developer 에서 꾸미는 작업 하기!

SELECT 'ALTER DATABASE RENAME FILE ' || '''' || name || '''' || ' to ' || '''' || '/home/oracle/userdata/' || '''' || ';' FROM v$datafile;
SELECT 'ALTER DATABASE RENAME FILE ' || '''' || name || '''' || ' to ' || '''' || '/home/oracle/userdata/' || '''' || ';' FROM v$tempfile;

 

# putty 창에서 실행

SQL> 

alter database rename file '/u01/app/oracle/oradata/ora11g/system01.dbf' to '/home/oracle/userdata/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/oracle/userdata/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/users01.dbf' to '/home/oracle/userdata/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/example01.dbf' to '/home/oracle/userdata/example01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/undo1.dbf' to '/home/oracle/userdata/undo1.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/temp01.dbf' to '/home/oracle/userdata/temp01.dbf';

 

처음에는 한 문장씩 진행해보자!

 

SQL> select name from v$datafile;

SQL> select name from v$tempfile;

 

제대로 옮겨졌는지 확인.

 

5. 오라클 데이터베이스 open

SQL> alter database open;

( # startup 하지 말것, 정상적으로 open 하자 )

SQL> select status from v$instance;

 

SQL> select * from dba_data_files;

 

SQL> select count(*) from hr.employees;

 

---

 

# 다시 원래 위치로 이관 작업하기

1. 오라클 데이터베이스를 정상적인 종료

SQL> shutdown immediate

 

2. 모든 데이터 파일을 새로운 디스크 위치로 이동 

[oracle@oracle ~]$

mv -v /home/oracle/userdata/system01.dbf /u01/app/oracle/oradata/ora11g/

mv -v /home/oracle/userdatasysaux01.dbf  /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/users01.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/example01.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata//undo1.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/temp01.dbf  /u01/app/oracle/oradata/ora11g/

 

3. 오라클 데이터베이스를 mount 까지만 open

SQL> startup mount

SQL> select status from v$instance; 

4. 기존 데이터파일을 새로운 데이터파일로 수정

SQL> 

alter database rename file '/home/oracle/userdata/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';
alter database rename file '/home/oracle/userdata/sysaux01.dbf' to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';
alter database rename file '/home/oracle/userdata/users01.dbf' to '/u01/app/oracle/oradata/ora11g/users01.dbf';
alter database rename file '/home/oracle/userdata/example01.dbf' to '/u01/app/oracle/oradata/ora11g/example01.dbf';
alter database rename file '/home/oracle/userdata/undo1.dbf' to '/u01/app/oracle/oradata/ora11g/undo1.dbf';
alter database rename file '/home/oracle/userdata/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';

 

5. 오라클 데이터베이스 open

SQL> alter database open;

SQL> select status from v$instance;

 

SQL> select * from dba_data_files;

 

SQL> select count(*) from hr.employees;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/userdata/temp01.dbf