Oracle数据库日常工作
技术交流

文档历史

版 本 修改原因 修改人 基于版本 日 期
V1 新增 屈毅 无 2020-03-10
V2 修改 屈毅 V1 2021-03-31

一．概述

1. 本文编写目的

本文旨在通过本次技术交流，让大家多一些对oracle数据库的了解，互相学习，取长补短。

2. 本文适用范围

  1. 上海大区技术支持中心人员Oracle数据库相关日常工作。

二．基本概念

1 表空间

概念：
关于表空间，我们需要理解 table / segment / extent / block 和 AUTOEXTEND。
一个table至少是一个segment，如果分区表，则每个分区是一个segment,table可以看成是一个逻辑上的概念，segment可以看成是这个逻辑概念的物理实现；segment由一个或多个extents组成，segment不可以跨表空间但可以跨数据文件；extent由多个连续的blocks组成，不可以跨数据文件；block由1-多个os块组成，是oracle i/o的最小存储单位，其大小由参数【show parameter db_block_size / SELECT * FROM v$parameter WHERE NAME =’db_block_size’;】指定，默认为8K
类型：
SELECT CONTENTS FROM SYS.DBA_TABLESPACES GROUP BY CONTENTS;
1、永久性[PERMANENT]表空间：一般保存表、视图、过程和索引等的数据。
2、临时性[TEMPORARY]表空间：只用于保存系统中短期活动的数据。
3、撤销权[UNDO]表空间：用来帮助回退未提交的事务数据。

2 用户及权限

参考：<>
需要关注profile、quota、sysprivs、role_privs、tab_privs、session_privs、session_roles
/创建普通用户/
create user USER_QZJ_SJTS identified by “123456”
default tablespace HCRP
temporary tablespace TEMP
profile DEFAULT
quota 307200m on BSOFT; —有限表空间配额
grant CREATE SESSION to USER_QZJ_SJTS;
grant select, insert on HCRP_QP.TEMP_TAB_001 to USER_QZJ_SJTS;
/查询用户拥有的系统、角色、对象权限/
select from user_sys_privs;
select from user_role_privs;
select * from user_tab_privs;
/查询当前会话可以使用的权限/
select * from session_privs;
/查询当前会话启用的角色/_
select * from session_roles;

3 概要文件

密码策略[附：Oracle 对特殊密码的处理]、资源限制参数、系统审计。
— 检看资源限制参数配置
SELECT FROM V$PARAMETER WHERE UPPER(NAME) =’RESOURCE_LIMIT’;
— 密码策略
SELECT FROM DBA_PROFILES WHERE PROFILE=’DEFAULT’ AND RESOURCE_TYPE =’PASSWORD’ ;
— 资源限制
SELECT * FROM DBA_PROFILES WHERE PROFILE=’DEFAULT’ AND RESOURCE_TYPE =’KERNEL’ ;
—检查是否开启了系统操作审计
SELECT NAME,VALUE FROM V$PARAMETER WHERE UPPER(NAME) = ‘AUDIT_SYS_OPERATIONS’;
—检查是否开启了审计跟踪
SELECT NAME,VALUE FROM V$PARAMETER WHERE UPPER(NAME) = ‘AUDIT_TRAIL’;
详细操作说明参看： 《Oracle数据库安全管理.doc》

  1. 所谓的特殊密码，重点提下符号@<br />    方法一： 先输入用户名和NET名，后输入密码，密码输入双引号。<br />C:\Users\Administrator>sqlplus ehrview@10.96.36.220_ehrview<br />    方法二： 采用转义字符  sqlplus ehrview/\"Password@jkjk\"@//10.96.36.220:1521/ehrview

4. 归档

—查看是否启用了归档
select name,log_mode from v$database;
—查看正在归档的状态，如果ARC状态为NO，表示系统没法自动做归档
select t.ARCHIVER from v$instance t; —STARTED
—查看归档日志占用空间的情况
select from v$recovery_file_dest;
select from v$flash_recovery_area_usage;
—查看当日归档日志文件的情况
select from v_archivelog_byday_summary where “归档完成时间[统计]”=trunc(sysdate);
select from v_archivelog_byfile_detail where completion_time>trunc(sysdate) order by completion_time desc;
—归档日志的删除，采用rman 恢复管理器
rman target /
rman> delete noprompt archivelog all completed before ‘sysdate-0.01’;

三．常用命令

netca dbca sqlplus oradim lsnrctl exp imp expdp impdp orakill
重点提下：

4 Netca

