« 解决方案：Oracle的DB Link问题及2019年4月升级路线详述 | Blog首页 | Oracle全面修正了关于DB Link和SCN补丁的公告 »

Oracle 11g在补丁修正中引入DBMS_SCN包及新特性

DBMS_SCN 的Package是在 11.2.0.3.9 中引入，在Opatch应用补丁的过程中，可以观察到这个执行过程。

整个Package 由两个源码文件组成，分别是：dbmsscnc.sql 和 prvtscnc.plb 。后者是加密Wrap过的代码。

以下引用了补丁修正安装后的代码执行过程，作为参考：

$ opatch napply -oh $ORACLE_HOME -local /u01/app/oracle/patch/17540582
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home       : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2015-01-10_11-12-31上午_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17540582  

是否继续? [y|n]
y
User Responded with: Y
All checks passed.
请关闭本地系统上在此 ORACLE_HOME 之外运行的 Oracle 实例。
(Oracle 主目录 = '/u01/app/oracle/product/11.2.0.3/db_1')

本地系统是否已准备打补丁? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17540582' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
ApplySession: Oracle 主目录中不存在可选组件 [ oracle.precomp.lang, 11.2.0.3.0 ] , 或找到更高版本。


正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo, 11.2.0.3.0 打补丁...
正在为组件 oracle.ldap.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.precomp.common, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.client, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.util, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.dbscripts, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo.locator, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rman, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.jai, 11.2.0.3.0 打补丁...
Verifying the update...
Composite patch 17540582 successfully applie
查看更新的结果
$ opatch lspatches
17540582;Database Patch Set Update : 11.2.0.3.9 (17540582)
查看更新补丁的内容
$ opatch lsinventory
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home       : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2015-01-10_11-12-31上午_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-01-10_11-12-31上午_1.txt

--------------------------------------------------------------------------------
已安装的顶级产品 (1):
Oracle Database 11g                                                  11.2.0.3.0
此 Oracle 主目录中已安装 1 个产品。

中间补丁程序 (1) :

Patch  17540582     : applied on Thu Feb 20 11:33:06 CST 2014
Unique Patch ID:  16985511
Patch description:  "Database Patch Set Update : 11.2.0.3.9 (17540582)"
   Created on 7 Jan 2014, 03:01:22 hrs PST8PDT
