Database SOS – 第80页 – Professional Database Recovery Support@+8617813235971

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

对于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)

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

ORACLE异常恢复后awr异常处理

有一个通过非常规方法恢复过的客户数据库出现问题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不一致等问题,在后续的使用中逐渐被显露出来,从而导致很多麻烦,重建库彻彻底底解决问题.

system01.dbf文件被offline,导致数据库报ORA-01245 ORA-01110故障恢复

有朋友找到我,说数据库做recover报ORA-01245和ORA-01110错误,无法继续恢复,请求支持

SQL> recover database using backup controlfile until cancel;
…………
第 1 行出现错误:
ORA-01245: RESETLOGS 完成时脱机文件 1 将丢失
ORA-01110: 数据文件 1: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\SYSTEM01.DBF'

通过Oracle Database Recovery Check检查数据库情况,发现datafile 1处于offline状态
oracle_recovery_check

Wed Aug 26 23:11:00 2015
alter database datafile 1 offline drop
Completed: alter database datafile 1 offline drop

从这里基本上可以知道为什么出现ORA-01245错误了,由于system表空间中文件被offline导致.

redo信息
oracle_recovery_check_redo

Mon Aug 24 22:38:35 2015
alter database clear unarchived logfile group 2
Clearing online log 2 of thread 1 sequence number 5705
Completed: alter database clear unarchived logfile group 2
Wed Aug 26 23:13:23 2015
alter database clear logfile group 3
Clearing online log 3 of thread 1 sequence number 5706
Completed: alter database clear logfile group 3

除当前redo之外,其他redo被clear

尝试恢复

SQL> alter database datafile 1 online;
数据库已更改。
SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG
ORA-00310: 归档日志包含序列 5706; 要求序列 5705
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO03.LOG'
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG
ORA-00339: 归档日志未包含任何重做
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO02.LOG'
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG
ORA-00310: 归档日志包含序列 5707; 要求序列 5705
ORA-00334: 归档日志: 'E:\APP\ADMINISTRATOR\ORADATA\HXV10\REDO01.LOG'

数据库做恢复需要seq 5705的redo,但是redo已经被clear,导致现在数据库常规手段无法恢复,只用使用隐含参数屏蔽数据库前滚(一致性检查)

再次尝试打开数据库

