검색결과 리스트
리두로그파일경로변경에 해당되는 글 1건
글
Tablespace와 Datafile 관리하기
1. Data File 이동
- 특정 디스크에 있는 데이터 파일들의 용량이 점점 증가하여 다른 더 큰 용량의 디스크를 설치 한 후 Data file을 이동시 사용.
- 절대 Datafile을 사용중일 때는 절대로 이동시키거나 복사하면 안된다.
* Tablespace를 Offline 하거나, Shutdown 한 후 작업.
1-1 Tablespace Offline 후 경로변경.
- 기존 경로 : /u01/app/oracle/oradata/PROD/
- 변경 경로 : /home/oracle/datamovedest1
/home/oracle/datamovedest2
1. 디렉토리 생성 [oracle@edydr1p0-PROD ~]$ pwd /home/oracle [oracle@edydr1p0-PROD ~]$ mkdir datamovedest1 [oracle@edydr1p0-PROD ~]$ mkdir datamovedest2 2. DB 접속 후 테이블 스페이스 Off Line 변경.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa01.dbf YES ONLINE HAKSA 20 /u01/app/oracle/oradata/PROD/haksa02.dbf NO ONLINE
7 rows selected.
SYS@PROD>alter tablespace haksa offline;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /u01/app/oracle/oradata/PROD/haksa01.dbf OFFLINE HAKSA /u01/app/oracle/oradata/PROD/haksa02.dbf OFFLINE
7 rows selected.
3. Off Line 이후 해당 옮기는 경로로 데이터파일 복사
[oracle@edydr1p0-PROD script]$ cp /u01/app/oracle/oradata/PROD/haksa01.dbf /home/oracle/datamovedest1 [oracle@edydr1p0-PROD script]$ cp /u01/app/oracle/oradata/PROD/haksa02.dbf /home/oracle/datamovedest2 4. DB에서 테이블스페이스 설정경로 확인 및 변경.
SYS@PROD>select name from v$datafile;
NAME --------------------------------------------------------------- /u01/app/oracle/oradata/PROD/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /u01/app/oracle/oradata/PROD/haksa01.dbf /u01/app/oracle/oradata/PROD/haksa02.dbf
7 rows selected. => Haksa data file기존경로
SYS@PROD>alter tablespace haksa rename datafile '/u01/app/oracle/oradata/PROD/haksa01.dbf' to '/home/oracle/datamovedest1/haksa01.dbf';
Tablespace altered.
SYS@PROD>alter tablespace haksa rename datafile '/u01/app/oracle/oradata/PROD/haksa02.dbf' to '/home/oracle/datamovedest2/haksa02.dbf';
Tablespace altered.
SYS@PROD>select name from v$datafile;
NAME ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/PROD/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /home/oracle/datamovedest1/haksa01.dbf /home/oracle/datamovedest2/haksa02.dbf
7 rows selected. => 경로 설정 변경된것 확인
4. 해당 Tablespace 다시 Online으로 변경. SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA /home/oracle/datamovedest1/haksa01.dbf OFFLINE HAKSA /home/oracle/datamovedest2/haksa02.dbf OFFLINE
7 rows selected.
SYS@PROD>alter tablespace haksa online;
Tablespace altered.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---------- -------------------------------------------------- --- ------- SYSTEM 400 /u01/app/oracle/oradata/PROD/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE HAKSA 20 /home/oracle/datamovedest1/haksa01.dbf YES ONLINE HAKSA 20 /home/oracle/datamovedest2/haksa02.dbf NO ONLINE
7 rows selected.
5. 정보조회가 가능한지 확인. SYS@PROD>select count(*) from scott.iphak;
COUNT(*) ---------- 650000 |
1-2 Offline 안되는 테이블스페이스의 Datafile이동
- System Tablespace, 사용중인 Undo Tablespace, default temporary tablespace 해당 테이블스페이스는 Offline이 불가능 하므로, DB 종료 후 진행해야한다.
- Off Line 불가능 확인
SYS@PROD>alter tablespace system offline; alter tablespace system offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary
SYS@PROD>alter tablespace undotbs1 offline; alter tablespace undotbs1 offline * ERROR at line 1: ORA-30042: Cannot offline the undo tablespace
|
- 실습
1. DB종료 및 Mount 단계까지 기동.
SYS@PROD>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD>startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted. 2. 변경할 디렉토리 생성 및 System datafile 복사 SYS@PROD>!mkdir /home/oracle/systemdest
SYS@PROD>!cp /u01/app/oracle/oradata/PROD/system01.dbf /home/oracle/systemdest
SYS@PROD>select instance_name from v$intance; select instance_name from v$intance * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only * MOUNT 상태기 때문에 조회불가능.
3. 기존 Data file 경로 확인 및 Data file 경로변경. SYS@PROD>select name from v$datafile;
NAME --------------------------------------------------------------- /u01/app/oracle/oradata/PROD/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /home/oracle/datamovedest1/haksa01.dbf /home/oracle/datamovedest2/haksa02.dbf
7 rows selected.
SYS@PROD>alter database rename 2 file '/u01/app/oracle/oradata/PROD/system01.dbf' 3 to '/home/oracle/systemdest/system01.dbf';
Database altered.
SYS@PROD>select name from v$datafile;
NAME -------------------------------------------------------------- /home/oracle/systemdest/system01.dbf /u01/app/oracle/oradata/PROD/sysaux01.dbf /u01/app/oracle/oradata/PROD/undotbs01.dbf /u01/app/oracle/oradata/PROD/users01.dbf /u01/app/oracle/oradata/PROD/example01.dbf /home/oracle/datamovedest1/haksa01.dbf /home/oracle/datamovedest2/haksa02.dbf
7 rows selected.
SYS@PROD>alter database open;
Database altered. |
1-3 Redo Log File 이동
- Redo Log File 또한 Offline 불가능 하므로 Database를 Mount 상태로 변경 후 작업해야함.
1. Redo log file 경로 확인 및 DB MOUNT 상태로 변경 SYS@PROD>@log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 27 CURRENT NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 25 INACTIVE YES 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 26 INACTIVE NO
SYS@PROD>select status from v$instance;
STATUS ------------ OPEN
SYS@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD>startup mount ORACLE instance started.
Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes !Database mounted. 2. 경로 이동할 디렉토리 생성 및 Redo log file 복사 SYS@PROD>! mkdir /home/oracle/redodest
SYS@PROD>! rmdir /home/oracle/redodest
SYS@PROD>! mkdir /home/oracle/redodest1
SYS@PROD>! mkdir /home/oracle/redodest2 SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo01a.log cp: missing destination file operand after `/u01/app/oracle/oradata/PROD/redo01a.log' Try `cp --help' for more information.
SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo01a.log /home/oracle/redodest1/redolog01_a.log
SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo02a.log /home/oracle/redodest1/redolog02_b.log
SYS@PROD>!cp /u01/app/oracle/oradata/PROD/redo03a.log /home/oracle/redodest1/redolog03_c.log 3. Redolog file 경로 변경. SYS@PROD>alter database rename 2 file '/u01/app/oracle/oradata/PROD/redo01a.log' 3 to '/home/oracle/redodest1/redolog01_a.log';
Database altered.
SYS@PROD>alter database rename 2 file '/u01/app/oracle/oradata/PROD/redo02a.log' 3 to '/home/oracle/redodest1/redolog02_b.log' 4 ;
Database altered. SYS@PROD>alter database rename 2 file '/u01/app/oracle/oradata/PROD/redo03a.log' 3 to '/home/oracle/redodest1/redolog03_c.log';
Database altered. 4. Redo log 경로 변경 확인
SYS@PROD>select member from v$logfile;
MEMBER --------------------------------------------- /home/oracle/redodest1/redolog01_a.log /home/oracle/redodest1/redolog02_b.log /home/oracle/redodest1/redolog03_c.log
SYS@PROD>@log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /home/oracle/redodest1/redolog01_a.log 100 27 CURRENT NO 2 /home/oracle/redodest1/redolog02_b.log 100 25 INACTIVE YES 3 /home/oracle/redodest1/redolog03_c.log 100 26 INACTIVE NO
|
1-4 Tablespace 삭제하기.
- 테이블스페이스에 테이블이 하나라도 있으면 그냥 안지워진다.
- including contents and datafiles 옵션을 통해 삭제.
SYS@PROD>drop tablespace haksa; drop tablespace haksa * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SYS@PROD>drop tablespace haksa including contents and datafiles;
Tablespace dropped.
SYS@PROD>@tbs_auto_status
TABLESPACE MB FILE_NAME AUT ONLINE_ ---------- ---- -------------------------------------------------- --- ------- SYSTEM 400 /home/oracle/systemdest/system01.dbf NO SYSTEM SYSAUX 200 /u01/app/oracle/oradata/PROD/sysaux01.dbf NO ONLINE UNDOTBS1 200 /u01/app/oracle/oradata/PROD/undotbs01.dbf NO ONLINE USERS 50 /u01/app/oracle/oradata/PROD/users01.dbf NO ONLINE EXAMPLE 100 /u01/app/oracle/oradata/PROD/example01.dbf NO ONLINE => 학사 테이블스페이스 제거 확인.
|
'Oracle 참고서적 정리 > 오라클 관리실무' 카테고리의 다른 글
7.Tablespace와 Datafile 관리하기 - Undo Tablespace / (1) | 2015.07.30 |
---|---|
7. Tablespace와 Data File 관리하기. (0) | 2015.07.16 |
6. Redo Log 관리하기 - SCN과 Checkpoint (0) | 2015.07.16 |
6-3. Redo Log 관리하기 - 실습 (0) | 2015.07.16 |
6-2. Redo Log File 구성 및 관리하기. (0) | 2015.06.18 |
RECENT COMMENT