解释：ORALCE网络配置向导
d:\app\Administrator\product\instantclient_11_2\sqlnet.ora [命名方法配置]
d:\app\Administrator\product\instantclient_11_2\listener.ora [监听程序配置]
d:\app\Administrator\product\instantclient_11_2\tnsnames.ora [本地网络服务名配置]
内容如下：
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, LDAP)
SQLNET.AUTHENTICATION_SERVICES= (NTS)是操作系统验证方式
SQL> conn /as sysdba
SQLNET.AUTHENTICATION_SERVICES= (NONE)是oracle密码验证方式
SQL> conn sys/manager as sysdba

5 Sqlplus

解释：Oracle的sql*plus是与oracle数据库进行交互的客户端工具

应用一：解析命令标识符的几种命名方法

  1. sqlplus / as sysdba 以dba连接到默认实例
    2. sqlplus bsoft/bsoft 以登录名连接到默认实例
    3. sqlplus bsoft/bsoft@bsoft 以本地命名方式连接到指定实例 命名方法配置 sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES)
    4. sqlplus MHEHR_QZJ/\”Password@jkjk\”@//10.96.37.40:1521/QZJ 以轻松连接命名方式连到指定实例 命名方法配置 sqlnet.ora NAMES.DIRECTORY_PATH= (EZCONNECT)
    5. 轻型目录访问协议 以目录命名方式连接到指定实例
    命名方法配置 sqlnet.ora NAMES.DIRECTORY_PATH= (LDAP)

    应用二：利用sqlplus批量执行SQL脚本

    案例如下：
    echo 正在执行批量脚本 …
    sqlplus %Username%/%Password%@%Servicename% @install.sql > install_log.log
    echo 执行批量脚本结束 …
    ————————————————————————————————————————
    — Oracle 标准代码批量执行程序
    — 文件名：install.sql
    — 创建人：屈毅
    — **说明： 记得在如下脚本中添加批量执行脚本文件*
    ————————————————————————————————————————
    alter session set nls_date_format = ‘YYYY.MM.DD HH24:MI:SS’;
    prompt 执行批量脚本 …
    @e:\BSOFT客户档案\Q青浦卫计委\监控平台\创业监管平台重点数据对象导出\exe\h_pck_jgpt_indexstatistics.pck
    @e:\BSOFT客户档案\Q青浦卫计委\监控平台\创业监管平台重点数据对象导出\exe\h_pro_indexstatistics.prc
    @e:\BSOFT客户档案\Q青浦卫计委\监控平台\创业监管平台重点数据对象导出\exe\h_pro_statistics.prc
    @e:\BSOFT客户档案\Q青浦卫计委\监控平台\创业监管平台重点数据对象导出\exe\h_pro_statistics_bytable.prc
    Quit

    6 Oradim

    解释：Oracle的数据库管理工具

在服务里生成一个新的实例管理服务，启动方式为手工
oradim -NEW -SID BSOFT -STARTMODE manual -PFILE “D:\Oracle\admin\BSOFT\pfile\initBSOFT.ora
oradim -NEW -SRVC OracleServiceBSOFT -STARTMODE manual -PFILE “D:\Oracle\admin\BSOFT\pfile\initBSOFT.ora
注：有效的服务名为 “OracleService” 后跟“ SID”
-SID BSOFT 与 -SRVC OracleServiceBSOFT 等价
删除此实例或服务
oradim -DELETE -SID BSOFT
oradim -DELETE -SRVC OracleServiceBSOFT
编辑此实例，启动方式改为手动
oradim -EDIT -SID BSOFT -STARTMODE manual
编辑此实例，启动方式改为自动
oradim -EDIT -SID BSOFT -STARTMODE auto
启动BSOFT
oradim -STARTUP -SID BSOFT
oradim -STARTUP -SID BSOFT -STARTTYPE srvc,inst
只启动BSOFT服务
oradim -STARTUP -SID BSOFT -STARTTYPE srvc
只启动BSOFT实例
oradim -STARTUP -SID BSOFT -STARTTYPE inst
关闭BSOFT
oradim -SHUTDOWN -SID BSOFT
oradim -SHUTDOWN -SID BSOFT -SHUTTYPE srvc,inst
Oracle数据库应用技术交流 - 图1

7 Lsnrctl

解释：Oracle数据库监听管理工具

Lsnrctl 常用命令

1、$lsnrctl status 检查当前监听器的状态
2、$lsnrctl start 启动所有的监听器,可以指定名字来启动特定的监听器
3、$lsnrctl stop 关闭所有的监听器，可以指定名字来关闭特定的监听器
4、$lsnrctl reload 重启监听器，此命令可以代替lsnrctl stop,lsnrctl start

