DB 청소
- schema
- tablespace
- datafile크기
Instance recovery = redo + undo 적용
checkpoint = buffer cache 저장, 강제로 할때는 alter system set checkpoint;
Mean-Time-Between-Failures(MTBF) : 길수록 좋음
Mean-Time-To-Recover(MTTR) : 짧을 수록 좋음 :오라클용어로=> Instance Recorvery시간으로 해석
Categories of Failures
- Statement failure : 구문 에러
- User process failure
- Network failure
- User error
- Instance failure
- Media failure by BNR로 고침
Multiplexing the Redo Log
- group / member 확인
- group / member 추가
- group / member 삭제
- current group 변경
- check point 강제 수행
확인
SELECT * FROM V$LOG;
SELECT member From v$logfile;
추가
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/04.log') SIZE 51200K
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/04_2.log' TO GROUP 4
삭제.
ALTER DATABASE DROP LOGFILE GROUP 4
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/04_2.log'
current group 변경
ALTER SYSTEM SWITCH LOGFILE;
check point 강제 수행
ALTER SYSTEM CHECKPOINT;
OMF 모드에서 Archive 모드로 전환하기
1. 각종 parameter 값 확인
SYS> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SYS> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SYS> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SYS> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
2. Mount mode로
startup mount
3. Archive 모드로 전환
alter database archivelog;
4. open
alter database open;
5. 확인
select log_mode from v$database;
6. Archive 파일 생성 여부
show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
alter system switch logfile;
!
cd /u01/app/oracle/flash_recovery_area/ORCL/archivelog
S SYS> select recid,status,name from V_$ARCHIVED_LOG;
RECID S NAME
---------- - ------------------------------------------------------------
1 A /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_
12/o1_mf_1_277_533zcbpm_.arc
S SYS> sav archived_log