Daily Archives: 2015 年 08 月 23 日

MySQL drop database恢复

Posted on by

昨天晚上接到一个网络服务请求，由于不小心点击了自己产品软件上面的清空数据功能(这个工具确实需要小心,在Oracle恢复案例中,也有xx企业erp该功能导致数据被删除请求恢复)，导致MySQL数据库被直接drop database掉了，之前没有做任何备份，只是发生故障之后，他们立即封存现场，备份出来了ibdata1文件。接到请求之后，通过让其把ibdata1文件发给过来，通过MySQL recovery工具进行分析恢复，经过6个小时的处理，基本上实现核心数据0丢失.
解析ibdata1(innodb文件)

[root@localhost recovery_MySQL]# ./stream_parser -f /tmp/ibdata1
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
File information:

time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 total size, in bytes:             18874368 (18.000 MiB)

(regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
number of hard links:                    1
user ID of owner:                        0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
group ID of owner:                       0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
group ID of owner:                       0
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)

File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)

File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
File information:

total size, in bytes:             18874368 (18.000 MiB)

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 Size to process:                  18874368 (18.000 MiB)
(regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
Opening file: /tmp/ibdata1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
File information:

blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
Size to process:                  18874368 (18.000 MiB)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
Opening file: /tmp/ibdata1
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
File information:

time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
total size, in bytes:             18874368 (18.000 MiB)

inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
Size to process:                  18874368 (18.000 MiB)
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
Size to process:                  18874368 (18.000 MiB)
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
device ID (if special file):             0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
protection:                         100644 (regular file)
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of hard links:                    1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
total size, in bytes:             18874368 (18.000 MiB)

number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec

[root@localhost recovery_MySQL]# cd pages-ibdata1
[root@localhost pages-ibdata1]# ls -l
total 16
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_INDEX
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_TYPE_BLOB
[root@localhost pages-ibdata1]# cd FIL_PAGE_INDEX
[root@localhost FIL_PAGE_INDEX]# ls -l
total 9924
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000001.page
-rw-r--r-- 1 root root  278528 Aug 20 22:53 0000000000000002.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000003.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000004.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000005.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000011.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000012.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000013.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000014.page
-rw-r--r-- 1 root root 2883584 Aug 20 22:53 0000000000000065.page
-rw-r--r-- 1 root root  475136 Aug 20 22:53 0000000000000066.page
-rw-r--r-- 1 root root  737280 Aug 20 22:53 0000000000000067.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000068.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000069.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000070.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000071.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000072.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000073.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000074.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000075.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000076.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000077.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000078.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000079.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000080.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000081.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000082.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000083.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000084.page
-rw-r--r-- 1 root root  753664 Aug 20 22:53 0000000000000085.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000086.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000087.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000088.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000089.page
-rw-r--r-- 1 root root  901120 Aug 20 22:53 0000000000000110.page
-rw-r--r-- 1 root root 2097152 Aug 20 22:53 0000000000000115.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000116.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000117.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000118.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000119.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000120.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000121.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000122.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000123.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000124.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 18446744069414584320.page
[root@localhost FIL_PAGE_INDEX]#

分析数据字典

[root@localhost recovery_MySQL]# ./recover_dictionary.sh

Generating dictionary tables dumps... OK
Creating test database ... Warning: Using a password on the command line interface can be insecure.
OK
Creating dictionary tables in database test:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
OK
All OK
Loading dictionary tables data:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
34 recs OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
3061 recs OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
All OK

关于MySQL数据字典恢复,也可以参考另外一篇文章:使用工具直接抽取MySQL数据字典

查看数据库中记录情况

[root@localhost recovery_MySQL]# MySQL -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34954
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    |
| SYS_FIELDS     |
| SYS_INDEXES    |
| SYS_TABLES     |
+----------------+
4 rows in set (0.00 sec)

MySQL> SELECT NAME,ID from SYS_TABLES;
+-------------------------+----+
| NAME                    | ID |
+-------------------------+----+
| cnywmcom/zx_account_log | 31 |
| cnywmcom/zx_area_code   | 32 |
| cnywmcom/zx_goods_group | 33 |
| cnywmcom/zx_manage_type | 34 |
| cnywmcom/zx_order_goods | 35 |
| cnywmcom/zx_order_info  | 36 |
| cnywmcom/zx_param       | 37 |
| cnywmcom/zx_users       | 46 |
| cnywmcom/zx_user_type   | 38 |
| SYS_FOREIGN             | 11 |
| SYS_FOREIGN_COLS        | 12 |
+-------------------------+----+
11 rows in set (0.00 sec)

MySQL> select * from SYS_INDEXES;
+----------+-----+-----------------+----------+------+-------+---------+
| TABLE_ID | ID  | NAME            | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+-----------------+----------+------+-------+---------+
|       11 |  11 | ID_IND          |        1 |    3 |     0 |      46 |
|       11 |  12 | FOR_IND         |        1 |    0 |     0 |      47 |
|       11 |  13 | REF_IND         |        1 |    0 |     0 |      48 |
|       12 |  14 | ID_IND          |        2 |    3 |     0 |      49 |
|       31 |  65 | PRIMARY         |        1 |    3 |     0 |      50 |
|       31 |  66 | user_id         |        1 |    0 |     0 |     216 |
|       32 |  67 | PRIMARY         |        1 |    3 |     0 |      52 |
|       33 |  68 | PRIMARY         |        1 |    3 |     0 |     205 |
|       34 |  69 | PRIMARY         |        1 |    3 |     0 |      54 |
|       35 |  70 | PRIMARY         |        1 |    3 |     0 |      55 |
|       35 |  71 | order_id        |        1 |    0 |     0 |      56 |
|       35 |  72 | goods_id        |        1 |    0 |     0 |      57 |
|       36 |  73 | PRIMARY         |        1 |    3 |     0 |      59 |
|       36 |  74 | order_sn        |        1 |    2 |     0 |      60 |
|       36 |  75 | user_id         |        1 |    0 |     0 |      61 |
|       36 |  76 | order_status    |        1 |    0 |     0 |      62 |
|       36 |  77 | shipping_status |        1 |    0 |     0 |      63 |
|       36 |  78 | pay_status      |        1 |    0 |     0 |     192 |
|       36 |  79 | shipping_id     |        1 |    0 |     0 |     193 |
|       36 |  80 | pay_id          |        1 |    0 |     0 |     194 |
|       36 |  81 | extension_code  |        2 |    0 |     0 |     195 |
|       36 |  82 | agency_id       |        1 |    0 |     0 |     203 |
|       37 |  83 | PRIMARY         |        1 |    3 |     0 |     196 |
|       38 |  84 | PRIMARY         |        1 |    3 |     0 |     206 |
|       46 | 120 | PRIMARY         |        1 |    3 |     0 |      58 |
|       46 | 121 | user_name       |        1 |    2 |     0 |     256 |
|       46 | 122 | email           |        1 |    0 |     0 |     257 |
|       46 | 123 | parent_id       |        1 |    0 |     0 |     258 |
|       46 | 124 | flag            |        1 |    0 |     0 |     259 |
+----------+-----+-----------------+----------+------+-------+---------+
29 rows in set (0.00 sec)

通过这里，我们就可以明确的知道，我们需要恢复的表的page是多少了，这里举例说明恢复表cnywmcom.zx_account_log,我们需要去分析65号page

分析表恢复

[root@localhost recovery_MySQL]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t dictionary/zx_account_log.sql|more
-- Page id: 223, Format: COMPACT, Records list: Invalid, Expected records: (0 7)
0B0C2000101C    55000230000000  zx_account_log  2979181 270888  "0"0"0.00"      "0.00"  "0.00"  2147483649      ""      0       ""      "0.00"  "-10643256.27"  "06."   "06."   "0782
7251.51"        "0.5"   "0.5"   "0.5"   "0.5"
000000042228    00000001040135  zx_account_log  561     1       "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436840718      "会员注册"      99      "13818289939"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    0000000104015A  zx_account_log  562     136     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436841067      "会员注册"      99      "17828025855"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    0000000104017F  zx_account_log  563     137     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436842239      "会员注册"      99      "18617157667"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    000000010401A4  zx_account_log  564     139     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436846738      "会员注册"      99      "18113070688"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    000000010401C9  zx_account_log  565     1       "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436848523      "会员注册"      99      "13623646573"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"

至此该表的数据已经恢复出来,剩下任务就是把数据保存为文件,并且导入到新库中.然后依次处理相关表.至此完成这次drop database的恢复.在恢复过程中,遇到不少坑,比如有些记录在对应的page中无法找到,需要进行额外处理.对于乱码问题需要进一步处理等.通过一系列的恢复,给客户恢复了所有核心表,实现核心数据0丢失.通过这次的恢复,已经证明我们不光是Oracle恢复中的行业强者,在MySQL数据库的各种故障中,我们也有立足之地.如果有MySQL数据库的恢复(误删除数据/delete,误删除表/drop table,误截断表/truncate table,误删除数据库/drop database,MySQL不能启动,ibdata1文件损坏/丢失等MySQL各种恢复)请求,如果您遇到MySQL恢复问题无法自行解决,请联系我们提供专业服务,最大程度减小您的损失：

Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

跳过dmp文件坏块

Posted on by

在有些情况下，大家都知道通过dul可以恢复损坏的dmp文件的表的数据,但是该方法有很多问题，特别是对很多数据类型的支持不够完美,比如lob,long raw类型等,而且还有可能恢复出来数据大量丢失,本人通过对dmp结构的分析,使用使用一些特殊的技巧方法,可以实现对于损坏的dmp文件,通过跳过异常坏块所在表,继续恢复后续表,从而最大程度减少损坏
创建测试表

SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> create table t_xifenfei1 as select * from dba_objects;

Table created.

SQL> create table t_xifenfei2 as select * from v$sql;

Table created.

SQL> create table t_xifenfei3 as select * from dba_tables;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
T_XIFENFEI2                    TABLE
T_XIFENFEI3                    TABLE

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86275

SQL>  select count(*) from t_xifenfei2;

  COUNT(*)
----------
      3387

SQL>  select count(*) from t_xifenfei3;

  COUNT(*)
----------
      2800

导出来dmp文件

[oracle@web103 ~]$ exp "'/ as sysdba'" owner=xifenfei file=/data/temp/t_xifenfei.dmp log=/data/temp/exp_t_xifenfei.log

Export: Release 11.2.0.4.0 - Production on Tue Aug 18 22:08:30 2015

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user XIFENFEI 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user XIFENFEI 
About to export XIFENFEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export XIFENFEI's tables via Conventional Path ...
. . exporting table                    T_XIFENFEI1      86275 rows exported
. . exporting table                    T_XIFENFEI2       3387 rows exported
. . exporting table                    T_XIFENFEI3       2800 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

获取dmp file中的T_XIFENFEI2所在位置
计划在该表上通过dd进行破坏,因此需要事先知道该表所在的dmp文件位置范围

CPFL> SEARCH TABLE T_XIFENFEI2 FROM EXPFILE  /tmp/t_xifenfei.dmp
9980561: TABLE "T_XIFENFEI2"
9980581: CREATE TABLE "T_XIFENFEI2" (表定义忽略)
9985356: BIND information for 87 columns
 col[  1] type 1 max length 1000 cset 852 (ZHS16GBK) form 1
 col[  2] type 112 max length 86 cset 852 (ZHS16GBK) form 1
 col[  3] type 1 max length 13 cset 852 (ZHS16GBK) form 1
 col[  4] type 2 max length 22
 col[  5] type 2 max length 22
 col[  6] type 2 max length 22
 col[  7] type 2 max length 22
 col[  8] type 2 max length 22
 col[  9] type 2 max length 22
 col[ 10] type 2 max length 22
 col[ 11] type 2 max length 22
 col[ 12] type 2 max length 22
 col[ 13] type 2 max length 22
 col[ 14] type 2 max length 22
 col[ 15] type 2 max length 22
 col[ 16] type 2 max length 22
 col[ 17] type 1 max length 38 cset 852 (ZHS16GBK) form 1
 col[ 18] type 2 max length 22
 col[ 19] type 2 max length 22
 col[ 20] type 2 max length 22
 col[ 21] type 2 max length 22
 col[ 22] type 2 max length 22
 col[ 23] type 2 max length 22
 col[ 24] type 2 max length 22
 col[ 25] type 2 max length 22
 col[ 26] type 2 max length 22
 col[ 27] type 2 max length 22
 col[ 28] type 2 max length 22
 col[ 29] type 2 max length 22
 col[ 30] type 2 max length 22
 col[ 31] type 1 max length 10 cset 852 (ZHS16GBK) form 1
 col[ 32] type 2 max length 22
 col[ 33] type 23 max length 2000
 col[ 34] type 2 max length 22
 col[ 35] type 2 max length 22
 col[ 36] type 2 max length 22
 col[ 37] type 1 max length 30 cset 852 (ZHS16GBK) form 1
 col[ 38] type 2 max length 22
 col[ 39] type 23 max length 8
 col[ 40] type 23 max length 8
 col[ 41] type 2 max length 22
 col[ 42] type 2 max length 22
 col[ 43] type 2 max length 22
 col[ 44] type 2 max length 22
 col[ 45] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 46] type 2 max length 22
 col[ 47] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 48] type 2 max length 22
 col[ 49] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 50] type 2 max length 22
 col[ 51] type 2 max length 22
 col[ 52] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 53] type 2 max length 22
 col[ 54] type 2 max length 22
 col[ 55] type 2 max length 22
 col[ 56] type 23 max length 8
 col[ 57] type 2 max length 22
 col[ 58] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 59] type 1 max length 19 cset 852 (ZHS16GBK) form 1
 col[ 60] type 2 max length 22
 col[ 61] type 1 max length 38 cset 852 (ZHS16GBK) form 1
 col[ 62] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 63] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 64] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 65] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 66] type 2 max length 22
 col[ 67] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 68] type 1 max length 30 cset 852 (ZHS16GBK) form 1
 col[ 69] type 1 max length 30 cset 852 (ZHS16GBK) form 1
 col[ 70] type 2 max length 22
 col[ 71] type 2 max length 22
 col[ 72] type 2 max length 22
 col[ 73] type 2 max length 22
 col[ 74] type 12 max length 7
 col[ 75] type 23 max length 2000
 col[ 76] type 2 max length 22
 col[ 77] type 2 max length 22
 col[ 78] type 2 max length 22
 col[ 79] type 2 max length 22
 col[ 80] type 2 max length 22
 col[ 81] type 2 max length 22
 col[ 82] type 2 max length 22
 col[ 83] type 2 max length 22
 col[ 84] type 2 max length 22
 col[ 85] type 2 max length 22
 col[ 86] type 2 max length 22
 col[ 87] type 2 max length 22
