Oracle MySQL 数据恢复 数据库讨论QQ群号: 171092051
| 10:00 | Oracle instance shutdown complete |
| 10:01 | database closed |
| 10:02 | shutdown issued by user |
| 10:03 | SCN 99999 |
| 10:04 | SCN 99000 |
| 10:05 | SQL result from client back to server |
| 10:06 | SQL fetch back to disk |
| 10:07 | SQL cursor back to parse |
| 10:08 | SQL optimize=>semantic parse=>syntax parse |
| 10:09 | user issue SQL: ;pme morf * tceles |
| 10:10+NN | database opened |
| 10:10+NN | redo scan , rolling forward |
| 10:10+NN | database mounted |
| 10:10+NN | instance nomount start |
| 10:10+NN | user issue in SQLPLUS: ;putrats |
| Oracle 各种删除操作对空间返还的说明 | |||||||
| 操作 | 表空间是否回收空间？ | 文件系统或ASM是否回收空间？ | 是否造成表上的碎片？ | 在本地管理表空间(LMT 9i以后)是否造成表空间碎片？ | 在字典管理表空间(DMT 9i以前)是否造成表空间碎片？注意现在的oracle版本不太可能用DMT | 是否造成索引碎片？ | 有心理问题，或者为应付领导，一定要对付对付不存在的碎片怎么办？ |
| DELETE SQL | 否 | 否 | 否，空间可以被该表重用。可以称之为高水位，但谈不上碎片 | 不适用 | 不适用 | 否 | 对表可以shrink space;对索引可以coalesce操作；对于大表而言IO和redo会很多，耗时也可能长 |
| DROP TABLE | 是的；视乎recyclebin参数是否进入回收站；但空间都可以被表空间重用 | 否 | 都没表了 | 本地管理表空间的extent是统一大小或系统自动分配大小，不存在表空间碎片 | 可能导致碎片(alter tablespace coalesce适用场景） | 索引都没了 | 不适用 |
| TRUNCATE TABLE | 默认是的 | 否 | 否 | 本地管理表空间的extent是统一大小或系统自动分配大小，不存在表空间碎片 | 可能导致碎片(alter tablespace coalesce适用场景） | 否 | 不适用 |
| 注：Oracle除非手动resize datafile，否则一般不会自动返回空间给文件系统或ASM | |||||||
from functools import reduce
def sigma(i,j):
return reduce(lambda a, x: a + x, [0]+list(range(i,j+1)))
print(sigma(1,10000))
MySQLGlance是免费的，你可以安心使用它。
MySQLGlance is free software , use it as you like.
仅需以下权限：
grant select on performance_schema.* to $USERNAME; grant select,execute on sys.* to $USERNAME; grant process on *.* to $USERNAME; flush privileges;
症状为使用mobaxterm 登录操作系统后，DBRECOVER for MySQL 在Linux 7上启动失败，GUI无法启动，日志报错为：
[root@ora7 dbrecover-for-mysql-2006]# less dbrecover-for-mysql.log.txt
Graphics Device initialization failed for : es2, sw
Error initializing QuantumRenderer: no suitable pipeline found
java.lang.RuntimeException: java.lang.RuntimeException: Error initializing QuantumRenderer: no suitable pipeline found
at com.sun.javafx.tk.quantum.QuantumRenderer.getInstance(QuantumRenderer.java:280)
at com.sun.javafx.tk.quantum.QuantumToolkit.init(QuantumToolkit.java:221)
at com.sun.javafx.tk.Toolkit.getToolkit(Toolkit.java:248)
at com.sun.javafx.application.PlatformImpl.startup(PlatformImpl.java:209)
at com.sun.javafx.application.LauncherImpl.startToolkit(LauncherImpl.java:675)
at com.sun.javafx.application.LauncherImpl.launchApplicationWithArgs(LauncherImpl.java:337)
at com.sun.javafx.application.LauncherImpl.launchApplication(LauncherImpl.java:328)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:767)
Caused by: java.lang.RuntimeException: Error initializing QuantumRenderer: no suitable pipeline found
at com.sun.javafx.tk.quantum.QuantumRenderer$PipelineRunnable.init(QuantumRenderer.java:94)
at com.sun.javafx.tk.quantum.QuantumRenderer$PipelineRunnable.run(QuantumRenderer.java:124)
at java.lang.Thread.run(Thread.java:748)
Exception in thread "main" java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:767)
Caused by: java.lang.RuntimeException: No toolkit found
at com.sun.javafx.tk.Toolkit.getToolkit(Toolkit.java:260)
at com.sun.javafx.application.PlatformImpl.startup(PlatformImpl.java:209)
at com.sun.javafx.application.LauncherImpl.startToolkit(LauncherImpl.java:675)
at com.sun.javafx.application.LauncherImpl.launchApplicationWithArgs(LauncherImpl.java:337)
at com.sun.javafx.application.LauncherImpl.launchApplication(LauncherImpl.java:328)
... 5 more
这是因为gtk 2没有安装导致的；虽然没装gtk2，但是可以启动xclock。
解决方法为，安装gtk2和libXtst.x86_64:
yum install gtk2 libXtst.x86_64 xclock xorg-x11-xauth.x86_64 unzip Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com base | 3.6 kB 00:00:00 extras | 2.9 kB 00:00:00 updates | 2.9 kB 00:00:00 (1/2): extras/7/x86_64/primary_db | 205 kB 00:00:00 (2/2): updates/7/x86_64/primary_db | 3.0 MB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package gtk2.x86_64 0:2.24.31-1.el7 will be installed --> Finished Dependency Resolution
DBRECOVER for MSSQL SQL Server是微软SQL SERVER数据库恢复软件。
支持如下场景：
OraGlance是免费的，你可以安心使用它。
OraGlance is free software , use it as you like.
| OraGlance | Enterprise Manager | 其他第三方监控软件 | |
| 价格 | 完全免费 | 费用包含在db license中 | 基于license或订阅收费 |
| 性能指标 | 追求精简 | 12c以后的express版精简，12c以前较为全面 | 追求全面 |
| 性能负载 | 极低，内存小于50MB，cpu在1%左右 | 基于java，内存和cpu使用略高 | B/S架构情况下普遍负载略高 |
| 响应速度 | 极快 | 正常情况下较快 | 正常情况下较快 |
| 部署情况 | 无需部署，一键使用 | 需要少量部署维护 | 一般需要单独部署 |
| 是否需要AGENT | 完全不需要 | cloud control需要安装agent | 可能需要 |
| 是否在库内创建对象和写数据 | 完全不创建 | 原生存在部分对象，例如sysman | 大部分需要 |
| 是否最小权限 | 只需要几个视图的查询权限 | 需要比较高的权限 | 可能需要读写权限 |
| 是否往需要访问外网 | 完全不需要 | 常规使用下完全不需要 | 可能需要访问外网获得完整功能 |
| 是否可以回溯监控历史 | 可以 | 部分可以 | 可能可以 |
| 当数据库hang时是否能监控 | 只要连接未被中断，除非极端情况，否则一直可监控性能；例如AWR快照已经无法写入的情况，则仍可以收集到丢失的AWR数据 | 可能完全卡死 | 可能完全卡死 |
同时支持多开程序，可以在同一台pc上多开监控多个数据库：
–建议重写该SQL
–建议收集对应的统计信息
–给出SQL Profile以改善执行计划
–建议添加对应的索引
–无建议
OraGlance是一个轻量级的图形化Oracle性能监控工具。由诗檀软件开发，它致力于提供简单有效的几个指标来监控Oracle数据库，而不提供过多的指标。
它是绿色的，非植入式的；你可以直接运行它，而基本不需要做任何准备工作。
不需要在Oracle数据库内创建任何对象，其只需要以下几个查询权限:
OraGlance is a lightweight oracle performance monitor tool , developed by parnassusdata.com . It will only focus on most important metric .
you can easily run it without any prerequisite. It will ask for below permission:
supports oracle version: 11gR2 12c
grant create session to pd1; grant select on gv_$active_session_history to pd1; grant select on gv_$SQL to pd1; grant select on gv_$SQL_MONITOR to pd1; grant select on gv_$SQLSTATS to pd1; grant select on v_$database to pd1; grant select on gv_$instance to pd1; grant select on gv_$statname to pd1; grant select on gv_$sysstat to pd1; grant select on gv_$osstat to pd1; grant select on gv_$dlm_misc to pd1; grant select on gv_$session_blockers to pd1; grant select on dba_hist_sqltext to pd1; grant select on dba_hist_sqlstat to pd1; grant select on dba_hist_snapshot to pd1; grant select on dba_advisor_sqlstats to pd1; grant select on dba_sqlset_statements to pd1; 如果要运行SQL Tuning Advisor 则需要授予dba权限 grant dba to pd1;
update log:
适配了12c，增加了登录界面保存，增加了托盘功能，优化了界面， 为sqlite中的表增加了索引。
build 2020-07-01 : https://zcdn.askmaclean.com/OraGlance200701.zip
2020-07-08:
build 2020-07-08: https://zcdn.parnassusdata.com/OraGlance200708.zip
2020-07-09：
现在登陆密码会以密文形式存放了
SQL> insert into abc select * from dba_objects;
insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
86977 rows created.
Elapsed: 00:00:00.33
SQL>
86977 rows created.
Elapsed: 00:00:00.16
SQL>
Commit complete.
Elapsed: 00:00:00.01
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
173954 rows created.
Elapsed: 00:00:00.26
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
347908 rows created.
Elapsed: 00:00:00.40
SQL>
Commit complete.
Elapsed: 00:00:00.01
SQL>
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
695816 rows created.
Elapsed: 00:00:00.87
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
1391632 rows created.
Elapsed: 00:00:01.40
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
2783264 rows created.
Elapsed: 00:00:02.63
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
5566528 rows created.
Elapsed: 00:00:05.40
SQL>
Commit complete.
Elapsed: 00:00:00.01
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
11133056 rows created.
Elapsed: 00:00:10.87
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
22266112 rows created.
Elapsed: 00:00:19.88
SQL>
Commit complete.
Elapsed: 00:00:00.02
SQL>
SQL>
SQL>
SQL> desc dba_objects;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> set linesize 300 pagesize 2000
SQL>
SQL> select count(*) from abc;
COUNT(*)
----------
44532224
Elapsed: 00:00:04.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1045519631
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 172K (1)| 00:34:34 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| ABC | 44M| 172K (1)| 00:34:34 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
635793 consistent gets
635777 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ parallel */ count(*) from abc;
COUNT(*)
----------
44532224
Elapsed: 00:00:03.19
Execution Plan
----------------------------------------------------------
Plan hash value: 2285262752
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31976 (1)| 00:06:24 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 44M| 31976 (1)| 00:06:24 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| ABC | 44M| 31976 (1)| 00:06:24 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
636281 consistent gets
635777 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
22266112 rows inserted in 19.88s. PC server virtual box , about 150MB/s disk space written. physical read 635777 blocks( 4.85g) in 3.19s, 1.52GB/s.
[oracle@ocp ~]$ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 158 model name : Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz stepping : 9 cpu MHz : 3599.883 cache size : 8192 KB physical id : 0 siblings : 3 core id : 0 cpu cores : 3 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 22 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed bogomips : 7199.76 clflush size : 64 cache_alignment : 64 address sizes : 39 bits physical, 48 bits virtual power management: processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 158 model name : Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz stepping : 9 cpu MHz : 3599.883 cache size : 8192 KB physical id : 0 siblings : 3 core id : 1 cpu cores : 3 apicid : 1 initial apicid : 1 fpu : yes fpu_exception : yes cpuid level : 22 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed bogomips : 7199.76 clflush size : 64 cache_alignment : 64 address sizes : 39 bits physical, 48 bits virtual power management: processor : 2 vendor_id : GenuineIntel cpu family : 6 model : 158 model name : Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz stepping : 9 cpu MHz : 3599.883 cache size : 8192 KB physical id : 0 siblings : 3 core id : 2 cpu cores : 3 apicid : 2 initial apicid : 2 fpu : yes fpu_exception : yes cpuid level : 22 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed bogomips : 7199.76 clflush size : 64 cache_alignment : 64 address sizes : 39 bits physical, 48 bits virtual power management: [oracle@ocp ~]$ cat /proc/meminfo MemTotal: 15238052 kB MemFree: 1185404 kB Buffers: 36968 kB Cached: 13501972 kB SwapCached: 0 kB Active: 9674364 kB Inactive: 4176152 kB Active(anon): 9564472 kB Inactive(anon): 1782020 kB Active(file): 109892 kB Inactive(file): 2394132 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 4128764 kB SwapFree: 4128764 kB Dirty: 4 kB Writeback: 0 kB AnonPages: 311584 kB Mapped: 439612 kB Shmem: 11034924 kB Slab: 67436 kB SReclaimable: 48416 kB SUnreclaim: 19020 kB KernelStack: 2192 kB PageTables: 28060 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 11747788 kB Committed_AS: 12092868 kB VmallocTotal: 34359738367 kB VmallocUsed: 107616 kB VmallocChunk: 34359622135 kB HardwareCorrupted: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB DirectMap4k: 13248 kB DirectMap2M: 15548416 kB
编辑如下文件/etc/yum.repos.d/CentOS-Base.repo
cat /etc/yum.repos.d/CentOS-Base.repo
# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client. You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the
# remarked out baseurl= line instead.
#
#
[base]
name=CentOS-6 - Base - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/6/os/$basearch/
http://mirrors.aliyuncs.com/centos/6/os/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/6/os/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
#released updates
[updates]
name=CentOS-6 - Updates - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/6/updates/$basearch/
http://mirrors.aliyuncs.com/centos/6/updates/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/6/updates/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
#additional packages that may be useful
[extras]
name=CentOS-6 - Extras - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/6/extras/$basearch/
http://mirrors.aliyuncs.com/centos/6/extras/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/6/extras/$basearch/
gpgcheck=1
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-6 - Plus - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/6/centosplus/$basearch/
http://mirrors.aliyuncs.com/centos/6/centosplus/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/6/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
#contrib - packages by Centos Users
[contrib]
name=CentOS-6 - Contrib - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos/6/contrib/$basearch/
http://mirrors.aliyuncs.com/centos/6/contrib/$basearch/
http://mirrors.cloud.aliyuncs.com/centos/6/contrib/$basearch/
gpgcheck=1
enabled=0
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
yum clean all
yum makecache
DBRECOVER FOR MYSQL是一个MySQL数据库(Innodb)恢复工具，该工具软件可以在MySQL没有备份的情况下，针对实例崩溃，Inoodb字典损坏无法启动数据库实例，DROP DATABASE，DROP TABLE，TRUNCATE TABLE,DELETE TABLE，磁盘/文件系统损坏等场景恢复数据库数据。
下载连接：https://zcdn.parnassusdata.com/dbrecover-for-mysql-2006.zip
视频教程：
特性：
此场景下用户尝试启动MySQL实例，但MySQL实例可能因数据库本身损坏而CRASH进程崩溃。
相关报错可能如下：
InnoDB: Waiting for the background threads to start
InnoDB: Error: tablespace size stored in header is 3712 pages, but
InnoDB: the sum of data file sizes is only 3072 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.
InnoDB: Assertion failure in thread 3876 in file ha_innodb.cc line 17352
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.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Assertion failure in thread 140154354255616 in file trx0purge.c line 848
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery
[ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace database/table uses space ID: 882 at filepath
[Note] InnoDB: Starting crash recovery.
[ERROR] InnoDB: Tablespace 11904 was not found at ./example_db/example1.ibd.
[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
[ERROR] InnoDB: Tablespace 11905 was not found at ./example_db/example2.ibd.
[ERROR] InnoDB: Cannot continue operation.
InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1
InnoDB: Database page corruption on disk or a failed file read of page 660. A table cannot be properly queried with the SELECT statement - additional possible output: MariaDB [psa]> select * from db_example.misc;
ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
mysql -uroot -p < employees.sql
Enter password: ********
mysql -uroot -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.14 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use employees;
Database changed
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> drop database employees;
Query OK, 14 rows affected (0.16 sec)
#sync
#sync
启动DBRECOVER FOR MYSQL软件，选择DROP DATABASE恢复场景：
选择正确的MYSQL数据库版本：
选择SELECT DIRECTORY，输入@@datadir所在目录路径， 点击start
软件会扫描目录下的ibdata1或mysql.ibd并扫描@datadir所在磁盘：
之后点击dropped databases节点，可以找到相关被drop删除的数据库子节点，并恢复其中的表数据：
以下恢复步骤即适用于drop table 也适用于 truncate table
mysql> select count(*) from employees.employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.09 sec)
mysql> select @@datadir;
+-----------+
| @@datadir |
+-----------+
| /m01/ |
+-----------+
1 row in set (0.00 sec)
mysql> drop table employees.employees;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table employees.employees;
Query OK, 0 rows affected (0.02 sec)
mysql> ^DBye
#sync
#sync
启动DBRECOVER FOR MYSQL软件，选择DROP TABLE恢复场景：
选择正确的MYSQL数据库版本：
选择SELECT DIRECTORY，输入@@datadir所在目录路径， 点击start
软件会扫描目录下的ibdata1或mysql.ibd并扫描@datadir所在磁盘：
!!!注意这里一定要输入原始@@datadir所在目录；不能是出现问题后拷贝原文件的一个目录。因为软件需要扫描@@datadir所在文件系统所在挂载点才能找到被drop的数据。
之后点击对应数据库下的dropped tables节点，看是否存在对应被drop的表:
针对truncate table，只需要在数据库树形图中查看对应的普通数据表节点即可看到数据，并导出数据
可以在界面右侧观察到该表的数据，之后的恢复与常规模式一致。
mysql> use employees;
Database changed
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> delete from employees;
mysql> flush table employees with read lock;
Query OK, 0 rows affected (0.01 sec)
例如以上误删除表上记录的场景，我们可以用dbrecover for mysql的undelete功能恢复。
找到该表对应的ibd文件，例如 上例中employees表对应employees.ibd。
启动dbrecover for mysql程序，在开始菜单中选择添加ibd文件，并加入该ibd文件。
右击ibd文件选择扫描，扫描后出现对应的page文件，右键选择输入建表的SQL语句。
建表语句可以通过show create table 命令在mysql中获得：
mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
点击确定后，界面右侧出现表信息，点击deleted数据：
点击导出到文件，即可将delete掉的数据恢复为MYSQLDUMP形式的INSERT语句
FRM文件的读取恢复功能是DBRECOVER FOR MYSQL的免费功能。
在某些场景下我们需要从MYSQL的FRM文件中获得建表语句，只需要在软件主界面下选择添加frm文件：
双击加入的frm文件或右键查看文件，可以在右侧界面看到该FRM文件对应的建表语句，用户可复制该语句。
在恢复场景中，用户可以通过该功能了解某张表所能恢复的记录数量。
选中数据表，右键选择Show Table Recoverable Row Count:
