단계 - PDF 무료 다운로드

본문서에서는 Tibero RDBMS 운영중에발생할수있는장애상황을가정하여각유형별장애상황에대해 Tibero 에서지원하고있는백업및복구방법을알아본다.

Contents 1. BACKUP & RECOVERY 개요... 4 1.1. BACKUP( 백업 )... 4 1.2. RECOVERY( 복구 )... 4 2. BACKUP... 5 2.1. 백업형태... 5 2.1.1. 논리적인백업... 5 2.1.2. 물리적인백업... 5 2.2. 백업모드... 6 2.3. BACKUP TEST... 7 2.3.1. Controlfile Backup... 7 2.3.1.1. Controlfile... 7 2.3.1.2. 사용되는동적뷰... 7 2.3.1.3. 다중화방법... 7 2.3.1.4. 백업방법... 7 2.3.2. Online Backup... 8 2.3.2.1. 티베로운영중백업 ( 온라인백업 )... 8 2.3.2.2. 동적뷰... 8 2.3.3. Offline Backup... 9 2.3.3.1. Tibero 종료후백업... 9 3. RECOVERY... 11 3.1. 부트과정별복구... 11 3.1.1. NOMOUNT... 11 3.1.2. MOUNT... 11 3.1.3. OPEN... 11 3.2. RECOVERY 종류... 11 3.2.1. Crash Recovery... 11 3.2.2. Media Recovery... 12 3.3. RECOVERY TEST... 13 3.3.1. Recovery 테스트홖경구성... 13 3.3.1.1..tip 파일설정 ($TB_SID.tip)... 13 1

3.3.1.2. Database 생성... 14 3.3.1.3. Tablespace 생성... 14 3.3.1.4. User 생성... 14 3.3.2. Controlfile 복구... 15 3.3.2.1. 운영상태에서 Controlfile이삭제된경우... 15 3.3.2.2. 운영상태에서 Controlfile이다른파일로덮어씌워진경우... 16 3.3.2.3. Database down 상태에서이중화 Controlfile 하나삭제된경우... 16 3.3.3. Online logfile 복구... 17 3.3.3.1. 운영상태에서로그그룹의로그멤버하나가삭제된경우... 17 3.3.3.2. 운영상태에서 INACTIVE 모드의로그멤버가삭제된경우... 19 3.3.3.3. 운영상태에서 CURRENT 모드의로그그룹이삭제된경우... 20 3.3.4. Temp Tablespace 복구... 23 3.3.4.1. 테이블스페이스의 Temp file 이삭제된경우... 23 3.3.5. Datafile 장애시완젂복구... 24 3.3.5.1. 자동완젂복구 (Archivelog mode)... 24 3.3.5.2. 사용자완젂복구 (Archivelog mode)... 25 3.3.5.3. 백업 Datafile 이없을시완젂복구 (Archivelog mode)... 26 3.3.6. Datafile 장애시불완젂복구... 27 3.3.6.1. 변경기반 (TSN) 불완젂복구 (Archivelog mode)... 27 3.3.6.2. 시갂기반불완젂복구 (Archivelog mode)... 29 3.3.6.3. 취소기반불완젂복구 (Archivelog mode)... 30 3.3.7. Datafile 유실시복구... 31 3.3.7.1. Datafile이유실되었을경우에복구... 31 3.3.7.2. Datafile 다른곳으로이동했을경우에복구... 33 2

Update History Date Worker Comments 2011.03.11 박근용 문서서식업데이트 2009.11.30 백서현 문서내용보완 2009.10.20 강대젂 문서서식업데이트및내용보완 교정 2008.06.20 이임형 최초작성 3

1. Backup & Recovery 개요 1.1. Backup( 백업 ) 여러가지유형의장애로부터데이터베이스를보호하기위함. MTBF(Mean Time Between Failure) 를증가시키고, MTTR(Mean Time To Recover) 를감소 관리자는시스템장애시발생한손실을최소화하고복구가능한상태로데이터베이스를운용해야함 최소한한달에한번데이터베이스젂체백업및하루에한번씩 Export 백업권장 데이터베이스관리자는백업에대한정책을수립하고꼭필요한데이터를최소한의양으로백업해야함. 백업이정상적으로수행되었는지주기적으로검증하는것을권장 1.2. Recovery( 복구 ) 시스템장애발생시복원또는시스템작동을유지하기위한젃차또는기법 4

2. Backup 2.1. 백업형태 2.1.1. 논리적인백업 - 데이터베이스의논리적인단위백업예 ) Table, Index, Constraint, Sequence 등으로 Export 툴로백업 2.1.2. 물리적인백업 - 데이터베이스를구성하는파일을운영체제레벨에서 copy 명령으로백업 예 ) datafile, controlfile, archive logfile 5

