数据库——数据库管理、维护及优化

1 实验目的

1. 掌握系统权限、对象权限的授予和回收；

2. 掌握使用数据泵的方法来备份及还原数据库；

3. 熟悉利用Oracle AWR 报告分析数据库性能。

2 实验内容

利用 DBA 用户进行 Oracle 数据库的系统权限、对象权限管理，利用数据泵
工具进行数据库数据的备份及恢复。生成 Oracle AWR 报告，并观测报告中的各
项参数，以此来进行数据库优化。

3 实验软件

Oracle 数据库、SQL Developer

4 实验步骤

4.1 权限管理

使用Oracle用户登录进入CentOS系统桌面，用sys账户进入sqlplus。

1. 创建一个用户口令认证的数据库用户usera_exer，口令为usera，默认表空间为USERS，默认临时表空间为TEMP，USERS表的配额为10MB，初始状态为锁定状态。

CREATE USER usera_exer 

IDENTIFIED BY usera

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA 10 M ON USERS

ACCOUNT LOCK;

2. 创建一个口令认证的数据库用户userb_exer，口令为userb，默认表空间为USERS，默认临时表空间为TEMP，USERS表的配额为10MB，初始状态为默认状态。

CREATE USER userb_exer 

IDENTIFIED BY userb

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA 10 M ON USERS;

3. 为usera_exer用户授权CREATESESSION系统权限、SCOTT.EMP的SELECT和UPDATE对象权限，同时允许该用户将获得的权限授予其他用户。记录使用的系统权限授权命令、对象权限授权命令，以及它们的执行结果。

GRANT CREATE SESSION

TO usera_exer

WITH ADMIN OPTION;

GRANT SELECT,UPDATE ON SCOTT.EMP

TO usera_exer

WITH GRANT OPTION;

4. 将用户usera_exer的账户解锁。记录使用的修改用户命令及其执行结果。

ALTER USER usera_exer ACCOUNT UNLOCK;

5. 用usera_exer账户登录数据库（conn usera_exer/usera），查询SCOTT.EMP中的数据。记录使用的查询命令，并记录查询获得的信息。

SELECT * FROM SCOTT.EMP;

6. 将SCOTT.EMP的SELECT权限和UPDATE权限授予用户userb_exer，但禁止用户userb_exer将获得的权限再授予其他用户。记录使用的对象授权命令及其执行结果。

GRANT SELECT,UPDATE ON SCOTT.EMP

TO userb_exer;

7. 用sys账户登录数据库（conn /as sysdba），禁止用户usera_exer将获得的CREATE SESSION系统权限再授予其他用户。记录使用的系统权限回收命令、系统权限授权命令，以及它们的执行结果。

REVOKE CREATE SESSION FROM usera_exer;

GRANT CREATE SESSION

TO usera_exer;

8. 禁止用户usera_exer将获得的SCOTT.EMP的SELECT和UPDATE对象权限再授予其他用户。记录使用的对象权限回收命令、对象权限授权命令，以及它们的执行结果。

REVOKE SELECT,UPDATE ON SCOTT.EMP FROM usera_exer;

GRANT SELECT,UPDATE ON SCOTT.EMP

TO usera_exer;

9. 创建角色rolea（口令为rolea）和roleb（口令为roleb），将CREATE TABLE权限、SCOTT.EMP的INSERT和DELETE权限授予rolea；将CONNECT和RESOURCE角色授予roleb。记录使用的创建角色命令、授权命令，以及它们的执行结果。

CREATE ROLE rolea IDENTIFIED BY rolea;

GRANT CREATE TABLE TO rolea;

GRANT INSERT,DELETE ON SCOTT.EMP TO rolea;

CREATE ROLE roleb IDENTIFIED BY roleb;

GRANT CONNECT,RESOURCE TO roleb;

10. 将角色rolea和roleb授予用户usera_exer。记录使用的角色授权命令及其执行结果。

GRANT rolea,roleb TO usera_exer;

4.2 数据库备份

1. 将权限 CREATE SESSION、CREATE TABLE、EXP_FULL_DATABASE、IMP_FULL_DATABASE 授予用户userb_exer。记录使用的授权命令及其执行结果。

