DM运维管理小知识

1、达梦用户俱乐部QQ群里偶尔会有新用户关于DM运维管理的一些相关问题 DM技术工程师对日常接触到较多的情况，以命令行操作为例，做了个整理， 包含以下四个方面：用户、备份、统计信息、调整基础参数，以供查阅。图片很多，请在wifi环境下阅读-数据库安装部署基准过程 -1.用户"T ALTER USER SY3DEA IDENTIFIED* dame ng 123认用勺的憲码2 CREATE USER DMD5A IDENTIFIED BY wdamengl23T,一一增31 管遅员弔户:GRANT resource TO DMDBA一一分斷工。弓nuiCE角用户建议根据项目和用户实际情况定

2、义一个缩写且便于记忆的用户名，密码由用户定义，例如武汉达梦则名字为WHDMT CREATE USER WHDMDBA IDENTIFIED BY MDMDBMS1234h, -增加用户管理员用户 0 GRANT DBA TO WHDMDBA-根据实际情况创建一个用户默认的表空间10 CREATE TABLESPACE TS_HBASPT DATAFILE * TS_H3A3PT01 . DBF r SIZE 12 311 JVLTER TABLESPACE !*£13ASTT ADD DATAFILE r TS_IBA3rT02 . EEF 1 SIZE 12S-根据迁移或者用户需求

3、以及项目定义一个普通用户13 CREATE USER WHDM IDENTIFIED BY T，WHDM_1234L4 DEFAULT TABLESPACE TS_WHDM一一増加普通用户，弁假用默认夷至间:z GRANT RESOURCE TO WHDM-2.备份（注：以下作业相关涉及函数及参数较多，如果可以，建议尽量使用 管理工具） -修改归档并进行备份测试I 19 ALTER DATABASE MOUNT2 0 ATTER DATABASE ARCHIVELOG-添加归档，指定最大大小为100G2 2 ALTER DATABASE ADD ARCnLVELQG ' DEET-Z

4、:DATZlXARCII, rYFE-LOCAL/23 FILESIZE-1024,SPACELIMIT-l02001- Windows24 /*ALTER DATABASE ADD ARCIIIVELOG DEST-/ept/±ndbm3/data/archf25 TYPE-LOCAL fFILE SIZE-lC 2 4,SPACE LINIT-l024C 0 f; -Linux*/2 < ALTER DATABASE OPEN-进行全量备份2 8 BACKUP DATABASE PULL TO DMBAK_FULL_00 BAKFILE29 T C： DMDBMSDArAB

5、AKDMBAK_FULL_OO .BAK1 COMPRES-SED-进行增量备份BACKUP DATAhiASE INCEtEMENT WET" BACKVPDIR ' C： EHDDMS?AT'ABAK TO DMBAK_INCREMENT_00 BAKFILE 1C: XDMDBMSDATABAKMMBAKINCREMEbiT_0O. BM' 3 COMPRESSED-开启定时任务进行定时备份和定时删除35 S P INI r_ JOB SYS 1开启代遲作业 -定时每周六运行，进行全量备份37 一call sr_Ror_JOE (' jce_fu

6、il_bak_timeLy'iIP call SP_CREATEZJCIE 1:MFLYr 1 0 T 1 0 0 ' 1 -J '崔匪全型甬吋'recall 3P_JOB CONFIG_STAHT EE FULI RAK TMEL¥F40 call _ADD_JOB_£TEF * JOB FULL BAK TIMELYr .1 STEP ITILL BAK' . Sr 01OOOC:DMDBMSDATABAKF 0 Q_ 001C;dmdbm3dataj ob log'142/*calL SP AED JOB_STEPI1J

7、0B_FVLI_BAR_TIK3LY*f SItE_ETT匸L_mAK*r 5tr )LDOO/Qpt/dmdhjni;/bak1 f 0, 0, A 0r /opL/dnidLin/dALd/job»lag r t 1) ； */ call SP ADDLJOB SCHEDULE 'JOE FULL QAK TIMELY* 'SCHEDULE_FULL_BAH'451 2- lr_64 _0 *00:05:NULL *2016-04-07 21;05:&77 NULL<6call SC_JQB_CONriG COMKIT pJLH_FTL二_B

8、AK_T二MELY'-定时每天运行，进行增量备份(晚上)1 8 -Cdll 52_DEt©P_JOB ( ' JOE-IMCREMEMT-BAFIIHZLMIGET1 );4 2 eil丄 3P_CEIEA7E_JOB 1 JOE-IMCREMENT-BAF1： IMELY_N： SET ' 1 0 0 0 * * 0 .*l call 9P_JOB_CONFIG_START 1 JOB_INCREMENI_EAK_rl IkRLV NlliE：T 151O&11 BF_ADD_JOT S TE P ' JOB_IE«CREMEirr

