探究Oracle自动诊断库（ADR）的使用与管理

初始化参数

工作原理

Oracle数据库使用两种初始化参数文件来启动和配置数据库实例：

SPFILE（服务器参数文件）：二进制数据文件，支持读写操作，允许动态修改参数值，适用于持久性设置。
PFILE（参数文件）：文本文件，包含名称/值对，只支持只读操作，适用于静态设置。

在数据库启动时，系统会读取这些文件中的参数来配置数据库实例。

例子：

创建PFILE：
```
CREATE PFILE FROM SPFILE;
```
使用PFILE启动数据库：
```
STARTUP PFILE='/path/to/init.ora';
```

修改初始化参数

为了设置容量限制或提高性能，可以使用Enterprise Manager或SQL*Plus来修改参数，常用命令包括ALTER SESSION和ALTER SYSTEM。

查询初始化参数：

通过查询V$PARAMETER视图，可以确定参数是否可以在会话级别、系统级别或PDB级别进行修改。

会话级别更改（查询ISSES_MODIFIABLE列）
系统级别更改（查询ISSYS_MODIFIABLE列）
PDB级别更改（查询ISPDB_MODIFIABLE列）

查询参数可修改性：

SELECT name, isses_modifiable, issys_modifiable, ispdb_modifiable
FROM v$parameter
WHERE name = 'open_cursors';

修改系统级参数：
```
ALTER SYSTEM SET open_cursors = 300 SCOPE = BOTH;
```
- SCOPE = MEMORY：只修改内存中的值，重启后失效。
- SCOPE = SPFILE：只修改SPFILE中的值，需重启生效。
- SCOPE = BOTH：同时修改内存和SPFILE中的值，立即生效且持久。

使用自动诊断库（ADR）

概述

ADR是一个数据库外部的文件系统，用于系统范围的中央跟踪和记录。它存储了以下数据库诊断数据：

跟踪文件（Traces）
警报日志（Alert log）
健康监控报告（Health monitor reports）

ADR的主要作用是收集和管理诊断数据，以便于数据库管理员快速定位和解决问题。

查看警报日志

警报日志记录了关于数据库实例和数据库的消息，包括非默认初始化参数、内部错误、管理操作和各种错误信息。

查找警报日志位置：

SELECT * FROM v$diag_info WHERE name = 'Diag Trace';

查看警报日志内容：
```
more alert_<SID>.log
```

管理DDL日志文件

启用DDL日志记录功能可以捕获所有DDL语句并记录到日志文件中。通过设置参数ENABLE_DDL_LOGGING为TRUE来启用此功能。

启用DDL日志记录：

ALTER SYSTEM SET enable_ddl_logging = TRUE;

查看DDL日志：

more $ORACLE_BASE/diag/rdbms/<dbname>/<SID>/log/ddl.log

查询动态性能视图

动态性能视图提供对实例内存结构变化状态的信息访问，例如：

会话、文件状态和锁定
任务进度
备份状态、内存使用情况和分配
系统和会话参数
SQL执行和统计信息

查询当前会话：

SELECT * FROM v$session WHERE machine = 'EDXX9P1' AND logon_time > SYSDATE - 1;

数据字典概述

数据字典存储在SYSTEM表空间中，包含了数据库的元数据。这些元数据包括表、索引、视图、用户、模式、过程等。

查询数据字典：
```
SELECT * FROM dictionary;
```
### 详细举例
### 1. 会话、文件状态和锁定

会话（Session）

会话指的是数据库用户连接到数据库服务器的实例。会话的信息可以从V$SESSION视图中查询。

示例：

SELECT sid, serial#, username, status, osuser, machine
FROM v$session;

该查询返回当前连接到数据库的会话的信息，包括会话ID（sid）、序列号（serial#）、用户名（username）、状态（status）、操作系统用户（osuser）和机器名（machine）。

文件状态（File Status）

文件状态指的是数据库文件的当前状态，可以从V$DATAFILE视图中查询。

示例：

SELECT file#, name, status
FROM v$datafile;

