Slide 1

Size: px

Start display at page:

Download "Slide 1"

奠誉段
5 years ago
Views:

1 数据库非常规恢复 Lunar

2 以下内容仅限于在非生产环境测试, 不能用于生产环境数据库, 所有 Internal 的方法 (BBED, DUL 等等 ), 请咨询 Oracle Support 部门, 这些方法未经 Oracle Support 建议和许可, 不得使用, 否则后果自负!! 9-2

3 常见错误 9-3

4 ORA-600 [4xxx] 9-4

5 Setting Multiple Events In the parameter file, you have two methods: 1. Use multiple, consecutive event lines: event = "10015 trace name context forever" event = "10046 trace name context forever, level 4" 2. Concatenate the events with a colon (:) as the separator: event = "10015 trace name context forever: trace name context forever, level 4" 9-5

6 Event oradebug setmypid oradebug EVENT TRACE NAME CONTEXT FOREVER, LEVEL 12 alter session set db_file_multiblock_read_count=1; oradebug TRACEFILE_NAME alter database Open; oradebug close_trace; 9-6

7 基本概念和名词解释 UBA: Undo block address RBA: Redo block address Dba: Data block address Rdba: Root dba Xid: Transaction ID ITL: Interested Transaction List 9-7

8 Oracle ROWID Format OOOOOOFFFBBBBBBSSS Slot (row) number Block number Relative file number Data object number 9-8

9 UBA 和 XID Converting DBA's (Database Addresses) to File # and Block # (Doc ID )--- External 9-9

10 Instance Startup Using AUM 1 Obtain UNDO_TABLESPACE from init.ora Is UNDO_TABLESPACE specified? y 2 Is enqueue available? ORA y Scan TS$ for first avail undo ts Acquire enqueue in X mode 5 UNDO tablespace found? y 6 Activate UNDO tablespace Online Tx tables within tablespace Finished TS$ scan? y ORA

11 Transaction Identifiers Transaction identifiers (XID) uniquely identify a transaction within the system; they are used within the Interested Transaction List (ITL) of the data block. A transaction identifier consists of: Undo segment number Transaction table slot number Sequence number or wrap# XID = usn#. slot#. wrap# 9-11

12 Undo Block Address The undo block address (UBA) uniquely identifies the undo block for a given transaction; it is found within the ITL of the data block. A UBA consists of: Data block address (DBA) of the block The sequence number of the block The record number within the block UBA = DBA. seq#. rec# 9-12

13 Undo Block Cache layer XID Seq# #Records Idx RB. Offset rec #1 Offset rec #2 Offset rec #3 Offset rec #4 Idx collect info... Flags Offset rec #n Free area Undo records Undo record #2 Undo record #1 Footer 9-13

14 Transaction Undo Chain Undo block U n Undo block U n-1 Tx i Tx i Transaction table slot U n-1 1st undo record Tx i U n Offset last record U 0 Chained undo records of Tx i 9-14

15 Example: Undo Record Chain Dump Undo chain TX table UNDO BLK: xid: 0x a.00000ced seq: 0x266 cnt: 0x3 irb: 0x3 icl: 0x0 flg:0x * *Rec #0x1 slt: 0x0a objn: 11987(0x00002ed3) objd: tblspc:4(0x ) Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x * uba: 0x00c ctl max scn:0x a135 prv tx scn:0x a *Rec #0x2 slt: 0x0a objn: 11987(0x00002ed3) objd: tblspc:4(0x ) * Layer: 11 (Row) opc: 1 rci 0x01 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x * *Rec #0x3 slt: 0x0a objn: 11987(0x00002ed3) objd: tblspc:4(0x ) * Layer: 11 (Row) opc: 1 rci 0x02 Undo type: Regular undo Last buffer split: No

16 Data Block Header: Cache Layer Cache layer Transaction layer Common Fixed and variable Table directory Row directory Data layer Free space Row data 9-16

17 Block Usage: An Example row1 offset 5 row2 offset 4 row3 offset 3 row4 offset 2 row5 offset 1 Row 5 Row 4 Row 3 Row 2 Row 1 1. Insert from the bottom of the block and move up. 2. Try to reuse first free slot in row index. 3. Attempt to get space from free space. 4. If not enough space, compress block. 9-17

18 Row Format: Column Data Column data is stored as a series of column length values and column data pairs. Extract from a logical block dump: tab 0, row tl: 6 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 2] c1 02 tab 0, row tl: 226 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [219] 6e 6f 4b 4c 4d... Flag byte(fb): H=header, F=first, L=last (entire row) Column count (cc) = 1, a NULL column in position 2 Column length is 2 bytes, the data bytes are c

