数据库-Oracle(三)

Oracle 运维

更改参数

  更改参数可以使用：

• ALTER SYSTEM
  系统级别的更改

• ALTER SESSION
  会话几倍的更改

ALTER SYSTEM
 的语法如下：

ALTER SYSTEM SET = SCOPE=MEMORY | SPFILE | BOTH

• SCOPE
  ：用于确定实在哪里进行修改。默认为 BOTH
  ，即应用于运行着的实例并写入 spfile。

• MEMORY
  ：修改内存中此参数的值。这个修改不是永久的，数据库关闭并重新启动，值会变为默认值。

• SPFILE
  ：修改 spfile 文件中此参数的值。这个修改是永久的。更改静态参数（也就是启动实例后不可更改，固定下来的参数）必须指定 SCOPE=SPFILE
  。

启动和关闭数据库实例

启动的三个阶段（启动和关闭需要sysdba 权限）

• SHUTDOWN
  ：关闭与数据库相关的所有文件，实例不存在。

• NOMOUNT
  ：根据参数文件的定义在内存中构建实例。实例启动。

• MOUNT
  ：读取控制文件（由 control_files
  参数定位）。此时数据库是关闭的。

• OPEN
  ：打开数据库。

查看日志

• 警报日志

警报日志由 DIAGNOSTIC_DEST
 参数确定，它提供数据库消息和错误信息（按时间排序）。下面命令查看警报日志的位置：

select name,value from v$spparameter where name='diagnostic_dest';

警报日志默认存储位置:

DIAGNOSTIC_DEST/diag/rdms///trace

• DDL 日志

DDL 日志记录的是DDL 命令，默认是停止的。开启需修改如下参数:

--启用

SQL> alter system set enable_ddl_logging=TRUE;

--查看其值

SQL> select name,value from v$parameter where name='enable_ddl_loggin
:

DLL 文件默认位置:

DIAGNOSTIC_DEST/diag/rdbms/<dbname>/<instancename>/log

创建用户

 在创建用户的时候通常会指定如下信息：

• 用户名

• 用户名需要遵守的规定：    

• 由字母
  ，数字
  ，_
  ，$
  ，#
  组成。

• 以字母开头，不能是 Oracle 中的关键字。

• 变量的长度最多为 30 个字符。

• 字母区分大小写，但所有字母自动转换为大写。

• 验证方式

• 默认表空间

• 临时表空间或临时表空间组

• 当前用户使用表空间的最大值

• 用户配置文件

• 用户状态

create user [用户名] identified by [密码]

用户权限管理

为了保证数据库的数据安全，必然会控制每个用户的权限。权限分为两种：

• 系统权限：指系统级操作的权限，通常是影响数据字典的操作。

• 对象权限：对数据库中对象的操作，通常是影响数据的操作。

系统权限

对象权限

对象权限也有很多，对象权限是对对象授权，也就是表，视图等。了解全部对象权限可参见

角色管理

角色不属于用户，它是独立的。角色实际上是一组权限。

当我们要授予很多用户相同的权限，如果对一个一个用户去授权，工作量就会很大，而直接创建一个包含一组权限的角色，给用户赋予这个角色就显得轻松许多。

另一方面，当我们需要改变一个用户权限的时候，使用直接授权的方式，依然很麻烦。而直接更改用户的角色就很容易。就像为一个员工安排职位一样，不同的职位拥有不同的权限，要更改用户的权限，直接改变员工的职位。

概要文件

create
profile pwd_time
--定义概要文件名称为 pwd_time

limit
failed_login_attempts
3
--限制连续错误次数

password_lock_time
1
;
--限制锁定账户天数

备份

备份包括逻辑备份和物理备份：

• 逻辑备份是一个导出的操作，它会去查询数据库对象（比如表，用户，存储过程等），然后将创建数据对象和数据的命令写到一个导出转储文件中，要恢复时直接执行导入，就回去读取转储文件中的命令并执行。

• 物理备份是转储的实际文件，比如控制文件，数据文件等等。

逻辑备份与恢复使用的是 Data Pump Export
 和 Data Pump Import
 工具

逻辑备份步骤:

• 创建一个目录用来存放日志和转储文件。（也可不创建，直接使用这个目录 $ORACLE_BASE/admin/<database_name>/dpdump）

• 在 Oracle 内创建一个指向该目录的指针 CREATE DIRECTORY [名称] as '<oracle_base>/admin/xe/dpdump';
  

• 使用expdp备份，需在linux中执行$ expdp system/Syl12345 tables=student directory=dpd dumpfile=exp_student.dmp
  

• system/Syl12345
  是执行备份的用户名和密码。

• tables
  是指定要备份的表。

• directory
  是指定备份存放的位置。如果不指定参数，则会存放到默认位置 DATA_PUMP_DIR
  ，可以使用 select * from dba_directories where directory_name='DATA_PUMP_DIR';
  查询到。

• dumpfile
  是指定备份的文件名。

导入操作

• linux 中执行$ impdp system/Syl12345 remap_table=student:studentbak directory=dpd dumpfile=exp_student.dmp
  

物理备份包含脱机备份和联机备份：

• 脱机备份：在关闭数据库后对数据库文件进行备份

• 联机备份：在数据库启动的情况下对数据库文件进行备份。此时数据库处于归档日志模式（也就是 ACHIVELOG
  ）模式。

我们通常使用 RMAN
工具在归档日志模式下进行备份。

使用RMAN 进行备份步骤:

• 更改时间格式，精准到秒:
  
  

• $ export NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:s'
  

  
  

• 切换到DBA 角色 

• 查询当前的归档模式 select name,log_mode from v$database;
  

• 切换到归到日志模式的步骤

• 关闭数据库 shutdown immediate;
  

• 启动数据库到 MOUNT
  阶段 startup mount;
  

• 切换为归档日志模式 alter database archivelog;
  

• 打开数据库  alter database open;
  

• 查看归档模式 archive log list;
  

• 查询归档日志所在目录  select dest_name,destination from v$ARCHIVE_DEST where dest_name='LOG_ARCHIVE_DEST_1';
  

• 登入目标数据库实列 $rman target
  

配置RMAN 

• 配置保留备份的时间

配置RMAN 不会废弃3天内的数据文件和归档日志重做日志备份

RMAN> configure retention policy to recovery window of 3 days;

• 配置并行度

RMAN> configure device type disk parallelism 2 backup type to compressed backupset;

• parallelism
  后面的数字 2
  代表使用 2 个通道并行执行备份操作，这样提高了性能。

• compressed
  代表压缩备份集，可节省空间。

• 配置 RMAN 备份文件的存储位置

• RMAN> configure channel 1 device type disk format '/u01/backup1_%U.bak';

• RMAN> configure channel 2 device type disk format '/u01/backup2_%U.bak';

• 配置自动删除归档重做日志

• RMAN> configure archivelog deletion policy to backed up 2 times to disk;
  

备份数据库

• 修改 u01 目录权限: sudo chown oracle u01
  

• 备份数据库:RMAN>backup database;
  

• 清除RMAN 配置:RMAN>configure device type disk clear;RMAN> configure device type disk clear; RMAN> configure channel 1 device type disk clear;
  

• 备份表空间:RMAN> backup tablespace [表空间名称];
  

增量备份

增量备份就是先创建一个初始备份文件，这个创建操作被称为 0 级备份，以后的备份操作（1 级备份）只是在初始备份文件的基础上备份了改变了的数据块。

• 0级备份:RMAN> backup incremental level 0 tablespace syltp1;

• 1级备份:RMAN> backup incremental level 1 tablespace syltp1;

不完全恢复

• 创建还原点

• 查询当前的SCN:RMAN>select current_scn from  v$database;
  

• 创建还原点:RMAN> create restore point syl_res_scn;
  

• 查询还原点:RMAN>select name,scn from v$restore_point;
  

• 关闭数据库 RMAN>shutdown immediate;
  

• 装载数据库 RMAN>startip mount;
  

• 恢复到还原点 

• RMAN> restore database until restore point syl_res_scn;
  

• RMAN> recover database until restore point syl_res_scn;
  

• 打开数据库

• 使用resetlogs打开数据库，重建联机重做日志

RMAN>alter database open resetlogs;

性能诊断

AWR