Sub-patch  16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch  16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
   Bugs fixed:
     13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
     13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
     14409183, 13492735, 12857027, 13496884, 14263036, 14263073, 13015379
     16038929, 17748833, 16563678, 13732226, 13866822, 13742434, 13944971
     12950644, 17748831, 12899768, 13063120, 13958038, 14613900, 13972394
     11877623, 17088068, 13072654, 12395918, 13814739, 17343514, 13649031
     13981051, 12797765, 17333200, 12923168, 16761566, 16279401, 13384182
     13466801, 15996344, 14207163, 13724193, 13642044, 11063191, 13945708
     12797420, 12865902, 15869211, 13041324, 14003090, 16314468, 16019955
     11708510, 14637368, 13026410, 13737746, 13742438, 15841373, 16347904
     15910002, 16362358, 14398795, 13579992, 16344871, 10400244, 14275605
     13742436, 9858539, 14841812, 16338983, 9703627, 13483354, 14207317
     14393728, 12764337, 16902043, 14459552, 14191508, 12964067, 12780983
     12583611, 14383007, 14546575, 15862016, 13476583, 13489024, 17748830
     14088346, 13448206, 16314466, 13419660, 14110275, 13430938, 13467683
     14548763, 12834027, 13632809, 13377816, 13036331, 14727310, 16175381
     13584130, 12829021, 15862019, 12794305, 14546673, 12791981, 13787482
     13503598, 10133521, 12744759, 13399435, 13553883, 14023636, 14762511
     9095696, 14343501, 13860201, 13257247, 14176879, 16014985, 12312133
     14480675, 16306019, 13559697, 9706792, 12974860, 12940620, 13098318
     13773133, 15883525, 16794244, 13340388, 13366202, 13528551, 12894807
     12747437, 13454210, 12748240, 13385346, 15987992, 13923995, 13582702
     14571027, 12784406, 13907462, 13493847, 13857111, 13035804, 16710363
     13544396, 14128555, 8547978, 14226599, 17478415, 17333197, 9397635
     14007968, 12925089, 12693626, 14189694, 12815057, 17761775, 16721594
     13332439, 14038787, 11071989, 14207902, 14062796, 12913474, 14390252
     16314470, 13370330, 14062794, 13358781, 17333202, 12960925, 9659614
     14546638, 13699124, 13936424, 9797851, 14301592, 16794240, 13338048
     12938841, 12620823, 12656535, 12678920, 14488943, 16850197, 14791477
     14062792, 13807411, 16794238, 15862022, 12594032, 13250244, 9761357
     12612118, 14053457, 13527323, 10625145, 15862020, 13910420, 12780098
     13696216, 10263668, 14841558, 16794242, 16944698, 15862023, 16056266
     13834065, 14351566, 13723052, 13011409, 14063280, 13566938, 13737888
     13624984, 16024441, 17333199, 13914613, 17540582, 14258925, 14222403
     14755945, 13645875, 12571991, 14664355, 12998795, 13719081, 14469008
     14188650, 17019974, 13742433, 16368108, 16314469, 12905058, 6690853
     16212405, 12849688, 13742435, 13464002, 13534412, 12879027, 12585543
     13790109, 12535346, 16382448, 12588744, 13916549, 13786142, 12847466
     13855490, 13551402, 12582664, 14262913, 17332800, 14695377, 12912137
     13612575, 13484963, 14163397, 17437634, 13772618, 16694777, 13070939
     14369664, 12391034, 13605839, 16314467, 16279211, 12976376, 12755231
     13680405, 14589750, 13742437, 14318397, 11868640, 14644185, 13326736
     13596521, 13001379, 12898558, 17752121, 13099577, 9873405, 16372203
     16344758, 11715084, 16231699, 9547706, 14040433, 12662040, 12617123
     17748832, 16530565, 12845115, 16844086, 17748834, 13354082, 13397104
     13913630, 16462834, 12983611, 13550185, 13810393, 14121009, 13065099
     11840910, 13903046, 15862017, 13572659, 16294378, 13718279, 13657605
     14480676, 13632717, 14668670, 14063281, 13420224, 13812031, 16299830
     12646784, 14512189, 12755116, 13616375, 17230530, 14035825, 13427062
     12861463, 13092220, 15862021, 13043012, 16619892, 13685544, 15862018
     13499128, 13561750, 12718090, 13848402, 13725395, 12401111, 12796518
     13362079, 12917230, 13042639, 13923374, 14220725, 12621588, 13524899
     14751895, 14480674, 13916709, 14076523, 15905421, 12731940, 13343438
     14205448, 17748835, 14127231, 17082364, 15853081, 14273397, 16844448
     14467061, 12971775, 16864562, 14497307, 12748538, 10242202, 14230270
     16382353, 13686047, 14095982, 17333203, 13591624, 14523004, 13440516
     16794241, 14062795, 13035360, 13040943, 13843646, 16794243, 14841409
     13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
     16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
     14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
     13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404
--------------------------------------------------------------------------------
OPatch succeeded.
数据库启动，并加载修改SQL Files到数据库
enmo@oracle> @?/rdbms/admin/catbundle.sql psu apply

PL/SQL procedure successfully completed.

Function created.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ENMO_GENERATE_2014Feb20_11_42_25.log
Apply script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_ROLLBACK.sql


PL/SQL procedure successfully completed.


Executing script file...


enmo@oracle> COLUMN spool_file NEW_VALUE spool_file NOPRINT
enmo@oracle> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name 
|| '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''')
|| '.log' AS spool_file FROM v$database;


enmo@oracle> SPOOL &spool_file
enmo@oracle> exec sys.dbms_registry.set_session_namespace('SERVER')


PL/SQL procedure successfully completed.


enmo@oracle> PROMPT Skipping EM Repository because it is not installed or versions mismatch...
Skipping EM Repository because it is not installed or versions mismatch...
enmo@oracle> PROMPT Processing Oracle Database Packages and Types...
Processing Oracle Database Packages and Types...
enmo@oracle> ALTER SESSION SET current_schema = sys;


