2023年河北职业院校-数据库技能大赛试卷B

2023年河北职业院校-数据库技能大赛

试卷（B）

参赛须知 

一、请检查和复核考试环境

1、请在VMware Workstation中打开培训使用的标准虚拟机“D:\ Centos72-V8R6C5B23”。

2、还原虚拟机快照至“环境准备”。

3、物理机中已安装linux虚拟机远程连接工具MobaXterm免费版。

4、数据库安装包、授权文件、金仓一键优化工具等文件都保存在虚拟机的/install目录中。

5、虚拟机信息（注意：严禁修改考试虚拟机的配置）。

（1）IP:192.168.40.111；

（2）ROOT密码：kingbase.123；

（3）硬件配置信息：CPU 2核、内存3GB、硬盘20GB；

（4）操作系统：CentOS Linux release 7.2。

二、★答题须知★

1、请考生在物理机桌面上创建考生答题目录，目录名称为“选手编号-【选手编号】”。例如：第30号选手应创建目录名为 “选手编号-30”。

2、交付物提交：提供每题要求的截图；提供试卷末尾指定的其他交付物。

（1）交付物不规范将会影响题目得分；

（2）如果选手赛后对成绩存有异议，交付物不规范不利于申诉。

EXAM01-数据库部署

一、部署数据库软件及服务（12分）

1、查看/install目录中的安装文件和授权文件，并挂载KES的安装文件。

（1）查看安装文件，KingbaseES_V008R006C005B0023_Lin64_single_install.iso；（1分）

（2）查看授权文件，安装时请选择企业版授权license_enterprise.dat；（1分）

（3）挂载安装文件到/mnt目录。（2分）

2、执行安装KingbaseES V8R6（图形化界面或字符界面安装均可）,要求如下：

（1）数据库管理员SYSTEM的密码设置为kingbase；

（2）数据库监听端口号设置为52022； （2分）

（3）数据库字符集设置为UTF8； （2分）

（4）配置操作系统启动时自动启动数据库服务。 （4分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam1-1.png。

$ ls -ltr /mnt |tail -2 $ /KingbaseES/V8/Server/bin/ ksql -Usystem -dtest -p52022 -c "select get_license_validdays()" $ /KingbaseES/V8/Server/bin/sys_ctl status -D /data $ /KingbaseES/V8/Server/bin/ksql -Usystem -dtest -p52022 -c "show port"|grep 52022 $ /KingbaseES/V8/Server/bin/ksql -Usystem -dtest -p52022 -c "\encoding" $ chkconfig --list|grep kingbase8d

二、配置KES安装用户的环境变量（4分）

1、允许数据库命令不带路径就可以执行。 （2分）

2、通过环境变量指定数据库默认所在的目录和默认端口号。 （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam1-2.png。

$ env|grep -iE 'kingbase_data|kingbase_port|path'

EXAM02-数据库参数配置

一、KES检查点参数优化（3分）

在KES的主配置文件中配置参数checkpoint_completion_target的值为0.9，并使参数生效。

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam2-1.png。

$ ksql -Usystem -d test -c "show checkpoint_completion_target"|tail -3|head -1 $ cat /data/kingbase.conf |grep checkpoint_completion_target

二、配置共享内存（3分）

要求使用alter system命令配置数据库共享内存约为512MB，并使参数生效。

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam2-2.png。

$ ksql -Usystem -dtest -c "show shared_buffers"|tail -3|head -1  $ cat /data/kingbase.auto.conf |grep -iE 'shared'

三、配置用户登录认证（4分）

1、配置system用户只能在服务器本地执行免密登录。 （2分）

2、配置其它用户只能在和数据库服务器同一个子网内执行MD5认证登录。 （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam2-3B.png。

$ cat /data/sys_hba.conf |grep -i -E '^[^#]'

EXAM03-数据库管理

一、用户的管理（4分）

创建用户user_admin、 user_rw、user_r1、user_r2，要求如下：

1、创建用户时使用默认权限,密码均为kingbase。 （2分）

2、允许用户user_r1最多可同时创建10个连接。 （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-1.png。

$ ksql -Usystem -dtest -c "\du user*"

二、表空间的管理（3分）

1、创建目录/home/kingbase/oa并设置合适的权限。 （2分）

2、创建表空间tbsoa并指定存储目录为/home/kingbase/oa。 （1分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-2.png。

$ ls -ltr oa -d $ ksql -Usystem -dtest -c "\db tbsoa"|grep tbsoa

三、数据库的管理（3分）

创建数据库oadb，要求如下：

1、默认字符集UTF8。 （1分）

2、数据库所有者为user_admin。 （1分）

3、默认表空间为tbsoa。 （1分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-3.png。

$ ksql -Usystem -dtest -c "\l+ oadb"|grep tbsoa

四、模式的管理（2分）

1、在数据库oadb中新建模式oa。 （1分）

2、模式oa的所有者为用户user_rw。 （1分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-4.png。

$ ksql -Usystem -doadb -c "\dn oa"|grep oa

五、表的管理（9分）

1、创建和修改表（5分）

（1）使用system用户登录test数据库,创建模式s01； （1分）

（2）执行脚本/install/EXAM_v0.11.sql，生成样例模式exam； （1分）

（3）参照exam中score和 course表，使用CTAS的方式在s01模式中创建同名表；（1分）

（4）增加约束，实现s01.score表的cno列参照引用s01.course表的cno列； （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-5-1.png。

$ ksql -Usystem -dtest -c "\d+ s01.score|course" $ ksql -Usystem -dtest -c "select * from s01.score limit 3"

2、表的备份和还原

（1）备份s01模式至/backup/dump/s01.tar,备份为tar格式；（2分）