GRANT CREATE SESSION,CREATE TABLE,EXP_FULL_DATABASE,IMP_FULL_DATABASE TO userb_exer;

2. 使用Oracle 用户在/home/Oracle_11g 下创建一个目录 imp_dir。

3. 用sys 账户进入 sqlplus，定义刚创建的 imp_dir 目录。

4. 查看Oracle 数据库的directory，记录查询结果。

5. 将 imp_dir 目录的读写权限授权给userb_exer 用户。

6. 退出 sqlplus，在 oracle 用户下，使用 userb_exer 用户用 expdp 备份 scot用户的所有表和所有数据，记录执行结果和dmp 和 log 文件。

4.3 生成AWR 报告

用sys 账户进入sqlplus，运行脚本 awrrpt.sql 产生整个数据库的AWR 报告。

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

要求：

1. 输出报告的文件格式为html

2. 报告的监控天数为 1 天

3. 报告针对的起始快照号和终止快照号包含最多 5 个最新快照

AWR 报告生成后，退出 sqlplus，用浏览器打开生成在当前目录下的 AWR报告文件，查看并截图记录能反映当前数据库性能的报告数据。

5 思考题

1. 创建一个以自己姓名命名的数据库账户并授予对表的操作权限，可以对该表数据进行查询、更新、删除等操作，操作完成后收回对表的操作权限，再尝试对表数据重复之前的操作。截图记录所有操作命令及其结果。

首先创建用户，设置基本参数，账户为非锁定状态，随后授予角色登录的系统权限和对用户STU2019010801003下的EMP表的SELECT,UPDATE,DELETE,INSERT操作权限，命令和运行结果如下所示：

CREATE USER LCR IDENTIFIED BY 20010507

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA 10 M ON USERS

ACCOUNT UNLOCK;

GRANT CREATE SESSION TO LCR;

GRANT SELECT,UPDATE,DELETE,INSERT ON STU2019010801003.EMP TO LCR;

随后使用LCR用户建立连接并登录，对EMP表进行插入，查询，删除：

INSERT INTO STU2019010801003.EMP VALUES ('1111', '2222', 'CLERK', '3333', TO_DATE('2021-12-06', 'yyyy-MM-dd'), '1300', null, '10');

SELECT * FROM STU2019010801003.EMP;

DELETE FROM STU2019010801003.EMP WHERE(EMPNO=1111);

其结果如下所示：

而后回收LCR用户对EMP表的权限：

REVOKE SELECT,UPDATE,DELETE,INSERT ON STU2019010801003.EMP FROM LCR;

回收权限成功：

而后再用LCR用户对EMP表进行操作则报错：

2. 分析 Oracle AWR 报告（文件 awrrpt_1_51326_51330.html）中反映当前数据库性能的报告数据，判断当前数据库是否存在性能瓶颈，以及当前数据库需要优化的选项。

CPU负载=DB TIME/（CPU数*ELAPSED）100% 计算：CPU负载= 232.95/（1640.29）=36%，说明目前CPU的负载为36%，可不考虑增加CPU。

Redo size的Per Second>Per Transport，说明commit次数很多，需要优化程序执行的SQL语句。

Logical reads为56621.1 ，而Executes 为569.7 56621.1/569.7=99.39说明需要优化程序执行的SQL语句。

共享池内存使用率为55%以内，说明共享池设置过大，可以减小共享池大小。

db file sequential read比较严重，可对热区大表进行分区进行优化。

存在Log File Sync事件，可启用多个LGWR进程，或将LOG文件移动到固态硬盘上进行优化。

存在enq:TX-index contention事件，是由于并发量较高造成。该部分可以采用定期将热表rebuild，并加大索引的pctfree进行优化。

6 总结及心得体会

在本次实验中，我们进行了用户和角色的创建，对他们进行了权限的授予和回收等权限管理，了解了数据库中常见的用户和角色的创建命令和常用的权限类型。掌握了用户权限的管理方法。

随后我们进行了数据泵对数据库进行备份和导出，以及使用AWR报告来分析数据库性能，对数据库的备份方法和数据库的运行性能分析方法有了一定的了解。收获良多。