软件产品更新下载

DataGoGo Oracle MySQL数据同步校准工具最新版:https://www.askmaclean.com/wp-content/uploads/2021/03/DataGoGo2104.zip

OraGlance 轻量级的Oracle性能监控工具最新版:  https://zcdn.askmaclean.com/OraGlance2103.zip

MySQLGlance  轻量级的MySQL性能监控工具最新版: https://zcdn.askmaclean.com/MySQLGlance2103.zip    

DBRECOVER for Oracle恢复工具最新版: https://zcdn.parnassusdata.com/dbrecover-for-oracle2009.zip

DBRECOVER for MySQL恢复工具最新版:https://zcdn.parnassusdata.com/dbrecover-for-mysql-2010.zip

OraDumpReader Oracle dump文件恢复工具最新版:https://zcdn.parnassusdata.com/OraDumpReader200707.zip

DBRECOVER for MS SQL SERVER恢复工具最新版:https://zcdn.parnassusdata.com/dbrecover-for-sqlserver-2007.zip

 

Oracle 相比 mysql 的优势在哪里

Oracle 相比 mysql 的优势在哪里   原帖子在这里:  https://v2ex.com/t/773654?p=1

 

目前的项目用的 mysql,支撑几十亿数据没问题(分库分表),偶尔慢 sql 也能优化索引解决,至于复杂查询通过搜索引擎实现,听说 Oracle 超级贵,那 Oracle 具体有什么优势呢,哪些场景下是 oracle only 的?

看完了回复,oracle 能单表支撑几十亿数据还是很强的,因为目前因为分表键导致很多需求无法实现,只能同步一个表用另一个分表键。

我再问下,如果是同时涉及几十个字段的复杂搜索,oracle 可以支持吗,目前用的搜索引擎实现也非常贵。

有一说一,单表支撑几十亿数据,听着有点玄乎,真的假的啊

我的回复:

 

oracle 的优化器 CBO optimizer 目前应该是所有 RDBMS 里最复杂的(不说是最先进的)。MYSQL 至少在优化器上还处于比较初级的阶段,虽然 MySQL 的目标可能并不希望实现非常复杂的优化器算法。其他一些东西 例如 undo 的实现等等 可能优势并不明显, 这里就不提了。

 

举一个例子, 都不使用索引的情况下,NO INDEX ! NO INDEX | NO INDEX !

以下数据量是一样的,机器是同一台。 Oracle 11.2.0.4 MySQL 8.0.14

都没有索引的情况下:oracle 使用 0.04 秒, MySQL 等了 10 分钟也没运行完

对于简单的 SQL 而言,差别不会有那么大。对于复杂的 SQL 而言, 能明显体现出优化器的优势。

对于拔高某个技术,没有兴趣。 所以 MYSQL 死忠请勿拍。

Oracle : 

SQL> set timing on;
SQL> SELECT c.cust_city,
  2         t.calendar_quarter_desc,
  3         SUM(s.amount_sold) sales_amount
  4    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  5   WHERE s.time_id = t.time_id
  6     AND s.cust_id = c.cust_id
  7     AND s.channel_id = ch.channel_id
  8     AND c.cust_state_province = 'FL'
  9     AND ch.channel_desc = 'Direct Sales'
 10     AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
 11   GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Cypress Gardens                2000-01      3545.82
Candler                        2000-01      4166.32
Sanibel                        2000-02     17908.67
Ocala                          2000-02      7081.73
Molino                         2000-01     18765.25
Ocala                          2000-01      7146.73
Palmdale                       2000-02     25829.24
Palmdale                       2000-01     37793.44
Molino                         2000-02      17144.7
Saint Marks                    2000-01     55781.37
Noma                           2000-01     33572.55
Evinston                       2000-02     62657.21
Candler                        2000-02      6493.94
Winter Springs                 2000-02           20
Sugarloaf Key                  2000-01     12027.66
Saint Marks                    2000-02      48858.7
Blountstown                    2000-02     38052.58
Sugarloaf Key                  2000-02      9659.44
Cypress Gardens                2000-02      4928.93
Evinston                       2000-01     53509.69
Blountstown                    2000-01      27024.7
Sanibel                        2000-01     15870.34
Winter Springs                 2000-01        31.46
Noma                           2000-02     23903.58