AWR
 （Automatic Workload Repository），也就是自动工作负载信息库，它是 SYSAUX
 表空间中的一组表。Oracle 会自动收集与性能有关的统计信息写到 AWR 中。

查看收集级别 show parameter statistics_level;

可以看到它的值默认是 TYPICAL
，确保收集数据库自我管理功能所需的所有主要统计信息，并提供最佳的整体性能。一般默认即可。

有几个与 AWR 相关的数据字典和动态性能视图：

• dba_hist_wr_control
  

• v$sysaux_occupants
  

• dba_hist_snapshot
  

可以看到它的值默认是TYPICAL
，确保收集数据库自我管理功能所需的所有主要统计信息，并提供最佳的整体性能。一般默认即可。详细了解可参考 STATISTICS_LEVEL
。有几个与 AWR 相关的数据字典和动态性能视图：dba_hist_wr_controlv$sysaux_occupantsdba_hist_snapshot

查询命令:

select snap_interval,retention from dba_hist_wr_control;

查看其占用的空间，单位是KB

select space_usage_kbytes from v$sysaux_occupants where occupant_name='SM/AWR';

快照时间、快照数量查询:

desc dba_hist_snapshot;--快照时间、快照数量

select min(begin_interval_time),max(begin_interval_time),count(snap_id) from dba_hist_snapshot;

生成AWR 报告

生成 AWR 报告，需要用到 $ORACLE_HOME/rdbms/admin
 里的 awrrpt.sql
 这个脚本。要生成 AWR 报告，直接使用如下命令调用，它会问你想要什么类型的报告，想查看最近几天的快照等等。根据你的需要输入即可。

查询命令:@?/rdbms/admin/awrrpt.sql

ASH

AWR 快照获取的频率低，或许有时候并不能获取到你想要的信息，这个时候可以使用 ASH（Active Session History），它收集到的信息比 AWR 更多，因为它是每秒从 v$session
中取样，收集了更多有关会话的信息。它有两个相关的数据字典视图：

• v$active_session_history
  

• dba_hist_active_sess_history
  

最简单的获取报告的方式还是运行 Oracle 提供的 $ORACLE_HOME/rdbms/admin/ashrpt.sql
这个脚本。使用如下命令便可调用，像生成 AWR 报告一样，它也会提示一些相关问题

查询命令:@?/rdbms/admin/ashrpt.sql

ADDM

ADDM
（Automatic Database Diagnostic Monitor），也就是自动数据库诊断监视器。它可以分析 AWR 中的两个快照中的数据，生成一个报告，这个报告包含了这两个快照期间的性能问题以及缓解问题的建议。

生成ADDM报告

相关命令:@<ORACLE_HOME>/rdbms/admin/addmrpt.sql

@ 是用来调用 sql 脚本。

<ORACLE_HOME>
替换为你的 ORACLE_HOME
路径。

调用过后，会输出最近的一些快照信息。然后需要输入你想查看的快照范围，它会提示你输入开始的快照 id 和结束的快照 id 以及报告名，输入之后就可以生成一个报告出来了。

注意：有可能会断开实例或者报错 ORA-20200: The instance was shutdown between snapshots 114 and 148
。原因就是你输入的快照期间有重启过数据库，换下快照 id 就行了。

警报系统

警报系统是用来监控数据库的，你只用配置它要监控的指标和阈值，如果超过阈值，就会发送通知。配置阈值使用的是 DBMS_SERVER_ALERT
 这个包，它可以为大量预定义的指标设置警报阈值，你可以参考 DBMS_SERVER_ALERT 。

查询警报指标:

select * from v$metricname;

通常建议是表空间的用量不要超过 85%，我们这里设置一个警报，在超过 70% 时警报，在超过 80% 时严重警报。

查询表空间容量

select d.tablespace_name,sum(d.bytes)/1024/1024 "total(MB)",sum(f.bytes)/1024/1024 "free(MB)",round((sum(d.bytes) - sum(f.bytes))/sum(d.bytes),4)*100 "Used(%)" from dba_data_files d left outer join dba_free_space f on d.tablespace_name=f.tablespace_name group by d.tablespace_name;

或者:

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;

查询警报信息

select * from dba_thresholds where object_name='ALERTTEST';

配置警报信息

