2017瀚高·第二届数据库技术峰会—Oracle数据库非常规恢复之道

感谢瀚高参与2017瀚高·第二届数据库技术峰会,提供机会和大家分享了《Oracle数据库非常规恢复之道》主题ppt分享如下:
ppt1
ppt2
ppt3
ppt4
ppt5
点击下载:2017瀚高·第二届数据库技术峰会–Oracle数据库非常规恢复之道

发表在 Oracle | 留下评论

[MySQL异常恢复]mysql ibd文件恢复

在mysql中由于某种原因保存有ibd文件,但是表已经被删除或者frm文件损坏亦或者ibdata文件损坏/丢失等。本文模拟在这种情况下,通过mysql自身技术即可完成ibd文件恢复.
测试环境mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.25    |
+-----------+
1 row in set (0.00 sec)

mysql主要参数

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 0     |
+-----------------------+-------+
1 row in set (0.00 sec)

innodb_file_per_table这个参数为on才能够实现每个表存储单独的ibd文件.innodb_force_recovery参数默认范围0

测试表情况

mysql> use xifenfei;
Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_xifenfei          |
+-----------------------------+
| user_login                  |
+-----------------------------+
1 rows in set (0.00 sec)

mysql> select count(*) from user_login;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.02 sec)

mysql> desc user_login;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID         | varchar(255) | NO   | PRI | NULL    |       |
| ACCOUNT    | varchar(255) | YES  |     | NULL    |       |
| LifeCycle  | int(11)      | YES  |     | NULL    |       |
| Name       | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| Role       | varchar(255) | YES  |     | NULL    |       |
| UTime      | varchar(255) | YES  |     | NULL    |       |
| UserID     | varchar(255) | YES  |     | NULL    |       |
| UserName   | varchar(255) | YES  |     | NULL    |       |
| UserStatus | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
10 rows in set (0.05 sec)

mysql> select * from user_login limit 1;
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| ID                               | ACCOUNT | LifeCycle | Name      | Password
                        | Role | UTime               | UserID
        | UserName | UserStatus |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh     |         0 | 胡元会    | 698d51a19
d8a121ce581499d7b701668 | |6|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL     |          1 |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
1 row in set (0.00 sec)

mysql> show create table user_login \G;
*************************** 1. row *************
       Table: user_login
