ocp-052知识点

发表于 更新于 阅读次数：

用户权限

  • 实验

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    system用户

    CREATE USER LIXL_TEST1 IDENTIFIED BY LIXL_TEST1 PASSWORD EXPIRE;
    GRANT CREATE SESSION TO LIXL_TEST1;
    GRANT CREATE TABLE TO LIXL_TEST1 WITH ADMIN OPTION;

    LIXL_TEST1用户
    SQL> create table lixl_table1(id number);

    Table created.

    SQL> insert into lixl_table1 values(1);
    insert into lixl_table1 values(1)
                *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'USERS'

    CREATE INDEX tab1_index_id ON lixl_table1(id);

  • 给用户赋予表空间操作的权限

    1
    2
    3
    4
    5
    6
    7
    -- 想要insert成功，需要这个权限
    grant unlimited tablespace to  LIXL_TEST;
    ```     

    - 查看系统默认的用户表空间
    ```sql
    SELECT DEFAULT_TABLESPACE FROM dba_users WHERE USERNAME='LIXL_TEST';

  • 查看表所属表空间

    1
    SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME=upper('lixl_table1');

  • REVOKE ROLE 撤销权限

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    sqlplus / as sysdba

    -- 创建用户
    create user USER_01 IDENTIFIED BY USER_01;
    create user USER_02 IDENTIFIED BY USER_02;

    -- 创建角色
    create role ROLE_01;

    -- 给角色授权
    GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO ROLE_01;

    -- 给用户赋角色
    GRANT ROLE_01 TO USER_01;

    -- 删除角色
    DROP ROLE ROLE_01;
    • 通过role授权(如crate table)，不能直接revoke用户的create table权限

RMAN

Recovery Manager是用于备份(backup)、还原(restore)和恢复(recover)Oracle数据库的工具。

  • Ora-19804
    Ora-19804: Cannot reclaim 45561856 bytes disk space from 8589934592 limit
    启动数据库到Nomount: startup nomount
    扩大闪回恢复区的大小:show parameter db_recovery
    执行SQL语句:alter system set db_recovery_file_dest_size=5g scope=both;
    打开数据库:alter database mount;
    打开数据库:alter database open;
    进入RMAN模式:rman target sys/admin nocatalog
    RMAN删除过期的备份文件与归档文件:delete backupset;
    RMAN删除过期的备份文件与归档文件:delete archivelog all;
    检查一下Flashrecovery Area的使用情况:select * from v$flash_recovery_area_usage;

  • 增量备份

    1
    2
    3
    4
    5
    6
    7
    打开控制文件自动备份
    RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
    打开备份优化
    RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

    执行0级差异增量备份操作
    RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

  • rman恢复数据库

    • 归档模式

      1
      2
      3
      startup force mount;
      restore database;
      alter database open resetlogs;

    • 非归档模式

      1
      2
      3
      4
      5
      6
      startup force nomount;
      restore controlfile;
      alter database mount;
      restore database;
      recover database noredo;
      alter database open resetlogs;

  • 备份文件类型

    • backupset 备份集

    • image copy（映像文件） 备份

    • 备份类型之Proxy Copies
      Proxy Copies
      During a proxy copy, RMAN turns over control of the data transfer to a media manager that supports this feature. Proxy copy can only be used with media managers that support it and cannot be used with channels of type DISK. The PROXY option of the BACKUP command specifies that a backup should be a proxy copy.
      在Proxy Copies内，RMAN将数据传输的控制权转交给支持此功能的媒体管理器。代理副本只能与支持它的媒体管理器一起使用，不能与类型的通道一起使用DISK。该PROXY的选项该BACKUP命令指定备份应该是代理副本。

      For each file that you attempt to back up with the BACKUP PROXY command, RMAN queries the media manager to determine whether it can perform a proxy copy. If the media manager cannot proxy copy the file, then RMAN backs the file up as if the PROXY option had not been used. (Use the PROXY ONLY option to force RMAN to fail if a proxy copy cannot be performed.)
      对于您尝试使用的备份的每个文件 BACKUP PROXY命令，RMAN查询媒体管理器以确定它是否可以执行代理副本。如果媒体管理器无法代理复制文件，则RMAN会将文件备份，就像PROXY未使用该选项一样。（使用PROXY ONLY 如果无法执行代理副本，则强制RMAN失败的选项。）

      Note that control files are never backed up with proxy copy. If the PROXY option is specified on an operation backing up a control file, then it is silently ignored for the purposes of backing up the control file.
      请注意，控制文件永远不会使用代理副本进行备份。如果在PROXY备份控制文件的操作上指定了该选项，则会以静默方式忽略该选项以备份控制文件。