ORACLE 例程已经启动。
Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             486540712 bytes
Database Buffers          285212672 bytes
Redo Buffers                5259264 bytes
数据库装载完毕。
SQL> recover database using backup controlfile;
ORA-00279: 更改 63960710 (在 08/23/2015 17:01:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\HXV10\ARCHIVELOG\2015_08_27\O1_MF_1_570
5_%U_.ARC
ORA-00280: 更改 63960710 (用于线程 1) 在序列 #5705 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SQL> alter database open resetlogs;
数据库已更改。

在数据库恢复中,请不要对system表空间数据文件进行offline操作,如果对此类文件进行offline操作,讲在数据库恢复过程中出现ORA-01245和ORA-01110错误,而且文件还会出现SYSOFF状态

[MySQL异常恢复]mysql delete 数据恢复

在mysql(innodb引擎)中,有些时候犹豫误操作导致表中数据被删除,从而导致不可挽回的损失,本文模拟在数据库被误delete的情况下,实现较为完美删除,当然在实际中可能有少量不覆盖或者无法恢复回来,但是在覆盖不多或者未覆盖的情况下,可以实现绝大多数甚至全部恢复.因此在发生误操作时候,应当第一时间保护现场,尽可能防止复写导致不可挽回的损失.在测试恢复过程中,由于mysql和操作系统编码问题,折腾了很久，感谢Lunar的指点
创建模拟表并插入数据

mysql> CREATE TABLE `sms_send_record_del` (
    ->   `messageId` varchar(30) NOT NULL,
    ->   `tokenId` varchar(20) NOT NULL,
    ->   `mobile` varchar(14) default NULL,
    ->   `msgFormat` int(1) NOT NULL,
    ->   `msgContent` varchar(1000) default NULL,
    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `deliverState` int(1) default NULL,
    ->   `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00',
    ->   PRIMARY KEY  (`messageId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sms_send_record_del select * from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0
mysql> checksum table sms_send_record_del;
+---------------------------------+------------+
| Table                           | Checksum   |
+---------------------------------+------------+
| sms_service.sms_send_record_del | 2258631583 |
+---------------------------------+------------+
1 row in set (0.00 sec)
mysql> checksum table sms_send_record;
+-----------------------------+------------+
| Table                       | Checksum   |
+-----------------------------+------------+
| sms_service.sms_send_record | 2258631583 |
+-----------------------------+------------+
1 row in set (0.00 sec)

mysql-delete-recovery-1

确定innodb文件对应位置

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

删除表记录
模拟错误操作,误删除表所有数据

mysql> delete from sms_send_record_del;
Query OK, 11 rows affected (0.00 sec)

解析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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.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:         315712
Opening file: /var/lib/mysql/ibdata1
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
File information:
total size, in bytes:            161480704 (154.000 MiB)
ID of device containing file:         2049
Size to process:                 161480704 (154.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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
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
File information:
blocksize for filesystem I/O:         4096
number of blocks allocated:         315712
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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.000 MiB)
time of last access:            1440601884 Wed Aug 26 23:11:24 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)
Size to process:                 161480704 (154.000 MiB)
All workers finished in 0 sec

分析数据字典

mysql> show tables
    -> ;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    |
| SYS_FIELDS     |
| SYS_INDEXES    |
| SYS_TABLES     |
+----------------+
4 rows in set (0.00 sec)
mysql> select * from SYS_TABLES;
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| NAME                                   | ID | N_COLS      | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| recover/t_delete                       | 74 |           2 |    1 |      0 |       0 |              |     0 |
| recover/t_delete1                      | 84 |           2 |    1 |      0 |       0 |              |     0 |
| recover/t_xifenfei                     | 75 |           2 |    1 |      0 |       0 |              |     0 |
| recover/zx_users                       | 89 |          85 |    1 |      0 |       0 |              |     0 |
| sms_service/sms_send_record            | 36 |           8 |    1 |      0 |       0 |              |     0 |
| sms_service/sms_send_record_del        | 90 |           8 |    1 |      0 |       0 |              |     0 |
| SYS_FOREIGN                            | 11 | -2147483644 |    1 |      0 |       0 |              |     0 |
| SYS_FOREIGN_COLS                       | 12 | -2147483644 |    1 |      0 |       0 |              |     0 |
| test/SYS_COLUMNS                       | 86 |           7 |    1 |      0 |       0 |              |     0 |
| test/SYS_FIELDS                        | 88 |           3 |    1 |      0 |       0 |              |     0 |
| test/SYS_INDEXES                       | 87 |           7 |    1 |      0 |       0 |              |     0 |
| test/SYS_TABLES                        | 85 |           8 |    1 |      0 |       0 |              |     0 |
| test/zx_users                          | 43 |          85 |    1 |      0 |       0 |              |     0 |
| xifenfei/t_delete                      | 44 |           8 |    1 |      0 |       0 |              |     0 |
| xifenfei/t_xifenfei                    | 59 |           2 |    1 |      0 |       0 |              |     0 |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
39 rows in set (0.00 sec)
mysql> select * from SYS_INDEXES WHERE TABLE_ID=90;
+----------+-----+---------+----------+------+-------+---------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+---------+----------+------+-------+---------+
|       90 | 110 | PRIMARY |        1 |    3 |     0 |    2955 |
+----------+-----+---------+----------+------+-------+---------+
1 row in set (0.00 sec)

找回被删除记录

[root@web103 mysql_recovery]# ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000110.page \
[root@web103 mysql_recovery]#    -t dictionary/sms_send_record_del.sql >/tmp/t_1.txt 2>/tmp/t_1.sql

mysql-delete-recovery-2

加载数据并验证

mysql> use sms_service;
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 /tmp/t_1.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 11 rows affected, 8 warnings (0.01 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 8
mysql> checksum table sms_send_record_del;
+---------------------------------+------------+
| Table                           | Checksum   |
+---------------------------------+------------+
| sms_service.sms_send_record_del | 2258631583 |
+---------------------------------+------------+
1 row in set (0.00 sec)

发生误操作之时,请尽可能保护现场,防止覆盖导致无可挽回的损失.

[MySQL异常恢复]恢复数据字典表讲解

在以前的文章中说过mysql的数据字典的恢复(使用工具直接抽取MySQL数据字典,缺少SYS_FIELDS表),主要的数据字典有一下几个，在本文中主要对这些数据字典的意义进行一些讲解，为大家更深一步了解mysql恢复处理思路
MySQL恢复字典表

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

SYS_TABLES
这个表是mysql恢复的最核心的表之一，主要是记录数据库在InnoDB中表的信息。它默认写在InnoDB的index_ids为1的里面，它的根页在8号page上，他的主要列结构为：

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)

NAME:顾名思义,就是表的名字,但是注意他记录的格式是db/table,例如:xifenfei/zx_users,表示为xifenfei数据库中的zx_users表
ID:表的编号
N_COLS:表一共包含的列的数量
TYPE, MIX_ID, MIX_LEN 和 CLUSTER_NAME列,对于数据库恢复无任何意义不做描述
SPACE:表空间的标示列. 例如： ibdata1 是 SPACE 0, ibdata2 是 SPACE 1, 每一个 ibd 文件都有自己的表空间标示.

SYS_INDEXES
这个也是mysql恢复的最核心表之一,主要是记录InnoDB的index信息,它默认InnoDB的index_ids为3的里面，他的主要结构为：

mysql> desc SYS_INDEXES;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | 0       |       |
| ID       | bigint(20) unsigned | NO   | PRI | 0       |       |
| NAME     | varchar(120)        | YES  |     | NULL    |       |
| N_FIELDS | int(10) unsigned    | YES  |     | NULL    |       |
| TYPE     | int(10) unsigned    | YES  |     | NULL    |       |
| SPACE    | int(10) unsigned    | YES  |     | NULL    |       |
| PAGE_NO  | int(10) unsigned    | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表标示列,为SYS_TABLES.ID
ID:为InnoDB中的index的编号,这个在恢复中非常重要,恢复之时需要根据这个去定位具体的文件
NAME:主要表的index的名字,有PRIMARY 和普通列的index信息,一般恢复之时我们选择PRIMARY
N_FIELDS:表名index包含列的数量,在mysql恢复中不重要
TYPE:恢复之中使用不到该列,不做说明
PAGE:用途等同SYS_TABLES.SPACE
PAGE_NO:标示为每个index的root page的page号,关于index中的page结构如下图所示
Bplustree

SYS_COLUMNS
这个表主要记录数据库中表的列的情况,它存储在index_id 2中.主要用它来确定需要恢复表的列的情况,如果你知道完全的列结构,该表不是MySQL恢复所必须的,它的主要结构为：

mysql> desc SYS_COLUMNS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | NULL    |       |
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       |
| NAME     | varchar(255)        | YES  |     | NULL    |       |
| MTYPE    | int(10) unsigned    | YES  |     | NULL    |       |
| PRTYPE   | int(10) unsigned    | YES  |     | NULL    |       |
| LEN      | int(10) unsigned    | YES  |     | NULL    |       |
| PREC     | int(10) unsigned    | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表标示列,为SYS_TABLES.ID
POS:该列所在表中的位置,该值从0开始
NAME:列的名字
MTYPE 和 PRTYPE:主要是为了记录列的类型,出现此类问题主要是由于InnoDB最初并不是为MySQL而设计,到了后面为更好支持MySQL,因此出现了两种情况.
LEN:列的长度.这个需要注意编码,比如数据库是utf8编码,定义的varchar(10),实际该处长度显示为30,因为每个除英文外的字符编码为3个byte.
PREC:有些特殊类型中,列的精确度定义

SYS_FIELDS
记录所有index的列的分布信息,它存储在index_id 4中,该表不是MySQL恢复所必须的,它的主要结构为:

mysql> desc SYS_FIELDS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(20) unsigned | NO   | PRI | NULL    |       |
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       |
| COL_NAME | varchar(255)        | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

INDEX_ID:index的标志,等同SYS_INDEXES.ID
POS:列在index中的位置,从0开始
COL_NAME:列的名称
通过上述相关表和列,然后结合MySQL相关恢复工具,就可以从底层在InnoDB出现问题,或者误操作之时提供恢复处理.

MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

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

[root@localhost recovery_MySQL]# ./stream_parser -f /tmp/ibdata1
Opening file: /tmp/ibdata1
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
File information:
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 total size, in bytes:             18874368 (18.000 MiB)
(regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
number of hard links:                    1
user ID of owner:                        0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
group ID of owner:                       0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
group ID of owner:                       0
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)
File information:
Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)
File information:
Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
File information:
total size, in bytes:             18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 Size to process:                  18874368 (18.000 MiB)
(regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
Opening file: /tmp/ibdata1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
File information:
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
Size to process:                  18874368 (18.000 MiB)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
Opening file: /tmp/ibdata1
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
File information:
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
total size, in bytes:             18874368 (18.000 MiB)
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
Size to process:                  18874368 (18.000 MiB)
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
Size to process:                  18874368 (18.000 MiB)
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
device ID (if special file):             0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
protection:                         100644 (regular file)
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of hard links:                    1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
total size, in bytes:             18874368 (18.000 MiB)
number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec
[root@localhost recovery_MySQL]# cd pages-ibdata1
[root@localhost pages-ibdata1]# ls -l
total 16
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_INDEX
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_TYPE_BLOB
[root@localhost pages-ibdata1]# cd FIL_PAGE_INDEX
[root@localhost FIL_PAGE_INDEX]# ls -l
total 9924
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000001.page
-rw-r--r-- 1 root root  278528 Aug 20 22:53 0000000000000002.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000003.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000004.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000005.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000011.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000012.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000013.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000014.page
-rw-r--r-- 1 root root 2883584 Aug 20 22:53 0000000000000065.page
-rw-r--r-- 1 root root  475136 Aug 20 22:53 0000000000000066.page
-rw-r--r-- 1 root root  737280 Aug 20 22:53 0000000000000067.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000068.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000069.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000070.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000071.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000072.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000073.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000074.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000075.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000076.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000077.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000078.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000079.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000080.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000081.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000082.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000083.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000084.page
-rw-r--r-- 1 root root  753664 Aug 20 22:53 0000000000000085.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000086.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000087.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000088.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000089.page
-rw-r--r-- 1 root root  901120 Aug 20 22:53 0000000000000110.page
-rw-r--r-- 1 root root 2097152 Aug 20 22:53 0000000000000115.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000116.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000117.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000118.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000119.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000120.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000121.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000122.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000123.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000124.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 18446744069414584320.page
[root@localhost FIL_PAGE_INDEX]#

分析数据字典

[root@localhost recovery_MySQL]# ./recover_dictionary.sh
Generating dictionary tables dumps... OK
Creating test database ... Warning: Using a password on the command line interface can be insecure.
OK
Creating dictionary tables in database test:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
OK
All OK
Loading dictionary tables data:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
34 recs OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
3061 recs OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
All OK

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

查看数据库中记录情况

[root@localhost recovery_MySQL]# MySQL -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34954
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    |
| SYS_FIELDS     |
| SYS_INDEXES    |
| SYS_TABLES     |
+----------------+
4 rows in set (0.00 sec)
MySQL> SELECT NAME,ID from SYS_TABLES;
+-------------------------+----+
| NAME                    | ID |
+-------------------------+----+
| cnywmcom/zx_account_log | 31 |
| cnywmcom/zx_area_code   | 32 |
| cnywmcom/zx_goods_group | 33 |
| cnywmcom/zx_manage_type | 34 |
| cnywmcom/zx_order_goods | 35 |
| cnywmcom/zx_order_info  | 36 |
| cnywmcom/zx_param       | 37 |
| cnywmcom/zx_users       | 46 |
| cnywmcom/zx_user_type   | 38 |
| SYS_FOREIGN             | 11 |
| SYS_FOREIGN_COLS        | 12 |
+-------------------------+----+
11 rows in set (0.00 sec)
MySQL> select * from SYS_INDEXES;
+----------+-----+-----------------+----------+------+-------+---------+
| TABLE_ID | ID  | NAME            | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+-----------------+----------+------+-------+---------+
|       11 |  11 | ID_IND          |        1 |    3 |     0 |      46 |
|       11 |  12 | FOR_IND         |        1 |    0 |     0 |      47 |
|       11 |  13 | REF_IND         |        1 |    0 |     0 |      48 |
|       12 |  14 | ID_IND          |        2 |    3 |     0 |      49 |
|       31 |  65 | PRIMARY         |        1 |    3 |     0 |      50 |
|       31 |  66 | user_id         |        1 |    0 |     0 |     216 |
|       32 |  67 | PRIMARY         |        1 |    3 |     0 |      52 |
|       33 |  68 | PRIMARY         |        1 |    3 |     0 |     205 |
|       34 |  69 | PRIMARY         |        1 |    3 |     0 |      54 |
|       35 |  70 | PRIMARY         |        1 |    3 |     0 |      55 |
|       35 |  71 | order_id        |        1 |    0 |     0 |      56 |
|       35 |  72 | goods_id        |        1 |    0 |     0 |      57 |
|       36 |  73 | PRIMARY         |        1 |    3 |     0 |      59 |
|       36 |  74 | order_sn        |        1 |    2 |     0 |      60 |
|       36 |  75 | user_id         |        1 |    0 |     0 |      61 |
|       36 |  76 | order_status    |        1 |    0 |     0 |      62 |
|       36 |  77 | shipping_status |        1 |    0 |     0 |      63 |
|       36 |  78 | pay_status      |        1 |    0 |     0 |     192 |
|       36 |  79 | shipping_id     |        1 |    0 |     0 |     193 |
|       36 |  80 | pay_id          |        1 |    0 |     0 |     194 |
|       36 |  81 | extension_code  |        2 |    0 |     0 |     195 |
|       36 |  82 | agency_id       |        1 |    0 |     0 |     203 |
|       37 |  83 | PRIMARY         |        1 |    3 |     0 |     196 |
|       38 |  84 | PRIMARY         |        1 |    3 |     0 |     206 |
|       46 | 120 | PRIMARY         |        1 |    3 |     0 |      58 |
|       46 | 121 | user_name       |        1 |    2 |     0 |     256 |
|       46 | 122 | email           |        1 |    0 |     0 |     257 |
|       46 | 123 | parent_id       |        1 |    0 |     0 |     258 |
|       46 | 124 | flag            |        1 |    0 |     0 |     259 |
+----------+-----+-----------------+----------+------+-------+---------+
29 rows in set (0.00 sec)

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

分析表恢复

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

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

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

exp dmp文件损坏(坏块/corruption)恢复—跳过dmp坏块

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

SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> create table t_xifenfei1 as select * from dba_objects;
Table created.
SQL> create table t_xifenfei2 as select * from v$sql;
Table created.
SQL> create table t_xifenfei3 as select * from dba_tables;
Table created.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
T_XIFENFEI2                    TABLE
T_XIFENFEI3                    TABLE
SQL> select count(*) from t_xifenfei1;
  COUNT(*)
----------
     86275
SQL>  select count(*) from t_xifenfei2;
  COUNT(*)
----------
      3387
SQL>  select count(*) from t_xifenfei3;
  COUNT(*)
----------
      2800

导出来dmp文件

[oracle@web103 ~]$ exp "'/ as sysdba'" owner=xifenfei file=/data/temp/t_xifenfei.dmp log=/data/temp/exp_t_xifenfei.log
Export: Release 11.2.0.4.0 - Production on Tue Aug 18 22:08:30 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user XIFENFEI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user XIFENFEI
About to export XIFENFEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export XIFENFEI's tables via Conventional Path ...
. . exporting table                    T_XIFENFEI1      86275 rows exported
. . exporting table                    T_XIFENFEI2       3387 rows exported
. . exporting table                    T_XIFENFEI3       2800 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

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

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

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

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

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

[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew
>file=/data/temp/t_xifenfei.dmp log=/data/temp/imp_t_xifenfei.log
Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:35:09 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table                  "T_XIFENFEI1"      86275 rows imported
IMP-00017: following statement failed with ORACLE error 1740:
 "CREATE TABLE "T_XIFENFEI2" ("SQL_TEXT" VARCHAR2(1000), "SQL_FULLTEXT" CLOB,"
 " "SQL_ID" VARCHAR2(13), "SHARABLE_MEM" NUMBER, "PERSISTENT_MEM" NUMBER, "RU"
 "NTIME_MEM" NUMBER, "SORTS" NUMBER, "LOADED_VERSIONS" NUMBER, "OPEN_VERSIONS"
 "" NUMBER, "USERS_OPENING" NUMBER, "FETCHES" NUMBER, "EXECUTIONS" NUMBER, "P"
 "X_SERVERS_EXECUTIONS" NUMBER, "END_OF_FETCH_COU"
IMP-00003: ORACLE error 1740 encountered
ORA-01740: missing double quote in identifier
IMP-00008: unrecognized statement in the export file:
IMP-00008: unrecognized statement in the export file:

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

检查导入表情况

SQL> conn xifenfeinew/"www.xifenfei.com"
Connected.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
SQL> select count(*) from t_xifenfei1;
  COUNT(*)
----------
     86275

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

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

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

重新导入dmp文件

[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew
>file=/data/temp/t_xifenfeinew.dmp log=/data/temp/imp_t_xifenfeinew.log
Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:41:04 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table                  "T_XIFENFEI1"      86275 rows imported
. . importing table                  "T_XIFENFEI3"       2800 rows imported
Import terminated successfully no warnings.

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

验证导入数据

[oracle@web103 ~]$ sqlplus xifenfeinew/"www.xifenfei.com"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 22:41:32 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
T_XIFENFEI3                    TABLE
SQL> select count(*) from t_xifenfei1;
  COUNT(*)
----------
     86275
SQL> select count(*) from t_xifenfei3;
  COUNT(*)
----------
      2800
SQL>

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

使用dml语句删除index测试(取代ddl)

在有些极端情况下,数据库由于某种原因无法执行ddl操作,但是可以执行dml语句,我们可以通过dml语句来替代ddl操作(非官方支持,纯属个人闲着无聊中的测试,请勿模仿,否则后果自负)
创建测试用户xifenfei

[oracle@web103 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 16 23:04:25 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user xifenfei identified by "www.xifenfei.com";
User created.
SQL> grant dba to xifenfei;
Grant succeeded.

创建测试index，并使用10046跟踪

SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL>  alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL>  select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_18838.trc
SQL> create index ind_xifenfei_1 on t_xifenfei(object_id,data_object_id);
Index created.
SQL> exit
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

分析trace文件,找出来核心操作步骤

--插入创建index的记录到obj$中
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,
  remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
--插入index的信息到seg$中
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,
  extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1,
  scanhint, bitmapranges)
values
 (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,
  :17),:18,:19)
--继续分配index的空间
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
  :9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
  groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
  DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19
where
 ts#=:1 and file#=:2 and block#=:3
--插入index对应的列信息到icol$中
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,
  spare1,spare2,spare3)
values
(:1,:2,:3,:4,0,0,0,:5,:6,:7,:8)
--插入index信息到ind$中
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,
  pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,
  cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,
  indmethod#,trunccnt,spare1,spare4,spare2,spare6)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
  :22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,
  :29,:30,:31,:33)

