OraGlance一个轻量级Oracle性能监控工具

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:

 

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 create session to pd1;

 

OraGlance是免费的,你可以安心使用它。

OraGlance is free software , use it as you like.

下载地址      Download URL : OraGlance2006

 

 

update log:

适配了12c,增加了登录界面保存,增加了托盘功能,优化了界面, 为sqlite中的表增加了索引。

 

build 2020-07-01         :  https://zcdn.askmaclean.com/OraGlance200701.zip

 

Oracle Bulk Insert Tuning Test log

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



Centos/Redhat 6 阿里云yum源

编辑如下文件/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 用户手册

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 5.1 到 MySQL 8.0(2020年5月),支持MYSQL 8.0的全新数据字典结构
  • 支持INNODB存储引擎, MYISAM支持仍在开发中
  • 支持各种故障导致的MYSQL实例崩溃下的数据恢复
  • 恢复结果为MYSQLDUMP格式的SQL文件
  • 支持对DELETE 操作的数据行恢复
  • 支持对DROP TABLE, TRUNCATE TABLE 操作的表恢复
  • 支持对DROP DataBase操作的数据库恢复
  • 支持因磁盘故障/文件系统损坏等情况下的恢复
  • 免费版支持每张表抽取1000行数据,每张表抽取100行被delete的数据
  • 基于JAVA开发,支持Windows/Redhat/Centos/Ubuntu等操作系统
  • 支持对FRM文件的恢复,将FRM文件解析为CREATE TABLE的SQL语句
  • 支持对LOB/TEXT等大对象的恢复

 

针对MySQL数据库无法打开的场景的恢复

 

此场景下用户尝试启动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...

  1. 建议首先将MYSQL实例关闭
  2. 在Windows下双击文件start_dbrecover_mysql.bat启动程序
  3. 在Linux下运行./start_dbrecover_mysql.sh启动程序
  4. 选择常规模式
  5. 选择对应的MYSQL版本,PageSize一般保持默认即可
  6. 点击选择目录,将MySQL数据文件夹所在目录加入,点击开始
  7. 在数据库树形图中选择你需要的表,双击可以浏览数据,此处最多显示1000行数据
  8. 点击导出到文件,会将该表的数据以MYSQLDUMP形式存放到SQL文件中
  9. 点击导出文件路径,可直达文件目录,其内容如下图
  10. 文件包含建表语句,和插入语句,使用mysql -uroot -p < 导出文件.sql 导入数据。

 

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)

针对drop database场景的恢复

 

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 场景的恢复

 

以下恢复步骤即适用于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,只需要在数据库树形图中查看对应的普通数据表节点即可看到数据,并导出数据

可以在界面右侧观察到该表的数据,之后的恢复与常规模式一致。

 

针对delete 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文件的恢复场景

FRM文件的读取恢复功能是DBRECOVER FOR MYSQL的免费功能。

在某些场景下我们需要从MYSQL的FRM文件中获得建表语句,只需要在软件主界面下选择添加frm文件:

 

 

双击加入的frm文件或右键查看文件,可以在右侧界面看到该FRM文件对应的建表语句,用户可复制该语句。

 

 

检查数据表上可恢复的行数

在恢复场景中,用户可以通过该功能了解某张表所能恢复的记录数量。

选中数据表,右键选择Show Table Recoverable Row Count:

OraDumpReader 0.1 Release

the software directly reads data from oracle exp & expdp result dump file . even the file is corrupted or damaged by malware/ransomware, the software can still scan the whole file , and find the good part of data.

IMP-00009 abnormal end of export file
IMP-00037 Character set marker unknown
ORA-31619: invalid dump file

OraDumpReader 是oracle导出数据恢复工具。

该软件可以直接读取oracle导出工具(包括exp和expdp)所产生的dump文件中的数据。当这些dmp文件被病毒软件加密破坏后,仍可以通过扫描整个文件,找出剩余的数据。

 

下载地址download url: https://zcdn.parnassusdata.com/OraDumpReader20200605.zip

 

 

 

 

PRM-DUL 5108 RC16

what’s new

  1. 增加了字段column长度的strict mode
  2. 增加了对Oracle 12c字段名字超过30字节的支持

 

https://zcdn.parnassusdata.com/DUL5108rc16_java.zip

 

 

 

Oracle export import传统导出导入工具与 expdp impdp数据泵必知必会

Oracle export import传统导出导入工具与 expdp impdp数据泵必知必会

 