metadata

元数据存储在control file

flashback

恢复误删除表

1
2
3
4
5
6
7
8
9
10
-- 如误删除EMP表
DROP TABLE EMP;

-- 查询回收站是否存在该表记录
select object_name,original_name,partition_name,type,ts_name,createtime,droptime 
  from recyclebin
 where original_name = 'EMP';
 
 -- 恢复
 flashback table EMP to before drop;

pfile

使用pfile启动数据库

1
2
3
4
5
6
7
8
9
-- 查看系统使用的spfile还是pfile启动的
-- 如果使用的pfile就没有内容
show parameter spfile

-- 从spfile创建pfile
create pfile='/u01/app/pfile.txt' from spfile;

-- 指定pfile startup daatabase
startup pfile='/u01/app/pfile.txt';

AWR

Oracle 11g AWR 系列一：AWR 的基本概念理解
Automatic Workload Repository (AWR)
收集、处理和维护用于问题诊断的性能统计信息。
该数据既存在于数据块中，也存在于内存中。
AWR 收集的数据可以通过报告和视图进行查看。

AWR 处理和收集的统计信息包括：
1.确定数据块 segment 访问路径和使用情况的对象统计信息
2.基于数据库活动的时间使用情况的时间模型统计信息，可在 V$SYS_TIME_MODEL 和 V$SESS_TIME_MODEL 视图中查看
3.V$SYSSTAT 和 V$SESSTAT 视图中收集的一些 sytem 和 session 的统计信息
4.按照 elapsed time 和 CPU time 等条件在系统上筛选出的产生较高负载的 SQL 语句
5.ASH(ctive Session History，活动会话历史记录)统计信息——最近的 session 活动的历史记录

IPS

(Incident Packaging Service)事件打包服务
利用这个打包事件压缩的文件夹提交给oracle的支持来帮助解决分析问题。
创建一个事件包，将添加一个或多个相关的问题到打包文件中。其中包括trace文件，dump文件等。
缺省情况下，每一个问题的事件包仅仅包括第一个和最后三个事件。事件包包含所有可以用于诊断的事件。也可以指定事件发生的时间或数量范围来打包。

锁