通过这里我们基本上可以整体概况出来创建index主要操作了obj$,seg$,icol$,ind$几张基表,那么如果我们想通过dml语句删除index,那可以尝试人工删除这些新增记录

分析人工dml语句删除index

[oracle@web103 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 16 23:31:49 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> select object_id,data_object_id from user_objects where object_name='IND_XIFENFEI_1';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    114642         114642
SQL> select object_id,data_object_id from user_objects where object_name='T_XIFENFEI';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    114641         114641
SQL>  SELECT OBJ#,DATAOBJ# FROM SYS.OBJ$ WHERE NAME='IND_XIFENFEI_1';
      OBJ#   DATAOBJ#
---------- ----------
    114642     114642
--通过上面语句我们可以确定,IND_XIFENFEI_1 index的obj#和dataobj#都是为114642,而且可以直接查询obj$基表获得
SQL> SELECT OBJ#,DATAOBJ#,TS#,FILE#,BLOCK# FROM SYS.IND$ WHERE OBJ#=114642;
      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#
---------- ---------- ---------- ---------- ----------
    114642     114642          4          4     323994
--ind$结合obj$.obj#的信息,可以获得ts#,file#,block#
SQL> SELECT TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='IND_XIFENFEI_1';
TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USERS                                    4       323994
SQL> SELECT TS# FROM V$TABLESPACE WHERE NAME='USERS';
       TS#
----------
         4
--这里证明ind$中的相关ts#,file#,block#和seg#中存储一致
SQL>  SELECT COL#,POS# FROM  SYS.icol$ WHERE  OBJ#=114642;
      COL#       POS#
---------- ----------
         4          1
         5          2
SQL> SELECT COL#,NAME from SYS.COL$ where OBJ# =114641;
      COL# NAME
---------- ------------------------------
         1 OWNER
         2 OBJECT_NAME
         3 SUBOBJECT_NAME
         4 OBJECT_ID
         5 DATA_OBJECT_ID
         6 OBJECT_TYPE
         7 CREATED
         8 LAST_DDL_TIME
         9 TIMESTAMP
        10 STATUS
        11 TEMPORARY
        12 GENERATED
        13 SECONDARY
        14 NAMESPACE
        15 EDITION_NAME
15 rows selected.
--这里证明icol$中存储的列，正好和我们创建index指定列一致

人工dml删除index

SQL> conn  / as sysdba
Connected.
SQL> delete from obj$ where obj#=114642 and dataobj#=114642;
1 row deleted.
SQL> delete from sys.seg$ where file#=4 and ts#=4 and block#=323994;
1 row deleted.
SQL> delete from icol$ where obj#=114642;
2 rows deleted.
SQL> delete from ind$ where obj#=114642 and file#=4 and ts#=4 and block#=323994;
1 row deleted.
SQL> commit;
Commit complete.

检查dml语句删除index效果

SQL>  select object_id,data_object_id from user_objects where object_name='IND_XIFENFEI_1';
no rows selected
SQL>  SELECT TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='IND_XIFENFEI_1';
no rows selected
SQL> select * from dba_indexes where index_name='IND_XIFENFEI_1';
no rows selected

从数据字典层面看，index IND_XIFENFEI_1确实已经被删除

尝试重建同名index
从数据字典层面查询,该index已经被删除,但是创建报ORA-00955: name is already used by an existing object错误

SQL> create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id);
create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id)
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object