19 Row Migration and Chaining Row migration: A row no longer fits in its original block. Row flag (1) Lock byte (1) # of cols (1) Next DBA (4) Next row slot (2) Migrated row Row flag (1) Lock byte (1) # of cols (1) Next DBA in chain (4) Next row slot (2) Column length (1 or 3) Column data

20 相关知识控制文件相关 scn v$database. checkpoint_change# v$datafile. checkpoint_change# 数据文件相关 scn v$datafile_header. checkpoint_change# 文件头模糊 v$datafile_header. FUZZY v$datafile. last_change# 9-20

21 9-21

22 1a slot 26 State 未提交事物 Dba----- uba---- 0x008004e0 9-22

oradebug setmypid alter system dump undo block '_SYSSMU5$' xid 5 26

header: Undo block xid: 04e0-- block 1248 xid: 0x0005.01a.

23 oradebug setmypid alter system dump undo block '_SYSSMU5$' xid ; oradebug close_trace oradebug tracefile_name Undo segment header: Undo block xid: 04e0-- block 1248 xid: 0x a Undo Segment Number : 5 Transaction Table Slot Number : 26 Wrap :

24 9-24

25 9-25

26 1, 事物恢复的起点 irb: recoder 0x5, cnt 对应 V$TRANSACTION.UBAREC 2, 从 recoder 5 找到前一个需要恢复的记录 0x04 3, recoder 5 中记录了 before image: 9-26

27 9-27

28 9-28

29 BBED 初认识从 Oracle 的部分平台和 Oracle 8 开始随产品一起发布从 Oracle 9i 以后,BBED 不再随产品发布,Linux 和 Unix 下需要手工编译 BBED is a SUPPORT ONLY tool and should NOT be discussed with customers. 9-29

30 BBED 初认识 9-30

31 BBED 安装和初使用 ---Oracle 8~8i UNIX 平台 : 从 Oracle 8 开始随产品附带, 需要单独编译 : 9-31

32 BBED 安装和初使用 ---9i~10g 在 9i/10g 中连接生成 bbed: cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed mv bbed $ORACLE_HOME/bin 9-32

33 BBED 安装和初使用 ---11g~12c 在 11g 和 12c 中生成 bbed: cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed 9-33

34 BBED 和 ASM 文件 9-34

35 BBED 常用命令 9-35

36 案例分析

37 Oracle 进行延迟块清除时, 会查询回滚段头以确认事务状态, 一些异常情况或者 smon_scn_time 信息紊乱等可能造成回滚段信息损坏, 使得系统查询到的回滚段的信息超前于当前数据库的状态, 类似情况就会造成 ORA-600 [4097] 错误 9-37

38 案例分析

39 9-39

40 9-40

41 How to identify which rollback segment (Undo Segment) is corrupted? 9-41

42 9-42

43 案例分析 3 SQL> startup ORACLE 例程已经启动 Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers Redo Buffers bytes bytes 数据库装载完毕 ORA-00368: 重做日志块中的校验和错误 ORA-00353: 日志损坏接近块更改时间 12/05/ :21:11 ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG SQL> col member for a35 SQL> select a.group#,a.status,b.member from v$log a,v$logfile b 2 where a.group#=b.group#; GROUP# STATUS MEMBER CURRENT R:\ORADATA\HZYL\REDO03.LOG 2 INACTIVE R:\ORADATA\HZYL\REDO02.LOG 1 INACTIVE R:\ORADATA\HZYL\REDO01.LOG 9-43

44 SQL> recover database until cancel; SQL>alter database open resetlogs ORA-01547: 警告 : RECOVER 成功但 OPEN RESETLOGS 将出现如下错误 ORA-01194: 文件 1 需要更多的恢复来保持一致性 ORA-01110: 数据文件 1: 'R:\ORADATA\HZYL\SYSTEM01.DBF 设置 _allow_resetlogs_corruption=true recover database until cancel; alter database open resetlogs; 导出导入重建数据库 9-44

45 案例分析 4 数据库启动 alert 日志报如下错误 Tue Feb 14 09:34: Errors in file d:\oracle\product\10.2.0\admin\interlib\bdump\interlib_smon_2784.trc: ORA-01595: error freeing extent (2) of rollback segment (3)) ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [6], [30], [], [], [], [], [] Tue Feb 14 09:35: Errors in file d:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2824.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [4193], [2005], [2008], [], [], [], [], [] ORA-00600: internal error code, arguments: [4193], [2005], [2008], [], [], [], [], [] 9-45

46 EVENT

47 案例分析 5 SYS.I_DEPENDENCY1 or SYS.I_DEPENDENCY2 9-47

48 ORA-600 [4194] 9-48

49 ORA-600 [4194] 9-49

50 ORA-600 [4193] 9-50

51 ORA-600 [4097] 9-51

