大型数据库笔记

环境配置

登录oracle(Oracle已经安装)：sqlplus sys/orcl as sysdba

sqlplus scott/tiger

(以超级管理员登录必须加as sysdba)

1
sz [文件名]
  • 切换用户：su - oracle （-和oracle中间有空格）
  • 运行设置Oracle环境shell脚本：. oraenv （. 后面有空格）
  • 注释：. = source(执行 shell 文件)
  • 输入实例名：orcl 按回车

su -xxx，-xxx是运行脚本

image-20230915001830676

sys用户 dba登录

01体系结构

OracleDB体系结构

image-20230914150100943

内存结构

Oracle DB 内存结构

Oracle DB 创建并使用内存结构来满足多种需要。例如，使用内存来存储正在运行的程序代码、在各用户之间共享的数据以及所连接的每个用户的专用数据区域。
一个实例有两个关联的基本内存结构：

•系统全局区 (SGA)：一组共享的内存结构（称为 SGA 组件），其中包含一个 Oracle DB 实例的数据和控制信息。SGA 由所有服务器进程和后台进程共享。SGA 中存储的数据有高速缓存的数据块和共享 SQL 区域等。

•程序全局区 (PGA)：包含某个服务器进程或后台进程的数据及控制信息的内存区域。PGA 是 Oracle DB 在服务器进程或后台进程启动时创建的非共享内存。服务器进程对 PGA 的访问是独占式的。每个服务器进程和后台进程都具有自己的 PGA。

image-20230914150838773

SGA

共享池

数据缓冲区

重做日志缓冲区

后台进程

进程监视器进程 (PMON)

进程监视器进程 (PMON) 在用户进程失败时执行进程恢复。PMON 负责清除数据库缓冲区高速缓存和释放该用户进程占用的资源。例如，PMON 会重置活动事务处理表的状态，释放锁，并从活动进程列表中删除该进程 ID。

PMON 定期检查分派程序和服务器进程的状态，并重新启动任何已停止运行（除了 Oracle DB 故意终止）的分派程序和服务器进程。PMON 还会在网络监听程序中注册有关实例和分派程序进程的信息。

与 SMON 一样，PMON 定期检查以查看是否需要运行；如果其它进程检测到需要该进程，也可以调用它。

image-20230914161215340

**恢复器进程 (**RECO)

恢复器进程 (RECO) 是一个用于分布式数据库配置的后台进程，它可以自动解决涉及分布式事务处理的故障。实例的 RECO 进程会自动连接到有问题的分布式事务处理中涉及的其它数据库。当 RECO 进程在涉及到的数据库服务器之间重新建立连接后，它会自动解决所有有问题的事务处理，并从每个数据库的暂挂事务处理表中删除所有对应于已解决的有问题事务处理的行。

如果 RECO 进程无法与远程服务器连接，RECO 会在某个计时间隔之后自动尝试重新连接。但是，RECO 在再次尝试另一个连接之前，会等待一段时间，该时间会随尝试次数不断
增加（呈幂指数增长）。

image-20230914161228190

归档进程 (ARCn)

发生日志切换之后，归档进程 (ARCn) 会将重做日志文件复制到指定的存储设备。仅当
数据库处于 ARCHIVELOG 模式且已启用自动归档时，才会存在 ARCn 进程。

如果您预计归档的工作负荷很重（例如在成批加载数据期间），可以增加最大归档进程数。此外，也可以有多个归档日志目标位置。建议每个目标位置至少有一个归档进程。默认
设置是四个归档进程。

image-20230914161301300

归档模式

默认为非归档模式。

查看数据库是否处于归档模式：archive log list。

1
SQL> archive log list

改变数据库的归档模式步骤：

(1)停止Oracle Server并装载数据库： 

1
2
3
sql>shutdown immediate //停

sql>startup mount  //启动到：装载数据库阶段

(2)修改数据库为归档模式： 

1
alter database archivelog;

(3) 打开数据库：alter database open;

测试是否生成归档日志：

（1）update emp表（生成重做日志信息）:

• update scott.emp set sal=sal + 500;

• commit;

（2）日志切换（生成归档日志）： alter system switch logfile;

（3）检查是否生成归档日志:

在文件夹/opt/oracle/flash_recovery_area下查看是否有归档文件生成。

改为非归档模式：

Mount状态：

alter database noarchivelog;

打开数据库：alter database open;

逻辑和物理数据库结构

数据库具有逻辑结构和物理结构。

数据库、表空间和数据文件

本幻灯片对数据库、表空间和数据文件之间的关系进行了说明。每个数据库都在逻辑上分为两个或多个表空间。在每个表空间均显式创建一个或多个数据文件，以在物理上存储表空间中所有逻辑结构的数据。对于 TEMPORARY 表空间，不创建数据文件，而是创建临时文件。表空间的数据文件可以采用任何受支持的存储技术进行物理存储。

表空间

数据库分为多个逻辑存储单元，这些单元称为“表空间”，用于对相关逻辑结构或数据
文件进行分组。例如，表空间一般会将一个应用程序的所有段分成一组，以简化一些管理操作。

数据块

Oracle DB 的数据存储在“数据块”中，数据块是粒度最低的一级。一个数据块对应于磁盘中特定字节数的物理空间。每个表空间的数据块大小是在表空间创建时指定的。数据库以 Oracle 数据块为单位使用和分配空闲数据库空间。

自动存储管理不需要做，实验室磁盘太少