9、_BlM_TIMELY_NI<5BT, r ' ETEP_INCIREHErNT_BAK' x E2 S R110C0C: X DHDEMSYUZTA BAK I C!: EMTBM£2ATABAK1.J 000 C kC: daidijnu»dAtajoe. Iq1 154 /*call 9P_ADD_JDB_9TEPl' JOB_IMC-tEKE NT_BAF_I IKEL Y_NI &HT ' r55 1 £TEr_rNCEEMENT_3AK' , 5 /_' 11000/opL/JrLJL;nt

10、/dALi/bjk | /op_/dmdbffii;/diLi/bik* f5<j0# 0t Of 0r *C :i*nii<iiHKrl+jnh 1 n jcall 9P_ADD_JOB_SCHEDULE ' JOD_TMCREMENl BAK TIMELY NIGJiT*'SCHEDU1F_INCFEMENT_3AK, 11100TD2：D5：56,59 NULL -2016-04-07 2玄汕5：57 NULL ' *C JcjJI SP_J0E_CDKFIG_70MMZT (JC3_IN7F£MiNT_BAK_r：MELY_NTGK71-定

11、时每天运行，进行增量备份（白天）62 -call SP DROP JOB('JOB INCREMENT 3AK TIMELY D;6? call SP_CREATE_JOB 1 JOE INCREMEMT BAK TIMELY DAY' 1 0 八 0 0. T 15 '宜时垮里暑州丨64 call 3E-_3 TAFT 'JOB INCREMEM-iaAF TIMELY DAY*6r «11 SP_ADD_JOB_STEP pJOB INCREMEMT BAE TIMELY DAY1 1 STEP_INCREMENT BAP 1E E 5 s 11-

12、iJ QOC : DMDBHBDATA BAK | C : QM2-BMSDA.TABAK 1 , D 0 Q 671 C ： dmdban-Xdat a nb - 1OCJ ' r 1 ;63 /*call SP ADD JOE STE P < r JOB_INREMZNZ'_BAK_T IHEL¥_EA¥ 1 r69' STEP_IP4CREMENT BAK' f S f * 11 DOO/cpt/dinritms/daa/bBk-1 / DpT:/=imdtms/data/Lak:-1Or 0f 0r 0,1 /&pt/r

13、dmdbm3/dat:a./jfik . l*q* r 1 ； */了 1 call SP_ADD_JOB_SCHEDUL£ * JOB_INCRHMENT_BAK_TIMElY_DAY *1 SCHEDULt_ INCR£Wb：Nr- _BAK '721.17 10, 0 IZ-OSiSfe1 NULL 12016-04-07 217oS2 57' . MULL fT'? call 3 P JOB COM FI G CONHIT ' JOB _ IW C REMENT： _BAK_T Itffi L¥_DA Y1-定时每周日运行，

14、删除前30天的备份，包括全量和增量7b 一亡a丄亠 SP DROP JOE (1 JOE_DEL_faAK_Tlbl£L¥ * ):Gcall SP_CREATE_JOB * 50B_DEL_BAK_TIMELY * 1 3 1 *3 0 八 01call SPJOB 2OMFZS START *JO& DEL BAK TZVEIY1-S call SP_A口口口口巳匸区卩 r JOB_DEL_EAK_TII>IELY TT STEP_口ZL_BA?< * 679 rSF_5ArCH_DEL_5AKR 'DAMENG' 'r T

15、r T 1r lf syadate-SQf -1); *30 o o o Q 1C:atnclbiiisdlatajob - logT 1Z1 call 5P_ADD_JOB_SC；HEDULE T CT05_DEL_BK_TZXELY 11 ECHEDUIjZDEIj-BAF 113221 1 070lJ;0&56' NUIi 72016- 04-D7 21:0S;57T NULL_ 1 rz 3 Cfl 11 SP JQF COMFT& 匚OMMIT ' JOB DKL 3AK TTMJCLY，-定时每周日运行，删除前30天的归档日志35-Call SP_D

16、ROP_JOE('JOB_DEL_ARCH_TIMKLY');SScall SP_CREATE_JOE 1 JOE 2EL ARCH TIMELY1 1 0 ' 1& 0 tt q "全曰拥煖备惜*Cdll SP JCB CONFIG_START * JOB_DEL_ARCH_TZKELY '3S call左T 它匸'JOB DEl7aRCK TtMELY 1 'STE? DEL AK亡H 'C SF_AR CH T VELOGDEt ET E_BE FORE_T I ME S Y5 P P.T E - n 1) ； 1

