데이터 이관 작업 전 상태로 원상 복구

1. 준비
1) spfil 위치 확인

show parameter spfile

 

2) pfile 생성

create pfile from spfile;

3) 백업본 체크
[oracle@oracle ~]$ cd backup/noarch/20240110/
[oracle@oracle 20240110]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

4) 데이터파일, 리두로그파일 위치변경 작업

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

5) temp file 위치 변경

alter database rename file '/home/oracle/ora_data/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';

6) redo log file 위치 변경

alter database rename file '/home/oracle/ora_data/redo03.log' to '/u01/app/oracle/oradata/ora11g/redo03.log';
alter database rename file '/home/oracle/ora_data/redo02.log' to '/u01/app/oracle/oradata/ora11g/redo02.log';
alter database rename file '/home/oracle/ora_data/redo01.log' to '/u01/app/oracle/oradata/ora11g/redo01.log';

7) pfile 수정을 위한 컨트롤 파일 위치 확보
(1) 이전 컨트롤 파일 위치
*.control_files='/home/oracle/ora_data/control01.ctl'

(2) 바꿀 컨트롤 파일 위치
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'

2. 파일 이관
1) 정상적인 종료 

shutdown immediate

2)파일 이관
cp -av /home/oracle/ora_data/*.* /u01/app/oracle/oradata/ora11g

3) pfile 수정
[oracle@oracle ora11g]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  init.ora  initora11g.ora  lkORA11G  orapwora11g  spfileora11g.ora
[oracle@oracle dbs]$ vi initora11g.ora
(1) 바꿀 위치: *.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'

4) startup (pfile로 mount까지)

startup pfile='$ORACLE_HOME/dbs/initora11g.ora' mount

5) 시스템의 TBS들 ALTER하기 ★
(1) 데이터파일, 리두로그파일 위치변경 작업

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

(2) temp file도 바꿔야함

alter database rename file '/home/oracle/ora_data/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';

(3) redo log file도 바꿔야함

alter database rename file '/home/oracle/ora_data/redo03.log' to '/u01/app/oracle/oradata/ora11g/redo03.log';
alter database rename file '/home/oracle/ora_data/redo02.log' to '/u01/app/oracle/oradata/ora11g/redo02.log';
alter database rename file '/home/oracle/ora_data/redo01.log' to '/u01/app/oracle/oradata/ora11g/redo01.log';

 => 모두 Database altered.

6) 데이터베이스 open

alter database open;

7) 파일들에 대한 위치정보 조회 (변경된 이력정보 확인)

SELECT tablespace_name, file_name FROM dba_data_files;
SELECT * FROM v$datafile;
SELECT * FROM v$logfile;
SELECT * FROM v$controlfile;
SELECT * FROM v$tempfile;

8) spfile 생성하기

create spfile from pfile;

9) shutdown - startup 하기

shutdown immediate
startup

10) 완료