Session altered.


enmo@oracle> @?/rdbms/admin/dbmsscnc.sql
enmo@oracle> Rem
enmo@oracle> Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0.3.0dbpsu/1 2013/11/06 04:17:31 mtiwary Exp $
enmo@oracle> Rem
enmo@oracle> Rem dbmsscn.sql
enmo@oracle> Rem
enmo@oracle> Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates.
enmo@oracle> Rem All rights reserved.
enmo@oracle> Rem
enmo@oracle> Rem    NAME
enmo@oracle> Rem      dbmsscnc.sql - dbms_scn package definition
enmo@oracle> Rem
enmo@oracle> Rem    DESCRIPTION
enmo@oracle> Rem      
enmo@oracle> Rem
enmo@oracle> Rem    NOTES
enmo@oracle> Rem      
enmo@oracle> Rem
enmo@oracle> Rem    MODIFIED   (MM/DD/YY)
enmo@oracle> Rem    mtiwary     05/26/12 - Declarations and definitions related to DBMS_SCN
enmo@oracle> Rem                           package.
enmo@oracle> Rem    mtiwary     05/26/12 - Created
enmo@oracle> Rem
enmo@oracle> 
enmo@oracle> Rem
enmo@oracle> Rem    BEGIN SQL_FILE_METADATA
enmo@oracle> Rem    SQL_SOURCE_FILE: rdbms/admin/dbmsscn.sql
enmo@oracle> Rem    SQL_SHIPPED_FILE:
enmo@oracle> Rem    SQL_PHASE:
enmo@oracle> Rem    SQL_STARTUP_MODE: NORMAL
enmo@oracle> Rem    SQL_IGNORABLE_ERRORS: NONE
enmo@oracle> Rem    SQL_CALLING_FILE:
enmo@oracle> Rem    END SQL_FILE_METADATA
enmo@oracle> 
enmo@oracle> SET ECHO ON
enmo@oracle> SET FEEDBACK 1
enmo@oracle> SET NUMWIDTH 10
enmo@oracle> SET LINESIZE 80
enmo@oracle> SET TRIMSPOOL ON
enmo@oracle> SET TAB OFF
enmo@oracle> SET PAGESIZE 100
enmo@oracle> 
enmo@oracle> CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;
  2  /


Library created.


enmo@oracle> 
enmo@oracle> CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS
  2  
  3  DBMS_SCN_API_MAJOR_VERSION  CONSTANT NUMBER := 1;
  4  DBMS_SCN_API_MINOR_VERSION  CONSTANT NUMBER := 0;
  5  
  6  PROCEDURE GetCurrentSCNParams(
  7                  rsl      OUT number,
  8                  headroom_in_scn OUT number,
  9                  headroom_in_sec OUT number,
 10                  cur_scn_compat OUT number,
 11                  max_scn_compat OUT number);
 12  
 13  --      Currently no exceptions are thrown.
 14  --      rsl             - Reasonable SCN Limit as of 'now'
 15  --      headroom_in_scn - Difference between current SCN and RSL
 16  --      headroom_in_sec - number of seconds it would take to reach RSL
 17  --                        assuming a constant SCN consumption rate associated
 18  --                        with current SCN compatibility level
 19  --      cur_scn_compat  - current value of SCN compatibility
 20  --      max_scn_compat  - max value of SCN compatibility this database
 21  --                        understands
 22  
 23  FUNCTION GetSCNParamsByCompat(
 24                  compat IN number,
 25                  rsl           OUT number,
 26                  headroom_in_scn OUT number,
 27                  headroom_in_sec OUT number
 28           ) RETURN boolean;
 29  
 30  --     compat           -- SCN compatibility value
 31  --     rsl              -- Reasonable SCN Limit
 32  --     headroom_in_scn  -- Difference between current SCN and RSL
 33  --     headroom_in_sec  -- number of seconds it would take to reach RSL
 34  --                         assuming a constant SCN consumption rate associated
 35  --                         with specified database SCN compatibility
 36  --
 37  --     Returns False if 'compat' parameter value is invalid, and OUT parameters
 38  --     are not updated.
 39  
 40  PROCEDURE GetSCNAutoRolloverParams(
 41                  effective_auto_rollover_ts OUT DATE,
 42                  target_compat OUT number,
 43                  is_enabled OUT boolean);
 44  
 45  --      effective_auto_rollover_ts  - timestamp at which rollover becomes
 46  --                                    effective
 47  --      target_compat               - SCN compatibility value this database
 48  --                                    will move to, as a result of
 49  --                                    auto-rollover
 50  --      is_enabled                  - TRUE if auto-rollover feature is
 51  --                                    currently enabled
 52  
 53  PROCEDURE EnableAutoRollover;
 54  
 55  PROCEDURE DisableAutoRollover;
 56  
 57  END DBMS_SCN;
 58  /


