Oracle系列之--Profile

Oracle系列之–Profile

最近一个项目数据库换了19c，然后就开始频繁报错，并且是个很诡异的错：java.sql.SQLException: ORA-01012: not logged on，定眼一看很简单，仔细一想不对劲。所有单词都认识，连起来就不懂了，这个错一般是我们使用PL/SQL这种开发工具时会出现的错误，一般和人为操作有关；但是这是程序报的错，程序也没有更改过，数据库也很正常，然而就是频繁的出现这个错误信息。

借由这个错误，今天就来说说Oracle中的资源限制，你也可以理解为环境变量。在Oracle中针对每一个用户也都是有响应的资源限制参数，平常使用我们是无感的，因为在没有显式设置时Oracle会为每一个用户使用一个默认的环境变量，即Default_Profile。所以我们并不需要单独进行设置，最近那个项目报错并不是换了19c才报的错，是换了DBA所以才报的错，表面掩盖了实质。。。新DBA对项目使用的Oracle用户修改了一个参数限制idle_time=15，默认情况下是UNLIMITED的，也就是无限制，被DBA改成了15，因此才导致了出现ORA-01012的错误，这个参数限制了空闲15分钟的数据库连接会被后台进程PMON强制释放回收。

问题已经找到，接下来就说说Profile吧。（以下内容都是从官网直接获取，边翻译边理解的，如有理解偏差请多指教。）

一、Profile概念

Oracle中的权限管理其实是非常精细的，只是在平常使用中我们都为了图省事儿全都使用了dba用户，这种最大化权限的操作方式让我们对数据库的访问变得简单，同时也留下了安全隐患以及一些性能问题。而Profile作为安全策略的一部分，可以为用户使用的各种Oracle系统级别的资源进行限制，通过这种方式能够定制化的限制用户的对数据库各种资源的异常消耗和占用，比如说cpu时间、IO、数据块访问量、会话数、超时时间等。

在大型系统中，这种有针对性的限制是非常有用的，因为数据库资源总量是一定的，如果某一用户消耗过多资源可能会对其他用户产生不利影响，但是设置资源限制会对用户创建会话有一些性能影响，用户在连接数据库的时候数据库需要把所有资源限制数据做导入。

二、限制级别和类型

对资源的限制有session级别和call级别，能够限制cpu时间、逻辑读以及其他一些类型。这里先介绍一下各种概念，具体的操作在后边会进行罗列。

1、session级别

这个就是比较熟知会话级别的限制，限制当前会话对数据库cpu及内存资源的访问，如果一旦超出限制数据库就会中断当前会话并对事务进行回滚，同时返回错误信息说明已经达到session级别的限制。该事务中之前执行的操作都还是完整的，对当前操作只能执行commit、rollback或者disconnect（执行disconnect时默认当前事务会提交），除此之外当前会话不能再执行任何其他操作，执行就会报错。

2、call级别

call级别比session级别更加精细，能够控制执行某一sql时的资源限制，call即调用。用户去执行一条sql能有什么操作呢，其实每一条sql的执行都会分几步来进行，如sql美化、预编译、编译、执行计划、加锁、内存检索、数据块检索等，因此在执行sql的不同阶段都会产生多个调用，就是为了防止在多个调用时出现资源消耗过大的现象而对call级别进行资源限制。如果达到了call级别的限制那么数据库就会终止处理当前sql，并对当前sql进行回滚，然后返回一个错误信息。对当前事务中其他sql是没有影响的，连接也不会断开。这种控制更精准但是操作起来也会更复杂。

3、CPU时间限制

每条sql执行时都会占用cpu time，为了防止一个大量数据操作的sql或异常sql占用过多的cpu time资源，可以对session级别和call级别的cpu时间进行动态或静态限制，单位时百分之一秒。

4、逻辑读限制