已选择 24 行。

已用时间:  00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 1865285285

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   607 | 46132 |   955   (2)| 00:00:12 |       |       |
|   1 |  HASH GROUP BY                 |           |   607 | 46132 |   955   (2)| 00:00:12 |       |       |
|*  2 |   HASH JOIN                    |           |  2337 |   173K|   954   (2)| 00:00:12 |       |       |
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |   274 |  4384 |    18   (0)| 00:00:01 |       |       |
|*  4 |     TABLE ACCESS FULL          | TIMES     |   274 |  4384 |    18   (0)| 00:00:01 |       |       |
|*  5 |    HASH JOIN                   |           | 12456 |   729K|   936   (2)| 00:00:12 |       |       |
|   6 |     MERGE JOIN CARTESIAN       |           |   383 | 14937 |   408   (1)| 00:00:05 |       |       |
|*  7 |      TABLE ACCESS FULL         | CHANNELS  |     1 |    13 |     3   (0)| 00:00:01 |       |       |
|   8 |      BUFFER SORT               |           |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|*  9 |       TABLE ACCESS FULL        | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|  10 |     PARTITION RANGE JOIN-FILTER|           |   918K|    18M|   526   (2)| 00:00:07 |:BF0000|:BF0000|
|  11 |      TABLE ACCESS FULL         | SALES     |   918K|    18M|   526   (2)| 00:00:07 |:BF0000|:BF0000|
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."TIME_ID"="T"."TIME_ID")
   4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
              "T"."CALENDAR_QUARTER_DESC"='2000-02')
   5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
   9 - filter("C"."CUST_STATE_PROVINCE"='FL')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1726  consistent gets
          0  physical reads
          0  redo size
       1495  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         24  rows processed


SQL> select count(*) from sh.sales;

  COUNT(*)
----------
    918843

SQL> select * From v$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

MySQL :

mysql> SELECT c.cust_city,
    ->        t.calendar_quarter_desc,
    ->        SUM(s.amount_sold) sales_amount
    ->   FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
    ->  WHERE s.time_id = t.time_id
    ->    AND s.cust_id = c.cust_id
    ->    AND s.channel_id = ch.channel_id
    ->    AND c.cust_state_province = 'FL'
    ->    AND ch.channel_desc = 'Direct Sales'
    ->    AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
    ->  GROUP BY c.cust_city, t.calendar_quarter_desc;


+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ch    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |      5 |    20.00 | Using where; Using temporary                       |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   1804 |    30.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  55065 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 914584 |     0.10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+



mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.14    |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from sh.sales;
+----------+
| count(*) |
+----------+
|   918843 |
+----------+
1 row in set (0.96 sec)

使用DataGoGo 全量同步/初始化Initial Load Oracle数据

现有ZHS和AL2套数据库,需要将ZHS下的EAS Schema数据初始化复制到AL数据库。

准备工作: 在AL数据库中对应创建EAS用户,并赋权:

 

 

 

set ORACLE_SID=AL

sqlplus / as sysdba

create user eas identified by eas;
grant connect,resource to eas;

 

 

 

打开DataGoGo.exe 点击开始

 

点击新建:

 

输入数据源ZHS实例的连接信息:

点击测试连接:

 

点击保存后, 再次新建目标数据库连接:

 

 

选中源数据源,点击下一步:

 

 

选中目标数据源,点击连接

 

连接后,会在左右2侧显示对象树形图:

 

选中用户名,右键点中,点击Transfer:

 