锁是一种保护机制， 用于管理对共享资源的并发访问。
oracle 数据库是多用户数据库， 可以并发操作。
锁避免了并发操作带来的数据不一致情况。

  • DML、事务及锁的关系
    DML 操作用到的两种锁：
    受影响记录上的排它锁 exclusive
    受影响表上的共享锁 shared

    • 排它锁： 当前会话(事务)加锁后， 其他会话不能加锁， 只能等待。
      行级别排他锁，保护这行的数据，防止其他会话修改这些行。 
      1
      2
      3
      4
      5
      -- 查询时给整行加锁
      SELECT * FROM EMP FOR UPDATE NOWAIT;

      -- 给指定SAL列加锁
      SELECT SAL FROM EMP FOR UPDATE;

    只锁定修改的行，缩小加锁范围。

    1
    2
    -- 如： session1 update 一行， session2 update 此行会等待。 但可以 update 其他行。
    UPDATE EMP SET SAL=1000 WHERE EMPNO=7369 ;
    • 共享锁： 可以加多份。 表级别， 保护表的结构， 防止其他会话使用 DDL 语句修改表的定义。
      如： session1 做 update 操作时， session2 不能把表删了， 不能把字段删了。

  • 死锁与锁等待
    产生死锁至少存在两个锁 及多于两个线程。

    • 行级锁死锁示例
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      -- 一。 session1
      UPDATE EMP SET SAL=8000 WHERE EMPNO=7788;

      -- 二。 session2
      UPDATE EMP SET SAL=sal*2 WHERE EMPNO=7369;

      -- 三。 session1
      UPDATE EMP SET SAL=8000 WHERE EMPNO=7369;

      -- 四。 session2
      UPDATE EMP SET SAL=sal*2 WHERE EMPNO=7788;

    进行步骤三时，session1出现锁等待。
    进行步骤四时，session2出现锁等待，同时session1出现死锁。 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    SCOTT@PROD> UPDATE EMP SET SAL=8000 WHERE EMPNO=7369;
    UPDATE EMP SET SAL=8000 WHERE EMPNO=7369
    *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting for resource
    ```   

    原因分析：
    步骤一 session1获取锁A，
    步骤二 session2获取锁B，
    步骤三 session1获取锁B（获取不到锁，处于锁等待状态）
    步骤四 session2获取锁A（也获取不到锁，并且发生死锁）

    解决：
    oracle检测到死锁时会自动释放其中一个锁，但另一个线程还处于锁等待状态。
    ```sql
    -- 查看处于锁等待的会话
    SELECT SID, EVENT, blocking_session,USERNAME FROM V$SESSION WHERE blocking_session IS NOT NULL;

    -- 查看当前会话ID
    select userenv('sid') from dual;

    发生锁等待，说明应用程序的处理事务逻辑有问题，需要调整应用中的DML了，
    可以通过oracle日志及代码查找出现死锁的语句然后调整业务逻辑。

OLTP与OLAP

On-Line Transaction Processing联机事务处理过程

  • 与事务、锁冲突、多线程等有关

On-Line Analytical Processing 联机分析处理过程

系统参数

IOT

Oracle提供了多种数据表存储结构。
最常见的就是三种，分别为堆表（Heap Table）、索引组织表（Index Organization Table，简称为IOT）和聚簇表（Cluster Table）。
Heap Table是我们在Oracle中最常使用的数据表，也是Oracle的默认数据表存储结构

index

unusable rebuild

SGA

AMM时还需要手动管理的组件：redo log buffer、keep buffer pool

space management

使用bitmap管理相邻空闲块
ASSM或MSSM管理一个表空间内的空闲块

fast recovery area size

db_recovery_file_dest_size =5G
重新设置的时候，不足的空间会自动删除过时的归档日志

dbca

oracle服务处于nomount状态时，使用dbca删除数据库，只删除服务，不删除dbf文件

redo与undo

external table

https://www.cnblogs.com/ilifeilong/p/7648193.html

incremenal checkpoint

ckpt增量检查点
作用：DBWn将脏块写入dbf文件时执行
触发时机：3秒、LGWn触发

PL/SQL package

PL/SQL包（package）一旦创建被存储在oracle数据库中，由两部分组成：
（1）.包规范（specification）一些包的定义信息，类型、记录、变量、常量、异常定义、游标和子程序；
（2）包体（Body）：包体是对包规范中声明的子程序的实现部分
置于规范的所有对象被称为公共对象。任何子程序在封装主体中没有包定义但编码被称为私有对象。

oracle lock

TM lock 表级锁
TX lock 行级锁或事务锁
select 无锁
select * from for update 行级排他锁
DML 行级排他锁
DDL 表级排他锁

INITIALLY IMMEDIATE

一般情况下（默认情况下），执行语句是立即检查约束(immediate),不能延迟
特殊情况下，也可以设置为提交事务时检查约束(DEFERRED)，也称延迟约束
设置延迟约束deferrable initially immediate
如果创建时没设置，则无法在后来使约束成为延迟约束。延迟约束一般用于外键
not deferrable | deferrable [ initially { immediate | deferred } ]。
not deferrable 是不可延迟，并且约束也无法更改为可延迟状态。约束会在每一句sql statement 之后都进行 check，不符合则 roll back
deferrable 是可延迟状态，在 deferrable 状态时又有2个选项，创建时缺省是initially immediate，即约束会在每一句sql statement 之后都进行 check，效果等同于not deferrable，但是可以修改延迟状态。
initially deferred 约束会在整个事务进行commit 时check，如果不符合则roll back
在deferrable状态下，可以通过set constraints修改 immediate或者deferred
约束的状态分为
ENABLE VALIDATE:无法输入违法约束的行，而且表中的所有行都符合约束，约束新行也约束现有数据
Disable NOVALIDATE：可以输入任何数据，表中可能已经存在不合要求的的数据
ENABLE NOVALIDATE：表中已经存在不合要求的数据，但现在输入的所有数据必须符合要求
DISABLE VALIDATE：表中所有的数据都符合约束，但新行未必如此.约束上的索引也会被删除