涉及到逻辑读就说明内存已经无法命中，必须要进行I/O操作，这对于数据库来说是十分昂贵的。内存命中率低于95%时就说明数据库出现了性能问题，这是数据库系统的主要性能瓶颈。为了防止某个事务占用过多I/O资源，可以在session级别和call级别对逻辑读的数据块数目做出限制。逻辑读包括从内存和磁盘读取数据块。这些限制是通过调用或会话期间执行的块读取次数来设置和度量的。

5、其他资源限制

对于其他资源的限制一般都是session级别的，可以限制：

• 用户的并发会话数：同一时间该用户能够有多少会话数，这在开发环境中是不应该设置的。

• 会话空闲超时时间：对于空闲连接，如果两次call之间的时间间隔达到设置值会话连接就会被强制回收，当前事务回滚，释放资源。在当前会话中再次执行sql操作就会报错，返回实例已经断开连接的的错误信息（文章最开始的错误就是这个设置引起的），单位是分钟。

  **需要指出的是：**就算当前会话因为该参数被释放，计算用户和会话资源限制时还是会被计算在内（比如用户的并发数限制）

• 会话连接超时时间：会话的最大连接时间，无论会话是否空闲，当达到这个限制后，这个会话都会被回滚，资源释放，单位是分钟。该参数配置时就需要谨慎，如果配置不当可能会导致业务的中断。

  **需要指出的是：**出于性能考虑oracle并不会连续的实时监控空闲时间和连接时间，所以数据库在检测到会话超时的时间和配置的时间之间会有几分钟的差值，如果配置15分钟的空闲超时时间，实际的空闲超时时间可能在15-17分钟左右

• 会话占用的SGA空间：能够限制SGA使用空间大小，单位是K或M。

三、限制资源设置建议

上面说到对于资源的限制可以使用静态的数值和动态限制，这其实是一种取舍。静态的数值设置简单，效果显而易见，但是适用的场景也比较固定，一旦偶有超出限制的资源操作就会出现错误；而动态的限制就比较灵活，设置起来也会比较复杂，动态的设置是通过分配各资源的使用限制权重和一个总的资源消耗阈值进行控制的。这种动态控制的前提是需要对系统比较熟悉，能够收集或者已经了解关于系统每种资源使用类型的历史信息。

对于静态参数需要配合resource_limit参数使用才能生效，通过v$parameter进行查看是否开启，使用alter system set resource_limit=true scope=both;来修改该参数状态；而动态参数的设置需要通过dba_profiles表中的composite_limit参数来控制开启，否则只设置各资源限制参数的权重是不生效的，使用alter profile <profile_name> limit composite_limit <integer_num/unlimited/default>;后边会具体说每项参数的具体设置方法和设置值。

四、Profile的作用

对profile有所了解后就明白它的作用，就是用作资源管理。就像开头所说的，profile就像是环境变量，每个数据库用户都必须要有一套且只能有一套对应的profile，多个用户可以公用同一套profile；未进行明确声明的用户用的就是数据库默认的profile–DEFAULT_PROFILE。

作为资源管理的一种方式，其实使用profile进行管理的资源还是非常有限的，oracle官方推荐使用Database Resource Manager管理系统资源，这是一种更加精细，更加专业的资源管理方式；允许oracle控制硬件级别的资源分配来更好的发挥数据库的性能。不过在实际应用中使用profile方式的更多，相对于Database Resource Manager更加复杂的管理配置方式，profile就显得简单了许多，并且profile所能限制的这几种资源对于大多数企业用户来说已经基本够用了。

对于profile的另外一种用途就是对数据库密码资源的控制，这在数据库日常管理中相对来说更常用到。上面说到，想要启用资源限制就需要使用alter system set resource_limit=true scope=both;来开启该功能，针对的就是资源管理，而profile中的密码管理始终是开启的，不需要也不能进行手动关闭。

参考官网：Configuring User Resource Limits

五、使用Profile

上面说了那么多基本都是概念性质，接下来就具体说说这个profile是如何使用的，包括创建、删除、修改其中的参数等。

1、创建

