control file, redo log file, data file, temp file 이관

SELECT name FROM v$controlfile;
SELECT member FROM v$logfile;
SELECT name FROM v$datafile;
SELECT name FROM v$tempfile;

1. file 새로운 위치로 변경
   

   /u01/app/oracle/oradata/ORA19C/control01.ctl →
   /home/oracle/userdata/control01.ctl

   1) control files 파라미터 변경
   ALTER SYSTEM SET control_files = '/home/oracle/userdata/control01.ctl' SCOPTE = SPFILE;

   2) 데이터베이스 정상적인 종료
   shutdown immediate

   3) 물리적 control file 이동
   mv -v /u01/app/oracle/oradata/ORA19C/control01.ctl /home/oracle/userdata/control01.ctl

   4) 물리적 data file 이동

   mv -v /u01/app/oracle/oradata/ORA19C/system01.dbf /home/oracle/userdata
   mv -v /u01/app/oracle/oradata/ORA19C/sysaux01.dbf /home/oracle/userdata
   mv -v /u01/app/oracle/oradata/ORA19C/undotbs01.dbf /home/oracle/userdata
   mv -v /u01/app/oracle/oradata/ORA19C/insa_tbs.dbf /home/oracle/userdata
   mv -v /u01/app/oracle/oradata/ORA19C/users01.dbf /home/oracle/userdata

   5) 물리적 log file 이동

   mv -v /u01/app/oracle/oradata/ORA19C/redo03.log /home/oracle/userdata
   mv -v /u01/app/oracle/oradata/ORA19C/redo02.log /home/oracle/userdata
   mv -v /u01/app/oracle/oradata/ORA19C/redo01.log /home/oracle/userdata

   6) 물리적 temp file 이동
   mv -v /u01/app/oracle/oradata/ORA19C/temp01.dbf /home/oracle/userdata

2. mount 단계 까지만 DB 열기
   startup mount

3. 리두 로그파일, 데이터파일, 임시파일을 새로운 위치로 변경

4. DB OPEN 단계로 변경

5. 디렉터리 파일위치 확인

---

show parameter db_files;
db_files : 데이터베이스에서 생성할 수 있는 데이터 파일의 수 파라미터

Big File 테이블스페이스

• 테이블스페이스에 단일 파일 하나만 포함한다.
• 최대 40(2**32)억개의 블록을 포함할 수 있다.
• 8k 블록일 경우 최대 파일의 크기는 32TBYTE
• 32K 블록일 경우 최대 파일의 크기는 128TBYTE
• OS가 파일의 크기를 뒷받침 되어야 한다.
• LINUX 같은 경우 최대 30G 이기때문에 의미가 없다.

CREATE BIGFILE TABLESPACE big_tbs
DATAFILE ' ' SIZE 1G
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

---

ROW ID 환산

select rowid , 
    dbms_rowid.rowid_object(rowid) as data_object_id, -- 오브젝트 ID
    dbms_rowid.rowid_relative_fno(rowid) as file_id, -- 데이터파일 ID
    dbms_rowid.rowid_block_number(rowid) as block_id, -- 블록 ID
    dbms_rowid.rowid_row_number(rowid) as row_slot, -- 로우 슬롯 ID
    employee_id
from hr.employees;

SHARED CURSOR 실행계획 확인

alter system flush shared_pool; -- shared_pool 초기화

SELECT sql_id, sql_text, parse_calls, loads, executions
FROM v$sql
WHERE sql_text LIKE '%hr.employees%'
AND sql_text not LIKE '%v$sql%';

SELECT * FROM hr.employees WHERE employee_id = 100;

parse_calls , executions : 해당 실행계획을 parse, execute 한 횟수
loads : 하드파싱 한 횟수

세부 실행계획 확인

SELECT * FROM TABLE(dbms_xplan.display_cursor('SQL_ID'));

---

UNDO

• DML작업식에 이전값을 저장하는 공간
• 트랜잭션이 종료(commit, rollback)될때 까지는 이전값을 보존해야 한다.
• 목적
  • rollback
  • 읽기 일관성(Read Consistent)
  • flashback query
  • 실패한 트랜잭션에 대해서 복구
• 한 세션에서 한번 할당받은 undo segment는 용량이 꽉 차더라도 다른 세그먼트로 변경되지 않는다. 오류발생
• 한번 RESIZE된 UNDO SEGMENT는 다시 줄어들지 않는다.

undo monitoring

• undo 공간이 부족한 경우 ORA-01650 : unable to extend rollback segment , ORA-30036: 세그먼트를 8만큼 실행 취소 테이블스페이스 'UNDO1'에서 확장할 수 없음
• 읽기 일관성이 어긋난 경우, 즉 long query문장에서 ORA-01555: snapshot too old error
  • undo 공간이 부족해서 그렇다.
• undo segment를 할당받을때 까지 발생하는 이벤트는 enq:US-contention wait event 발생

undo 공간이 부족할 경우 해결방법

• 기존 undo tablespace에 데이터 파일 추가

ALTER TABLESPACE undotbs1 ADD DATAFIE '/u01/app/oracle/oradata/ORA19C/undotbs02.dbf' SIZE 10M AUTOEXTEND ON

새로운 UNDO tablespace 생성

• SEGMENT SPACE MENAGEMENT MANUAL 방식 사용, PCTUSED, FREELISTS를 사용해야한다.

1. 생성

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/app/oracle/oradata/ORA19C/undo01.dbf' SIZE 10M
AUTOEXTEND ON;

- extent 와 segment를 설정하려고 하면 오류 발생

• dba_tablepsaces 딕셔너리 뷰를 확인해보면 CONTENTS가 UNDO 로 되어있는걸 확인할 수 있다.
  
  - PERMANENT는 일반 테이블스페이스이다.

2. 변경 테이블 스페이스로 파라미터 설정

ALTER SYSTEM SET undo_tablespace = undo1 SCOPE = BOTH;

- undo_tablespace 는 동적 파라미터이기 때문에 메모리에서 바로 변경가능
- undo_tablespace는 테이블스페이스 1개만 설정해야 한다.

3. 파라미터 확인

show parameter undo_tablespace;

4. undo segment 확인

SELECT segment_id, segment_name, owner, tablespace_name, status
FROM dba_rollback_segs;

- system undo는 dictionary 테이블에 대한 변경작업시에 사용된다
- 예를 들어 CREATE USER를 하게 될 경우 USER 딕셔너리에 변경 작업이 발생된다.

5. 기존 undo 테이블 스페이스의 상태가 offline이 되고 새롭게 설정된 undo 테이블스페이스 상태가 online이 된걸 확인 할수 있다. 이후 새롭게 트랜잭션을 발생되면 ONLINE 세그먼트에 할당된다.

UPDATE hr.employees
SET salary = salary * 1.1
WHERE employee_id = 100;

SELECT n.usn,n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

- rssize는 undo segment의 사이즈, xacts는 해당 세그먼트에 연결된 트랜잭션 갯수
- 트랜잭션이 종료되면 자동으로 해제된다.

6. 트랜잭션 정보 확인

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

- 어느 유저에서 유입된 트랜잭션인지 확인할 때 사용된다.
- xidusn : undo segment 번호
- ubafil : 데이터파일 번호
- ubablk : undo block 번호
- used_ublk : undo block 사용 갯수