动态注册 与 静态注册

  1. 什么是注册？
    注册就是将数据库作为一个服务注册到监听程序。
    2. 什么是动态注册？
    动态注册是在instance启动的时候PMON进程根据init.ora中的instance_name,service_names两个参数将实例和服务动态注册到listener中。在sqlplus下通过show parameter service_names 和show parameter instance_name可以查看这两个参数的值。
    3. 什么是静态注册？
    静态注册是在instance启动的时候读取listener.ora文件的配置，将实例和服务注册到监听程序。
    4. 如何查询某服务是静态注册还是动态注册？
    实例状态为UNKNOWN值时表明此服务是静态注册的设置
    实例状态为READY值时表明此服务是动态注册的设置

    监听器密码设置方法(LISTENER)

    lsnrctl>change_password
    lsnrctl>save_config
    lsnrctl>stop —报错TNS-01169: The listener has not recognized the password
    lsnrctl>set password 123
    lsnrctl>stop —OK

    listener.ora中其它重要参数配置[LOCAL_OS_AUTHENTICATION_LISTENER]

    lsnrctl>status
    LOCAL_OS_AUTHENTICATION_LISTENER = OFF
    1、添加LOCAL_OS_AUTHENTICATION_LISTENER = OFF之前
    Security ON: Password or Local OS Authentication
    2、添加LOCAL_OS_AUTHENTICATION_LISTENER = OFF之后
    Security ON: Password

    8 exp imp expdp impdp

    解释：Oracle数据库逻辑备份
    先列举一个问题
    问题现象 ：Oracle11g默认对空表不分配segment，故使用exp导出Oracle11g数据库时，空表不会导出。
    解决办法：
    针对以后：设置deferred_segment_creation[延迟] 参数为FALSE后，无论是空表还是非空表，都分配segment。该值设置后只对后面新增的表产生作用，对之前建立的空表不起作用。
    SQL>alter system set deferred_segment_creation=false;
    SQL>show parameter deferred_segment_creation;
    针对之前：可以使用手工为空表分配Extent的方式，来解决导出之前建立的空表的问题。使用ALLOCATE EXTENT可以为数据库对象分配Extent，可以针对数据表、索引、物化视图等手工分配Extent
    语法如下：ALLOCATE EXTENT { SIZE integer [K | M] | DATAFILE ‘filename’ | INSTANCE integer }
    SQL>Select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows=0

重要参数说明：

EXPDP
NETWORK_LINK=LINK_EHRVIEW 源系统的远程数据库链接的名称。用于数据库的异地备份
Oracle数据库应用技术交流 - 图2
CONTENT=DATA_ONLY 指定要导出的数据。有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY。

IMPDP
注意：
1. 如果新环境的用户名表空间都和原来的不同，需要做映射
REMAP_TABLESPACE=EHRVIEW:EHRVIEWBAK 将表空间对象重新映射到另一个表空间。
REMAP_SCHEMA=EHRVIEW:EHRVIEWBAK 将一个方案中的对象加载到另一个方案。
REMAP_TABLE=EHRVIEW.H_TAB_TEST_TEMP: H_TAB_TEST_TEMP2 将一个用记的对象映射到另一个用户。
Oracle数据库应用技术交流 - 图3

9 orakill

解释：Oracle进程杀

背景及现象：

  1. KILL掉的session一直在后台存在
  2. JOB长时间运行且不能终止
  3. dba_ddl_locks锁，存储过程或函数编译不了

Oracle数据库应用技术交流 - 图4

1)在unix上，用root身份执行命令:
#kill -9 12345（即第2步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第2步查询出的spid。
Oracle数据库应用技术交流 - 图5

四．性能优化

10 执行计划

应用场景：常规SQL执行效率分析

  1. PLSQL实现方式：直接File->New->Explain Plan Window
  2. Oracle客户端操作实现方式：

EXPLAIN PLAN FOR SELECT FROM DC1001;
SELECT FROM TABLE(DBMS_XPLAN.DISPLAY);
Oracle数据库应用技术交流 - 图6

  1. SQLPLUS实现方式

set autotrace traceonly explain
Oracle数据库应用技术交流 - 图7

11 大表分区[在线重定义]

应用场景：一般是针对大表做的处理，提升SQL执行效率
参考文档：《Oracle分区管理.doc》
案例：TEMP_TAB_TEST_PARTITION

分区类型

