Oracle Database Administration
Oracle Database Administration
Architecture
实例与数据库
Oracle数据库服务器包含2部分：
- 实例，包含内存和后台进程
- 数据库，就是物理存储
我们经常说的Oracle数据库，广义的说，就是包括实例部分和物理存储部分，狭义的说，就只是物理存储部分。但是对于Oracle来说，他的物理存储部分就叫数据库，管理部分叫实例，2个合起来叫数据库服务器（Database Server）。
那么客户端如何去访问数据库服务器呢，是通过客户端的用户进程把请求交给数据库服务器的服务器进程，由服务器进程来负责处理请求，也就是说客户端无法直接去修改数据库的东西，真正修改是通过数据库服务器上面的服务器进程来进行处理的。
Oracle把数据库和实例分开，就形成了以下2种的组合。
- 第一种是非集群的环境，在一台服务器上面创建多个实例和多个数据库，但是一个实例只能对应一个数据库。
- 第二种是集群的环境，将实例放在多个服务器上面，而将数据库放在共享存储上面，多个实例对应一个数据库，任何一个实例所在的服务器（称之为节点）宕机，都不影响整个系统，而且任何实例对共享存储的修改，在其他实例都可以看得到，提供了一个高可用的解决方案。集群系统一般有2种实现方式，一种是Oracle的RAC（Real Application Cluster），一种是第三方的HA（High Availability），在Oracle的RAC出现之后，第三方的HA用得越来越少了。目前一般都是使用Oracle的RAC部署集群。
连接与会话
数据库创建好后，客户端要去访问数据库，这里就涉及到2个概念。
- 连接（connection），就是客户端的用户进程和服务器端的实例之间的通信路径。如果用户进程和实例在同一主机上面，使用内部进程来创建连接，如果用户进程和实例在不同主机上面，则使用网络来创建连接。
- 会话（session），就是用户登录到数据库实例的一种状态，或者说是通信双方从开始通信到通信结束期间的一个上下文（Context）。这个上下文是一段位于服务器端的内存：记录了本次连接的客户端机器、通过哪个应用程序、哪个用户登录等信息。例如使用用户名和密码通过SQL*Plus登录到数据库实例，就是创建了一个会话。是专属于某一个用户的特定的连接。
一个连接对应一个会话的示意图如下：
一个连接对应两个会话的示意图如下：
可以通过动态性能视图V$SESSION去查询会话的信息。
例子：通过设置autotrace，在同一个连接下新增加一个会话
SQL> select sid,serial#,paddr from v$session where username='SYS';
SID SERIAL# PADDR
---------- ---------- ----------------
39 1021 00000000917A8D40
SQL> set autotrace on statistics;
SQL> select sid,serial#,paddr from v$session where username='SYS';
SID SERIAL# PADDR
---------- ---------- ----------------
29 921 00000000917A8D40
39 1021 00000000917A8D40
例子：结束所有会话但是不断开连接
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
例子：查看会话详细信息
SQL> conn / as sysdba
Connected.
SQL> desc v$session;
SQL> select sid,serial#,username,program from v$session;
SID SERIAL# USERNAME PROGRAM
---------- ---------- ---------- ----------------------------------------
21 403 SYS sqlplus@oracletest1 (TNS V1-V3)
其中：
SID为Session identifier，会话标识符
Serial#为Session serial number，会话序列号
Oracle内部进程的USERNAME为空
需要使用SID和Serial#共同确定唯一的session，例如我们在会话里面再使用conn / as sysdba发起新的session，此时session的sid不变，但是serial#会改变。
SQL> conn / as sysdba
Connected.
SQL> select sid,serial#,username,program from v$session;
SID SERIAL# USERNAME PROGRAM
---------- ---------- ---------- ----------------------------------------
21 405 SYS sqlplus@oracletest1 (TNS V1-V3)
例子：查看参数sessions的值
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 247
可以看到这个数据库的session数量最大为247，那么这个数字是如何来的呢？我们在创建数据库的时候会指定一个服务器进程Processes参数，这个参数限制了连接到实例的操作系统进程数量。
例子：查看参数processes的值
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 150
那么这个参数和session有什么关系呢？如果数据库运行在专用服务器模式（Dedicated），一个会话对应一个服务器进程，如果数据库运行在共享服务器模式（Shared），一个服务器进程可以为多个会话服务。
例子：查询数据库运行在哪种服务器方式
SQL> select distinct server from v$session;
SERVER
---------
DEDICATED
可以通过动态性能视图v$process去查询进程的信息。
例子：通过v$session和v$process视图看会话和进程的关系
SQL> select s.sid,s.username,p.spid,p.username from v$session s join v$process p on s.paddr=p.addr where s.username is not null;
SID USERNAME SPID USERNAME
---------- ---------- ---------- ----------
46 SYS 10844 oracle
38 SYS 11445 oracle
37 HR 11495 oracle
45 SYS 11178 oracle
Oracle的sessions和processes的数量关系是：
- Oracle 11g R1以及之前版本
sessions=(1.1 * processes) + 5
- Oracle 11g R2
sessions=(1.5 * processes) + 22
例子：查看当前数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
可以看到当前版本为11g R2，前面查询到的sessions数量为247，processes数量为150，则刚好满足公式：
247=(1.5*150)+22
如果你碰到了ORA-12516错误，一般都是超过了Oracle的会话数限制，但是Oracle的最大会话数是由初始化参数processes控制的，并不是由参数sessions控制的，设置processes后系统通过上面的公式自动计算sessions的值。
可以通过动态性能视图v$resource_limit去查询资源限制。
例子：查看系统曾经最大会话数量，如果接近最大值，则增大processes
SQL> select * from v$resource_limit where resource_name = 'sessions';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------- ------------------- --------------- -------------------- --------------------
sessions 45 69 247 247
所有会话曾经达到的最大值为69，包含内部进程的会话。
如果这个最大值接近247，则增加processes。
例子：增大processes为300
SQL> alter system set processes=300 scope=spfile;
System altered.
需要重启数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
查看确认，已经更改了。
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 300
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 472
v$session视图的status字段表示会话的状态，会话有ACTIVE、INACTIVE、KILLED、CACHED、SNIPED五个状态，一般比较常见的是ACTIVE、INACTIVE、KILLED这三个状态。
ACTIVE：处于此状态的会话，表示正在执行，处于活动状态。
INACTIVE：处于此状态的会话表示不是正在执行的
KILLED：处于此状态的会话，表示出现了错误或进程被杀掉，正在回滚，也占用系统资源
CACHED： Session temporarily cached for use by Oracle*XA
SNIPED： Session inactive, waiting on the client，标记为SNIPED的进程被释放有两种条件：
相关的terminal再一次试图登录及执行sql
手动的在操作系统后台kill掉相应的spid
例子：查看会话的状态
SQL> select sid,serial#,username,program,status from v$session;
SID SERIAL# USERNAME PROGRAM STATUS
---------- ---------- ---------- ---------------------------------------- --------
1 5 SYS sqlplus@oracletest1 (TNS V1-V3) INACTIVE
2 1 oracle@oracletest1 (PMON) ACTIVE
如果某个用户的会话无响应，可以使用alter system kill session手工结束。
例子：结束某个用户的会话
SQL> select sid,serial#,username,program,status from v$session where username is not null;
SID SERIAL# USERNAME PROGRAM STATUS
---------- ---------- ---------- ---------------------------------------- --------
1 5 SYS sqlplus@oracletest1 (TNS V1-V3) INACTIVE
45 51 HR sqlplus@oracletest1 (TNS V1-V3) INACTIVE
46 65 SYS sqlplus@oracletest1 (TNS V1-V3) ACTIVE
SQL> alter system kill session '45,51' immediate;
System altered.
SQL> select sid,serial#,username,program,status from v$session where username is not null;
SID SERIAL# USERNAME PROGRAM STATUS
---------- ---------- ---------- ---------------------------------------- --------
1 5 SYS sqlplus@oracletest1 (TNS V1-V3) INACTIVE
46 65 SYS sqlplus@oracletest1 (TNS V1-V3) ACTIVE
内存结构
Oracle数据库服务器包括实例和数据库。
实例又由内存和后台进程组成。
客户端发出的SQL命令由服务器进程响应，在内存区域内进行语法分析、编译和执行，将修改信息写入日志文件，再将修改后的数据写入数据文件，最后将SQL执行的结果反馈给用户。不同的操作系统以及数据库的配置，会有不同的内存结构，这里我们的数据库主要运行在Linux下，主要学习Linux下的Oracle的内存结构，暂不去讨论Windows的内存结构。
我们先来看内存结构，如下图：
内存结构又主要分为2个部分：
- System Global Area (SGA)：包含数据和控制信息的共享内存区域。这句话可以说是SGA的定义。虽然简单，但其中阐述了SGA几个很重要的特性：
- SGA的构成——数据和控制信息；
- SGA是共享的，即当有多个用户同时登录了这个实例，SGA中的信息可以被它们同时访问；
- 一个SGA只服务于一个实例，也就是说，当一台机器上有多个实例运行时，每个实例都有一个自己的SGA，尽管SGA来自于OS的共享内存区，但实例之间不能相互访问对方的SGA区
- Program Global Areas (PGA)：包含一个服务器进程或者后台进程的数据和控制信息的私有内存区域。它是Oracle在一个服务器进程或者后台进程启动时创建的非共享内存，一个PGA也只能被拥有它的那个服务器进程所访问。PGA的总和称为instance PGA。
SGA
System Global Area (SGA)主要由以下部分组成：
- 共享池（Shared Pool）
- 数据库缓冲区缓存（Buffer Cache）
- 重做日志缓冲（Redo Log Buffer）
- 大池（Large Pool）
- Java池（Java Pool）
- 流池（Streams Pool）
Shared Pool
共享池（Shared Pool）由库缓存（Library Cache）、数据字典缓存（Data Dictionary Cache）、SQL查询结果及PL/SQL函数结果缓存、并行执行消息的缓冲以及控制结构组成。在Oracle 11g中，Shared Pool的大小建议由系统自动管理。
可以通过动态性能视图v$sgainfo去查询SGA内存组件大小。
例子：查看共享池的大小
SQL> select name,bytes/1024/1024 from v$sgainfo where name='Shared Pool Size';
NAME BYTES/1024/1024
-------------------------------- ---------------
Shared Pool Size 168
Library Cache
库缓存（Library Cache）是用于存储可执行的SQL和PL/SQL代码的共享池内存结构，包括共享的SQL和PL/SQL区域以及控制结构（如锁、库缓存句柄）。用于解析SQL语句，解析和编译PL/SQL程序，生成SQL执行计划。如果是共享服务模式，那么库缓存还包括私有SQL区域。
当执行一个SQL语句，如果该SQL语句的解析已经存在于库缓存且可以被共享，则数据库直接使用已有的解析，我们称之为软解析或者库缓存命中，否则就需要创建一个新的解析，我们称之为硬解析或者库缓存命中失败。
共享SQL区和私有SQL区：
Oracle会为每一条SQL语句运行提供一个共享SQL区（Shared SQL Areas）和私有SQL区（专用模式下Private SQL Areas属于PGA）。
一个共享SQL区中保存了一条语句的解析树和查询计划。当发现两个（或多个）用户都在运行同一SQL语句时，Oracle会重新组织SQL区，使这些用户能重用共享SQL区。但还会在私有SQL区中保存一份这条SQL语句的拷贝。
就是说执行SQL语句的每一个会话在他的PGA里面都有一个私有SQL区，执行同一SQL语句的用户的私有SQL区都指向同一个共享SQL区，因此，在不同PGA里面的私有SQL区可以关联同一个共享SQL区。在多用户系统中，Oracle通过为SQL语句使用同一共享SQL区多次运行来节省内存。
当一条新的SQL语句被解析时，Oracle从共享池中分配一块内存作为共享SQL区。这块内存的大小与这条语句的复杂性相关。如果Shared Pool没有足够空间分配给共享SQL区，Oracle将释放从LRU（Least Recently Used）链表中查找到最近最少使用的内存块，直到有足够空间给新的语句的共享SQL区。如果Oracle释放的是一个共享SQL区的内存，那么相应的语句在下次执行时需要再次解析并重新分配共享SQL区。而从解析语句到分配共享SQL区是一个比较消耗CPU的工程。这就是为什么我们提倡使用绑定变量的原因了。在没有使用绑定变量时，语句中的变量的数值不同，oracle就视为一条新的语句（9i后可以通过cursor_sharing来控制），重复上面的解析、内存分配的动作，将大大消耗系统资源，降低系统性能。
Data Dictionary Cache
数据字典是有关于数据库的参考信息、数据库的结构信息和数据库中的用户信息的一组表和视图的集合，如我们常用到的V$视图、DBA_视图都属于数据字典。在SQL语句解析的过程中，Oracle频繁的访问（如果需要的话）这些数据字典，因此内存中有两处地方被专门用于存放数据字典。一个地方就是数据字典缓存（Data Dictionary Cache）。数据字典缓存也被称为行缓存（Row Cache），因为它是以记录行为单元存储数据的，而不像Buffer Cache是以数据块为单元存储数据。内存中另外一个存储数据字典的地方是库缓存。所有Oracle的用户都可以访问这两个地方以获取数据字典信息。
在SQL Trace中，这种对数据字典的访问就被统计为回调（recursive calls）。
例子：查看SQL语句执行的回调信息
SQL> set linesize 150
SQL> set autotrace traceonly
SQL> select * from hr.employees;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
149 recursive calls
0 db block gets
245 consistent gets
17 physical reads
0 redo size
10427 bytes sent via SQL*Net to client
596 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
107 rows processed
SQL> select * from hr.employees;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
10427 bytes sent via SQL*Net to client
596 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
Server Result Cache
服务器结果缓存（Server Result Cache）是Oracle 11g的新特性，包括SQL查询结果缓存和PL/SQL函数结果缓存，当sql执行时，数据库先去result cache查找相关结果；当结果相关的对象发生变化，例如相关表的数据被删除一条，则结果缓存失效。
Reserved Pool
前面提到，如果Oracle解析一个 PL/SQL程序单元，也需要从共享池中分配内存给这些程序单元对象。由于这些对象本一般比较大（如包），所以分配的内存空间也相对较大。系统经过长时间运行后，共享池可能存在大量内存碎片，导致无法满足对于大块内存段的分配。
为了使有足够空间缓存大程序块，Oracle专门从共享池分配一块内存保存这些大块。这个保留池（Reserved Pool）的默认大小是共享池的5%。它的大小也可以通过参数SHARED_POOL_RESERVED_SIZE来调整。保留池是从共享池中分配，不是直接从SGA中分配的，它是共享池的保留部分，用于存储大块段。
Shared Pool中内存大于5000字节的大段就会被存放在共享池的保留部分。而这个大小限制是通过隐含参数_SHARED_POOL_RESERVED_MIN_ALLOC来设定的。除了在实例启动过程中，所有小于这个数的内存段永远都不会放到保留部分中，而大于这个值的大内存段也永远不会存放到非保留池中，即使共享池的空间不够用的情况下也是如此。
保留池的空闲内存也不会被包含在普通共享池的空闲列表中。它会维护一个单独的空闲列表。保留池也不会在它的LRU列表中存放可重建段。当释放普通共享池空闲列表上的内存时是不会清除这些大段的，同样，在释放保留池的空闲列表上的大内存段时也不会清除普通共享池中内存。
通过视图V$SHARED_POOL_RESERVED可以查到保留池的统计信息。其中字段REQUEST_MISSES记录了没有立即从空闲列表中得到可用的大内存段请求次数，这个值要为0。因为保留池必须要有足够个空闲内存来适应那些短期的内存请求，而无需将那些需要长期cache住的没被pin住的可重建的段清除。否则就需要考虑增大SHARED_POOL_RESERVED_SIZE了。
你可以通过观察视图V$SHARED_POOL_RESERVED的MAX_USED_SIZE字段来判断保留池的大小是否合适。大多数情况下，你会观察到保留池是很少被使用的，也就是说5%的保留池空间可能有些浪费。但这需要经过长期观察来决定是否需要调整保留池大小。
例子：查看参数SHARED_POOL_RESERVED_SIZE
SQL> show parameter SHARED_POOL_RESERVED_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 6M
例子：查看共享池的保留池中，没有立即从空闲列表中得到可用的大内存段请求次数
SQL> select request_misses from v$shared_pool_reserved;
REQUEST_MISSES
--------------
0
共享池的内存管理
通常来说，共享池是根据修正过的LRU算法来释放其中的对象（共享SQL区和数据自动记录行）的，否则这些对象就一直保持在共享池中。如果共享池需要为一个新对象分配内存，并且共享池中没有足够内存时，内存中那些不经常使用的对象就被释放掉。一个被许多会话使用过的共享池对象，即使最初创建它的进程已经结束，只要它是有用的，都会被修正过的LRU算法一直保持在共享池中。这样就使一个多用户的Oracle系统对SQL语句的处理和内存消耗最小。
当一条SQL语句被提交给Oracle执行，Oracle会自动执行以下的内存分配步骤：
- Oracle检查共享池，看是否已经存在关于这条语句的共享SQL区。如果存在，这个共享SQL区就被用于执行这条语句。而如果不存在，Oracle就从共享池中分配一块新的共享SQL区给这条语句。同时，无论共享SQL区存在与否，Oracle都会为用户分配一块私有SQL区以保存这条语句相关信息（如变量值）。
- Oracle为会话分配一个私有SQL区。私有SQL区的所在与会话的连接方式相关。
在以下情况下，Oracle也会将共享SQL区从共享池中释放出来：
- 当使用ANALYZE语句更新或删除表、簇或索引的统计信息时，所有与被分析对象相关的共享SQL区都被从共享池中释放掉。当下一次被释放掉的语句被执行时，又重新在一个新的共享SQL区中根据被更新过的统计信息重新解析
- 当对象结构被修改过后，与该对象相关的所有共享SQL区都被标识为无效（invalid）。在下一次运行语句时再重新解析语句
- 如果数据库的全局数据库名（Global Database Name）被修改了，共享池中的所有信息都会被清空掉
- DBA通过ALTER SYSTEM FLUSH SHARED_POOL手工方式清空共享池
例子：手动清空共享池
SQL> alter system flush shared_pool;
System altered.
Database Buffer Cache
数据库缓冲区缓存（Database Buffer Cache）也叫Buffer Cache，是SGA区中专门用于存放从数据文件中读取的的数据块拷贝的区域。Oracle进程如果发现需要访问的数据块已经在Buffer Cache中，就直接读写内存中的相应区域，而无需读取数据文件，从而大大提高性能。Buffer Cache对于所有Oracle进程都是共享的，能被所有Oracle用户访问。在Oracle 11g中，Database Buffer Cache的大小建议由系统自动管理。
例子：查看数据库缓冲区缓存的大小
SQL> select name,bytes/1024/1024 from v$sgainfo where name='Buffer Cache Size';
NAME BYTES/1024/1024
-------------------------------- ---------------
Buffer Cache Size 332
Buffer有三种状态：
- Unused：没有被使用的Buffer，数据库优先使用
- Clean：已经被使用过但包含的是clean的数据，就是目前和磁盘上的数据一致，可以重复使用
- Dirty：包含的是修改后数据，但是还没有写入到磁盘，与磁盘上面的数据不一致
Buffer有两种访问模式：
- Current mode：指当前用户访问当前修改后未提交事务的数据，获取的就是当前Buffer Cache里面的数据块
- Consistent mode：指其他用户访问当前用户修改后未提交事务的数据，获取的是undo里面的读一致性的数据块
Oracle对于buffer cache的管理，是通过两个重要的链表实现的：写链表和最近最少使用链表（the Least Recently Used LRU）。写链表所指向的是所有脏数据块缓存。而LRU链表指向的是所有空闲的缓存、pin住的缓存以及还没有来的及移入写链表的脏缓存。空闲缓存中没有任何有用的数据，随时可以使用。而pin住的缓存是当前正在被访问的缓存。LRU链表的两端就分别叫做最近使用端（the Most Recently Used MRU）和最近最少使用端（LRU）。
当一个Oracle进程访问一个缓存时，这个进程会将这块缓存移到LRU链表中的MRU。而当越来越多的缓冲块被移到MRU端，那些已经过时的脏缓冲（即数据改动已经被写入数据文件中，此时缓冲中的数据和数据文件中的数据已经一致）则被移到LRU链表中LRU端。
当一个Oracle用户进程第一次访问一个数据块时，它会先查找buffer cache中是否存在这个数据块的拷贝。如果发现这个数据块已经存在于buffer cache（即命中cache hit），它就直接读从内存中取该数据块。如果在buffer cache中没有发现该数据块（即未命中cache miss），它就需要先从数据文件中读取该数据块到buffer cache中，然后才访问该数据块。命中次数与进程读取次数之比就是我们一个衡量数据库性能的重要指标：buffer hit ratio（buffer命中率）。
可以通过动态性能视图v$sysstat去查询系统信息。
例子：获得自实例启动至今的buffer命中率
SQL> select 1-(sum(decode(name,'physical reads',value,0))/
2 (sum(decode(name,'db block gets',value,0))+
3 (sum(decode(name,'consistent gets',value,0))))) "Buffer Hit Ration"
4 from v$sysstat;
Buffer Hit Ration
-----------------
.984403316
根据经验，一个良好性能的系统，这个值一般保持在95%以上。
上面提到如果未命中（missed），则需要先将数据块读取到缓存中去。这时Oracle进程需要从空闲列表中找到一个合适大小的空闲buffer。如果空闲列表中没有合适大小的空闲buffer，它就会从LRU端开始查找LRU链表，直到找到一个可重用的缓存块或者达到最大查找块数限制。在查找过程中，如果进程找到一个脏缓存块，它将这个缓存块移到写链表中去，然后继续查找。当它找到一个空闲块后，就从磁盘中读取数据块到缓存块中，并将这个缓存块移到LRU链表的MRU端。
当有新的对象需要请求分配buffer时，会通过内存管理模块请求分配空闲的或者可重用的buffer。“free buffer requested”就是产生这种请求的次数；当请求分配buffer时，已经没有适合大小的空闲buffer时，需要从LRU链表上获取到可重用的buffer。但是LRU链表上的buffer并非都是立即可重用的，还会存在一些块正在被读写或者已经被别的用户所等待。根据LRU算法，查找可重用的buffer是从链表的LRU端开始查找的，如果这一段的前面存在这种不能立即被重用的buffer，则需要跳过去，查找链表中的下一个buffer。“free buffer inspected”就是被跳过去的buffer的数目。
例子：查看系统目前free buffer requested和free buffer inspected的值
SQL> select name,value from v$sysstat where name in ('free buffer requested','free buffer inspected');
NAME VALUE
---------------------------------------------------------------- ----------
free buffer requested 39220
free buffer inspected 0
如果Oracle用户进程达到查找块数限制后还没有找到空闲缓存，它就停止查找LRU链表，并且通过信号通知DBW0进程将脏缓存写入磁盘去。
由于Buffer Cache中存放的是从数据文件中来的数据块的拷贝，因此它的大小的计算也是以块的尺寸为基数的。而数据块的大小是由参数db_block_size指定的。9i以后，块的大小默认是8K，它的值一般设置为和操作系统的块尺寸相同或者它的倍数。每次读多少块是由参数db_file_multiblock_read_count来控制的，在Oracle10gR2以前的版本中，DBA必须根据db_block_size参数，以及应用系统的特性，来调整db_file_multiblock_read_count参数。在Oracle10gR2及其之后的版本中，Oracle数据库已经可以根据系统的IO能力以及Buffer Cache的大小来动态调整该参数值，Oracle建议不要显式设置该参数值。
例子：查看参数db_block_size
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
从9i开始，Oracle支持创建不同块尺寸的表空间，并且可以为不同块尺寸的数据块指定不同大小的buffer cache。9i以后，除了SYSTEM表空间和TEMPORARY表空间必须使用标准块尺寸外，所有其他表空间都可以最多指定四种不同的块尺寸。而标准块尺寸还是由上面的所说的参数db_block_size来指定。而db_cache_size则是标准块尺寸的buffer cache的大小。
非标准块尺寸的块大小可以在创建表空间（CREATE TABLESPACE）时通过BLOCKSIZE参数指定。而不同块尺寸的buffer cache的大小就由相应参数DB_nK_CACHE_SZIE来指定，其中n可以是2，4，8，16或者32。需要先设置DB_nK_CACHE_SIZE来指定缓存这个表空间数据块的buffer cache的大小，然后才能创建了一个块大小为nK的非标准块尺寸的表空间。
任何一个尺寸的Buffer Cache都是不可以缓存其他尺寸的数据块的。因此如果你打算使用多种块尺寸用于你的数据库的存储，你需要给你要用到的非标准块尺寸的数据块指定相应的Buffer Cache大小。这些参数使你可以为系统指定多达4种不同块尺寸的Buffer Cache。
特别注意，DB_nK_CACHE_SIZE 参数不能设定标准块尺寸的缓冲区大小。举例来说，如果 DB_BLOCK_SIZE 设定为 4K，就不能再设定 DB_4K_CACHE_SIZE 参数。
例子：是否可以设置设置db_8k_cache_size大小
SQL> alter system set db_8k_cache_size=200m scope=both;
alter system set db_8k_cache_size=200m scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
例子：创建一个非标准块的表空间，假定块大小为16k
先查看DB_nK_CACHE_SZIE参数，为0表示还没有启用
SQL> show parameter db_16k_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
直接创建表空间会报错
SQL> create tablespace ts01 datafile '+DATA/stone/datafile/ts01.dbf' size 10m blocksize 16K;
create tablespace ts01 datafile '+DATA/stone/datafile/ts01.dbf' size 10m blocksize 16K
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
为非标准块尺寸的数据块指定相应的Buffer Cache大小
SQL> alter system set db_16k_cache_size=1m scope=both;
System altered.
SQL> show parameter db_16k_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
即使修改时指定的是1M，修改之后系统会自动给出一个最小的4M大小，这个参数受Granule Size控制。小于4M系统自动设置为4M，大于4M，就是4M的整数倍。
SQL> select * from v$sgainfo where name='Granule Size';
NAME BYTES RES
-------------------------------- ---------- ---
Granule Size 4194304 No
然后再来创建表空间就可以了
SQL> create tablespace ts01 datafile '+DATA/stone/datafile/ts01.dbf' size 10m blocksize 16k;
Tablespace created.
可以将database buffer cache分成不同的buffer pool，达到不同的cache数据的目的。有以下三种：
- 默认缓冲池（Default pool），块正常缓存的缓冲池，如果没有指定其他的缓冲池，那database buffer cache就是默认的缓冲池
- 保持缓冲池（Keep pool），用于缓存那些需要经常查询的对象但又容易被默认缓冲区置换出去的对象，可以更久被保留。它的大小由参数DB_KEEP_CACHE_SZIE控制
- 回收缓冲池（Recycle pool），用于缓存临时使用的、不被经常使用的较大的对象，可以更快被清除。它的大小由参数DB_RECYLE_CACHE_SIZE指定
这三个参数相互之间是独立的，并且他们都只适用于标准块尺寸的数据块。
Redo Log Buffer
重做日志缓冲（Redo Log Buffer）是SGA中一段保存数据库修改信息的缓存。这些信息被存储在重做条目(Redo Entries)中。重做条目中包含了由于INSERT、UPDATE、DELETE、CREATE、ALTER或DROP所做的修改操作而需要对数据库重新组织或重做的必须信息。在必要时，重做条目还可以用于数据库恢复。
重做条目是Oracle数据库进程从用户内存中拷贝到Redo Log Buffer中去的。重做条目占用重做日志缓冲连续的空间，循环写入。后台进程LGWR负责将Redo Log Buffer中的信息写入到磁盘上联机重做日志文件。
例子：查看重做日志缓冲的大小
SQL> select name,bytes from v$sgainfo where name='Redo Buffers';
NAME BYTES
-------------------------------- ----------
Redo Buffers 2371584
SQL> select name,bytes from v$sgastat where name='log_buffer';
NAME BYTES
-------------------------- ----------
log_buffer 2371584
一般默认情况下的log_buffer的大小够用了，查看Log_buffer是否需要调整，可以查看数据库是否有大量的log buffer space等待事件出现。redo log 最开始是在pga中的uga产生的（数据库一般是专有模式），oracle会把它拷贝到SGA中的log_buffer中去，如果log_buffer过小，或者lgwr不能够快速将redo 写入到log file中，那么就会产生log buffer space等待事件，遇到此类问题，可以增加 log_buffer大小，调整log file到裸设备，I/O快的磁盘中。
Large Pool
大池（Large Pool）是SGA中的一块可选内存池，根据需要时配置。在以下情况下需要配置大池：
- 用于共享服务（Shared Server MTS方式中）的会话内存和Oracle分布式事务处理的Oracle XA接口
- 使用并行查询（Parallel Query Option PQO）时
- IO服务进程
- Oracle备份和恢复操作（启用了RMAN时）
通过从大池中分配会话内存给共享服务、Oracle XA或并行查询，Oracle可以使用共享池主要来缓存共享SQL，以防止由于共享SQL缓存收缩导致的性能消耗。此外，为Oracle备份和恢复操作、IO服务进程和并行查询分配的内存一般都是几百K，这么大的内存段从大池比从共享池更容易分配得到。
例子：查看大池的大小
SQL> select name,bytes/1024/1024 from v$sgainfo where name='Large Pool Size';
NAME BYTES/1024/1024
-------------------------------- ---------------
Large Pool Size 8
Java Pool
Java池（Java Pool）也是SGA中的一块可选内存区，它也属于SGA中的可变区。Java池的内存是用于存储所有会话中特定Java代码和JVM中数据。Java池的使用方式依赖于Oracle服务的运行模式。不要认为使用Java开发的程序就要用Java池，只是说在数据库里面使用Java去编写了存储过程才会用到Java池，但是目前很少有人用Java去编写存储过程了。Java池的大小可以通过参数JAVA_POOL_SIZE来设置。
例子：查看Java池的大小
SQL> select name,bytes/1024/1024 from v$sgainfo where name='Java Pool Size';
NAME BYTES/1024/1024
-------------------------------- ---------------
Java Pool Size 4
Streams Pool
流池（Streams Pool）是Oracle 10g中新增加的。是为了增加对流（流复制是Oracle 9iR2中引入的一个非常吸引人的特性，支持异构数据库之间的复制。10g中得到了完善）的支持。但是现在Oracle的流复制技术用得很少了，因为Oracle收购了Oracle GoldenGate，可以跨平台，跨数据库，Oracle现在主推这个，流复制就用得越来越少了。流池也是可选内存区，属于SGA中的可变区。它的大小可以通过参数STREAMS_POOL_SIZE来指定。如果没有被指定，流池大小默认为0，Oracle会在第一次使用流时自动创建，根据需要动态增加。
例子：查看流池的大小
SQL> select name,bytes from v$sgainfo where name='Streams Pool Size';
NAME BYTES
-------------------------------- ----------
Streams Pool Size 0
Shared Pool、Large Pool、Java Pool和Streams Pool这几块内存区的大小是跟随系统参数设置而改变的，所以又通称为可变SGA（Variable SGA）。
例子：查看sga的组成
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 2257840
Variable Size 532679760
Database Buffers 297795584
Redo Buffers 2371584
SQL> show sga;
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 532679760 bytes
Database Buffers 297795584 bytes
Redo Buffers 2371584 bytes
Fixed Size
Fixed Size部分为sga的固定部分，其大小由Oracle数据库设定且不可手工修改，包含:
- 数据库和实例的状态信息等通用信息，后台进程需要访问这部分信息
- 进程之间的通信信息，比如锁的信息
例子：查看Fixed Size的大小
SQL> select name,bytes from v$sgainfo where name='Fixed SGA Size';
NAME BYTES
-------------------------------- ----------
Fixed SGA Size 2257840
PGA
如果数据库是专用服务器模式，Program Global Areas (PGA)主要由以下部分组成：
- Stack Space（堆栈空间）
- User Global Area（UGA）
其中User Global Area（UGA）包括以下部分：
- Cursor State：存放游标的运行信息（可以把游标看成私有SQL区）
- User Sessions Data：存放会话的控制信息
- SQL Working Areas：处理SQL语句
其中Cursor State（私有SQL区）包括以下部分：
- run-time area：包含执行计划状态信息
- persistent area：包含绑定变量值
其中SQL Working Areas包括以下部分：
- Sort Area：使用order by或者group by排序数据时会用到这个区域
- Hash Area：表发生hash jions时会用到这个区域
- Create Bitmap Area：位图索引创建会用到这个区域
- Bitmap Merge Area：解析位图索引执行计划会用到这个区域
如果数据库是共享服务器模式，Program Global Areas (PGA)只有Stack Space，UGA在SGA里面，如果配置了大池，UGA在大池，如果没有配置大池，UGA在共享池。
例子：查看当前PGA的大小
SQL> select component,current_size/1024/1024,max_size/1024/1024 from v$memory_dynamic_components where component='PGA Target';
COMPONENT CURRENT_SIZE/1024/1024 MAX_SIZE/1024/1024
-------------------- ---------------------- ------------------
PGA Target 280 280
SQL> select name,value/1024/1024 from v$pgastat where name='aggregate PGA target parameter';
NAME VALUE/1024/1024
---------------------------------------- ---------------
aggregate PGA target parameter 280
例子：查看排序区，hash区，位图区，位图合并区的大小
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL> show parameter hash_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hash_area_size integer 131072
SQL> show parameter create_bitmap_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
SQL> show parameter bitmap_merge_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
例子：查看游标（私有SQL区）的数量
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
open_cursors设置一个会话可以同时打开的游标数。由于每打开一个游标，都需要一部分PGA内存分配出来作为私有SQL区。因此这个参数也影响了每个进程的PGA内存的占用大小。
例子：查看每个session 的pga内存使用
SQL> select p.username,p.spid,p.program,pm.category,pm.used,pm.allocated,pm.max_allocated
2 from v$process p join v$process_memory pm on p.pid=pm.pid
3 join v$session s on s.paddr=p.addr and s.username is not null;
USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
---------- ---------- ------------------------------ --------------- ---------- ---------- -------------
oracle 42393 oracle@oracletest1 (TNS V1-V3) SQL 27496 40240 4892600
oracle 42393 oracle@oracletest1 (TNS V1-V3) PL/SQL 224 2008 2008
oracle 42393 oracle@oracletest1 (TNS V1-V3) Freeable 0 1048576
oracle 42393 oracle@oracletest1 (TNS V1-V3) Other 2179302 2179302
grid 42463 oracle@oracletest1 SQL 12968 89472 820392
grid 42463 oracle@oracletest1 PL/SQL 18848 25064 27352
grid 42463 oracle@oracletest1 Freeable 0 1048576
grid 42463 oracle@oracletest1 Other 2491862 2741694
grid 42469 oracle@oracletest1 SQL 152 71032 3204032
grid 42469 oracle@oracletest1 PL/SQL 43344 90696 95976
grid 42469 oracle@oracletest1 Freeable 0 524288
进程结构
Oracle数据库的进程分为3类：
- 用户进程：运行连接到oracle数据库的应用或者工具的代码
- 数据库进程：运行Oracle数据库自己的代码，包括服务器进程及后台进程
- Oracle守护进程及应用进程：包括监听和GI的进程，这些进程不仅仅用于一个数据库
用户进程（客户端进程）用于运行连接到oracle数据库的应用或者工具的代码，当用户执行一个基于Oracle数据库的应用程序时，客户端操作系统就会创建一个用户进程。用户进程可以在数据库服务器上面，也可以不在数据库服务器上面。
服务器进程用于处理连接到该实例的用户进程的请求，主要任务包括：
- 解析并执行用户所提交的SQL语句
- 搜索SGA区的数据库缓存，决定是否读取数据文件。如果数据块不在SGA区的数据库缓存中，就将其从数据文件中读入
- 将查询执行的结果数据返回给用户
例子：在客户端创建一个到数据库的连接，查看用户进程和服务器进程
C:\Users\shilei>sqlplus hr/hr@192.168.230.138:1521/stone
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 18 08:52:00 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
查看用户进程
C:\Users\shilei>tasklist | findstr sqlplus
sqlplus.exe 22764 Console 1 30,036 K
查看对应的服务器进程，其中“LOCAL=NO”表示不是本地连接，是一个远程连接
[oracle@oracletest ~]$ ps -ef | grep LOCAL=NO | grep -v grep
oracle 3625 1 0 08:52 ? 00:00:00 oraclestone (LOCAL=NO)
我们在创建数据库的时候，会让你选择数据库的工作模式，是专用模式还是共享模式，不同模式下Oracle的服务器进程是不一样的，这里看一下这两种模式的区别。
Dedicated Server(专用服务器模式)：数据库为每一个用户进程分配一个服务器进程为其服务
下图表示用户通过网络连接数据库时专用服务器模式下用户进程和服务器进程之间的关系。
在专用服务器架构下，有以下两种通信机制
- 如果用户进程和专业服务器进程在一台机器上面，则使用操作系统的内部进程进行通信
- 如果用户进程和专业服务器进程在不同机器上面，则使用网络进行通信
Shared Server（共享服务器模式）：使用调度器引导网络会话请求到共享服务器进程池，使用共享服务器模式可以减少操作系统的进程数量以及PGA，但是会增大响应时间，提高安装和调优的复杂性，故目前大多数情况下都没有使用。
Oracle在实例启动的时候自动创建多个后台进程来操作管理数据库，每个后台进程都有各自的任务，可以通过v$process视图查看有哪些后台进程在运行。
例子：查看有哪些后台进程在运行
SQL> select pname from v$process where pname is not null order by pname;
PNAME
-----
ARC0
ARC1
ARC2
ARC3
CJQ0
CKPT
D000
DBRM
DBW0
DIA0
DIAG
GEN0
LGWR
MMAN
MMNL
MMON
PMON
PSP0
Q001
Q002
QMNC
RECO
S000
SMCO
SMON
VKTM
W001
27 rows selected.
也可以通过操作系统查看，后台进程在操作系统上面的名称为“ora_后台进程名称_实例名称”
[root@oracletest ~]# ps -ef | grep _stone | grep -v grep
oracle 2436 1 0 Nov18 ? 00:00:09 ora_pmon_stone
oracle 2438 1 0 Nov18 ? 00:00:16 ora_psp0_stone
oracle 2440 1 2 Nov18 ? 00:24:47 ora_vktm_stone
oracle 2444 1 0 Nov18 ? 00:00:02 ora_gen0_stone
oracle 2446 1 0 Nov18 ? 00:00:04 ora_diag_stone
oracle 2448 1 0 Nov18 ? 00:00:03 ora_dbrm_stone
oracle 2450 1 0 Nov18 ? 00:00:58 ora_dia0_stone
oracle 2452 1 0 Nov18 ? 00:00:02 ora_mman_stone
oracle 2454 1 0 Nov18 ? 00:00:09 ora_dbw0_stone
oracle 2456 1 0 Nov18 ? 00:00:10 ora_lgwr_stone
oracle 2458 1 0 Nov18 ? 00:00:23 ora_ckpt_stone
oracle 2460 1 0 Nov18 ? 00:00:07 ora_smon_stone
oracle 2462 1 0 Nov18 ? 00:00:00 ora_reco_stone
oracle 2464 1 0 Nov18 ? 00:00:18 ora_mmon_stone
oracle 2466 1 0 Nov18 ? 00:00:31 ora_mmnl_stone
oracle 2468 1 0 Nov18 ? 00:00:01 ora_d000_stone
oracle 2470 1 0 Nov18 ? 00:00:00 ora_s000_stone
oracle 2478 1 0 Nov18 ? 00:00:01 ora_arc0_stone
oracle 2480 1 0 Nov18 ? 00:00:01 ora_arc1_stone
oracle 2482 1 0 Nov18 ? 00:00:01 ora_arc2_stone
oracle 2484 1 0 Nov18 ? 00:00:01 ora_arc3_stone
oracle 2487 1 0 Nov18 ? 00:00:00 ora_qmnc_stone
oracle 2491 1 0 Nov18 ? 00:00:01 ora_q001_stone
oracle 2509 1 0 Nov18 ? 00:00:13 ora_cjq0_stone
oracle 2757 1 0 Nov18 ? 00:00:02 ora_smco_stone
oracle 9575 1 0 08:31 ? 00:00:00 ora_q002_stone
oracle 12657 1 0 15:00 ? 00:00:00 ora_w001_stone
例子：通过v$bgprocess视图来查看Oracle有哪些后台以及描述
SQL> select name,description from v$bgprocess;
NAME DESCRIPTION
----- ----------------------------------------------------------------
PMON process cleanup
VKTM Virtual Keeper of TiMe process
......
DBWn
DBWn(Database Writer)数据库写进程将数据库缓冲区缓存中修改的块（Dirty）写入数据文件中。参数DB_WEITER_PROCESSES定义DBWn进程的数量。大多数情况下一个数据库写进程（DBW0）就足够了，但是如果你的数据库数据量改动频繁和大量，可以增加额外的数据库写进程（DBW0-DBW9，DBWa-DBWz，最多36个，不要超过CPU的数量）以提高性能，但对于单CPU系统来说，只能有一个数据库写进程。
例子：查看当前系统DBWn的数量以及最大的数量
SQL> show parameter db_writer_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
SQL> select pname from v$process where pname like '%DBW%';
PNAME
-----
DBW0
[oracle@oracletest ~]$ ps -ef | grep dbw | grep -v grep
oracle 2454 1 0 Nov19 ? 00:00:13 ora_dbw0_stone
最大数量
SQL> select count(*) from v$bgprocess where name like '%DBW%';
COUNT(*)
----------
36
触发DBWn写数据库缓冲区缓存中的脏块到磁盘的条件有：
- 当服务器进程在扫描了一定数量的数据库缓冲区缓存还找不到可用的干净块来写入，则通知DBWn将脏块写入到磁盘
- DBWn定期的写buffer，来推进检查点(实例恢复必须开始的redo thread position）
在许多情况下，DBWn写的数据分散在整个磁盘（scattered write）。因此，DBWn写往往比LGWR的顺序写慢。DBWn执行多块写时可能提高效率，多块写入的数目与操作系统有关。
LGWR
LGWR（Log Writer）日志写进程将重做日志缓冲区的数据顺序写入（sequential write）到磁盘的联机重做日志文件。一个实例只有一个LGWR进程
例子：查看当前系统的LGWR
SQL> select pname from v$process where pname='LGWR';
PNAME
-----
LGWR
[oracle@oracletest ~]$ ps -ef | grep lgwr | grep -v grep
oracle 2456 1 0 Nov20 ? 00:00:19 ora_lgwr_stone
触发LGWR条件有：
- 用户提交事务
- 发生联机重做日志文件切换
- 每隔3秒
- 重做日志缓冲区1/3满或者新产生大于1MB的数据
- DBWR 触发LGWR写入
在DBWn将数据库缓冲区缓存中的脏块写入到磁盘的数据文件之前，这些脏块对应的重做记录必须先写入到磁盘的联机重做日志文件，如果DBWn发现对应的重做记录还没有写入到磁盘的联机重做日志文件，则通知LGWR进行写入，等待写入完成后，DBWn再进行写入。
当用户执行COMMIT语句，事务被分配一个SCN（system change number），LGWR立即将提交记录、提交SCN以及事务的重做条目一起写入到磁盘，而对应修改的数据库缓冲区缓存中的脏块则会延迟到更有效率的时候写入到磁盘，这就是所谓的快速提交机制，也就是说只要将事务的相应重做信息通过LGWR写入到了磁盘，即使该事务修改后的数据还没有写入到磁盘，Oracle数据库就认为已经事务已经完成。
在某些情况下，即使用户没有执行COMMIT语句，LGWR也会将事务的重做信息写入到磁盘，但是只有后面用户执行了COMMIT语句，写入到磁盘的重做信息才会永久有效。
CKPT
在数据库系统中，写联机重做日志文件和写数据文件是数据库中IO消耗最大的两种操作，在这两种操作中写数据文件属于分散写，写联机重做日志文件是顺序写，因此为了保证数据库的性能，通常数据库都是在提交（commit）完成之前要先保证重做日志都被写入到联机重做日志文件中，而脏数据块保存在数据库缓冲区缓存（buffer cache）中再不定期的分批写入到数据文件中。也就是说日志写入和提交操作是同步的，而数据写入和提交操作是不同步的。这样就存在一个问题，当一个数据库崩溃的时候并不能保证数据库缓冲区缓存里面的脏数据全部写入到数据文件中，这样在实例启动的时候就要使用日志文件进行恢复操作，将数据库恢复到崩溃之前的状态，保证数据的一致性。检查点是这个过程中的重要机制，通过它来确定，恢复时哪些日志应该被扫描并应用于恢复。
例子：查看当前系统的CKPT
SQL> select pname from v$process where pname='CKPT';
PNAME
-----
CKPT
[oracle@oracletest ~]$ ps -ef | grep ckpt | grep -v grep
oracle 2503 1 0 Nov29 ? 00:00:22 ora_ckpt_stone
CKPT（Checkpoint）检查点进程将检查点信息（包括检查点位置、SCN、开始恢复的联机重做日志位置等）写入控制文件和数据文件头部并通知DBWn将脏块写入到磁盘。
Checkpoint主要2个作用：
- 保证数据库的一致性，这是指通知DBWn将脏块写入到磁盘，保证内存和磁盘上的数据是一样的
- 缩短实例恢复的时间，实例恢复要把实例异常关闭前没有写入到硬盘的脏数据通过日志进行恢复。如果脏块过多，实例恢复的时间也会很长，检查点的发生可以减少脏块的数量，从而提高实例恢复的时间
Oracle数据库有以下几种检查点：
Thread checkpoint（线程检查点），数据库将被某个特定的重做线程所修改的所有缓冲区写入磁盘。数据库中所有实例的线程检查点的集合即为数据库检查点。线程检查点在下列情况下发生：
一致的数据库关闭
ALTER SYSTEM CHECKPOINT 语句
联机重做日志切换
ALTER DATABASE BEGIN BACKUP 语句
Tablespace and data file checkpoints（表空间和数据文件检查点），表空间检查点是一组数据文件检查点，每个数据文件检查点对表空间中的某个数据文件做检查点操作。这些检查点发生在很多情况下，包括将一个表空间设为只读、将表空间脱机、收缩数据文件、或执行ALTER TABLESPACE BEGIN BACKUP等。
Incremental checkpoints（增量检查点），增量检查点是一种线程检查点，部分原因是为了避免在联机重做日志切换时写入大量的块。DBWn至少每隔三秒会进行检查以确定是否有工作要做。当 DBWn 将脏缓冲区写入磁盘时, 它会向前推进检查点位置，导致CKPT将检查点位置写入控制文件，而不是数据文件头。
其他类型的检查点包括实例和介质恢复检查点，和删除或截断模式对象时的检查点。
前面LGWR和CKPT都提到了SCN（system change number），那么SCN到底是什么呢，SCN用以标识数据库在某个确切时刻提交的版本。在事务提交时，它被赋予一个唯一的标识事务的SCN。SCN同时被作为Oracle数据库的内部时钟机制，可被看做逻辑时钟，每个数据库都有一个全局的SCN生成器。作为数据库内部的逻辑时钟，数据库事务依SCN而排序，Oracle也依据SCN来实现一致性读（Read Consistency）等重要数据库功能。SCN在数据库中是唯一的，并随时间而增加，但是可能并不连贯。除非重建数据库，SCN的值永远不会被重置为0。系统当前SCN并不是在任何的数据库操作时都会改变，SCN通常在事务提交或回滚时改变。在控制文件、数据文件头、数据库、日志文件头、日志文件change vector中都有SCN，但其作用各不相同。
CKPT一定是是在checkpoint发生的时候将数据库当前的SCN更新入数据库文件头和控制文件当中，同时DBWn进程将buffer cache中的脏数据块写到数据文件当中(这个脏数据也一定是当前online redo log保护的那一部分)。同时CKPT进程还会在控制文件当中记录(redo block address)RBA，这个地址用来标志恢复的时候需要从日志中的那个位置开始。
ORACLE中除了有SCN，还有Checkpoint_Change#，那这两者的关系是什么呢，其实Checkpoint_Change#是来源于SCN，SCN是时刻在变化的，Checkpoint_Change#是在数据发生了检查点的时候才改变的，它的值来源于SCN。因为SCN时刻在变化，无法捕获到Checkpoint_Change#等于SCN的时刻。
例子：查看系统当前的SCN
SQL> select dbms_flashback.get_system_change_number() SCN from dual;
SCN
----------
2681573
在Oracle数据库中和checkpoint相关的SCN总共有4个
(1）System checkpoint SCN (存在于控制文件)
在系统执行checkpoint后，Oracle会更新当前控制文件中的System checkpoint SCN。我们可以通过select checkpoint_change# from v$database来查看。
例子：查看当前的System checkpoint SCN
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2678314
(2）Datafile checkpoint SCN (存在于控制文件)
由于控制文件中记录了Oracle中各个数据库文件的位置和信息，其中当然也包括了Datafile checkpoint SCN，因此在执行checkpoint的时候，Oracle还会去更新控制文件中所记录的各个数据文件的datafile checkpoint SCN。我们可以通过select checkpoint_change# from v$datafile来查看。
例子：查看当前各个数据文件的Datafile checkpoint SCN
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf 2678314
(3）Start SCN (存在于各个数据文件头)
在执行checkpoint时，Oracle会更新存放在各个实际的数据文件头的Start SCN（注意绝对不会是控制文件中的），这个SCN存在的目的是用于检查数据库启动过程中是否需要做media recovery（介质恢复），我们可以通过select checkpoint_change# from v$datafile_header来查看
例子：查看当前各个数据文件的Start SCN
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------------------------------------- ------------------
/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf 2678314
/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf 2678314
(4）STOP SCN(又称End SCN，存在于控制文件)
STOP SCN也是记录在控制文件当中，每一个所记录的数据文件头都有一个对应的STOP SCN，这个STOP SCN一定是存在于控制文件当中。这个SCN存在的绝对意义主要是用来去验证数据库启动过程中是否需要做instance recovery（实例恢复）。我们可以通过select name,last_change# from v$datafile来查看。那么在数据库正常运行的情况下，对于read/write的online 数据文件这个SCN号为#FFFFFF(NULL)。
例子：查看当前各个数据文件的STOP SCN
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
-------------------------------------------------------------------------------- ------------
/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf
那么我们来看看不需要做实例恢复和需要做实例恢复的情况。
(1）如果系统正常关闭，则system checkpoint scn = datafile checkpoint scn = start scn = stop scn，不需要做实例恢复。
例子：查看系统正常关闭后，mount状态下各个SCN的情况
先创建一个脚本，同时获取上面的四个SCN
[oracle@oracletest ~]$ cat getscn.sql
select 'System Checkpoint SCN - V$Database:checkpoint_change#' "SCN Location",checkpoint_change# from v$database
union
select 'Datafile Checkpoint SCN - V$Datafile:checkpoint_change#' "SCN Location",checkpoint_change# from v$datafile
union
select 'Datafile Start SCN - V$Datafile_header:checkpoint_change#' "SCN Location",checkpoint_change# from v$datafile_header
union
select 'Datafile Stop SCN - V$Datafile:Last_change#' "SCN Location",last_change# from v$datafile;
然后查看当前各个SCN的值
SQL> @getscn
SCN Location CHECKPOINT_CHANGE#
--------------------------------------------------------- ------------------
Datafile Checkpoint SCN - V$Datafile:checkpoint_change# 2678314
Datafile Start SCN - V$Datafile_header:checkpoint_change# 2678314
Datafile Stop SCN - V$Datafile:Last_change#
System Checkpoint SCN - V$Database:checkpoint_change# 2678314
正常关闭数据库并启动到mount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> @getscn
SCN Location CHECKPOINT_CHANGE#
--------------------------------------------------------- ------------------
Datafile Checkpoint SCN - V$Datafile:checkpoint_change# 2683010
Datafile Start SCN - V$Datafile_header:checkpoint_change# 2683010
Datafile Stop SCN - V$Datafile:Last_change# 2683010
System Checkpoint SCN - V$Database:checkpoint_change# 2683010
可以看到这四个SCN相等，不需要进行实例恢复。
(2）系统异常关闭，则system checkpoint scn = datafile checkpoint scn = start scn , stop scn is NULL，需要进行实例恢复。
例子：查看系统异常关闭后，mount状态下各个SCN的情况
SQL> alter database open;
Database altered.
SQL> startup mount force
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> @getscn
SCN Location CHECKPOINT_CHANGE#
--------------------------------------------------------- ------------------
Datafile Checkpoint SCN - V$Datafile:checkpoint_change# 2683013
Datafile Start SCN - V$Datafile_header:checkpoint_change# 2683013
Datafile Stop SCN - V$Datafile:Last_change#
System Checkpoint SCN - V$Database:checkpoint_change# 2683013
可以看到stop scn为空，需要进行实例恢复。
在正常open数据库的时候，Oracle会将记录在控制文件中的每一个数据文件头的STOP SCN都设置为#FFFFFF(NULL)，那么如果数据库进行了正常关闭比如(shutdown or shutdown immediate)，这个时候系统会执行一个检查点，这个检查点会将控制文件中记录的各个数据文件头的STOP SCN更新为当前online数据文件的各个数据文件头的Start SCN，也就是STOP SCN=Start SCN，如果再次启动数据库的时候发现二者相等，则直接打开数据库，并再次将STOP SCN设置为#FFFFFF(NULL)，那么如果数据库是异常关闭，那么checkpoint就不会执行，因此再次打开数据库的时候STOP SCN<>Start SCN，这个时候就需要做实例恢复。
而在数据库的启动过程中，当System Checkpoint SCN=Datafile Checkpoint SCN=Start SCN的时候，Oracle数据库是可以正常启动的，而不需要做任何的media recovery。而如果三者当中有一个不同的话，则需要做media recovery。
SMON
一个实例只有一个系统监控进程SMON（System Monitor），作用：
- 在实例启动时负责对数据库进行恢复。如果是非正常关闭数据库，则当下次启动例程时，SMON进程会自动读重做日志文件，对数据库进行恢复，即执行将已提交的事务写入数据文件中、回退未提交的事务等操作
- 清除已经分配但不再使用的表空间中的临时段。(如果表空间中有大量的盘区，则清除就会花费大量时间，就会影响数据库启动时的性能)
- 合并基于数据字典管理的各个表空间中位置相邻的空闲盘区，使之更容易分配
例子：查看当前系统的SMON
SQL> select pname from v$process where pname='SMON';
PNAME
-----
SMON
[oracle@oracletest ~]$ ps -ef | grep smon | grep -v grep
oracle 2505 1 0 Nov29 ? 00:00:04 ora_smon_stone
PMON
PMON（Process Monitor）进程监控进程的作用：
- 在用户进程失败的时候执行进程恢复，并负责清除数据库缓冲区缓存，释放进程使用的资源。比如说重置当前活动的事务表，释放不需要的locks，清理进程id（隐式回滚）。
- 周期性的检查调度器或者服务器进程，并在中断运行后进行重启（故意中断的除外）。
- 监控超时的空闲会话。
- 动态注册数据库服务到监听。当一个实例开启，PMON会查看listener是否正在运行。如果listener正在运行，那么PMON会传递相关的参数，如果没有运行那么PMON定期的尝试连接。
例子：查看当前系统的PMON
SQL> select pname from v$process where pname='PMON';
PNAME
-----
PMON
[oracle@oracletest ~]$ ps -ef | grep pmon | grep -v grep
oracle 2480 1 0 Nov29 ? 00:00:10 ora_pmon_stone
RECO
在分布式数据库中，RECO（Recover Process）恢复进程自动解决分布式事务发生错误的情况。一个节点上的RECO进程自动连接到没有被正确处理事务相关的数据库上面。当RECO建立了数据库之间的连接，它会自动的解决没有办法处理的事务，删除与该事务相关的行（清理事务表）。
例子：查看当前系统的RECO
SQL> select pname from v$process where pname='RECO';
PNAME
-----
RECO
[oracle@oracletest ~]$ ps -ef | grep reco | grep -v grep
oracle 2507 1 0 Nov29 ? 00:00:01 ora_reco_stone
ARCn
ARCn（Archiver Process）归档进程的作用：
- 在重做日志切换后拷贝重做日志文件到存储上面
- 收集事务的重做数据并传输到备库
归档进程只有数据库在归档模式并且自动归档开启的时候存在（从Oracle10g开始，log_archive_start参数已经被废弃，只要启动数据库的归档模式，Oracle就会启用自动归档）。系统默认有4个归档进程。可以有多个归档目的地。
例子：查看数据库当前是否是归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
例子：查看当前系统的ARCn
SQL> select pname from v$process where pname like '%ARC%';
PNAME
-----
ARC0
ARC1
ARC2
ARC3
[oracle@oracletest ~]$ ps -ef | grep arc | grep -v grep
oracle 2523 1 0 Nov29 ? 00:00:01 ora_arc0_stone
oracle 2525 1 0 Nov29 ? 00:00:02 ora_arc1_stone
oracle 2527 1 0 Nov29 ? 00:00:01 ora_arc2_stone
oracle 2529 1 0 Nov29 ? 00:00:01 ora_arc3_stone
存储结构
Oracle数据库必须包含以下三种文件：
- 控制文件（Control files）：包含数据库的信息以及备份的信息
- 数据文件（Data files）：包含数据字典和用户数据
- 联机重做日志文件（Online redo log files）：包含数据的改变信息，保证不丢失数据
Oracle数据库正常稳定运行建议还包括以下文件：
- 参数文件（Parameter files）：包含实例启动时的参数信息
- 密码文件（Password file）：允许用户使用sysdba、sysoper和sysasm角色远程连接到数据库进行管理操作
- 备份文件（Backup files）：用于数据库恢复
- 归档重做日志文件（Archived redo log files）：联机重做日志文件的归档
- 跟踪文件（Trace files）：用于记录服务器进程或者后台进程的错误信息
- 告警文件（Alert log file）：记录数据库运行的信息以及错误
Oracle数据库的逻辑和物理存储：
逻辑上，一个数据库包含多个表空间，一个表空间包含多个段，一个段包含多个区，一个区包含多个数据块。
数据块（data blocks）：逻辑存储结构中最小的逻辑单位，数据库输入输出操作的最小存储单位，由多个操作系统块构成。
区（extent）：由一组连续的数据块构成，是存储分配的最小单位，是表中数据增大的基本单位。
段（segment）：由数据区构成，是独立的逻辑存储结构。段是为特定的数据对象分配的一系列数据区，占用磁盘空间。不是所有的数据库对象都会分配段，比如视图、触发器、包。4种主要类型的段：
数据段，创建表时自动创建以表名字命名的数据段
索引段，创建索引时自动创建以索引名字命名的索引段
回滚段：存储undo信息
临时段：SQL语句需要临时工作区（比如排序）就会用到临时段
表空间（tablespace）：表空间是数据库的最大逻辑划分区域，用来存放表，索引，回滚段等数据对象，任何数据对象在创建时都必须指定存储在某个表空间中。
表空间与数据文件相对应，一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间，单个数据文件最大大小为（222-1）*DB_BLOCK_SIZE。如果是大文件表空间，则只有一个数据文件，最大可以到（232-1）*32K=128T。
例子：查看当前数据库的控制文件
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl NO 16384 596
/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl YES 16384 596
例子：查看当前数据库的数据文件及表空间
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf USERS
/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf UNDOTBS1
/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf SYSAUX
/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf SYSTEM
/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf EXAMPLE
例子：查到当前数据库的联机重做日志文件
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c06xhj1g_.log NO
3 ONLINE /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c06xhj37_.log YES
2 ONLINE /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c06xhdlj_.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c06xhdnc_.log YES
1 ONLINE /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c06xh9t5_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c06xh9xt_.log YES
例子：查到当前数据库的归档日志文件
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 47
Next log sequence to archive 49
Current log sequence 49
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
SQL> select name,sequence#,first_change# from v$archived_log;
NAME SEQUENCE# FIRST_CHANGE#
------------------------------------------------------------------------------------------ ---------- -------------
/u01/app/oracle/fast_recovery_area/STONE/archivelog/2015_09_24/o1_mf_1_5_c06xz88j_.arc 5 987870
/u01/app/oracle/fast_recovery_area/STONE/archivelog/2015_09_24/o1_mf_1_6_c0784v9r_.arc 6 997228
例子：查看当前数据库的参数文件
SQL> !ls /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora
例子：查看当前数据库的密码文件
SQL> !ls /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone
例子：查看当前数据库的跟踪文件以及告警文件
11g之前是由参数background_dump_dest指定位置，11g使用ADR后，在ADR对应目录下。
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/sto
ne/stone/trace
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/stone/stone
1 Diag Trace /u01/app/oracle/diag/rdbms/stone/stone/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/stone/stone/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/stone/stone/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/stone/stone/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/stone/stone/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/stone/stone/trace/stone_ora_678.trc
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
例子：查看当前数据库USER表空间DEPT的表段的段信息
SQL> select owner, segment_name, segment_type, tablespace_name, bytes, blocks
2 from dba_segments
3 where tablespace_name = 'USERS'
4 and segment_name = 'DEPT';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS
----- ------------ ------------ --------------- ---------- ----------
SCOTT DEPT TABLE USERS 65536 8
例子：查看当前数据库USER表空间DEPT的表段的区信息
SQL> select segment_name, extent_id, block_id, blocks
2 from dba_extents
3 where segment_name = 'DEPT';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
------------ ---------- ---------- ----------
DEPT 0 128 8
例子：查看块大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
交互过程
描述一下用户和服务器进程在不同的机器上面，通过网络与数据库进行交互的最基本的过程。
（1）数据库实例启动
（2）用户发起一个用户进程
（3）服务器上的监听检测到连接请求，创建一个服务器进程
（4）用户发起DML语句
（5）服务器进程收到DML语句，检查共享池中是否有同样的SQL语句的共享SQL区，如果有，服务器进程确认用户的访问权限并使用共享SQL区去处理这个语句，如果没有，分配新的共享SQL区去解析和处理语句。
（6）服务器进程从数据文件或者数据库缓冲区缓存获取必要的数据
（7）服务器进程在SGA中修改数据。事务提交后，LGWR将修改动作写入联机重做日志文件，DBWn在合适的时候将修改的块更新的磁盘上
（8）如果事务成功，服务器进程通过网络给应用发送消息，如果失败，将会返回错误
Installation
这里展示了如何在 Linux 环境下快速安装 Oracle 数据库软件并创建数据库。
环境配置
系统信息
| 序号 | 项目 | 信息 |
|---|---|---|
| 1 | OS Version | RHEL 6.7-x86_64 |
| 2 | Memory | 2G |
| 3 | Swap | 2G |
| 4 | Disk | 40G |
| 5 | Hostname | test |
| 6 | IP | 192.168.8.131 |
软件信息
| 序号 | 名称 | 版本 | 安装包 |
|---|---|---|---|
| 1 | Database | 11.2.0.4 | p13390677_112040_Linux-x86-64_1of7.zip p13390677_112040_Linux-x86-64_2of7.zip |
关闭防火墙
[root@test ~]# /etc/init.d/iptables stop
[root@test ~]# chkconfig iptables off
关闭 SELinux
修改文件 /etc/selinux/config，将SELINUX=enforcing修改为SELINUX=disabled
[root@test ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
修改完成后重启主机
[root@test ~]# init 6
关闭 NetworkManager
[root@test ~]# /etc/init.d/NetworkManager stop
Stopping NetworkManager daemon: [ OK ]
[root@test ~]# chkconfig NetworkManager off
配置地址映射
[root@test ~]# echo "192.168.8.131 test" >> /etc/hosts
配置 YUM
如果可以上外网，可以配置公网 YUM，如果不能上公网，可以配置本地 YUM。这里虚拟机使用光盘镜像配置本地 YUM。
[root@test ~]# mount /dev/cdrom /media/
[root@test ~]# cd /etc/yum.repos.d/
[root@test ~]# mkdir bak
[root@test ~]# mv *.repo bak/
[root@test ~]# vi local.repo
[Local]
baseurl=file:///media/Server
gpgcheck=0
enabled=1
EOF
[root@test ~]# yum clean all
[root@test ~]# yum makecache
安装依赖
[root@test ~]# yum install -y binutils* compat-libstdc++* elfutils-libelf* elfutils-libelf-devel* elfutils-libelf-devel-static* gcc* gcc-c++* glibc* glibc-common* glibc-devel* glibc-headers* kernel-headers* ksh* libaio* libaio* libgcc* libgomp* libstdc++* libstdc++-devel* make* sysstat* unixODBC* unixODBC-devel* compat-libcap1 xdpyinfo
创建用户和组
[root@test ~]# groupadd oinstall
[root@test ~]# groupadd dba
[root@test ~]# useradd -g oinstall -G dba oracle
[root@test ~]# echo "Abcd1234" | passwd --stdin oracle
[root@test ~]# mkdir -p /u01/
[root@test ~]# chown -R oracle:oinstall /u01/
[root@test ~]# chmod -R 775 /u01/
配置环境变量
[root@test ~]# vi ora_env.txt
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
ORACLE_SID=test; export ORACLE_SID
ORA_NLS33=$ORACLE_HOME/nls/admin/data; export ORA_NLS33
NLS_LANG=american_america.al32utf8;export NLS_LANG
NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss';export NLS_DATE_FORMAT
PATH=$ORACLE_HOME/bin:$PATH; export PATH
[root@test ~]# cat ora_env.txt >> /home/oracle/.bash_profile
[root@test ~]# su - oracle
[oracle@test ~]# env | grep ORACLE
ORACLE_SID=test
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
安装数据库
解压安装包启动安装程序
[oracle@test ~]# unzip p13390677_112040_Linux-x86-64_1of7.zip
[oracle@test ~]# unzip p13390677_112040_Linux-x86-64_2of7.zip
[oracle@test ~]# cd database/
[oracle@test database]$ export DISPLAY=192.168.8.1:0.0
[oracle@test database]$ ./runInstaller
在 “Configure Security Updates” 页面，不勾选 “I wish to receive security updates via My Oracle Support.”
在弹出的对话框，选择 “Yes”
在 “Download Software Updates” 页面，选择 “Skip software updates”
在 “Download Software Updates” 页面，选择 “Install databases software only”，只安装数据库
在 “Grid Installation Options” 页面，选择 “Single instance database installation”，安装单实例数据库
在 “Select Product Languages” 页面，保持默认选择的语言 “English”
在 “Select Database Edition” 页面，保持默认选择 “Enterprise Edition”
在 “Specify Installation Location” 页面，保持默认的 “Oracle Base”和“Software Location”，此处 “Oracle Base” 为环境变量 ORACLE_BASE 的值，“Software Location” 为环境变量 ORACLE_HOME 的值。
在 “Create Inventory” 页面，保持默认的 “Inventory Directory” 和 “oraInventory Group Name”
在 “Privileged Operating System Groups” 页面，保持默认的 “Database Administrator(OSDBA) Group”，“Database Operator(OSOPER) Group(Option)” 选择 “dba”。这里指定 OSDBA 和 OSOPER 组，默认情况下，指定 OSDBA 为 DBA 组，获取 SYSDBA 权限，指定 OSOPER 为 OPER 组，获取 SYSOPER 权限。这里由于单实例安装不执行职责划分，故都选择 DBA 组。因为 ORACLE 用户是属于 DBA 组的，故 ORACLE 用户就有了创建数据库的权限（SYSDBA）。
在 “Perform Prerequisite Checks” 页面，列出了不满足安装条件的项目，如果是缺少相关软件包，就需要先去安装软件包。
如果是系统参数不合适，可以点击 “Fix & Check Again”，弹出 “Execute Fixup Scripts” 对话框，提示生成了一个脚本：/tmp/CVU_11.2.0.4.0_oracle/runfixup.sh，执行该修复脚本的步骤如下：
- 打开一个新的终端窗口
- 使用 “root” 用户登录
- 运行该脚本
- 然后再点击该对话框的 “Ok”
执行结果如下：
[root@test ~]# /tmp/CVU_11.2.0.4.0_oracle/runfixup.sh
Response file being used is :/tmp/CVU_11.2.0.4.0_oracle/fixup.response
Enable file being used is :/tmp/CVU_11.2.0.4.0_oracle/fixup.enable
Log file location: /tmp/CVU_11.2.0.4.0_oracle/orarun.log
Setting Kernel Parameters...
/tmp/CVU_11.2.0.4.0_oracle/orarun.sh: line 246: [: 18446744073692774399: integer expression expected
The value for shmmax in response file is not greater than value of shmmax for current session. Hence not changing it.
The value for shmmni in response file is not greater than value of shmmni for current session. Hence not changing it.
/tmp/CVU_11.2.0.4.0_oracle/orarun.sh: line 337: [: 18446744073692774399: integer expression expected
The value for shmall in response file is not greater than value of shmall for current session. Hence not changing it.
The value for semmsl in response file is not greater than value of semmsl for current session. Hence not changing it.
The value for semmns in response file is not greater than value of semmns for current session. Hence not changing it.
The value for semmni in response file is not greater than value of semmni for current session. Hence not changing it.
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
uid=1000(oracle) gid=1000(oinstall) groups=1000(oinstall),1001(dba)
点击 “Execute Fixup Scripts” 对话框的 “Ok” 后，会再次进行检查，暂时解决不了又不影响安装的问题，勾选 “Ignore All”。
在 “Summary” 页面，点击 “Install” 进行安装。
如果操作系统为 Linux 7 版本，安装到 86% 时会出现如下报错：
Error in invoking target 'agent nmhs' of makefile '/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oraInventory/logs/installActions2023-11-22_07-56-10PM.log' for details.
解决方法为修改文件 $ORACLE_HOME/sysman/lib/ins_emagent.mk：
sed -i "s/(MK_EMAGENT_NMECTL)/(MK_EMAGENT_NMECTL) -lnnz11/g" $ORACLE_HOME/sysman/lib/ins_emagent.mk
然后在报错对话框中点击 “Retry”，继续安装。
安装到 94% 的时候，弹出 “Execute Configuration Scripts” 对话框，执行脚本的步骤如下：
- 打开一个新的终端窗口
- 使用 “root” 用户登录
- 依次运行脚本
- 然后再点击该对话框的 “Ok”
执行 /u01/app/oraInventory/orainstRoot.sh：
[root@test ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
执行 /u01/app/oracle/product/11.2.0/dbhome_1/root.sh：
[root@test ~]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
最后在 “Finish” 页面，提示安装成功。
创建数据库
Oracle 使用 dbca 命令来创建数据库，在创建数据库之前，需要考虑以下问题：
- 数据库的使用场景，事务性（OLTP）还是分析型（OLAP）
- 数据库的容量要求，数据的增长情况
- 数据库的性能要求，支持的并发数量
- 数据库的安全要求，需要的审计级别
- 数据库的高可用性，日志的归档处理
数据库安装完成后，命令行执行 dbca。
[oracle@test database]$ dbca
启动 Database Configuration Assistant，创建数据库。
选择 “Create a Database”。
保持默认选择创建数据库的模版 “General Purpose or Transaction Processing”。
输入全局数据库名和 SID，全局数据库名格式一般为 database_name.domain_name，SID 默认为数据库名称，唯一标识一个实例。注意，数据库名（db_name）不要超过 8 个字符，不然会被截断。
取消选择 “Configure Enterprise Manager”。此处如果选择 “Configure Enterprise Manager”，需要先配置监听。由于没有安装 Grid Control，故只能选择 “Configure Database Control for local management”。
在 “Automatic Maintenance Tasks” 页面，勾选 “Enable automatic maintenance tasks”，以便收集统计信息。
为 SYS 和 SYSTEM 用户指定密码，可以分别设置为不同的密码，也可以设置为同一个密码。
选择存储类型，“Storage Type” 可以选择 “File System” 或者 “Automatic Storage Management（ASM）”，这里没有配置 ASM，故选择 “File System”。存储位置 “Storage Locations” 选择 “Use Oracle-Managed Files”，选择 OMF 的好处是，在创建表空间或者为表空间增加数据文件的时候不需要指定数据文件的路径和文件名。点击 “Multiplex Redo and Control Files” 可以多路复用联机重做日志文件和控制文件。
保持默认勾选 “Specify Fast Recovery Area”，保持默认 “Fast Recovery Area”，根据归档日志的大小和保留时间设置 “Fast Recovery Area Size”，同时勾选 “Enable Archiving”，启用归档。
勾选 “Sample Schemas”，用于测试，包括常用的 HR 模式等。生产环境可以不安装。还可以在 “Custom Scripts” 指定数据库创建完成后需要运行的脚本。
指定内存大小，测试环境可以选择 “Typical”，保持默认勾选 “Use Automatic Memory Management” 使用自动内存管理。
根据最大并发数，指定可以同时连接到数据库的操作系统进程的最大数量，包括用户进程和数据库后台进程。由于前面选择的是 “General Purpose or Transaction Processing” 模块，“Block Size” 的大小默认为 8KB，不能修改。
指定字符集，选择Oracle推荐使用的 “Use Unicode（AL32UTF8）”。不要使用中文字符集 ZHS16GBK，生僻字会出现乱码。
Connect Mode 保持默认选择 “Dedicated Serve Mode”
查看确认前面的存储设置，如果选择了预定义的模版，则不能增加或者删除控制文件和数据文件。
可以调整 “Redo Log Groups” 的数量和大小。
保持默认选择 “Create Database”，创建数据库。
点击 “Ok” 开始创建。
创建完成后，需要注意以下内容：
- 安装日志文件位置
- Global Database Name（全局数据库名）
- System Identifier（SID）
- Server Parameter File name（spfile）
点击 “Password Management”，可以解锁用户并设置密码。
Instance
管理数据库需要使用客户端，Oracle 官方出品的客户端有：
- 命令行界面客户端：SQL*Plus，适用于数据库管理
- 图形化界面客户端：SQL Developer，适用于数据库开发
启动数据库
大多数情况下，使用 SQL*Plus 的 STARTUP 命令启动实例，挂载（mount）并打开（open）数据库。
启动分为 NOMOUNT，MOUNT，OPEN 三个阶段，顺序如下：
NOMOUNT
在数据库创建、重建控制文件或者某些备份和恢复时候，需要将实例启动到 nomount 模式
启动实例到 nomount 包括下面的内容：
- 在 $ORACLE_HOME/dbs 目录下按顺序查找查询下面文件：
- spfile<SID>.ora
- spfile.ora
- init<SID>.ora
- 分配 SGA
- 启动后台进程
- 打开 alter_<SID>.log 和 trace 文件
MOUNT
mount 数据库包括下面的内容：
- 从参数文件中找到控制文件的位置并打开
- 从控制文件中读取数据文件和联机重做日志文件的文件名和状态，但是不去验证数据文件和联机重做日志文件是否存在
可以在 mount 状态执行特定的维护操作，比如：
- 重命名数据文件
- 修改数据库的归档模式
- 执行完全的数据库恢复
OPEN
打开数据库包括：
- 打开数据文件
- 打开联机重做日志文件
任何一个数据文件或者联机重做日志文件不存在将会返回错误。Oracle 验证所有数据文件和联机重做日志文件，检查他们的一致性。
例子：启动数据库
#使用 SYSDBA 权限连接数据库
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 4 21:02:51 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
--直接启动到 OPEN
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动到 nomount
SQL> startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
--从nomount到mount
SQL> alter database mount;
Database altered.
--从mount到open
SQL> alter database open;
Database altered.
关闭数据库
使用 SQL*Plus 的 SHUTDOWN 命令关闭数据库。
正常关闭分为 CLOSE，NOMOUNT，SHUTDOWN 三个阶段，顺序如下：
关闭数据库有四个选项：
- abort：下次启动需要实例恢复，如非必要，不要使用，其他选项都不起作用了才用
- immediate：最常用的选项，回滚没有提交的事务，断开所有连接，下次启动无需实例恢复
- transactional：允许完成现有事务，但不能开始新的事务，下次启动无需实例恢复
- normal：等待会话断开，不能建立新的连接，下次启动无需实例恢复，默认的关闭选项
| Database Behavior | ABORT | IMMEDIATE | TRANSACTIONAL | NORMAL |
|---|---|---|---|---|
| Permits new user connections | No | No | No | No |
| Waits until current sessions end | No | No | No | Yes |
| Waits until current transactions end | No | No | Yes | Yes |
| Performs a checkpoint and closes open files | No | Yes | Yes | Yes |
例子：关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 826753024 bytes
Fixed Size 2257760 bytes
Variable Size 536874144 bytes
Database Buffers 281018368 bytes
Redo Buffers 6602752 bytes
Database mounted.
Database opened.
SQL> shutdown abort
ORACLE instance shut down.
参数
当启动数据库的时候，会去读初始化参数文件。有2种类型的参数文件
- Server Parameter File（spfile）：优先读取，二进制文件，只能由数据库进行读写，不能进行手动编辑，位于Oracle实例所在服务器上，不受数据库关闭的影响，默认的名称为spfile<SID>.ora，Oracle在启动的时候会自动去找这个名称的文件。
- Text initialization parameter file：文本文件，数据库只能读取，不能写入，可以使用文本编辑软件手动进行编辑，不受数据库关闭的影响，默认的名称为init<SID>.ora，如果Oracle在启动的时候没有找到spfile，则会自动找这个名称的文件。
Oracle推荐使用spfile维护初始化参数。
初始化参数分为基本和高级2类，在大多数情况下，只需要设置和调整大约30个基本参数就可以使数据库获得合理的性能。在极少数情况才需要调整高级参数获取优化的性能。Oracle 11gR2大约有314个高级参数。
例子：查看基本参数
SQL> select name,value from v$parameter where isbasic='TRUE';
NAME VALUE
------------------------------ ----------------------------------------------------------------------
processes 150
sessions 248
nls_language AMERICAN
nls_territory AMERICA
sga_target 0
control_files /u01/app/oracle/oradata/TEST/controlfile/o1_mf_koq84qpx_.ctl, /u01/app
/oracle/fast_recovery_area/TEST/controlfile/o1_mf_koq84qqt_.ctl
db_block_size 8192
compatible 11.2.0.4.0
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
cluster_database FALSE
db_create_file_dest /u01/app/oracle/oradata
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size 4385144832
undo_tablespace UNDOTBS1
instance_number 0
ldap_directory_sysauth no
remote_login_passwordfile EXCLUSIVE
db_domain
shared_servers 1
remote_listener
db_name test
db_unique_name test
open_cursors 300
star_transformation_enabled FALSE
pga_aggregate_target 0
30 rows selected.
常用的参数如下：
- DB_NAME和DB_DOMAIN：组成全局数据库名
- DB_RECOVERY_FILE_DEST和DB_RECOVERY_FILE_DEST_SIZE：指定快速恢复区位置和大小
- UNDO_TABLESPACE：指定UNDO表空间
- CONTROL_FILES：控制文件名称，Oracle建议多路复用控制文件
- DB_FILES：数据文件的最大数量，默认值为200
- PROCESSES：同时连接数据库的操作系统用户进程最大数量，包括后台进程，默认值为150
- DB_BLOCK_SIZE：表空间使用的数据库块大小，创建数据库时指定，后面不能更改，范围从2k到32k，默认为8k
- DB_CACHE_SIZE：标准块缓冲区缓存大小，如果设置了SGA_TARGET，则默认值为0
- SGA_TARGET：指定了SGA的总大小，如果设置了SGA，则下面的参数都为0，表示由系统自动调整大小，如果手动设置了非零值，则为最小值
- Buffer cache (DB_CACHE_SIZE)
- Shared pool (SHARED_POOL_SIZE)
- Large pool (LARGE_POOL_SIZE)
- Java pool (JAVA_POOL_SIZE)
- Streams pool (STREAMS_POOL_SIZE)
- MEMORY_TARGET：指定Oracle可用的内存，自动管理SGA和PGA
- 在文本初始化参数文件init<SID>.ora中，如果只设置了MEMORY_TARGET，没有设置MEMORY_MAX_TARGET，则数据库自动设置MEMORY_MAX_TARGET的值为MEMORY_TARGET的值；如果只设置了MEMORY_MAX_TARGET，没有设置MEMORY_TARGET，则MEMORY_TARGET的值默认为0
- 数据库启动后，可以使用ALTER SYSTEM动态调整MEMORY_TARGET，但不能超过MEMORY_MAX_TARGET
- PGA_AGGREGATE_TARGET：分配给所有服务器进程的PGA的总大小，不是在SGA中的，Oracle可用总内存减去SGA就是PGA
例子：查看某个参数
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 792M
memory_target big integer 792M
shared_memory_address integer 0
两种初始化参数类型：
- Static parameters（静态参数）：影响实例或者整个数据库，只能通过修改init.ora或者SPFILE文件，需要数据库重启才能生效，大约有110个
- Dynamic parameters（动态参数）：可以在数据库中使用alter session或者alter system进行修改，大约有234个，分为2类
- session-level parameters：只影响当前用户会话，例如nls参数，会话结束就失效了
- system-level parameters：影响整个数据库和所有会话，例如sga_target或者归档位置，需要使用scope选项，如果要让参数永久生效，就需要指定scope=both将参数设置添加到spfile中去
使用alter system设置或者修改初始化参数的值，scope选项指定修改的范围
- scope=spfile：只对服务器参数文件spfile进行修改，不对当前实例进行修改，在下次启动时永久生效，只能用于静态参数
- scope=memory：只对内存中的参数进行修改，当前实例立即生效，对于动态参数，不会去更新spfile，所以不会永久有效，静态参数不能使用这个选项
- scope=both：既修改服务器参数文件也修改内存中的参数，当前实例立即生效，对于动态参数，会去更新spfile，所以永久有效，静态参数不能使用这个选项
如果实例没有使用spfile启动，则指定scope=spfile或者scope=both会报错。如果使用spfile启动实例，则默认选项是scope=both，如果使用文本参数文件启动实例，则默认选项是scope=memory。
可以通过V$PARAMETER查看参数的类型以及修改方式。
例子：在会话级别修改nls_date_format参数
SQL> select sysdate from dual;
SYSDATE
------------
14-DEC-15
SQL> alter session set nls_date_format='mon dd yyyy';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------
dec 14 2015
告警日志
每个数据库都有一个名字为alert_<sid>.ora的告警日志文件，如果设置了$ORACLE_BASE，则默认位于$ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace目录下。
告警文件按照时间先后顺序记录如下信息：
- 使用非默认的初始化参数启动数据库
- 所有的内部错误（ORA-600），块错误（ORA-1578），死锁错误（ORA-60）
- 管理操作，例如使用 SQL 语句创建，修改，删除数据库和表空间，使用 EM 或者 SQL*Plus 启动、关闭数据库，归档日志以及恢复数据库
- 专有服务模式或者共享服务模式调度进程的一些信息和错误
- 物化视图自动刷新错误
由于告警文件的大小会不断增长，所以需要经常查看，定期备份并删除。当数据库重新去写告警文件的时候，会自动重新创建一个。
Oracle 还有一个 XML 版本的告警文件，位于 $ORACLE_BASE/diag/rdbms/<db_name\>/<SID\>/alert 目录下。
例子：查看告警日志文件位置
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- --------------------- ------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/test/test
1 Diag Trace /u01/app/oracle/diag/rdbms/test/test/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/test/test/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/test/test/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/test/test/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/test/test/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_7678.trc
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
例子：使用 tail 查看告警日志
[oracle@t ~]$ tail -10f /u01/app/oracle/diag/rdbms/test/test/trace/alert_test.log
Setting Resource Manager plan SCHEDULER[0x32DD]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Nov 04 22:00:00 2022
Starting background process VKRM
Fri Nov 04 22:00:00 2022
VKRM started with pid=26, OS id=7154
Fri Nov 04 22:00:04 2022
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Fri Nov 04 22:00:32 2022
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Networking
Oracle Net Services
Oracle Net Services 是一套提供连接解决方案的网络组件，在客户端或者数据库和数据库之间建立网络连接。在网络会话建立后，Oracle Net 为客户端应用程序和数据库服务器扮演信使的角色。在客户端应用程序和数据库服务器之间建立和维护连接以及交换信息。Oracle Net（或者其他模拟 Oracle Net 的组件，例如 JDBC）位于需要与数据库对话的计算机上面。
在客户端计算机上面，Oracle Net 是一个后台组件，服务于应用程序到数据库的连接。
在数据库服务器上面，Oracle Net 包括一个称之为 “Oracle Net Listener” 的活动进程，它负责协调数据库和外部应用程序之间的连接。
Oracle Net Listener
Oracle Net Listener（一般简称为监听）是运行在数据库服务器上的进程，处理客户端的连接请求，一个监听可以服务多个数据库实例及多个客户端连接。
客户端通过监听建立连接的步骤：
- 客户端进程或者其他数据库请求连接
- 监听将请求转发到对应的服务器进程（server process）
- 客户端进程直接连接到服务器进程，监听不再参与通信
可以使用 lsnrctl 控制监听，包括启动、停止、检查状态、重载、动态配置多个监听以及修改监听密码等。
如果不指定监听名称或者没有使用 set current_listener 命令，则对默认监听（名称为 listener）进行操作。
例子：查看监听帮助
[oracle@test ~]$ lsnrctl help
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-NOV-2022 22:26:38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
例子：关闭监听
[oracle@test ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-NOV-2022 22:28:55
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
例子：启动监听
[oracle@test ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-NOV-2022 22:29:01
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-NOV-2022 22:29:01
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
The listener supports no services
The command completed successfully
例子：查看监听状态
[oracle@mysql ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-NOV-2022 22:28:43
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-NOV-2022 22:26:07
Uptime 0 days 0 hr. 2 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
Service Names
数据库通过设置参数 SERVICE_NAMES 使用服务名称（service name）区分来自不同应用的连接，数据库在启动时，会将服务名称注册到监听。启动后如果新增加了服务名称，或者先启动数据库后启动监听，则 PMON 进程会将如下实例信息注册到监听：
- 服务名称
- 实例名称及其当前和最大负载
- 实例的服务器进程，包括其类型、协议地址以及当前和最大负载
例子：增加服务名称并注册到监听
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string test
SQL> alter system set service_names='test,ios,android,web';
System altered.
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string test,ios,android,web
查看监听状态
[oracle@test ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-NOV-2022 22:54:15
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-NOV-2022 22:29:01
Uptime 0 days 0 hr. 25 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Services Summary...
Service "android" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "ios" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "web" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
服务注册有以下2种方式：
- 动态服务注册（Dynamic Service registration）：Oracle 8i 以及后续的版本可以在数据库启动的时候自动注册到默认监听，不需要额外的配置
- 静态服务注册（Static Service registration）：Oracle 早期版本不能自动注册，因此需要一个包含所有数据库服务列表的监听配置文件
listener.ora。现在的版本也可以在以下情况使用静态服务注册：- 监听不在默认端口 1521
- 应用需要静态服务注册
Naming Methods
Oracle Net 有多种方法解析客户端的连接信息：
- Easy connect naming：客户端连接到数据库服务器使用 TCP/IP 连接串，包括主机名或 IP 地址，端口以及服务名，格式为：
CONNECT username/password@host[:port]\[/service_name] - Local naming：使用本地配置文件
tnsnames.ora中的连接串 - Directory naming：使用兼容的 LDAP 目录服务器
- External naming：NIS 等
Easy Connect
客户端使用简单连接，需要提供所有信息，使用下面的格式：
<username>/<password>@<hostname>:<listener port>/<service name>
- 默认启用
- 不需要客户端配置
- 只支持 TCP/IP（no SSL）
- 不支持高级连接选项，比如：
- 故障切换
- 资源路由
- 负载均衡
例子：客户端使用简单连接到数据库
[oracle@test ~]$ sqlplus hr/hr@192.168.44.138:1521/test
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 5 23:13:21 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Local Naming
客户端使用本地命名，用户需要为 Oracle Net service 提供一个别名，相比简单连接只需记住一个简短的别名，Oracle Net 将别名与已知服务名的本地列表进行对比，服务名的本地列表存储在客户端的文本文件 <oracle_home>/network/admin/tnsnames.ora 中，可以使用 TNS_ADMIN 环境变量修改这个默认位置，经常用于应用程序与数据库的连接。
- 要求客户端文件
tnsnames.ora - 支持所有 Oracle Net 协议
- 支持高级连接选项，比如：
- 故障切换
- 资源路由
- 负载均衡
例子：客户端使用本地命名连接到数据库
#创建一个服务别名test
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
test =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.44.138)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=test))
)
[oracle@test ~]$ sqlplus hr/hr@test
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 5 23:27:00 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
使用与 ping 类似的 tnsping 工具测试 Oracle Net 服务别名：
- 在客户端和服务器端确认连接
- 不验证请求的服务是否可用
- 支持简单连接
- 支持本地和目录命名
该工具只验证到达监听的主机名（IP），端口和协议，不去检查监听是否处理对应的服务。
例子：测试服务别名
[oracle@test ~]$ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-NOV-2022 23:30:36
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.44.138)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=test)))
OK (0 msec)
Database Link
通过创建 Database Link 模式对象在数据库之间建立连接。
语法：
CREATE DATABASE LINK <remote_global_name>
CONNECT TO <user> IDENTIFIED BY <pwd>
USING '<connect_string_for_remote_db>';
其中 '<connect_string_for_remote_db>' 既可以使用简单连接，也可以使用本地命名或者目录命名，如果使用本地命名，需要先配置 tnsnames.ora 文件。
例子：创建 Database Link 并通过数据字典查看
SQL> conn hr/hr
Connected.
SQL> create database link remote1
2 connect to hr identified by hr
3 using '192.168.44.138:1521/test';
Database link created.
SQL> select count(*) from employees@remote1;
COUNT(*)
----------
107
SQL> desc user_db_links;
Name Null? Type
----------------------------------------- -------- ----------------------------
DB_LINK NOT NULL VARCHAR2(128)
USERNAME VARCHAR2(30)
PASSWORD VARCHAR2(30)
HOST VARCHAR2(2000)
CREATED NOT NULL DATE
SQL> select * from user_db_links;
DB_LINK USERNAME PASSWORD HOST CREATED
---------- ---------- ---------- ------------------------------ ------------
REMOTE1 HR 192.168.230.138:1521/stone 17-DEC-15
Storage
Physical Storage Structures
关系数据库的一个特点就是逻辑数据结构独立于物理存储结构。由于物理结构和逻辑结构是分开的，因此可以管理数据的物理存储，而不会影响对逻辑结构的访问。例如，重命名数据库文件不会重命名存储在其中的表。
Oracle 数据库（这里单指数据库，不包括实例）是一系列存放在磁盘上的文件集合。包括：
- 数据文件（Data files）和临时文件（temp files）
- 控制文件（Control files）
- 联机重做日志文件（Online redo log files）
数据文件
Oracle 数据库以物理方式将表空间数据存储在数据文件中。表空间和数据文件密切相关，但有重要区别：
每个表空间由一个或多个数据文件组成
数据库的数据集中存储在位于每个表空间中的数据文件中
段可以跨越一个或多个数据文件，但不能跨越多个表空间
数据库必须有 SYSTEM 和 SYSAUX 表空间，Oracle 数据库会在数据库创建期间自动为 SYSTEM 表空间分配数据库的第一个数据文件
SYSTEM 表空间包含数据字典，即一组包含数据库元数据的表;通常，数据库还具有UNDO表空间和临时表空间
例子：查看数据文件
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------- --------------------
/u01/app/oracle/oradata/TEST/datafile/o1_mf_users_koq832n4_.dbf USERS
/u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_koq832mm_.dbf UNDOTBS1
/u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_koq832mg_.dbf SYSAUX
/u01/app/oracle/oradata/TEST/datafile/o1_mf_system_koq832lh_.dbf SYSTEM
/u01/app/oracle/oradata/TEST/datafile/o1_mf_example_koq850om_.dbf EXAMPLE
控制文件
数据库控制文件是一个仅与一个数据库关联的小型二进制文件。
控制文件是用于查找数据库文件和管理数据库状态的根文件。控制文件包含如下信息：
数据库名称和数据库唯一标识符（DBID）
数据库创建的时间戳
有关数据文件、联机重做日志文件和归档日志文件的信息
表空间信息
RMAN 备份
控制文件用于以下用途：
包含有关打开数据库所需的数据文件、联机重做日志文件等的信息。控制文件跟踪数据库的结构更改。例如，当管理员添加、重命名或删除数据文件或联机重做日志文件时，数据库将同步更新控制文件。
包含在数据库未打开时必须可访问的元数据。例如，控制文件包含恢复数据库所需的信息，包括检查点。
Oracle 数据库在数据库使用期间连续读取和写入控制文件，并且必须在数据库打开时可供写入。例如，恢复数据库涉及从控制文件中读取数据库中包含的所有数据文件的名称。其他操作（如添加数据文件）会更新存储在控制文件中的信息。
Oracle 数据库支持同时打开多个相同的控制文件。通过在不同磁盘上多路复用控制文件，数据库可以实现控制文件的冗余，从而避免单点故障。
例子：查看控制文件
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ----------------------------------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/TEST/controlfile/o1_mf_koq84qpx_.ctl NO 16384 594
/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_koq84qqt_.ctl YES 16384 594
联机重做日志
联机重做日志是数据库恢复的关键文件，由两个或多个预分配文件组成，存储对数据库的更改。
Oracle 数据库将每个事务同步写入重做日志缓冲区，然后将其写入联机重做日志。
数据库实例的联机重做日志称为重做线程。在单实例配置中，只有一个实例访问数据库，因此只存在一个重做线程。但是，在 Oracle Real Application Clusters（Oracle RAC）配置中，两个或多个实例同时访问一个数据库，每个实例都有自己的重做线程，可避免争用。
联机重做日志由两个或多个联机重做日志文件组成。Oracle 数据库至少需要两个文件，以保证一个文件始终可用于写入，而另一个文件正在归档（如果数据库处于 ARCHIVELOG 模式）。
Oracle 数据库一次仅使用一个联机重做日志文件来存储从重做日志缓冲区写入的记录。LGWR 进程正在写入的联机重做日志文件称为当前联机重做日志文件。
当数据库停止写入一个联机重做日志文件并开始写入另一个日志文件时，将发生日志切换。通常，日志切换发生在当前联机重做日志文件已写满且必须继续写入时。但是也可以将日志切换配置为定期进行，而不管当前联机重做日志文件是否已填满。
LGWR 循环写入联机重做日志文件:
在非归档模式下，一个联机重做日志文件写满后，可立即写下一个联机重做日志文件；而在归档模式下，需要确认下一个联机重做日志文件已经归档了才能写入。
Oracle 数据库可以在不同的位置自动维护两个或多个相同的联机重做日志副本。联机重做日志组由联机重做日志文件及其冗余副本组成。每个相同的副本都是联机重做日志组的成员。维护联机重做日志组的多个成员可防止重做日志丢失。理想情况下，成员的位置应位于单独的磁盘上，以便一个磁盘的故障不会导致整个联机重做日志丢失。
例子：查看联机重做日志
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ----------------------------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_3_koq84vky_.log NO
3 ONLINE /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_koq84vm7_.log YES
2 ONLINE /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_2_koq84tr8_.log NO
2 ONLINE /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_2_koq84tss_.log YES
1 ONLINE /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_1_koq84sy6_.log NO
1 ONLINE /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_1_koq84t0l_.log YES
6 rows selected.
Logical Storage Structures
Oracle 数据库为数据库中的所有数据分配逻辑空间。数据库空间分配的逻辑单元是数据块（data block）、区（extent）、段（segment）和表空间（tablespace）。在物理级别，数据存储在磁盘上的数据文件中，数据文件中的数据存储在操作系统块中。
数据块
Oracle 数据库以数据块为单位管理数据库数据文件中的逻辑存储空间。数据块是数据库 I/O 的最小单位，由多个操作系统块组成。
数据块可以分为三部分：
- 块头：块头包含段类型（比如表或索引），数据块地址，表目录，行目录，以及约 23 个字节的事务槽，用于块内行的修改。块头从顶部向下增长。
- 行数据：这是块中的行的实际数据。行数据从底部向上增长。
- 空闲空间：空闲空间在块的中间，使块头和行数据都能增长。插入新行或者使用更大的值更新现有行都将占用空闲空间。
区
区是由连续数据块组成的逻辑单元。
默认情况下，数据库会在创建数据段时为其分配初始区。区不能跨数据文件。
尽管尚未向段添加任何数据，但初始区中的数据块仍保留给此段。每个段的第一个数据块包含该段中区的目录。
如果初始区已满，并且需要更多空间，则数据库会自动为此段分配增量区。增量区是为线创建的后续区。
分配算法取决于表空间是本地管理还是字典管理。在本地管理情况下，数据库在数据文件的位图中搜索相邻的空闲块。如果数据文件空间不足，则数据库将在另一个数据文件中查找。段的区始终位于同一表空间中，但可能位于不同的数据文件中。
删除表中的数据不会回收数据区块，除非使用 DROP 命令删除对象。
在某些情况下，可以手动释放空间。Oracle Segment Advisor 根据对象的碎片级别确定对象是否有可用于回收的空间。以下技术可以释放区：
使用
online segment shrink（在线段收缩）来回收段中的碎片空间将非分区表或表分区的数据移动到新段中
重建或合并索引
截断表
释放未使用的空间，这会释放数据库段高水位线端的未使用空间，并使该空间可用于表空间中的其他段
释放区后，Oracle 数据库会修改本地管理表空间数据文件中的位图，以将重新获得的区标记为可用空间。释放的区块中的任何数据都将无法访问。
段都由以区表示存储参数定义。这些参数控制 Oracle 数据库如何为段分配可用空间。
存储设置按以下优先级顺序确定，前面的设置将覆盖后面的设置：
段存储子句
表空间存储子句
数据库默认值
本地管理表空间可以具有统一的区大小，也可以具有由系统自动确定的可变区大小：
- 自动：表空间的区大小由系统管理。不能为临时表空间（temporary tablespace）指定自动方式。
- 统一：表空间的区大小一致，默认大小为 1MB。所有的临时表空间都采用这种方式。不能为 UNDO 表空间指定统一方式。
段
段由区组成，是占用存储空间的数据库对象，分为：
- 用户段
- 临时段
- UNDO 段
用户段
存储用户对象数据，包括：
- 表及表分区
- LOB 及LOB 分区
- 索引及索引分区
创建一个表段：
创建一个表段，同时还创建索引段以及 LOB 段：
例子：查看用户段
SQL> select segment_name,segment_type,bytes/1024/1024 Mb from dba_segments where owner='HR' and segment_name like 'EMP%';
SEGMENT_NAME SEGMENT_TYPE MB
-------------------- ------------------ ----------
EMPLOYEES TABLE .0625
EMP_DEPARTMENT_IX INDEX .0625
EMP_EMAIL_UK INDEX .0625
EMP_EMP_ID_PK INDEX .0625
EMP_JOB_IX INDEX .0625
EMP_MANAGER_IX INDEX .0625
EMP_NAME_IX INDEX .0625
临时段
处理查询时，Oracle 数据库通常需要临时工作区来执行 SQL 语句的中间阶段。可能需要临时段的典型操作包括排序、哈希和合并位图。在创建索引时，Oracle 数据库还会将索引段放入临时段中，然后在索引完成后将其转换为永久段。
如果可以在内存中执行操作，Oracle 数据库不会创建临时段。但是如果无法使用内存，则数据库会自动在磁盘上分配一个临时段。
Oracle 数据库在用户会话期间根据需要为查询分配临时段，并在查询完成时删除这些段。对临时段的更改不会记录在联机重做日志中。
数据库在分配给用户的临时表空间中创建临时段。
Oracle 数据库还可以为临时表及其索引分配临时段。临时表处理仅在事务或会话期间存在的数据。每个会话仅访问为本会话分配的区，而无法访问为其他会话分配的区。
Oracle 数据库在临时表发生第一个 INSERT 时为该表分配段。
临时表的段在当前用户的临时表空间中分配。假定分配给用户 1 的临时表空间是 temp1，分配给用户 2 的临时表空间是 temp2。在这种情况下，user1 将临时数据存储在 temp1 段中，而用户 2 将临时数据存储在 temp2 段中。
例子：查看用于排序的临时段
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,added_extents,extent_hits,freed_extents,free_requests,max_blocks,max_used_blocks,max_sort_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_BLOCKS MAX_USED_BLOCKS MAX_SORT_BLOCKS
-------------------- ------------- ------------ ----------- ----------- ------------- ----------- ------------- ------------- ---------- --------------- ---------------
TEMP 0 3584 0 3584 0 6 0 0 3584 256 128
例子：查看临时段使用情况
SQL> select username,session_num,sql_id,tablespace,contents,segtype,segfile#,segblk#,extents,blocks from v$tempseg_usage;
USERNAME SESSION_NUM SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS
------------ ----------- ------------- ----------- --------- --------- ---------- ---------- ---------- ----------
TSTUSR 3449 g9u0thas9acfr TEMP TEMPORARY LOB_DATA 1001 190976 1 128
TSTUSR 41551 fzzbk2zw05r5d TEMP TEMPORARY LOB_DATA 1001 624640 1 128
UNDO 段
Oracle 数据库维护事务操作的记录，统称为 UNDO 数据。Oracle 数据库使用 UNDO 执行以下操作：
回滚活动事务
恢复已终止事务
提供读一致性
执行一些逻辑闪回操作
Oracle 数据库将 UNDO 数据存储在数据库内。对 UNDO 块的更改会生成 REDO。
UNDO 数据存储在 UNDO 表空间中。Oracle 数据库提供了一种完全自动化的机制（称为 automatic undo management mode），用于管理 UNDO 表空间中的 UNDO 段和空间。
例子：查看回滚段
SQL> select tablespace_name,owner,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME OWNER SEGMENT_NAME STATUS
-------------------- ------ ---------------------- ----------------
SYSTEM SYS SYSTEM ONLINE
UNDOTBS1 PUBLIC _SYSSMU10_1197734989$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU9_1650507775$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU8_517538920$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU7_2070203016$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU6_1263032392$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU5_898567397$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU4_1254879796$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU3_1723003836$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU2_2996391332$ ONLINE
UNDOTBS1 PUBLIC _SYSSMU1_3724004606$ ONLINE
11 rows selected.
表空间
表空间存放段。
数据库必须具有 SYSTEM 和 SYSAUX 表空间。
- SYSTEM：Oracle服务器使用系统表空间来管理数据库。它包括包含数据库管理信息的数据字典和表，这些都包含在SYS模式下，并且只能由SYS用户或其他有相关权限的管理用户访问。
- SYSAUX：SYSTEM表空间的一个辅助表空间。以前SYSTEM表空间中的一些部件和产品现在放到了SYSAUX表空间，10g及以后的版本的数据库必须有一个SYSAUX表空间。
- TEMP：当执行一个需要创建临时段（如大的排序或索引的创建）的SQL语句就需要临时表空间。与为每个用户分配一个默认的表空间存储创建的数据对象一样，也会为每个用户分配一个临时表空间。最好的做法是定义数据库的默认临时表空间，新创建的用户无需设置就可以使用。在预先设定的数据库，TEMP表空间就是默认的临时表空间。这意味着，如果创建用户的时候没有指定临时表空间，Oracle数据库的就将TEMP分配给该用户。
- UNDOTBS1：用于数据库服务器存储UNDO信息。如果数据库使用自动UNDO管理，那么在任何时候只能使用一个UNDO表空间。这个表空间是在创建数据库时创建的。
- USERS：用于存储用户对象和数据。在创建用户的时候如果没有指定默认表空间，则该用户的默认表空间就是USERS。对于SYS和SYSTEM用户，默认的永久表空间是SYSTEM。
- EXAMPLE：示例表空间。
一个表空间可以有三种不同的状态。不同表空间的类型有不同的状态。
Read Write：表空间联机，可以读取和写入。
Read Only：现有的事务可以完成（提交或回滚），但不允许新的DML操作。只读状态的表空间是联机的。不能设置SYSTEM和SYSAUX表空间为只读。也不能设置UNDO和TEMP表空间为只读。
Offline：可以把一个在线表空间脱机，让这部分数据暂时不可用，其他数据还是可用的。offline有以下选项：
Normal：如果表空间的所有数据文件脱机时没有出现错误则可以正常脱机。在脱机的时候通过对所有数据文件发出检查点确保所有数据写入到磁盘。
Temporary：如果表空间的数据文件脱机时出现错误可以临时脱机。Oracle将数据文件脱机并发出检查点。如果表空间的一个或多个文件由于写错误脱机，然后使用offline temporary将表空间脱机，再联机就需要进行恢复。
Immediate：表空间可以采取脱机而不对数据库文件发出检查点。如果使用offline immediate将表空间脱机，再联机就需要先对表空间进行介质恢复。如果数据库运行在NOARCHIVELOG模式下，则不能使用immediate选项。
注意：SYSTEM表空间和TEMP表空间不能脱机。
例子：查看表空间及其对应的数据文件
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_data_files order by 1;
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
EXAMPLE /u01/app/oracle/oradata/TEST/datafile/o1_mf_example_lovwqmoj_.dbf .305786133 31.9999847 YES
SYSAUX /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_lovwpm10_.dbf .5078125 31.9999847 YES
SYSTEM /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_lovwpm0o_.dbf .732421875 31.9999847 YES
UNDOTBS1 /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_lovwpm11_.dbf .087890625 31.9999847 YES
USERS /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_lovwpm1b_.dbf .004882813 31.9999847 YES
例子：设置 UNDO 表空间数据文件为固定大小
SQL> alter database datafile '/u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_lovwpm11_.dbf' resize 1G;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_lovwpm11_.dbf' autoextend off;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_lovwpm11_.dbf 1 0 NO
例子：查看临时表空间及使用情况
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_temp_files order by 1;
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TEMP /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_lovwqmd1_.tmp .028320313 31.9999847 YES
SQL> select tablespace_name,TABLESPACE_SIZE/1024/1024/1024 Total_Gb,ALLOCATED_SPACE/1024/1024/1024 Allo_Gb,FREE_SPACE/1024/1024/1024 Free_Gb from dba_temp_free_space;
TABLESPACE_NAME TOTAL_GB ALLO_GB FREE_GB
-------------------- ---------- ---------- ----------
TEMP .028320313 .028320313 .02734375
例子：设置临时表空间为固定大小
SQL> alter database tempfile '/u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_lovwqmd1_.tmp' resize 1G;
Database altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_lovwqmd1_.tmp' autoextend off;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_lovwpm11_.dbf 1 0 NO
OMF
使用OMF（Oracle Managed Files）避免了在Oracle数据库中直接管理操作系统文件，特定的操作只需要使用数据库对象而不需要使用文件名，下面的数据库结构都可以使用OMF：
- 表空间
- 重做日志文件
- 控制文件
- 归档文件
- 块改变跟踪文件
- 闪回日志
- RMAN备份
通过配置如下参数，使用OMF：
| Parameter | Description |
|---|---|
| DB_CREATE_FILE_DEST | Defines the location of the default file system directory for data files and temporary files |
| DB_CREATE_ONLINE_LOG_DEST_n | Defines the location for redo log files and control file creation |
| DB_RECOVERY_FILE_DEST | Default location for the fast recovery area |
例子：查看参数DB_CREATE_FILE_DEST，并创建表空间不指定数据文件，使用默认值自动创建数据文件（大小100M，自动扩展，增量100M）
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata
SQL> create tablespace tbs1;
Tablespace created.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_data_files where tablespace_name='TBS1';
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TBS1 /u01/app/oracle/oradata/TEST/datafile/o1_mf_tbs1_low28qk8_.dbf .09765625 31.9999847 YES
例子：为表空间增加数据文件
SQL> alter tablespace tbs1 add datafile;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_data_files where tablespace_name='TBS1';
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TBS1 /u01/app/oracle/oradata/TEST/datafile/o1_mf_tbs1_low28qk8_.dbf .09765625 31.9999847 YES
TBS1 /u01/app/oracle/oradata/TEST/datafile/o1_mf_tbs1_low29jn5_.dbf .09765625 31.9999847 YES
例子：为临时表空间增加临时文件
SQL> alter tablespace temp add tempfile;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_temp_files order by 1;
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TEMP /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_low2djyt_.tmp .09765625 31.9999847 YES
TEMP /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_lovwqmd1_.tmp 1 0 NO
SQL> alter database tempfile '/u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_low2djyt_.tmp' resize 1G;
Database altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_low2djyt_.tmp' autoextend off;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 Gb,maxbytes/1024/1024/1024 MGb,autoextensible from dba_temp_files order by 1;
TABLESPACE_NAME FILE_NAME GB MGB AUT
-------------------- ---------------------------------------------------------------------- ---------- ---------- ---
TEMP /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_low2djyt_.tmp 1 0 NO
TEMP /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_lovwqmd1_.tmp 1 0 NO
Users
Account
用户账户（user account）：
- 一个唯一的用户名，不超过30个字节，以字母开头，不能包含特殊字符
- 一种认证方式，最常用的就是密码
- 一个默认表空间，注意授权和配额
- 一个临时表空间，用于排序和临时表，无需配额
- 一个用户资源文件，用于资源和密码限制
- 一个初始消费组，用于资源管理器
- 一个账户状态，打开、锁住和过期状态
模式（schema）：
- 用户对象的集合
- 与用户账户同名
预定义管理账户：
- SYS账户
- 授予DBA角色及其他角色
- 可以进行启动、关闭数据库以及其他维护命令
- 用于数据字典和自动负载知识库（Automatic Workload Repository AWR）
- SYSTEM账户被授予DBA，MGMT_USER和AQ_ADMINISTRATOR_ROLE角色
SYS和SYSTEM账户默认授予DBA角色。此外，SYS帐户拥有所有权限和数据字典。连接到SYS帐户，如果是数据库实例必须使用AS SYSDBA，如果是ASM实例必须使用AS SYSASM。被授予SYSDBA权限的任何用户都可以使用AS SYSDBA连接到SYS帐户。只有被授予了SYSDBA、SYSOPER或者SYSASM权限的用户才可以启动和关闭实例。SYSTEM帐户没有SYSDBA权限，被授予MGMT_USER和AQ_ADMINISTRATOR_ROLE角色。在数据库中SYS和SYSTEM账户是必需的，不能被删除。
例子：创建用户并指定默认表空间
SQL> conn / as sysdba
Connected.
SQL> create user user01 identified by user01 default tablespace tbs1;
User created.
创建用户的时候，必须选择身份验证技术：
- Password：在设置密码时，可以立即将该密码过期，迫使用户在第一次登录后更改密码。在11g中创建的所有密码是默认区分大小写。升级到11g的数据库的密码不区分大小，但是修改后就区分大小写了。
- External： Oracle将身份验证委托给外部服务，比如操作系统，网络认证服务或者外部认证服务，不需要输入口令。如果使用操作系统认证，设置OS_AUTHENT_PREFIX初始化参数，创建同名的操作系统用户和数据库用户，并在数据库用户名前面使用此参数作为前缀，此参数默认为OPS$字符串。当OS_AUTHENT_PREFIX为空时，表示如果操作系统上存在与数据库用户同名的用户即可直接登录数据库。
- Global：通过Oracle Internet Directory认证用户
例子：创建操作系统用户test，创建数据库用户test，用户名前缀为OPS$的用户，测试是否进行外部认证
先创建操作系统用户test并设置密码，然后切换到该用户，设置环境变量
[root@test ~]# useradd test
[root@test ~]# passwd test
Changing password for user test.
New password:
BAD PASSWORD: it is too simplistic/systematic
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.
[root@test ~]# su - test
[test@test ~]$ id test
uid=502(test) gid=502(test) groups=502(test)
[test@test ~]$ export ORACLE_SID=test
[test@test ~]$ export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH
[test@test ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
创建数据库用户test并授予权限
SQL> show parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
SQL> create user ops$test identified by external;
User created.
SQL> grant connect to ops$test;
Grant succeeded.
在操作系统用户test上是使用外部认证登录数据库
[test@test ~]$ sqlplus /
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:49:24 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
管理员安全
- 对于授权用户SYSDBA和SYSOPER的连接，只有在使用密码文件或者操作系统权限验证后才能获得授权。
- 如果使用操作系统身份验证，数据库不使用提供的用户名和密码。
- 如果没有密码文件，或者提供的用户名或密码不在密码文件中，或没有提供用户名和密码，则使用操作系统身份验证。
- 在Oracle 11g密码文件中的密码默认区分大小。
- 如果通过密码文件认证成功，则会使用用户名记录连接。如果通过操作系统认证成功，则使用”CONNECT /”连接并不记录该用户。
注意：如果是操作系统的OSDBA和OSOPER组的成员，并且以 SYSDBA 或 SYSOPER 身份进行连接，不论指定了什么用户名和密码，都会带有相关管理权限进行连接。
例子：密码文件重命名后，本地登录正常，远程登录失败
重命名密码文件：
[oracle@test ~]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtest /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtest1
本地登录正常：
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 18 00:01:18 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
远程登录失败：
C:\Users\test>sqlplus sys/123456@192.168.230.138:1521/test as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 18 00:02:41 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误
Privilege
System Privileges
系统权限（System Privileges）：允许用户对数据库执行特定操作，例如创建表空间就是一个系统权限，有超过170种系统权限，很多系统权限包括ANY关键字
授予带有“ANY”关键字的权限表示可以跨模式。如果只有CREATE TABLE的权限，只能在自己的模式下创建表，如果有SELECT ANY TABLE权限则可以查看其他用户的表。SYS用户和有DBA角色的用户被授予了所有带“ANY”关键字的权限，可以对任何数据对象进行任何操作。
授予系统权限的SQL语法：
GRANT <system_privilege> TO <grantee clause> [WITH ADMIN OPTION]
WITH ADMIN OPTION表示可以将该系统权限进行转授权。
授予管理员的系统权限有：
- RESTRICTED SESSION：即使数据库在restricted模式，也可以登录
- SYSDBA和SYSOPER：这两个权限可以进行关闭启动数据库，执行恢复以及其他管理任务，但是SYSOPER不能查看用户数据。包含如下系统权限：
- STARTUP and SHUTDOWN
- CREATE SPFILE
- ALTER DATABASE OPEN/MOUNT/BACKUP
- ALTER DATABASE ARCHIVELOG
- ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE, requires connecting as SYSDBA.)
- RESTRICTED SESSION
- SYSDBA系统权限还可以进行不完全恢复以及删除数据库。SYSDBA系统权限允许用户使用SYS用户进行连接。
- SYSASM：该权限允许启动、关闭和管理ASM实例。
- DROP ANY object：该权限删除任何对象的权限，允许删除其他模式用户的对象。
- CREATE，MANAGER，DROP和ALTER TABLESPACE：该权限允许创建、管理、删除和修改表空间。
- CREATE LIBRARY：Oracle数据库允许开发人员从PL/SQL创建和调用外部代码（例如一个C库）。该库必须由数据库中的库对象命名。创建库的权限允许用户创建一个任意的代码库。
- CREATE ANY DIRECTORY：作为一个安全措施，操作系统目录下的代码必须链接到一个数据库虚拟目录对象。使用创建任何目录权限，可以调用不安全的代码的对象。创建任何目录的权限允许用户创建一个目录对象（具有读写访问），对应可以访问的任何目录。这意味着用户可以在这些目录中访问外部程序。用户可以尝试直接读取和写入任何数据库文件，如数据文件、重做日志和审计日志。
- GRANT ANY OBJECT PRIVILEGE：允许在不拥有的对象上授予对象权限。
- ALTER DATABASE and ALTER SYSTEM：允许修改数据库和实例（例如，重命名一个数据文件或刷新缓冲区高速缓存）。
Object Privileges
对象权限（Object Privileges）：允许用户对数据库对象执行特定的操作，如果不另外授权，用户只能访问自己的对象。
授予对象权限的SQL语法：
GRANT <object_privilege> ON <object> TO <grantee clause>
[WITH GRANT OPTION]
权限的回收可以参考：回收权限
Role
角色就是一组相关权限的集合，使用角色可以简化权限的管理。
更简单的权限管理：使用角色来简化权限管理。你可以将权限授予一个角色，然后给每个用户授予这个角色，而不是给予相同的权限集。
动态权限管理：当角色的权限被修改时，所有被授予角色的用户都会自动获得修改后的权限。
权限的选择可用性：角色可以启用或者禁用，用于临时打开或者关闭权限。
角色特性：
- 可以对角色进行授予和回收权限。
- 角色可以授予给其他用户或者角色，也可以从其他用户和角色回收。
- 角色可以包括系统权限和对象权限
- 可以对用户进行启动或者禁用角色
- 角色可以使用启用密码
- 角色不属于任何人，不属于任何模式
预定义角色有：
| Role | Privileges Included |
|---|---|
| CONNECT | CREATE SESSION |
| RESOURCE | CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE |
| SCHEDULER_ ADMIN | CREATE ANY JOB, CREATE EXTERNAL JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULER |
| DBA | Most system privileges; several other roles. Do not grant to nonadministrators. |
| SELECT_ CATALOG_ROLE | No system privileges; HS_ADMIN_ROLE and over 1,700 object privileges on the data dictionary |
- 当运行数据库创建脚本时，会自动定义数据库的几个角色。
- 出于安全考虑，从10.2.0版本起，CONNECT角色只有CREATE SESSION权限。
- 注意授予RESOURCE角色包括UNLIMMITED TABLESPACE权限。
角色的创建和授予参考：创建和授予权限给角色
Profile
资源文件（profile）可以用来对用户所能使用的数据库资源进行限制，用户一次只能分配一个资源文件，通过资源文件可以控制用户的资源消耗，管理账户状态以及密码失效条件。如果用户在更改其资源文件时已登录，该更改将在下一次登录生效。
默认的资源文件作为所有其他资源文件的基础。资源文件中的限制可以显式指定，也可以无限制，还可以参考默认资源文件。
资源文件生效需要将初始化参数RESOURCE_LIMIT设置为true，默认为false，但资源文件密码设置仍强制有效。
可以使用资源文件控制下面系统资源：
- Cpu_per_session：指定会话的CPU时间限制，单位为百分之一秒。
- Cpu_per_call：指定一次调用（解析、执行和提取）的CPU时间限制，单位为百分之一秒。
- Network/Memory：可以指定如下：
- Connect_time：指定会话的总的连接时间，以分钟为单位。
- Idle_time：指定会话允许连续不活动的总的时间，以分钟为单位，超过该时间，会话将断开。但是长时间运行查询和其他操作的不受此限制。
- Concurrent Sessions：表示使用数据库用户帐户可以创建多个并发会话。
- Private_sga：指定一个会话可以在共享池（SGA）中所允许分配的最大空间，以字节为单位。（该限制只在使用共享服务器结构时才有效，会话在SGA中的私有空间包括私有的SQL和PL/SQL，但不包括共享的SQL和PL/SQL）。
- Disk I/O：限制用户可以在每一个会话级或每一个call级别读取的数据量。包括从内存和磁盘读取的总量。可以限制I/O密集型语句过度使用内存和磁盘。
例子：修改密码不过期
SQL> conn / as sysdba
Connected.
SQL> select * from dba_profiles where resource_name='PASSWORD_LIFE_TIME' and profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- -------------------- -------- ----------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
SQL> alter profile default limit password_life_time unlimited;
Profile altered.
SQL> select * from dba_profiles where resource_name='PASSWORD_LIFE_TIME' and profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- -------------------- -------- ----------
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
Locking
在数据库允许会话修改数据之前，会话必须先锁定正在修改的数据。锁使会话对数据进行独占控制，其他会话在锁释放前无法修改锁定的数据，只能排队等待。
事务可以锁定单行数据，多行数据，甚至整个表。Oracle支持手动和自动锁定。自动获得的锁始终选择最低级别的锁，以尽量减少与其他事务潜在的冲突。
事务完成后（提交或者回滚）所有的锁被释放。如果事务失败，后台进程自动回滚失败的事务并释放该事务所持有的所有锁。
默认行级锁模式，不同的事务可以在同一个表中更新不同的行，而不互相干扰。
DML Locks
每个DML事务获取2个锁：
- 行上面的EXCLUSIVE锁
- 表级（TM）上的ROW EXCLUSIVE（RX）。这可以防止在更新的时候其他会话锁定整个表（或删除或截断它）。这种模式也被称为subexclusive表锁（SX）。
例子：更新表的行，在另个一个会话删除这个表
会话1
SQL> update employees set salary=10000 where employee_id=100;
1 row updated.
会话2
SQL> drop table employees;
drop table emp
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
锁冲突的原因
未提交更改，最常见的原因。
长时间运行的事务：许多应用程序使用批处理来执行批量更新。这些批处理作业通常是在非业务时间执行，但在某些情况下，他们可能没有完成或可能需要太长时间运行。当事务和批处理同时执行时，会经常出现锁冲突。
不必要的高锁定级别：不是所有的数据库都支持行级锁。某些数据库仍只能在页或表级锁定。开发者编写运行在许多不同的数据库的应用程序的时候，经常人为的高级别锁定，就使得Oracle数据库的行为类似于其他只能表级锁定的数据库系统。
为了解决锁冲突，持有该锁的会话必须释放它。让会话释放锁最好的方式是联系用户完成事务。如果联系不了用户，则需要DBA介入处理。
例子：使用V$SESSION查询锁并终止会话
SQL> select sid,serial#,username from v$session where sid in (select blocking_session from v$session);
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 7 HR
SQL> alter system kill session '1,7' immediate;
System altered.
Deadlocks
死锁是锁冲突的一个特殊例子。互相等待对方完成事务。
Oracle数据库自动检测死锁并终止错误的语句。对该错误的正确响应是提交或回滚，将释放该会话中的任何其他锁，以便其他会话可以继续它的事务。
例子：死锁
--会话1：
SQL> update employees set salary=salary*1.1 where employee_id=100;
1 row updated.
--会话2：
SQL> update employees set manager_id=100 where employee_id=102;
1 row updated.
--会话1：
SQL> update employees set salary=salary*1.1 where employee_id=102;
--会话2：
SQL> update employees set manager_id=101 where employee_id=100;
--会话1：
SQL> update employees set salary=salary*1.1 where employee_id=102;
update employees set salary=salary*1.1 where employee_id=102
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
--回滚：
SQL> rollback;
Rollback complete.
--会话2：
SQL> update employees set manager_id=101 where employee_id=100;
1 row updated.
Undo
Undo Data
undo数据是：
- 数据修改前的拷贝
- 用于变更数据的事务
- 至少要保留到事务结束
- 用于支持：
- 回滚操作
- 读一致性操作
- 闪回查询，闪回事务和闪回表
- 恢复失败的事务
当数据库中的一个进程更改数据时，Oracle将保存旧的数据（undo data）。undo可以让你回滚未提交的数据。undo支持读一致性和闪回查询。undo还可以“倒带”（闪回）事务和表。
读一致查询提供了数据一致的查询结果，读一致性查询要成功，原始数据必须要作为undo存在。如果原始数据不再可用，将收到一个“snapshot too old”的错误（ora-01555）。只要undo还在，Oracle数据库就可以重建数据以满足读一致的查询。
undo也用来恢复失败的事务。当用户决定提交或回滚事务时，用户会话结束异常（可能是由于网络错误或客户机上的故障），就发生了一次失败的事务。失败的事务也可能发生在实例崩溃或发出SHUTDOWN ABORT命令。事务失败时，数据库回退由用户所做的所有更改，从而恢复原始数据。
undo至少要保留到事务结束：
- 用户取消事务（事务回滚）。
- 用户结束事务（事务提交）。
- 用户执行DDL语句，如CREATE、DROP、RENAME或ALTER语句。如果当前事务中包含任何DML语句，数据库首先提交事务，然后执行并提交DDL。
- 用户会话终止异常（事务回滚）。
- 用户会话正常退出（事务提交）。
当一个事务开始时，被分配一个undo段。在整个事务的生命周期中，当数据改变时，复制原始数据到undo段。通过V$TRANSACTION动态性能视图可以查看将哪些事务分配给了哪个undo段。
undo段是由实例自动创建的特殊段，用于支持事务处理。与其他段一样，undo段也是由区构成。根据需要undo段自动增长和收缩。
在完成事务之前或者用完所有undo空间之前，事务根据需要会一直写undo段的区。如果区被写满，事务就会从段的下一个区获取空间。
例子：查看undo段
SQL> select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1';
OWNER SEGMENT_NAME MB
---------- ------------------------------ ----------
SYS _SYSSMU10_1197734989$ 2.125
SYS _SYSSMU9_1650507775$ 2.125
SYS _SYSSMU8_517538920$ 2.125
SYS _SYSSMU7_2070203016$ 2.125
SYS _SYSSMU6_1263032392$ 1.125
SYS _SYSSMU5_898567397$ 2.125
SYS _SYSSMU4_1254879796$ 2.125
SYS _SYSSMU3_1723003836$ 2.125
SYS _SYSSMU2_2996391332$ 1.125
SYS _SYSSMU1_3724004606$ 2.125
10 rows selected.
undo数据在undo段中，undo段只能在undo表空间中。（不能在undo表空间中创建其他段类型，比如表）
虽然数据库可能有多个undo表空间，但只能指定一个作为当前的undo表空间。
Oracle自动创建undo段，所有者为SYS用户。由于undo段类似一个环形区域，每个段至少有2个区。默认最大区数量取决于数据库的块大小（8 kb大小的块有32765个区）。
undo表空间是永久的表空间，本地管理的表空间，自动区扩展，由数据库自动管理。
例子：查看当前的undo表空间及大小
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME MB
------------------------------ -------------------------------------------------- ----------
UNDOTBS1 +DATA/stone/datafile/undotbs1.258.893370691 100
例子：查看哪些会话使用了undo段及使用大小
SQL> select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb
2 From dba_rollback_segs r,v$rollstat v,v$transaction t,v$session s
3 Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
4 order by segment_name;
SID SERIAL# SQL_ID USN SEGMENT_NAME STATUS MB
---------- ---------- ------------- ---------- ------------------------------ ---------------- ----------
78 5253 6 _SYSSMU6_1263032392$ ONLINE 1.1171875
| Undo | Redo | |
|---|---|---|
| Record of | How to undo a change | How to reproduce a change |
| Used for | Rollback, read consistency, flashback | Rolling forward database changes |
| Stored in | Undo segments | Redo log files |
| Protects against | Inconsistent reads in multiuser systems | Data loss |
undo和redo用于不同的地方。如果需要撤消更改就需要undo。如果需要重新进行更改，则需要redo。为保障数据不丢失，undo块更改也会写入redo。
事务提交后，LGWR将事务的变化写入联机重做日志文件。虽然被更改的数据可能尚未写入到数据文件，写入到重做日志文件就足够保证数据库的一致性了。
如果在进行提交之前停电，那么下次启动就会需要进行实例恢复，SMON进程读取redo重新进行更改，然后使用undo进行回滚，以保证数据的一致性。
Managing Undo
Oracle数据库提供了自动undo管理，可以为所有会话在专门的undo表空间中完全自动化的管理undo信息和表空间。系统自动为undo信息提供最佳的保留时间。更确切地说，自动扩展的undo表空间，undo数据保留时间要比运行时间最长的查询稍长；固定大小的undo表空间，数据库动态地调整最佳的保留时间。
undo_retention初始化参数指定已提交的undo数据至少要保留多少秒。为自动扩展undo表空间设置尽可能长的最小undo保留时间以满足可能发生的最长的闪回操作。对于自动扩展undo表空间，undo数据至少要保留该参数指定的时间，并根据查询对undo的需求自动进行调整。但这种自动调整保留时间可能不能满足某些闪回操作。
针对固定大小的undo表空间，系统根据undo表空间的大小和历史使用情况，自动调整最佳undo保存时间；如果没有启用保留保证（RETENTION GUARANTEE），将会忽略undo_retention参数。
undo信息分为三类：
- 未提交的undo信息（主动）：支持正在运行的事务，回滚或者事务失败需要这些信息，不会被覆盖。
- 已提交的undo信息（未过期）：不再需要支持运行的事务，但仍需要满足undo保留时间间隔。也被称为“未过期”的undo信息。
- 过期undo信息（过期）：不再需要支持运行的事务。当活动事务请求undo空间的时候，将覆盖过期的undo信息。
如果undo空间不足，为保证活动事务成功，那么默认会覆盖尚未过期的已提交事务的undo信息。 可以通过设置保留保证（RETENTION GUARANTEE）来改变这个默认行为。如果设置了保留保证，那么undo数据就必须保留设置的时间，即使其他的事务会因获取不到足够的undo空间而失败。
RETENTION GUARANTEE是UNDO表空间的属性而不是一个初始化参数。可以用SQL命令行语句修改。语法：
SQL> alter tablespace undotbs1 retention guarantee;
返回到正常设置，使用下面的命令：
SQL> alter tablespace undotbs1 retention noguarantee;
最佳实践是将undo表空间设置为固定大小，如果决定设置undo表空间为固定大小，必须设置足够大才能避免以下错误：
- DML的失败
- “snapshot too old”错误
例子：更换一个小的undo表空间，修改一张大表，然后再查询，模拟ora-01555错误
创建undo表空间
SQL> create undo tablespace undotbs2 datafile '+DATA' size 1m retention noguarantee;
Tablespace created.
指定该表空间为当前undo表空间
SQL> alter system set undo_tablespace=undotbs2 scope=memory;
System altered.
创建一张大表
SQL> conn hr/hr
Connected.
SQL> create table emp as select * from employees;
Table created.
SQL> insert into emp select * from emp;
107 rows created.
SQL> insert into emp select * from emp;
214 rows created.
SQL> insert into emp select * from emp;
428 rows created.
SQL> insert into emp select * from emp;
856 rows created.
SQL> insert into emp select * from emp;
1712 rows created.
SQL> insert into emp select * from emp;
3424 rows created.
SQL> insert into emp select * from emp;
6848 rows created.
SQL> insert into emp select * from emp;
insert into emp select * from emp
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
SQL> commit;
Commit complete.
更新所有的行
SQL> declare
2 begin
3 for i in 1..10000 loop
4 update emp set salary=i where employee_id=100;
5 commit;
6 end loop;
7 end;
8 /
在其他会话进行查询
SQL> select salary from emp;
SALARY
----------
3800
3600
2900
2500
4000
3900
ERROR:
ORA-01555: snapshot too old: rollback segment number 17 with name
"_SYSSMU17_1622872040$" too small
9180 rows selected.
修改undo表空间
SQL> alter system set undo_tablespace=undotbs1;
System altered.
Flashback
使用闪回技术处理逻辑错误，可以快速简单恢复数据。使用闪回技术，可以定位人为错误影响的对象和行数据并进行修复。下面列出的闪回技术，闪回数据库使用闪回日志，闪回删除使用回收站，其他技术使用undo数据。
| Object Level | Scenario Examples | Flashback Technology | Depends On | Affects Data |
|---|---|---|---|---|
| Database | Truncate table; Undesired multitable changes made | Database | Flashback logs | TRUE |
| Table | Drop table | Drop | Recycle bin | TRUE |
| Table | Update with the wrong WHERE clause | Table | Undo data | TRUE |
| Table | Compare current data with data from the past | Query | Undo data | FALSE |
| Table | Compare versions of a row | Version | Undo data | FALSE |
| Table | Keep historical transaction data | Data Archive | Undo data | TRUE |
| Transaction | Investigate and back out suspect transactions | Transaction | Undo/redo from Archive logs | TRUE |
不是所有的闪回技术都会修改数据库，有些技术只是查询数据的其他版本，调查问题辅助恢复。
启用闪回，需要：
- 一个有足够大小的UNDO表空间，存放闪回操作所需要的数据。用户更新数据越频繁，所需空间越多。如果不能确定表空间大小，可以先设置UNDO表空间为自动扩展，通过V$UNDOSTAT视图查看一段时间UNDO块信息，并计算空间需求，然后再设置UNDO表空间为合适固定大小。
- 默认启用Automatic Undo Management。
- 对于固定大小的UNDO表空间，则Oracle自动调整UNDO保留时间。
- 对于自动扩展的UNDO表空间，则通过UNDO_RETENTION参数指定UNDO保留最小时间，默认为900秒。
通过V$UNDOSTAT视图的TUNED_UNDORETENTION字段查看事务提交后对应的undo数据保留的时间。设置UNDO_RETENTION参数不会保证没有过期的数据不会被覆盖。如果系统需要更多的UNDO空间，则会覆盖最近产生的未过期的UNDO数据。
- 为UNDO表空间指定RETENTION GUARANTEE语句确保不会覆盖没有过期的UNDO数据。
- 授予闪回权限给用户、角色或者应用。
创建闪回数据归档，满足长时间的保留要求。
例子：查看V$UNDOSTAT视图获取UNDO信息
SQL> SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
2 TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
3 UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON",
4 MAXQUERYLEN, TUNED_UNDORETENTION
5 FROM v$UNDOSTAT;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXCON MAXQUERYLEN TUNED_UNDORETENTION
------------------- ------------------- ---------- ---------- ---------- ---------- ----------- -------------------
01/29/2016 16:36:07 01/29/2016 16:37:30 2 0 0 0 563 1403
01/29/2016 16:26:07 01/29/2016 16:36:07 2 35 162 3 563 1403
01/29/2016 16:16:07 01/29/2016 16:26:07 2 23 272 1 1167 2007
01/29/2016 16:06:07 01/29/2016 16:16:07 2 32 146 3 567 1407
01/29/2016 15:56:07 01/29/2016 16:06:07 2 142 413 3 1169 2010
01/29/2016 15:46:07 01/29/2016 15:56:07 2 16 192 2 569 1409
01/29/2016 15:36:07 01/29/2016 15:46:07 2 34 171 4 1173 2013
01/29/2016 15:26:07 01/29/2016 15:36:07 2 28 165 1 573 1413
01/29/2016 15:16:07 01/29/2016 15:26:07 2 13 139 1 1175 2015
例子：查看DBA_FREE_SPACE视图获取UNDO表空间剩余大小
SQL> SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS1';
SUM(BYTES)
----------
561774592
例子：查看DBA_UNDO_EXTENTS视图获取当前UNDO表空间EXTENT的使用情况
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS SUM(BYTES) COUNT(*)
--------- ---------- ----------
UNEXPIRED 21430272 27
EXPIRED 9502720 40
例子：查看V$TRANSACTION视图获取当前事务的UNDO使用情况
SQL> SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS='ACTIVE';
XIDUSN XIDSLOT XIDSQN USED_UBLK
---------- ---------- ---------- ----------
5 30 3187 1
Flashback Query
闪回查询使用SELECT语句中的AS OF子句，查询指定时间的数据。AS OF子句可以使用timestamp或者SCN指定时间戳。
例子：闪回查询
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mI:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-01-29 19:06:49
SQL> update employees set salary=10000 where employee_id=200;
1 row updated.
SQL> commit;
Commit complete.
SQL> select employee_id,salary from employees as of timestamp to_timestamp('2016-01-29 19:06:49','yyyy-mm-dd hh24:mi:ss') where employee_id=200;
EMPLOYEE_ID SALARY
----------- ----------
200 4400
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
4446226
SQL> update employees set salary=20000 where employee_id=200;
1 row updated.
SQL> commit;
Commit complete.
SQL> select employee_id,salary from employees as of scn 4446226 where employee_id=200;
EMPLOYEE_ID SALARY
----------- ----------
200 10000
如果发现数据错误，可以使用闪回查询的结果回到以前的数据。
例子：使用闪回查询结果修改数据
SQL> update employees set salary=(select salary from employees as of timestamp to_timestamp('2016-01-29 19:06:49','yyyy-mm-dd hh24:mi:ss') where employee_id=200) where employee_id=200;
1 row updated.
SQL> select employee_id,salary from employees where employee_id=200;
EMPLOYEE_ID SALARY
----------- ----------
200 4400
SQL> commit;
Commit complete.
Flashback Version Query
闪回查询是查询某一个时间点的数据，闪回版本查询使用VERSIONS子句查询某一段时间行的所有版本。
闪回版本查询的返回结果是行的改变历史，且只返回已提交的行，包括删除和重新插入的行版本。提供了一种审计表行的方式，并可以获取改变行的事务信息。可以使用返回的事务标识符，通过LogMiner执行日志挖掘，或者进行闪回事务查询。
其中VERSION_XID是一个伪列，返回对应行版本的事务标识符。
例子：闪回版本查询
SQL> select versions_xid,salary from employees
2 versions between scn 4446226 and 4447033
3 where employee_id=200;
VERSIONS_XID SALARY
---------------- ----------
06001100B60D0000 30000
08001800080D0000 4400
070007000B0C0000 20000
10000
VERSIONS子句不能查询以下类型的表：
- 外部表（External tables）
- 临时表（Temporary tables）
- 固定表（Fixed tables）
VERSIONS子句同样不能查询视图，但视图定义可以使用VERSIONS子句。
如果有DDL语句修改了表的结构，则SELECT语句中的VERSIONS子句不能跨该DDL语句显示该表行的版本。也就是说，闪回版本查询会在表结构改变这个时间点停止查询。
Flashback Table
使用闪回表，可以将表恢复到某个时间点而不需要进行基于时间点的恢复操作。
在线进行闪回表操作，只回滚修改的表及其依赖的对象。
闪回表语句作为单个事务执行，要么所有表闪回成功，要么整个事务回滚。
可以使用闪回版本查询和闪回事务查询确定闪回的时间点。
必须授予用户FLASHBACK TABLE或者FLASHBACK ANY TABLE系统权限。
必须对需要闪回的表启用行迁移。
注意事项：
- 整个闪回表语句是一个事务。要么都执行要么都不执行。
- 闪回表获得表的排他DML锁。
- 受影响对象的统计信息不会被闪回。
- 维护现有的索引，但删除的索引不会重建。对提交物化视图的依赖也自动维护。
- 如果在闪回的过程中违反了任何约束，则闪回操作终止。
- 闪回表操作不能跨DDL（只改变表存储属性的DDL语句除外）。
- 不能闪回系统表，远程表和固定表。
例子：闪回表
SQL> create table test1 as select * from employees;
Table created.
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2022-11-11 14:27:17
SQL> select count(*) from test1;
COUNT(*)
----------
107
SQL> delete test1;
107 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test1;
COUNT(*)
----------
0
SQL> alter table test1 enable row movement;
Table altered.
SQL> flashback table test1 to timestamp to_timestamp('2022-11-11 14:27:17','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from test1;
COUNT(*)
----------
107
Flashback Transaction Query
闪回事务查询是一个诊断工具，可以查看在事务级别对数据库所做的更改。使用户能够在数据库中诊断问题并进行分析和审核。
可以使用FLASHBACK_TRANSACTION_QUERY视图来确定所有必要的SQL语句，用于撤消指定的事务或者指定的时期内所做的更改。
例子：闪回事务查询
SQL> create table emp as select * from employees where 1=0;
Table created.
SQL> insert into emp select * from employees where employee_id=100;
1 row created.
SQL> commit;
Commit complete.
SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_name='EMP';
XID START_SCN COMMIT_SCN ROW_ID OPERATION UNDO_SQL
---------------- ---------- ---------- ------------------- ---------- ----------------------------------------------------------
010020005C0C0000 4542902 4542905 AAAV9xAAEAAAAI/AAA INSERT delete from "HR"."EMP" where ROWID = 'AAAV9xAAEAAAAI/AAA';
注意事项：
- 在数据库中，DDL操作是一系列的空间管理操作和修改数据字典。对DDL的闪回事务查询显示了数据字典的变化。
- 如果从数据库中删除表，闪回事务查询显示对象编号而不会显示表名。
- 如果从数据库中删除用户，闪回事务查询显示用户ID而不是用户名。
- 当某个事务没有足够的UNDO数据，则FLASHBACK_TRANSACTION_QUERY的OPERATION字段的值为UNKNOWN。
Flashback Transaction
使用闪回事务，可以回退事务及其依赖事务，相当于创建了一个补偿事务用于回退不必要的更改。
使用闪回事务，必须启用补充日志并授予正确的权限。例如，HR用户想为REGIONS表使用闪回事务，SYSDBA需要在SQL*Plus中执行如下命令：
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
grant execute on dbms_flashback to hr;
grant select any transaction to hr;
例子：启用闪回事务
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data(primary key) columns;
Database altered.
SQL> grant execute on dbms_flashback to hr;
Grant succeeded.
SQL> grant select any transaction to hr;
Grant succeeded.
例子：使用DBMS_FLASHBACK进行闪回事务
SQL> create table emp as select * from employees where 1=0;
Table created.
SQL> insert into emp select * from employees where employee_id=100;
1 row created.
SQL> commit;
Commit complete.
SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_name='EMP';
XID START_SCN COMMIT_SCN ROW_ID OPERATION UNDO_SQL
---------------- ---------- ---------- ------------------- ---------- -----------------------------------------------------------
010020005C0C0000 4542902 4542905 AAAV9xAAEAAAAI/AAA INSERT delete from "HR"."EMP" where ROWID = 'AAAV9xAAEAAAAI/AAA';
SQL> select * from emp;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- --------- ----- ------------ --------- ------- ------ -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 10000 90
SQL> conn / as sysdba
Connected.
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('010020005C0C0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from hr.emp;
no rows selected
SQL> rollback;
Rollback complete.
SQL> select * from hr.emp;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- --------- ----- ------------ --------- ------- ------ -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 10000 90
事务间存在的依赖关系主要有以下三种：
- Write-after-write dependency：Transaction 1 changes a row of a table, and later transaction 2 changes the same row.（事务1更改了表的行，后续的事务2又更改了相同的行）
- Primary key dependency：A table has a primary key constraint on column c. In a row of the table, column c has the value v. Transaction 1 deletes that row, and later transaction 2 inserts a row into the same table, assigning the value v to column c.（表的C列为主键约束，表的某一行的C列的值为V，事务1删除了该行，后续的事务2插入了一行，且C列的值为V，即在一张拥有主键的表中TX1首先删除了一行，之后TX2又插入了具有相同主键值的另一行。）
- Foreign key dependency：In table b, column b1 has a foreign key constraint on column a1 of table a. Transaction 1 changes a value in a1, and later transaction 2 changes a value in b1.（表b中列b1有一个外键约束参考表a的列a1，事务1更改了a1的一个值，后续的事务2修改了b1的一个值，即由于TX1的修改（insert或update）而产生了新的可被外键参考的字段值，之后TX2修改（insert或update）外键字段时利用了TX1所产生的字段值。）
如果使用默认的NOCASCADE选项执行闪回事务失败，表明存在依赖的事务，需要更改闪回选项，Oracle提供了四种闪回选项：
- NOCASCADE（无级联，默认）：若检测到Transaction之间存在依赖关系，则无法进行回退。
- NONCONFLICT_ONLY（仅限无冲突）：仅回退Transaction里不存在依赖关系的SQL，保证事务的一致性，会破坏事务的完整性。
- NOCASCADE_FORCE（强制无级联）：若Transaction间仅存在”Write-after-write dependency”类型的依赖关系，则可以实施强行回退；若遇到除”Write-after-write dependency”以外的其它类型的依赖关系，则无法进行回退。
- CASCADE（级联）：对存在依赖关系的Transaction实施连带回退。
例子：对三种依赖关系应用这四种闪回选项
（1）Write-after-write dependency
先构造表和数据
SQL> conn / as sysdba
Connected.
SQL> create table emp as select employee_id,department_id from hr.employees where 1=0;
Table created.
SQL> create table dept as select department_id,department_name from hr.departments where 1=0;
Table created.
SQL> insert into emp values(1,1);
1 row created.
SQL> commit;
Commit complete.
以上一个插入为事务1
SQL> insert into emp values(2,2);
1 row created.
SQL> insert into emp values(3,4);
1 row created.
SQL> commit;
Commit complete.
以上2个插入为事务2
SQL> update emp set department_id=3 where department_id=4;
1 row updated.
SQL> commit;
Commit complete.
以上更新为事务3
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 2
3 3
SQL> col versions_starttime format a25;
SQL> col versions_endtime format a25;
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from emp versions between scn minvalue and maxvalue t;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPLOYEE_ID DEPARTMENT_ID
------------------------- ------------------------- ---------------- - ----------- -------------
31-JAN-16 06.25.56 PM 050017002E0D0000 U 3 3
31-JAN-16 06.25.31 PM 31-JAN-16 06.25.56 PM 09000A00890D0000 I 3 4
31-JAN-16 06.25.31 PM 09000A00890D0000 I 2 2
31-JAN-16 06.24.28 PM 02001F003B0D0000 I 1 1
使用NOCASCADE选项，对事务2进行闪回，由于后续的事务3对事务2的数据进行了修改，故不能执行成功。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 4
表的内容没有变化：
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 2
3 3
使用NONCONFLICT_ONLY选项，对事务2进行闪回。事务2有两条insert语句，事务3的update语句依赖于事务2的第2条insert语句，所以仅回退事务2里的1条insert语句。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nonconflict_only);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
回滚闪回事务。
SQL> rollback;
Rollback complete.
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 2
3 3
使用NOCASCADE_FORCE选项，对事务2进行闪回。事务2的两条insert语句回退了，事务3里的update虽然是基于事务2里第一条insert的结果，update的结果还是被保留下来了，可以看出NOCASCADE_FORCE在处理具有"Write-after-write dependency"依赖关系的记录时不会考虑记录间的依赖关系仅回退指定事务的操作。（此处结果与NONCONFLICT_ONLY，是否有问题）
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
SQL> rollback;
Rollback complete.
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 2
3 3
使用CASCADE选项，对事务2进行闪回。要回退的是事务2，因为事务3依赖于事务2，所以事务3随着事务2一起被回退，并且回退的顺序应该是先回退事务3再回退事务2。可见CASCADE会把与要回退的事务存在依赖关系的事务一起回退。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
SQL> rollback;
Rollback complete.
SQL> select * from emp;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 2
3 3
（2）Primary key dependency
必须打开primary key supplemental logging，否则执行dbms_flashback.transaction_backout时会出现ORA-55511: Flashback Transaction experienced error in executing undo SQL。
SQL> alter database add supplemental log data(primary key) columns;
Database altered.
构造表和数据
SQL> create table emp1(employee_id number primary key,department_id number);
Table created.
SQL> insert into emp1 values(1,1);
1 row created.
SQL> insert into emp1 values(2,2);
1 row created.
SQL> commit;
Commit complete.
以上2个insert语句为事务1。
SQL> insert into emp1 values(3,3);
1 row created.
SQL> delete emp1 where employee_id=2;
1 row deleted.
SQL> commit;
Commit complete.
以上的insert和delete为事务2。
SQL> insert into emp1 values(2,4);
1 row created.
SQL> commit;
Commit complete.
以上的insert为事务3。
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
2 4
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from emp1 versions between scn minvalue and maxvalue t;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPLOYEE_ID DEPARTMENT_ID
------------------------- ------------------------- ---------------- - ----------- -------------
31-JAN-16 08.31.08 PM 0A0007008D0C0000 I 2 4
31-JAN-16 08.30.47 PM 03001D005B0D0000 D 2 2
31-JAN-16 08.30.47 PM 03001D005B0D0000 I 3 3
31-JAN-16 08.29.14 PM 31-JAN-16 08.30.47 PM 01000000700C0000 I 2 2
31-JAN-16 08.29.14 PM 01000000700C0000 I 1 1
使用NOCASCADE选项，对事务2进行闪回，由于后续的事务3对事务2的数据进行了修改，故不能执行成功。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 4
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
2 4
使用NONCONFLICT_ONLY选项，对事务2进行闪回。事务3里的insert依赖于事务2里的delete，所以仅回退了事务2里的insert，NONCONFLICT_ONLY在flashback时会避开有依赖关系的行。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nonconflict_only);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 4
SQL> rollback;
Rollback complete.
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
2 4
使用NOCASCADE_FORCE选项，对事务2进行闪回。NOCASCADE_FORCE仅能回退存在"Write-after-write dependency"依赖关系的事务，如果依赖关系来自于primary key或者foreign key则无法实现回退。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-55511: Flashback Transaction experienced error in executing undo SQL
ORA-00001: unique constraint (ORA-00001: unique constraint (SYS.SYS_C0011795) violated
.) violated
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 4
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
2 4
使用CASCADE选项，对事务2进行闪回。要回退的是事务2，但事务3依赖于事务2，Cascade能够将有级联关系的事务一起回退掉。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
2 2
SQL> rollback;
Rollback complete.
SQL> select * from emp1;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
1 1
3 3
2 4
（3）Foreign key dependency
必须开启foreign key supplemental logging才能追踪foreign key的依赖关系。
SQL> alter database add supplemental log data(foreign key) columns;
Database altered.
构造表和数据
SQL> create table dept2(department_id number primary key,department_name varchar2(20));
Table created.
SQL> create table emp2(employee_id number,department_id number references dept2(department_id));
Table created.
SQL> insert into dept2 values(1,'a');
1 row created.
SQL> commit;
Commit complete.
以上1个insert为事务1。
SQL> insert into dept2 values(2,'b');
1 row created.
SQL> insert into dept2 values(3,'c');
1 row created.
SQL> commit;
Commit complete.
以上2个insert为事务2。
SQL> insert into emp2 values(200,2);
1 row created.
SQL> commit;
Commit complete.
以上1个insert为事务3。
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from dept2 versions between scn minvalue and maxvalue t;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------------------- ---------------- - ------------- --------------------
31-JAN-16 09.25.27 PM 06000700170E0000 I 3 c
31-JAN-16 09.25.27 PM 06000700170E0000 I 2 b
31-JAN-16 09.25.12 PM 07001400660C0000 I 1 a
使用NOCASCADE选项，对事务2进行闪回，由于后续的事务3对事务2的依赖，故不能执行成功。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('06000700170E0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 4
SQL> select * from dept2;
DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
1 a
2 b
3 c
使用NONCONFLICT_ONLY选项，对事务2进行闪回。仅回退了事务2里的第2条insert，事务2里的第1条insert和事务3保持原状。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('0A001F00930C0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nonconflict_only);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from dept2;
DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
1 a
2 b
SQL> select * from emp2;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
200 2
SQL> rollback;
Rollback complete.
SQL> select * from dept2;
DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
1 a
2 b
3 c
SQL> select * from emp2;
EMPLOYEE_ID DEPARTMENT_ID
----------- -------------
200 2
使用NOCASCADE_FORCE选项，对事务2进行闪回。NOCASCADE_FORCE仅能回退存在"Write-after-write dependency"依赖关系的事务，如果依赖关系来自于primary key或者foreign key则无法实现回退。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('0A001F00930C0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktftbProcessKGL_4], [7901], [7842], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 4
但是出现上面这个错误，网上没有这个错误的资料，MOS里面也没有搜索到（也有可能是我的搜索方法不对），有知道的朋友麻烦分享一下），只能重新构造表和数据。
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from dept2 versions between scn minvalue and maxvalue t;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------------------- ---------------- - ------------- --------------------
31-JAN-16 09.55.36 PM 02000B00500D0000 I 3 c
31-JAN-16 09.55.36 PM 02000B00500D0000 I 2 b
31-JAN-16 09.55.30 PM 01000B00780C0000 I 1 a
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('02000B00500D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-55511: Flashback Transaction experienced error in executing undo SQL
ORA-02292: integrity constraint (ORA-02292: integrity constraint (SYS.SYS_C0011807) violated - child record found
.) violated - child record found
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 4
使用CASCADE选项，对事务2进行闪回。事务3依赖于事务2里的第一条insert，事务2、事务3全部回退。
SQL> declare
2 v_xid sys.xid_array:=sys.xid_array(hextoraw('02001B00510D0000'));
3 begin
4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from dept2;
DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
1 a
SQL> select * from emp2;
no rows selected
四种闪回选项在三种依赖关系下是否能够成功闪回的总结：
| NOCASCADE | NOCASCADE_FORCE | CASCADE | NONCONFLICT_ONLY | |
|---|---|---|---|---|
| Write-after-write dependency | 否 | 是 | 是 | 是 |
| Primary key dependency | 否 | 否 | 是 | 是 |
| Foreign key dependency | 否 | 否 | 是 | 是 |
选择闪回选项后，会在DBA_FLASHBACK_TXN_STATE和DBA_FLASHBACK_TXN_REPORT视图中生成相关性报告。
- 检查已回退的所有事务处理的相关性报告。
- 提交更改。
- 回滚更改。
DBA_FLASHBACK_TXN_STATE视图包含事务处理的当前状态：该事务处理在系统中处于活动状态还是已被有效回退。
DBA_FLASHBACK_TXN_REPORT视图可提供关于数据库中已提交的所有回退事务处理的详细信息。此视图中的每一行都与一个回退事务处理相关联。
Total Recall
Oracle数据库11g的全面回忆功能（也称闪回数据归档）提供了一种用于跟踪生产数据库改变的机制，安全、高效、使用方便且对应用透明。
使用全面回忆技术，可以自动跟踪和存储数据到启用了闪回数据归档功能的表中。这确保了闪回查询可以获得SQL级访问数据库对象的版本而不会产生快照过旧的错误。
闪回数据归档可跟踪和存储所有事务改变到到一个“跟踪”表，而不需要再在应用中创建这个功能了。使用全面回忆可以用于审核报告，数据分析和决策支持系统。
应用举例：
- 审核支持：查找去年的重复保险索赔。
- 合规性支持：监测股票交易。
- 信息生命周期管理（ILM）：获得病人的病史。
- 保留策略强制要求：自动删除五年以上的记录。
- 历史报告：检索客户的信用和支付历史。
- 错误恢复：恢复误删除或误更新的记录。
一个闪回数据归档包含一个或多个表空间。可以有多个闪回数据归档。基于保留时间的要求，创建不同的闪回数据归档。例如，创建一个所有记录必须保存两年，创建另一个所有记录必须保存五年。数据库在保留时间到期后一天将自动清除所有的历史信息。
创建步骤如下：
（1）为闪回数据归档创建一个表空间。大小取决于基表和预期的DML和DDL活动。
（2）创建一个指定保留时间的闪回数据归档。此项任务需要FLASHBACK ARCHIVE ADMINISTER系统权限。如果要求不同的保留时间，则必须创建不同的归档。
（3）为表启用闪回归档。此项任务需要FLASHBACK ARCHIVE对象权限。虽然为表启用了闪回归档，但一些DDL语句不允许在该表执行。默认情况下，表没有启用闪回存档。
例子：为表创建闪回数据归档
SQL> create tablespace fda_tbs1;
Tablespace created.
SQL> create flashback archive fda1 tablespace fda_tbs1 quota 10M retention 1 year;
Flashback archive created.
SQL> alter table hr.employees flashback archive fda1;
Table altered.
闪回数据归档使用FBDA后台进程在一个自动调整的时间间隔从UNDO（和缓冲区缓存）捕获历史数据。时间间隔默认为五分钟。被更新的行整行都会被存储，不论该行有多少字段被更新。
- 历史数据使用OLTP表压缩进行压缩，不使用混合列压缩。注意：如果基表使用混合列压缩，则该表不能启用闪回数据归档。
- 每一个闪回归档分区至少1天和1MB数据。
- FBDA进程可以调用多达10个闪回归档子进程。
- 如果闪回归档进程和子进程太忙，会严重影响用户的响应时间。
使用CREATE FLASHBACK ARCHIVE语句创建闪回数据归档。
- 可以指定默认闪回数据归档。
- 需要提供闪回数据归档的名称。
- 需要提供闪回数据归档的第一个表空间名称。
- 可以指定闪回数据归档的最大使用空间，默认是无限制。除非第一个表空间的空间配额是无限的，否则必须指定此值，不然会出现ORA-55621错误。
- 需要指定保留时间（为表的闪回数据归档数据指定存储的天数）。
为表启用闪回数据归档，可以在CREATE TABLE或者ALTER TABLE语句指定FLASHBACK ARCHIVE子句。在FLASHBACK ARCHIVE子句，指定表的历史数据存储的位置。默认使用系统默认的闪回数据归档。在ALTER TABLE语句指定NO FLASHBACK ARCHIVE为表禁用闪回数据归档。
例子：创建一个默认的闪回数据归档，名字为fla1，使用10 GB的tbs1表空间，保留五年
-- create the Flashback Data Archive
CREATE FLASHBACK ARCHIVE DEFAULT fla1
TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR;
例子：指定默认的闪回数据归档
-- Specify the default Flashback Data Archive
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
例子：启动闪回数据归档。如果没有启用Automatic Undo Management，则在修改表时会出现ORA-55614错误
-- Enable Flashback Data Archive
ALTER TABLE inventory FLASHBACK ARCHIVE;
ALTER TABLE stock_data FLASHBACK ARCHIVE;
例子：检索2007年初的所有货物的库存
SELECT product_number, product_name, count FROM inventory
AS OF TIMESTAMP TO_TIMESTAMP ('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
例子：为闪回数据归档fla1增加表空间tbs3，配额为5GB
ALTER FLASHBACK ARCHIVE fla1
ADD TABLESPACE tbs3 QUOTA 5G;
例子：修改闪回数据归档fla1的保留时间到2年
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
例子：清除闪回数据归档fla1的所有超过1天的历史数据。通常情况下，会自动清除过期的数据，也可以手动清除
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day);
例子：删除闪回数据归档fla1及其历史数据，但不删除表空间
DROP FLASHBACK ARCHIVE fla1;
当对表启用了闪回数据归档，一些DDL语句会产生错误ORA-55610，例如：
- ALTER TABLE语句包含了UPGRADE TABLE子句。
- ALTER TABLE语句移动或交换分区或子分区的操作。
- DROP TABLE语句。
闪回数据归档使用指导：
- 可以用于闪回查询、闪回版本查询或闪回事务查询。
- 所有的闪回处理使用当前会话设置，如国家语言和字符集，而不是查询时生效的设置。
- 使用SCN查询精确时间数据。如果使用时间戳，实际查询时间可能比你指定的时间提前3秒。Oracle数据库内部使用SCN并每3秒映射SCN到时间戳。
- 使用DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER函数获取SCN。
- 计算或检索过去的时间，使用函数的返回值作为时间戳或SCN的参数。例如，对SYSTIMESTAMP加上或减去一个INTERVAL。
- 为了确保数据库的一致性，在查询过去的数据之前，总是执行COMMIT或ROLLBACK操作。
- 不能查询动态性能视图以前的数据。但可以查询静态数据字典视图的历史数据，比如*_tables。
可以使用数据字典视图查看跟踪表和闪回数据归档的元数据。访问USER_FLASHBACK_*，必须有表的所有者的权限。查看DBA_FLASHBACK_*视图，需要SYSDBA权限。
| View Name (DBA/USER) | Description |
|---|---|
| *_FLASHBACK_ARCHIVE | Displays information about Flashback Data Archives |
| *_FLASHBACK_ARCHIVE_TS | Displays tablespaces of Flashback Data Archives |
| *_FLASHBACK_ARCHIVE_TABLES | Displays information about tables that are enabled for flashback archiving |
例子 ：查询闪回数据归档信息
SQL> select f.owner_name,f.flashback_archive_name,f.retention_in_days from dba_flashback_archive f;
OWNER_NAME FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
------------------------------ ------------------------- -----------------
SYS FDA1 365
SQL> select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
------------------------- ------------------ --------------- ------------
FDA1 1 FDA_TBS1 10
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- ---------- ------------------------- ------------------ -------
EMP HR FDA1 SYS_FBA_HIST_89969 ENABLED
Flashback Drop
使用闪回删除命令，可以恢复DROP TABLE语句删除的表，无需使用基于时间点的恢复。
注意：当前用户的RECYCLEBIN初始化参数控制闪回删除是否启用。如果设置该参数为OFF，则删除的表不会进入到回收站。如果设置参数为ON，则删除的表进入回收站并可回收。默认情况下，RECYCLEBIN设置为ON。
例子：查看RECYCLEBIN初始化参数
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL> alter system set recyclebin=on scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string ON
如果没有启用回收站，当删除一个表，表及其依赖的对象相关联的空间会被立即回收（即可以用于其他对象）。
如果启用了回收站，当删除一个表，表及其依赖的对象相关联的空间不会被立即回收，即使它出现在DBA_FREE_SPACE视图。删除的对象在回收站被引用且仍旧属于其所有者。除非空间不足，回收站对象使用的空间不会自动回收。这可以使用户在尽可能长的时间内恢复回收站对象。
当删除的表“移动”到回收站，系统对该表及其相关的对象和约束进行重命名。命名约定如下：
BIN$unique_id$version
unique_id是一个26个字符的全局唯一标识符，version是一个由数据库指定的版本号。
回收站本身就是一个数据字典表，维护被删除对象的原始名称和系统生成名称之间的关系。可以使用DBA_RECYCLEBIN查询回收站。
例子：查看回收站
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KCkSs4IS/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:11:28:12
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:04:21
EMP BIN$J9iOOkW8dhzgU4vmqMCn2w==$0 TABLE 2015-12-27:10:00:41
使用FLASHBACK TABLE ... TO BEFORE DROP命令从回收站恢复表及其依赖对象，可以在该命令中使用该表的原始名称，或者使用在该对象被删除时分配给该对象的系统名称。
如果使用原始名称，且在回收站中该名称对应多个对象版本，则恢复最后删除的那一个（LILO：last in，first out）。如果想恢复其他版本，可以指定系统生成的表名进行恢复，或者继续使用FLASHBACK TABLE ... TO BEFORE DROP，直到恢复到想要的版本。
如果在表删除后，在同一个模式中又创建了一个新的相同名字的表，闪回删除时如果不指定RENAME TO子句进行重命名，则会报错。
注意：闪回删除表后，表的索引、触发器和约束仍然使用回收站中的名称。因此，建议在闪回删除前查询回收站和DBA_CONSTRAINTS视图，重命名索引、触发器和约束。
例子：闪回删除
SQL> flashback table emp to before drop rename to emp_rec;
Flashback complete.
只要回收站中对象的空间没有被回收，就可以使用闪回删除进行恢复。以下为回收站对象回收策略：
- 使用PURGE命令手动清理。
- 空间不足时自动清理：当对象位于回收站，则该对象占用的空间就可以进行自动回收，DBA_FREE_SPACE视图包含回收站中对象占用空间。表空间的空闲空间按以下顺序进行使用：
- 回收站对象之外的空闲空间。
- 回收站对象占用的空闲空间。在这种情况下，回收站使用先进先出（FIFO）算法自动清除回收站对象。
- 如果表空间自动扩展，自动分配空闲空间。
使用PURGE命令从回收站永久删除对象。当一个对象被从回收站删除，则对象及其依赖对象从数据库中永久删除。因此，从回收站中PURGE的对象不能再使用闪回删除进行恢复。
PURGE用法如下：
- PURGE TABLE：删除表
- PURGE INDEX：删除索引
- PURGE TABLESPACE：删除表空间中的所有对象
- PURGE RECYCLEBIN：删除回收站中属于当前用户的对象
- PURGE DBA_RECYCLEBIN：删除回收站中所有对象
注意：对于PURGE TABLE和PURGE INDEX命令，如果使用对象的原始名称且回收站中有多个对象对应这个名称，则删除最先进入回收站的对象（FIFO）。
例子：删除回收站的对象
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:04:21
EMP BIN$J9iOOkW8dhzgU4vmqMCn2w==$0 TABLE 2015-12-27:10:00:41
SQL> purge table emp;
Table purged.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:04:21
可以使用DROP TABLE PURGE命令彻底删除表及其依赖的对象，这些对象不会进入回收站。
当使用DROP TABLESPACE ... INCLUDING CONTENTS命令删除表空间时，表空间中的对象不会进入回收站，而且回收站中属于该表空间的对象也会被清除。当不使用INCLUDING CONTENTS子句删除表空间时，表空间必须为空才能执行成功，即使回收站中有属于该表空间的对象。在这种情况下，这些对象也会被清除。
当使用DROP USER ... CASCADE命令时，用户和用户拥有的所有对象都会被永久删除。回收站中属于该用户的对象也会被删除。
例子：彻底删除表
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:04:21
SQL> drop table emp1 purge;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:04:21
查询回收站可以：
- 通过USER_RECYCLEBIN视图或者RECYCLEBIN同义词查看删除的对象。
- 通过DBA_RECYCLEBIN视图查看所有用户删除的对象。
- 使用SQL*Plus命令SHOW RECYCLEBIN显示可以“undropped”的对象。
例子：查看回收站
SQL> select owner,original_name,object_name,type,ts_name,droptime,related,space
2 from dba_recyclebin where can_undrop='YES';
OWNER ORIGINAL_NAME OBJECT_NAME TYPE TS_NAME DROPTIME RELATED SPACE
---------- -------------------- ------------------------------ ---------- ---------- ------------------- ---------- ----------
SYS EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE TS02 2015-12-31:10:53:34 89456 8
HR EXPORT000067 BIN$KBi1njRdZtXgU4vmqMDxTA==$0 TABLE USERS 2015-12-30:14:33:00 89302 8
SYS EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE TS02 2015-12-31:10:04:21 89455 8
HR EMP BIN$KpmWw9TVSWDgU4vmqMDVxA==$0 TABLE EXAMPLE 2016-01-31:11:08:56 89121 8
HR EMP BIN$KpoTVpYRVQDgU4vmqMDVTA==$0 TABLE USERS 2016-01-31:11:43:46 89954 8
HR EMP BIN$Kp7F8DcYaeLgU4vmqMAJuA==$0 TABLE USERS 2016-01-31:17:20:02 89966 8
HR EMP BIN$Kp7F8DcdaeLgU4vmqMAJuA==$0 TABLE USERS 2016-01-31:17:21:52 89968 8
7 rows selected.
SQL> select original_name,object_name,ts_name,droptime
2 from user_recyclebin where can_undrop='YES';
ORIGINAL_NAME OBJECT_NAME TS_NAME DROPTIME
-------------------- ------------------------------ ---------- -------------------
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TS02 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TS02 2015-12-31:10:04:21
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KCkSs4IN/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:53:34
EMP BIN$KCkSs4II/UXgU4vmqMCqCA==$0 TABLE 2015-12-31:10:04:21
Flashback Database
使用闪回数据库，可以撤销某个时间点之后的所有更改，快速将数据库回到这个时间点。由于不需要还原备份，所有此操作很快。可以使用此功能来解决数据逻辑错误问题。
当使用闪回数据库，Oracle数据库使用过去的块映像来更改数据库。在正常的数据库操作时，数据库记录这些块映像到闪回日志。闪回日志顺序写入且不归档。Oracle数据库在快速恢复区中自动创建、删除和调整闪回日志大小。
闪回数据库需要的时间取决于闪回的时间点以及该时间点后数据库的活动量。还原和恢复数据库可能需要更长的时间。闪回日志中的前映像只能将数据库还原到过去的某一个时间点，正向恢复用于将数据库恢复到过去的某一时间一致的状态。数据库将数据文件返回到先前的某个时间点，但不影响辅助文件，比如初始化参数文件。闪回数据库也可以用于Data Guard和Recovery Advisor以及同步复制数据库。
当启用闪回数据库，RVWR（Flashback Write）后台进程启动。这个后台程序从闪回缓冲区按顺序将闪回数据库数据写到闪回日志，闪回日志循环使用。当发出FLASHBACK DATABASE命令，使用闪回日志将数据块还原到之前的映像，使用重做日志前滚到指定的时间点。
启用闪回数据库的开销取决于读/写的混合数据库负载。由于查询不需要记录任何闪回数据，写密集型的负载越大，打开闪回数据库的开销越高。
配置闪回数据库
配置闪回数据库步骤如下：
- 配置快速恢复区。
- 使用初始化参数
DB_FLASHBACK_RETENTION_TARGET设置保留时间目标，指定可以闪回数据库的时间上限，单位为分钟。这个参数仅仅是一个目标值，而不保证一定可以达到，闪回时间间隔取决于快速恢复区保留了多少闪回数据。 - 使用以下命令启用闪回数据库：
ALTER DATABASE FLASHBACK ON；在启用闪回数据库之前，数据库必须配置为归档模式。
使用以下语句查询是否启用了闪回数据库：
SELECT flashback_on FROM v$database;
使用ALTER DATABASE FLASHBACK OFF命令禁用闪回数据库，同时自动删除闪回日志。
注意：数据库只能在mounted in exclusive模式下启用闪回数据库。
例子：配置闪回数据库
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> alter system set db_flashback_retention_target=2880 scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
执行闪回数据库
可以使用RMAN的FLASHBACK DATABASE命令执行闪回数据库操作。可以使用SEQUENCE和THREAD指定一个重做日志序列号和线程作为一个下限。
也可以使用SQL的FLASHBACK DATABASE命令将数据库恢复到过去的某个时间或SCN。如果使用SCN子句，必须提供一个SCN号。如果使用TO TIMESTAMP子句，必须提供一个时间戳。还可以指定一个还原点名称。
可以使用V$SESSION_LONGOPS视图监控闪回数据库处理过程。
注意：数据库必须在mounted in exclusive模式运行FLASHBACK DATABASE命令，完成后可以以只读模式打开数据库查看改变，闪回完成后进入读写模式需要使用RESETLOGS选项打开数据库。
例子：闪回数据库
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-02-01 21:57:52
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> delete from emp;
107 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
RMAN> flashback database to time="to_date('2016-02-01 21:57:52','yyyy-mm-dd hh24:mi:ss')";
Starting flashback at 01-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:26
Finished flashback at 01-FEB-16
SQL> alter database open read only;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
闪回数据库操作完成后，可以以只读模式打开数据库验证是否使用了正确的目标时间或SCN。如果不是，可以再次闪回数据库，或执行一个恢复操作前滚数据库。
以下情形不能进行闪回数据库：
- 控制文件被恢复或者被重建
- 表空间被删除
- 数据文件被收缩
可以在FLASHBACK DATABASE命令中使用TO BEFORE RESETLOGS子句以便闪回到上一个RESETLOGS操作之前。
注意：闪回保留时间不是一个绝对保证时间，如果快速恢复区空间不够，将会自动删除闪回日志。
例子：闪回数据库后撤销闪回操作
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> create restore point fb1;
Restore point created.
SQL> delete from emp;
107 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> flashback database to restore point fb1;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
0
例子：数据文件收缩后闪回失败
SQL> alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' resize 80M;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> flashback database to restore point fb1;
flashback database to restore point fb1
*
ERROR at line 1:
ORA-38766: cannot flashback data file 17; file resized smaller
ORA-01110: data file 17: '+DATA/stone/datafile/fda_tbs1.281.902663435'
SQL> alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' offline;
Database altered.
SQL> flashback database to restore point fb1;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from emp;
COUNT(*)
----------
107
再次online数据文件报错：
SQL> alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' online;
alter database datafile '+DATA/stone/datafile/fda_tbs1.281.902663435' online
*
ERROR at line 1:
ORA-01190: control file or data file 17 is from before the last RESETLOGS
ORA-01110: data file 17: '+DATA/stone/datafile/fda_tbs1.281.902663435'
例子：闪回到上一次resetlogs之前
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 889389056 bytes
Fixed Size 2258360 bytes
Variable Size 662702664 bytes
Database Buffers 218103808 bytes
Redo Buffers 6324224 bytes
Database mounted.
SQL> flashback database to before resetlogs;
Flashback complete.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
监控闪回数据库
使用V$FLASHBACK_DATABASE_LOG视图监视闪回数据库保留时间目标。
- ESTIMATED_FLASHBACK_SIZE字段估计满足当前闪回保留时间所需要的快速恢复区磁盘空间。
- FLASHBACK_SIZE字段为闪回数据的当前大小，以字节为单位。
- OLDEST_FLASHBACK_SCN和OLDEST_FLASHBACK_TIME字段显示数据库可以闪回到的最小的SCN及时间的大概值，V$DATABASE中的CURRENT_SCN字段为当前数据库的SCN。
例子：通过V$FLASHBACK_DATABASE_LOG查询闪回日志的信息
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
4350231 2016-02-02 14:38:55 1440 104857600 331431936
使用V$FLASHBACK_DATABASE_STAT视图监控将闪回数据写入到闪回日志的开销。此视图包含24小时的信息，每行表示1小时的时间间隔。可以使用此视图确定闪回数据生成速率的变化。
- FLASHBACK_DATA字段表示闪回数据写入字节数。
- REDO_DATA字段表示重做数据写入字节数。
- DB_DATA字段表示数据块读写字节数。
例子：使用V$FLASHBACK_DATABASE_STAT查询闪回数据的信息
SQL> select * from v$flashback_database_stat;
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2016-02-02 14:51:45 2016-02-02 15:47:18 12812288 19611648 7825408 0
使用V$RECOVERY_FILE_DEST视图查看快速恢复区的信息。
- NAME：快速恢复区名字。
- SPACE_LIMIT：参数DB_RECOVERY_FILE_DEST_SIZE指定的磁盘可用限制值。
- SPACE_USED：使用了多少字节。
- SPACE_RECLAIMABLE：通过空间管理算法删除过期、冗余以及低优先级的文件而可以回收的空间。
- NUMBER_OF_FILES：文件数量。
例子：使用V$RECOVERY_FILE_DEST视图查看快速恢复区的信息
SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------- ----------- ---------- ----------------- ---------------
/u01/app/oracle/fast_recovery_area 8589934592 3987886592 1587984384 17
Auditing
审计的类型：
- 强制性审计：不管其他的审计选项或参数，某些动作必须被审计。强制审计日志的原因是数据库需要记录一些数据库活动，比如授权用户的连接。
- 标准数据库审计：使用初始化参数audit_trail，在系统级别启用。选择要审核的对象和权限，并通过AUDIT命令设置审核属性。
- 基于值的审计：标准数据库审计的扩展，不仅捕获发生的审计事件，还包括插入，更新或删除的实际值。通过数据库触发器实现了基于值的审计。
- 细粒度审计（FGA）：标准数据库审计的扩展，获取实际的SQL语句。
- SYSDBA（和SYSOPER）审计：使用操作系统审计记录，在DBA和审计员或者监控DBA活动的管理员之间分离审计职责。
例子：查看初始化参数audit_trail
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
标准数据库审计
使用数据库审计，必须先设置静态参数AUDIT_TRAIL参数指定审计记录的存储位置同时启用数据库审计。启用数据库审计并指定审计选项后（登录事件、系统运行和对象权限，或SQL语句的使用），数据库开始收集审计信息。
如果AUDIT_TRAIL设置为OS，审计记录存储在操作系统。在Windows环境就是event log。在Unix或Linux环境下，审计记录存储在AUDIT_FILE_DEST参数指定目录。
如果AUDIT_TRAIL设置为DB或DB，EXTENDED，可以在DBA_AUDIT_TRAIL视图（基于aud$的视图，其他如dba_audit_session,dba_audit_object,dba_audit_statement都只是dba_audit_trail的一个子集）查看审核记录。
如果AUDIT_TRAIL设置为XML或XML，EXTENDED，审计记录写入AUDIT_FILE_DEST参数指定目录下的XML文件，使用V$XML_AUDIT_TRAIL视图查看该目录下所有的XML文件。
维护审计记录是一项重要的管理任务。根据审计选项的重点，审计记录可能会非常迅速地增长。如果没有正确的维护，审计记录可以创建非常多记录从而影响系统的性能。
例子：查看初始化参数audit_file_dest
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/test/adump
例子：通过DBA_AUDIT_TRAIL视图查看对HR用户从昨天开始的审计记录
SQL> select username,action_name,priv_used,timestamp from dba_audit_trail where username='HR' and timestamp>sysdate-1;
USERNAME ACTION_NAME PRIV_USED TIMESTAMP
---------- ---------------------------- ---------------------------------------- ------------
HR ALTER SYSTEM 22-DEC-15
HR LOGON CREATE SESSION 22-DEC-15
HR LOGON CREATE SESSION 22-DEC-15
HR LOGON CREATE SESSION 22-DEC-15
HR LOGON CREATE SESSION 22-DEC-15
HR LOGON CREATE SESSION 22-DEC-15
HR LOGON CREATE SESSION 22-DEC-15
HR LOGOFF BY CLEANUP 22-DEC-15
HR LOGOFF 22-DEC-15
HR LOGOFF 22-DEC-15
HR LOGOFF 22-DEC-15
例子：查看各种审计的记录数量
SQL> select action_name,count(*) from dba_audit_trail group by action_name;
ACTION_NAME COUNT(*)
---------------------------- ----------
LOGOFF BY CLEANUP 312
SYSTEM REVOKE 1
LOGON 9149
LOGOFF 8454
DROP DIRECTORY 1
GRANT ROLE 1
SESSION REC 1
CREATE DIRECTORY 13
SET ROLE 18
SYSTEM GRANT 4
ALTER SYSTEM 1
CREATE DATABASE LINK 1
使用ALTER SYSTEM命令设置AUDIT_TRAIL参数。因为这是一个静态的参数，所以修改后必须重新启动数据库才能生效。
如果使用DBCA创建数据库，默认AUDIT_TRAIL参数设置为DB。当AUDIT_TRAIL设置DB，默认行为是将审计信息记录到AUD$表。大多数情况下不会对数据库性能有很大的影响。
Oracle推荐使用操作系统审计线索文件。如果手动创建的数据库（使用CREATE DATABASE命令），AUDIT_TRAIL参数默认设置为NONE。
例子：修改初始化参数audit_trail为db,extended
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL> alter system set audit_trail=db,extended scope=spfile;
System altered.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
DBA_COMMON_AUDIT_TRAIL视图包含了标准和细粒度的审计日志记录。
例子：通过DBA_COMMON_AUDIT_TRAIL视图查看对HR用户从昨天开始的审计记录
SQL> select audit_type,db_user,statement_type,priv_used,extended_timestamp from DBA_COMMON_AUDIT_TRAIL
2 where db_user='HR' and extended_timestamp>sysdate-1;
AUDIT_TYPE DB_US STATEMENT_TYPE PRIV_USED EXTENDED_TIMESTAMP
----------------- ----- --------------------- -------------------- ----------------------------------------
Standard Audit HR ALTER SYSTEM 22-DEC-15 04.57.24.478172 PM +08:00
Standard Audit HR LOGON CREATE SESSION 22-DEC-15 04.24.15.821173 PM +08:00
Standard Audit HR LOGON CREATE SESSION 22-DEC-15 04.25.19.863358 PM +08:00
Standard Audit HR LOGON CREATE SESSION 22-DEC-15 04.26.02.415283 PM +08:00
Standard Audit HR LOGON CREATE SESSION 22-DEC-15 04.26.21.517455 PM +08:00
Standard Audit HR LOGON CREATE SESSION 22-DEC-15 04.26.55.641184 PM +08:00
Standard Audit HR LOGOFF BY CLEANUP 22-DEC-15 11.10.11.996552 PM +08:00
Standard Audit HR LOGOFF 22-DEC-15 04.24.15.787890 PM +08:00
Standard Audit HR LOGOFF 22-DEC-15 04.25.12.309045 PM +08:00
Standard Audit HR LOGOFF 22-DEC-15 04.25.50.705529 PM +08:00
Standard Audit HR LOGOFF 22-DEC-15 04.26.17.797885 PM +08:00
指定审核选项：
- SQL语句审计：可以审计任何对表的DDL语句，包括创建表，删除表，截断表等等。还可以指定执行用户以及执行语句成功或者失败。
- 系统权限审计：用于审计系统权限（如DROP ANY TABLE）。可以指定执行用户以及执行语句成功或者失败。默认情况下使用BY ACESS。每次审核系统权限就会生成一条审核记录。可以通过BY SESSION对记录进行分组，一个会话中的相同语句类型对应一条记录，减少对系统性能和存储的影响。
- 对象权限审计：可用于审核表、视图、程序、序列、目录和用户定义的数据类型的操作。可以用于审核执行语句成功或失败，并按会话或访问分组。与系统权限审计不同，默认按会话分组。如果要为每个操作生成一个单独的审计线索记录，则必须显式指定BY ACESS。
例子：增加HR用户访问表失败的SQL语句的审计
SQL> audit table by hr whenever not successful;
Audit succeeded.
例子：增加对HR用户SELECT ANY TABLE系统权限的审计
SQL> audit select any table by hr by session;
Audit succeeded.
例子：增加对HR模式下EMPLOYEES表对象的更新和删除记录的审计
SQL> audit update,delete on hr.emp by access;
Audit succeeded.
SQL> select username,action_name,priv_used,timestamp from dba_audit_trail where username='HR';
USERNAME ACTION_NAME PRIV_USED TIMESTAMP
-------------- -------------- -------------------- ---------------------
HR LOGOFF 23-dec-15 17:11:56
HR LOGON CREATE SESSION 23-dec-15 17:11:59
HR DELETE 23-dec-15 17:12:39
在Oracle 11g启用审计后，默认审计对安全重要的权限和语句，对以下权限和语句访问成功或者失败进行审计。
Privileges Audited by Default
ALTER ANY PROCEDURE
ALTER ANY TABLE
ALTER DATABASE
ALTER PROFILE
ALTER SYSTEM
ALTER USER
AUDIT SYSTEM
CREATE ANY JOB
CREATE ANY LIBRARY
CREATE ANY PROCEDURE
CREATE ANY TABLE
CREATE EXTERNAL JOB
CREATE PUBLIC DATABASE LINK
CREATE SESSION
CREATE USER
GRANT ANY OBJECT PRIVILEGE
GRANT ANY PRIVILEGE
GRANT ANY ROLE
DROP ANY PROCEDURE
DROP ANY TABLE
DROP PROFILE
DROP USER
EXEMPT ACCESS POLICY
Statements Audited by Default
SYSTEM AUDIT BY ACCESS
ROLE BY ACCESS
例子：查看审计的权限
SQL> select PRIVILEGE,SUCCESS,FAILURE from DBA_PRIV_AUDIT_OPTS;
PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
23 rows selected.
例子：查看审计的对象
SQL> select owner,object_name,object_type from DBA_OBJ_AUDIT_OPTS;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
HR EMP TABLE
例子：查看审计的语句
SQL> select audit_option from dba_stmt_audit_opts
2 minus
3 select privilege from dba_priv_audit_opts;
AUDIT_OPTION
----------------------------------------
DATABASE LINK
DIRECTORY
PROFILE
PUBLIC SYNONYM
ROLE
SYSTEM AUDIT
SYSTEM GRANT
7 rows selected.
基于值的审计
数据库审计记录发生在被审计对象中的插入、更新和删除，但不捕获更改的实际值。基于值的审计利用数据库触发器来捕获已更改的值。
当用户插入、更新或删除有相关触发器表的数据的时候，触发器拷贝审计信息到指定表。相比标准的数据库审计，基于值的审计更加影响性能，因为每一次插入，更新或删除操作都会执行触发器。只有在标准数据库审计所捕获的信息不足的情况下，才需要使用基于值的审计。
审计触发器是基于值审计的关键，可以用一个简单的PL/SQL触发器捕捉审计信息。
典型审计触发器的例子：
CREATE OR REPLACE TRIGGER system.hrsalary_audit
AFTER UPDATE OF salary
ON hr.employees
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :old.salary != :new.salary THEN
INSERT INTO system.audit_employees
VALUES (sys_context('userenv','os_user'), sysdate,
sys_context('userenv','ip_address'),
:new.employee_id ||
' salary changed from '||:old.salary||
' to '||:new.salary);
END IF;
END;
/
这个触发器捕捉hr.employees表工资列的变化。当更新行时，触发器将检查工资字段。如果旧的值不等于新的值，则将审计记录插入到audit_employees表（在SYSTEM模式单独创建的一个表）。审计记录包括用户名、IP地址、变更的主键和变更的实际工资值。
数据库触发器还可用于在标准数据库审计未收集足够数据的情况下捕获有关用户连接的信息。通过登录触发器，管理员可以捕捉到连接到数据库的用户数据。包括：
- IP地址
- 用来连接到实例的程序的前48个字符
- 用于连接实例的终端名称
细粒度审计
细粒度审计（FGA）捕获实际的查询或操纵数据的SQL语句。
FGA可以审计一个表或视图的单个列，甚至可以指定捕获条件。FGA支持多个相关列，默认情况下，任何一个列出现在SQL语句中都会被审计。使用DBMS_FGA.ALL_COLUMNS和DBMS_FGA.ANY_COLUMNS指定审计基于的列。
使用DBMS_FGA PL/SQL包创建表或视图的审计策略。如果查询返回的任何一行与被审计列和指定的审计条件相匹配，审计事件会创建一个审计记录并存储在审计跟踪中。FGA自动审计在语句级别，一个返回数千行的查询语句只产生一个审计记录。
使用DBMS_FGA.ADD_POLICY存储过程创建一个细粒度审计策略：
dbms_fga.add_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'audit_emps_salary',
audit_condition=> 'department_id=10',
audit_column => 'SALARY,COMMISSION_PCT',
handler_schema => 'secure',
handler_module => 'log_emps_salary',
enable => TRUE,
statement_types => 'SELECT,UPDATE');
参数：
- 策略名称（policy_name）：policy_name = >‘audit_emps_salary'
- 审计条件（audit_condition）：触发审计策略的谓词条件，audit_condition =>'department_id = 10'
- 审计列（audit_column）：审计的字段，可选，audit_column = >'salary,commision_pct'
- 对象（object_schema,object_name）：指定审计的对象，包括模式名称和对象名称，object_schema = >'hr', object_name = >'employees'
- 处理程序（handler）：一个可选的PL/SQL事件处理程序，指定必须在审计中采取的动作。例如，事件处理程序可以向管理员发送警告页面。如果未定义，则将审计记录插入到审计跟踪中。如果定义了一个审计事件处理程序，则将该审计记录插入到审计跟踪，并执行审计事件处理程序。事件处理程序包括2个参数：
- 包含PL/SQL程序的模式
- PL/SQL程序的名称
- 上面例子中，handler_schema = >‘secure’， handler_module = >'log_emps_salary'
- 状态（status）：状态指示是否启用FGA政策。enable=>true
对于DELETE语句，在策略定义中指定相关列没有用，因为DELETE语句会涉及到表的所有列，因此，不论是否指定了被审计字段，DELETE语句总是会被审计。
FGA支持MERGE语句。如果满足条件，则相关的INSERT，UPDATE和DELETE语句都会被审计。
例子：创建一个FGA审计，进行操作后查看审计记录
SQL> SHOW USER;
USER is "SYS"
SQL> exec dbms_fga.add_policy(object_schema=>'HR', object_name=> 'EMP', policy_name=> 'check_emp_audit',statement_types => 'INSERT, UPDATE, DELETE, SELECT');
PL/SQL procedure successfully completed.
对emp表进行增删改查
SQL> conn hr/hr
Connected.
SQL> insert into hr.emp select * from hr.employees;
107 rows created.
SQL> select count(*) from hr.emp;
COUNT(*)
----------
107
SQL> update emp set salary=10000 where employee_id=100;
1 row updated.
SQL> delete from hr.emp;
107 rows deleted.
SQL> rollback;
Rollback complete.
查看审计结果
SQL> conn / as sysdba
Connected.
SQL> select db_user,sql_text from dba_fga_audit_trail;
DB_USER SQL_TEXT
------------------------------ -------------------------------------------------------
HR insert into hr.emp select * from hr.employees
HR select count(*) from hr.emp
HR update emp set salary=10000 where employee_id=100
HR delete from hr.emp
FGA使用指导：
- 如果要审计所有行，设置audit_conditions为null
- 如果要审计所有列，设置audit_column为null
- 策略名字必须唯一
- 被审计的表或者视图必须先要存在
- 如果审计条件语法错误，则访问审计对象的时候会返回ORA-28112错误
- 如果审计字段不在表中，则没有行会被审计
- 如果event handler不存在，不返回错误，还是会创建审计记录
SYSDBA审计
SYSDBA和SYSOPER权限的用户可以启动和关闭数据库。因为它们可以在数据库关闭时进行更改，所以这些权限的审计跟踪必须存储在数据库之外。Oracle数据库自动捕获用户SYSDBA和SYSOPER的登录事件，可以跟踪授权或未授权的SYSDBA和SYSOPER行为。
数据库总是捕获授权用户的登录事件。如果启用DBA审计还会捕获其他操作。通过设置初始化参数AUDIT_SYS_OPERATIONS=TURE（默认值为FALSE），启用对SYSDBA和SYSOPER用户的审计。
如果审计SYS的操作，AUDIT_FILE_DEST初始化参数控制审计记录的存储位置。在Windows平台上，审计记录默认为事件日志。在Unix和Linux平台，审计记录存储在AUDIT_FILE_DEST位置。
例子：查看初始化参数AUDIT_SYS_OPERATIONS
SQL> show parameter audit_sys_operations
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
维护审计记录
查看审计记录并从数据库或操作系统中删除旧记录。审计记录可能会用完存储。
标准审计的审计记录存储在AUD$表。FGA的审计记录是FGA_LOG$表。这些表默认情况下在SYSTEM表空间创建。可以使用数据泵导出导入工具把这些表移动到另一个表空间。
注意：不支持将审计表移出SYSTEM表空间。
最佳实践：使用基于时间戳的导出，然后基于相同时间戳删除审计记录。
例子：查看AUD$表的记录以及大小
SQL> select userid,count(1) from aud$ group by userid;
USERID COUNT(1)
------------------------------ ----------
OPS$TEST 4
OPS$ORACLE 1
OPS$SHILEI 3
HR 133
USER001 23
TS01 2
USER1 1
OE 26
SH 4
DBSNMP 16199
SYSMAN 1531
STONE 1
BI 2
SYS 5
14 rows selected.
SQL> select owner,segment_name,bytes/1024/1024 mb from dba_segments where segment_name='AUD$';
OWNER SEGMENT_NA MB
---------- ---------- ----------
SYS AUD$ 4
可以取消对dbsnmp的审计
SQL> noaudit session by dbsnmp;
Noaudit succeeded.
Moving Data
SQL*Loader
使用SQL*Loader加载外部文件到数据库表。
加载数据需要：
- Input data files（输入数据文件）：源数据所在文件
- Control file（控制文件）：文本文件，指定输入数据文件的位置以及解析方式等，分三部分：
- 会话级别的信息，包括全局选项，数据位置等
- 表信息，表的名字和字段
- 可选部分，包含输入数据
加载数据会产生：
- Log file（日志文件）：存储日志信息
- Bad file（问题记录文件）：存储包含错误的记录（格式无效或者不满足约束）
- Discard file（丢弃文件）：存放违反在控制文件里定义的过滤条件的纪录
SQL*Loader使用控制文件：
- 指定其他文件位置
- 指定数据格式
- 配置：内存管理，记录的拒绝规则
- 如何操作加载的数据
例子：控制文件
1 -- This is a sample control file
2LOAD DATA
3INFILE ’SAMPLE.DAT’
4BADFILE ’sample.bad’
5DISCARDFILE ’sample.dsc’
6APPEND
7INTO TABLE emp
8WHEN (57) = ’.’
9TRAILING NULLCOLS
10(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,’$99,999.99’)",
comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’
":comm * 100"
)
- 第一行为注释，可以位于任何地方，“--”开头。
- LOAD DATA表示这是一个新数据加载的开始，如果要继续之前的加载，可以使用CONTINUE LOAD DATA
- INFILE指定数据所在文件名称
- BADFILE指定问题记录保存文件
- DISCARDFILE指定丢弃记录保存文件
- APPEND用于加载数据到一个已经有数据的表，如果是空表，使用INSERT关键字
- INTO TABLE指定导入数据到哪个表
- WHEN语句指定每条记录需要满足的域值条件，WHEN (57) = '.'表示记录的第57个字符为'.'
- TRAILING NULLCOLS语句不存在的字段为NULL
- 剩下的是字段列表和格式
常规导入通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。
- 传统路径（conventional path）：SQLLDR 会利用SQL插入加载数据。
- 直接路径（direct path）：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块，而绕过整个SQL引擎和UNDO生成，同时还可能避开REDO生成。要在一个没有任何数据的库中充分加载数据，最快的方法就是采用并行直接路径加载。总是在表的最高水位之上插入数据，这种方式消除了用于搜索部分填充块的时间。
| Conventional Load | Direct Path Load |
|---|---|
| Uses COMMIT | Uses data saves (faster operation) |
| Always generates redo entries | Generates redo only under specific conditions |
| Enforces all constraints | Enforces only PRIMARY KEY, UNIQUE, and NOT NULL |
| Fires INSERT triggers | Does not fire INSERT triggers |
| Can load into clustered tables | Does not load into clusters |
| Allows other users to modify tables during load operation | Prevents other users from making changes to tables during load operation |
| Maintains index entries on each insert | Merges new index entries at the end of the load |
例子：使用SQL*Loader加载数据到数据库
[oracle@test ~]$ cat sqlload.CTL
LOAD DATA
APPEND
INTO TABLE HR.EMPLDR
(
FIRST_NAME POSITION(1:20) CHAR,
LAST_NAME POSITION(21:45) CHAR
)
[oracle@test ~]$ sqlldr control='/home/oracle/sqlload.CTL'
Username:hr
Password:
SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 30 17:41:10 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 10
Data Pump
日常工作中，主要使用Data Pump（数据泵）进行数据迁移，包括EXPDP（数据导出）和IMPDP（数据导入）这两个工具。
相比之前的exp和imp，Data Pump的好处有：
- 可以使用EXCLUDE，INCLUDE和CONTENT参数进行细粒度的对象和数据选择
- 可以使用VERSION参数为导出的文件指定数据库版本以便用于先前版本的数据库
- 可以使用PARALLEL参数指定并行度
- 可以使用ESTIMATE_ONLY参数估计导出数据到占用多少空间而不需要实际执行
- 可以使用Network模式，在分布式环境中通过DBLINK导入导出
- 可以使用REMAP，在导入的时候更改目标数据文件名称，模式名称和表空间名称
- 可以使用SAMPLE参数指定导出数据的百分比
- 可以使用COMPRESSION进行压缩，节约空间
- 可以对元数据，数据，字段进行加密，指定加密算法，安全类型等
- 使用非压缩的CLOB格式导出XML类型字段
- 可以使用Legacy模式支持exp和imp脚本
由于Data Pump是一个服务器端的程序，需要通过目录对象指定dump文件目录。Oracle提供了一个叫DATA_PUMP_DIR的目录对象用于导出。如果设置了ORACLE_BASE，则默认路径为<ORACLE_BASE>/admin/DB_UNIQUE_NAME/dpdump，如果没有设置ORACLE_BASE,设置了ORACLE_HOME，则默认路径为<ORACLE_HOME>/admin/DB_UNIQUE_NAME/dpdump。
例子：创建目录对象
SQL> create directory test as '/home/oracle';
Directory created.
SQL> grant read,write on directory test to hr;
Grant succeeded.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ --------------------------------------------------------------------------------
SYS TEST /home/oracle
SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS TEST1 /home/oracle
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/oracletest1/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
11 rows selected.
使用expdp导出数据库数据到操作系统的dump文件，再使用impdp导入dump文件到目标数据库。在服务器端进行，不在客户端进行。
通过使用Network模式，源数据库到目标数据库直接加载。避免了在文件系统上创建转储文件，可以最大限度地减少导出和导入操作的总消耗时间。
使用Master Table（MT）来维护导出操作，开始导出操作时创建该表，导出完成后写入到dump文件中，导入时先加载MT到数据库当前模式用于按顺序创建导入对象，导入完成后会删除MT。
例子：导出Table
[oracle@test ~]$ expdp hr/hr directory=test tables=hr.emp dumpfile=hr1.dmp logfile=hr1.log;
例子：导出整个Schema
[oracle@test ~]$ expdp hr/hr directory=test schemas=hr dumpfile=hr2.dmp logfile=hr2.log;
例子：导出整个Schema，排除指定的Table
[oracle@test ~]$ expdp hr/hr directory=test schemas=hr exclude=table:\"in \(\'EMP\',\'DEPT\'\)\" dumpfile=hr3.dmp logfile=hr3.log;
例子：导出整个Schema，排除指定的Table
[oracle@test ~]$ expdp hr/hr directory=test schemas=hr exclude=table:\"in \(select table_name from dba_tables where owner=\'HR\' AND \(table_name like \'TEST\%\' or table_name like \'XXX\%\'\)\)\" dumpfile=hr4.dmp logfile=hr4.log;
例子：导出多个Schema
[oracle@test ~]$ expdp system/123456 directory=test schemas=hr,scott dumpfile=hr5.dmp logfile=hr5.log;
例子：导出整个Schema的元数据，并排除DBLINK，STATISTICS，JOB
[oracle@test ~]$ expdp system/123456 directory=test schemas=hr content=metadata_only exclude=db_link,statistics,job dumpfile=hr6.dmp logfile=hr6.log;
如果指定的表数量太多导致命令行长度超过限制，则可以将参数写到parfile文件中，然后在命令行中指定parfile位置即可。
例子：导出数据，使用parfile指定参数
[oracle@test ~]$ vim hr7.par
userid=hr/hr
directory=test
dumpfile=hr7.dmp
logfile=hr7.log
tables='EMPLOYEES'
[oracle@test ~]$ expdp parfile=hr7.par
例子：导入数据，如果表存在则删除重建，排除DBLINK
[oracle@test ~]$ impdp system/123456 directory=test dumpfile=hr2.dmp logfile=hr8.log table_exists_action=replace exclude=db_link
如果目标库没有源库Schema对应的表空间，要么目标库创建同名的表空间，要么导入的时候使用REMAP_TABLESPACE进行映射。可以进行映射的有：
- REMAP_DATAFILE：转换数据文件名称，跨平台的时候可以使用
- REMAP_TABLESPACE：可以将对象导入到指定的表空间
- REMPA_SCHEMA：可以将对象导入到指定的模式
- REMAP_TABLE：导入时重命名表
- REMAP_DATA：导入时更改数据，一般用于重新生成主键
例子：导入数据，导入到不同的Schema和Tablespace，表空间需要先在数据库中创建
[oracle@test ~]$ impdp system/123456 directory=test dumpfile=hr2.dmp logfile=h9.log remap_schema=hr:human remap_tablespace=user:human table_exists_action=replace
Transportable Tablespaces
使用可传输表空间是在两个Oracle DB之间移动大量数据的最快方式。使用可传输表空间，可以将Oracle 数据文件（包含表数据、索引和几乎其它所有Oracle DB 对象）从一个数据库迁移到另一个数据库。
对于相同数据而言，使用可传输表空间迁移数据比执行导入/导出的速度要快得多。这是因为，包含所有实际数据的数据文件只是被复制到目标位置，只有表空间对象的元数据通过数据泵传送到新数据库。
为了能够将数据文件从一个平台传输到另一个平台，必须确保源系统和目标系统都在支持的平台上运行。
注意：可跨平台传输的表空间功能要求两个平台使用相同的字符集。
源数据库和目标数据库都需要先将其数据库COMPATIBLE初始化参数提高到10.0.0或更高，才能使用可跨平台传输的表空间功能。
要将表空间从一个平台传输到另一个平台（源到目标），必须将表空间集内的数据文件转换为目标数据库可以理解的格式。虽然使用Oracle DB时磁盘结构符合通用格式，但源平台和目标平台仍有可能使用不同的endian 格式（字节顺序）。转至不同的endian 平台时，必须使用RMAN 实用程序的CONVERT命令来转换字节顺序。可以在源平台或目标平台上执行此操作。对于endian 格式相同的平台，则不需要转换。
可以查询V$TRANSPORTABLE_PLATFORM来确定两个平台的endian 顺序是否相同。
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (AMD64) Little
例子：查看当前数据库的Endian格式
SQL> SELECT tp.endian_format
2 FROM v$transportable_platform tp, v$database d
3 WHERE tp.platform_name = d.platform_name;
ENDIAN_FORMAT
--------------
Little
在执行跨平台传输的准备工作期间，使用RMAN CONVERT命令，将表空间、数据文件或数据库转换为目标平台的格式。CONVERT不改变输入文件，会将转换的文件写入到指定的输出目标。
CONVERT TABLESPACE示例：
- 假设你在Linux 32 位平台上有一个ORCL数据库，你想将其传输到Solaris 64 位平台上。
- 作为TARGET 连接至源数据库（已装载或已打开的）。
- 转换时，表空间必须是只读的。
- 结果是/tmp/transport_to_solaris/目录中的一组转换的数据文件，其中的数据以适合Solaris 64 位平台的正确endian 顺序排列。
例子：转换Endian
RMAN> sql 'alter tablespace ts read only';
sql statement: alter tablespace ts read only
RMAN> convert tablespace ts to platform 'Solaris[tm] OE (64-bit)'
2> format '/home/oracle/trans/%U';
Starting conversion at source at 31-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00011 name=+DATA/stone/datafile/ts.261.907752919
converted datafile=/home/oracle/trans/data_D-STONE_I-3016613505_TS-TS_FNO-11_2nr1sjrp
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 31-MAR-16
Starting Control File and SPFILE Autobackup at 31-MAR-16
piece handle=+FRA/stone/autobackup/2016_03_31/s_907956091.271.907956093 comment=NONE
Finished Control File and SPFILE Autobackup at 31-MAR-16
[oracle@test ~]$ ls trans/
data_D-STONE_I-3016613505_TS-TS_FNO-11_2nr1sjrp
例子：传输表空间
源端查看字节顺序：
SQL> SELECT tp.endian_format FROM v$transportable_platform tp, v$database d WHERE tp.platform_name = d.platform_name;
ENDIAN_FORMAT
--------------
Little
目的端查看字节顺序：
SQL> SELECT tp.endian_format FROM v$transportable_platform tp, v$database d WHERE tp.platform_name = d.platform_name;
ENDIAN_FORMAT
--------------
Little
在源端创建测试表空间和表：
SQL> create tablespace tts01;
Tablespace created.
SQL> create table emp01 tablespace tts01 as select * from hr.employees;
Table created.
确认源端和目的端的目录对象：
SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ----------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/
SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ----------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/
检测源端表空间的自包含：
SQL> exec dbms_tts.transport_set_check('TTS01');
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
将源端的表空间设置为readonly
SQL> alter tablespace tts01 read only;
Tablespace altered.
导出源端的元数据
[oracle@oeldb1 ~]$ expdp dumpfile=tts01.dmp logfile=tts01.log transport_tablespaces=tts01
Export: Release 11.2.0.4.0 - Production on Thu Aug 25 22:31:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA dumpfile=tts01.dmp logfile=tts01.log transport_tablespaces=tts01
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/stone/dpdump/tts01.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS01:
/u01/app/oracle/oradata/STONE/datafile/o1_mf_tts01_cvxz0p42_.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Aug 25 22:32:54 2016 elapsed 0 00:01:21
将dump文件和表空间对应的数据文件拷贝到目的端相应目录：
[oracle@oeldb1 ~]$ scp /u01/app/oracle/admin/stone/dpdump/tts01.dmp oracle@192.168.247.142:/u01/app/oracle/admin/stone/dpdump
[oracle@oeldb1 ~]$ scp /u01/app/oracle/oradata/STONE/datafile/o1_mf_tts01_cvxz0p42_.dbf oracle@192.168.247.142:/u01/app/oracle/oradata/STONE/datafile/
在目的端导入元数据
[oracle@oeldb2 ~]$ impdp dumpfile=tts01.dmp transport_datafiles=/u01/app/oracle/oradata/STONE/datafile/o1_mf_tts01_cvxz0p42_.dbf
Import: Release 11.2.0.4.0 - Production on Thu Aug 25 23:08:08 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA dumpfile=tts01.dmp transport_datafiles=/u01/app/oracle/oradata/STONE/datafile/o1_mf_tts01_cvxz0p42_.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Aug 25 23:08:21 2016 elapsed 0 00:00:06
在目的端查看表空间状态并置为读写
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TTS01';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS01 READ ONLY
SQL> alter tablespace tts01 read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TTS01';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS01 ONLINE
SQL> select count(*) from emp01;
COUNT(*)
----------
107
Edition-Based Redefinition
Oracle在11g引入了Edition-Based Redefinition（EBR），主要是为了解决在更新数据库对象，比如PL/SQL程序，视图等，如果该对象被锁住了，会导致更新必须等待，如果要使更新立即完成，则需要停止应用的问题。实现方式就是通过创建版本，新的代码在子版本实现，通过指定版本来对新旧版本代码进行切换。
这个功能主要用在有大量PL/SQL程序的数据库，例如Oracle EBS数据库。
EBR通过版本化的方式可实现以下目标：
代码的改变，包括PL/SQL，表定义等，安装在子（新）版本中
数据的改变只写到新的表或新的列，父（老）版本不会看到
跨版本触发器将父（老）版本应用所做改变反映到子（新）版本中，反之亦然
EBR依赖于3类新的对象，即edition、editioning view和crossedition trigger。
若只改变视图，同义词和PL/SQL对象，edition就够了
若表结构和数据的改变并是在后端完成，不涉及终端用户，则只需edition和editioning view
若表结构和数据的改变是由终端用户发起，则三者都需要
Edition
版本化和非版本化对象
Edition是非Schema对象，不属于任何用户，从11gR2开始，每个数据库都有一个默认版本，即ORA$BASE。
SQL> show edition
EDITION
------------------------------
ORA$BASE
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
SQL> select edition_name,parent_edition_name,usable from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USA
------------------------------ ------------------------------ ---
ORA$BASE YES
可版本化对象
不是所有的模式对象都是可版本化（editionable ）的，可版本化的模式对象有：
SYNONYMVIEW- All PL/SQL object types:
FUNCTIONLIBRARYPACKAGEandPACKAGE BODYPROCEDURETRIGGERTYPEandTYPE BODY
除此之外，其他对象，例如表，公有同义词，都是不支持版本化的。
如果可版本化对象的所有者是editions-enabled ，则该对象是editioned ，否则该对象是potentially editioned。
版本化对象规则
A noneditioned object cannot depend on an editioned object.
For example:
- A public synonym cannot refer to an editioned object.
- A function-based index cannot depend on an editioned function.
- A materialized view cannot depend on an editioned view.
- A table cannot have a column of a user-defined data type (collection or Abstract Data Type (ADT)) whose owner is editions-enabled.
- A noneditioned subprogram cannot have a static reference to a subprogram whose owner is editions-enabled.
For the reason for this rule, see "Actualizing Referenced Objects".
An ADT cannot be both editioned and evolved.
For information about type evolution, see Oracle Database Object-Relational Developer's Guide.
An editioned object cannot be the starting or ending point of a
FOREIGN KEYconstraint.The only editioned object that this rule affects is an editioned view. An editioned view can be either an ordinary view or an editioning view.
用户启用版本
可以在CREATE USER或者ALTER USER语句中使用使用ENABLE EDITIONS语句来为用户启用版本。该操作是不可逆的，一旦为用户启用版本，则该用户现有可版本化对象及后续创建的可版本化对象就自动版本化了。
如果启用版本的两个用户之间存在相关的依赖关系，还需要使用FORCE关键字先为某一个用户启用版本。例如用户A有可版本化的对象a1和a2，用户B有可版本的对象b1和b2，对象a1依赖对象b1，对象b2依赖对象a2，则为用户A和用户B启用版本步骤如下：
Using
FORCE, enable editions for userA:ALTER USER A ENABLE EDITIONS FORCE;Now
a1anda2are editioned objects, andb2(which depends ona2) is invalid.Enable editions for user
B:ALTER USER B ENABLE EDITIONS;Recompile
b2, using the appropriateALTERstatement withCOMPILE. For a PL/SQL object, also specifyREUSESETTINGS.For example, if
b2is a procedure, use this statement:ALTER PROCEDURE b2 COMPILE REUSE SETTINGS
创建版本
使用CREATE EDITION语句来创建版本。
继承对象和实际对象
每个数据库会话一次只能使用一个版本。创建时，子版本从其父版本继承数据库中在父版本中可见的所有已版本化对象。每个继承的对象在子版本中可见。
例子： creates a procedure named hello in the edition ora$base, and then creates the edition e2 as a child of ora$base. When e2 invokes hello, it invokes the inherited procedure. Then e2 changes hello, actualizing it. The procedure hello in the edition ora$base remains unchanged, and is no longer visible in e2. Now when e2 invokes hello, it invokes the actual procedure.
先创建用户，授予权限，启用版本，切换到新创建的用户
SQL> create user user1 identified by user1 default tablespace users;
User created.
SQL> grant dba to user1;
Grant succeeded.
SQL> alter user user1 enable editions;
User altered.
SQL> conn user1/user1
Connected.
再执行如下步骤：
Create procedure in parent edition:
CREATE OR REPLACE PROCEDURE hello IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello, edition 1.'); END hello; /Invoke procedure in parent edition:
BEGIN hello(); END; /Result:
Hello, edition 1. PL/SQL procedure successfully completed.Create child edition:
CREATE EDITION e2;Use child edition:
ALTER SESSION SET EDITION = e2;For information about
ALTERSESSIONSETEDITION, see "Changing Your Session Edition".In child edition, invoke procedure:
BEGIN hello(); END; /Child edition inherits procedure from parent edition. Child edition invokes inherited procedure. Result:
Hello, edition 1. PL/SQL procedure successfully completed.Change procedure in child edition:
CREATE OR REPLACE PROCEDURE hello IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello, edition 2.'); END hello; /Child changes only its own copy of procedure. Child's copy is an actual object.
Invoke procedure:
BEGIN hello(); END; /Child invokes its own copy, the actual procedure:
Hello, edition 2. PL/SQL procedure successfully completed.Return to parent:
ALTER SESSION SET EDITION = ora$base;Invoke procedure and see that it has not changed:
BEGIN hello(); END; /Result:
Hello, edition 1. PL/SQL procedure successfully completed.
删除继承对象
如果子版本的用户删除继承对象，则该对象在子版本中不再可见，但在父版本中仍然可见。
例子： creates a procedure named goodbye in the edition ora$base, and then creates edition e2 as a child of ora$base. After e2 drops goodbye, it can no longer invoke it, but ora$base can still invoke it.
先删除之前创建的版本：
SQL> show edition
EDITION
------------------------------
ORA$BASE
SQL> drop edition e2 cascade;
Edition dropped.
再执行以下步骤：
Create procedure in edition
ora$base:CREATE OR REPLACE PROCEDURE goodbye IS BEGIN DBMS_OUTPUT.PUT_LINE('Good-bye!'); END goodbye; /Invoke procedure:
BEGIN goodbye; END; /Result:
Good-bye! PL/SQL procedure successfully completed.Create edition
e2as a child ofora$base:CREATE EDITION e2;Use edition
e2:ALTER SESSION SET EDITION = e2;ALTERSESSIONSETEDITIONmust be a top-level SQL statement. For more information, see "Changing Your Session Edition".In
e2, invoke procedure:BEGIN goodbye; END; /e2invokes inherited procedure:Good-bye! PL/SQL procedure successfully completed.In
e2, drop procedure:DROP PROCEDURE goodbye;In
e2, try to invoke dropped procedure:BEGIN goodbye; END; /Result:
BEGIN goodbye; END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'GOODBYE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignoredReturn to parent:
ALTER SESSION SET EDITION = ora$base;In parent, invoke procedure:
BEGIN goodbye; END; /Result:
Good-bye! PL/SQL procedure successfully completed.
例子： e2 creates a function named goodbye and then an edition named e3 as a child of e2. When e3 tries to invoke the procedure goodbye (which e2 dropped), an error occurs, but e3 successfully invokes the function goodbye (which e2 created).
Return to
e2:ALTER SESSION SET EDITION = e2;For information about
ALTERSESSIONSETEDITION, see "Changing Your Session Edition".In
e2, create function namedgoodbye:CREATE OR REPLACE FUNCTION goodbye RETURN BOOLEAN IS BEGIN RETURN(TRUE); END goodbye; /Create edition
e3:CREATE EDITION e3 AS CHILD OF e2;Use edition
e3:ALTER SESSION SET EDITION = e3;In
e3, try to invoke proceduregoodbye:BEGIN goodbye; END; /Result:
goodbye; * ERROR at line 2: ORA-06550: line 2, column 3: PLS-00221: 'GOODBYE' is not a procedure or is undefined ORA-06550: line 2, column 3: PL/SQL: Statement ignoredIn
e3, invoke functiongoodbye:BEGIN IF goodbye THEN DBMS_OUTPUT.PUT_LINE('Good-bye!'); END IF; END; /Result:
Good-bye! PL/SQL procedure successfully completed.
版本授权
版本创建者可以使用语句 GRANT USE ON EDITION 将版本的使用权限授予其他用户。
如果要授予所有用户，则可以使用以下两种方式：
Grant the
USEprivilege on the edition toPUBLIC:GRANT USE ON EDITION edition_name TO PUBLICMake the edition the database default edition:
ALTER DATABASE DEFAULT EDITION = edition_nameThis has the side effect of granting the
USEprivilege onedition_nametoPUBLIC.
当前版本和会话版本
数据库会话在任何时间使用的版本为当前版本（current edition），当数据库会话开始，当前版本就是其会话版本（session edition），改变会话版本，当前版本也会随之改变。但是，也会有当前版本与会话版本不同的情况。
初始会话版本
当连接数据库时，可以指定初始会话版本，通过以下SQL查询可以指定的版本：
SELECT EDITION_NAME FROM ALL_EDITIONS;
那么如何在连接数据库的时候指定版本呢，可以通过在service中指定，Oracle推荐使用 srvctl add service 或者srvctl modify service 命令，通过-t选项指定服务的默认初始会话版本。
如果连接时没有指定版本，则会使用数据库默认版本作为初始会话版本。
修改会话版本
使用 ALTER SESSION SET EDITION 语句修改当前会话版本。
查看当前版本和会话版本
查看当前版本：
SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM DUAL;
查看会话版本：
SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') FROM DUAL;
退役版本
通过收回edition的use权限实现。
删除版本
系统默认的edition不能删除。 必须没有人使用此edition时才可删除。 或者其没有子edition，或者其子edition中没有editioned对象时才可删除。
Editioning view
由于表是不能被版本化的，那么如果要更改表的结构，如增加字段，这时就需要使用版本视图来向用户提供不同版本下的表结构。
Crossedition Triggers
如果发布版本时还涉及到数据的变化，那么就需要使用跨版本触发器。跨版本触发器分为：
- 正向跨版本触发器：将父版本表字段的数据变化同步到子版本相关字段中，在父版本触发
- 反向跨版本触发器：将子版本表字段的数据变化同步到父版本相关字段中，在子版本触发
实战
使用EBR来进行在线程序更新，具体是将HR用户下的EMPLOYEES表 PHONE_NUMBER 字段拆分为 COUNTRY_CODE和PHONE_NUMBER_WITHIN_COUNTRY两个字段 ，这里涉及到表结构和数据的变化，需要用到Editioning view和Crossedition Triggers。
准备工作
- 安装数据库
- 下载和解压 ebr.zip
配置环境
使用SYS用户连接到数据库：
SQL> conn / as sysdba
Connected.
使用HR用户连接到数据库，默认使用的是父版本ORA$BASE：
SQL> conn hr/hr
Connected.
SQL> show edition
EDITION
------------------------------
ORA$BASE
EBR准备
在SYS用户下为HR用户启用版本：
SQL> ALTER USER hr ENABLE EDITIONS;
User altered.
在HR用户下重命名需要修改的表：
SQL> ALTER TABLE employees RENAME TO employees_;
Table altered.
在HR用户下为重命名的表创建版本视图，视图的名字为表之前的名字：
SQL> CREATE EDITIONING VIEW employees AS
2 SELECT
3 employee_id, first_name, last_name, email, PHONE_NUMBER, hire_date, job_id, salary, commission_pct, manager_id, department_id
4 FROM employees_;
View created.
创建子版本
在SYS用户下基于当前默认版本Ora$Base创建新的版本post_upgrade：
SQL> CREATE EDITION post_upgrade AS CHILD OF Ora$Base;
Edition created.
使用SYS用户将版本post_upgrade的USE权限授予HR用户：
SQL> GRANT USE ON EDITION post_upgrade TO hr;
Grant succeeded.
使用子版本
在HR用户下设置当前版本为post_upgrade：
SQL> ALTER SESSION SET EDITION = post_upgrade;
Session altered.
修改表结构
在HR用户下修改employees_表结构：
SQL> ALTER TABLE employees_ ADD
2 (COUNTRY_CODE VARCHAR2(5),
3 PHONE_NUMBER_WITHIN_COUNTRY VARCHAR2(20));
Table altered.
修改版本视图
在HR用户下，在子版本post_upgrade中，修改之前在父版本Ora$Base中创建的版本视图employees，增加字段COUNTRY_CODE和PHONE_NUMBER_WITHIN_COUNTRY ：
SQL> CREATE OR REPLACE EDITIONING VIEW employees AS
2 SELECT employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, phone_number_within_country, country_code
3 FROM employees_;
View created.
创建正向跨版本触发器
在HR用户下，在子版本post_upgrade中，执行脚本fwd_ce.sql，具体代码为：
-- The procedure called by empl_forward
VARIABLE Warnings VARCHAR2(4000)
begin
:Warnings := $$Plsql_Warnings;
end;
/
--------------------------------------------------------------------------------
alter session set Plsql_Warnings = ' enable:all, disable:06005, disable:06006'
/
create or replace procedure Set_Country_Code_And_Phone_No(
Phone_Number in varchar2,
Country_Code out nocopy varchar2,
Phone_Number_V2 out nocopy varchar2)
is
Char_To_Number_Error exception;
pragma Exception_Init(Char_To_Number_Error, -06502);
Bad_Phone_Number exception;
Nmbr varchar2(30) := Replace(Phone_Number, '.', '-');
function Is_US_Number(Nmbr in varchar2) return boolean is
Len number := Length(Nmbr);
Dash_Pos number := Instr(Nmbr, '-');
n pls_integer;
begin
if Len is null or Len <> 12 then
return false;
end if;
if Dash_Pos is null or Dash_Pos <> 4 then return false; end if;
begin
n := To_Number(Substr(Nmbr, 1, 3));
exception when Char_To_Number_Error then
return false;
end;
Dash_Pos := Instr(Nmbr, '-', 5);
if Dash_Pos is null or Dash_Pos <> 8 then return false; end if;
begin
n := To_Number(Substr(Nmbr, 5, 3));
exception when Char_To_Number_Error then
return false;
end;
begin
n := To_Number(Substr(Nmbr, 9));
exception when Char_To_Number_Error then
return false;
end;
return true;
end Is_US_Number;
begin
if Nmbr like '011-%' then
declare
Dash_Pos number := Instr(Nmbr, '-', 5);
begin
Country_Code := '+'||To_Number(Substr(Nmbr, 5, Dash_Pos-5));
Phone_Number_V2 := Substr(Nmbr, Dash_Pos+1);
exception when Char_To_Number_Error then
raise Bad_Phone_Number;
end;
elsif Is_US_Number(Nmbr) then
Country_Code := '+1';
Phone_Number_V2 := Nmbr;
else
raise Bad_Phone_Number;
end if;
exception when Bad_Phone_Number then
Country_Code := '+0';
Phone_Number_V2 := '000-000-0000';
end Set_Country_Code_And_Phone_No;
/
--------------------------------------------------------------------------------
declare
Stmt constant varchar2(32767) := '
alter session set Plsql_Warnings = '''||:Warnings||'''';
begin
execute immediate Stmt;
end;
/
-- The trigger empl_forward
CREATE OR REPLACE TRIGGER empl_forward
BEFORE INSERT OR UPDATE ON employees_
FOR EACH ROW
FORWARD CROSSEDITION
DISABLE
BEGIN
Set_Country_Code_And_Phone_No(
:New.Phone_Number,
:New.Country_Code,
:New.Phone_Number_Within_Country);
END;
这段代码在表employees_ 创建了一个正向跨版本触发器empl_forward，当在父版本Ora$Base上对表employees_ 进行DML操作时就会触发该触发器，将对父版本的修改传递到子版本去。
SQL> @fwd_ce.sql
PL/SQL procedure successfully completed.
Session altered.
SP2-0804: Procedure created with compilation warnings
PL/SQL procedure successfully completed.
12 /
Trigger created.
创建反向扩版本触发器
在HR用户下，在子版本post_upgrade中，执行脚本rev_ce.sql，具体代码为：
CREATE OR REPLACE TRIGGER empl_reverse
BEFORE INSERT OR UPDATE ON employees_
FOR EACH ROW
REVERSE CROSSEDITION
DISABLE
BEGIN
:NEW.phone_number :=
CASE :NEW.country_code
WHEN '+1' THEN
REPLACE(:NEW.phone_number_within_country, '-', '.')
ELSE
'011.'||LTRIM(:NEW.country_code, '+')||'.'||
REPLACE(:NEW.phone_number_within_country, '-', '.')
END;
END employees_reverse;
/
这段代码在表employees_ 创建了一个反向跨版本触发器empl_reverse，当在子版本post_upgrade上对表employees_ 进行DML操作时就会触发该触发器，将对子版本的修改传递到父版本去。
SQL> @rev_ce.sql
Trigger created.
正向操作
在HR用户下，在子版本post_upgrade中，执行脚本bulk_fwd.sql，具体代码为：
ALTER trigger empl_forward enable
/
ALTER trigger empl_reverse enable
/
DECLARE
c number := DBMS_Sql.Open_Cursor();
x number;
BEGIN
DBMS_Sql.Parse(
c => c,
Language_Flag => DBMS_Sql.Native,
Statement => 'update employees set employee_id = employee_id',
Apply_Crossedition_Trigger => 'empl_forward');
x := DBMS_Sql.Execute(c);
DBMS_Sql.Close_Cursor(c);
commit;
end;
/
这段代码先启用前面创建的两个触发器，然后执行一个update语句，并触发正向跨版本触发器empl_forward。
SQL> @bulk_fwd.sql
Trigger altered.
Trigger altered.
PL/SQL procedure successfully completed.
执行成功后，再执行脚本ver_fwd.sql查看结果，具体代码为：
--切换到父版本，执行update会触发正向跨版本触发器
ALTER SESSION SET EDITION =ORA$BASE;
SELECT phone_number FROM employees WHERE employee_id=101;
UPDATE employees SET phone_number = '515.123.4444' WHERE employee_id =101;
COMMIT;
ALTER SESSION SET EDITION = post_upgrade;
SELECT employee_id, country_code, phone_number_within_country
FROM employees WHERE employee_id =101;
执行结果如下：
SQL> @ver_fwd.sql
Session altered.
PHONE_NUMBER
--------------------
515.123.4568
1 row updated.
Commit complete.
Session altered.
EMPLOYEE_ID COUNT PHONE_NUMBER_WITHIN_
----------- ----- --------------------
101 +1 515-123-4444
在父版本插入或者更新employees_表之前，empl_forward触发器使用PHONE_NUMBER字段的新值更新字段COUNTRY_CODE和PHONE_NUMBER_WITHIN_COUNTRY。所以在子版本中，查询COUNTRY_CODE和PHONE_NUMBER_WITHIN_COUNTRY就可以看到更新的值。
反向操作
在HR用户下，在子版本post_upgrade中，执行脚本ver_rev.sql，具体代码为：
--在子版本执行update会触发反向跨版本触发器
ALTER SESSION SET EDITION =post_upgrade;
UPDATE employees SET phone_number_within_country = '515.123.4567'
WHERE employee_id =101;
SELECT employee_id, country_code, phone_number_within_country
FROM employees WHERE employee_id=101;
COMMIT;
ALTER SESSION SET EDITION = ORA$BASE;
SELECT employee_id, phone_number
FROM employees WHERE employee_id =101;
执行结果如下：
SQL> @ver_rev.sql
Session altered.
1 row updated.
EMPLOYEE_ID COUNT PHONE_NUMBER_WITHIN_
----------- ----- --------------------
101 +1 515.123.4567
Commit complete.
Session altered.
EMPLOYEE_ID PHONE_NUMBER
----------- --------------------
101 515.123.4567
当在子版本post_upgrade中向表employees_插入数据或者更新PHONE_NUMBER_WITHIN_COUNTRY字段时，就会触发empl_reverse触发器，使用PHONE_NUMBER_WITHIN_COUNTRY这个字段的新值来更新PHONE_NUMBER字段。
发布版本
通过回收HR用户对父版本的权限，来发布子版本。
--连接到HR用户，默认使用的还是父版本，查询的数据也是来自于父版本的版本视图
SQL> conn hr/hr
Connected.
SQL> show edition
EDITION
------------------------------
ORA$BASE
SQL> select * from employees where employee_id=101;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- ---------- ---------- -------------------- ----------------- ---------- ---------- -------------- ---------- -------------
101 Neena Kochhar NKOCHHAR 515.123.4567 20050921 00:00:00 AD_VP 17000 100 90
--连接到SYS用户，设置数据库的默认版本为子版本，然后退役父版本
SQL> conn / as sysdba
Connected.
SQL> ALTER DATABASE DEFAULT EDITION = post_upgrade;
Database altered.
SQL> select grantee,privilege from dba_tab_privs where table_name='ORA$BASE';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
PUBLIC USE
SQL> REVOKE USE ON EDITION ora$base FROM PUBLIC;
Revoke succeeded.
--再次连接到HR用户，默认使用的就是子版本了，查询的数据也是来自于子版本的版本视图
SQL> conn hr/hr
Connected.
SQL> show edition
EDITION
------------------------------
POST_UPGRADE
SQL> select * from employees where employee_id=101;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID PHONE_NUMBER_WITHIN_ COUNT
----------- ---------- ---------- ---------- -------------------- ----------------- ---------- ---------- -------------- ---------- ------------- -------------------- -----
101 Neena Kochhar NKOCHHAR 515.123.4567 20050921 00:00:00 AD_VP 17000 100 90 515.123.4567 +1
--最后禁用掉跨版本触发器
SQL> ALTER trigger empl_forward disable;
Trigger altered.
SQL> ALTER trigger empl_reverse disable;
Trigger altered.