DB스토리

DB 청소

1. schema
2. tablespace
3. 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

1. Statement failure : 구문 에러
2. User process failure
3. Network failure
4. User error
5. Instance failure
6. 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