UIStory: 12월 2007

- 데이터 파일의 위치를 변경하여 오라클을 올려보겠다.

- 이동파일은 데이터 파일 + redo log 파일이다.

- 테스트 환경은 : redhat9 + oracle 9.2.0.4 / vmware 상에서 실시

- 현재 dbf 파일들이 OS 영역에 위치하고 있으므로 디스크 하나 추가후 oradata로 마운트하고 datafile 과 redolog 파일의 위치를 바꾸어 오라클을 정상 가동 시키겠다.

- 현재상황

Filesystem    Size Used Avail Use% Mounted on
/dev/sda3    14G 8.4G 4.6G 65% /
/dev/sda1    99M 9.3M 85M 10% /boot
/dev/sdb1 5.0G 2.1G 2.6G 45% /backup
none    147M 0 147M 0% /dev/shm

- 데이터 파일 위치

SQL> select name from v$datafile ;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undotbs01.dbf
/oracle/oradata/orcl/cwmlite01.dbf
/oracle/oradata/orcl/drsys01.dbf
/oracle/oradata/orcl/example01.dbf
/oracle/oradata/orcl/indx01.dbf
/oracle/oradata/orcl/odm01.dbf
/oracle/oradata/orcl/tools01.dbf
/oracle/oradata/orcl/users01.dbf
/oracle/oradata/orcl/xdb01.dbf

10 rows selected.

- redo log 파일 위치

SQL> col member format a45
SQL> select * from v$logfile ;

GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ---------------------------------------------
   3 ONLINE /oracle/oradata/orcl/redo03.log
   2 ONLINE /oracle/oradata/orcl/redo02.log
   1 ONLINE /oracle/oradata/orcl/redo01.log

- controlfile 위치

SQL> col name format a40
SQL> select * from v$controlfile ;

STATUS NAME
------- ----------------------------------------
/oracle/oradata/orcl/control01.ctl
/oracle/oradata/orcl/control02.ctl
/oracle/oradata/orcl/control03.ctl

- 모두 /oracle/oradata/orcl/ 밑에 위치해 있음을 알수 있다.

- datafile 들은 /oradata/datafile 밑에 위치시키고

- redolog file은 /oradata/redolog 밑에 위치시키고

- control file 은 현재의 디렉토리와 /oradata/controlfile/ 밑에 하나를 위치시켜겠다.

- 오라클을 정상종료 시킨다.

[oracle@oracle oracle]$ sh oracle_stop.sh

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Mar 16 09:55:26 2007

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 16-MAR-2007 09:55:33

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
###################################################################
###################### oracle daemon check ########################
###################################################################
oracle    1473 1472 0 09:37 pts/0    00:00:00 -bash
oracle    1604 1473 0 09:55 pts/0    00:00:00 sh oracle_stop.sh
oracle    1608 1604 0 09:55 pts/0    00:00:00 ps -ef
oracle    1609 1604 0 09:55 pts/0    00:00:00 grep ora

- 시스템(리눅스)상에서 디스크 추가 후 /oradata 로 마운트시킨다.(생략)

- 마운트 하고 나서의 정보

Filesystem    Size Used Avail Use% Mounted on
/dev/sda3    14G 8.4G 4.6G 65% /
/dev/sda1    99M 9.3M 85M 10% /boot
/dev/sdb1 5.0G 2.1G 2.6G 45% /backup
none    147M 0 147M 0% /dev/shm
/dev/sdc1 5.0G 33M 4.7G 1% /oradata <-- 추가된것

- 시스템 명령을 이용하여 dbf 파일과 redolog 파일과 controlfile 을 copy한다(copy할것, 괜히 move 했다가 안올라오면 원복시켜야함으로)

[oracle@oracle oracle]$ more move.sh

#!/bin/bash

cp /oracle/oradata/orcl/system01.dbf /oradata/datafile
cp /oracle/oradata/orcl/undotbs01.dbf /oradata/datafile
cp /oracle/oradata/orcl/cwmlite01.dbf /oradata/datafile
cp /oracle/oradata/orcl/drsys01.dbf /oradata/datafile
cp /oracle/oradata/orcl/example01.dbf /oradata/datafile
cp /oracle/oradata/orcl/indx01.dbf /oradata/datafile
cp /oracle/oradata/orcl/odm01.dbf /oradata/datafile
cp /oracle/oradata/orcl/tools01.dbf /oradata/datafile
cp /oracle/oradata/orcl/users01.dbf /oradata/datafile
cp /oracle/oradata/orcl/xdb01.dbf /oradata/datafile

