1. 達(dá)夢(mèng)數(shù)據(jù)庫(kù)的國(guó)產(chǎn)化背景與核心定位在信息技術(shù)應(yīng)用創(chuàng)新的大背景下數(shù)據(jù)庫(kù)作為信息系統(tǒng)的核心組件其自主可控的重要性日益凸顯。達(dá)夢(mèng)數(shù)據(jù)庫(kù)DM Database作為國(guó)內(nèi)首批自主研發(fā)的大型通用關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)經(jīng)過近四十年的技術(shù)積累已形成覆蓋OLTP、OLAP、HTAP全場(chǎng)景的產(chǎn)品矩陣。與Oracle、SQL Server等國(guó)際商業(yè)數(shù)據(jù)庫(kù)相比達(dá)夢(mèng)在語法兼容性上實(shí)現(xiàn)了超過90%的SQL標(biāo)準(zhǔn)兼容度同時(shí)針對(duì)國(guó)產(chǎn)CPU如龍芯、飛騰和操作系統(tǒng)麒麟、統(tǒng)信UOS進(jìn)行了深度優(yōu)化。達(dá)夢(mèng)數(shù)據(jù)庫(kù)7.0版本在TPC-C基準(zhǔn)測(cè)試中達(dá)到百萬級(jí)tpmC的性能指標(biāo)支持單機(jī)部署、共享存儲(chǔ)集群、分布式集群等多種架構(gòu)。其核心優(yōu)勢(shì)體現(xiàn)在存儲(chǔ)引擎采用行列混合存儲(chǔ)模式同時(shí)優(yōu)化了B樹索引結(jié)構(gòu)支持表空間級(jí)加密和字段級(jí)透明加密TDE提供基于REDO日志的邏輯復(fù)制和基于存儲(chǔ)層的物理復(fù)制兼容Oracle的PL/SQL語法和SQL Server的T-SQL特性提示達(dá)夢(mèng)安裝包分為開發(fā)版和企業(yè)版開發(fā)版支持Windows/Linux環(huán)境但限制并發(fā)連接數(shù)企業(yè)版需按CPU核心數(shù)授權(quán)。2. 達(dá)夢(mèng)數(shù)據(jù)庫(kù)的安裝與配置實(shí)踐2.1 Windows環(huán)境安裝詳解以DM8開發(fā)版在Windows Server 2019上的安裝為例從官網(wǎng)獲取ISO鏡像約1.2GB掛載后運(yùn)行setup.exe選擇典型安裝時(shí)需注意安裝路徑避免包含中文和空格默認(rèn)端口5236建議修改為非標(biāo)準(zhǔn)端口字符集選擇UTF-8代碼頁(yè)65001安裝完成后需手動(dòng)啟動(dòng)DmService服務(wù)驗(yàn)證安裝成功的命令disql SYSDBA/SYSDBAlocalhost:5236 select * from v$version;常見安裝報(bào)錯(cuò)處理error creating bean with name jdbcmappingcontext通常因JDBC驅(qū)動(dòng)版本不匹配導(dǎo)致需使用達(dá)夢(mèng)官方提供的dm-connector-java建議8.0以上版本端口沖突通過netstat -ano確認(rèn)端口占用情況修改dm.ini中的PORT參數(shù)2.2 Linux環(huán)境一鍵部署方案對(duì)于生產(chǎn)環(huán)境推薦使用達(dá)夢(mèng)提供的自動(dòng)化安裝腳本# 下載安裝包和校驗(yàn)文件 wget https://download.dameng.com/DM8/202301/dm8_20230117_x86_rh6_64.zip wget https://download.dameng.com/DM8/202301/dm8_20230117_x86_rh6_64.zip.sha256 # 校驗(yàn)完整性 sha256sum -c dm8_20230117_x86_rh6_64.zip.sha256 # 解壓并執(zhí)行安裝 unzip dm8_20230117_x86_rh6_64.zip cd dmdbms/bin ./DMInstall.bin -i安裝過程中需交互式配置選擇安裝類型典型/自定義指定數(shù)據(jù)文件存放路徑建議獨(dú)立磁盤設(shè)置SYSDBA初始密碼需包含大小寫字母和數(shù)字3. 達(dá)夢(mèng)數(shù)據(jù)庫(kù)的核心操作與遷移實(shí)踐3.1 基礎(chǔ)對(duì)象管理創(chuàng)建學(xué)生表的示例CREATE TABLE student ( stu_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender CHAR(1) CHECK(gender IN (M,F)), birth DATE, score DECIMAL(5,2) ) TABLESPACE MAIN; -- 創(chuàng)建只讀用戶 CREATE USER reader IDENTIFIED BY Reader123; GRANT SELECT ANY TABLE TO reader;達(dá)夢(mèng)特有的管理命令查看表空間使用率SELECT * FROM DBA_TABLESPACES監(jiān)控會(huì)話SELECT * FROM V$SESSIONS分析SQL性能EXPLAIN SELECT * FROM student WHERE score903.2 異構(gòu)數(shù)據(jù)庫(kù)遷移方案從Oracle到達(dá)夢(mèng)的遷移流程使用達(dá)夢(mèng)遷移工具DTS創(chuàng)建遷移項(xiàng)目配置源庫(kù)Oracle連接參數(shù)服務(wù)名/SID字符集建議AL32UTF8指定要遷移的schema數(shù)據(jù)類型映射調(diào)整Oracle的NUMBER→達(dá)夢(mèng)的DECIMALVARCHAR2→VARCHARCLOB→TEXT執(zhí)行結(jié)構(gòu)遷移后驗(yàn)證對(duì)比表數(shù)量、字段類型檢查約束和索引數(shù)據(jù)遷移階段建議分批提交每1000行關(guān)閉觸發(fā)器禁用外鍵約束注意Oracle的ROWNUM偽列需替換為達(dá)夢(mèng)的ROW_NUMBER()函數(shù)序列對(duì)象需重新創(chuàng)建。4. 達(dá)夢(mèng)數(shù)據(jù)庫(kù)的性能優(yōu)化與安全實(shí)踐4.1 SQL優(yōu)化方法論慢SQL排查步驟開啟監(jiān)控ALTER SYSTEM SET ENABLE_MONITOR1;查詢耗時(shí)SQLSELECT TOP 10 SQL_TEXT, EXEC_TIME, LOGIC_READS FROM V$SQL_AREA ORDER BY EXEC_TIME DESC;優(yōu)化措施避免SELECT *只查詢必要字段對(duì)JOIN字段建立組合索引使用/* INDEX(table_name index_name) */提示強(qiáng)制索引并行查詢配置示例-- 設(shè)置并行度 ALTER SESSION SET PARALLEL_DEGREE4; -- 并行查詢示例 SELECT /* PARALLEL(8) */ dept_name, AVG(salary) FROM employee GROUP BY dept_name;4.2 安全防護(hù)要點(diǎn)針對(duì)SQL注入的防護(hù)策略應(yīng)用層防護(hù)使用預(yù)編譯語句PreparedStatement禁止拼接SQL特別是MyBatis中避免使用${}數(shù)據(jù)庫(kù)層防護(hù)-- 啟用SQL防火墻 ALTER SYSTEM SET SQL_FIREWALL_ENABLE1; -- 配置敏感操作審計(jì) CREATE AUDIT POLICY audit_ddl ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE;漏洞掃描應(yīng)對(duì)對(duì)奇安信等掃描器報(bào)告的誤報(bào)可通過重寫SQL或添加/* DAMENG_NO_SQL_INJECT */注釋排除加密通信配置生成SSL證書openssl req -newkey rsa:2048 -nodes -keyout dm.key -x509 -days 365 -out dm.crt修改dm.iniSSL_ENABLE 1 SSL_KEY_PATH /opt/dmdbms/conf/dm.key SSL_CERT_PATH /opt/dmdbms/conf/dm.crt5. 高可用架構(gòu)設(shè)計(jì)與容器化部署5.1 主備集群搭建達(dá)夢(mèng)數(shù)據(jù)守護(hù)DataGuard配置流程主庫(kù)配置dm.iniINSTANCE_NAME DM_PRIMARY MAL_INI 1 ARCH_INI 1備庫(kù)配置INSTANCE_NAME DM_STANDBY DG_ACTIVE 1配置歸檔dmarch.ini[ARCHIVE_LOCAL] ARCH_TYPE LOCAL DEST /opt/dmdbms/arch啟動(dòng)順序先啟動(dòng)主庫(kù)再啟動(dòng)備庫(kù)驗(yàn)證同步狀態(tài)SELECT STATUS$ FROM V$DATABASE;業(yè)務(wù)系統(tǒng)適配建議使用達(dá)夢(mèng)提供的JDBC驅(qū)動(dòng)配置連接池故障轉(zhuǎn)移在應(yīng)用代碼中實(shí)現(xiàn)讀寫分離路由監(jiān)控日志切換頻率V$ARCHIVED_LOG5.2 Docker容器化部署使用docker-compose部署開發(fā)環(huán)境version: 3 services: dm8: image: dameng/dm8:latest container_name: dm8 environment: - DM_HOME/opt/dmdbms - INSTANCE_NAMEDM_DOCKER ports: - 5236:5236 volumes: - ./data:/opt/dmdbms/data - ./arch:/opt/dmdbms/arch restart: unless-stopped關(guān)鍵參數(shù)說明數(shù)據(jù)卷映射確保持久化存儲(chǔ)需預(yù)先創(chuàng)建本地目錄并設(shè)置權(quán)限mkdir -p ./data ./arch chown -R 1001:1001 ./data ./arch首次啟動(dòng)后需執(zhí)行docker exec -it dm8 /opt/dmdbms/bin/dminit \ PATH/opt/dmdbms/data DB_NAMEDAMENG INSTANCE_NAMEDM_DOCKER6. 開發(fā)工具鏈與生態(tài)集成6.1 客戶端工具連接Navicat連接達(dá)夢(mèng)的配置要點(diǎn)下載達(dá)夢(mèng)ODBC驅(qū)動(dòng)dmodbc創(chuàng)建系統(tǒng)DSN驅(qū)動(dòng)程序選擇DM8 ODBC DRIVER服務(wù)器填I(lǐng)P:端口認(rèn)證方式選用戶名/密碼Navicat新建連接時(shí)類型選ODBC選擇已創(chuàng)建的DSN勾選使用UnicodeDataGrip配置步驟添加驅(qū)動(dòng)Driver files添加dm-connector-java-8.1.1.49.jarClass選dm.jdbc.driver.DmDriverURL模板jdbc:dm://host:port測(cè)試連接時(shí)需關(guān)閉SSL驗(yàn)證6.2 開發(fā)框架適配MyBatis集成注意事項(xiàng)dependency groupIdcom.dameng/groupId artifactIddm-connector-java/artifactId version8.1.1.49/version /dependency !-- 配置數(shù)據(jù)源 -- bean iddataSource classorg.apache.commons.dbcp2.BasicDataSource property namedriverClassName valuedm.jdbc.driver.DmDriver/ property nameurl valuejdbc:dm://127.0.0.1:5236/DAMENG/ /bean動(dòng)態(tài)SQL避坑指南避免使用${}直接拼接參數(shù)分頁(yè)查詢建議select idpagingQuery resultTypemap SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rn, t.* FROM table_name t ) WHERE rn BETWEEN #{start} AND #{end} /selectSpring Boot自動(dòng)配置示例spring.datasource.driver-class-namedm.jdbc.driver.DmDriver spring.datasource.urljdbc:dm://localhost:5236/DAMENG?serverTimezoneAsia/Shanghai spring.jpa.database-platformorg.hibernate.dialect.DmDialect7. 運(yùn)維監(jiān)控與故障排查體系7.1 日常監(jiān)控指標(biāo)關(guān)鍵性能視圖查詢-- 表空間使用率監(jiān)控 SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,2) Total(MB), ROUND(SUM(BYTES)/1024/1024 - SUM(FREE_BYTES)/1024/1024,2) Used(MB) FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME; -- 會(huì)話資源占用TOP 10 SELECT SESS_ID, USER_NAME, STATUS, ROUND(USED_MEM/1024/1024,2) MEM(MB), CPU_TIME FROM V$SESSIONS ORDER BY CPU_TIME DESC LIMIT 10;自動(dòng)化監(jiān)控腳本示例Shell#!/bin/bash DM_PATH/opt/dmdbms/bin MONITOR_LOG/var/log/dm_monitor.log # 檢查實(shí)例狀態(tài) status$(${DM_PATH}/DmService status | grep -c running) if [ $status -eq 0 ]; then echo $(date) - DM instance is down! ${MONITOR_LOG} ${DM_PATH}/DmService start fi # 檢查表空間 ${DM_PATH}/disql SYSDBA/SYSDBA EOF SET ECHO OFF SET FEEDBACK OFF SPOOL /tmp/tablespace_check.txt SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE STATUSOFFLINE; SPOOL OFF EXIT; EOF if [ -s /tmp/tablespace_check.txt ]; then mail -s DM Tablespace Alert adminexample.com /tmp/tablespace_check.txt fi7.2 典型故障處理連接池耗盡處理查看當(dāng)前連接數(shù)SELECT COUNT(*) FROM V$SESSIONS;釋放空閑連接-- 查找空閑超過30分鐘的會(huì)話 SELECT KILL ||SESS_ID||; FROM V$SESSIONS WHERE STATEIDLE AND LAST_RECV_TIME SYSDATE-30/1440;調(diào)整連接池參數(shù)# 在應(yīng)用連接池配置中 spring.datasource.max-active50 spring.datasource.max-wait10000備份恢復(fù)操作# 全量備份 ./dmrman CTLSTMTBACKUP DATABASE /opt/dmdbms/data/DAMENG/dm.ini FULL TO BACKUP_FILE1 BACKUPSET /opt/backup/full_bak # 增量備份 ./dmrman CTLSTMTBACKUP DATABASE /opt/dmdbms/data/DAMENG/dm.ini INCREMENTAL WITH BACKUPDIR /opt/backup/full_bak TO INCR_BACKUP_FILE1 BACKUPSET /opt/backup/incr_bak # 恢復(fù)示例 ./dmrman CTLSTMTRESTORE DATABASE /opt/dmdbms/data/DAMENG/dm.ini FROM BACKUPSET /opt/backup/full_bak ./dmrman CTLSTMTRECOVER DATABASE /opt/dmdbms/data/DAMENG/dm.ini FROM BACKUPSET /opt/backup/full_bak ./dmrman CTLSTMTRECOVER DATABASE /opt/dmdbms/data/DAMENG/dm.ini UPDATE DB_MAGIC