execute DBMS_SERVER_ALERT.SET_THRESHOLD(- metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,- warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,- warning_value => '70',

- critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,- critical_value => '80',- observation_period => 1,- consecutive_occurrences => 1,- instance_name => NULL,

- object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,- object_name => 'ALERTTEST')

SET_THRESHOLD
创建警报。

=>
是把右边的值传入左边的参数。这些参数的含义

内存优化

自动内存管理

Oracle 中提供了对 SGA 内存自动管理（AMM，Automatic Memory Mangement），但不包括日志缓冲区。一般建议配置自动 SGA 内存管理。使用了自动内存管理，我们就不用自己手动调优相关内存的一些初始化参数，Oracle 会自动根据性能报告对参数进行动态调优。

MEMORY_TARGET
是动态的但其大小不可超过 MEMORY_MAX_TARGET
，可以在不关闭实例的情况下对其进行调整。

使用自动内存管理有以下几步：

• 以 sysdba 身份登入实例

• 设置 MEMORY_TARGET 和 MEMORY_MAX_TARGET

• 重启数据库

计算 MEMORY_TARGET
参数最小应该设置的值

MEMORY_TARGET = SGA_TARGET + MAX(PGA_AGGREGATE_TARGET, MAXIMUM PGA ALLOCATED) --计算公式

设置 MEMORY_TARGET

alter system set memory_target=2048M scope=spfile;

设置 MEMORY_MAX_TARGET

MEMORY_MAX_TARGET 的值可以设置为大于或者等于 MEMORY_TARGET 的值，它表示 MEMORY_TARGET 所能设置的最大值。

alter system set memory_max_target=3072M scope=spfile;

最后，将 memory_target
 和 memory_max_target
 恢复为之前的初始值 0：

alter system set memory_target=0;

alter system set memory_max_target=0 scope=spfile;

PGA 优化

PGA
 保存用户的连接信息，还有为排序操作提供临时存储等作用。我们用一个假设来说明 PGA 对性能的影响：假如我们对 1000 行结果集使用 order by
 排序，每次放 100 行到 PGA 的排序区域进行排序，每次排完序会将已排序的行放到临时数据文件中，所以每次会产生一次 I/O，将 1000 行排序至少要产生 10 次 I/O。如果我们每次是放 200 行去排序的话，可想而知 I/O 至少会减少一半。

使用 PGA 顾问主要针对的是参数 PGA_AGGREGATE_TARGET
。使用 v$pga_target_advice
 这个 PGA 顾问，可以预计不同 PGA_AGGREGATE_TARGET
 下的性能情况，对我们的 PGA 优化有指导作用。

查看当前pga_aggregate_target 的值: show parameter pga_aggregate_target;

使用 PGA 顾问： select pga_target_for_estimate/1024/1024,pga_target_factor,estd_extra_bytes_rw,estd_overalloc_count from v$pga_target_advice;

每列解释：

查询结果解析：

pga_target_factor
为 1 的行是我们当前设置的 PGA_AGGREGATE_TARGET
情况。从对应的第一列的值 512 可以看出当前我们设置的是 512M，这个值与我们在之前查询的参数值一样。预估的磁盘 I/O 和过度分配内存数都为 0，不需要优化。

来看第一行，它表示设置 PGA_AGGREGATE_TARGET
为 64M，第二列说明这个预测值是我们当前设置值的百分之 12.5 。这行的 estd_overalloc_count
为 1，不为 0 了，说明 64M 不够大，我们不应设置 PGA_AGGREGATE_TARGET
为 64M。

SGA 优化

• 共享池 SHARED_POOL_SIZE
  ：存储最常提交的 SQL 语句或者 PL/SQL 块以加快解析过程

• 数据库缓冲区缓存 DB_CACHE_SIZE
  ：存储最常访问和修改的数据块以加快检索

• 大池 LARGE_POOL_SIZE
  ：用与 RMAN，共享服务器等

• 流池 STREAMS_POOL_SIZE
  ：存储 Oracle 流特性的数据和控制结构

• java 池 JAVA_POOL_SIZE
  ：存储用户会话使用的 java 代码

• 日志缓冲区 LOG_BUFFER
  ：临时存储提交给数据库的所有事务项