2.2. 백업모드 NOARCHIVELOG 모드에서의백업 (Offline Backup / Cold Backup) 데이터베이스를구성하는젂체파일에대해운영을멈춘상태에서백업데이터베이스를백업받은시점으로의복구만가능 ARCHIVELOG 모드에서의백업 (Online Backup / Hot Backup) 데이터베이스운영중에도백업가능. Controlfile 생성문, Datafile, Archive logfile 백업 백업된 archive logfile 의시점에따라 datafile 백업시점이후로의복구도가능 Consistent 백업 정상적인 Shutdown 후의 Backup Inconsistent 백업 DB 운영중에 Backup 또는정상종료되지않은상태에서의 Backup NOARCHIVELOG 모드에서는권장하지않음 6

2.3. Backup Test 2.3.1. Controlfile Backup 2.3.1.1. Controlfile - 데이터베이스의구조를이진파일형태로저장 - 데이터베이스를 mount 할때반드시필요 - 파일이없으면복구를하거나재생성해야함 - 두개이상의 controlfile로구성을권장하고서로다른디스크에위치시킬것 2.3.1.2. 사용되는동적뷰 - v$controlfile 2.3.1.3. 다중화방법 - 데이터베이스를 down (tbdown) - Control file을다른위치로 copy - $TB_SID.tip파일내 CONTROL_FILES파라미터에위에서 copy한 Control file 경로추가 - 데이터베이스를기동 (tbboot) 2.3.1.4. 백업방법 - Offline backup : O/S의 copy 명령을통해별도의위치에 copy SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS [ 백업할파일경로및이름 ] REUSE NORESETLOGS; 7

2.3.2. Online Backup 2.3.2.1. 티베로운영중백업 ( 온라인백업 ) ALTER DATABASE 명령으로테이블스페이스의 Datafile 백업한다. ARCHIVELOG 모드에서만사용가능하다. - Tibero 데이터베이스에온라인백업시작을알림 SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP; - OS 명령으로해당테이블스페이스의데이터파일복사 SQL>!cp /home/tibero/tbdata/system001.tdf /home/tibero/tbdata_bak/system001.tdf - Tibero 데이터베이스에온라인백업종료를알림 SQL> ALTER TABLESPACE SYSTEM END BACKUP; * 주의사항 : 온라인백업중에는데이터베이스의변경사항에대한 log 의양이늘어나기때문에가능하면 싞속하게작업을종료할것 2.3.2.2. 동적뷰 - V$BACKUP : 현재 begin backup 으로인해 backup mode 인상태를확인한다. SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP; SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ------------- --------------- ---------- -------------------- 0 ACTIVE 12084 2009/11/30 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 8

2.3.3. Offline Backup 2.3.3.1. Tibero 종료후백업 Tibero를정상종료한후 OS의 Copy 명령을이용해 Datafile, Logfile, Controlfile, Tip file 등을백업한다. MOUNT 또는 OPEN 모드에서 v$datafile, v$logfile 뷰를통해백업할파일정보조회할수있으며, ARCHIVELOG 모드에서는 archive 파일도백업해야한다. - V$DATAFILE 에서백업대상파일조회 SQL> select file#, create_date, ts#, status, enabled, ckpt_tsn, name from v$datafile; FILE# CREATE_DATE TS# STATUS ENABLED CKPT_TSN NAME ---------- -------------------- ---------- ------- ---------- ---------- ---------------------------------------- 0 2009/11/30 0 ONLINE READ WRITE 12140 /home/tibero/tbdata/system001.tdf 1 2009/11/30 1 ONLINE READ WRITE 12140 /home/tibero/tbdata/undo001.tdf 2 2009/11/30 3 ONLINE READ WRITE 12140 /home/tibero/tbdata/usr001.tdf 3 2009/11/30 4 ONLINE READ WRITE 12140 /home/tibero/tbdata/my_file001.tdf - V$LOGFILE 에서백업대상 logfile 조회 SQL> select group#, status, type, member from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------ ---------------------------------------- 0 ONLINE /home/tibero/tbdata/redo01.redo 0 ONLINE /home/tibero/tbdata/redo02.redo 1 ONLINE /home/tibero/tbdata/redo11.redo 1 ONLINE /home/tibero/tbdata/redo12.redo 2 ONLINE /home/tibero/tbdata/redo21.redo 2 ONLINE /home/tibero/tbdata/redo22.redo - V$CONTROLFILE 에서백업대상 controlfile 조회 SQL> select name from v$controlfile; NAME ---------------------------------------- /home/tibero/tbdata/control01.ctl /home/tibero/tbdata/control02.ctl 9