解决重建同名index报ORA-00955错误

SQL> ALTER SESSION SET EVENTS '955 trace name errorstack level 3';
Session altered.
SQL> create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id);
create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id)
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>  select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_20277.trc
--分析trace文件发现创建index执行计划部分
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id  | Operation               | Name          | Rows  | Bytes | Cost  | Time      |
------------------------------------------------+-----------------------------------+
| 0   | CREATE INDEX STATEMENT  |               |       |       |   548 |           |
| 1   |  INDEX BUILD NON UNIQUE | IND_XIFENFEI_1|       |       |       |           |
| 2   |   SORT CREATE INDEX     |               |  127K | 3302K |       |           |
| 3   |    INDEX FAST FULL SCAN | IND_XIFENFEI_1|       |       |       |           |
------------------------------------------------+-----------------------------------+
--从这里大概知道原因了，创建index之时，由于shared pool里面依旧记录了老的index信息，因此再次创建index之时，
--直接从老index读取信息，而且明确也依旧是在shared pool里面检查未通过，从而出现了ORA-00955错误
SQL> alter system flush shared_pool;
System altered.
SQL> create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id);
Index created.
SQL> select obj#,dataobj# from SYS.obj$ where name='IND_XIFENFEI_1';
      OBJ#   DATAOBJ#
---------- ----------
    114643     114643
SQL> select object_type,object_name from dba_objects where object_id=114643;
OBJECT_TYPE
-------------------
OBJECT_NAME
--------------------------------------------------------------------------------
INDEX
IND_XIFENFEI_1
SQL> select index_name from dba_indexes where table_name='T_XIFENFEI' AND OWNER='XIFENFEI';
INDEX_NAME
------------------------------
IND_XIFENFEI_1

补充说明
1.在不同的数据库版本,不同的平台可能操作语句以后一定的出入,依据你测试为准
2.在dml删除index操作中,可能还涉及到一些其他外围表处理,比如统计信息等
3.切勿在生产库中测试,否则可能导致数据库数据字典混乱,后果非常严重

Linux 7 新命令之—lscpu和systemctl