出现Schema 传输界面,默认选中了所有表

 

点击Start后开始Schema用户级别数据传输,传输过程中 会显示传输进度:

 

 

在有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下,DataGoGo的单线程传输速度约为4500条/秒。

在没有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下,DataGoGo的单线程传输速度约为30000条/秒。

点击Close后,可以在右侧树形图看到传输过来的表:

 

 

 

按照重要程度划分数据库级别

按照重要程度划分数据库级别
级别 D C B A S
影响面 小于10人 10-1000人 1000-100000人 100000-1000000人 1000000人以上
业务类型举例 测试/开发系统,小型OA 记账软件等 中型OA ERP 财务软件等 中大型ERP MES HRM ,大型医院HIS 电信CRM 银行BANKING等 大型公共应用 如12306等
灾难救援价格 500-5000 5千到三万 3万到10万 10万到50万 50万以上
一般的配套设施 几乎无任何有效备份 少量磁盘上逻辑备份 可能有物理备份+其他备份形式 物理/逻辑备份+DataGuard,OGG等物理/逻辑灾备;存储级别冗余等+多数据中心冗余 都有

DataGoGo Oracle/MySQL数据库复制同步校准软件

DataGoGo Oracle/MySQL数据库复制同步校准软件

DataGoGo Rdbms Oracle MySQL Table/Schema Sync Compare Repair

最新版:

 

DataGoGo(DGG) is a single application that allows you to tranfer data between Oracle and MySQL.
It will also support Microsoft SQL Server in future.

It supports below directionals:

Oracle To Oracle
Oracle To MySQL
MySQL To MySQL
MySQL To Oracle

特性:

快速比较同步Oracle表数据
快速比较同步MySQL表数据
支持异构传输 Oracle <=> MySQL , 未来将支持 SQL Server
将支持快速数据表校准修复
将支持索引、视图、触发器等其他类型对象

收费功能,以下功能需要购买企业版后方能使用:

LOB字段的传输和比较
并行数据传输

 

DataGoGo210413

 

 

 

Download  https://zcdn.askmaclean.com/DataGoGo2103.zip

DataGoGo2104

 

 

 

 

Oracle 12.2 使用datagurd技术极短停机时间内快速迁移数据库方案步骤

=================================> 前期配置应在割接前10天完成 <=================================

 

1、在新服务器上配置必要的os参数,包含大内存页等

2、在新服务器上12.2.0.1 GI和RDBMS,安装2010补丁:p31741641_122010和p31750094_122010

3、创建一个与oldorcl库参数配置匹配的空数据库

4、复制密码文件到新服务器:

[oracle@]$ srvctl config database -d as19_fra1gh | grep ‘Password file’
Password file: +DATAC1//PASSWORD/passwd <– this file is what needs to be copied to /tmp and scp’d to the standby (result may differ)

[oracle@]$ export ORACLE_SID=+ASM1
[oracle@]$ export ORACLE_HOME=/u01/app/12.2.0.1/grid
[oracle@]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@]$asmcmd cp +DATAC1//PASSWORD/passwd /tmp/passwd. copying +DATAC1//PASSWORD/passwd -> /tmp/passwd.

[oracle@]$ scp /tmp/orapw1 oracle@:/tmp/orapw

5、配置tnsnames.ora文件
包含 oldorcl 条目 和neworcl条目

oldorcl 老的源库
neworcl 新的目标库

6、在新服务器上恢复控制文件

startup nomount;

restore standby controlfile from service oldorcl;
alter database mount;

7、配置rman参数

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

8、 全量恢复数据库 ;也可以基于传输备份文件实现,影响大同小异

restore database from service oldorcl;

9、 增加standby logfile:

SQL> alter database add standby logfile thread 1
group 7 (‘+DATA’) size 4294967296,
group 8 (‘+DATA’) size 4294967296,
group 9 (‘+DATA’) size 4294967296;