该查询返回数据库中数据文件的信息，包括文件编号（file#）、文件名（name）和文件状态（status）。

锁定（Lock）

锁定是为了确保数据的一致性和完整性，对数据库资源进行的保护措施。锁的信息可以从V$LOCK视图中查询。

示例：

SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock;

该查询返回当前锁的信息，包括会话ID（sid）、锁类型（type）、资源标识符（id1和id2）、锁模式（lmode）、请求的锁模式（request）和是否被阻塞（block）。

任务进度

任务进度指的是数据库中后台进程或用户进程的当前执行状态。可以从V$SESSION_LONGOPS视图中查询长时间运行的操作的进度。

示例：

SELECT sid, serial#, opname, target, elapsed_seconds, time_remaining
FROM v$session_longops;

该查询返回长时间运行的操作的信息，包括会话ID（sid）、序列号（serial#）、操作名（opname）、目标（target）、已运行时间（elapsed_seconds）和剩余时间（time_remaining）。

备份状态、内存使用情况和分配

备份状态（Backup Status）

备份状态可以从V$BACKUP视图中查询。

示例：

SELECT file#, status
FROM v$backup;

该查询返回数据库中每个数据文件的备份状态，包括文件编号（file#）和备份状态（status）。

内存使用情况和分配（Memory Usage and Allocation）

内存使用情况和分配可以从V$SGA和V$PGA视图中查询。

示例：

SELECT * FROM v$sga;

该查询返回系统全局区（SGA）的内存使用情况。

SELECT * FROM v$pga;

该查询返回程序全局区（PGA）的内存使用情况。

系统和会话参数

系统和会话参数是Oracle数据库的配置参数，可以从V$PARAMETER视图中查询。

示例：

SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter;

该查询返回数据库参数的信息，包括参数名（name）、参数值（value）、是否为默认值（isdefault）、是否可以在会话级别修改（isses_modifiable）和是否可以在系统级别修改（issys_modifiable）。

SQL执行和统计信息

SQL执行和统计信息可以从V$SQL和V$SQLAREA视图中查询。

示例：

SELECT sql_id, executions, elapsed_time, cpu_time
FROM v$sql;

该查询返回每个SQL语句的执行信息，包括SQL ID（sql_id）、执行次数（executions）、运行时间（elapsed_time）和CPU时间（cpu_time）。

数据字典

数据字典存储在SYSTEM表空间中，包含了数据库的元数据。元数据是关于数据库结构和对象的信息，例如表、索引、视图、用户、模式和过程等。

表（Tables）

表是数据库中的基本存储单位，用于存储数据。

示例：

SELECT table_name
FROM all_tables
WHERE owner = 'SCOTT';

该查询返回用户SCOTT拥有的所有表的表名。

索引（Indexes）

索引是为了提高查询性能而创建的数据库对象。

示例：

SELECT index_name, table_name
FROM all_indexes
WHERE table_owner = 'SCOTT';

该查询返回用户SCOTT所有表的索引信息，包括索引名（index_name）和表名（table_name）。

视图（Views）

视图是一个虚拟表，通过SQL查询定义。

示例：

SELECT view_name
FROM all_views
WHERE owner = 'SCOTT';

该查询返回用户SCOTT拥有的所有视图的视图名。

用户（Users）

用户是数据库的访问主体，有自己的权限和模式。

示例：

SELECT username
FROM all_users;

该查询返回数据库中的所有用户。

模式（Schemas）

模式是用户拥有的数据库对象的集合。

示例：

SELECT username
FROM dba_users;

该查询返回数据库中所有模式的用户名。

过程（Procedures）

过程是存储在数据库中的一组PL/SQL语句，用于完成特定任务。

示例：

SELECT object_name
FROM all_procedures
WHERE owner = 'SCOTT';

该查询返回用户SCOTT拥有的所有存储过程的名称。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

**全网第一个AI+PPT提效小册希望能对大家有帮助订阅之后有专属学习交流群
以及AI考试资料分享**

本文由mdnice多平台发布