opatch

oracle打补丁工具
默认安装，位置./$ORCLE_HOME/OPatch/opatch version
在线升级补丁

1
2
3
$ ./opatch apply online -connectString PROD:sys:oracle
``` 
查看本机补丁列表详情

$ ./opatch lsinventory -detail


# AUDIT_TRAIL
AUDIT_TRAIL启用或禁用数据库审计。当设置该参数为NONE或FALSE时，将禁止数据库审计；当设置该参数为OS时，将激活数据库审计，并将审计记录写入到OS审计跟踪文件中；当设置该参数为DB或TRUE时，将激活数据库审计，并将审计记录写入到数据字典SYS.AUD$中；当设置该参数为DB_EXTENDED时，不仅将审计记录写入到数据字典SYS.AUD$中，还会填充该数据字典的SQLBIND和SQLTEXT列。
取值说明:
none 禁用数据库审计
os 启用数据库审计，并将数据库审计记录定向到操作系统审计记录
db 启用数据库审计，并将数据库所有审计记录定向到数据库的SYS.AUD$表
db,extended 启用数据库审计，并将数据库所有审计记录定向到数据库的SYS.AUD$表。另外，填充SYS.AUD$表的SQLBIND 列和SQLTEXT CLOB 列。
xml 启用数据库审计，并将所有记录写到XML格式的操作系统文件中。
xml,extended 启用数据库审计，输出审计记录的所有列，包括SqlText和SqlBind的值。
你可以使用SQL语句AUDIT来设置审计选项，不管如何设置此参数。 
本题开启审计并对EMP表上的所有操作进行审计 audit all on Emp 

# db link
默认private只有当前用户访问，public所有用户可访问

# expdp
可以导出view/sequence/package

# trace files
Trace file：跟踪文件一般由各个后台进程生成，一般在遇到错误时生成。这些文件与警报日志一起放在跟踪目录中
跟踪文件是包含用于调查问题的诊断数据的管理文件。此外，跟踪文件还可以为应用程序或实例的调优提供指导，每个服务器和后台进程都可以周期性地写入相关的跟踪文件。关于流程环境、状态、活动和错误的文件信息。
SQL跟踪工具还创建跟踪文件，这些文件提供关于单个SQL语句的性能信息。要启用对客户机标识符、服务、模块、操作、会话、实例或数据库的跟踪，必须在DBMS_MONITOR包中执行适当的过程，或者使用Oracle Enterprise Manager。
转储是一种特殊类型的跟踪文件。虽然跟踪往往是诊断数据的连续输出，但转储通常是响应事件(例如事件)的诊断数据的一次性输出。当发生事件时，数据库将一个或多个转储写入为该事件创建的事件目录。事件转储还包含文件名中的事件号

# materialized view
物化视图，需要空间(create table权限)

# oracle grid
要先创建os dba group和os user account

# SRVCTL 
可以用来添加和删除数据库，关闭数据库实例
https://blog.csdn.net/w892824196/article/details/79295593

# Optimizer Statistics
https://docs.oracle.com/cd/E25178_01/server.1111/e16638/stats.htm 
https://yq.aliyun.com/articles/283792   
oracle优化器对一个sql语句检测所有可能的执行计划并选择一个成本值最小的,这里的成本代表了一个特定执行计划的资源使用情况.为了让优化器能准确的判断一个执行计划的成本它必须要关于sql语句要访问的所有对象(表或索引)的信息同时还要有运行sql语句的系统信息.

#