常见使用export/import expdp/impdp的理解错误:

  1. exp/imp expdp/impdp 以为是跑在sqlplus里的
  2. export/expdp 分不清楚
  3. export和expdp兼容性搞不清楚
  4. 怕用expdp,因为不会建directory
  5. 以为expdp/impdp可以远程运行
  6. 不会用expdp的remap和exclude
  7. exp/imp 有include、exclude需求,无法满足
  8. imp 想转换表空间,但不会
  9. 搞不清楚啥是逻辑备份、物理备份。把export/expdp当成物理备份
  10. 用export/expdp当备份,但产生的dmp文件都放在和数据库一个机器和盘上,经常被一锅端

export/expdp这个 搞残很多纯开发 不懂oracle基础知识的;相反 mysqldump对大部分开发都很友好, 虽然技术上mysqldump很落后;所以技术先进没有用 ,而且容易和具体操作者脱节。

互联网公司真的不用oracle数据库吗?

互联网公司不使用oracle数据库这个印象对很多同学来说是有点深的;为了打破这个错误印象,老刘根据维基百科上的世界上最大的前20家互联网公司列表做了梳理,其中14家有使用oracle数据库技术占70%,其中5家使用了Exadata一体机占25%,其中5家为重度使用占25%。 ​​​​比较典型的互联网公司例如facebook,其实一直在使用oracle。Apple苹果因为不算互联网公司,所以不列在其中,但其也较为重度使用oracle。

 

持续更新地址: https://github.com/macleanliu/askRDBMS/blob/master/top-oracle-internet-user.md

 

 

MySQL 5.7在Ubuntu 18.04上重新初始化mysqld –initialize mysqld: Can’t create directory (Errcode: 13 – Permission denied)

注意在ubuntu上mysqld –initialize ,要配置apparmor,可以通过修改参数文件 vim /etc/apparmor.d/usr.sbin.mysqld, 也可以像我一样直接禁用mysql的apparmor profile。

否则会出现 Can’t create directory (Errcode: 13 – Permission denied) 错误

 

su -  root


systemctl stop mysql

 
mkdir -p /d01/mysql 


#这里重新初始化的目标目录是/d01/mysql


chown mysql:mysql /d01
chown mysql:mysql /d01/mysql 


#一些权限设置
mkdir /var/run/mysqld
chown mysql:mysql /var/run/mysqld
chown mysql:mysql /var/log/mysql
chown mysql:mysql /var/log/mysql/*

修改 /etc/mysql/mysql.conf.d/mysqld.cnf

 
datadir         = /d01/mysql


下面 禁用mysql的apparmor profile

aa-status |grep mysql
ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld



重启 apparmor

 systemctl restart apparmor

aa-status |grep mysql

//aa-status |grep mysql 应当无结果



mysqld --initialize --user=mysql




root@vultr:~# grep password  /var/log/mysql/error.log 
2020-04-27T07:39:28.126296Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-04-27T07:39:32.229124Z 0 [Note] Shutting down plugin 'sha256_password'
2020-04-27T07:39:32.229128Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-04-27T07:39:44.435551Z 0 [Note] Shutting down plugin 'sha256_password'
2020-04-27T07:39:44.435556Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-04-27T07:41:01.360902Z 1 [Note] A temporary password is generated for root@localhost: T#jTiKh+d9Og


临时密码T#jTiKh+d9Og

systemctl start mysql 

mysql -uroot -pT#jTiKh+d9Og
 



MySQL 5.7在Ubuntu 18.04上快速安装 不依赖于APT源脚本

wget https://www.dropbox.com/s/uvmtsd1lqpoju8l/libaio1_0.3.112-5_amd64.deb
wget https://www.dropbox.com/s/tiwi2qhhptpgljm/libmecab2_0.996-6_amd64.deb
wget https://www.dropbox.com/s/nem5b480tckdxx9/libssl1.1_1.1.1c-1ubuntu4_amd64.deb
wget https://www.dropbox.com/s/vrzyqn140cfz77k/mysql-server_5.7.29-1ubuntu18.04_amd64.deb-bundle.tar

tar -xvf mysql-server*.tar
dpkg -i libaio1_0.3.112-5_amd64.deb
dpkg -i libmecab2_0.996-6_amd64.deb 
dpkg -i libssl1.1_1.1.1c-1ubuntu4_amd64.deb
# touch /etc/mysql/my.cnf.fallback
dpkg -i mysql-common_*.deb
dpkg -i mysql-community-client*
dpkg -i mysql-client*
dpkg-preconfigure mysql-community-server_*
dpkg -i mysql-community-server*

沪ICP备14014813号

沪公网安备 31010802001379号