52 Event

53 常用隐含参数 _allow_resetlogs_corruption Actvie/Current redo log 坏块,IO 错误, 丢失等因为 redo log 异常导致数据库不能启动主要是屏蔽 redo 前滚, 强制打开数据库, 可能导致 redo 中数据丢失, 使用需要慎重 _offline_rollback_segments 强制把异常 undo 设置为 offline 状态, 主要处理回滚段存在但是异常情况 _corrupted_rollback_segments 强制直接标记回滚段不正常, 主要用于处理回滚段损坏严重 ( 比如丢失 ), 比 _offline_rollback_segments 对数据库的破坏性更加严重 Undo 段出现异常无法正常回滚回滚事务, 导致数据库无法打开, 例如含回滚事务的回滚段 block 出现坏块, 回滚段和 redo 前滚信息不一致等通过设置该参数屏蔽回滚段 ( 该回滚段未提交事务自动提交 ), 将导致数据不一致, 使用需要慎重 9-53

54 BBED 的用途 bbed 在数据库非 open 情况下修改 block 内容主要用于一些场景恢复 1) 缺少归档情况下数据文件 online 2) 系统基表事务未提交数据库不能 open 3) 部分坏块修复 4) 9-54

55 Diagnostic Events Event 10231: Skip corrupted blocks during a full table scan Event 10232: Dump corrupted blocks in a trace file Event 10233: Skip corrupted data or index blocks on index range scans 9-55

56 dul dul 是在数据库不正常 open 情况下, 直接读取数据文件恢复数据内容 Dul 主要用于以下场景恢复 1) 数据库使用各种方法无法 open 2) 无删除表恢复 3)truncate table 删除 4) 丢失 system 恢复 9-56

57 9-57

58 dul 初试 Init.dul 文件配置 osd_big_endian_flag=false osd_dba_file_bits=10 osd_c_struct_alignment=32 osd_file_leader_size=1 osd_word_size = 32 dc_columns= dc_tables=10000 dc_objects= dc_users=400 dc_segments= Buffer= control_file = control.txt db_block_size=8192 export_mode=true compatible=

59 Control.txt 文件配置通过启动数据库到 mount 执行 select ts#,rfile#,name from v$datafile 获得 dul]$ more control.txt 0 1 /u01/oracle/oradata/lunar/system01.dbf 1 2 /u01/oracle/oradata/lunar/undotbs01.dbf 2 3 /u01/oracle/oradata/lunar/sysaux01.dbf 4 4 /u01/oracle/oradata/lunar/users01.dbf 6 5 /u01/oracle/oradata/lunar/datfttuser.dbf 9-59

60 Dul 开始干活 dul]$./dul Data UnLoader: Internal Only - on Sun Jun 10 06:39: with 64-bit io functions Copyright (c) Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only Found db_id = Found db_name = lunar 加载数据字典 DUL> BOOTSTRAP; Unload table DUL> UNLOAD TABLE hr.test; 9-60

Client Databases Architected for Protection of Critical Data Database Delta Push DBs

ship for near-zero data loss Oracle Database Backup Logging Recovery Appliance Autonomous

from tape Tape Library Cloud Scale Scales to 1000s of Clients Petabytes of Data No

restores to any point-in-time using deltas Built on Exadata scaling & resilience Complete

61 Client Databases Architected for Protection of Critical Data Database Delta Push DBs access and send only changed data Minimal impact on production servers Real-time redo ship for near-zero data loss Oracle Database Backup Logging Recovery Appliance Autonomous Archive Copy to tape: no production server load Tapes utilized all day Restore directly from tape Tape Library Cloud Scale Scales to 1000s of Clients Petabytes of Data No expensive backup agents Delta Store Validated, compressed database change data Fast restores to any point-in-time using deltas Built on Exadata scaling & resilience Complete Data Protection Solution Replication for DR Unified management: database to tape Per-database provisioning 9-61

62 Q & A 9-62

预备知识控制文件相关 scn v$database. checkpoint_change# v$datafile. checkpoint_change# 点击输入文字数据文件相关 scn v$datafile_header.checkpoint_change# 数据库干净判断 v$datafil

预备知识控制文件相关 scn v$database. checkpoint_change# v$datafile. checkpoint_change# 点击输入文字数据文件相关 scn v$datafile_header.checkpoint_change# 数据库干净判断 v$datafil Oracle 数据库非常规恢复之道程飞网名 : 惜分飞 QQ 号 :107644445 邮箱 :dba@xifenfei.com 技术 BLOG:www.xifenfie.com 预备知识控制文件相关 scn v$database. checkpoint_change# v$datafile. checkpoint_change# 点击输入文字数据文件相关 scn v$datafile_header.checkpoint_change#