标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:十一月 2011
Data pump 中network_link参数的使用续(登录用户和源端用户不一致处理)
1、准备工作
[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:08:04 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create user test identified by xifenfei; User created. SQL> grant connect,resource to test; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
2、远程导出数据
[oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1 Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:09:35 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name TEST_DIR is invalid --对目录需要相关权限 [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:09:59 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> grant read,write on directory test_dir to test; Grant succeeded. SQL> expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1 SP2-0734: unknown command beginning "expdp test..." - rest of line ignored. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1 Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:10:23 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORA-31631: privileges are required ORA-39109: Unprivileged users may not operate upon other users' schemas --执行导出用户需要相关权限exp_full_database [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:11:54 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> grant exp_full_database to test; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1 Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:14:29 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 22 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "CHF"."T2_1" 9.326 MB 100000 rows Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is: /tmp/t1.dmp Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 09:14:35 --实现用test登录,导出源端chf用户下面的表 [oracle@node1 ~]$ rm /tmp/t1.dmp [oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=T2_1 Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:14:57 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORA-39001: invalid argument value ORA-39195: At least one schema in the TABLE_FILTER does not exist. --如果当前登录用户和表所属用户不同,需要指定schema的名称
3、直接导入数据到目标端
[oracle@node1 ~]$ impdp test/xifenfei directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=chf.t2_1 Import: Release 11.2.0.3.0 - Production on Wed Nov 30 09:45:38 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORA-31631: privileges are required ORA-39109: Unprivileged users may not operate upon other users' schemas --登录用户需要imp_full_database权限 [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:46:41 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> grant imp_full_database to test; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ impdp test/xifenfei directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=chf.t2_1 Import: Release 11.2.0.3.0 - Production on Wed Nov 30 09:46:59 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=chf.t2_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 22 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "XFF"."T2_1" 100000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 09:47:07 --使用test用户登录,目标端用户为xff,源端用户为chf [oracle@node1 ~]$ impdp test/xifenfei directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1 Import: Release 11.2.0.3.0 - Production on Wed Nov 30 09:52:54 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORA-39001: invalid argument value ORA-39195: At least one schema in the TABLE_FILTER does not exist. --不同用户时,需要指定源端表所属用户
Data pump 中network_link参数的使用
一、准备工作
1、源端
[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 29 15:07:35 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from T2_1 ; COUNT(*) ---------- 100000
2、目标端
--tns配置 test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 14:57:08 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create public database link dblink_test 2 connect to chf identified by xifenfei using 'test'; Database link created. SQL> grant read,write on directory test_dir to xff; Grant succeeded.
二、导入方式
1、expdp导出,impdp导入
[oracle@node1 ~]$ expdp chf/xff directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1 Export: Release 11.2.0.3.0 - Production on Tue Nov 29 15:05:36 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 22 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "CHF"."T2_1" 9.326 MB 100000 rows Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_01 is: /tmp/t1_2.dmp Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at 15:06:52 --1、userid使用的是目标端(expdp端)登录 --2、tables对应的表所有者需要和userid相同 [oracle@node1 tmp]$ ll /tmp/t1_2.dmp -rw-r----- 1 oracle oinstall 9859072 11-29 15:06 /tmp/t1_2.dmp [oracle@node1 tmp]$ impdp xff/xifenfei directory=test_dir dumpfile=t1_2.dmp REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users Import: Release 11.2.0.3.0 - Production on Tue Nov 29 15:30:15 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "XFF"."SYS_IMPORT_FULL_01": xff/******** directory=test_dir dumpfile=t1_2.dmp REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "XFF"."T2_1" 9.326 MB 100000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at 15:30:18 [oracle@node1 tmp]$ sqlplus xff/xifenfei SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 15:30:43 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select count(*) from t2_1; COUNT(*) ---------- 100000
2、使用impdp直接导入
[oracle@node1 tmp]$ impdp chf/xff directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1 Import: Release 11.2.0.3.0 - Production on Tue Nov 29 15:48:49 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "CHF"."SYS_IMPORT_TABLE_01": chf/******** directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 22 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "XFF"."T2_1" 100000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "CHF"."SYS_IMPORT_TABLE_01" successfully completed at 15:49:00 --1、userid使用的是impdp端登录 --2、tables对应的表所有者需要和userid相同(tables的表所有者是源端,使用REMAP_SCHEMA映射owner) [oracle@node1 tmp]$ sqlplus xff/xifenfei SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 15:51:18 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select count(*) from t2_1; COUNT(*) ---------- 100000
如果登录用户和需要导入的表用户名不同,暂不清楚怎么处理
Mysql误删除数据及其bug分析
一、现状描述
11月25日晚上8点40分接到现场电话,说我们公司所有员工的im不能正常登陆,im数据库服务器(mysql数据库)的公司表中对应的我们公司名称被删除,需要处理。接到这个异常后,第一想到的是下午下班前,收到一封ogg的警告邮件说ogg进程终止,然后我登陆数据库查看的时候,发现ogg已经工作正常,所以也就没有太多关注。既然已经出现了这个问题,那么先解决问题,再分析原因。因为这些都是ogg从oracle端同步过来的一些数据,所以直接从oracle那边初始化一份过来,然后重设同步程序就可以了。
二、错误分析
1、出现这个问题,第一想到的就是binlog,因为公司表的数据是从我们oracle那边同步过去的,而且oracle那边没有任何关于这个表的删除操作,所以我定位这个表的delete操作
[mysql@ezgclient mysqllog]$ mysqlbinlog mysqlbin.000150>/tmp/11_25.txt [mysql@ezgclient mysqllog]$ vi /tmp/11_25.txt …… # at 1396789 # at 1396875 #111125 16:15:31 server id 2 end_log_pos 1396875 Table_map: `a`.`abc` mapped to number 5304 #111125 16:15:31 server id 2 end_log_pos 1397000 Delete_rows: table id 5304 flags: STMT_END_F BINLOG ' o07PThMCAAAAVgAAAItQFQAAALgUAAAAAAAABnNydGFpcgAPdGFiX3V1bV9jb21wYW55AA4DD/b2 CAwMCA8PDw8PCBAAARQAFgCAAIAAQABAAEAA+D8= o07PThkCAAAAfQAAAAhRFQAQALgUAAAAAAEADv//AP7iAAAAFAC6vNbdysC1vL/GvLzT0M/euavL voAAAAAAOLkeQYAAAAAAAAAAAAEpAAAAAAAAANkqumA8EgAAO8kaakoSAAABAAAAAAAAAA9CVTAw MDAwMDAzNTgwNzg= ……
2、因为binlog_format采用的是row模式,所以需要进一步解析binglog
[mysql@ezgclient mysqllog]$ mysqlbinlog -v -v mysqlbin.000150>/tmp/11_25.txt [mysql@ezgclient mysqllog]$ vi /tmp/11_25.txt #111125 16:15:31 server id 2 end_log_pos 1396875 Table_map: `a`.`abc` mapped to number 5304 #111125 16:15:31 server id 2 end_log_pos 1397000 Delete_rows: table id 5304 flags: STMT_END_F BINLOG ' o07PThMCAAAAVgAAAItQFQAAALgUAAAAAAAABnNydGFpcgAPdGFiX3V1bV9jb21wYW55AA4DD/b2 CAwMCA8PDw8PCBAAARQAFgCAAIAAQABAAEAA+D8= o07PThkCAAAAfQAAAAhRFQAQALgUAAAAAAEADv//AP7iAAAAFAC6vNbdysC1vL/GvLzT0M/euavL voAAAAAAOLkeQYAAAAAAAAAAAAEpAAAAAAAAANkqumA8EgAAO8kaakoSAAABAAAAAAAAAA9CVTAw MDAwMDAzNTgwNzg= '/*!*/; ### DELETE FROM a.abc ### WHERE ### @1=226 /* INT meta=0 nullable=0 is_null=0 */ ### @2='*****有限公司' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */ ### @3=951656001 /* DECIMAL(20,0) meta=5120 nullable=0 is_null=0 */ ### @4=000000001 /* DECIMAL(22,0) meta=5632 nullable=1 is_null=0 */ ### @5=41 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @6=2005-05-30 15:11:29 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @7=2011-08-17 02:02:19 /* DATETIME meta=0 nullable=1 is_null=0 */ ### @8=1 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @9='BU0000000358078' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */ ### @10=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @11=NULL /* VARSTRING(128) meta=64 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(128) meta=64 nullable=1 is_null=1 */ ### @13=NULL /* VARSTRING(128) meta=64 nullable=1 is_null=1 */ ### @14=NULL /* VARSTRING(128) meta=0 nullable=1 is_null=1 */ # at 1397000 #111125 16:15:31 server id 2 end_log_pos 1397027 Xid = 79238866 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */;
通过上面的分析和这里的日志情况显示,很明显有人误删除了这条记录,导致我们公司所有员工不能登录im(登录在线的,不会使用到这条记录,这个也就是导致了我们到晚上八点多才发现这个异常)
3、检查error日志
检查这个日志,发现一个很明显的bug,这个是导致数据库重启,以及那个时间因为数据库重启导致ogg进程异常收到邮件
111125 16:15:35 InnoDB: Assertion failure in thread 1095162176 in file row/row0mysql.c line 1534 InnoDB: Failing assertion: index->type & DICT_CLUSTERED InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 111125 16:15:35 - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=1048576 max_used_connections=30 max_threads=1000 threads_connected=14 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 9234379 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0xb8bf170 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x4146d100 thread_stack 0x30000 /opt/mysql/product/5.1/bin/mysqld(my_print_stacktrace+0x2e)[0x8a74ce] /opt/mysql/product/5.1/bin/mysqld(handle_segfault+0x322)[0x5dc992] /lib64/libpthread.so.0[0x357980eb10] /lib64/libc.so.6(gsignal+0x35)[0x3578c30265] /lib64/libc.so.6(abort+0x110)[0x3578c31d10] /opt/mysql/product/5.1/bin/mysqld(row_unlock_for_mysql+0x2f2)[0x7f4a52] /opt/mysql/product/5.1/bin/mysqld(row_search_for_mysql+0x22e1)[0x802591] /opt/mysql/product/5.1/bin/mysqld(_ZN11ha_innobase10index_readEPhPKhj16ha_rkey_function+0x192)[0x7724d2] /opt/mysql/product/5.1/bin/mysqld(_ZN7handler16read_range_firstEPK12st_key_rangeS2_bb+0xbe)[0x6caa9e] /opt/mysql/product/5.1/bin/mysqld(_ZN7handler22read_multi_range_firstEPP18st_key_multi_rangeS1_jbP17st_handler_buffer+0xce)[0x6c85be] /opt/mysql/product/5.1/bin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x127)[0x6aa557] /opt/mysql/product/5.1/bin/mysqld[0x6c415d] /opt/mysql/product/5.1/bin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP11st_sql_listyyb+0x86c)[0x66fc5c] /opt/mysql/product/5.1/bin/mysqld(_Z21mysql_execute_commandP3THD+0x38bf)[0x5f03af] /opt/mysql/product/5.1/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5f25e7] /opt/mysql/product/5.1/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe63)[0x5f3453] /opt/mysql/product/5.1/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f3d16] /opt/mysql/product/5.1/bin/mysqld(handle_one_connection+0x236)[0x5e66d6] /lib64/libpthread.so.0[0x357980673d] /lib64/libc.so.6(clone+0x6d)[0x3578cd3d1d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x2aab4890fcd0 is an invalid pointer thd->thread_id=62259 thd->killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 111125 16:15:35 mysqld_safe Number of processes running now: 0 111125 16:15:35 mysqld_safe mysqld restarted InnoDB: Log scan progressed past the checkpoint lsn 0 694228728 111125 16:15:36 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 694229872 111125 16:15:36 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 5 6 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 1397027, file name /opt/mysql/mysqldata/mysqllog/mysqlbin.000149 111125 16:15:37 InnoDB: Started; log sequence number 0 694229872 111125 16:15:37 [Note] Recovering after a crash using /opt/mysql/mysqldata/mysqllog/mysqlbin 111125 16:15:37 [Note] Starting crash recovery... 111125 16:15:37 [Note] Crash recovery finished.
而且还有个巧合就是查看binlog发现,DELETE FROM a.abc where ……之后,数据库就因为这个bug自动重启了。
4、网友解释
# /opt/mysql/product/5.1/bin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP11st_sql_listyyb+0x86c)[0x66fc5c] # /opt/mysql/product/5.1/bin/mysqld(_Z21mysql_execute_commandP3THD+0x38bf)[0x5f03af] ---从这信息看是整理簇索引,导致表空间出现损坏 ---分析的情况,你的系统应该正在做一个DELETE操作,而且应该无索引可走,删除的数据量也比较大 ---可能是大量数据被缓存在innodb_buffer_pool_size中,并且其内部有创建自适应的hash索引,因删除数据而不得不重新创建, ---以及你的服务器当时IO出现瓶颈,导致一时无法响应Innodb master thread,而出现问题,并且InnoDB引擎在此方面出现过BUG ---解决版本是5.1.37之后,所以建议使用:5.1.40版本,较稳定
感谢jinguanding前辈热情帮助
http://www.itpub.net/forum.php?mod=viewthread&tid=1515971&page=1#pid18593129