Conventional export
9986063: start of table data
19675141: TABLE "T_XIFENFEI3"

使用dd命令破坏T_XIFENFEI2所在位置的dmp文件

[oracle@web103 ~]$ dd if=/dev/zero of=/data/temp/t_xifenfei.dmp bs=1024 count=2 conv=notrunc seek=9747
2+0 records in
2+0 records out
2048 bytes (2.0 kB) copied, 1.6e-05 seconds, 128 MB/s

尝试imp导入被破坏的dmp文件数据

[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew 
>file=/data/temp/t_xifenfei.dmp log=/data/temp/imp_t_xifenfei.log

Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:35:09 2015

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table                  "T_XIFENFEI1"      86275 rows imported
IMP-00017: following statement failed with ORACLE error 1740:
 "CREATE TABLE "T_XIFENFEI2" ("SQL_TEXT" VARCHAR2(1000), "SQL_FULLTEXT" CLOB,"
 " "SQL_ID" VARCHAR2(13), "SHARABLE_MEM" NUMBER, "PERSISTENT_MEM" NUMBER, "RU"
 "NTIME_MEM" NUMBER, "SORTS" NUMBER, "LOADED_VERSIONS" NUMBER, "OPEN_VERSIONS"
 "" NUMBER, "USERS_OPENING" NUMBER, "FETCHES" NUMBER, "EXECUTIONS" NUMBER, "P"
 "X_SERVERS_EXECUTIONS" NUMBER, "END_OF_FETCH_COU"
IMP-00003: ORACLE error 1740 encountered
ORA-01740: missing double quote in identifier
IMP-00008: unrecognized statement in the export file: 
  
IMP-00008: unrecognized statement in the export file:

导入报IMP-00003 ORA-01740 IMP-00008,由于dmp文件被dd破坏(而且破坏位置是T_XIFENFEI2所在之处),因此imp导入到T_XIFENFEI2之时,抛出大量异常,imp终止

检查导入表情况

SQL> conn xifenfeinew/"www.xifenfei.com"
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86275

和预期相符,表t_xifenfei1导入进去,但是t_xifenfei2由于坏块原因未导入,由于t_xifenfei3在t_xifenfei2之后,因此也未导入

使用CPFL程序抽取正常dmp文件

CPFL>getdmp '/data/temp/t_xifenfei.dmp' skip table 'T_XIFENFEI2'
>/data/temp/t_xifenfeinew.dmp

重新导入dmp文件

[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew 
>file=/data/temp/t_xifenfeinew.dmp log=/data/temp/imp_t_xifenfeinew.log

Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:41:04 2015

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table                  "T_XIFENFEI1"      86275 rows imported 
. . importing table                  "T_XIFENFEI3"       2800 rows imported
Import terminated successfully no warnings.

导入了t_xifenfei1，t_xifenfei3，果然t_xifenfei2被跳过

验证导入数据

[oracle@web103 ~]$ sqlplus xifenfeinew/"www.xifenfei.com"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 22:41:32 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
T_XIFENFEI3                    TABLE

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86275

SQL> select count(*) from t_xifenfei3;

  COUNT(*)
----------
      2800

SQL>

通过验证数据证明，通过CPFL完美跳过了坏块所在表,实现后续数据完美恢复

bbed for win 64

Posted on by

很多朋友反馈在win 64位操作系统之上无法使用bbed(包括9i,10g,11g,12c数据库版本),以前写过一篇文章,完美实现了在win平台的各个版本的数据库版本之上实现使用bbed(在win中运行bbed程序),可惜很遗憾没有注明平台信息,留下了不少疑问,今天在自己的电脑上再次实现此功能,用来证明win 64位的平台之上也可以运行bbed程序(数据库版本包括10g,11g,12c,在10g之前x86架构中无win 64位版本数据库,因此我也无能为力).

操作系统版本64位
本机测试为win 7 64位操作系统
win-64

win-64-2

数据库版本64位
本机测试数据库版本为12.1.0.2 64位版本(因为12c都支持,那对于10g/11g更是不在话下）
db-64

bbed运行情况
这里的bbed只是运行起来,并未加载数据文件,因此这里看到的FILENAME为空,但是不妨碍证明bbed可以在win平台,64位的数据库中运行
bbed-win64

整体证明win 64位平台,64位数据库运行bbed
一图抵上千言万语,让我们使用一幅完整截图来说明,bbed是可以运行在win 64位平台的64位版本的数据库之上(而且这里使用了目前最新的12.1.0.2版本)
oracle-64-bbed-10g-11g-12c