Package created.


enmo@oracle> 
enmo@oracle> @?/rdbms/admin/prvtscnc.plb
enmo@oracle> SET ECHO ON
enmo@oracle> SET FEEDBACK 1
enmo@oracle> SET NUMWIDTH 10
enmo@oracle> SET LINESIZE 80
enmo@oracle> SET TRIMSPOOL ON
enmo@oracle> SET TAB OFF
enmo@oracle> SET PAGESIZE 100
enmo@oracle> CREATE OR REPLACE PACKAGE BODY DBMS_SCN wrapped
  2  a000000
  3  1
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  b
 20  6c0 243
 21  QlmAiY1dAl0ShRRHlX+HGNAfF7Mwgw23ACAVfC9A2k7VVhtmMilHXbSA4+y0szHoAcIlGGvF
 22  LFznjZK7HsiO4405ad7otP6DvBJPmF/CgKv7vWxPthzol8UbWtg5Rsh0bB1IL1o27IiiL4Pp
 23  ghghXIzy7qpN8ZKAqy5GoYTd+NFVjhaAPl79bXMSsYU3kLeYwwq6YrfeYIGtMvJPmD01eYTm
 24  6ZHFbXW65+zhiLyd4n6gFjHiFm8ewsIUlps9n1Qmhi8+HDugSGp5JJUj8nWOq0ENurliNrJN
 25  hU0xgcfHK5K6QfbtOHA/U80YLHmYL19b0SJ/rClUGJ61NxJXZGyQ5KEL4FaSdiRh+mztwHkD
 26  0vUMuhwvNnlpUxmcvWlSy/43x86V3wrQNDQ+u0hWeLus6JG2IndfBYS5uYxgDImhZhepALfL
 27  t71Ti3U3O8u0T7YrCu/D3Cr1ZiWOVQsf/xfYVuerG93+lzkruPtiRdV4U5PReE9tBiwb0r+Z
 28  zwEKhyQwCZo3l/PypHsCJbpX2E6cQwagpSSNihdqCzJce+R5Ek7PZ6VqrwhVeOL4icI=
 29  
 30  /


Package body created.


enmo@oracle> CREATE OR REPLACE PUBLIC SYNONYM dbms_scn FOR sys.dbms_scn;


Synonym created.


enmo@oracle> /


Synonym created.


enmo@oracle> GRANT EXECUTE ON dbms_scn TO PUBLIC;


Grant succeeded.


enmo@oracle> /


Grant succeeded.


enmo@oracle> PROMPT Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
enmo@oracle> PROMPT Skipping Oracle interMedia because it is not installed or versions mismatch...
Skipping Oracle interMedia because it is not installed or versions mismatch...
enmo@oracle> PROMPT Skipping Spatial because it is not installed or versions mismatch...
Skipping Spatial because it is not installed or versions mismatch...
enmo@oracle> ALTER SESSION SET current_schema = SYS;


Session altered.


enmo@oracle> PROMPT Updating registry...
Updating registry...
enmo@oracle> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '11.2.0.3',
  9     9,
 10     'PSU',
 11     'PSU 11.2.0.3.9');


1 row created.


enmo@oracle> COMMIT;


Commit complete.

By eygle on 2018-03-18 10:56 | Comments (0) | Oracle12c/11g | 3281 |

SCN