查看当前SGA_TARGET 值：show parameter sga_target

使用 v$sga_target_advice
 这个 SGA 顾问来预计查看SGA_TARGET
 下的性能情况：select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

每列解释：

查询结果解释：

同样 sga_size_factor 为 1 是代表的当前 SGA_TARGET 情况。可以看到对应的第 1 列是 1536M ，和我们之前查询的当前值一样。

第一行当 sga 为 384M 时，estd_db_time 是最小的。我们可以将 SGA_TARGET 参数设置为 384M。

修改sga_target 的值 alter system set sga_target=384M;

查看SGA 实际大小  select sum(bytes)/1024/1024 from v$sgastat;

数据库缓冲区缓存优化

性能指标

数据库缓冲区缓存的性能指标主要是命中率，命中率是指从数据库缓冲区缓存中读取的数据块的比例，命中率保持在 95% 以上较佳。

计算命中率我们需要用到 v$sysstat
 这个动态性能视图

命中率公式:(1-(从磁盘读取的总块数/(块获取数+一致性获取数)))*100 也就是 (1-(<physical reads> (<db block gets> + <consistent gets>)))*100

• 查询:从磁盘读取的总块数

select value from v$sysstat where name like 'physical reads';

• 查询:块获取数

select value from v$sysstat where name like 'db block gets';

• 查询:一致性获取数

select value from v$sysstat where name like 'consistent gets';

使用 v$db_cache_advice

使用 DB 缓存顾问可以评估 DB_CACHE_SIZE 对命中率的影响：

字段说明：

共享池优化

在共享池中有两个重要的缓存：

• 数据字典缓存：执行 SQL 语句时，Oracle 会用到数据字典，数据字典缓存用来缓存数据字典，以加快读取速度。

• 库缓存：缓存 SQL 语句，PL/SQL 语句块等等。

SQL 的执行过程:

执行 SQL 语句时，首先需要解析语句，会消耗一定时间，而共享池用来共享相同的 SQL 语句，执行语句时，首先会从共享 SQL 区域中寻找有没有此语句，如果有的话就直接执行，而不需要再次解析，从而节省了时间。

注意：相同 SQL 的含义是语句完全一致（包含大小写，空格数等等）。

数据字典缓存性能指标

查询数据库字典缓存的命中率:

select sum(gets),sum(getmisses),(sum(gets-getmisses-fixed)/sum(gets))*100 "HIT RATIO(%)" from v$rowcache;

v$rowcache 字段说明：

库缓存性能指标

库缓存性能指标有两个：

• 重载率

• 命中率

重载率：已经过时的语句重新加入内存的比率

查询库缓存的命中率:

select namespace,pinhits,pins,pinhitratio from v$librarycache;

查询结果解析：

从查询结果可看出重载率大约为 0.11%，说明有一些已经过时或失效的语句重新载入内存。如果重载率超过了 1%，我们就应增大 SHARED_POOL_SIZE
参数了。

从查询结果可看出命中率大约为 95%。命中率应保持在 95% 以上。如果低于 95%，就应增大 SHARED_POOL_SIZE
了。

v$librarycache 字段说明：

共享池空闲率

查询共享池空闲率：

select (select bytes from v$sgastat where name='free memory' and pool='shared pool')/(select sum(bytes) from v$sgastat where pool='shared pool')*100 "free(%)" from dual;

一般空闲率在 10% 到 20% 比较适当。在 20% 到 40% 可以接受。低于 10% 说明分配不足，可以增大 SHARED_POOL_SIZE
 。高于 40% ，说明过度分配了，可以减少 SHARED_POOL_SIZE
。

存储优化

创建具有最优性能的表空间

创建表空间时，使用本地管理和ASSM(自动段空间管理)

创建表空间

 create tablespace autoex datafile 'autoex.dbf' size 1m;

使用ASSM

select tablespace_name,extent_management,segment_space_management from dba_tablespaces where tablespace_name='AUTOEX';

行链接与行迁移

当我们在插入一行记录时，如果一个块的空闲空间不足以存放进这一行记录的话，Oracle 就会去链接一个或多个这个段中保留的块来存储这一行记录。这就是行链接
。