17、0000.50 r c： dtidbmBdai;a jck>, Legr 1- call S P_ A DD_ JOB_SC HKDULF T JOB_DFTi_F r*F TZKFTjY 1 r SCFFF UIjF_7IEL_A?CFr92 I- 2 1 10 'OOiOSjSG1 _NVLL r2016-01-Q7 21;05;S7r7 NULL '3 call SP_JO&_CONFIG_comTT 订日_r：F二_amCH_ETMFT.Y-3.统计信息 -使用WHDMDBA登录执行以下统计 -创建视图，统计所有的模式和表、.CREATE OR HE PL

18、ACE VIEW ¥_ALL 二 A39 ? AS_c, mis籾师査计ji C - JTET.EGT102A MAKE SCH_NAME1C-3BTA3_NAMElQA FRJOMSYS SSOBJECTS A JOIN SYS S：fSOBJECTS E10 SOW1LAID=H 3CHIDILJJANDArYPE$= 0£Tif9'ANUAMAM£ NOT1nr ;1s¥£1r1CTISYS1* 5¥£AUDITOR1SYSSSO1'YfiJOk11SYSDBA0JWDE丘UTAB1111ANDBPID

19、丄丄ANDNAME MOTLIKE P%5ACX'ORDER fix 12-创建记录表1113 撻记录克''-CREATE TAELE T STAT TAB115 (_lit SCH_NAME VABCHAR 2011 TAB_NAME "ZARCHAR 500 DEFAULT SYSDATE118 TAB CNT INTIIS £TAT_T I ME DAT 四 I ME120 ;-存储过程，统计所有表数据量,或者指定用户下的表数据量ffH.nPT tiI.'皈召*nUTW PTMAfrit*CREATE OR PXPIJLCE PROCZ

20、IilJRE P STXT PRH工 E SCH7ESU1 HKXE VAFZrJIEL 50 ASJ v_oirr iitt；124 BECLH 7 - mtyn曾时刚打耳品 砒国白下辭梯阳TF ECASE CHEaANME =咋二1? THEN-匚.SCHEHA_Ma?tE .：= 1 1ua EMB IF-2；FO-K BEC XN HE1ECTIAB_liAKE FRCW 章二AL1L_T栖LIKE B B l| I SCK£MA_NAMI- I I ' ' LOOPBEGINE>®CUTE. IMMEDIATE 1 SELECT CTC* F

21、ROM °T IRTC 3CE_NAME | * .s, 1 J | ft£C TAB_tCAME I I 1 "INTO V_OTT02EJCCEPTION Ifl僵坤 OTHERS THIKN133T.CHT ; = -1 -1 昶EHDEJCEO.ITK IV«E£i LATE 11 甘 £R12 MT .- t_S ：氏 JLZ 壬亡fT 甜曲2宦 P詢 T!Pl ： h.- !1 - I IRK 述”_h&EF r < i7ttc TAB»WEI I ' |TP 1 I |V_CNTI r 1

22、OWi IXH3P_ izCCHMITIS- EhoD140 P STAT TABLE 1AiL-定时每周日运行，统计本周所有表数据丄4二鬥HSJf)运m備计疋间讦i耐用j 4?1 SF_CROP_ JDB ( 1 JDB_STAT TAFT." GO-NT TTY"Ty '):j 4 ' trail 5F_CFEATE_JOE ' 33B_5TRT_TAfi J.r_'NT_T IIYRT. Y ' 1 0 T ' Q D » r D 寸-葫b"1 -J wll SF . COHFIG START H J

23、OE SZAZ TABLE COUNT TIMELY ' A - trail SP_AUD_JOH_STEF 1 JOD_GTAT_TABJLE_CatrMT_r»tMEtTf 1 r 1 STEP_STAT_TAfitE CDUNl*1 s141-0« mr&T_TAKU «ALL" ： * ,0 . 00' 1 , 1i 4 = Mil S P_APDLJCiB_S CHE DULE * JX)B_S7AT_TABLE _CQUNT_T IHELY *1 SCHEDULE. S TAT_T BIE _C OUNT *I4S 1

24、2110,00：OS：&&1 . NULL. R21£-04-07 21!0&：B7' NULJ. R 1'd Mil SP_JOB_CQ)TFIG_COy«TT 'EE WT%T TAB.F COITTT TTMFL7 '-统计每周总量和增量鸭r-懈他耳匸郢:啤空:5 CREATE OR REPIACE VIEW V_SELTAB SUM15 ASlb： Jftrl頓 felt!电ZSiIS" SELECT * TOTAL ROWS - PRV TOTAL ROWS ZNCREMEN'T ROWS.