Oracle表分区分为四种：范围分区，散列分区，列表分区和复合分区。
一：范围分区:就是根据数据库表中某一字段的值的范围来划分分区
partition by range(grade)
(
partition bujige values less than(60), —不及格
partition jige values less than(85), —及格
partition youxiu values less than(maxvalue) —优秀
);
select from graderecord partition(youxiu);
二：散列分区:散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等
partition by hash(sno)
(
partition p1,
partition p2,
partition p3
);
select from graderecord partition(p1);
三：列表分区：列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
partition by list(dormitory)
(
partition d229 values(‘229’),
partition d228 values(‘228’),
partition d240 values(‘240’)
);
select * from graderecord partition(d229); —宿舍房间号

原理解释

基表表结构定义给了分区表
分区表表结构定义给了基表

普通表转分区表

  1. 自定义同结构的分区表PAR_TABLE：
  2. 执行

—检查是否能在线重定义
begin Dbms_Redefinition.can_redef_table(USER, ‘unpar_table’); END;
—开始执行在线重定义
begin Dbms_Redefinition.start_redef_table( USER, ‘unpar_table’, ‘par_table’); END;
执行结果：两表都有数据，分区在 par_table 上
—执行结束
dbms_redefinition.finish_redef_table( USER, ‘unpar_table’, ‘par_table’); END;
执行结果：两表都有数据，分区在 unpar_table 上

  1. 删除自定义表

DROP TABLE PAR_TABLE CASCADE CONSTRAINTS;
—查询
SELECT FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=’TEMP_TAB_TEST_PARTITION’;
SELECT FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME=’TEMP_TAB_TEST_PARTITION’;
SELECT FROM TEMP_TAB_TEST_PARTITION PARTITION(P_201803);
SELECT FROM TEMP_TAB_TEST_PARTITION SUBPARTITION(P_201801_SP_JG006);

12 并行处理[parallel]

应用场景：一般是针对大表做的处理，提升SQL执行效率
参考文档：https://blog.csdn.net/c2311156c/article/details/80660734
系统默认是不启用并行的[DEGREE=1]
SELECT DEGREE,COUNT(1) RN FROM USER_TABLES GROUP BY DEGREE ORDER BY COUNT(1) DESC;
启用并行查询
SQL> ALTER TABLE T1 PARALLEL;
SQL> ALTER SESSION ENABLE PARALLEL DML;
告知oracle，对T1启用parallel查询，但并行度要参照系统的资源负载状况来确定。
利用hints提示，启用并行，同时也可以告知明确的并行度，否则oracle自行决定启用的并行度，这些提示只对该sql语句有效。
SQL> SELECT DEGREE FROM USER_TABLES WHERE TABLE_NAME=’T1’; 启用后值为DEFAULT
并行度为Default，其值由下面2个参数决定
SQL> SHOW PARAMETER CPU
cpu_count表示cpu数
parallel_threads_per_cpu表示每个cpu允许的并行进程数
default情况下，并行数为cpu_countparallel_threads_per_cpu
取消并行查询
SQL> ALTER TABLE T1 NOPARALLEL;
SQL> SELECT DEGREE FROM USER_TABLES WHERE TABLE_NAME=’T1’; 不启用时值为1
数据字典视图
v$px_session
sid：各个并行会话的sid
qcsid：query coordinator sid,查询协调器sid[即 userenv(‘SID’)]
开启parallel功能：alter session enable parallel dml;
语法：/+parallel(table_short_name,cash_number)/
—开启
ALTER TABLE TEMP_TAB_TEST_BIGDATA PARALLEL;
INSERT /+parallel(t1,8)/ INTO TEMP_TAB_TEST_BIGDATA_BAK SELECT FROM TEMP_TAB_TEST_BIGDATA;
—29994000 rows inserted in 89.935 seconds
—不开启
ALTER TABLE TEMP_TAB_TEST_BIGDATA NOPARALLEL;
INSERT INTO TEMP_TAB_TEST_BIGDATA_BAK SELECT * FROM TEMP_TAB_TEST_BIGDATA;
—29994000 rows inserted in 111.244 seconds
—效率差异比对
Oracle数据库应用技术交流 - 图8
Oracle数据库应用技术交流 - 图9

13 AWR

概念

自动工作负载库是Oracle公司提供的一个工具，其自动收集、处理、维护性能相关的统计信息。这些统计信息可以协助我们找出Oracle的性能瓶颈。
形象理解:AWR报告其实就是一张数据库健康体检表，它显示了数据库健康的各项指标。
在默认情况下，Oracle启用数据库统计收集这项功能（即启用AWR）。是否启用AWR由初始化参数STATISTICS_LEVEL控制。通过SHOW PARAMETER命令可以显示STATISTICS_LEVEL的当前值：
SQL> SHOW PARAMETER STATISTICS_LEVEL
Oracle数据库应用技术交流 - 图10
如果STATISTICS_LEVEL的值为TYPICAL或者 ALL，表示启用AWR；如果STATISTICS_LEVEL的值为BASIC，表示禁用AWR。