Create Table: CREATE TABLE `user_login` (
  `ID` varchar(255) NOT NULL,
  `ACCOUNT` varchar(255) DEFAULT NULL,
  `LifeCycle` int(11) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Password` varchar(255) DEFAULT NULL,
  `Role` varchar(255) DEFAULT NULL,
  `UTime` varchar(255) DEFAULT NULL,
  `UserID` varchar(255) DEFAULT NULL,
  `UserName` varchar(255) DEFAULT NULL,
  `UserStatus` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> show variables like 'datadir';
+---------------+-----------------------------------------------+
| Variable_name | Value                                         |
+---------------+-----------------------------------------------+
| datadir       | D:\xifenfei\mysql-5.6.25-winx64\data\ |
+---------------+-----------------------------------------------+
1 row in set (0.00 sec)

备份ibd文件

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-02  20:07            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节
C:\Users\XIFENFEI>cp D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd d:/
C:\Users\XIFENFEI>dir d:\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 d:\ 的目录

2016-12-25  23:15            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节

模拟删除表(ibd文件也被删除)

mysql> drop table xifenfei.user_login;
Query OK, 0 rows affected (0.03 sec)


C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

找不到文件

创建新表

mysql> CREATE TABLE `user_login` (
    ->   `ID` varchar(255) NOT NULL,
    ->   `ACCOUNT` varchar(255) DEFAULT NULL,
    ->   `LifeCycle` int(11) DEFAULT NULL,
    ->   `Name` varchar(255) DEFAULT NULL,
    ->   `Password` varchar(255) DEFAULT NULL,
    ->   `Role` varchar(255) DEFAULT NULL,
    ->   `UTime` varchar(255) DEFAULT NULL,
    ->   `UserID` varchar(255) DEFAULT NULL,
    ->   `UserName` varchar(255) DEFAULT NULL,
    ->   `UserStatus` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-25  23:19            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节

mysql> select count(*) from xifenfei.user_login;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

停掉mysql,替换user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-25  23:22            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,787,141,632 可用字节

C:\Users\XIFENFEI>cp d:\user_login.ibd D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-02  20:07            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,787,141,632 可用字节
1
<strong>启动mysql 服务,查询数据库</strong>
1
<strong>mysql 日志报错</strong>
1
2016-12-25 23:31:07 11632 [Note] MySQL: ready for connections.
Version: '5.6.25'  socket: ''  port: 3306  MySQL Community Server (GPL)
InnoDB: Error: tablespace id is 56 in the data dictionary
InnoDB: but in file .\xifenfei\user_login.ibd it is 47!
2016-12-25 23:31:31 2eb8  InnoDB: Assertion failure in thread 11960 in file fil0fil.cc line 796
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be

很明显由于替换的ibd文件和现在数据库记录的ibd文件的page的字典信息不匹配,因为数据库无法正常查询该数据,而且mysql为了安全直接把实例给crash了.

恢复操作

mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> alter table xifenfei.user_login discard tablespace;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> alter table xifenfei.user_login import tablespace;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select count(*) from xifenfei.user_login;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

mysql> select * from xifenfei.user_login limit 1;
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| ID                               | ACCOUNT | LifeCycle | Name      | Password
                        | Role | UTime               | UserID
        | UserName | UserStatus |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh     |         0 | 胡元会    | 698d51a19
d8a121ce581499d7b701668 | |6|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL     |          1 |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
1 row in set (0.00 sec)

通过mysql自带的discard tablespace和import tablespace操作后,表数据已经可以完成查询了.
mysql日志

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Failed to find tablespace for table '"xifenfei"."user_login"' in the cache. Attempting to load the tablespace with space id 56.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: In file '.\xifenfei\user_login.ibd', tablespace id and flags are 47 and 0, but in the InnoDB data dictionary they are 56 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei/user_login'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 30e8 InnoDB: cannot calculate statistics for table "xifenfei"."user_login" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.
2016-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase I - Update all pages
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!
2016-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 56
2016-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 56
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase III - Flush changes to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase IV - Flush complete

mysql日志依旧报了page字典信息不匹配.但是数据已经可以访问,通过mysqldump导出重新创建表即可.如果由于ibd损坏使用该方法无法恢复,请参考:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

发表在 MySQL恢复 | 标签为 , , | 留下评论

KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type]

在oracle asm的使用过程中由于操作系统层面的错误操作导致asm disk 被破坏,这里列举了几种破坏之后的kfed报错现象(KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type])
asm mount 磁盘组报错(ORA-15040 ORA-15042)

SQL> alter diskgroup DATA mount;
alter diskgroup DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "2" is missing from group number "2"

asm alert日志报错(ORA-15335 ORA-15066 ORA-15196等)

ORA-15335: ASM metadata corruption detected in disk group 'DATA'
ORA-15130: diskgroup "DATA" is being dismounted
ORA-15066: offlining disk "DATA_0002" in group "DATA" may result in a data loss
ORA-15196: invalid ASM block header [kfc.c:26368] [endian_kfbh] [2147483651] [48] [0 != 1]

kfed查看磁盘头报错
文件文件头(不光是disk header的4k,可能是连续的几个au,甚至更多)可能彻底损坏,一般kfed 读取都会看到KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type]之类错误

[oracle@fcomtaep2 disks]$ kfed read ASMRECO03
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: blk=0
kfbh.block.obj: 0 ; 0x008: file=0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
7FC18D899400 00000000 00000000 00000000 00000000 [................]
  Repeat 27 times
7FC18D8995C0 FEEE0001 0001FFFF FFFF0000 00000FFF [................]
7FC18D8995D0 00000000 00000000 00000000 00000000 [................]
  Repeat 1 times
7FC18D8995F0 00000000 00000000 00000000 AA550000 [..............U.]
7FC18D899600 20494645 54524150 00010000 0000005C [EFI PART....\...] <==== **** Here ******
7FC18D899610 BD82BBB3 00000000 00000001 00000000 [................]
7FC18D899620 0FFFFFFF 00000000 00000022 00000000 [........".......]
7FC18D899630 0FFFFFDE 00000000 FD8857E5 42D7B49B [.........W.....B]
7FC18D899640 0901FA87 6B3DB5AA 00000002 00000000 [......=k........]
7FC18D899650 00000080 00000080 FE48EB77 00000000 [........w.H.....]
7FC18D899660 00000000 00000000 00000000 00000000 [................]
  Repeat 25 times
7FC18D899800 EBD0A0A2 4433B9E5 B668C087 C79926B7 [......3D..h..&..]
7FC18D899810 5381F6DF 4626F988 0E4F468D D78D3B28 [...S..&F.FO.(;..]
7FC18D899820 000007A1 00000000 0FFFF85F 00000000 [........_.......]
7FC18D899830 00000000 00000000 00720070 006D0069 [........p.r.i.m.]
7FC18D899840 00720061 00000079 00000000 00000000 [a.r.y...........]
7FC18D899850 00000000 00000000 00000000 00000000 [................]
 Repeat 186 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

“EFI PART”是分区的元数据,一般是被分区导致asm disk损坏.

[ebernal@dbaasm new2]$ kfed read emcpowerl | head -25
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: blk=0
kfbh.block.obj: 0 ; 0x008: file=0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
2ABD671E9400 00000000 00000000 00000000 00000000 [................]
  Repeat 31 times
2ABD671E9600 4542414C 454E4F4C 00000001 00000000 [LABELONE........]
2ABD671E9610 E4E1DDB1 00000020 324D564C 31303020 [.... ...LVM2 001] <==== **** Here ******
2ABD671E9620 50365A77 71327874 34303156 4B4E6136 [wZ6Ptx2qV1046aNK]
2ABD671E9630 35395159 5147634C 487A5A38 63575A37 [YQ95LcGQ8ZzH7ZWc]
2ABD671E9640 00000000 00000019 00030000 00000000 [................]
2ABD671E9650 00000000 00000000 00000000 00000000 [................]
2ABD671E9660 00000000 00000000 00001000 00000000 [................]
2ABD671E9670 0002F000 00000000 00000000 00000000 [................]
2ABD671E9680 00000000 00000000 00000000 00000000 [................]
  Repeat 215 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

“LVM2 001” 是逻辑卷的名字,该asm disk很可能被做为lvm管理而被破坏

[ebernal@dbaasm tars]$ kfed read rhdisk16
kfbh.endian:                         65 ; 0x000: 0x41
kfbh.hard:                           73 ; 0x001: 0x49
kfbh.type:                           88 ; 0x002: *** Unknown Enum ***
kfbh.datfmt:                         32 ; 0x003: 0x20
kfbh.block.blk:              1111709260 ; 0x004: blk=1111709260
kfbh.block.obj:              1634861056 ; 0x008: file=131072
kfbh.check:                         119 ; 0x00c: 0x00000077
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
2B6FE2AC1400 20584941 4243564C 61720000 00000077  [AIX LVCB..raw...] <==== **** Here ******
2B6FE2AC1410 00000000 00000000 00000000 00000000  [................]
2B6FE2AC1420 00000000 00000000 30300000 38306430  [..........000d08]
2B6FE2AC1430 30306131 34643030 30303030 31303030  [1a0000d400000001]
2B6FE2AC1440 61006533 766C6D73 7461645F 00003161  [3e.asmlv_data1..]
2B6FE2AC1450 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
2B6FE2AC1480 54000000 4D206575 20207961 31312037  [...Tue May  7 11]
2B6FE2AC1490 3A33343A 32203633 0A333130 00000000  [:43:36 2013.....]
2B6FE2AC14A0 65755400 79614D20 20372020 343A3131  [.Tue May  7 11:4]
2B6FE2AC14B0 34323A38 31303220 00000A33 44000000  [8:24 2013......D]
2B6FE2AC14C0 41313830 30303444 6D6D7900 02007900  [081AD400.ymm.y..]
2B6FE2AC14D0 0100E40C 656E6F4E 00000000 00000000  [....None........]
2B6FE2AC14E0 00000000 00000000 00000000 00000000  [................]
        Repeat 14 times
2B6FE2AC15D0 00000000 00000000 65310000 61653934  [..........1e49ea]
2B6FE2AC15E0 342E3862 00000000 00000000 00000000  [b8.4............]
2B6FE2AC15F0 00000000 00000000 00000000 00000000  [................]
  Repeat 224 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][88]

这里的“AIX LVCB..raw” 是AIX OS volume 的元数据库,也就是说,asm disk 被作为了aix os层面破坏

[oracle@dbep2 disks]$ kfed read asm-disk3
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: blk=0
kfbh.block.obj: 0 ; 0x008: file=0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
06000000 00000000 00000000 00000000 00000000 [................]
  Repeat 25 times
0602100 51e2b7f6 00ed4e00 00000000 00000001  [...Q.N..........]
0602120 00000000 0000000b 00000100 0000003c [............<...]
0602140 00000242 0000007b 5d8468e7 6147782a [B...{....h.]*xGa]
0602160 d17851a2 327552e2 00000000 00000000 [.Qx..Ru2........]
0602200 00000000 00000000 3130752f 91a4f000 [......../u01....] <==== **** Here ******
0602220 ff8808e4 d5104cff 000000ac 00000100 [.....L..........]
0602240 00000000 00000000 00000000 09d18000 [................]
  Repeat 254 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][88]

这里的/u01很可能表明该asm disk被文件系统覆盖

对于asm disk的各种破坏情况,如果是normal/high冗余,那么asm dg没有问题,可以考虑通过删除异常盘,然后重新加入;如果是外部冗余遭遇到asm disk 被破坏,一般asm disk 会dismount,而且无法正常mount,如果有备份的磁盘头,可以尝试还原磁盘头,mount 磁盘组,然后只读方式迁移数据;如果没有备份磁盘头或者还原之后也无法mount,可能需要通过一些额外的方式处理比如通过工具在asm dismount状态下恢复数据文件,甚至通过对asm block/oracle block碎片重组的方式恢复数据.参考相关文章:
oracle asm系列文章汇总
pvid=yes导致asm无法mount
asm disk header 彻底损坏恢复
分区无法识别导致asm diskgroup无法mount
oracle asm disk格式化恢复—格式化为ext4文件系统
oracle asm disk格式化恢复—格式化为ntfs文件系统
asm disk误设置pvid导致asm diskgroup无法mount恢复
分享oracleasm createdisk重新创建asm disk后数据0丢失恢复案例
ORA-15042: ASM disk “N” is missing from group number “M” 故障恢复

发表在 Oracle ASM, Oracle备份恢复 | 标签为 , , , , , | 留下评论

YOUR FILES ARE ENCRYPTED!—ORACLE比特币勒索恢复支持

最近Oracle数据库被黑勒索比特币的事件一起又一起,相比是最近黑客们是不是都想着弄点钱回家好过春节.前段时间分析过关于由于绿色版plsql dev的afterconnect.sql被人注入了恶意的脚本的事件具体见:plsql dev引起的数据库被黑勒索比特币实现原理分析和解决方案,对该种故障进行了分析,也提供了预防和解决方案.今天对于另一起比较常见比较常见的黑文件系统,勒索比特币的案例进行了分析,并且成功恢复出来数据.
被黑后现象
How to restore files


数据文件被加密
20161126191331
bbed分析文件

C:\Users\XIFENFEI>bbed password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Sat Nov 26 19:15:00 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename 'D:\TEMP\BAK\GCwqPFVL2zzGGBCo.orgasm'
        FILENAME        D:\TEMP\BAK\GCwqPFVL2zzGGBCo.orgasm

BBED> set blocksize 8192
        BLOCKSIZE       8192


BBED> d
 File: D:\TEMP\BAK\GCwqPFVL2zzGGBCo.orgasm (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 f993499a db22faf6 c4ee1bde efbedec0 d7c0e2a0 5408896f e3f88e2f c3179373
 0de737db 595477d5 a12c64ae 08341d67 ff93dd1f ef04568b 62f46c03 315605db
 25affd5c 9be11af8 4eff4028 26081235 8fa1f832 6e02ab4a 6295a24a 4021a418
 a4a5c934 d4ecea4d f614f413 04869513 dec22a31 43fd7a5b 51a8b29b 656e1963
 955f540a 892dff97 6a681024 c5a57176 c6168175 28e2fea6 2439fd0c cf3c8c99
 487e909d 0951aded 8ba53a61 542b1f9a cb3c5d8b 21d25c82 fbb31554 20977f8b
 f46ba6d0 5e47328a d8c4d634 97bfeefa 4b632c38 a35a2f19 06fa9be2 b1984eab
 d9a30fe2 67513bc1 657897b2 f12a2e90 acee0bd2 8bf8af82 cfc79417 f8afd2c6
 6bab98cd 16e9ddc5 40e1705f b5763002 505b8964 805c6361 a7a84980 b7826019
 a4dafb89 0b0f27c0 fa19924e c3956a43 7815f01c e9eae471 c23e52bd 4b76556c
 40895e77 c1fd70c0 7dff4132 4189788e 895590b5 57469886 90ebc360 796e426c
 913026ed b4ea7026 41f18de1 e174a0c8 7a325eb9 edb1b296 dbfc1948 25f7af82
 df999d8b c8106e84 134531ea 5f5c6461 8fd4fc3e b04be820 4c838a2f 045d818d
 8eeace89 ac1ee884 b8d56032 9fc580c4 e288fd13 6f80f6d3 f176df8b 47645955
 738a9bbd 1c0ce841 395d3d84 a071937a 74991167 41a5bdf3 970a283b 4c6ecd25
 c1f0b4da 9115574b 7cdf44c6 4f799113 a6edca86 f62a20fb 354e433b 4f2250f5

 <32 bytes per line>

BBED> set block 2
        BLOCK#          2

BBED> d
 File: D:\TEMP\BAK\GCwqPFVL2zzGGBCo.orgasm (0)
 Block: 2                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 f19a5ede 35f9e2ed d51d9b35 ef28cfe0 9deb6021 e018bf6f 2d84be28 779ee63a
 cf3172ff 49f3b959 ef92728b c0a7129a 7335afb8 a1e0fe5c 7fc5b213 8a2afc78
 78ddd22e 9ff63c2e 6432a073 adad2138 b18ee56d fab16ba3 688968f5 b53d03ca
 1f1d80d3 a87bb038 38c84b6a 74f253bc 55efc8f9 e2c1d194 26803ccc 575300b2
 025eff5c 824bca6b 440e3cc3 2f48a704 b3db6db8 bf48903e 04bf7efa 019f0986
 264806ab a8a93048 1f2d7b4e b29a923b 61a701d0 d6783f69 027f06db f4d16fba
 4b9bbd68 3a32fa66 e9e18a4c 7332a908 7e9fab7e cc8810dc 438d7157 397467b1
 d8d0e972 4b892411 bfc1bab2 6e247b4c ad2b05a8 be799d07 d1226408 0ff00bc1
 3943c5aa 63182479 6c84e5db ab213221 736af62b bb9dc047 d4a28e40 8451119c
 6db794cc 5df39d30 592d7656 0a76048d 9b5d3b3d 7d85ccb8 796c5809 ae1122e1
 73006061 d22d0dfe 9a7b839a a5c32d6a cd8ad956 5e2a8013 280fc444 9807b477
 3eda5bca 0f6a2958 e0334dfc 52c23a95 fa2cfaff a86b1456 c74a0cd9 eec77fe6
 96261513 0044e3ef ef843e13 004a9ef2 ef01d670 6c988cb7 df0dea99 58ff78ac
 aa5783e8 b6e2b89d da953d7b 3b4ea7fa 8352d388 eb6a8d76 9b9525a0 f34d444c
 83d60651 6ff7f287 bd9f8bcf 5dae9592 32db539d d0c14939 0ab4e403 ff537cfa
 9657a1be 3e5aa43d 6fdf56fd 90dbd567 b7fe4aeb d3226a29 075da375 7c3d7581

 <32 bytes per line>

通过我们的一系列分析,对于这种数据文件里面的数据已经被加密,直接使用他们肯定是无法恢复出来其中的数据,我们通过一系列的分析,已经成功找出来他们其中的规律,对这种故障实现完美恢复,如果有这个方面的恢复请求,可以随时联系我们
Phone:13429648788    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

发表在 Oracle备份恢复 | 标签为 , , , | 留下评论

通过拷贝block实现system文件大量坏块恢复

有朋友找到我,他有客户库大量坏块,需要我们提供支持,因为这个库里面含有大量的存储过程,包等,要求数据要直接导出,不能使用工具挖.
dbv检查system大量坏块

DBVERIFY: Release 11.2.0.4.0 - Production on 星期二 11月 22 17:17:51 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


DBVERIFY - 开始验证: FILE = h:\oracle\system01.dbf
页 4543 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x004011bf (file 1, block 4543)
Fractured block found during dbv: 
Data in bad block:
 type: 0 format: 2 rdba: 0x004011bf
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x7641344a
 check value in block header: 0xb6ff
 computed block checksum: 0x797

页 4544 标记为损坏
Corrupt block relative dba: 0x004011c0 (file 1, block 4544)
Bad header found during dbv: 
Data in bad block:
 type: 71 format: 3 rdba: 0x754e362f
 last change scn: 0x7a37.6d424862 seq: 0x39 flg: 0x32
 spare1: 0x35 spare2: 0x32 spare3: 0x3931
 consistency value in tail: 0x7638356c
 check value in block header: 0x4856
 block checksum disabled
 …………
页 4613 标记为损坏
Corrupt block relative dba: 0x00401205 (file 1, block 4613)
Bad header found during dbv: 
Data in bad block:
 type: 97 format: 7 rdba: 0x79634449
 last change scn: 0x4364.77426a4c seq: 0x41 flg: 0x35
 spare1: 0x34 spare2: 0x36 spare3: 0x7734
 consistency value in tail: 0x505a4550
 check value in block header: 0x434d
 computed block checksum: 0x6f3f

页 4614 标记为损坏
Corrupt block relative dba: 0x00401206 (file 1, block 4614)
Completely zero block found during dbv: 
…………
页 5125 标记为损坏
Corrupt block relative dba: 0x00401405 (file 1, block 5125)
Completely zero block found during dbv: 


DBVERIFY - 验证完成

检查的页总数: 124160
处理的页总数 (数据): 90745
失败的页总数 (数据): 0
处理的页总数 (索引): 14417
失败的页总数 (索引): 0
处理的页总数 (其他): 3323
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 15092
标记为损坏的总页数: 583
流入的页总数: 5
加密的总页数        : 0
最高块 SCN            : 1417256245 (2.1417256245)

这里比较明显,一共583个坏块,而且是连续坏块(5125-4543+1)

尝试启动数据库

--直接尝试打开数据库
SQL> RECOVER DATABASE;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01578: ORACLE data block corrupted (file # 1, block # 4575)
ORA-01110: data file 1: 'H:\ORACLE\SYSTEM01.DBF'
进程 ID: 2572
会话 ID: 85 序列号: 1

--跳过坏块event打开库
SQL> startup mount pfile='h:/oracle/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 2137886720 bytes
Fixed Size                  2282944 bytes
Variable Size             520096320 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4894720 bytes
数据库装载完毕。
SQL> show parameter event;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      43810 trace name context forev
                                                 er, level 10, 10231 trace name
                                                  context forever, level 10, 10
                                                 232 trace name context forever
                                                 , level 10, 10233 trace name c
                                                 ontext forever, level 10, 1004
                                                 1 trace name context forever,
                                                 level 10
xml_db_events                        string      enable
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'H:\ORACLE\SYSTEM01.DBF'


SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [],
[], [], [], [], []
进程 ID: 9316
会话 ID: 4 序列号: 3

--upgrade方式打开数据库
SQL> startup mount pfile='h:/oracle/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 2137886720 bytes
Fixed Size                  2282944 bytes
Variable Size             520096320 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4894720 bytes
数据库装载完毕。
SQL> alter database open upgrade
  2  ;
alter database open upgrade
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [],
[], [], [], [], []
进程 ID: 7976
会话 ID: 4 序列号: 3

至此数据库在这种坏块情况下,正常打开相当渺茫,因为报错的这些block 都是非常靠前的,也就是说这个里面很多块在数据库创建好的时候就已经在了(特别是通过模板创建的数据库,这些部分很可能都是固定的),考虑使用其他库的block来替代这些坏块,然后尝试打开库

修复坏块

[oracle@app101-20 ~]$ dd if=/Data/oracle/oradata/txlhdb/system01.dbf of=/tmp/1.dbf skip=4543 bs=8192 count=583
583+0 records in
583+0 records out
4775936 bytes (4.8 MB) copied, 0.0533578 s, 89.5 MB/s

H:\oracle>dd if=d:/temp/1.dbf of=h:\oracle\system01.dbf seek=4543 bs=8192 count=583 conv=notrun
rawwrite dd for windows version 0.6beta3.
Written by John Newbigin <jn@it.swin.edu.au>
This program is covered by terms of the GPL Version 2.

notrun
583+0 records in
583+0 records out

H:\oracle>dbv file=system01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on 星期二 11月 22 20:17:51 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\ORACLE\SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 124160
处理的页总数 (数据): 90761
失败的页总数 (数据): 0
处理的页总数 (索引): 14479
失败的页总数 (索引): 0
处理的页总数 (其他): 3393
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 15527
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 295310052 (11.295310052)

再次尝试打开数据库

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期二 11月 22 20:18:19 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup mount pfile='h:/oracle/pfile.txt';
ORACLE 例程已经启动。

Total System Global Area 2137886720 bytes
Fixed Size                  2282944 bytes
Variable Size             520096320 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4894720 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

后续错误ORA-04023处理

C:\Users\XIFENFEI>exp "'/ as sysdba'" owner=XIFENFEI file=d:/full_xff.dmp lo
g=d:/full_xff.log   FEEDBACK=10000  COMPRESS=NO BUFFER=102400000 STATISTICS=none


Export: Release 11.2.0.4.0 - Production on 星期二 11月 22 20:20:27 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00056: 遇到 ORACLE 错误 4023
ORA-04023: 无法验证或授权对象 SELECT xdb_uid FROM SYS.EXU9XDBUID
EXP-00000: 导出终止失败

数据库所有视图无法查询,通过直接对基表user$,obj$,view$等表查询出来视图信息,然后直接编译,然后数据可以完美导出,完成本次恢复

SQL> select 'alter view '||b.name||'.'||c.name||'  compile;'
  2  from view$ a,user$ b,obj$ c
  3  where a.obj#=c.obj#
  4  and c.owner#=b.user#;

1


发表在 非常规恢复 | 标签为 , , , , | 留下评论