cp /oracle/oradata/orcl/redo03.log /oradata/redolog
cp /oracle/oradata/orcl/redo02.log /oradata/redolog
cp /oracle/oradata/orcl/redo01.log /oradata/redolog

cp /oracle/oradata/orcl/control03.ctl /oradata/controlfile

- 왠만하면 스크립터를 간단히 만들어서 수행하기 바란다. 나중에 내가 멀했는지 모르는건 골치아프다.

- 자 이제 pfile(pfile 로 오라클 가동중이였으므로) 을 고쳐서 controlfile 의 위치를 수정해주고 오라클을 마운트 단계까지 올린다.

[oracle@oracle dbs]$ vi initorcl.ora

*.aq_tm_processes=1
*.background_dump_dest='/oracle/admin/orcl/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl','/oradata/controlfile/control03.ctl'

SQL> startup mount
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>

- 데이터베이스 이름을 rename 한다. 이것도 간단한 script 를 만들어서 수행한다.

[oracle@oracle oracle]$ more rename.sh

alter database rename file '/oracle/oradata/orcl/system01.dbf' to '/oradata/datafile/system01.dbf' ;
alter database rename file '/oracle/oradata/orcl/undotbs01.dbf' to '/oradata/datafile/undotbs01.dbf';
alter database rename file '/oracle/oradata/orcl/cwmlite01.dbf'to '/oradata/datafile/cwmlite01.dbf' ;
alter database rename file '/oracle/oradata/orcl/drsys01.dbf' to '/oradata/datafile/drsys01.dbf' ;
alter database rename file '/oracle/oradata/orcl/example01.dbf' to '/oradata/datafile/example01.dbf' ;
alter database rename file '/oracle/oradata/orcl/indx01.dbf' to '/oradata/datafile/indx01.dbf' ;
alter database rename file '/oracle/oradata/orcl/odm01.dbf' to '/oradata/datafile/odm01.dbf' ;
alter database rename file '/oracle/oradata/orcl/tools01.dbf' to '/oradata/datafile/tools01.dbf' ;
alter database rename file '/oracle/oradata/orcl/users01.dbf' to '/oradata/datafile/users01.dbf' ;
alter database rename file '/oracle/oradata/orcl/xdb01.dbf' to '/oradata/datafile/xdb01.dbf' ;

alter database rename file '/oracle/oradata/orcl/redo03.log' to '/oradata/redolog/redo03.log' ;
alter database rename file '/oracle/oradata/orcl/redo02.log' to '/oradata/redolog/redo02.log';
alter databsse rename file '/oracle/oradata/orcl/redo01.log' to '/oradata/redolog/redo01.log';

SQL> @rename.sh

Database altered.

SQL> alter database open ;

Database altered.

- 자 이제 다 잘돼서 올라왔다. 정보를 확인해보고 기존의 dbf 파일과 redolog 파일은 지우든지 내버려두든지 알아서 하면 된다(내버려두면 cold 백업본이 되는 셈이다.)

SQL> select name from v$datafile
2 ;

NAME
--------------------------------------------------------------------------------
/oradata/datafile/system01.dbf
/oradata/datafile/undotbs01.dbf
/oradata/datafile/cwmlite01.dbf
/oradata/datafile/drsys01.dbf
/oradata/datafile/example01.dbf
/oradata/datafile/indx01.dbf
/oradata/datafile/odm01.dbf
/oradata/datafile/tools01.dbf
/oradata/datafile/users01.dbf
/oradata/datafile/xdb01.dbf

SQL> select * from v$logfile ;

GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ---------------------------------------------
   3 STALE ONLINE /oradata/redolog/redo03.log
   2 ONLINE /oradata/redolog/redo02.log
   1 ONLINE /oracle/oradata/orcl/redo01.log

- 들고갈때 덧글.. 출처를 명시해 주기 바랍니다.