首先，想要创建profile必须要有create profile权限，这个和create user权限是一样的；创建之后只是有了一个profile，想要使用还必须要指定一个用户，才能发挥应有的作用；其次，还需要数据库开启resource_limit功能，前面已经说过了，就不再赘述了；最后，profile有两部分组成，resource部分和password部分。通过dba_profiles中RESOURCE_TYPE字段可以清晰的看到两部分区别:

PROFILE	RESOURCE_NAME	RESOURCE_TYPE	LIMIT
DEFAULT	COMPOSITE_LIMIT	KERNEL	UNLIMITED
DEFAULT	SESSIONS_PER_USER	KERNEL	UNLIMITED
DEFAULT	CPU_PER_SESSION	KERNEL	UNLIMITED
DEFAULT	CPU_PER_CALL	KERNEL	UNLIMITED
DEFAULT	LOGICAL_READS_PER_SESSION	KERNEL	UNLIMITED
DEFAULT	LOGICAL_READS_PER_CALL	KERNEL	UNLIMITED
DEFAULT	IDLE_TIME	KERNEL	UNLIMITED
DEFAULT	CONNECT_TIME	KERNEL	UNLIMITED
DEFAULT	PRIVATE_SGA	KERNEL	UNLIMITED
DEFAULT	FAILED_LOGIN_ATTEMPTS	PASSWORD	10
DEFAULT	PASSWORD_LIFE_TIME	PASSWORD	UNLIMITED
DEFAULT	PASSWORD_REUSE_TIME	PASSWORD	UNLIMITED
DEFAULT	PASSWORD_REUSE_MAX	PASSWORD	UNLIMITED
DEFAULT	PASSWORD_VERIFY_FUNCTION	PASSWORD	NULL
DEFAULT	PASSWORD_LOCK_TIME	PASSWORD	1
DEFAULT	PASSWORD_GRACE_TIME	PASSWORD	7

因此，在创建profile时就是要指定以上这些参数的值，如果不指定，那么就会使用默认值，即default的值。

截取官网上的两个图来说明创建profile的具体sql语句：

这个是创建语句，其中椭圆中的profile是你的profile名称，如dev_profile/prod_profile等，起一个标识作用；LIMIT后边的参数分为两部分，resource_parameters和password_parameters，这里只是更便于说明，虽然这么标识但是实际SQL中并不会有明显区分，在LIMIT后边直接罗列的就是上面表格中的那些参数名，这里应该是通过PASSWORD前缀去区分是否是密码参数的。

对于resource_parameters部分：

包含两部分，参数名和参数值。参数名就是上边表格列出的9个，对于参数值，前8个有整型、无限制和默认三种选择；对于SGA的限制则有一点区别就是有单位区别，像K、M、G、T等，前8个单位都是固定的。

对于password_parameters部分：

也包含两部分，参数名和参数值。参数值的不同区别在于使用表达式或者函数，当然这里使用使用整型数值也还是可以的。

具体每个参数的含义及其单位会在后边单独一一讲解。需要单独说一下对于profile的名称需要符合Oracle的命名规范，包括不建议使用“SYS”和“ORA”开头的名称，名称中不建议使用双引号，如果使用“（双引号）要成对出现，同时会自动忽略名称中的双引号，不区分大小写，名称的长度限制虽然最长可以达到128字节，但是建议名称不超过30字节等等。具体的命名规则可以参照官方：Database Object Names and Qualifiers

这里再给出两个具体的实例以作说明：

/**创建resource相关资源限制profile**/
CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000;

创建了一个名为“app_user”的profile，并进行了如下限制：

• 对当前用户下会话的并发数不做限制；
• 执行sql时占用的cpu时间不做限制；
• 执行单条sql占用的cpu时间限制不超过30秒；
• 每个连接最长使用时间未45分钟；
• 每个会话中能够进行的逻辑数据块读使用默认值；
• 每次调用sql能够读取内存和磁盘中的数据块不超过1000个；
• 每个会话不能占用超过15KB的SGA空间；
• 每个会话的总资源消耗成本不能超过500万；
• 没有对连接空闲时间和密码进行限制，oracle将会使用默认profile进行限制。