25、 5FRjQM.5r SELECT £TAT_TIME TOPAL ROWS LEAD TOTAL OW£ OVER ORDEH BY SATIKE DE£C tRU TOTAL .ROWS1 泊 FROMasLEev un>fiTAT_PiNE.id 时血.aomiabjcmti TOTM-RCwa15*FROM T_STAT_TAR 二时GROUP BY LEFT STTTIME 10jfflKRE PPV_TOTAL_FOWS IS NOT WLL-查看每周总量和增量13总星和增里164 SELECT * PROM V_SEL_TAB_SUH-统计每个

26、表每周总量和增量'CREATE REPLACE VTiRW V_SEL_TAB_IWCR'厂一一线rl鶴亍勰宝和雄-SELECT 5TAT_TLME TA3_KCKS PRV_rAH_R0W5 TAEi_RaW5 -PRV_TAB_ROiirS IJ»CMEMEWT_KaWiS SCH_MilKE TAB_NAME17： SELECT £TAT_TTME TMl_ACW£ LEAD TAB_RCTMS OVER 3>ARTI7IGW BY SCM_NAMT TA5_KAra；77UIUDER B¥STAT_TIMEDK6C PR7J

27、TAB_ioWS SCH_WAME TAJjAME173 FROM".7iSELECT LEFT S7AT_TIME ID STATTZME TAfiCNT TAB_ROWS SCH NAME TABHAMEFROM T_5TAT_TAE1. ric J二 WILE血 FRV_TM_ROWS IS NOT HULL-查看每个表每周总量和增量17 9 SELECT + FROM V_SEL_TAE_INCR-查看指定表每周总量和增量|181 SELECT * FROM V SEL TAB INCR WHERE TAB_NAME= * STAT TAB F-查看每个表最新周总量和增量11

28、8 3 SELECT * FROM V_EEL_TAB_INCR:F 丄 WHERE STAT_TIME=185 SELECT MAX STAT TIME186 FROM V_SE L_T AB_ I NCR OKDER SY TB_ROWs"dESG-查看增量较大的表数据信息L-.I SELECT * fKOM V SELJTAB INCR ORDER BY INCREKENT RDWS DKSC NULLS LAST-统计数据库存储空间和表空间存储空间-，51 DROPT_SrAT_0_SrEi?.r' ? CEULATK TABLE T_STAT_Dn_SI ZE193