当一行原本存放在一个块中的记录，由于更新操作导致行长变大，而这个块已经容纳不下时，Oracle 会把这行记录存放到一个空间足够的新块中。这就是行迁移
。

可想而知，如果产生行链接或者行迁移是很消耗性能的，因为数据库需要扫描更多的块来获取某一行记录。

行链接和行迁移有两个相关的存储参数：

• PCTFREE
  ：为更新已存在的数据预留空间的百分比。

• PCTUSED
  ：用于插入数据的最小空间的百分比。

我们可以将块比作水杯，向块添加数据比作向水杯加水，假设 PCTFREE 设定是 20 ，则当我们将水添加到 80% 的时候，就不能再添加，剩余的 20% 是用来为更新数据预留的空间。假设 PCTUSED 设定的 60 ，则当我们将水喝到只剩 60% 的时候，就可以加水了。

检测行迁移和行链接

• 分析某张表

 analyze table [表名称] compute statistics;

• 查看分析出来的统计信息

select num_rows,blocks,empty_blocks,chain_cnt,round(chain_cnt/num_rows*100,2) "chain_pct(%)",pct_free from user_tables where table_name=[表名称];

chain_cnt
就是表中出现的行链接和行迁移的总和。这里是 0 说明此时并为发生行链接和行迁移。

chain_pct(%)
是发生行链接和行迁移的百分比。一般超过 15%
，就会存在问题。

解决行链接/行迁移问题

  解决行链接/行迁移的思路是创建一张临时表，把存在行链接/行迁移的数据行放进去，并把原表中存在行链接/行迁移的数据行删除，修改了 PCTFREE 后，再把临时表中的数据插入到原表中。这样做的好处是不会影响到原表中的其他数据。大致需要如下几个步骤：

• 调用 utlchn1.sql
  脚本将存在行链接/行迁移的数据行放入表 chained_rows
  中

• 创建一张临时表 rowtest_tmp
  来存放存在行链接的数据行

• 删除原始表 rowtest
  中存在行链接的数据行

• 重新定义 PCTFREE

• 清除碎片

• 将临时表中数据插入到原表中

清除碎片

之前的删除会产生一些碎片，在清除碎片之前，先来了解碎片是怎么产生的。Oracle 中存在一个高水位线（HWM，High Water Mark）的概念，它就像水杯上的一个水位线，当向杯子里加水到水位线时就不能再加，想要继续加得提高水位线，但不同的是当喝掉水时，水位线不会自己下降，在水位线和水面之间存在一段未使用的空间。在一些数据删除时，之前为这些数据分配的盘区并没有被删除，而是保留了下来，在高水位线下存在未使用的空间，在查询时也会扫描这些未使用的空间，导致查询缓慢，并且在插入数据时，这些由删除数据释放出来的空间也不会马上被使用，甚至永远不会被使用，这些空间是由很多空闲的块组成的，这些空闲的块就是碎片。

使用 autotrace
 工具可以判断是否有高水位线以下的未使用空间：

set autotrace trace statistics;

select * from rowtest;

返回的数据行数为 0，但是从缓冲区缓存中读取的数据块不等于0。表明很有可能存在高水位线下的未使用空间。

查询表 真正使用的数据块

select distinct count(distinct substr(rowid,1,15)) num from [表名称];

查询表的高水位线下所有的块数和高水位线上的空块数（blocks 是在高水位线下表中块的数目，empty_blocks 是高水位线上未使用的块的数目（也就是从来没有使用过的块）。

select blocks，empty_blocks from user_tables where table_name=[表名称];

• 释放高水位下的未使用空间

alter [表名称] rowtest move;

收缩表(另外一种方法)

alter table [表名称] enable row movement;

alter table [表名称] shrink space;

alter table [表名称] shrink space cascade;

• 将临时表中的数据写入原表

insert into rowtest select * from rowtest_tmp;

• 检查表情况

 analyze table rowtest compute statistics;

select num_rows,blocks,empty_blocks,chain_cnt,round(chain_cnt/num_rows*100,2) "chain_pct(%) 2  ",pct_free  from user_tables where table_name=[表名称];

• 将之前创建的 chained_rows 和 rowtest_tmp 表删除

drop table chained_rows;

drop table rowtest_tmp;