- 티베로를정상종료 - File copy ( 앞에서조회한모든파일을 copy 한다. $ cp /home/tibero/tbdata/*.tdf /home/tibero/tbdata_bak/ $ cp /home/tibero/tbdata/*.tdf /home/tibero/tbdata_bak/ $ cp /home/tibero/tbdata/*.redo /home/tibero/tbdata_bak/ $ cp /home/tibero/tbdata/*.ctl /home/tibero/tbdata_bak/ 또는 $ cp /home/tibero/tbdata/*.* /home/tibero/tbdata_bak/ 10

3. Recovery 3.1. 부트과정별복구 3.1.1. NOMOUNT - 인스턴스시작 - Database 생성가능 - Controlfile 재생성가능 3.1.2. MOUNT - Datafile 이름변경가능 - Online Redo logfile Archive 옵션홗성화 / 비홗성화가능 - 젂체데이터베이스복구작업가능 3.1.3. OPEN - Controlfile 에정의한모든 file 오픈 3.2. Recovery 종류 3.2.1. Crash Recovery 개요 - 시스템이상, shutdown abort 등비정상적인데이터베이스종료후 Tibero가기동하는과정에서자동으로수행 - Online redo log file, online data file, current control file만을사용 - Undo 테이블스페이스를이용하여 commit 되지않은데이터에대해복구작업 특징 - Database에접근가능한 Instance에 Failure가발생한경우 - Database가기동될때자동수행됨 - Redo 의 roll forward and roll back 11

3.2.2. Media Recovery 개요티베로를구성하는파일에물리적인손상이발생하였거나정상동작을할수없는경우복구하는과정 특징 - DBA에의한명령에의해수동으로수행 (ALTER DATABASE RECOVER...) - Backup받은 datafile을이용해서복구 - redo logfile 이나 archive logfile로부터 redo record를적용 - MOUNT 모드에서만가능하다. 필요한경우 - Datafile의버젂이다른경우 - Datafile의손상으로백업해놓은 datafile을이용하는경우 - Controlfile을복구한경우 참고하는동적뷰 - V$RECOVER_FILE - V$RECOVERY_FILE_STATUS - V$LOGFILE, V$CONTROLFILE, V$LOG 종류 - Complete Recovery : Archive logfile 과 Online logfile 을모두사용해서가장최근로그까지모두반영 - Incomplete Recovery : logfile 일부만적용하거나특정시점으로복구가능. RESETLOGS 적용필요 필요한사항 - 온라인리두로그파일에손상이있을때 - 사용자의실수로 data 유실 - archive 로그파일의유실 Resetlogs - Incomplete recovery를하게되면반드시 resetlogs로데이터베이스를기동해야한다. - resetlogs 이젂 datafile, logfile 과 resetlogs 이후의파일은서로호홖되지않는다. resetlogs 이젂에백업파일이나 logfile들을이용하여 resetlogs 이후로복구할수없다. 또한 resetlogs 이후의 file들을가지고 resetlogs 이젂상태로 incomplete 복구도불가능하다. - resetlogs 시작한경우, 반드시새로운백업을받기를권장한다 - Resetlogs로데이터베이스기동하기 t RESETLOGS 12

3.3. Recovery Test 3.3.1. Recovery 테스트홖경구성 3.3.1.1..tip 파일설정 ($TB_SID.tip) DB_NAME=tibero LISTENER_PORT=8629 CONTROL_FILES=/home/tibero/tbdata/control01.ctl,/home/tibero/tbdata/control02.ctl DB_CREATE_FILE_DEST=/home/tibero/tbdata LOG_ARCHIVE_DEST=/home/tibero/arch DBWR_CNT=1 DBMS_LOG_TOTAL_SIZE_LIMIT=300M TRACE_LOG_TOTAL_SIZE_LIMIT=300M WTHR_PROC_CNT=1 _WTHR_PER_PROC=10 TOTAL_SHM_SIZE=320M DB_BLOCK_SIZE=8K DB_CACHE_SIZE=256M LOG_BUFFER=10M LOG_LVL=2 # module, work log example: #LOG_LVL_FRM=5 #LOG_WORK_LVL_RECOVERY=5 SQL_LOG_ON_MEMORY=Y #_BTRACE_METHOD=1 BTRACE_FILE_LIMIT=1M BTRACE_TOTAL_LIMIT=10G BTRACE_MAP="/home/tibero/tibero4/config/btrace.map" BTRACE_USEC=Y 13

3.3.1.2. Database 생성 CREATE DATABASE USER SYS IDENTIFIED BY TIBERO MAXDATAFILES 256 CHARACTER SET MSWIN949 -- UTF8, EUCKR, ASCII,MSWIN949 LOGFILE GROUP 0 ('redo01.redo','redo02.redo') SIZE 10M, GROUP 1 ('redo11.redo','redo12.redo') SIZE 10M, GROUP 2 ('redo21.redo','redo22.redo') SIZE 10M MAXLOGFILES 100 MAXLOGMEMBERS 8 ARCHIVELOG DATAFILE 'system001.tdf' SIZE 128M AUTOEXTEND ON NEXT 16M MAXSIZE 3072M DEFAULT TABLESPACE USR DATAFILE 'usr001.tdf' SIZE 128M AUTOEXTEND ON NEXT 16M MAXSIZE 3072M DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'temp001.tdf' SIZE 256M AUTOEXTEND ON NEXT 16M MAXSIZE 10240M EXTENT MANAGEMENT LOCAL AUTOALLOCATE UNDO TABLESPACE UNDO DATAFILE 'undo001.tdf' SIZE 256M AUTOEXTEND ON NEXT 16M MAXSIZE 10240M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; 3.3.1.3. Tablespace 생성 DROP TABLESPACE "MY_FILE001" INCLUDING CONTENTS AND DATAFILES; CREATE TABLESPACE "MY_FILE001" DATAFILE 'my_file001.tdf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 3G EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; 3.3.1.4. User 생성 DROP USER TIBERO CASCADE; CREATE USER tibero IDENTIFIED BY tmax DEFAULT TABLESPACE MY_FILE001; GRANT DBA TO tibero; **** Test version : Tibero 4 SP1(Build 44781) 14

3.3.2. Controlfile 복구 3.3.2.1. 운영상태에서 Controlfile 이삭제된경우 [ 장애발생 ] SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/tibero/tbdata/ctl_backup.sql' REUSE NORESETLOGS; $ rm -rf /home/tibero/tbdata/c1.ctl [ 조치방법 ] 백업받은 controlfile 스크립트로 control 파일재생성 clean ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1003 : Unable to open file /home/tibero/tbdata/control01.ctl. * Current server mode is NOMOUNT. ******************************************************** $tbsql sys/tibero SQL> @ctl_backup.sql SQL>exit ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1024 : Database needs media recovery: media recovery required (/home/tibero/tbdata/system001.tdf). * Current server mode is MOUNT. ******************************************************** $tbsql sys/tibero SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- --------- ---------------------------- ------------------ -------------------- 0 ONLINE media recovery required 1 2009/11/30 1 ONLINE media recovery required 8 2009/11/30 2 ONLINE media recovery required 16 2009/11/30 3 ONLINE media recovery required 12106 2009/11/30 4 rows selected. SQL> alter database recover automatic database; Database altered. SQL> select * from v$recover_file; FILE# ERROR CHANGE# TIME ---------- ------------------- ---------- ---------- 0 row selected. 15

3.3.2.2. 운영상태에서 Controlfile 이다른파일로덮어씌워진경우 [ 조치방법 ]3.3.2.1 의운영상태에서 Controlfile 이삭제된경우와복구방법이동일. 3.3.2.3. Database down 상태에서이중화 Controlfile 하나삭제된경우 [ 장애발생 ) $ rm -rf /home/tibero/tbdata/control01.ctl ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1003 : Unable to open file /home/tibero/tbdata/control01.ctl. * Current server mode is NOMOUNT. ******************************************************** [ 조치방법 ] 다른 control 파일을이용하여 copy 후복구함. $ cp /home/tibero/tbdata/control02.ctl /home/tibero/tbdata/control01.ctl 16

3.3.3. Online logfile 복구 3.3.3.1. 운영상태에서로그그룹의로그멤버하나가삭제된경우 [ 장애발생 ) SQL> select * from v$log; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 1 10485760 2 YES INACTIVE 0 0 1 2 10485760 2 NO CURRENT 7286 2009/11/30 0 2-1 10485760 2 NO UNUSED 0 3 selected. SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> select * from v$log; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 4 10485760 2 YES INACTIVE 12358 2009/11/30 0 1 5 10485760 2 NO CURRENT 12361 2009/11/30 0 2 3 10485760 2 YES INACTIVE 12356 2009/11/30 3 selected. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ----------- ------ ---------------------------------------- 0 ONLINE /home/tibero/tbdata/redo01.redo 0 ONLINE /home/tibero/tbdata/redo02.redo 1 ONLINE /home/tibero/tbdata/redo11.redo 1 ONLINE /home/tibero/tbdata/redo12.redo 2 ONLINE /home/tibero/tbdata/redo21.redo 2 ONLINE /home/tibero/tbdata/redo22.redo 6 selected. SQL>!rm -rf /home/tibero/tbdata/redo01.redo SQL>!rm -rf /home/tibero/tbdata/redo21.redo SQL> exit ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1003 : Unable to open file /home/tibero/tbdata/redo21.redo. * Current server mode is MOUNT. ******************************************************** 17

[ 조치방법 ] 같은그룹내의로그파일을복사하여복구성공 SQL>!ls -al /home/tibero/tbdata 합계 842576 drwx------ 3 tibero tmax 4096 11월 30 19:01. drwxr-xr-x 9 tibero tmax 4096 11월 30 17:46.. -rw-r--r-- 1 tibero tmax 24 11월 30 17:12.passwd -rw------- 1 tibero tmax 2080768 11월 30 19:01 control01.ctl -rw------- 1 tibero tmax 2080768 11월 30 19:01 control02.ctl -rw------- 1 tibero tmax 10485760 11월 30 19:01 my_file001.tdf drwx------ 2 tibero tmax 4096 11월 30 17:11 psm -rw------- 1 tibero tmax 10485760 11월 30 17:59 redo02.redo -rw------- 1 tibero tmax 10485760 11월 30 19:01 redo11.redo -rw------- 1 tibero tmax 10485760 11월 30 19:01 redo12.redo -rw------- 1 tibero tmax 10485760 11월 30 17:59 redo22.redo -rw------- 1 tibero tmax 134217728 11월 30 19:01 system001.tdf -rw------- 1 tibero tmax 268435456 11월 30 17:11 temp001.tdf -rw------- 1 tibero tmax 268435456 11월 30 19:01 undo001.tdf -rw------- 1 tibero tmax 134217728 11월 30 19:01 usr001.tdf SQL>!cp /home/tibero/tbdata/redo02.redo /home/tibero/tbdata/redo01.redo SQL>!cp /home/tibero/tbdata/redo22.redo /home/tibero/tbdata/redo21.redo SQL> exit 18

3.3.3.2. 운영상태에서 INACTIVE 모드의로그멤버가삭제된경우 [ 장애발생 ] INACTIVE 된상태의로그멤버 (redo21.redo) 1 개가삭제된경우 SQL> select * from v$log; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 4 10485760 2 YES INACTIVE 12358 2009/11/30 0 1 5 10485760 2 NO CURRENT 12361 2009/11/30 0 2 3 10485760 2 YES INACTIVE 12356 2009/11/30 3 selected. SQL>!rm -rf /home/tibero/tbdata/redo21.redo SQL> exit [ 조치방법 ] 해당로그그룹을삭제후재생성 $ rm -rf /home/tibero/tbdata/redo22.redo mount SQL> ALTER DATABASE DROP LOGFILE GROUP 2; SQL> ALTER DATABASE ADD LOGFILE GROUP 2 2 ('/home/tibero/tbdata/redo21.redo', '/home/tibero/tbdata/redo22.redo') SIZE 1048576; SQL > exit 19

3.3.3.3. 운영상태에서 CURRENT 모드의로그그룹이삭제된경우 [ 장애발생 ] 운영중로그그룹의로그멤버가하나삭제된경우를참조하여 CURRENT 그룹의 멤버두개를모두삭제후재기동 --Archivelog 모드확인 SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG SQL> create table t1 (c1 number); SQL> insert into t1 values(10); SQL> commit; SQL> alter system switch logfile; SQL> insert into t1 values(20); SQL> commit; SQL> alter system switch logfile; SQL> insert into t1 values(30); SQL> commit; SQL> alter system switch logfile; -- 온라인백업 SQL> alter database begin backup; SQL>!cp /home/tibero/tbdata/system001.tdf /home/tibero/backup_arch/system001.tdf SQL>!cp /home/tibero/tbdata/undo001.tdf /home/tibero/backup_arch/undo001.tdf SQL>!cp /home/tibero/tbdata/usr001.tdf /home/tibero/backup_arch/usr001.tdf SQL>!cp /home/tibero/tbdata/my_file001.tdf /home/tibero/backup_arch/my_file001.tdf SQL> alter database end backup; --Control 파일백업 SQL> alter database backup controlfile to trace as '/home/tibero/backup_arch/ctl_bak.sql' reuse RESETLOGS; SQL> insert into t1 values(40); SQL> commit; SQL> alter system switch logfile; SQL> insert into t1 values(50); SQL> commit; SQL> alter system switch logfile; -- 아래내용은복구안함 (Archive 파일생성안함 ) SQL> insert into t1 values(60); SQL> commit; SQL> exit $ /home/tibero/tbdata> rm *.redo $ /home/tibero/tbdata> tbdown abort 20

[ 조치방법 ] 특정시점의 TSN 을이용하여변경기반불완젂복구 -- 시점을맞추기위해 control 파일및데이터파일도모두삭제 $ /home/tibero/tbdata> rm *.* $ /home/tibero/tbdata> mv../backup_arch/*.*. --nomount 로기동하여 controlfile 백업스크립트로 controfile 생성 nomount SQL> @ctl_bak.sql SQL> quit mount SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- ------------------------------ ---------- -------------------- 0 ONLINE online backup in progress 1 2009/11/30 1 ONLINE online backup in progress 8 2009/11/30 2 ONLINE online backup in progress 16 2009/11/30 3 ONLINE online backup in progress 12106 2009/11/30 4 rows selected. SQL> select * from v$log; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 1 10485760 2 NO CURRENT 0 0 1-1 10485760 2 NO UNUSED 0 0 2-1 10485760 2 NO UNUSED 0 3 rows selected. -- 마지막 Archive logfile 의 NEXT_TSN 정보를이용하여변경기반복구를진행한다. --NEXT_TSN 정보확인방법 (1) v$archive_dest_files 의 next_tsn 정보확인 -- (2) 아카이브파일덤프발생시켜 next_tsn 정보확인 -- 변경기반복구작업시에는 next_tsn -1 값으로진행함. SQL> select name, next_change# from v$archive_dest_files order by name; NAME NEXT_CHANGE# ---------------------------------------- ---------------------------- /home/tibero/arch/log-t0-r0-s1.arc 7295 /home/tibero/arch/log-t0-r0-s2.arc 11729 /home/tibero/arch/log-t0-r0-s3.arc 11737 /home/tibero/arch/log-t0-r0-s4.arc 11746 /home/tibero/arch/log-t0-r0-s5.arc 11825 /home/tibero/arch/log-t0-r0-s6.arc 11833 21

--TSN 조회시없을경우 logfile 덤프를발생시킨다. SQL> alter system dump logfile '/home/tibero/arch/log-t0-r0-s6.arc'; <<< dump log 시작 ( 파일경로 : $TB_HOME/instance/$TB_SID/dump/tracedump/)>>> ================================================================== **Dump start at 2009-12-02 15:52:30 DUMP of LOG FILE /home/tibero/arch/log-t0-r0-s6.arc (from blkno 0 to -1) LOGFILE HEADER BLOCK dump dbname='tibero' dbid=f57600ad version=4.1 cf_seqno=47 filetype=1 fileno=2 blksize=512 filesize=1536 THREAD=0 desc='tibero_logfile_header' nab=3 seq=6 resetlogs=0000.00000000 low=0000.00002e31 2009-12-02 15:47:47 next=0000.00002e39 2009-12-02 15:48:01 status=17 term_reco_stamp=0000.00000000 ================================================================== <<< dump log 끝 >>> --next_tsn 값을 10 진수로변경 : 0000.00002e39 (hex) => 11833(dec) --next_tsn 1 =11832 SQL> ALTER DATABASE RECOVER AUTOMATIC DATABASE UNTIL CHANGE 11832; Database altered. SQL> exit resetlogs -- 백업한 Control 파일스크립트내의주석스크립트부분을활용하여 temp 파일생성 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/tibero/tbdata/temp001.tdf' 2 SIZE 256M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE 2G; Tablespace 'TEMP' altered. SQL>!ls al temp*.tdf -rw------- 1 tibero tmax 268435456 12 월 1 09:40 temp001.tdf -- 데이터조회 SQL> select * from t1; C1 ---------- 10 20 30 40 50 5 rows selected. 22

3.3.4. Temp Tablespace 복구 3.3.4.1. 테이블스페이스의 Temp file 이삭제된경우 [ 장애발생 ] SQL>!rm rf /home/tibero/tbdata/temp001.tdf [ 조치방법 ] tbboot 이후 temp file 은자동적으로생성됨 $ ls al /home/tibero/tbdata $ ls al /home/tibero/tbdata 23

3.3.5. Datafile 장애시완젂복구 3.3.5.1. 자동완젂복구 (Archivelog mode) [ 장애발생 ] datafile 이삭제된경우 $ cp R /home/tibero/tbdata /home/tibero/tbdata_bak $ tbsql tibero/tmax SQL> CREATE TABLE T1 (C1 VARCHAR(5)); created. SQL> INSERT INTO T1 (C1) VALUES ('00011'); SQL> INSERT INTO T1 (C1) VALUES ('00012'); SQL> INSERT INTO T1 (C1) VALUES ('00013'); SQL> COMMIT; SQL> conn sys/tibero SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> exit $ rm rf /home/tibero/tbdata/my_file001.tdf [ 조치방법 ] AUTOMATIC 으로자동복구 $ cp /home/tibero/tbdata_bak/my_file001.tdf /home/tibero/tbdata mount SQL> ALTER DATABASE RECOVER AUTOMATIC; SQL> exit $ tbsql tibero/tmax SQL> select * from t1; C1 ----- 00011 00012 00013 24

3.3.5.2. 사용자완젂복구 (Archivelog mode) [ 장애발생 ] 3.3.5.1 과동일장애 [ 조치방법 ] archive 파일로생성된것은 archive 파일을이용하여명시적으로각각복구를하 고, archive 파일로생성되지않은 current 상태인것은 redo 파일로복구 $ cp /home/tibero/tbdata_bak/my_file001.tdf /home/tibero/tbdata mount SQL> ALTER DATABASE RECOVER; SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/arch/log-t0-r0-s2.arc'; SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/arch/log-t0-r0-s3.arc'; SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/arch/log-t0-r0-s4.arc'; SQL> SELECT * FROM V$LOG; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 4 10485760 2 YES INACTIVE 12303 2009/12/01 0 1 5 10485760 2 NO CURRENT 12308 2009/12/01 0 2 3 10485760 2 YES INACTIVE 12300 2009/12/01 SQL> SELECT * FROM V$LOGFILE; GROUP# STATUS TYPE MEMBER ---------- ------- ------ ---------------------------------------- 0 ONLINE /home/tibero/tbdata/redo01.redo 0 ONLINE /home/tibero/tbdata/redo02.redo 1 ONLINE /home/tibero/tbdata/redo11.redo 1 ONLINE /home/tibero/tbdata/redo12.redo 2 ONLINE /home/tibero/tbdata/redo21.redo 2 ONLINE /home/tibero/tbdata/redo22.redo SQL> ALTER DATABASE RECOVER LOGFILE '/home/tibero/tbdata/redo11.redo'; Database altered. SQL> exit $ tbsql tibero/tmax SQL> select * from t1; C1 ----- 00011 00012 00013 25

3.3.5.3. 백업 Datafile 이없을시완젂복구 (Archivelog mode) [ 장애발생 ] 3.3.5.1 과동일장애 (tdf 파일이삭제된경우 ) [ 조치방법 ] 먼저 datafile 을생성하고, AUTOMATIC 으로복구 mount SQL> ALTER DATABASE CREATE DATAFILE '/home/tibero/tbdata/my_file001.tdf'; SQL> ALTER DATABASE RECOVER AUTOMATIC; SQL> exit $ tbsql tibero/tmax SQL> SELECT * FROM T1; C1 ----- 00011 00012 00013 26

3.3.6. Datafile 장애시불완젂복구 3.3.6.1. 변경기반 (TSN) 불완젂복구 (Archivelog mode) [ 장애발생 ] 실수로모든데이터파일을덮어쓴경우 $ cp R /home/tibero/tbdata /home/tibero/tbdata_bak SQL> SELECT * FROM V$LOG; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 1 10485760 2 YES INACTIVE 0 0 1 2 10485760 2 NO CURRENT 7286 2009/11/30 0 2-1 10485760 2 NO UNUSED 0 3 rows selected. SQL> CREATE TABLE T1 (C1 VARCHAR(5)); SQL> INSERT INTO T1 (C1) VALUES ('00011'); SQL> INSERT INTO T1 (C1) VALUES ('00012'); SQL> INSERT INTO T1 (C1) VALUES ('00013'); SQL> COMMIT; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> INSERT INTO T1 (C1) VALUES ('00021'); SQL> INSERT INTO T1 (C1) VALUES ('00022'); SQL> INSERT INTO T1 (C1) VALUES ('00023'); SQL> COMMIT; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> INSERT INTO T1 (C1) VALUES ('00031'); SQL> INSERT INTO T1 (C1) VALUES ('00032'); SQL> INSERT INTO T1 (C1) VALUES ('00033'); SQL> COMMIT; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> INSERT INTO T1 (C1) VALUES ('00041'); SQL> INSERT INTO T1 (C1) VALUES ('00042'); SQL> INSERT INTO T1 (C1) VALUES ('00043'); SQL> COMMIT; SQL> exit $ cp /home/tibero/tbdata_bak/*.tdf /home/tibero/tbdata 27

[ 조치방법 ] TSN 넘버를지정하여특정시점으로복구 -- 최근백업받은데이터파일로복원 $ cp /home/tibero/tbdata_bak/*.tdf /home/tibero/tbdata mount SQL> SELECT * FROM V$LOG; THREAD# GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- -------- ------------- ------------ ----------- 0 0 4 10485760 2 YES INACTIVE 12339 2009/12/01 0 1 5 10485760 2 NO CURRENT 12356 2009/12/01 0 2 3 10485760 2 YES INACTIVE 12327 2009/12/01 3 rows selected. SQL> ALTER DATABASE RECOVER AUTOMATIC DATABASE UNTIL CHANGE 12356; SQL> exit ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1024 : Resetlogs is required. * Current server mode is MOUNT. ******************************************************** resetlogs SQL> select * from t1; C1 -------- 00011 00012 00013 00021 00022 00023 00031 00032 00033 9 rows selected. SQL> exit 28

3.3.6.2. 시갂기반불완젂복구 (Archivelog mode) [ 장애발생 ] 사용자실수로테이블을 DROP 한경우 $ cp R /home/tibero/tbdata /home/tibero/tbdata_bak SQL> create table t1 (C1 varchar(5)); SQL> insert into t1 values('00011'); SQL> commit; SQL> select * from t1; C1 -------- 00011 1 row selected. SQL>!date 2009. 12. 01. ( 화 ) 15:22:52 KST SQL> drop table t1; SQL> alter system switch logfile; SQL> exit [ 조치방법 ] DROP 된시점젂으로시갂을지정하여복구 $ cp /home/tibero/tbdata_bak/*.tdf /home/tibero/tbdata -- 홖경변수셋팅후마운트모드로기동 $ export TB_NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' mount --DROP 된시점젂까지 Time 기반복구진행 SQL> alter database recover automatic database until time '2009-12-01 15:22:52'; Altered; SQL> exit -- 리두로그초기화모드로데이터베이스오픈 resetlogs SQL> select * from t1; C1 -------- 00011 29

3.3.6.3. 취소기반불완젂복구 (Archivelog mode) [ 장애발생 ] 디스크손상으로인해주요데이터파일손실되고아카이브로그파일중 log-t0-r0-s4.arc 파일손상으로 log-t0-r0-s3.arc 까지복구가능한상황 ( 여기서는 my_file001.tdf 및 log-t0-r0-s4.arc 파일삭제 ) $tbsql tibero/tmax SQL> create table t1 (c1 number); SQL> insert into t1 values(10); SQL> commit; SQL> alter system switch logfile; -- log-t0-r0-s2.arc 생성됨. SQL> insert into t1 values(20); SQL> commit; SQL> alter system switch logfile; -- log-t0-r0-s3.arc 생성됨. SQL> insert into t1 values(30); SQL> commit; SQL> alter system switch logfile; -- log-t0-r0-s4.arc 생성됨. SQL> exit $tbdown $rm /home/tibero/tbdata/my_file001.tdf /home/tibero/arch/log-t0-r0-s4.arc [ 조치방법 ] 가장최근에받은젂체 backup파일로복원후, 해당 archive file까지적용하여복구 $ cp /home/tibero/tbdata_bak/*.tdf /home/tibero/tbdata/ --마운트모드로 DB기동 mount - 백업할시점지정후복원 SQL> select name, next_change# from v$archive_dest_files order by name; NAME NEXT_CHANGE# ---------------------------------------- ---------------------------- /home/tibero/arch/log-t0-r0-s1.arc 7295 /home/tibero/arch/log-t0-r0-s2.arc 11743 /home/tibero/arch/log-t0-r0-s3.arc 11750 SQL> alter database recover database until cancel; Database altered. SQL> alter database recover logfile '/home/tibero/arch/log-t0-r0-s2.arc'; Database altered. SQL> alter database recover logfile '/home/tibero/arch/log-t0-r0-s3.arc'; Database altered. SQL> alter database recover cancel; Database altered. SQL>exit - 리두로그초기화모드로데이터베이스오픈 resetlogs $tbsql tibero/tmax SQL> select * from t1; C1 ---------- 10 20 30

3.3.7. Datafile 유실시복구 3.3.7.1. Datafile이유실되었을경우에복구 [ 장애발생 ] $ rm /home/tibero/tbdata/my_file001.tdf [ 조치방법 ] 해당 Datafile 을 drop 하여해결 ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1024 : Database needs media recovery: open failed (/home/tibero/tbdata/my_file001.tdf). * Current server mode is MOUNT. ******************************************************** $tbsql sys/tibero SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- -------------------- ---------- -------------------- 3 ONLINE open failed 12106 2009/11/30 1 row selected. SQL> select file#, create_tsn, create_date, ts#, status,ckpt_tsn, name from v$datafile; FILE# CREATE_TSN CREATE_DATE TS# STATUS CKPT_TSN NAME ---------- ---------- -------------------- ---------- ------- ---------- ---------------------------------------- 0 1 2009/11/30 0 ONLINE 12255 /home/tibero/tbdata/system001.tdf 1 8 2009/11/30 1 ONLINE 12255 /home/tibero/tbdata/undo001.tdf 2 16 2009/11/30 3 ONLINE 12255 /home/tibero/tbdata/usr001.tdf 3 12106 2009/11/30 4 ONLINE 12255 /home/tibero/tbdata/my_file001.tdf 4 rows selected. SQL> alter database datafile 3 offline for drop; Database altered. SQL> exit 31

resetlogs $tbsql sys/tibero SQL> select file#, create_tsn, create_date, ts#, status,ckpt_tsn, name from v$datafile; FILE# CREATE_TSN CREATE_DATE TS# STATUS CKPT_TSN NAME ---------- ---------- -------------------- ---------- ------- ---------- ---------------------------------------- 0 1 2009/11/30 0 ONLINE 12255 /home/tibero/tbdata/system001.tdf 1 8 2009/11/30 1 ONLINE 12255 /home/tibero/tbdata/undo001.tdf 2 16 2009/11/30 3 ONLINE 12255 /home/tibero/tbdata/usr001.tdf 3 12106 2009/11/30 4 OFFLINE 12255 /home/tibero/tbdata/my_file001.tdf 4 rows selected. SQL> drop tablespace my_file001 including contents and datafiles; Tablespace 'MY_FILE001' dropped. SQL> select file#, create_tsn, create_date, ts#, status,ckpt_tsn, name from v$datafile; FILE# CREATE_TSN CREATE_DATE TS# STATUS CKPT_TSN NAME ---------- ---------- -------------------- ---------- ------- ---------- ---------------------------------------- 0 1 2009/11/30 0 ONLINE 12294 /home/tibero/tbdata/system001.tdf 1 8 2009/11/30 1 ONLINE 12294 /home/tibero/tbdata/undo001.tdf 2 16 2009/11/30 3 ONLINE 12294 /home/tibero/tbdata/usr001.tdf 3 rows selected. 32

3.3.7.2. Datafile 다른곳으로이동했을경우에복구 [ 장애발생 ] $ mv /home/tibero/tbdata/system001.tdf /home/ti bero/tbdata_bak/system001.tdf [ 조치방법 ] 이동한경로를앞으로사용할경로로지정하여복구 ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1024 : Database needs media recovery: open failed (/home/tibero/tbdata/system001.tdf). * Current server mode is MOUNT. ******************************************************** SQL> select file#, create_tsn, create_date, ts#, status,ckpt_tsn, name from v$datafile; FILE# CREATE_TSN CREATE_DATE TS# STATUS CKPT_TSN NAME ---------- ---------- -------------------- ---------- ------- ---------- ---------------------------------------- 0 1 2009/11/30 0 ONLINE 12255 /home/tibero/tbdata/system001.tdf 1 8 2009/11/30 1 ONLINE 12255 /home/tibero/tbdata/undo001.tdf 2 16 2009/11/30 3 ONLINE 12255 /home/tibero/tbdata/usr001.tdf 3 12106 2009/11/30 4 ONLINE 12255 /home/tibero/tbdata/my_file001.tdf 4 rows selected. SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- -------------------- ---------- -------------------- 0 ONLINE open failed 1 2009/11/30 1 row selected. SQL> alter database rename file '/home/tibero/tbdata/system001.tdf' to '/home/tibero/tbdata_bak/system001.tdf'; Database altered. SQL> select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- -------------------- ---------- -------------------- 0 row selected. SQL> exit disconnected. 33

Information Intelligence, Tibero