Skip to content

在前阵子有一个客户的Mysql数据库被恶意删除,通过一晚努力恢复回来了。这里介绍下mysql的特殊恢复工具 undrop for innodb。

 

官网:

http://twindb.com/undrop-tool-for-innodb/

 

首先这是一个开源的工具,在github上有更新,在官网上也有对各个场景的使用介绍,这里不做多的介绍,我这里列举一个问题就是sys_parse工具的编译,官网提供的方法以及read me里的方式在我的环境里是编译不过去的,客户的数据库版本是5.1的,所在的系统平台是Cent os,但是需要引起注意的undrop for innodb有建议使用的操作系统版本,我使用的是Centos 6.5,Mysql 5.7版。下面是这次的恢复过程,请注意我改变的编译语法。

 

CentOs 6.5 Mysql 5.7.23

./stream_parser -f /mysqldata/ibdata1
[root@localhost undrop-for-innodb-develop]# ./stream_parser -f /mysqldata/ibdata1
Opening file: /mysqldata/ibdata1
File information:
ID of device containing file:        64512
inode number:                       652164
protection:                         100755 (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:          36912
time of last access:            1559729794 Wed Jun  5 18:16:34 2019
time of last modification:      1559281768 Fri May 31 13:49:28 2019
time of last status change:     1559729794 Wed Jun  5 18:16:34 2019
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec
[root@localhost FIL_PAGE_INDEX]# ls -al
total 1812
drwxr-xr-x 2 root root   4096 Jun  5 18:23 .
drwxr-xr-x 4 root root   4096 Jun  5 18:23 ..
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000001.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000002.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000003.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000004.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000005.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000006.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000011.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000012.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000013.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000014.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000043.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000044.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000045.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000046.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000047.page
-rw-r–r– 1 root root 262144 Jun  5 18:23 0000000000000048.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000049.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000050.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000051.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000052.page
-rw-r–r– 1 root root 147456 Jun  5 18:23 0000000000000053.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000054.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000055.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000056.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000057.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000058.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000059.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000060.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000061.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000062.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000063.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000064.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000065.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000066.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000067.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000068.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000069.page
-rw-r–r– 1 root root  81920 Jun  5 18:23 0000000000000070.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000071.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000072.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 18446744069414584320.page
mkdir -p dumps/default
恢复sys_tables
[root@localhost undrop-for-innodb-develop]# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql -o ./dumps/default/SYS_TABLES  -l ./dumps/default/SYS_TABLES.sql
[root@localhost undrop-for-innodb-develop]# cd dumps
[root@localhost dumps]# ls default/
SYS_TABLES  SYS_TABLES.sql
[root@localhost dumps]# ls
default
[root@localhost dumps]# cd default/
[root@localhost default]# ls
SYS_TABLES  SYS_TABLES.sql
[root@localhost default]# cat SYS_TABLES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 2)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007F01    02000001860817  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING” 48      4       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 26, Lost records: YES, Leaf page: YES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 2)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007F01    02000001860817  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING” 48      4       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 26, Lost records: YES, Leaf page: YES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 3)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 25, Lost records: YES, Leaf page: YES
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql -o ./dumps/SYS_TABLES  -l ./dumps/SYS_TABLES.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql -o ./dumps/SYS_INDEXES  -l ./dumps/SYS_INDEXES.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql -o ./dumps/SYS_COLUMNS  -l ./dumps/SYS_COLUMNS.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql -o ./dumps/SYS_FIELDS  -l ./dumps/SYS_FIELDS.sql
create database dictionary;
use dictionary;
mysql> source  /undropmysql/dictionary/SYS_TABLES.sql
mysql> source  /undropmysql/dictionary/SYS_INDEXES.sql
mysql> source  /undropmysql/dictionary/SYS_FIELDS.sql
mysql> source  /undropmysql/dictionary/SYS_COLUMNS.sql
mysql> source  /undropmysql/dictionary/SYS_TABLES.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> .
    ->
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.’ at line 1
mysql> source  /undropmysql/dictionary/SYS_INDEXES.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> /undropmysql/dictionary/SYS_FIELDS.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/undropmysql/dictionary/SYS_FIELDS.sql’ at line 1
mysql> source  /undropmysql/dictionary/SYS_FIELDS.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> source  /undropmysql/dictionary/SYS_COLUMNS.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#执行前面生成的LOAD DATA语句导入恢复的记录
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_TABLES.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_COLUMNS.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_INDEXES.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_FIELDS.sql
 要用mysql_config的绝对路径
 make sys_parser /usr/bin/mysql_config cc -o sys_parser sys_parser.c ‘mysql_config –cflags’ ‘mysql_config –libs’  ===>测试不行
 make sys_parser /usr/bin/mysql_config cc ‘mysql_config –cflags’ ‘mysql_config –libs’ -o sys_parser sys_parser.c  ===>测试不行
  gcc sys_parser.c -o sys_parser $(/usr/bin/mysql_config –libs –cflags) ==》重新修改代码后按照c语言编译的方法改写通过
 mysql> desc SYS_TABLES
    -> ;
+————–+———————+——+—–+———+——-+
| Field        | Type                | Null | Key | Default | Extra |
+————–+———————+——+—–+———+——-+
| NAME         | varchar(255)        | NO   | PRI |         |       |
| ID           | bigint(20) unsigned | NO   |     | 0       |       |
| N_COLS       | int(10)             | YES  |     | NULL    |       |
| TYPE         | int(10) unsigned    | YES  |     | NULL    |       |
| MIX_ID       | bigint(20) unsigned | YES  |     | NULL    |       |
| MIX_LEN      | int(10) unsigned    | YES  |     | NULL    |       |
| CLUSTER_NAME | varchar(255)        | YES  |     | NULL    |       |
| SPACE        | int(10) unsigned    | YES  |     | NULL    |       |
+————–+———————+——+—–+———+——-+
8 rows in set (0.00 sec)
mysql> select * from SYS_TABLES;
+———————————-+—-+——–+——+——–+———+————–+——-+
| NAME                             | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+———————————-+—-+——–+——+——–+———+————–+——-+
| car_doc/t_advert                 | 30 |     13 |    1 |      0 |       0 |              |     0 |
| car_doc/t_auth_config            | 31 |      8 |    1 |      0 |       0 |              |     0 |(空表)
| car_doc/t_book_type              | 32 |     17 |    1 |      0 |       0 |              |     0 |
| car_doc/t_car_type               | 33 |     18 |    1 |      0 |       0 |              |     0 |
| car_doc/t_count                  | 34 |      8 |    1 |      0 |       0 |              |     0 |
| car_doc/t_dict                   | 18 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_feedback               | 36 |      4 |    1 |      0 |       0 |              |     0 |
| car_doc/t_log                    | 37 |      5 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager                | 38 |      9 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager_authbak        | 39 |      7 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager_role           | 40 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_meals                  | 41 |      5 |    1 |      0 |       0 |              |     0 |
| car_doc/t_news                   | 42 |     12 |    1 |      0 |       0 |              |     0 |
| car_doc/t_order                  | 26 |     16 |    1 |      0 |       0 |              |     0 |
| car_doc/t_role                   | 44 |      6 |    1 |      0 |       0 |              |     0 |
| car_doc/t_role_auth              | 45 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_user                   | 46 |      9 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING       | 48 |      4 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING@00231 | 48 |      4 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING@00232 | 47 |      4 |    1 |      0 |       0 |              |     0 |
+———————————-+—-+——–+——+——–+———+————–+——-+
20 rows in set (0.00 sec)
 gcc sys_parser.c -o sys_parser $(/usr/bin/mysql_config –libs –cflags)
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_advert
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_auth_config
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_book_type
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_car_type
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_count
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_dict
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_feedback
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_log
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager_authbak
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager_role
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_meals
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_news
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_order
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_role
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_role_auth
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_user
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING@00231
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING@00232
mysql>  create database car_doc;
Query OK, 1 row affected (0.00 sec)
mysql> use car_doc
Database changed
mysql> source /undropmysql/car_doc/t_advert.sql
Query OK, 0 rows affected (0.04 sec)
mysql> source /undropmysql/dumps/default/t_advert.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.07 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from car_doc;
ERROR 1146 (42S02): Table ‘car_doc.car_doc’ doesn’t exist
mysql> select * from t_advert;
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
| id  | url            | title            | createor            | status | createTime | updateTime | sort        | updateor | picture | releaseTime | approvalHistory | offlineTime |
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
|  24 | 12016051209173 | 3094e08e0738c422 | 51007424128@qq.co   | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
|  25 | 12016051210135 | 2dd39aef34bf64a7 | 8568144450@qq.co    | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
|  26 | 12016061312503 | 19864b15399af40d | 51007424128@qq.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 119 | 12017100914075 | 290c508ebb5434bd | 8dfylxxgk@163.co    | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 120 | 12017100914084 | 1f9fad68aa8404ae | 31007424128@qq.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 121 | 12017100914093 | 6bbabcaa806f0400 | 1zhufeicai@163.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 122 | 12018051611300 | 852199cab75a745f | e2042213325@qq.co   | m      | 0          |            | -1317011456 |          | NULL    |             | NULL            |             |
| 123 | 12018052315465 | 3b5ff7ba13b784f9 | e2042213325@qq.co   | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
| 124 | 12018081413501 | 254bdc81e991a495 | bberling@livemail.t | w      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
| 125 | 12018082911062 | 85f7a36723107478 | 384558040@qq.co     | m      | 0          |            | -1317011456 |          | NULL    |             | NULL            |             |
| 126 | 12018111208521 | 1aeafacfb1013418 | azhufeicai@163.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
11 rows in set (0.00 sec)
mysql>
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000016.page -t car_doc/t_auth_config.sql  > dumps/default/t_auth_config 2> dumps/default/t_auth_config.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000047.page -t car_doc/t_auth_config.sql  > dumps/default/t_auth_config 2> dumps/default/t_auth_config.sql
source /undropmysql/car_doc/t_auth_config.sql
source /undropmysql/dumps/default/t_auth_config.sql
mysql> use car_doc
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> source /undropmysql/car_doc/t_auth_config.sql
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_auth_config
    -> ;
+————-+————-+——+—–+———+——-+
| Field       | Type        | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| id          | bigint(20)  | NO   | PRI | NULL    |       |
| name        | varchar(40) | NO   |     | NULL    |       |
| url         | varchar(60) | NO   |     | NULL    |       |
| status      | int(11)     | NO   |     | NULL    |       |
| pid         | bigint(20)  | NO   |     | NULL    |       |
| createtime  | datetime    | NO   |     | NULL    |       | 类型改为varchar
| operator    | varchar(45) | YES  |     | NULL    |       |
| operatetime | datetime    | YES  |     | NULL    |       |
+————-+————-+——+—–+———+——-+ 类型改为varchar
8 rows in set (0.01 sec)
mysql> source /undropmysql/car_doc/t_auth_config.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_auth_config.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 67 rows affected (0.01 sec)
Records: 34  Deleted: 33  Skipped: 0  Warnings: 0
======重要 表数据为如下 ==== 因为时间导出有问题,导入mysql有异常故截取记录 以供查询(有33条记录标记为被正常删除过的) =========
— Page id: 415, Format: COMPACT, Records list: Valid, Expected records: (18 18)
000000180000    20001180000001  t_auth_config   6442450944      “”      “”      0       -9223371929413474304    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:26”
000000190400    28001180000001  t_auth_config   6442450944      “”      “”      0       -9223371925185613824    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:27”
0000001A0000    30001180000001  t_auth_config   6442450944      “”      “”      0       -9223371920890644480    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:28”
0000001B0000    38001180000001  t_auth_config   6442450944      “”      “”      0       -9223371916595675136    “0789-56-88 90:47:04”   “”      NULL
0000001C0000    40001180000001  t_auth_config   6442450944      “”      “”      0       -9223371912233596928    “0789-56-88 90:47:04”   NULL    NULL
0000001D0400    48001180000001  t_auth_config   6442450944      “”      “”      0       -9223371908005736448    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:31”
0000001E0000    50001180000001  t_auth_config   6442450944      “”      “”      0       -9223371903710767104    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:35”
0000001F0000    58001180000001  t_auth_config   6442450944      “”      “”      0       -9223371886530895872    “0789-56-88 90:47:04”   “”      NULL
000000230000    60001180000001  t_auth_config   6442450944      “”      “”      0       -9223371882168817664    “0789-56-88 90:47:04”   NULL    NULL
000000240400    68001180000001  t_auth_config   6442450944      “”      “”      0       -9223371877940957184    “0789-56-88 90:47:04”   NULL    NULL
000000250000    70001180000001  t_auth_config   6442450944      “”      “”      0       -9223371873645987840    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:44”
000000260000    78001180000001  t_auth_config   6442450944      “”      “”      0       -9223371847876182016    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:47”
0000002C0000    80001180000001  t_auth_config   6442450944      “”      “”      0       -9223371834991278080    “0789-56-88 90:47:04”   “”      NULL
0000002F0000    88001180000001  t_auth_config   6442450944      “”      “”      0       -9223371830696308736    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:49”
000000300000    90001180000001  t_auth_config   6442450944      “”      “”      0       -9223371826401339138    “5150-81-54 22:05:44”   NULL    NULL
000000310000    98FED280000001  t_auth_config   6442450944      “”      “”      0       -9223371822106411008    “0000-00-00 00:00:00”   “”      “0000-00-00 00:00:00”
000000320000    00000000000000  t_auth_config   6442450944      “”      “”      -2147483648     -9223372036854775808    “0000-00-00 00:00:00”   NULL    “0000-00-00 00:00:00”
— Page id: 415, Found records: 17, Lost records: YES, Leaf page: YES
— Page id: 415, Format: COMPACT, Records list: Valid, Expected records: (18 18)
000000180000    20001180000001  t_auth_config   6442450944      “”      “”      0       -9223371929413474304    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:26”
000000190400    28001180000001  t_auth_config   6442450944      “”      “”      0       -9223371925185613824    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:27”
0000001A0000    30001180000001  t_auth_config   6442450944      “”      “”      0       -9223371920890644480    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:28”
0000001B0000    38001180000001  t_auth_config   6442450944      “”      “”      0       -9223371916595675136    “0789-56-88 90:47:04”   “”      NULL
0000001C0000    40001180000001  t_auth_config   6442450944      “”      “”      0       -9223371912233596928    “0789-56-88 90:47:04”   NULL    NULL
0000001D0400    48001180000001  t_auth_config   6442450944      “”      “”      0       -9223371908005736448    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:31”
0000001E0000    50001180000001  t_auth_config   6442450944      “”      “”      0       -9223371903710767104    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:35”
0000001F0000    58001180000001  t_auth_config   6442450944      “”      “”      0       -9223371886530895872    “0789-56-88 90:47:04”   “”      NULL
000000230000    60001180000001  t_auth_config   6442450944      “”      “”      0       -9223371882168817664    “0789-56-88 90:47:04”   NULL    NULL
000000240400    68001180000001  t_auth_config   6442450944      “”      “”      0       -9223371877940957184    “0789-56-88 90:47:04”   NULL    NULL
000000250000    70001180000001  t_auth_config   6442450944      “”      “”      0       -9223371873645987840    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:44”
000000260000    78001180000001  t_auth_config   6442450944      “”      “”      0       -9223371847876182016    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:47”
0000002C0000    80001180000001  t_auth_config   6442450944      “”      “”      0       -9223371834991278080    “0789-56-88 90:47:04”   “”      NULL
0000002F0000    88001180000001  t_auth_config   6442450944      “”      “”      0       -9223371830696308736    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:49”
000000300000    90001180000001  t_auth_config   6442450944      “”      “”      0       -9223371826401339138    “5150-81-54 22:05:44”   NULL    NULL
000000310000    98FED280000001  t_auth_config   6442450944      “”      “”      0       -9223371822106411008    “0000-00-00 00:00:00”   “”      “0000-00-00 00:00:00”
000000320000    00000000000000  t_auth_config   6442450944      “”      “”      -2147483648     -9223372036854775808    “0000-00-00 00:00:00”   NULL    “0000-00-00 00:00:00”
— Page id: 415, Found records: 17, Lost records: YES, Leaf page: YES
=====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000048.page -t car_doc/t_book_type.sql  > dumps/default/t_book_type 2> dumps/default/t_book_type.sql
mysql> source /undropmysql/dumps/default/t_book_type.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 554 rows affected (0.12 sec)
Records: 554  Deleted: 0  Skipped: 0  Warnings: 0
修改了 t_book_type表的字段approvalHistory的属性text为varchar(20000)
====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000049.page -t car_doc/t_car_type.sql  > dumps/default/t_car_type 2> dumps/default/t_car_type.sql
source /undropmysql/car_doc/t_car_type.sql
source /undropmysql/dumps/default/t_car_type.sql
mysql>
mysql>
mysql> source /undropmysql/car_doc/t_car_type.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_car_type.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 38 rows affected (0.00 sec)
Records: 38  Deleted: 0  Skipped: 0  Warnings: 0
====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000050.page -t car_doc/t_count.sql  > dumps/default/t_count 2> dumps/default/t_count.sql
source /undropmysql/car_doc/t_count.sql
source /undropmysql/dumps/default/t_count.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000050.page -t car_doc/t_count.sql  > dumps/default/t_count 2> dumps/default/t_count.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_count.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_count.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.01 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000023.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000051.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
source /undropmysql/car_doc/t_dict.sql
source /undropmysql/dumps/default/t_dict.sql
空表
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000023.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_dict.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_dict.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘stat: No such file or directory
Can’t stat /undropmysql/pages-ibdata1/FIL_PAGE_I’ at line 1
mysql> source /undropmysql/dumps/default/t_dict.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 14 rows affected (0.00 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000052.page -t car_doc/t_feedback.sql  > dumps/default/t_feedback 2> dumps/default/t_feedback.sql
source /undropmysql/car_doc/t_feedback.sql
source /undropmysql/dumps/default/t_feedback.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000052.page -t car_doc/t_feedback.sql  > dumps/default/t_feedback 2> dumps/default/t_feedback.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_feedback.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_feedback.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 20 rows affected (0.01 sec)
Records: 20  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000025.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000053.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
source /undropmysql/car_doc/t_log.sql
source /undropmysql/dumps/default/t_log.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000053.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_log.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_log.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1138 rows affected (0.05 sec)
Records: 1138  Deleted: 0  Skipped: 0  Warnings: 0
========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000055.page -t car_doc/t_manager.sql  > dumps/default/t_manager 2> dumps/default/t_manager.sql
source /undropmysql/car_doc/t_manager.sql
source /undropmysql/dumps/default/t_manager.sql
空表无数据(或者数据丢失)
[root@localhost default]# cat t_manager
— Page id: 328, Format: COMPACT, Records list: Valid, Expected records: (1 1)
— Page id: 328, Found records: 0, Lost records: YES, Leaf page: YES
— Page id: 328, Format: COMPACT, Records list: Valid, Expected records: (1 1)
— Page id: 328, Found records: 0, Lost records: YES, Leaf page: YES
[root@localhost default]# cat t_manager.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE ‘/undropmysql/dumps/default/t_manager’ REPLACE INTO TABLE `t_manager` CHARACTER SET UTF8 FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘t_manager\t’ (`id`, `account`, `password`, `isdisable`);
— STATUS {“records_expected”: 2, “records_dumped”: 0, “records_lost”: true} STATUS END
mysql> use car_doc
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> source /undropmysql/car_doc/t_manager.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_manager.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000058.page -t car_doc/t_manager_authbak.sql  > dumps/default/t_manager_authbak 2> dumps/default/t_manager_authbak.sql
source /undropmysql/car_doc/t_manager_authbak.sql
source /undropmysql/dumps/default/t_manager_authbak.sql
倒数第二个字段有乱码,无法识别因此保留数据给予手工录入
— Page id: 342, Format: COMPACT, Records list: Valid, Expected records: (18 18)
00000000066D    DB0000015D0110  t_manager_authbak       1       61      28      1       2016-04-09 01:06:10     huanad  2016-04-09 01:06:10
00000000066D    DB0000015D0120  t_manager_authbak       2       61      24      1       2016-04-09 13:37:08     huanad  2016-04-09 13:37:08
00000000066D    DB0000015D0130  t_manager_authbak       3       61      25      1       2016-04-09 13:37:25     huanad  2016-04-09 13:37:25
00000000066D    DB0000015D0140  t_manager_authbak       4       61      26      1       2016-04-09 13:37:43     huanad  2016-04-09 13:37:43
00000000066D    DB0000015D0150  t_manager_authbak       97      61      29      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0160  t_manager_authbak       99      61      30      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0170  t_manager_authbak       101     61      31      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0180  t_manager_authbak       103     61      35      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0190  t_manager_authbak       105     61      36      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01A0  t_manager_authbak       107     61      37      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01B0  t_manager_authbak       109     61      38      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01C0  t_manager_authbak       111     61      43      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01D0  t_manager_authbak       113     61      44      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01E0  t_manager_authbak       115     61      47      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01F0  t_manager_authbak       117     61      48      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0200  t_manager_authbak       119     61      49      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0210  t_manager_authbak       121     61      50      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0220  t_manager_authbak       123     61      59      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
— Page id: 342, Found records: 18, Lost records: NO, Leaf page: YES
— Page id: 342, Format: COMPACT, Records list: Valid, Expected records: (18 18)
00000000066D    DB0000015D0110  t_manager_authbak       1       61      28      1       2016-04-09 01:06:10     huanad  2016-04-09 01:06:10
00000000066D    DB0000015D0120  t_manager_authbak       2       61      24      1       2016-04-09 13:37:08     huanad  2016-04-09 13:37:08
00000000066D    DB0000015D0130  t_manager_authbak       3       61      25      1       2016-04-09 13:37:25     huanad  2016-04-09 13:37:25
00000000066D    DB0000015D0140  t_manager_authbak       4       61      26      1       2016-04-09 13:37:43     huanad  2016-04-09 13:37:43
00000000066D    DB0000015D0150  t_manager_authbak       97      61      29      1       2016-01-12 16:59:25     Shuanad  2016-01-12 16:59:25
00000000066D    DB0000015D0160  t_manager_authbak       99      61      30      1       2016-01-12 16:59:25     Shuanad  2016-01-12 16:59:25
00000000066D    DB0000015D0170  t_manager_authbak       101     61      31      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0180  t_manager_authbak       103     61      35      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0190  t_manager_authbak       105     61      36      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01A0  t_manager_authbak       107     61      37      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01B0  t_manager_authbak       109     61      38      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01C0  t_manager_authbak       111     61      43      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01D0  t_manager_authbak       113     61      44      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01E0  t_manager_authbak       115     61      47      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01F0  t_manager_authbak       117     61      48      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0200  t_manager_authbak       119     61      49      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0210  t_manager_authbak       121     61      50      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0220  t_manager_authbak       123     61      59      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
— Page id: 342, Found records: 18, Lost records: NO, Leaf page: YES
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000062.page -t car_doc/t_manager_role.sql  > dumps/default/t_manager_role 2> dumps/default/t_manager_role.sql
source /undropmysql/car_doc/t_manager_role.sql
source /undropmysql/dumps/default/t_manager_role.sql
Database changed
mysql> source /undropmysql/car_doc/t_manager_role.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_manager_role.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000063.page -t car_doc/t_meals.sql  > dumps/default/t_meals 2> dumps/default/t_meals.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000035.page -t car_doc/t_meals.sql  > dumps/default/t_meals 2> dumps/default/t_meals.sql
source /undropmysql/car_doc/t_meals.sql
source /undropmysql/dumps/default/t_meals.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/dumps/default/t_meals.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000064.page -t car_doc/t_news.sql  > dumps/default/t_news 2> dumps/default/t_news.sql
source /undropmysql/car_doc/t_news.sql
source /undropmysql/dumps/default/t_news.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000064.page -t car_doc/t_news.sql  > dumps/default/t_news 2> dumps/default/t_news.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_news.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_news.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t car_doc/t_order.sql  > dumps/default/t_order 2> dumps/default/t_order.sql
source /undropmysql/car_doc/t_order.sql
source /undropmysql/dumps/default/t_order.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t car_doc/t_order.sql  > dumps/default/t_order 2> dumps/default/t_order.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_order.sql
Query OK, 0 rows affected (0.03 sec)
mysql> source /undropmysql/dumps/default/t_order.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 318 rows affected (0.01 sec)
Records: 318  Deleted: 0  Skipped: 0  Warnings: 0
===========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000066.page -t car_doc/t_role.sql  > dumps/default/t_role 2> dumps/default/t_role.sql
source /undropmysql/car_doc/t_role.sql
source /undropmysql/dumps/default/t_role.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000066.page -t car_doc/t_role.sql  > dumps/default/t_role 2> dumps/default/t_role.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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>
mysql> source /undropmysql/car_doc/t_role.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_role.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 14 rows affected (0.01 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
=======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000067.page -t car_doc/t_role_auth.sql  > dumps/default/t_role_auth 2> dumps/default/t_role_auth.sql
source /undropmysql/car_doc/t_role_auth.sql
source /undropmysql/dumps/default/t_role_auth.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000067.page -t car_doc/t_role_auth.sql  > dumps/default/t_role_auth 2> dumps/default/t_role_auth.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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>
mysql> source /undropmysql/car_doc/t_role_auth.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_role_auth.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 103 rows affected (0.01 sec)
Records: 103  Deleted: 0  Skipped: 0  Warnings: 0
=======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000070.page -t car_doc/t_user.sql  > dumps/default/t_user 2> dumps/default/t_user.sql
source /undropmysql/car_doc/t_user.sql
source /undropmysql/dumps/default/t_user.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000070.page -t car_doc/t_user.sql  > dumps/default/t_user 2> dumps/default/t_user.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 car_doc
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> source /undropmysql/car_doc/t_user.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 271 rows affected (0.01 sec)
Records: 271  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING.sql  > dumps/default/WARNING 2> dumps/default/WARNING.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
source /undropmysql/dumps/default/WARNING.sql
Copyright (c) 2000, 2019, 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 PLEASE_READ_ME_VVV
Database changed
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”WARNING'(
        ‘id’ INT NOT NULL,
        ‘warning’ TEXT,
        ‘Bitcoin_A’ at line 1
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/WARNING.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING@00231.sql  > dumps/default/WARNING@00231 2> dumps/default/WARNING@00231.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00231.sql
source /undropmysql/dumps/default/WARNING@00231.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING@00231.sql  > dumps/default/WARNING@00231 2> dumps/default/WARNING@00231.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 PLEASE_READ_ME_VVV
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> source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00231.sql
ERROR 1050 (42S01): Table ‘WARNING@00231’ already exists
mysql> source /undropmysql/dumps/default/WARNING@00231.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000071.page -t PLEASE_READ_ME_VVV/WARNING@00232.sql  > dumps/default/WARNING@00232 2> dumps/default/WARNING@00232.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00232.sql
source /undropmysql/dumps/default/WARNING@00232.sql
mysql> use PLEASE_READ_ME_VVV
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> source /undropmysql/dumps/default/WARNING@00232.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
恢复完成

==================================================================

==========================

 

 

Mysql data unload – undrop for innodb

跟踪oracle 进程链接建立时候发生的情况

 

本方法主要使用触发器的方式对新建立的进程自动进行10046的trace并进行分析的方式,期间使用到truss,需要掌握truss的使用方法,具体如下:

 

创建用户

create user trace_user identified by trace_user account unlock;
grant create session,dba to trace_user;

创建登陆触发器

create or replace trigger sys.set_trace
after logon on database
when (user = 'trace_user')
declare
lcommand varchar(200);
begin
execute immediate 'alter session set statistics_level=all';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end set_trace;
/

 

尝试登陆

sqlplus "trace_user/trace_user" << eof
exit;
eof

这将在$ ORACLE_BASE / admin / <db_name> / udump目录下生成(10046)跟踪文件

然后通过查看“e =”部分查看数据库调用(PARSE,BIND,EXEC,FETCH)是否有大量时间,这意味着数据库调用所消耗的持续时间

通过查看“ela =”部分检查等待事件是否有大量时间,这意味着等待事件消耗的持续时间(可能通过进行系统调用)

PARSING IN CURSOR #5 len=131 dep=1 uid=0 oct=3 lid=0 tim=15687899202263 hv=1389591971 ad='ca9a7948'
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)
END OF STMT
EXEC #5:c=0,e=145,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=15687899202260
WAIT #5: nam='db file sequential read' ela= 30005 file#=1 block#=24208 blocks=1 obj#=-1 tim=15687899232346
FETCH #5:c=0,e=30075,p=1,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=15687899232396

没有数据库调用占用大量时间

less oracle_ora_1118270.trc | grep -i ",e=" | cut -d , -f2 > karlarao.txt ; sed -n 's/e=/ /p' karlarao.txt | sort -nr | less

 358746
 183162
 61293
 44661
 32580
 30075
 28695
 26950
 25837
 24244
 ...

没有等待事件耗费大量时间

less oracle_ora_1118270.trc | grep -i "ela=" | cut -d " " -f8 | sort -nr | less

30005
28624
13253
11592
9650

没有 “os thread startup”事件!

less oracle_ora_1118270.trc | grep -i "ela=" | cut -d "=" -f2 | uniq

'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'db file sequential read' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'db file sequential read' ela
'SQL*Net message to client' ela
'db file sequential read' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'db file sequential read' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela

下来对系统调用进行检测,这里使用truss工具

这个工具可以让你做一些应用程序跟踪,这里是跟踪SQL * Plus,并显示应用程序对外部库和内核的调用

truss -c sqlplus "/ as sysdba" << EOF
exit;
EOF

truss -d sqlplus "/ as sysdba" << EOF
exit;
EOF

 

“-c”开关计算跟踪的系统调用,故障和信号

“-d”开关显示每行的时间戳(以秒为单位)。跟踪输出的第一行将显示测量各个时间戳的基准时间

这里下面可以发现是DNS服务导致sqlplus“/ as sysdba”操作减慢,并且有“os线程启动”等待,下面的文本是“truss”输出的部分,当在/etc/resolv.conf和/ etc / hosts上读取时,“(sleep……)”事件表示减速。

ENOTTY和ECONNREFUSED是看到的错误,这里此文件/usr/include/sys/errno.h上找到有关错误消息的更多详细信息。以下是它们的解释

 

#define	ENOTTY	25	/* Inappropriate I/O control operation	*/
#define ECONNREFUSED    79      /* Connection refused */

 

根据“truss -d”的记录,可以看到时间信息从2.3379秒(读取/etc/resolv.conf) – > 7.3477秒 – > 17.3489秒 – > 37.3555秒(读数) / etc / hosts) – > 70.3863秒(已连接)

0.3172:        gethostname(0x0FFFFFFFFFFF9920, 256) = 0
0.3175:        _getpid()                        = 1102056
0.3177:        open("/etc/resolv.conf", O_RDONLY) = 8
0.3180:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
0.3182:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 69
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 0
0.3190:        close(8)                         = 0
0.3194:        socket(2, 2, 0)                  = 8
0.3197:        getsockopt(8, 65535, 4104, 0x0FFFFFFFFFFF70E4, 0x0FFFFFFFFFFF70E0) = 0
0.3199:        connext(8, 0x09001000A0018600, 16) = 0
0.3202:        send(8, 0x0FFFFFFFFFFF7FF0, 41, 0) = 41
0.3205:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3208:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) Err#79 ECONNREFUSED
0.3210:        close(8)                         = 0
0.3212:        socket(2, 2, 0)                  = 8
0.3215:        sendto(8, 0x0FFFFFFFFFFF7FF0, 41, 0, 0x09001000A0018610, 16) = 41
0.3217:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3220:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) = 108
0.3222:        close(8)                         = 0
0.3224:        socket(2, 2, 0)                  = 8
0.3227:        getsockopt(8, 65535, 4104, 0x0FFFFFFFFFFF70E4, 0x0FFFFFFFFFFF70E0) = 0
0.3229:        connext(8, 0x09001000A0018600, 16) = 0
0.3231:        send(8, 0x0FFFFFFFFFFF7FF0, 28, 0) = 28
0.3233:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3236:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) Err#79 ECONNREFUSED
0.3238:        close(8)                         = 0
0.3240:        socket(2, 2, 0)                  = 8
0.3243:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018610, 16) = 28
0.3245:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3248:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) = 28
0.3250:        close(8)                         = 0
0.3252:        socket(2, 2, 0)                  = 8
0.3373:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018600, 16) = 28
2.3379:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) (sleeping...)
2.3379:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 0
5.3468:        close(8)                         = 0
5.3470:        socket(2, 2, 0)                  = 8
5.3472:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018600, 16) = 28
7.3477:        _poll(0x0FFFFFFFFFFF7190, 1, 10000) (sleeping...)
7.3477:        _poll(0x0FFFFFFFFFFF7190, 1, 10000) = 0
15.3479:        close(8)                        = 0
15.3482:        socket(2, 2, 0)                 = 8
15.3484:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018600, 16) = 28
17.3489:        _poll(0x0FFFFFFFFFFF7190, 1, 20000) (sleeping...)
17.3489:        _poll(0x0FFFFFFFFFFF7190, 1, 20000) = 0
35.3491:        close(8)                        = 0
35.3495:        getdomainname(0x0FFFFFFFFFFF9480, 256) = 0
35.3497:        open("/etc/hosts", O_RDONLY)    = 8
35.3500:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
35.3502:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
35.3505:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 3453
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 0
35.3514:        close(8)                        = 0
kwrite(9, "01 �0603 s".., 432)    = 432
37.3555:        kread(10, "16060280".., 2064) (sleeping...)
kread(10, "06 E06\b".., 2064)   = 1605
70.3762:        open("/oracle/app/product/10.2/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 8
70.3764:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
70.3767:        lseek(8, 0, 0)                  = 0
kread(8, "1513 "011303\t\t".., 256)     = 256
70.3772:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3776:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3781:        lseek(8, 50688, 0)              = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
70.3786:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3790:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3795:        lseek(8, 50688, 0)              = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
70.3799:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3804:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3808:        lseek(8, 50688, 0)              = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
70.3813:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3817:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3822:        lseek(8, 51712, 0)              = 51712
kread(8, "\n0589 D058A".., 512)     = 512
70.3827:        close(8)                        = 0
kwrite(9, " <0611 k".., 60)     = 60
kread(10, " �06\b".., 2064)   = 179
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
70.3854:        lseek(4, 512, 0)                = 512
kread(4, "17 �".., 512)     = 512
70.3858:        lseek(4, 1024, 0)               = 1024
kread(4, "16 * R h819E".., 512)     = 512
70.3863:        lseek(4, 4096, 0)               = 4096
kread(4, "\f82 P86".., 512)     = 512

kwrite(1, "\n", 1)                              = 1
Connected to:
kwrite(1, " C o n n e c t e d   t o".., 14)     = 14
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
kwrite(1, " O r a c l e   D a t a b".., 77)     = 77
With the Partitioning, OLAP, Data Mining and Real Application Testing options
kwrite(1, " W i t h   t h e   P a r".., 78)     = 78

kwrite(1, "\n", 1)                              = 1
70.3882:        kfcntl(1, F_GETFL, 0x0000000000000008) = 2
70.3886:        __libc_sbrk(0x0000000000030020) = 0x000000001023C880
70.3892:        access("login.sql", 0)          Err#2  ENOENT
70.3895:        access("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0) = 0
70.3898:        statfs("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0x0FFFFFFFFFFFDA10) = 0

 

事实证明确实是dns出现了问题,在调整后迅速恢复了响应时间

3.0713:        gethostname(0x0FFFFFFFFFFF9900, 256) = 0
3.0716:        _getpid()                        = 2539762
3.0719:        open("/etc/resolv.conf", O_RDONLY) = 8
3.0721:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
3.0724:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 70
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 0
3.0731:        close(8)                         = 0
3.0735:        socket(2, 2, 0)                  = 8
3.0740:        getsockopt(8, 65535, 4104, 0x0FFFFFFFFFFF70C4, 0x0FFFFFFFFFFF70C0) = 0
3.0742:        connext(8, 0x09001000A0018600, 16) = 0
3.0746:        send(8, 0x0FFFFFFFFFFF7FD0, 41, 0) = 41
3.0750:        _poll(0x0FFFFFFFFFFF7170, 1, 5000) = 1
3.0753:        nrecvfrom(8, 0x0FFFFFFFFFFF9300, 1024, 0, 0x0FFFFFFFFFFF7930, 0x0FFFFFFFFFFF7158) = 108
3.0756:        send(8, 0x0FFFFFFFFFFF7FD0, 28, 0) = 28
3.0758:        _poll(0x0FFFFFFFFFFF7170, 1, 5000) = 1
3.0761:        nrecvfrom(8, 0x0FFFFFFFFFFF9300, 1024, 0, 0x0FFFFFFFFFFF7930, 0x0FFFFFFFFFFF7158) = 28
3.0764:        close(8)                         = 0
3.0767:        getdomainname(0x0FFFFFFFFFFF9460, 256) = 0
3.0769:        open("/etc/hosts", O_RDONLY)     = 8
3.0771:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
3.0774:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
3.0777:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 3453
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 0
3.0786:        close(8)                         = 0
kwrite(9, "01 �0603 s".., 432)    = 432
kread(10, "06 H06\b".., 2064)   = 1608
3.0973:        open("/oracle/app/product/10.2/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 8
3.0975:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
3.0978:        lseek(8, 0, 0)                   = 0
kread(8, "1513 "011303\t\t".., 256)     = 256
3.0983:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.0988:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.0993:        lseek(8, 50688, 0)               = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
3.0997:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.1002:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.1006:        lseek(8, 50688, 0)               = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
3.1011:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.1015:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.1021:        lseek(8, 50688, 0)               = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
3.1025:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.1029:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.1034:        lseek(8, 51712, 0)               = 51712
kread(8, "\n0589 D058A".., 512)     = 512
3.1038:        close(8)                         = 0
kwrite(9, " <0611 k".., 60)     = 60
kread(10, " �06\b".., 2064)   = 179
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
3.1064:        lseek(4, 512, 0)                 = 512
kread(4, "17 �".., 512)     = 512
3.1069:        lseek(4, 1024, 0)                = 1024
kread(4, "16 * R h819E".., 512)     = 512
3.1073:        lseek(4, 4096, 0)                = 4096
kread(4, "\f82 P86".., 512)     = 512

kwrite(1, "\n", 1)                              = 1
Connected to:
kwrite(1, " C o n n e c t e d   t o".., 14)     = 14
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
kwrite(1, " O r a c l e   D a t a b".., 77)     = 77
With the Partitioning, OLAP, Data Mining and Real Application Testing options
kwrite(1, " W i t h   t h e   P a r".., 78)     = 78

kwrite(1, "\n", 1)                              = 1
3.1095:        kfcntl(1, F_GETFL, 0x0000000000000008) = 2
3.1099:        __libc_sbrk(0x0000000000030020)  = 0x000000001023C880
3.1105:        access("login.sql", 0)           Err#2  ENOENT
3.1109:        access("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0) = 0
3.1112:        statfs("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0x0FFFFFFFFFFFD9F0) = 0
跟踪oracle 进程链接建立时候发生的情况

从坏块中恢复未损坏的数据

前面介绍通过skip坏块(event或者dbms包)和构造rowid的方式,以及通过salvage脚本从存在坏块的表中恢复数据,同时也可以通过mdata恢复工具来unload出表中的数据,这里补充一个场景,就是坏块中未损坏的数据的恢复。

从已经损坏的块中尽可能的恢复数据的思路依旧参考的是通过rowid的方式来实现恢复,这里讨论oracle 8i以及8i以上版本的数据库.

 

rowid基本结构这里不做详细解释,前面有介绍过,这下面是官方的介绍。

       OOOOOO = is a base 64 encoding of the 32-bit dataobj# (Data object
                number was introduced in 8.0 to track versions of the same
                segment because certain operations can change the version.
                It is used to discover stale ROWIDs and stale undo records)
          FFF = is a base 64 encoding of the relative file number
       BBBBBB = is a base 64 encoding of the block number
          SSS = is a base 64 encoding of the slot (row) number

   Note that the ROWID contains the relative file number which is distinct from
   the absolute file number and new for Oracle8.  A relative file number is
   relative to the tablespace (meaning a tablespace can have a first, second,
   third file, etc.) and an absolute file number is absolute in the whole
   system.  Two different files may have the same relative number.

   例如 "SELECT ACCT_NO, ROWID from EXAMPLE;" 获得的如下结果:

   ACCT_NO    ROWID
   ---------- ------------------
        12345 AAAAh3AAGAAACJAAAA
        19283 AAAAh3AAGAAACJAAAB
        22345 AAAAh4AAFAAAAADAAA
        60372 AAAAh4AAFAAAAADAAB

     dbms_rowid构造rowid的函数:

              ROWID_CREATE(rowid_type    IN number,
                           object_number IN number,
                           relative_fno  IN number,
                           block_number  IN number,
                           row_number    IN number)
              return ROWID;

     -- rowid_type      - type (restricted=0/extended=1)
     -- object_number   - data object number
     -- relative_fno    - relative file number
     -- block_number    - block number in this file
     -- row_number      - row number in this block



    使用高低位的rowid来跳过坏块使用基于rowid的方式扫描出相关rows,这和上篇介绍的构造的rowid方式是一样的。

     The "LOW_RID" is the lowest rowid INSIDE the corrupt block:

       SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0) LOW_RID
	 from DUAL;

     The "HI_RID" is the first rowid AFTER the corrupt block:

       SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0) HI_RID
	 from DUAL;


	CREATE TABLE salvage_table AS
	 SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A
	  WHERE rowid < '<low_rid>'
	;

	INSERT INTO salvage_table 
	 SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A
	  WHERE rowid >= '<hi_rid>'
	;

    如果是分区表,则通过针对存在坏块的分区的方式即可

	CREATE TABLE salvage_table AS
	 SELECT /*+ ROWID(A) */ * 
	   FROM <owner.tablename> PARTITION (<partition_name>) A
	  WHERE rowid < '<lo_rid>'
	;

	INSERT INTO salvage_table 
	 SELECT /*+ ROWID(A) */ * 
	   FROM <owner.tablename> PARTITION (<partition_name>) A
	  WHERE rowid >= '<hi_rid>'
	;

   如果表中带有long字段需要使用exp/imp的方式来进行相关数据的导入导出,同时要带有where条件来帅选出rowid的范围。
   剩下就是要讨论的从损坏的块中恢复数据,当然如果坏块是段头那么就没必要从段头中恢复rows,可以通过如下的sql判断损坏的块是否表段头。
 
   select file_id,block_id,blocks,extent_id 
   from dba_extents 
   where owner='<owner>' 
     and segment_name='<table_name>' 
     and segment_type='TABLE'
   order by extent_id;

     FILE_ID  BLOCK_ID    BLOCKS EXTENT_ID
   --------- --------- --------- ---------
           8     94854     20780         0 <- EXTENT_ID 为0则代表坏块为段头


  Finding out about data in the Corrupt Block
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  构造rowid的方式根据块号可以构造出坏块里面row地址,前面有说明,如果表中存在索引,则可以尝试通过索引来判断坏块中row的信息,可以结合上面提到的坏块上下块的高低位rowid来扫描出坏块里的rows信息,具体如下:
 
  如果索引所在字段要求为非空,则可以通过以下sql的索引快速全扫描来实现坏块rowid的获取: 
	SELECT /*+ INDEX_FFS(X <index_name>) */ 
		<index_column1>, <index_column2> ...
	  FROM <tablename> X
	 WHERE rowid >= '<low_rid>'
	   AND rowid <  '<hi_rid>'
	;

如果索引所在的列允许null,则无法使用索引快速全扫描并且必须使用范围扫描。这需要知道确保领先索引列的最小可能值,具体sql如下:

	SELECT /*+ INDEX(X <index_name>) */ 
		<index_column1>, <index_column2> ...
	  FROM <tablename> X
	 WHERE rowid >= '<low_rid>'
	   AND rowid <  '<hi_rid>'
	   AND <index_column1> >= <min_col1_value>
	;

 

8i以上版本的恢复案例:

SQL> select * from scott.partitionexample;

	ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
	ORA-01110: data file 7: '/oracle1/oradata/V816/oradata/V816/users01.dbf'

    >>  <RFN> = 7 , <BL> = 12698 , <AFN> = 7

	SQL> SELECT tablespace_name, segment_type, owner, segment_name
               FROM dba_extents
              WHERE file_id =7
		AND 12698 between block_id AND block_id + blocks - 1 ;

        TABLESPACE_NAME  SEGMENT_TYPE       OWNER   SEGMENT_NAME
        ---------------  ------------       -----   ------------
        USERS            TABLE PARTITION    SCOTT   PARTITIONEXAMPLE

        SQL> SELECT partition_name FROM dba_extents
              WHERE file_id =7
	        AND 12698 between block_id AND block _id + blocks - 1;

	PARTITION_NAME
	------------------------------
	PARTEX2

        SQL> SELECT data_object_id
	       FROM dba_objects
	      WHERE object_name = 'PARTITIONEXAMPLE'  and owner='SCOTT'
	        AND subobject_name= 'PARTEX2';

	DATA_OBJECT_ID
 	---------------
     	88145
       
        找出高低位的rowid并扫描相关数据

	SQL> select dbms_rowid.rowid_create(1, 88145,7,12698,0) from dual;

	DBMS_ROWID.ROWID_C
	------------------
	AAAVhRAAHAAADGaAAA

	SQL>  select dbms_rowid.rowid_create(1, 88145,7,12699,0) from dual;

	DBMS_ROWID.ROWID_C
	------------------
	AAAVhRAAHAAADGbAAA

	SQL> SELECT /*+ ROWID(A) */ * 
	       FROM scott.partitionexample A
     	      WHERE rowid < 'AAAVhRAAHAAADGaAAA';

	COLUMN1    COLUMN2
	---------- ----------
	        15 a
	       ...

	SQL> SELECT /*+ INDEX_FFS(A PARTEXAM) */  column1
	       FROM  scott.partitionexample A
	      WHERE  rowid >= 'AAAVhRAAHAAADGaAAA'
		AND  rowid <  'AAAVhRAAHAAADGbAAA' ;
	
	COLUMN1
	----------
	        25
	...

 

 


 

 

REM NAME: salvage.sql
REM NOTE: This program will not handle long, long raw colums or chained rows.
SET ECHO OFF;  
SET SERVEROUTPUT ON;  
  
CREATE OR REPLACE FUNCTION dectohex(a IN NUMBER) RETURN VARCHAR2 IS  
      x VARCHAR2(8) := '';  
        y VARCHAR2(1);  
        z NUMBER;  
        w NUMBER;  
    BEGIN  
        IF a > POWER(2,32) OR a < 0  THEN  
            RAISE invalid_number;  
        END IF;  
        w := a;  
        WHILE w > 0 LOOP  
 z := w MOD 16;  
        IF z = 10 THEN y := 'A';  
        ELSIF z = 11 THEN y := 'B';  
        ELSIF z = 12 THEN y := 'C';  
        ELSIF z = 13 THEN y := 'D';  
     ELSIF z = 14 THEN y := 'E';  
        ELSIF z = 15 THEN y := 'F';  
        ELSE y := TO_CHAR(z);  
        END IF;  
            w := TRUNC(w / 16);  
            x := CONCAT(y,x);   -- build x string backwards  
        END LOOP;  
        RETURN x;  
    END;  
/  
  
  
DECLARE  
  
        table_name         VARCHAR2(30):= 'dept';  
   user_name          VARCHAR2(30):= 'scott';      
  
        file_id            NUMBER;  
        block_id           NUMBER;  
        extentid           number;  
    blocks             NUMBER;  
        file_id_hex        VARCHAR2(4);  
        block_id_hex       VARCHAR2(8);  
        row_count_hex      VARCHAR2(4);  
        row_count          NUMBER;  
        block_counter      NUMBER;  
        for_loop_counter   NUMBER;  
        corrupt_block_flag NUMBER:=0;  
        check_file NUMBER;  
        check_block        NUMBER;  
        row_id_hex         CHAR(18);  
        invalid_rowid      EXCEPTION;  
        rows_per_block     NUMBER:=100;  
  
    CURSOR c1 IS SELECT file_id, block_id, blocks,extent_id  FROM dba_extents  
       WHERE SEGMENT_NAME = UPPER(table_name) AND OWNER = UPPER(user_name)  
    ORDER BY EXTENT_ID;  
            
    CURSOR c2 IS SELECT file_id,block_id FROM corrupt_block;  
      
    PROCEDURE ins (v_rowid VARCHAR2) is  
      bad_rowid  EXCEPTION;  
      PRAGMA EXCEPTION_INIT (bad_rowid, -01410);  
      BEGIN  
   INSERT INTO salvaged_rows_table  
                SELECT *  
                FROM SCOTT.DEPT  
                WHERE rowid = v_rowid;  
  
      EXCEPTION  
         WHEN bad_rowid THEN  
         NULL;  
    END ins;  
  
BEGIN  
  OPEN c1;  
  LOOP   
    FETCH c1 INTO file_id, block_id, blocks,extentid;  
    EXIT WHEN c1%NOTFOUND;  
    dbms_output.put_line('extent: file:'||to_char(file_id)||'  
block_id:'||to_char(block_id)||' blocks:'||to_char(blocks));  
    file_id_hex := DECTOHEX(file_id);  
    block_counter:= block_id;  
    for_loop_counter:= block_id + blocks-1;  
    FOR i IN block_id..for_loop_counter LOOP  
      corrupt_block_flag:= 0;  
      OPEN c2;  
        LOOP  
          FETCH c2 INTO check_file, check_block;  
          EXIT WHEN c2%NOTFOUND;  
          IF (check_block = block_counter AND check_file = file_id) THEN  
             corrupt_block_flag :=1;  
               dbms_output.put_line('....Skipping corrupt  
block:'||to_char(block_counter)|| ' file id:'||to_char(file_id));  
          END IF;  
        END LOOP;    
      CLOSE c2;  
      block_id_hex := dectohex(block_counter);  
      IF corrupt_block_flag = 0 THEN  
        FOR row_count IN 0..rows_per_block LOOP  
            IF row_count = 0 THEN  
               row_count_hex := '0';  
            ELSE  
               row_count_hex:=dectohex(row_count);    
      END IF;  
            row_id_hex:=LPAD(block_id_hex,8,0) ||'.'||  
    LPAD(row_count_hex,4,0) ||'.'|| LPAD(file_id_hex,4,0);  
            ins(row_id_hex);  
        END LOOP;  
      END IF;  
      block_counter := block_counter+1;  
      COMMIT;  
    END LOOP;       
  END LOOP;  
CLOSE c1;  
COMMIT;  
END;  
/
SALVAGE.SQL – PL/SQL Code to Extract Data from a Corrupt Table

利用构造ROWID脚本实现无备份情况下绕过ORA-1578、8103、1410、00600 kdsgrp1 等坏块场景

利用构造rowid的方式来实现绕过坏块恢复正常的表数据库,也可以通过该脚本尝试读取坏块内部未损坏的数据,只是脚本需要修改。

 

SKIP ORA-1578 ORA-8103 ORA-1410


REM Create a new table based on the table that is producing errors with no rows:

create table <new table name>
as
select *
from   <original table name>
where  1=2;

REM Create the table to keep track of ROWIDs pointing to affected rows:

create table bad_rows (row_id rowid, oracle_error_code number);

set serveroutput on

DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

CURSOR c1 IS select /*+ index_ffs(tab1 <index name>) parallel(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL
order by rowid;

r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into <new table name>
select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
from <original table name> A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103, 1578) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

Notes:

  • Replace the next values in the plsql script by the values of the affected table: <index name>, <original table name>, <indexed column>, <list of columns from table (ie col1, col2,..)>
  • <index name> should be replaced by preferrable a Primary Key index.
  • If a Primary Key index is used, remove the where condition:  “where <indexed column> is NOT NULL”
  • The “order by rowid” clause is to get the rows ordered by blocks so a batch of Inserts finds the block in the buffer cache.  Otherwise, one rowid can bring one block from the table and insert just one row, the next rowid brings a different row and insert one row as opposed of finding the same previous block.
  • The idea is to get the rowid’s from an existent index, then get all the columns from the table for each rowid and insert these rows into the new table. Using the “index” hint, allows the optimizer to choose the most appropriated index to scan the table based on the indexed column.
  • Make sure that the select in the plsql is using an index. One way to verify if the index is used is to get an execution plan from sqlplus:

explain plan for
select /*+ index_ffs(tab1 <index name>) parallel(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL
order by rowid;

set lines 200
@?/rdbms/admin/utlxplp</div>


  • Note that the plsql executes an INSERT for 20000 rows and COMMIT. If it is required to change this, adjust the value of rows. e.g.:
rows NATURAL := 50000; -> to insert 50000 rows in one execution of INSERT and commit every 50000 records.
  • If 'Total Bad Rows:' displays 0 and it is known for certain that there is a block incorrect on disk that is causing the ORA-8103, , then it means that the block is empty (no rows) and there is not data loss.

SKIP ORA-600 in a Table

 

This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work.


drop table bad_rows;
create table bad_rows (row_id ROWID
,oracle_error_code number);

rem Create the new empty table:

create table &&new_table
as select *
from &&affected_table
where 1=2;


set serveroutput on
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for i in (select rowid rid from  &&affected_table)  loop
begin
insert into  &&new_table
select *
from &&affected_table
where rowid=i.rid;
n:=n+1;
exception
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/

SKIP ORA-600 in IOT

This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work for an Index Organized Table (IOT).


drop table bad_rows;
create table bad_rows (row_id UROWID
,oracle_error_code number);

rem Create the new empty table:

create table &&new_table
as select *
from &&affected_table
where 1=2;


set serveroutput on
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for i in (select /*+ INDEX_FFS(IOT_1) */ rowid rid from  &&affected_table IOT_1)  loop
begin
insert into  &&new_table
select *
from &&affected_table
where rowid=i.rid;
n:=n+1;
exception
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/