29、 -15 4 TBS_HA«E VJlKCHAR 2 015 :IN-11 5 STAT TTFF rA-TTyT 0 FA1JI.T YPF7-TFj gj :； S3 -一5FtECT * FRCWi T STAT DB SIZE OKPER PT 3TA.T_TTMF? CREATE OR REPLACE PROCEDURE F_STAT_5I3i Ai2(. J HEGIHEfl 1 INSERT INTO T STAT DB SIZE TES WAME TOTAL, ETEE MBQD2 SEIiECT +DB' TBS_tfAME TDTAL_SI ZE*PftG

30、E/1024 /103 4 FROM VS DATABASE -.'.i' 2 I NSKHT LHTO T_STA1?_ DB_SlZE TEB_JJAMZ TOTAZj_5I SE_M3Q04 SJUJICT LENJLL HAME * ALJ_TL二 NAME S JM TOTAL_SI22*0*/1024/102 3_J4B_i j FROM V5TA3LESPACECROUP BY RjOKLUP NAMZ2 C 7 END|208 CALL P STAT SIZE；-定时每周日运行，统计本周表空间存储210-till SP_DRO2_JOE(1JOB_STAT_DB

31、_SIZH_TIMELY');2J 1 ejll SP_CF.EATa_J'OB * JOB STATZE_7IMELY' 1 0 f 0 Q D '整时竦怙5里'212 SF_ JOB_CCNFIG_3TARI FJCE_3TAT_DB SIZE_TIMELY,213 cjII S F_ ADD _ JOB _ SCE P 1 JOB STAT_DB SIZE TIMELY' ' STEPSTAT TB SIZZ"214 D ,r_STAT_SIZE, 0 0r 00*'1215 ull SF_AZfD JOB_SCH

32、KLC-K R JCE STAT DB £IZE_2IMELY' ' SC-zEDULE .STAT DB SIZE216 12- 1 _10 r 00:05:56'' .' NULL' '2016-04-Q7 21：0S： 57T NULL 1721 trill SF JOB CONF IG_ COMM IT 'JOB STAT DE SIZE TIMELY*-统计每个表空间增量CREATE OR REPLACE VIEW V_5EL_SE_rHC!l4 AJS2iL WC-1+tdilS-.SEEECT STAZ 二

33、二ME T3£ NAME TCTAL 5IZE MB P3：T TCT* BIZH： XE_ . ZOTAIl SIZE MS-?fcT_T*CAL_S13E MB *fCRE<EKT AGWS_ r PBfita SELECT 5IAZ_Tl>ffi： TBS ITMIZ T OTAL_31 ZE_MZ-.L LEAD TOTAL_EI：E HD_ OVEH PARTITIOH-B¥ TBS HnMZ ORDEIK DY £TAT_7IKE 口ESC EKV_TOTAL_EIZE_MBh FBCM_ : ：SEJLECT LEFT STTS CZMZ

34、 二9 STA TZKE T&£ 14AKE 7DIAL SIZE H3_2.-FRCM T_fiTAT_2fi_£iE2230. muz RE FRV_Z CT AL_ 113E_M3 1£ HCT HVLL-统计每个表空间增量233 SELECT 卄 FROM V SEL DB IMCR一 -统计会话并发情况2 3 5 CKEATE TABLE T STAT SESS COUNT236 (一 一 一23"SESSTYPE VARCHAR 20238 SESS COUNT INT239 SESS_TIMF DATETIME 0240 )； 一-

35、统计会话并发情况URE P STAT SESS STAT CNT INT AS2 百 L CREATE OR REPLACE PRj/ 3 BBGIN244 -毘计澄時笈麻只250齐H254 kj E t也j g<s r 7256INSEHT INTO T_STAT SESS COUNT SELECT ' S££S+ SESS_7YPi CDUtFT * FROM VSg£SSI0N_HISTORYWHERE CREATH_TZME>SELECT IFNU I. MAX SES5_TIME FROM T STAT SE 5 S COUNTWll

36、KftE £ESS_T¥FE"' ,：J'. !：fCROUP BY LEFT_C B£ATE_TIKE 1鼻11AV1NC COUMT * >STAT CMI£ES5 C3UNT*2001 01INSERT INTO T_STAT_SESS COUNT SELECT 'SOL' SESS_TYPE COUNT * FROM VSSQL_IIISTORVWHERE START_Z IKE >吕ELECT TFNTJLL MAX SEES TIMEEESE SGUNTOOl-Ol-OlLEFT STAR

37、T TZMZ 19 SESS TIME01:01:01*bWM r_J rAT_SE S S_ JC 'JhiTWHERE SESEYFE-1 1CHQUP BY LEU ETART_TIHE : 9HAVING CDUXT * >STAI CNTfc 3265 CALL P STAT SESS(2);L67 call SF_tROP_JOB；1JOB ST AT StS S COUWTT:HE LY *); pfiD call p?cail -'.um-定时每小时运行，统计时间段内并发情况SP_CREATE_JOB ' CC3_STAT_SiSS_CCUNT_Z

38、rMELYF 1 0. * H . 0 0 0 '尸州腎计二忑并右田'S?2jOE_CONFI<i_STAT ' JOE_3TAT_SES：Ol'N：_：IMELV¥?710:I fal1»l 1S P_ADD_J0iB_STE P ' JOB_STAT_SE SS_COUNT_TIME1S * r 1STE P_5TAT_S£S 3_C0WT ' *TCALL P_STAT_SEE3&) ； 1 O. Or 0 .* 11S P_ADn_Jafi_SCHPDULE 1 J OE_STAT_E E S&

39、#163;_CDUN T_T IME h f -1 £CHE DULE_3TArT_SRS S_C DiniT * f? 1 飞,GO '00:00200*7 -23759:&9' H '2?1£-<；4-07 21iOS;E7t_ HULL, 厂BP JOB CONFIG COMMIT r JOB ?AT l疋，曙 COUST TIMEI F-查询会话并发统计，按并发量排序。I '' SEL.BCT * FROM T_STAT SES£_COUNT WHERE SESS_TYFE-1 GEG 1 ORDER BY SESS_C(XJr<T DKSC-查询会话并发统计，按并发时间排序。|2： 8 SELECT * FROM T_STAT_SE£S_COUNr WHEJ<E SESS_TYIE- + SES£ 1 ORDER BY £Z££_TIME DESC-按照分钟统计会话并发2 3： SELECT laEFT SESS_TIME 16 SUM S£SS_COOTT2 51 FROM T_SIAr