RMAN Backup and Recovery Seungtaek Lee( 放浪 DBA)
User-managed Backup vs Server-managed Backup 단계 Inconsistency 현상 User-managed Backup Server-managed Backup (RMAN) 시작. File Inconsistency : Backup 수행시 File 부터 Copy하지않을경우복구시에 Redo Log 적용시작시점을알수없음. 수동으로 Begin Backup command 수행. Data File 에 Hot Backup Fuzzy Bits(0x01) 이설정됨.. Tablespace/Database 단위로 Checkpoint 발생. Data File 의 Checkpoint 을 Backup 이끝날때까지고정시킴. 복구시 Backup File 의 Checkpoint 이 Redo Log 적용시작시점이됨. Data File 단위로 Checkpoint 발생. Backup 시작시점의 Checkpoint 이 Backup Set 일경우메모리 (Large Pool/Shared Pool) 에기록되고 Image Copy 일경우 Backup 본의 Block 에기록된다. 복구시 Checkpoint 이 Redo Log 적용시작시점이됨. Backup 수행중에 Data File 의 Checkpoint 은 Checkpoint 발생때변한다. 수행. Block Inconsistency : Backup IO 단위와 Oracle Block 단위차이때문에 Backup Block 이손상됨 (Fractured Block). 해당 Block 에 Transaction 발생시최초한번은 Block Image Redo Record 생성 (OPCODE:18.1). Oracle Block Size 의배수로 Backup 수행하고 Block 손상유무확인함 종료. Backup Inconsistency : Backup 종료시점을알수없어서 Redo Log 최소적용시점을알수없음. 수동으로 End Backup command 수행. Redo log 에 Hot Backup end marker 생성 복구시 Consistency 보장을위한최소복구시점임. (Recovery 수행시해당 marker 를만나면 Hot Backup Fuzzy Bits 설정이해제되고 DB Open 이가능함 ). Data File 에 Hot Backup Fuzzy Bits 설정이해제됨. Backup 본의 Block 중에서가장높은 (Absolute Fuzzy ) 이 Backup 시작시점의 (Checkpoint ) 과함께 Backup 본의 Block 에기록됨. Backup 본 에기록된 Absolute Fuzzy 이복구 Consistency 보장을위한최소복구시점임. 2
More Redo Log during User-managed Backup SQL> update test set a='ci-tec' where a='tec'; SQL> alter tablespace users end backup; REDO RECORD - Thread:1 RBA: 0x000588.00000002.0010 LEN: 0x2050 VLD: 0x05 : 0x0000.002cff6e SUB: 2 04/27/2010 08:45:13 CHANGE #1 TYP:3 CLS: 1 AFN:4 DBA:0x0100c2e1 OBJ:55790 :0x0000.002cfed7 SEQ: 1 OP:18.1 Log block image redo entry Dump of memory from 0xAEDEF220 to 0xAEDF1208 AEDEF220 00000001 0000D9EE 002CFED5 00000000 [...,...] AEDEF230 00320002 0100C28A 00230007 0000033A [..2...#.:...] AEDEF240 008001F4 00140201 00008000 002C8D7A [...z.,.] AEDEF250 00260008 00000432 008009A9 000F03C5 [..&.2...] AEDEF260 00002001 002CFED7 00000000 00000000 [....,...] AEDEF270 00030100 00180001 1F6B1F6C 00001F6B [...l.k.k...] AEDEF280 1F8C0003 1F6CFFFF 00000000 00000000 [...l...] AEDEF290 00000000 00000000 00000000 00000000 [...] Repeat 499 times REDO RECORD - Thread:1 RBA: 0x000588.00000012.0160 LEN: 0x01b0 VLD: 0x01 : 0x0000.002cff6e SUB: 3 04/27/2010 08:45:13 CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 OBJ:4294967295 :0x0000.002cff21 SEQ: 1 OP:5.2 ktudh redo: slt: 0x001b sqn: 0x00000439 flg: 0x0012 siz: 156 fbi: 0 uba: 0x008008ad.027a.2a pxid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x008008ad OBJ:4294967295 :0x0000.002cff20 SEQ: 1 OP:5.1 ktudb redo: siz: 156 spc: 488 flg: 0x0012 seq: 0x027a rec: 0x2a xid: 0x0006.01b.00000439 ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 55789 objd: 55790 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x008008ad.027a.29. KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0100c2e1 hdba: 0x010001ab itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 10 ncol: 1 nnew: 1 size: -3 col 0: [ 3] 54 45 43 -> TEC CHANGE #3 TYP:2 CLS: 1 AFN:4 DBA:0x0100c2e1 OBJ:55790 :0x0000.002cfed7 SEQ: 1 OP:11.5 KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0006.01b.00000439 uba: 0x008008ad.027a.2a Block cleanout record, scn: 0x0000.002cff6e ver: 0x01 opt: 0x02, entries follow... itli: 2 flg: 2 scn: 0x0000.002cfed7 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0100c2e1 hdba: 0x010001ab itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 2(0x2) flag: 0x2c lock: 1 ckix: 10 ncol: 1 nnew: 1 size: 3 col 0: [ 6] 43 49 2d 54 45 43 -> CI-TEC REDO RECORD (8272bytes) : Block Image Redo Record 추가 Redo Record CHANGE#1 : Block Image Dump 생성 REDO RECORD (432bytes) : Data 변경 Redo Record CHANGE#1 : Rollback Segment 의 Block 변경 Transaction Table CHANGE#2 : Rollback Segment 의 Undo Block 변경 TEC CHANGE#3 : Data Block 변경 CI-TEC REDO RECORD (112bytes) : End Backup Redo Record CHANGE#1 : End backup marker REDO RECORD - Thread:1 RBA: 0x000588.00000015.0010 LEN: 0x0070 VLD: 0x05 : 0x0000.002cff74 SUB: 1 04/27/2010 08:45:21 CHANGE #1 MEDIA RECOVERY MARKER :0x0000.00000000 SEQ: 0 OP:17.1 End backup marker - file:4 scn: 0x0000.002cff5c 3 REDO RECORD (112bytes) : End Backup Redo Record CHANGE#1 : End backup marker
RMAN Backup & Recovery Process Restored Data File Step Description Fuzzy CKPT Online Data File 38 37 39 37 38 36 Backup Set Block 4 34 34 37 38 36 Block 5 34 34 37 38 36 Block 4 36 38 Consistency Fuzzy Checkpoint Absolute Fuzzy 2 Restore 38 37 34 37 38 36 36 38 34 34 37 38 36 3 Recovery Online Backup RMAN Online Backup 이시작되면해당 Data File 에대해 Checkpoint 를발생시킨다. 이때의 Checkpoint 이기록된 Data File Block 을 Large Pool 메모리에 Copy 한다. 이후순서대로 Block 을읽어 Backup Set 에저장한다. Backup 종료시점에 Backup Set Block 의가장높은 (Absolute Fuzzy ) 을 Block 에업데이터하여 Backup Set 끝부분에 Block 을추가한다. (V$BACKUP_DATAFILE. ABSOLUTE_FUZZY_CHANGE#) Fuzzy 38 37 34 37 38 36 36 37 34 37 35 35 34 34 37 38 34 34 Recovery Recovery 시에는 Block 에기록된 Checkpoint 부터 Redo Record 를적용한다. Absolute Fuzzy 까지 Redo Record 가적용되면 Fuzzy Bit 이없어지고 DB 를 Open 할수있다. CKPT Fuzzy 36 34 34 35 35 35 31 30 34 30 34 34 34 1 Online Backup 36 Large Pool RMAN Image Copy Image Copy 백업도 Backup Set 백업과거의동일하게수행된다. 단 Block 을 Image Copy 본앞부분에가장먼저저장한다.(Image Copy 본은 Data File 과똑같은구조로만들기때문에 Restore 없이경로만바꾸어사용할수도있다.) 그리고 Backup 종료시점에 Absolute Fuzzy 을 Image Copy Block 에업데이트한다. 이때문에 Image Copy 본은 Tape 백업이지원되지않는다. 4
RMAN Components Catalog DB RMAN 1 st Default Channel Polling Channel Server Session Target Controlfile Target DB Components 1 st Default Channel Polling Channel Description Backup 을제외한모든작업을수행하는 Channel (ex cmpatibility 확인SQL문수행,control file 조회등 Allocated Channel 할당을위한 Channel Backup Disk Allocated Channel Server Session Input Buffers Output Allocated Channel Input Buffers Output Buffers Backup/Restore 를실제수행하는 Channel - Validate, Compression, Encryption - 4 per datafile - Normally 1MB per Channel - 4 per channel - DISK : 1MB per Channel - SBT: 256KB per Channel MML MML Buffers Server Client 5
RMAN Buffers Write I/O Output Buffer Output Buffer Output BLKSIZE Buffer Output BLKSIZE Buffer BLKSIZE SBT : 256KB (BLKSIZE) DISK : 1MB 4 PGA/SGA Disk Buffer Multiplexing MIN(MAXOPENFILES,FILEPERSET) Multiplexing 4 Buffer Size 1 MB 4 > Multiplexing 8 512 KB Multiplexing > 8 128 KB Validate (and Compress, Encryption) and Copy to Output Buffer Device Type #Buffers Per Channel Buffer Size Total Buffer Size _BACKUP_KSFQ_BUFCNT (Default 16) Output 4 (_BACKUP_KSFQ_BUFCNT) 1 MB (_db_file_direct_io_count) 4MB Input Buffer Input Buffer Input Buffer Input Buffer Tape Buffer _BACKUP_KSFQ_BUFSZ _BACKUP_KSFQ_BUFSZ _BACKUP_KSFQ_BUFSZ _BACKUP_KSFQ_BUFSZ Buffer Size Parameter Buffer 수 Buffer 위치 256 KB (Output) 128 KB (Input) PARMS=BLKSIZE 11gR2:_BACKUP_SEQ_BUF SZ 11gR2:_BACKUP_SEQ_BU FCNT SIO : PGA AIO : Large Pool Read I/O Read I/O Read I/O Read I/O 6
Validate Backup Checksum - Default 로모든 Backup Block 에대해 Block 에 Checksum 값을기록한다. - 해당 Block 에이미 Checksum 값이있으면 (db_block_checksum 설정 ) Checksum 값기록을생략한다. - 주로 physical(os/hw) 문제로인한 corruption(faulty Disk, Faulty Memory, /Footer of block mismatch 등 ) 을 detect 할수있다. - NOCHECKSUM 구문으로기능을 disable 시킬수있다.(not recommend) Logical Block Check - CHECK LOGICAL 구문으로기능을 enable 한다.(default는 disable) - Logical 및 Physical Corruption을 Detect 할수있다. (ex. Row Piece, Index Entry Corruption 등 ) - 약간의 Overhead 발생 (1~10%) 7
Compressed Backup Null Block Compression - 모든 Block 을 Read 한후한번도 Data 가저장된적인없는 Block(Null Block) 만 Backup 시 Skip 함 (truncate/drop/shrink 되어도 skip 안됨 ) Unused Block Compression - LMT Bitmap 정보를읽은후현재사용중인 Block만 Read한후 Backup함 (truncate/drop purge/shrink skip됨 ) - 아래조건이모두만족되어야수행됨 COMPATIBLE 10.2 DB Version 10.2.0.2 RMAN DISK Backup or OSB Tape Backup (3 rd Party Tape Backup은지원안함 ) Locally Managed Tablespace Not Exist GRPs (Guaranteed Restore Points) Backup Set as Part of a Full Backup (level 0) Binary Compression BACKUP AS COMPRESSED BACKUPSET Command 로수행 8
Duplicate Database/TSPITR Target DB Connect Target TSPITR Auxiliary Channel Duplicate Database Connect Auxiliary Duplicate Database - RMAN 의백업파일을이용하여별도의 DB 를자동으로생성 /Open 해주는 RMAN 기능 - 특정시점테이블복구, 테스트 DB 구성, Standby DB 구성등의용도로활용할수있다 - 11g 부터는백업본이없어도 Target DB 의 Datafile 을가지고 Duplicate DB 를생성할수도있다.(Active Database Duplication) RMAN connect target sys/q1w2e3r4@mos15k; connect rcvcat rman5/rman5@swbck2; connect auxiliary /; run { allocate auxiliary channel t1 type 'SBT_TAPE'; allocate auxiliary channel t2 type 'SBT_TAPE'; send 'NSR_ENV=(NSR_SERVER=kmbck2, NSR_CLIENT=dmos15d1)'; duplicate target database to 'MOSTEST' skip tablespace APC,APC_I,COMPTXN,COMPTXN_I,EDCOL,EDCOL_I,EXTTXN,EXTTXN_I,EXT_OTHERS,EXT_OTHERS_I, LEGACY,LEGACY_I,WORKTXN,WORKTXN_I,OTHERS,OTHERS_I,PARAMETER,PARAMETER_I,RPT,RPT_I, SCHEDULE,SCHEDULE_I,TS_CUR_1,TS_CUR_1_IDX,TS_EQP_HIST_1,TS_EQP_HIST_1_IDX,TS_LOT_HIST_1, TS_LOT_HIST_1_IDX,TS_LOT_HIST_2,TS_LOT_HIST_2_IDX,TS_LOT_HIST_3,TS_LOT_HIST_3_IDX, WORKMAN,WORKMAN_I ; TSPITR (Tablespace Point-in-Time Recovery) - Auxiliary Instance 에서특정테이블스페이스만 Incomplete 복구를수행하여 Transportable Tablespace 기능을이용하여 Target DB 의특정테이블스페이스를과거시점으로복구하는기능 - RMAN 에서는 recover tablespace until time 구문만으로완전자동화 (Fully Automated) 하여운영 DB 의특정테이블스페이스를과거시점으로바꾸기때문에조심해서사용하여야한다. release channel t1; release channel t2; } 9
Block Change Tracking File File #1 bitmap extent bitmap extent bitmap extent bitmap extent X$KRCBIT bitmap extent bitmap extent bitmap extent bitmap extent 과거 Backup 이후변경된 block 정보를비트맵으로저장하여 Incremental Backup 수행시변경된 Block 만 Read 하여빠르게 Backup(Fast Incremental Backup) 할수있게지원하는 File. 각 Datafile 마다최대 8 개 Version(_bct_bitmaps_per_file) 의 Backup 에대한변경된 block 정보를 bitmap extent 에 switch 하면서저장한다.(bitmap extent 1 개에는 1 개의 version 정보만저장됨 ) 변경된 block 은 32k (_bct_chunk_size) 크기의 Chunk 단위로관리한다 RAC 환경하에서는 BCT File 을 ASM/Raw Device/CFS 같은공유 Device 에생성하여야한다. 10
Incrementally Updated Backup Time 1 Time 2 Time 3 구분 Backup Comment Full Backup (Image Copy) 1 Backup 2 Update Incremental (Backup set) 1 Backup 2 Update Incremental (Backup set) Time 1 Full 초기 Full backup Time 2 Incremental 1 Incremental backup 2 Full backup 본에 update Updated Full Backup (Image Copy) Updated Full Backup (Image Copy) Time 3 Incremental 1 Incremental backup 2 Full backup 본에 update connnect target / connect rcvcat rman5/rman5@swbck2; run { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '+DATA%u_%p_%c'; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '+DATA%u_%p_%c'; crosscheck copy tag 'PRMY_FULL_DISK'; delete noprompt expired copy tag 'PRMY_FULL_DISK'; 최초 Backup 시에는 Image Copy Full Backup(1) 을수행하고이후 Backup 수행시에는 Incremental Backup 을수행하여이때만들어지는 Incremental Backup(2) 을 Image Copy Full Backup 본에 Update 하는 Backup. 대용량 DB 환경하에서 Incremental Backup 수행만으로 Full Backup 본을생성할수있으므로 Disk Full Backup 을좀더빠르게수행할수있는 Backup 방식. backup incremental level 1 for recover of copy with tag 'PRMY_FULL_DISK' database ; recover copy of database with tag PRMY_FULL_DISK ; release channel disk1; release channel disk2; } 11
Standby DB Backup in Data Guard Standby DB Backup 구성법 Online Standby DB Backup 을구성하기위해서는반드시 Backup 정보를저장하는 Catalog DB 구성이필요하다. 왜냐하면 RMAN 은 Catalog DB 가구성안되어있을시에는 Catalog DB 대신에디폴트로 Backup 정보저장을위해서 Target DB 의 control file 을사용하는데 control file 을사용하면 Product DB 와 Standby DB 의 control file 이다르기때문에 Backup 정보를서로공유할수없다. Target DB 를 Primary DB 로설정하고 Catalog DB 로접속한다음 register database 로 Catalog DB 에 DB 정보를등록한다. Backup 시에는 Standby DB 를 Target 설정후 Catalog DB 에접속한후 Backup 을수행하면 Backup 정보가자동으로 Catalog DB 에 Primary DB Backup 정보로등록되게된다. Standby DB Backup 제약사항 Standby DB 에서는 Incrementally Updated Backup 기능이지원하지않는다. 그리고 Standby DB 의 BCT File 은 11g R1 버전부터지원이된다. connect target /; connect rcvcat rman1/rman1@swbck2; run{ allocate channel t1 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k1'; allocate channel t2 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k1'; allocate channel t3 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k1'; allocate channel t4 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k1'; allocate channel t5 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k2'; allocate channel t6 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k2'; allocate channel t7 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k2'; allocate channel t8 type 'SBT_TAPE' connect 'sys/q1w2e3r4@mos15k2'; send 'NSR_ENV=(NSR_SERVER=kmesbk, NSR_CLIENT=dmos15d1, NSR_DATA_VOLUME_POOL=VTLDB)'; backup as compressed backupset incremental level 0 filesperset 4 tag STBY_FULL_TAPE' format 'stby_%d_%u' database; release channel t1; release channel t2; release channel t3; release channel t4; release channel t5; release channel t6; release channel t7; release channel t8; } 12
Online Standby DB Backup in Data Guard & Split-Mirror SW Loca l Instance 3 Block Data file Tablespace Full DB Switch RMAN Recovery Data Guard 2 1 Online Compress Parallel RMAN Backup Rem ote Instance 단계내용 1 RMAN Online Stand by DB Backup 2 Storage Mirror/Split Backup 3 RMAN Recovery Standby DB 를 Online 으로 Backup 을수행함. Standby DB 에서 Backup 을수행하기때문에 Product DB 에전혀영향을주지않는다 (Data Gua rd 모드는 Max Performance 모드로설정 ). Image Copy, Compress Ba ckup 등다양한형태의 Backup 이가능하다. Backup Disk 형태는 Filesy stem/asm/vtl 등이가능하다. ( 만약 VTL Backup 구성이거나 Backup Disk 가같은스토리지로구성되어있을경우에는 2 단계는생략된다.) Standby DB 로받은 Backup 이저장된 Backup Disk 를 Storage Mirror 솔류션 (EMC SRDF, Hitachi UR/True Copy, HP CA 등 ) 을사용하여운영스토리지에 Backup Disk 복제본을생성한다. DB 장애시운영 Storage 에있는 Backup Disk 복제본을 Mount 하여 R MAN Recovery 작업을수행한다. Block/Datafile/Tablespace/Database 단위 Recovery 및 Image Copy Switch 등다양한 RMAN Recovery 기능을사용하여 Recovery 가능하다 Storage Mirror S/W - Step 1 : Raw Device Image Copy Backup RMAN> backup as copy DB_FILE_NAME_CONVERT ('/dev/vg/','/dev/bvg/') database reuse; - Step 2 : Disk Backup Activation ( 예 :HP-UX, True Copy) Product DB Backup Disk Backup Disk Standby DB split g DG명 -S vgchange a y /dev/bvg - Step 3 : Rename Data File RMAN> SWITCH DATABASE TO COPY; - Step 4 : Recover Database RMAN> RECOVER DATABASE; - Step 5 : Rename tempfile RMAN> run { SET NEWNAME FOR TEMPFILE 1 TO /dev/bvg/temp01 ; SWITCH TEMPFILE ALL; } - Step 6 : Open Database RMAN> alter database open; 13
Best Practice Product DB 는 Incrementally Updated Backup 방식으로 Local ASM Backup Storage 에 Backup 을수행한다. Standby DB 는 Online Compressed 방식으로 VTL 및 Remote ASM Storage 에 Backup 을수행한다. Recovery 필요시에는 Local ASM Storage 의 Backup 으로 1 차 Recovery 를수행한다. Local ASM Storage Backup 에문제가있을시에는 VTL 이나 Remote ASM Storage 에있는 Backup 을이용한다. 14