月归档：八月 2015

[MySQL异常恢复]mysql drop table 数据恢复

发表于 2015 年 8 月 29 日 由 惜分飞

对于MySQL数据库的innodb引擎的数据库中,由于误操作删除表,或者由于sqldump自动生成语句含drop table create table语句导致数据丢失,在没有覆盖的情况下,可以实现完美恢复
创建测试表

mysql> CREATE TABLE recover.`t_drop` (
    ->   `messageId` varchar(30) NOT NULL,
    ->   `msgContent` varchar(1000) default NULL,
    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `deliverState` int(1) default NULL,
    ->   PRIMARY KEY  (`messageId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into recover.t_drop select messageId,msgContent,scheduleDate,deliverState from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from recover.`t_drop`;
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| messageId          | msgContent                                                                       | scheduleDate        | deliverState |
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| 10235259536125650  | 尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 10235353811295807  | 尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 102354211240398235 | 尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 102354554052884567 | 尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 132213454294519126 | 尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82329022242584577  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82329022242584581  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 8233400415607376   | 尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82334502212106951  | 尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82339012756833423  | 尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 8234322198577796   | 尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
11 rows in set (0.00 sec)

mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 | 
+-----------------+-----------+
1 row in set (0.00 sec)

删除测试表,后续用来恢复

mysql> drop table recover.t_drop;
Query OK, 0 rows affected (0.00 sec)

解析ibdata文件

[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Worker(1): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(0): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(4): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(3): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(2): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(7): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(5): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(6): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
All workers finished in 1 sec

恢复mysql字典

[root@web103 mysql_recovery]# ./recover_dictionary.sh 
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 162 recs OK
SYS_COLUMNS ... 1247 recs OK
SYS_INDEXES ... 216 recs OK
SYS_FIELDS ... 411 recs OK
All OK
[root@web103 mysql_recovery]# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10162
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, 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> select * from SYS_TABLES WHERE name like 'recover%';
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| NAME                    | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| recover/#sql-64a9_1f6d  | 120 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/#sql2-64a9-1f6d | 115 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete        |  74 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete1       |  84 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/t_drop          | 125 |      4 |    1 |      0 |       0 |              |     0 | 
| recover/t_truncate      | 120 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/t_xifenfei      |  75 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/zx_users        |  89 |     85 |    1 |      0 |       0 |              |     0 | 
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
8 rows in set (0.00 sec)

mysql> select * from SYS_INDEXES WHERE TABLE_ID=125;
+----------+-----+---------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+---------+----------+------+-------+------------+
|      125 | 142 | PRIMARY |        1 |    3 |     0 | 4294967295 | 
+----------+-----+---------+----------+------+-------+------------+
1 row in set (0.00 sec)

恢复被删除表记录

[root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000142.page  -t dictionary/t_drop.sql >dumps/default/t_drop 2>2.sql
[root@web103 mysql_recovery]# more dumps/default/t_drop
-- Page id: 9860, Format: COMPACT, Records list: Valid, Expected records: (11 11)
00000099F9F2    80000026800110  t_drop  "10235259536125650"     "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800129  t_drop  "10235353811295807"     "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800142  t_drop  "102354211240398235"    "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680015C  t_drop  "102354554052884567"    "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800176  t_drop  "132213454294519126"    "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800190  t_drop  "82329022242584577"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001A9  t_drop  "82329022242584581"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001C2  t_drop  "8233400415607376"      "尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001DA  t_drop  "82334502212106951"     "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001F3  t_drop  "82339012756833423"     "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680020C  t_drop  "8234322198577796"      "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
-- Page id: 9860, Found records: 11, Lost records: NO, Leaf page: YES

恢复数据入库

mysql> source dictionary/t_drop.sql
Query OK, 0 rows affected (0.00 sec)

mysql> source 2.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.00 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 | 
+-----------------+-----------+
1 row in set (0.00 sec)

至此实现删除数据完美恢复

发表在 MySQL恢复 | 标签为 , , , , , | 评论关闭

[MySQL异常恢复]无主键情况下innodb数据恢复

发表于 2015 年 8 月 29 日 由 惜分飞

在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复
创建模拟表—无主键

mysql>  CREATE TABLE `t1` (
    ->   `messageId` varchar(30) character set utf8 NOT NULL,
    ->   `tokenId` varchar(20) character set utf8 NOT NULL,
    ->   `mobile` varchar(14) character set utf8 default NULL,
    ->   `msgFormat` int(1) NOT NULL,
    ->   `msgContent` varchar(1000) character set utf8 default NULL,
    ->   `scheduleDate` timestamp NOT NULL default '0000-00-00 00:00:00',
    ->   `deliverState` int(1) default NULL,
    ->   `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00'
    -> ) ENGINE=INnodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 select * from sms_service.sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0
…………
mysql> insert into t1 select * from t1;
Query OK, 81664 rows affected (2.86 sec)
Records: 81664  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 163328 rows affected (2.74 sec)
Records: 163328  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   326656 | 
+----------+
1 row in set (0.15 sec)

解析innodb文件

[root@web103 mysql_recovery]# rm -rf pages-ibdata1/
[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
Opening file: /var/lib/mysql/ibdata1
File information:

time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
(regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819465 Sat Aug 29 11:37:45 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
All workers finished in 0 sec

恢复数据字典

[root@web103 mysql_recovery]# ./recover_dictionary.sh 
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 48 recs OK
SYS_COLUMNS ... 397 recs OK
SYS_INDEXES ... 67 recs OK
SYS_FIELDS ... 89 recs OK
All OK

分析数据字典,找出来index_id
这里需要注意对于没有主键的表恢复,我们对应的类型是GEN_CLUST_INDEX

mysql> select * from SYS_TABLES where name='test/t1';
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| NAME                                   | ID  | N_COLS      | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| test/t1                                | 100 |           8 |    1 |      0 |       0 |              |     0 | 
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
40 rows in set (0.00 sec)

mysql> SELECT * FROM SYS_INDEXES where table_id=100;
+----------+-----+------------------------------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME                         | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+------------------------------+----------+------+-------+------------+
|      100 | 119 | GEN_CLUST_INDEX              |        0 |    1 |     0 |       2951 | 
+----------+-----+------------------------------+----------+------+-------+------------+
67 rows in set (0.00 sec)

恢复数据

root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000119.page  -t dictionary/t1.sql >/tmp/2.txt 2>2.sql
[root@web103 mysql_recovery]# more /tmp/2.txt
-- Page id: 10848, Format: COMPACT, Records list: Valid, Expected records: (73 73)
00000002141B    0000009924F2    80000027133548  t1      "82334502212106951"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141C    0000009924F2    80000027133558  t1      "82339012756833423"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141D    0000009924F2    80000027133568  t1      "8234322198577796"      "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141E    0000009924F2    80000027133578  t1      "10235259536125650"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141F    0000009924F2    80000027133588  t1      "10235353811295807"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021420    0000009924F2    80000027133598  t1      "102354211240398235"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021421    0000009924F2    800000271335A8  t1      "102354554052884567"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021422    0000009924F2    800000271335B8  t1      "132213454294519126"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021423    0000009924F2    800000271335C8  t1      "82329022242584577"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "2015-08-26 22:02:17"
…………
[root@web103 mysql_recovery]# cat /tmp/2.txt|grep  -v "Page id:"|wc -l
380731

因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据

发表在 MySQL恢复 | 标签为 , , | 评论关闭

ORACLE异常恢复后awr异常处理

发表于 2015 年 8 月 29 日 由 惜分飞

有一个通过非常规方法恢复过的客户数据库出现问题awr无法收集统计信息(几个月前非常规方法恢复的库,因为未重建库),不太方便跟踪数据库性能,让其帮忙分析跟踪问题.
人工收集统计信息报错RA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)

SQL> execute dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

*
第 1 行出现错误:
ORA-13509: 更新 AWR 表时出错
ORA-00001: 违反唯一约束条件 (ORA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)
.)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1

通过分析trace文件问题如下

Trace file D:\APP\ADMINISTRATOR\diag\rdbms\rac\rac2\trace\rac2_m000_1628.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
Windows NT Version V6.1 Service Pack 1 
CPU                 : 32 - type 8664, 32 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:83326M/131035M, Ph+PgF:214386M/262068M 
Instance name: rac2
Redo thread mounted by this instance: 2
Oracle process number: 61
Windows thread id: 1628, image: ORACLE.EXE (M000)


*** 2015-08-28 11:39:51.967
*** SESSION ID:(2062.93) 2015-08-28 11:39:51.968
*** CLIENT ID:() 2015-08-28 11:39:51.968
*** SERVICE NAME:(SYS$BACKGROUND) 2015-08-28 11:39:51.968
*** MODULE NAME:(MMON_SLAVE) 2015-08-28 11:39:51.968
*** ACTION NAME:(Auto-Flush Slave Action) 2015-08-28 11:39:51.968
 
*** KEWROCISTMTEXEC - encountered error: (ORA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)
)  
  *** SQLSTR: total-len=342, dump-len=240, 
      STR={insert into   WRM$_SNAPSHOT  (snap_id, dbid, instance_number, startup_time,begin_interval_time, 
end_interval_time, snap_level,    status, error_count, bl_moved, snap_flag, snap_timezone)  
values   (:snap_id, :dbid, :instance_number, :sta}
*** KEWRAFS: Error=13509 encountered by Auto Flush Slave.

这里可以明确的定位到,由于insert WRM$_SNAPSHOT表之时出现主键冲突导致无法收集统计信息.因为awr的数据都是历史数据,可以全部清理,因此尝试删除掉awr相关数据看是否能够解决问题

对收集快照做10046 跟踪发现

SQL> oradebug setmypid
已处理的语句
SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

SQL> oradebug tracefile_name
D:\APP\ADMINISTRATOR\diag\rdbms\rac\rac2\trace\rac2_ora_5944.trc
SQL> execute dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

*
第 1 行出现错误:
ORA-13509: 更新 AWR 表时出错
ORA-00001: 违反唯一约束条件 (ORA-00001: 违反唯一约束条件 (SYS.WRM$_SNAPSHOT_PK)
.)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1


--trace文件分析
PARSING IN CURSOR #1362260992  lid=0 tim=22781405124 hv=438921370 ad='148fd90590' sqlid='15rbgh4d2ku4u'
insert into WRM$_SNAPSHOT(snap_id, dbid, instance_number, startup_time,begin_interval_time, end_interval_time,
snap_level,status, error_count, bl_moved,snap_flag, snap_timezone)values(:snap_id, :dbid, :instance_number, 
:startup_time, :begin_interval_time, :end_interval_time, :snap_level,    :status, 0, 0, :bind1, :bind2)
END OF STMT
PARSE #1362260992:c=0,e=474,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=22781405122
BINDS #1362260992:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=208 off=0
  kxsbbbfp=513a6bf8  bln=22  avl=03  flg=05
  value=9277
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=513a6c10  bln=22  avl=06  flg=01
  value=2429481020
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=513a6c28  bln=22  avl=02  flg=01
  value=2
 Bind#3
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
  oacflg=00 fl2=8000000 frm=00 csi=00 siz=0 off=72
  kxsbbbfp=513a6c40  bln=11  avl=07  flg=01
  value=28-8月 -15 10.06.53 上午
 Bind#4
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
  oacflg=00 fl2=8000000 frm=00 csi=00 siz=0 off=88
  kxsbbbfp=513a6c50  bln=11  avl=07  flg=01
  value=28-8月 -15 10.06.53 上午
 Bind#5
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00
  oacflg=00 fl2=8000000 frm=00 csi=00 siz=0 off=104
  kxsbbbfp=513a6c60  bln=11  avl=11  flg=01
  value=28-8月 -15 04.11.40.017000000 下午
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=120
  kxsbbbfp=513a6c70  bln=22  avl=02  flg=01
  value=1
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=144
  kxsbbbfp=513a6c88  bln=22  avl=02  flg=01
  value=1
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=168
  kxsbbbfp=513a6ca0  bln=22  avl=02  flg=01
  value=1
 Bind#9
  oacdty=183 mxl=11(11) mxlc=00 mal=00 scl=09 pre=09
  oacflg=01 fl2=8000000 frm=00 csi=00 siz=0 off=192
  kxsbbbfp=513a6cb8  bln=11  avl=11  flg=01
  value=Unhandled datatype (183) found in kxsbndinf

这里可以明确定位到,awr在收集信息的时候就是插入的值和库中本身存在的记录冲突,从而出现此类问题

清理awr数据

SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9081

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(9081,9277);

PL/SQL 过程已成功完成。

SQL>
SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9277

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(9080,9278);

PL/SQL 过程已成功完成。

SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9277

SQL> delete from  WRM$_SNAPSHOT where snap_id=9277;
delete from  WRM$_SNAPSHOT where snap_id=9277
             *
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [13011], [6653], [8456911], [2], [8456911], [3],
[], [], [], [], [], []


SQL> delete /*+ RULE */ from  WRM$_SNAPSHOT where snap_id=9277;

已删除0行。

这里有几分诡异,snap_id=9277的记录无法清理,而且正常删除报ORA-00600[13011].根据经验,出现该问题,很可能是由于表和index的记录问题

尝试rebuild index

SQL> analyze table WRM$_SNAPSHOT validate structure cascade;
analyze table WRM$_SNAPSHOT validate structure cascade
*
第 1 行出现错误:
ORA-01499: 表/索引交叉引用失败 - 请参阅跟踪文件


SQL> select index_name from dba_indexes where table_name='WRM$_SNAPSHOT';

INDEX_NAME
------------------------------
WRM$_SNAPSHOT_PK

SQL> alter index WRM$_SNAPSHOT_PK rebuild;

索引已更改。

SQL> select /*+ full(t) */ max(snap_id),min(snap_id) from WRM$_SNAPSHOT t;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------


SQL> select max(snap_id),min(snap_id) from WRM$_SNAPSHOT;

MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
        9277         9277

这里很明确的定位了,由于表和index的记录不一致,而且通过rebuild,发现index依旧有问题

重建index

SQL> set linesize 180
SQL> set pages 999
SQL> set long 90000
SQL> select dbms_metadata.get_ddl('INDEX','WRM$_SNAPSHOT_PK','SYS') from dual;

DBMS_METADATA.GET_DDL('INDEX','WRM$_SNAPSHOT_PK','SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."WRM$_SNAPSHOT_PK" ON "SYS"."WRM$_SNAPSHOT" ("DBID", "SNAP_ID", "INSTANCE_NUMBER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"


SQL> DROP INDEX "SYS"."WRM$_SNAPSHOT_PK" ;
DROP INDEX "SYS"."WRM$_SNAPSHOT_PK"
                 *
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引


SQL> alter table "SYS"."WRM$_SNAPSHOT" drop  constraint "SYS"."WRM$_SNAPSHOT_PK";
alter table "SYS"."WRM$_SNAPSHOT" drop  constraint "SYS"."WRM$_SNAPSHOT_PK"
                                                        *
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项


SQL> alter table "WRM$_SNAPSHOT" drop  constraint "WRM$_SNAPSHOT_PK";

表已更改。

SQL>alter table "WRM$_SNAPSHOT" add constraint "WRM$_SNAPSHOT_PK" primary key("DBID", "SNAP_ID", "INSTANCE_NUMBER");

表已更改。

再次尝试做快照

SQL> execute dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kewrose_1], [600], [ORA-00600: 内部错误代码, 参数: [6002], [6], [104], 
[4], [0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: 在 line 1

悲剧再次发生,收集快照之时遭遇悲催的ORA-00600[kewrose_1]/ORA-600[6002]的错误.范围awr的以前数据都不要了,也就采用最极端的处理方法,定位到表,然后处理之

继续10046跟踪

PARSING IN CURSOR #1495840456 tim=24328721585 hv=4050667988 ad='146f9948f8'sqlid='84qubbrsr0kfn'
insert into wrh$_latch(snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps,
 immediate_gets,immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time)  
select :snap_id, :dbid, :instance_number, hash, level#, gets,    misses, sleeps, 
immediate_gets, immediate_misses, spin_gets,    sleep1, sleep2, sleep3, 
sleep4, wait_time  from    v$latch  order by    hash
END OF STMT
PARSE #1495840456:c=0,e=376,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=24328721584
BINDS #1495840456:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=60471350  bln=22  avl=03  flg=05
  value=9280
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=60471368  bln=22  avl=06  flg=01
  value=2429481020
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=60471380  bln=22  avl=02  flg=01
  value=2
ORA-00600: 内部错误代码, 参数: [6002], [6], [104], [4], [0], [], [], [], [], [], [], []

通过这里可以定位到问题是发生在wrh$_latch表的insert操作之上

分析并truncate table

SQL> SELECT COUNT(*) FROM wrh$_latch;

COUNT(*)
--------
     0

SQL> truncate table wrh$_latch;

表被截断

再次收集快照信息

SQL> execute dbms_workload_repository.create_snapshot();

PL/SQL 过程已成功完成。

SQL> @?/rdbms/admin/awrrpt.sql
--工作正常

经过一些列处理,终于让awr能够正常工作了,特别是在做过异常恢复之后,awr数据可能有各种问题导致工作不正常,可以考虑重建awr,也可以考虑类似我这样彻底清理awr数据,然后放手处理.当然对于使用非常规方法恢复的Oracle数据库,在条件允许的情况下,建议逻辑方式重建库.因为有数据字典不一致,有逻辑坏块,有表和index不一致等问题,在后续的使用中逐渐被显露出来,从而导致很多麻烦,重建库彻彻底底解决问题.

发表在 Oracle | 标签为 , , , , | 评论关闭