目录
一、完成达梦数据库备份恢复测试,验证物理备份可以支持指定时间点的还原。
二、完成达梦数据库逻辑备份测试,验证是否支持跨模式导入,是否支持部分表导入?如何实现指定时间范围的数据通过DMP进行迁移。
三、如何通过存储过程/存储函数传入模式名和要查找的字符串,判断出该模式下表中数据是否存在所要查找的字符串。
四、设计一个记录表,通过每周定时任务统计指定模式下的每个表的行数,已经分配的存储空间和已使用的存储空间。完成3周统计后,统计所有表每周增量。
五、设计一个记录表,通过每周定时任务统计每个表空间的大小。完成3周统计后,统计每个表空间增量情况。
六、利用数据库自身能力,设计一个逻辑,实现对数据库每分钟执行的SQL数进行统计并倒序排列。
七、利用数据库自身能力,设计一个逻辑,实现对数据库每秒瞬时并发会话数进行统计并倒序排序。
一、完成达梦数据库备份恢复测试,验证物理备份可以支持指定时间点的还原。
全库备份
backup database backupset '/home/dmdba/dmdbms/dmbak';
标注时间以及创建对照表
select SYSDATE, count(1) from TEST.EMPLOYEE;
--2022-09-20 12:27:50
CREATE TABLE TEST.EMPLOYEE_1 AS SELECT A.* ,SYSDATE TJSJ FROM TEST.EMPLOYEE A;
SELECT * FROM TEST.EMPLOYEE_1;
--2022-09-20 12:28:39
DROP TABLE TEST.EMPLOYEE;
SELECT SYSDATE;
--2022-09-20 12:29:12
select * from TEST.EMPLOYEE;
--2022-09-20 12:35:05
停服务,进入dmrman进行数据库指定时间还原恢复
./DmServiceDMSERVER stop
restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmdbms/dmbak/923'
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' with archivedir '/home/dmdba/dmdbms/dmarch' until time '2022-09-23 20:43:00'
recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic
二、完成达梦数据库逻辑备份测试,验证是否支持跨模式导入,是否支持部分表导入?如何实现指定时间范围的数据通过DMP进行迁移。
一、验证是否支持跨模式导入
通过dexp导出数据
./dexp SYSDBA/SYSDBA@192.168.254.149:5236 SCHEMAS=DMHR DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak.dmp LOG=dmhs_bak.log
查看数据文件
创建模式test
将dmp文件导入模式test
./dimp SYSDBA/SYSDBA@192.168.254.149:5236 REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak.dmp LOG=dmhs_imp.log
通过指定REMAP_SCHEMA参数,格式为DMHR:TEST,将模式DMHR导入模式TEST。
二、导入部分表
指定tables参数,可以导入指定表,多个表之间’,'号隔开。
./dimp SYSDBA/SYSDBA@192.168.254.149:5236 tables=DMHR.CITY, DMHR.JOB REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak.dmp LOG=dmhs_imp.log
三、验证是否支持指定时间范围的数据通过DMP迁移
在导出时可以通过QUERY参数,指定where参数导出表的子集的select 子句。
要想指定时间范围导出,前提是该表存在时间字段。
选择EMPLOYEE测试
指定参数QUERY导出
./dexp SYSDBA/SYSDBA@192.168.254.149:5236 tables=DMHR.EMPLOYEE QUERY="where HIRE_DATE> '2010-11-27'" DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak2.dmp LOG=dmhs_bak2.log
导入数据
./dimp SYSDBA/SYSDBA@192.168.254.149:5236 tables=DMHR.EMPLOYEE REMAP_SCHEMA=DMHR:TEST TABLE_EXISTS_ACTION=REPLACE DIRECTORY=/home/dmdba/dmdbms/dmbak FILE=dmhs_bak2.dmp LOG=dmhs_imp2.log
三、如何通过存储过程/存储函数传入模式名和要查找的字符串,判断出该模式下表中数据是否存在所要查找的字符串。
存储过程
create table info (table_owner VARCHAR(20),table_name VARCHAR(20),column_name VARCHAR(20),var_value VARCHAR(20));
create or REPLACE PROCEDURE FIND_STRING (var_table_owner VARCHAR(20),var_value VARCHAR(20))
as
DECLARE
var_sql VARCHAR2(1000);
var_cnt int;
begin
for table_detail In (select table_name,column_name from dba_tab_columns where owner=var_table_owner) loop
begin
var_sql := 'select count(*) from '||var_table_owner||'.' ||table_detail.table_name||' where trim('||table_detail.column_name||')='''||var_value||'''';
--print var_sql;
execute immediate var_sql into var_cnt;
--print var_cnt;
if(var_cnt>0)then
insert into info values(var_table_owner,table_detail.table_name,table_detail.column_name,var_value);
commit;
end if;
exception when others then
print SQLCODE || ':' ||SQLERRM;
end;
end loop;
end;
call FIND_STRING('DMHR','105');
select * from info;
dba_tab_columns视图查询
select t.table_name, t.column_name
from dba_tab_columns t
where owner='DMHR';
select EMPLOYEE_ID from DMHR.EMPLOYEE where EMPLOYEE_ID='1005';
select DEPARTMENT_ID from DMHR.DEPARTMENT where DEPARTMENT_ID='101';
select JOB_ID from DMHR.JOB where JOB_ID='72';
四、设计一个记录表,通过每周定时任务统计指定模式下的每个表的行数,已经分配的存储空间和已使用的存储空间。完成3周统计后,统计所有表每周增量。
建表
DROP TABLE IF EXISTS SYSDBA.TABLESIZE_COUNT;
CREATE TABLE TABLESIZE_COUNT (OWNER VARCHAR(100),TABLE_NAME VARCHAR(100),TABLE_USED_PAGES INTEGER,TABLE_USED_SPACE INTEGER,ROW_NUM int, TJDATE TIMESTAMP);
统计信息存储过程
DECLARE
V_OWNER VARCHAR2(100);
V_TABNAME VARCHAR2(100);
STMT1 VARCHAR2(200);
STMT2 VARCHAR2(200);
NUM_ROWS1 NUMBER;
NUM_ROWS2 NUMBER;
count_rows int;
STMT3 varchar(200);
TJDATE TIMESTAMP;
BEGIN
FOR REC IN
(
SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER='DMHR' ORDER BY 1, 2
)
LOOP
SELECT REC.OWNER, REC.TABLE_NAME INTO V_OWNER, V_TABNAME FROM DUAL;
STMT1 := 'SELECT TABLE_USED_PAGES('''||V_OWNER||''','''||V_TABNAME||''')*(PAGE/1024)';
EXECUTE IMMEDIATE STMT1 INTO NUM_ROWS1;
EXECUTE IMMEDIATE 'INSERT INTO TABLESIZE_COUNT(OWNER,TABLE_NAME,TABLE_USED_PAGES) VALUES('''||V_OWNER||''','''||V_TABNAME||''','''||TO_NUMBER(NUM_ROWS1)||''');';
STMT2 := 'SELECT TABLE_USED_SPACE('''||V_OWNER||''','''||V_TABNAME||''')*(PAGE/1024)';
EXECUTE IMMEDIATE STMT2 INTO NUM_ROWS2;
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET TABLE_USED_SPACE= '''||TO_NUMBER(NUM_ROWS2)||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
STMT3:='select count(*) from DMHR.'||REC.TABLE_NAME;
execute immediate STMT3 into count_rows;
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET ROW_NUM= '''||count_rows||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET TJDATE= '''||SYSDATE||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
EXECUTE IMMEDIATE 'UPDATE TABLESIZE_COUNT SET ID = '''||5||''' WHERE OWNER= '''||V_OWNER||''' AND TABLE_NAME = '''||V_TABNAME||''';';
COMMIT;
END LOOP;
END;
SELECT * FROM SYSDBA.TABLESIZE_COUNT;
创建统计每周增量建表
CREATE TABLE "SYSDBA"."T_STAT_TAB_INC"
(
"TAB_NAME" VARCHAR(30) NOT NULL,
"STAT_TIME" DATE NOT NULL,
"TOTAL_ROWS" INT,
"PRV_TOTAL_ROWS" INT,
"INCREMENT_ROWS" INT,
NOT CLUSTER PRIMARY KEY("TAB_NAME", "STAT_TIME")) STORAGE(ON "MAIN", CLUSTERBTR) ;
SELECT * FROM SYSDBA.TABLESIZE_COUNT ORDER BY TABLE_NAME ,TJDATE;
模拟数据
INSERT INTO SYSDBA.TABLESIZE_COUNT (OWNER ,TABLE_NAME,TABLE_USED_PAGES,TABLE_USED_SPACE , ROW_NUM ,TJDATE)
SELECT
OWNER
, TABLE_NAME
, TABLE_USED_PAGES + 50 as TABLE_USED_PAGES
,TABLE_USED_SPACE AS TABLE_USED_SPACE
, ROW_NUM + 10 AS ROW_NUM
,'2022-09-23 21:58:13.000000' as TJDATE FROM SYSDBA.TABLESIZE_COUNT WHERE TJDATE ='2022-09-22 21:58:13.000000';
COMMIT ;
统计行数增量
SELECT * FROM T_STAT_TAB_INC
CREATE OR REPLACE procedure P_SEL_TAB_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
TJDATE DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT ;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO T_STAT_TAB_INC (TAB_NAME , STAT_TIME ,TOTAL_ROWS ,PRV_TOTAL_ROWS ,INCREMENT_ROWS )
SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0) as INCREMENT_ROWS
FROM(
SELECT TABLE_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TABLE_NAME,STAT_TIME DESC),0) PRV_TOTAL_ROWS
FROM (
SELECT TABLE_NAME,LEFT(TJDATE,10) STAT_TIME ,SUM(ROW_NUM) TOTAL_ROWS
FROM SYSDBA.TABLESIZE_COUNT where TABLE_NAME = A.TABLE_NAME
GROUP BY TABLE_NAME , LEFT(TJDATE,10)
order by TABLE_NAME , LEFT(TJDATE,10)
)
)
ORDER BY TABLE_NAME, STAT_TIME ;
COMMIT ;
END LOOP;
END;
CALL P_SEL_TAB_SUM('DMHR.TABLESIZE_COUNT');
SELECT * FROM SYSDBA.T_STAT_TAB_INC
建表
CREATE TABLE T_STAT_TABSPACE_INC_1 AS SELECT * FROM T_STAT_TAB_INC ;
统计存储空间增量
CREATE OR REPLACE procedure P_SEL_TABSPACE_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
TJDATE DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT ;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO T_STAT_TABSPACE_INC_1 (TAB_NAME , STAT_TIME ,TOTAL_ROWS ,PRV_TOTAL_ROWS ,INCREMENT_ROWS )
SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0) as INCREMENT_ROWS
FROM(
SELECT TABLE_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TABLE_NAME,STAT_TIME DESC),0) PRV_TOTAL_ROWS
FROM (
SELECT TABLE_NAME,LEFT(TJDATE,10) STAT_TIME ,SUM(TABLE_USED_PAGES) TOTAL_ROWS -- ,sum(TABLE_USED_SPACE) as TOTAL_TABLE_USED_SPACE
FROM SYSDBA.TABLESIZE_COUNT where TABLE_NAME = A.TABLE_NAME
GROUP BY TABLE_NAME , LEFT(TJDATE,10)
order by TABLE_NAME , LEFT(TJDATE,10)
)
)
ORDER BY TABLE_NAME, STAT_TIME ;
COMMIT ;
END LOOP;
END;
SELECT * FROM SYSDBA.TABLESIZE_COUNT ORDER BY TABLE_NAME ,TJDATE
TRUNCATE TABLE T_STAT_TABSPACE_INC_1 ;
CALL P_SEL_TAB_SUM('DMHR.TABLESIZE_COUNT');
SELECT * FROM T_STAT_TABSPACE_INC_1 ;
关联查询两张表
SELECT A.* ,B.TOTAL_ROWS AS TOTAL_SPACE ,B. PRV_TOTAL_ROWS AS PRV_TOTAL_SPACE ,B.INCREMENT_ROWS AS INCREMENT_SPACE
FROM T_STAT_TABSPACE_INC_1 B JOIN T_STAT_TAB_INC A ON A.TAB_NAME =B.TAB_NAME AND A.STAT_TIME =B.STAT_TIME
ORDER BY A.TAB_NAME ,A.STAT_TIME
创建定时-每三周执行统计
call SP_CREATE_JOB('static_tab_inc',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('static_tab_inc');
call SP_ADD_JOB_STEP('static_tab_inc', 'static_tab_inc', 0, 'TRUNCATE TABLE T_STAT_TAB_INC;
CALL P_SEL_TAB_SUM(''SYSDBA'');', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('static_tab_inc', 'static_tab_inc', 1, 2, 3, 2, 0, '02:00:00', NULL, '2022-09-21 22:11:09', NULL, '');
call SP_JOB_CONFIG_COMMIT('static_tab_inc');
五、设计一个记录表,通过每周定时任务统计每个表空间的大小。完成3周统计后,统计每个表空间增量情况。
统计表空间大小
CREATE TABLE TABLESPACE (TABLESPACE_NAME VARCHAR(200),USED DEC, FERR DEC, TOTAL DEC ,PER_FREE VARCHAR(200),TJDATE TIMESTAMP);
DROP TABLE SYSDBA.TABLESPACE;
SELECT * FROM SYSDBA.TABLESPACE;
DECLARE
TABLESPACE_NAME VARCHAR(200);
USED DEC;
FERR DEC;
TOTAL DEC;
PER_FREE VARCHAR(200);
TJDATE TIMESTAMP;
BEGIN
FOR REC IN
(
SELECT TJDATE FROM DBA_TABLES WHERE OWNER='SYSDBA'
)
LOOP
EXECUTE IMMEDIATE 'UPDATE TABLESPACE SET TJDATE= '''||SYSDATE||''';';
COMMIT;
END LOOP;
INSERT INTO TABLESPACE (TABLESPACE_NAME , USED ,FERR ,TOTAL ,PER_FREE )
SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",
F.FREE_SPACE "FREE (MB)",
T.TOTAL_SPACE "TOTAL(MB)",
(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ' PER_FRE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS *
(SELECT PARA_VALUE
FROM V$DM_INI
WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1024)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
END;
统计每个表空间增量建表
CREATE TABLE "SYSDBA"."T_STAT_SPACE_INC"
(
"TAB_NAME" VARCHAR(30) NOT NULL,
"STAT_TIME" DATE NOT NULL,
"TOTAL_ROWS" INT,
"PRV_TOTAL_ROWS" INT,
"INCREMENT_ROWS" INT,
NOT CLUSTER PRIMARY KEY("TAB_NAME", "STAT_TIME")) STORAGE(ON "MAIN", CLUSTERBTR) ;
SELECT * FROM TABLESPACE;
模拟数据
INSERT INTO SYSDBA.TABLESPACE (TABLE_NAME,USED,FERR, TOTAL ,PER_FREE,TJDATE)
SELECT
TABLE_NAME ,
USED + 200 as USED,
FERR AS FERR,
TOTAL AS TOTAL,
PER_FREE AS PER_FREE,
'2022-09-25 21:58:13.000000' as TJDATE FROM SYSDBA.TABLESPACE ;
COMMIT ;
统计每个表空间增量
CREATE OR REPLACE procedure P_SEL_SPACE_SUM (SCH_NAME VARCHAR(100) )
AS
DECLARE
TAB_NAME VARCHAR(30);
TJDATE DATE ;
TOTAL_ROWS INT;
PRV_TOTAL_ROWS INT;
INCREMENT_ROWS INT ;
COUNT_ROWS INT ;
BEGIN
COUNT_ROWS:=0;
FOR A IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = SCH_NAME )
LOOP
INSERT INTO T_STAT_SPACE_INC (TAB_NAME , STAT_TIME ,TOTAL_ROWS ,PRV_TOTAL_ROWS ,INCREMENT_ROWS )
SELECT *,IFNULL(TOTAL_ROWS - PRV_TOTAL_ROWS ,0) as INCREMENT_ROWS
FROM(
SELECT TABLE_NAME,STAT_TIME,IFNULL(TOTAL_ROWS,0) AS TOTAL_ROWS ,IFNULL(LEAD(TOTAL_ROWS) OVER(ORDER BY TABLE_NAME,STAT_TIME DESC),0) PRV_TOTAL_ROWS
FROM (
SELECT TABLE_NAME,LEFT(TJDATE,10) STAT_TIME ,SUM(USED) TOTAL_ROWS
FROM SYSDBA.TABLESPACE where TABLE_NAME = TABLESPACE.TABLE_NAME
GROUP BY TABLE_NAME , LEFT(TJDATE,10)
order by TABLE_NAME , LEFT(TJDATE,10)
)
)
ORDER BY TABLE_NAME, STAT_TIME ;
COMMIT ;
END LOOP;
END;
CALL P_SEL_SPACE_SUM('SYSDBA.TABLESPACE');
SELECT * FROM SYSDBA.T_STAT_SPACE_INC ;
六、利用数据库自身能力,设计一个逻辑,实现对数据库每分钟执行的SQL数进行统计并倒序排列。
CREATE TABLE T_STAT_SQL_COUNT
(
SESS_TYPE VARCHAR(20),
SESS_COUNT INT,
SESS_TIME DATETIME(0)
);
CREATE OR REPLACE PROCEDURE P_STAT_SQL AS
BEGIN
INSERT INTO T_STAT_SQL_COUNT
SELECT 'SQL' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(START_TIME,19) SESS_TIME
FROM V$SQL_HISTORY
WHERE START_TIME > (
SELECT IFNULL(MAX(SESS_TIME),'2022-09-21 01:01:01')
FROM T_STAT_SQL_COUNT
WHERE SESS_TYPE = 'SQL')
GROUP BY LEFT(START_TIME,19);
END;
调用存储过程
CALL P_STAT_SQL();
倒序排列
SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SQL_COUNT
WHERE SESS_TYPE='SQL'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;
七、利用数据库自身能力,设计一个逻辑,实现对数据库每秒瞬时并发会话数进行统计并倒序排序。
建表
CREATE TABLE T_STAT_SESS_COUNT
(
SESS_TYPE VARCHAR(20),
SESS_COUNT INT,
SESS_TIME DATETIME(0)
);
统计会话并发情况
truncate table T_STAT_SESS_COUNT;
select * from T_STAT_SESS_COUNT;
CREATE OR REPLACE PROCEDURE P_STAT_SESS AS
BEGIN
INSERT INTO T_STAT_SESS_COUNT
SELECT 'SESS' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(CREATE_TIME,19) SESS_TIME
FROM V$SESSION_HISTORY
WHERE CREATE_TIME > (
SELECT IFNULL(MAX(SESS_TIME),'2022-09-21 01:01:01')
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE = 'SESS')
GROUP BY LEFT(CREATE_TIME,19);
END;
调用存储过程
call P_STAT_SESS();
倒序排列
SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE='SESS'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;