04实例

spfile：/opt/orcale/product

图形界面 管理 服务器

控制台地址可以在此文件中查看：$ORACLE_HOME\install\readme.txt

控制台的操作：

查看状态： emctl status dbconsole

启动：emctl start dbconsole

停止（不要执行停止这条命令）：emctl stop dbconsole

改显示日期

1
alter session set nls_date_format='yyyy-mm-dd';

V$PARAMETER 显示当前会话中的当前参数值

1
2
3
4
5
6
7
SQL> SELECT name , value FROM V$PARAMETER;
NAME                   VALUE
------------          ----------
lock_name_space        2
processes              150
sessions               247
…

MOUNT阶段

  • 执行以下任务必须装载数据库
    • 重命名数据文件
    • 启用和禁用联机重做日志文件归档选项
    • 执行完整的数据库恢复

OPEN阶段

image-20231007142725996

image-20231007144423909

数据字典视图

image-20231007145236364

用法举例：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT table_name, tablespace_name 
FROM user_tables;

SELECT sequence_name, min_value, max_value, increment_by 
FROM all_sequences 
WHERE sequence_owner IN ('MDSYS','XDB');

SELECT USERNAME, ACCOUNT_STATUS 
FROM dba_users 
WHERE ACCOUNT_STATUS = 'OPEN';

DESCRIBE dba_indexes

查找scott用户有哪些索引，以及这些索引所在的表空间名称:

1
2
3
4
select table_name from dict where table_name like '%INDEX%’;

Desc user_indexes select  INDEX_NAME, TABLESPACE_NAME from user_indexes;

06配置Oracle Network

vim保存失败，修改文件权限可以用winscp

07Storage

08User

权限

  • 系统权限：允许用户在数据库中执行特定的操作
  • 对象权限：允许用户访问和操纵特定的对象

dba分配权限

1
sqlplus / as sysdba

查权限

1
select * from user_role_privs;

系统权限

所有系统权限：

1
select * from system_privilege_map;

授予系统权限：

1
2
3
GRANT <system_privilege> TO <grantee clause> [WITH ADMIN OPTION] 
# Grant create table, unlimited tablespace to scott with admin option;

WITH ADMIN OPTION: 被授权用户可以转授权给其它用户，收回权限没有级联，慎用

查询用户具有的系统权限： 

1
select * from user_sys_privs;

撤销系统权限的 SQL语句：

1
2
REVOKE <system_privilege> FROM <grantee clause>
# REVOKE CREATE TABLE FROM joe;

对象权限

授予对象权限

1
GRANT <object_privilege> ON <object> TO <grantee clause> [WITH GRANT OPTION]

查看用户对象权限：

1
select * from user_tab_privs;

撤销对象权限时会级联撤销。

•用户只能撤销授予他们的那些权限。例如，Bob 无法撤销 Joe 授予 Emily 的对象权限。

•只有被授予者或者具有 GRANT ANY OBJECT PRIVILEGE 系统权限的用户可以撤销对象权限。

09Locking

锁

  • 防止多个会话同时更改同一数据
  • 在指定语句的最低可能级别自动获取

机制

  • 插入、更新、删除：行级锁
  • 修改对象（如表移动）：对象级锁
  • 查询不需要任何锁

事务处理结束（使用 COMMIT 或 ROLLBACK 操作）前一直保持锁定

10Undo

还原数据

还原数据是：

  • 原始的、修改之前的数据副本

  • 针对更改数据的每个事务处理而捕获，保存旧值（还原数据）

  • 至少保留到事务处理结束

  • 用于支持：

    • 回滚操作 rollback
    • 读取一致性查询 ：提供与查询开始时的数据一致的结果；“快照太旧” (ORA-01555)
    • Oracle 闪回查询、闪回事务处理和闪回表
    • 从失败的事务处理中进行恢复

还原信息：至少保留到事务处理结束

image-20231109142603647

  • 每个事务处理仅分配一个还原段。
  • 还原段可根据需要自动增长和收缩
  • 一个还原段可以同时服务多个事务处理。

还原信息分为三类

  • 未提交的还原信息（活动）
  • 提交的还原信息（未过期）：
  • 过期的还原信息（过期）：

11Auditing

指定审计选项,每次行使审计的系统权限时，都会生成一条记录

image-20231116143337623

设计触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE TRIGGER system.hrsalary_audit 
AFTER UPDATE OF sal
ON scott.emp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :old.sal != :new.sal THEN
INSERT INTO system.audit_employees 
VALUES 
  (
    sys_context('userenv', 'os_user'), 
    sysdate, 
    sys_context('userenv', 'ip_address'),
    :new.empno || ' salary changed from '||:old.sal|| ' to ' ||:new.sal); END IF; END; /
1
2
3
4
5
6
7
exec dbms_fga.add_policy(
  object_schema =& gt; 'scott', 
  object_name =& gt; 'emp',  
  audit_condition =& gt; 'MGR=7566', 
  audit_column =& gt; 'comm', 
  enable =& gt; TRUE, 
  statement_types =& gt; 'SELECT,UPDATE’);

UPDATE scott.emp
SET comm = 1000
WHERE ENAME = ‘FORD’;

基础

打开管理界面

1
2
3
4
su - oracle
. oraenv
orcl
emctl start dbconsole

360极速浏览器打开

https://192.168.126.151:5500/em

Gauss安装

文件查找，非编辑状态：”/查找内容”