快照生成及参数设置

快照生成：
ORACLE默认是每隔一小时生成一次快照，每隔7天自动清除先前生成的快照。当然快照的频率和保留时间可以由用户自行修改，具体操作见下文描述。
用户也可以使用下面的命令手工采样（手工生成快照）：
Oracle数据库应用技术交流 - 图11
SQL WINDOWS:
BEGIN dbms_workload_repository.create_snapshot(); END;
CMD WINDOWS:
EXEC dbms_workload_repository.create_snapshot();
通过查询视图 DBA_HIST_SNAPSHOT ，可以知道系统中产生了哪些快照。
通过查询视图 DBA_HIST_WR_CONTROL ，可以知道系统快照的生成频率、保留时长、TOPNSQL。
参数设置：
通过MODIFY_SNAPSHOT_SETTINGS过程，DBA可以调整包括快照收集频率、快照保存时间、以及捕获的SQL数量三个方面的设置。分别对应MODIFY_SNAPSHOT_SETTINGS的三个参数：
Retention ：设置快照保存的时间，单位是分钟。可设置的值最小为1天，最大为100年。设置该参数值为0的话，就表示永久保留收集的快照信息。
Interval ：设置快照收集的频率，以分钟为单位。可设置的值最小为10分钟，最大为100年。如果设置该参数值为0，就表示禁用AWR特性。
Topnsql ：指定收集的比较占用资源的SQL数量，可设置的值最小为30，最大不超过50000。
exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
exec dbms_workload_repository.modify_snapshot_settings(interval=>120);

AWR报告

产生整个数据库[defaults the dbid and instance number]的AWR报告，运行脚本awrrpt.sql[d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql]。
产生某个实例[the user for the dbid and instance number]的AWR报告，运行脚本awrrpti.sql[d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpti.sql]。
产生某条SQL语句[the user for the dbid, instance number and the sql id
]的AWR报告，运行脚本awrsqrpt.sql[d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrsqrpt.sql]。
过程中要求输入：
[报文格式]:report_type: 默认是html，直接回车将默认成HTML格式
[快照天数]:num_days: 直接回车将列出所有完成的快照
[快照起始ID]:begin_snap
[快照结束ID]:end_snap
[报文文件名]:report_name: 默认文件名格式如:awrrpt_1_1714_1716.html 默认路径是 c:\Users\Administrator\awrrpt_1_1714_1716.html ，不过也可以在输入时自己指定目录[alternative]。

五．不同数据库间的互连

  1. KETTLE
  2. 链接服务器

SQLSERVER 连接 ORACLE : SQLSERVER 链接服务器
ORACLE 连接 SQLSERVER : ORACLE 透明网关

  1. Oracle sql developer 的 ODBC 连接

优点：基于ODBC的连接，不需要安装ORACLE的透明网关，但需要加载第三方JDBC驱动
备注：详细操作说明可参看《Oracle配置透明网关访问SQLServer.doc》

六．数据库的备份恢复

ORACLE备份有三种方式：

  • 逻辑备份
    • 逻辑备份是读取一系列的数据库记录集，并写入二进制文件中，这些记录集的读取与其所处位置无关，主要执行的命令是EXP EXPDP[详见本文档前面部分的描述]
    • 物理备份它涉及到组成数据库的文件，但不考虑逻辑内容。

优点：不必关闭数据库，备份和恢复都易于操作
缺点：执行效率不高，且只能恢复到备份点

  • 脱机物理备份[冷备]
    • 脱机物理备份是指数据库在关闭的情况下，对数据库文件进行备份，数据库使用的每个文件都被备份下来，这些文件包括：
  • 所有数据文件（v$datafile)
  • 所有控制文件 (v$controlfile)
  • 所有联机REDO LOG 文件(v$logfile)
  • 初始化参数INIT.ORA文件(可选)

优点：备份和恢复都易于操作
缺点：必须关闭数据库，且只能恢复到备份点

  • 联机物理备份[热备]
    • 联机物理备份是指数据库开启的情况下，对数据库进行备份，其前提是要求数据库必须设置为archivelog mode [alter database archivelog;]

优点：备份时数据库是可以打开的，可实现备份点恢复
缺点：执行过程相对复杂