/**创建password相关资源限制**/
CREATE PROFILE app_user2 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

创建了一个“app_user2”的profile并进行了密码相关限制。对于密码验证功能使用了一个verify_function函数，具体如何使用验证函数或者想要自定义验证函数可以参考：Introducing Oracle Database Security

2、删除

删除时也需要有drop权限，并配合cascade关键字进行删除，其实与删除表的操作一样。DROP PROFILE <profile_name> CASCADE;

删除profile后，该profile所指定的用户将会自动被分配给Default Profile；因此，Default Profile是无法被删除的，就是上边表格中的这些参数是不能被删除的。同时，删除的profile不会应用到当前已连接的会话中，在之后创建的会话中才会生效。

参考官网：CREATE PROFILE

六、限制资源参数

在创建profile的sql语句中可以看到，在参数的值中两个较常见的值：UNLIMITED和DEFAULT。

UNLIMITED

表示对资源使用没有上限，可以尽可能大的占用系统资源；在密码设置中表示对该参数无限制。

DEFAULT

表示使用默认的profile配置，对于一些不必要或可以忽略的参数可以使用默认值，在默认profile中大部分参数值是unlimited的，因此大部分的default其实等于unlimited。

如果在指定资源参数时不显式的指定参数的值，那么该参数会自动使用default。

RESOURCE_PARAMETERS

SESSIONS_PER_USER

限制单个用户的并发会话数

CPU_PER_SESSION

指定单个会话的cpu使用时间限制，单位百分之一秒

CPU_PER_CALL

指定每次调用时的cpu使用时间限制，这里的调用指解析、执行和读取等方法，单位百分之一秒

CONNECT_TIME

指定一个会话的总运行时间限制，单位分钟

IDLE_TIME

指定一个会话中允许连续的非活动时间，如果一直在执行sql或其他数据库交互操作的时间不计算在内，对比connect_time可以清楚这里是指空闲连接超时时间，单位分钟

LOGICAL_READS_PER_SESSION

指定一个会话中允许读取的数据块数量，包括从内存(逻辑读)和磁盘读取(物理读)的数据块，单位个

LOGICAL_READS_PER_CALL

在每次调用sql时允许读取的数据块数量，单位个

PRIVATE_SGA

指定会话可以在SGA共享池中分配的私有空间数量，该参数生效的前提是数据库启用了SGA管理内存模式，即是在ASMM模式才生效。对于该参数的单位有bytes、K、M、G、T、P、E，从小到大都是1024倍的关系，如果不指定单位数据库默认使用字节作为单位，建议明确单位限制，在某些情况下不指定单位可能会有错误信息出现。

COMPOSITE_LIMIT

限制单个会话能够使用的系统总资源成本，单位是一种服务单位。计算方式是CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION和PRIVATE_SGA的加权和。具体的使用和计算方式会在后边单独讲解。

PASSWORD_PARAMETERS

设置密码参数时，FAILED_LOGIN_ATTEMPTS和PASSWORD_REUSE_MAX这两个参数值是整型数字，除了这两个参数之外的其他参数，单位都是天。但是之前说了这些参数都可以是表达式，是因为在某些场景下可能天这个单位过大（比如测试时），因此可以用表达式来缩短参数设置的时间，比如使用1/1440限定为1分钟，1/86400限定为1秒钟等，当然这里并不是说不能使用十进制来表示分钟和秒，只是使用表达式更为清晰，如果你使用0.0417来表示一个小时也是完全可以的，但是显然这个数字不方便计算并且这也不是一个精确值，只是1/24的近似值。不过这个值也有上下限，最小值为1秒，最大值为24855天。

FAILED_LOGIN_ATTEMPTS

指定连续登录失败的次数限制，超过此次数当前用户将会被锁定。如果不指定当前参数默认值为10次。

PASSWORD_LIFE_TIME