（2）将备份文件/backup/dump/s01.tar恢复到oadb库的oa模式中。 （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-5-2B.png。

$ ls -ltr /backup/dump/s01.tar $ ksql -Usystem -doadb -c "\dt+ oa.*"|grep oa

六、视图的管理（3分）

使用system用户登录oadb库执行以下操作：

1、为表oa.course和oa.score创建视图oa.v_info，视图列含sno,cno,cname,score。（1分）

2、要求视图只显示课程编号为20且考试分数大于90分的记录。（2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-6B.png。

$ ksql -Usystem -doadb -c "\dv oa.v_info"|grep v_info $ ksql -Usystem -doadb -c "select * from oa.v_info"

七、权限的管理（6分）

使用system用户登录oadb库执行以下操作：

1、授权user_r1和user_r2用户对oa模式具有usage权限； （2分）

2、授权用户user_r1只能查询表oa.score和oa.course； （2分）

3、授权用户user_r2能查询和更新表oa.score和oa.course。 （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam3-7.png。

$ ksql -Usystem -doadb -c "\dn+ oa" $ ksql -Usystem -doadb -c "\dp+ oa.score|course"

EXAM04-物理备份

执行物理备份 （10分）

1、物理备份存储目录设置为/backup/rman/。 （1分）

2、设置操作系统定时备份策略：

（1）每日凌晨2点全备； （1分）

（2）每日凌晨4点增备； （1分）

（3）备份文件保留策略设置成冗余份数为2； （1分）

3、执行全量物理备份。 （2分）

4、执行增量物理备份。 （2分）

5、执行差异物理备份。 （2分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam4-1.png。

$ ls -ltr /backup/rman $ cat /etc/cron.d/KINGBASECRON $ cat /backup/rman/sys_rman.conf | grep -iE 'repo1' $ sys_rman --config=/backup/rman/sys_rman.conf --stanza=kingbase info | grep -iE 'full|incr|diff'

EXAM05-运维与优化

一、数据库巡检 （4分）

1、查看数据库列表。 （1分）

2、通过数据库version函数检查数据库版本信息。 （1分）

3、通过数据库get_license_validdays函数检查数据库license有效期。 （1分）

4、通过数据库sys_postmaster_start_time函数查看实例启动时间。（1分）

5、将以上巡检命令和巡检结果保存/backup/dbcheck.txt；

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam5-1.png。

$ cat /backup/dbcheck.txt

二、SQL基础 （12分）

使用system用户登录test数据库，使用“\d”查看exam模式中相关表的结构，执行以下操作:

1、从exam模式的score、course、student表中查询满足条件的记录。（4分）

（1）查找每门课程考试成绩低于本门课程平均成绩的学员信息；

（2）显示学员编号，学员姓名、课程编号、课程名称、考试分数，本门课程平均成绩；

（3）请将sql语句和运行结果保存至/backup/sql-B01.txt；

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam5-2-1B.png。

$ cat /backup/sql-B01.txt

2、从exam.score表中查询20号课程的考试分数按从高到低排列前三的学员信息。 （4分）

（1）要求显示学员编号、课程编号、考试分数；

（2）请将sql语句和运行结果保存至/backup/sql-B02.txt。

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam5-2-2B.png。

$ cat /backup/sql-B02.txt

3、从exam模式的course、score表中查询每门课程的统计信息。 （4分）

（1）要求显示课程编号、课程名称、课程最高分数、参加考试人数、通过考试人数。

（2）请将sql语句和运行结果保存至/backup/sql-B03.txt；

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam5-2-3B.png。

$ cat /backup/sql-B03.txt

三、SQL优化（6分）

1、system用户登录oadb库后，使用以下语句创建测试表和插入测试数据。 （2分）

$ ksql oadb system create table oa.t01(col1 int,col2 text,col3 int,col4 int); create table oa.t02(col1 int,col2 text); insert into oa.t01 select generate_series(1,1000000),md5(random()),random()*10000,random()*1000; insert into oa.t02 select generate_series(1,100000),md5(random());

2、通过添加索引的方式来优化查询，控制实际执行时间在1ms之内。 （4分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam5-3.png。

$ ksql oadb system -c "\dt+ oa.t01|t02"|grep oa $ ksql oadb system -c "explain analyze SELECT a.* FROM oa.t01 a,oa.t02 b WHERE a.col4=b.col1 AND a.col1 = 100" |grep Execution

四、对象占用空间管理（7分）

使用system用户登录oadb数据库执行以下操作:

1、使用下面的SQL在oa模式中创建表t03。 （1分）

CREATE TABLE oa.t03 with (autovacuum_enabled=off)  as SELECT * FROM oa.t01;

2、在t03表的col1字段上创建索引idx_t03。 （2分）

3、使用下面的SQL删除t03表中50%的数据。 （1分）

delete from oa.t03 where col1<=250000 or col1>750000;

4、手动释放t03表删除的数据行所占用的空间，查看空间占用结果。 （3分）

答题后执行以下命令，并将截图保存至考生答题目录，图片文件命名为exam5-4.png。

$  ksql oadb system -c "select count(*) from oa.t03"|grep 500000 $  ksql oadb system -c "\di+ oa.idx_t03"|grep oa $  ksql oadb system -c "\dt+ oa.t03"|grep oa

EXAM06-DBA职业素养

1、请对DBA的职业素养进行描述（5分）

其他交付物

完成EXAM01-EXAM05考题后，需把linux虚拟机中下列文件找到，放入考生答题目录中。

（1）/etc/sysctl.conf；

（2）/etc/security/limits.conf；

（3）/data/kingbase.conf；

（4）/data/kingbase.auto.conf；

（5）/data/sys_hba.conf；