SQL> alter database add standby logfile thread 2
group 11 (‘+DATA’) size 4294967296,
group 12 (‘+DATA’) size 4294967296,
group 13 (‘+DATA’) size 4294967296;

10、 配置 归档传输 log_archive_dest_2 (基于归档同步) log_archive_dest_2=’service=neworcl lgwr async’

如果 归档日志过多 ,那么可以 定期执行recover database from service oldorcl; (基于增量备份实现同步) ,而不打开归档传输

11、 将neworcl数据库启动到只读模式,并打开日志应用

alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

=================================> 割接窗口开始 <=================================

 

必须打开归档传输log_archive_dest_2=’service=neworcl lgwr async’

 

停止应用

 

停止数据库实例 shutdown immediate; ==》 约5分钟

 

将oldorcl数据库启动到 限制模式 ==》 约5分钟

 

startup restrict;

 

创建一条新记录 ,并确认传输到新库 ==> 约1分钟

create table test1(t1 date);
insert into test1 values(sysdate);
commit;
alter session set nls_date_format=’YYYY-MM-DD hh24:mi:ss’;
alter system checkpoint;

alter system switch logfile;

alter system switch logfile;
alter system switch logfile;
select * from test1;

在新orcl确认
select * from test1;

将neworcl数据库启动到打开模式 ==》10分钟

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

alter database open;

检查数据

停数据库实例 、监听、SCAN ip资源 ==》10分钟

切换SCAN IP ==>10分钟

启动所有资源 ,启动neworcl 2节点数据库实例 ==》15分钟

去掉 log_archive_dest_2

=================================> 割接窗口结束 <=================================

自己写的RMAN备份脚本

一套自己写的rman备份脚本,包含全量、增量和归档日志备份,备份策略为每7天一个全备份,2天一个增量备份和每8小时一次归档备份,并删除一个月前的备份和归档。

 

 



crontab 



0 0,8,16 * * * sh /home/oracle/backup_script/backup-archive.sh >> /home/oracle/backup_script/backup-archive.log
0 0 7,14,21,28 * * sh /home/oracle/backup_script/backup-full.sh >> /home/oracle/backup_script/backup-full.log
0 0 2,4,6,8,10,12,16,18,20,22,24,26 * * sh /home/oracle/backup_script/backup-inc.sh >> /home/oracle/backup_script/backup-inc.log




FULL 

sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET incremental level 0  database tag ORCL_FULL format '/oracle_bak/oradir/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup as compressed backupset tag ORCL_ARCHIVE format '/oracle_bak/oradir/%d_%T_%s_%p_ARCHIVE' archivelog all not backed up delete all input ;
delete backup of archivelog until time='sysdate-30';
backup tag ORCL_CONTROL current controlfile format '/oracle_bak/oradir/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}




INC


sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET incremental level 1  database tag ORCL_INC1 format '/oracle_bak/oradir/%d_%T_%s_%p_INC1' ;
sql 'alter system archive log current';
backup tag ORCL_CONTROL current controlfile format '/oracle_bak/oradir/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}



ARCHIVE 

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
allocate channel c1 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
allocate channel c2 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
allocate channel c3 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
allocate channel c4 type disk MAXOPENFILES=1 parms ' BLKSIZE=1048576 ';
sql 'alter system archive log current';
backup as compressed backupset tag ORCL_ARCHIVE format '/oracle_bak/oradir/%d_%T_%s_%p_ARCHIVE' archivelog all not backed up delete all input ;
delete backup of archivelog until time='sysdate-30';
backup tag ORCL_CONTROL current controlfile format '/oracle_bak/oradir/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

看了TENET电影做个逆向的Oracle数据库周边

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 各种删除操作对空间返还的说明

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

 

 

 

Oracle 各种删除操作对空间返还的说明

python计算sigma


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

沪ICP备14014813号-2

沪公网安备 31010802001379号