限制使用相同密码进行身份验证的天数，超过这个天数没有进行密码更改将会使密码过期，导致无法登录。如果不指定当前参数默认是180天。对于这个参数可能会有人有些疑问，自己创建的数据库用户一直都没有更改过密码也没有提示过密码过期的情况，这是因为你在创建用户时没有指定密码有效期，在create user中使用PASSWORD EXPIRE子句将会使该参数的设置值生效。

另外对于设置该参数时需要特别注意，此处设置的时间并不是从当前设置时间开始生效的，是从最近一次修改当前用户的密码开始计算的，用户的创建时间和密码修改时间记录在sys.user$表中，CTIME表示创建时间，PTIME表示修改时间。假如你设置了1天有效期，那么oracle将会从PTIME中记录的时间开始计算，超过1天没有修改密码将会使当前用户的密码有效期设置超时，无法再次登录。这一点是需要特别注意的。

想要查询已经创建的用户是否设置了密码有效期，可以使用如下sql：

SELECT CREATED, EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = '<user_name>';

对于密码有效期的设置对当前已经登录的用户是不生效的，数据库会从下一次用户连接时开始计算时间。因此，作为DBA想要修改该参数时需要确保所配置的profile指定的用户都没有进行连接，可以通过v$session视图进行查看。否则可能出现特权用户，即已经连接的用户一直不断开连接，那么这个设置对那个连接永远不会生效。

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

这两个参数是结合使用的。password_reuse_time指定密码可重用的周期，如设置为10则表示10天内修改的密码不能和上次相同；password_reuse_max指定密码不能重复使用的次数间隔，如设置3则表示最近一次修改的密码不能和最近三次中任意一次修改的密码相同。

如果你将其中一个设置为unlimited，那么密码将永远不能重复，同样如果指定为default数据库的默认配置也是unlimited，这是一样的效果。但是如果你将两个参数都设置为unlimited那么数据库就会忽略这两个参数。

PASSWORD_LOCK_TIME

连续登陆失败达到限制次数后密码锁定的天数，默认值是1天。

PASSWORD_GRACE_TIME

密码过期的宽限期，配合password_life_time使用，能够使密码在使用超过有效期后并不会马上禁止登录，会在登录时进行警告ORA-28002: The password will expire within n days；当超过宽限期还未进行密码修改则密码将会彻底失效。如果不设置该参数默认宽限期是7天。

PASSWORD_VERIFY_FUNCTION

密码复杂度校验函数允许在创建profile时将函数脚本作为参数进行传递，oracle有默认的验证脚本，你也可以自定义验证脚本。

对于自定义脚本的函数，只需要将该参数的值设置为自定义的函数名即可，不过自定义的校验函数必须在SYS用户下，并且当前用户（创建profile的用户）必须有可执行权限。

如果该参数设置为null则表示不启用密码复杂度校验。

对于默认的密码验证函数11g和12c有所不同，11g使用的两个默认函数是VERIFY_FUNCTION和VERIFY_FUNCTION_11G，在12c中进行了废弃，使用了校验强度更强的ORA12C_VERIFY_FUNCTION和ORA12C_STRONG_VERIFY_FUNCTION来代替。想要查看这两个函数或者自定义函数可以查看${ORACLE_HOEM}/rdbms/admin/utlpwdmg.sql脚本，在11g中该脚本中定义了VERIFY_FUNCTION，想要看12c中的ORA12C_VERIFY_FUNCTION则需要查看${ORACLE_HOEM}/rdbms/admin/catpvf.sql脚本(11g中是没有这个脚本的，函数直接定义在utlpwdmg中，12c中将两者分离了)

参考官网：Semantics、Specifying a Profile for the User

七、修改静态配置

对于已经创建的profile想要添加、修改、删除配置参数均是通过alter profile语句进行的。修改后的参数只会对后续再新创建的会话产生影响，而不会影响当前会话，所以修改配置后请不要使用当前会话验证。

想要使用修改语句首先必须拥有alter权限，其次对于12c及以后版本由于使用容器管理实现可插拔，因此在修改配置时需要指定特定的容器，如果想要对所有容器均生效那就需要在根容器中进行操作。

这里又借了官网一个图来展示具体的sql：

与创建profile语句基本类似，这里多了一个CONTAINER子句来标定修改的配置作用域是当前容器还是全部容器，在12c之前是没有该参数的，就需要写了。其他两个子句resource_parameters和password_parameters的参数配置则和创建时的一样。

举个栗子：

ALTER PROFILE app_user LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LOCK_TIME 1
   PASSWORD_LIFE_TIME 90
   PASSWORD_GRACE_TIME 5
   SESSIONS_PER_USER 5
   IDLE_TIME 2;

这个sql设置适用于app_user限定的数据库用户，登陆失败尝试次数为5次，失败5次后账户锁定1天。密码有效期为90天，宽限期5天，也就是说超过90天不修改密码会在你下次登陆时给出警告提示，超过95天不修改密码当前用户密码将失效导致无法使用当前用户登录数据库。用户的会话并发数最大为5个，空闲连接超时时间为2分钟。你要知道alter语句既是修改，也是添加，也是删除的含义。当前设置如果app_user配置中没有定义过这两个参数，那么就等于新增；如果之前定义过这些参数，那么就是将参数修改为当前数值；如果不是具体数值而使用default则代表删除之前定义的值而使用default配置的值进行限定。

另外，要使以上配置生效别忘了执行另一条sql：ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

参考官网：ALTER PROFILE

八、修改动态配置

之前说到，静态参数修改相对来说比较简单明显，对于参数中的数字只要知道单位就能明显知道限定的范围，如：5次、90天、2分钟等。但是有时候这些固定参数太过生硬太过暴力，对于服务器来说可能达不到最大的优化性能，这个时候使用动态参数就能让数据库自己决定每个参数的限定范围从而达到更高效的利用服务器性能的目的，而我们需要做的就是配置每项参数的权重然后再限定一下资源消耗总阈值就行了。

这就是说我们设定一个资源消耗的计算公式，oracle根据我们设定的公式来动态分配每种资源的限制范围，最终结果是在资源消耗超过我们设定的总阈值时中止会话并返回错误信息。设置的前提当然也是需要有alter权限。对于总阈值的设定是在profile中使用COMPOSITE_LIMIT配置来限定，sql语句如：alter profile app_user limit COMPOSITE_LIMIT 5000000;即设置app_user限定的用户使用数据库总资源阈值不能超过500万个服务单位。

下面具体说说资源权重的配置，目前能够配置的资源有四种：CPU_PER_SESSION、LOGICAL_READS_PER_SESSION、CONNECT_TIME、PRIVATE_SGA。通过RESOURCE_COST表能够进行查询每种资源的权重配额，看着资源名称是不是很熟悉？这确实和静态配置很相似，两者可以共同限定会话对数据库资源的访问限制。

再来看个sql图：

sql几乎是非常固定的，每种参数的值都是整数类型。设置后对当前会话和之后新创建的会话均生效。oracle会将你设置的每项配置和其权重相乘，然后相加得出最后的总消耗成本，与前面设置的COMPOSITE_LIMIT配置进行比较，如果超过设定的阈值就会中断当前会话并返回错误信息。

对于每项配置的含义其实已经不需要多说，前面已经介绍过。这里说一下配置对应的值和之前的不一样，这里配置的值是所指配置的权重，而不是限定阈值。比如这里设置CUP_PER_SESSION为50并不是会话占用cpu的时间，而是计算总成本时计算会话占用cpu时间的计算权重为50，其他的自行理解。

你分配给每个资源的权重决定了该资源的使用对总资源成本的贡献，换句话说你分配的权重越大代表的就是你越重视该项资源的消耗程度，即该项资源可能存在性能短板，因此不能过多消耗。如果您没有为资源分配权重，那么权重默认为0，随后对资源的使用不会增加成本。

参考官网：ALTER RESOURCE COST