單節點oracle升級10.2.0.1.0-10.2.0.4.0

哎呀我的天吶發表於2014-10-30
Oracle

Oracle版本升級介紹

例如：oracle9.0.1.1.2

9：版本號

0：新特性版本號

1（第一個）：維護版本號

1（第二個）：普通的補丁設定號碼

2：特殊的平臺補丁設定號碼

Oracle 的版本號很多，先看 11g 的一個版本號說明：

注意： 在 oracle 9.2 版本之後， oracle 的 maintenance release number 是在第二數字位更改。 而在之前，是在第三個數字位。
1. Major Database Release Number
 1. Major Database Release Number
第一個數字位，它代表的是一個新版本軟體，也標誌著一些新的功能。如 11g， 10g。
2. Database Maintenance Release Number
 2. Database Maintenance Release Number
第二個數字位，代表一個 maintenance release 級別，也可能包含一些新的特性。
3. Fusion Middleware Release Number
 3. Fusion Middleware Release Number（很少變）
第三個數字位，反應 Oracle 中介軟體（Oracle Fusion Middleware）的版本號。
4. Component-Specific Release Number
 4. Component-Specific Release Number
第四個數字位，主要是針對元件的釋出級別。不同的元件具有不同的號碼。 比如 Oracle 的
patch 包。
第五個數字位，這個數字位標識一個平臺的版本。 通常表示 patch 號。
Upgrade
 Upgrade 與 Update
首先，我們針對所使用的資料庫可能會進行如下措施，版本升級或補丁包升級，那何為版本升級、何為補丁包升級呢？
比如我的當前資料庫是 10G R2 版本，但公司最近有個升級計劃，把這套資料庫升級到當下最新的 11G R2，這種大版本間升級動作即為 Upgrade。根據公司計劃在原廠工程師和 DBA 共同努力下，資料庫已升級到 11G R2，當下版本為 11.2.0.3.0。這時候原廠工程師推薦把最新的 PSU 給打上，獲得老闆的批准之後，我們又把資料庫進行補丁包的升級，應用了 PSU Patch 14727310 之後，資料庫版本現在成為 11.2.0.3.5，這個過程即是 Update。
1. 什麼是 PSU/CPU？
CPU: Critical Patch Update
Oracle 對於其產品每個季度發行一次的安全補丁包，通常是為了修復產品中的安全隱患。
PSU: Patch Set Updates需要專門的升級工具Opath升級
Oracle 對於其產品每個季度發行一次的補丁包，包含了 bug 的修復。 Oracle 選取被使用者下載數量多的，並且被驗證
過具有較低風險的補丁放入到每個季度的 PSU 中。在每個 PSU 中不但包含 Bug 的修復而且還包含了最新的 CPU。

不得不再次提醒， Upgrade 和 Update 都希望在獲得原廠的支援下進行，尤其是 Upgrade，這對於企業來說是個非常大的動作！


10.2.0.1.0--10.2.0.4.0

CPU Update步驟

檢查當前版本


點選(此處)摺疊或開啟

  1. SQL> select * from v$version;
  2.  
  3. BANNER
  4. ----------------------------------------------------------------
  5. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
  6. PL/SQL Release 10.2.0.1.0 - Production
  7. CORE 10.2.0.1.0 Production
  8. TNS for Linux: Version 10.2.0.1.0 - Production
  9. NLSRTL Version 10.2.0.1.0 – Production

檢查磁碟空間（system必須要少於10m）

... ...

調整記憶體引數（share pool和java pool加快升級速度）

調整sga大小【沒做】

點選(此處)摺疊或開啟

  1. SQL> alter system set sga_max_size = 1300M scope=spfile;
  2.  
  3. System altered.
  4.  
  5. SQL> alter system set sga_target = 1000m scope=spfile;
  6.  
  7. System altered.

調整共享池的大小【沒做】

點選(此處)摺疊或開啟

  1. SQL> show parameter shared
  2.  
  3. NAME TYPE VALUE
  4. ------------------------------ -------------- ------------------------
  5. hi_shared_memory_address integer 0
  6. max_shared_servers integer
  7. shared_memory_address integer 0
  8. shared_pool_reserved_size big integer 8178892
  9. shared_pool_size big integer 0
  10. shared_server_sessions integer
  11. shared_servers integer 1
  12.  
  13. SQL> alter system set shared_pool_size=200m scope=spfile;
  14.  
  15. System altered.

調整java池的大小【沒做】

點選(此處)摺疊或開啟

  1. SQL> show parameter java
  2.  
  3. NAME TYPE VALUE
  4. -------------------------- ------------------ -------------
  5. java_max_sessionspace_size integer 0
  6. java_pool_size big integer 300M
  7. java_soft_sessionspace_limit integer 0
  8.  
  9. SQL> alter system set java_pool_size=300M scope=spfile;
  10.  
  11. System altered.
  12.  
  13. SQL> startup force nomount;
  14. ORACLE instance started.
  15.  
  16. Total System Global Area 1375731712 bytes
  17. Fixed Size 2020704 bytes
  18. Variable Size 872417952 bytes
  19. Database Buffers 486539264 bytes
  20. Redo Buffers 14753792 bytes
  21. SQL> show parameter sga
  22.  
  23. NAME TYPE VALUE
  24. ------------------------ ------------------------ -------
  25. lock_sga boolean FALSE
  26. pre_page_sga boolean FALSE
  27. sga_max_size big integer 1312M
  28. sga_target big integer 1008M
  29.  
  30. SQL> show parameter shared
  31. NAME TYPE VALUE
  32. ---------------------------- ------------------- -----------------
  33. shared_pool_size big integer 208M
  34.  
  35. SQL> show parameter java
  36.  
  37. NAME TYPE VALUE
  38. --------------------------- ------------------------- -----------
  39. java_pool_size big integer 304M
  40.  
  41.  
  42. SQL> show parameter shared
  43.  
  44. NAME TYPE VALUE
  45. ------------------------------------ --------------------------------- ------------------------------
  46. hi_shared_memory_address integer 0
  47. max_shared_servers integer
  48. shared_memory_address integer 0
  49. shared_pool_reserved_size big integer 10905190
  50. shared_pool_size big integer 208M
  51. shared_server_sessions integer
  52. shared_servers integer 1
  53. SQL> show parameter disp
  54.  
  55. NAME TYPE VALUE
  56. --------------------------- -------------------- ----------------
  57. dispatchers string (PROTOCOL=TCP) (SERVICE=prodXDB) max_dispatchers integer
  58.  
  59. SQL> alter system set dispatchers=\'\';
  60.  
  61. System altered.
  62.  
  63. SQL> alter system set shared_servers= 0;
  64.  
  65. System altered.

關閉監聽 em

點選(此處)摺疊或開啟

  1. [oracle@test Disk1]$emctl stop dbconsole
  2. [oracle@test Disk1]$isqlplusctl stop
  3. [oracle@test Disk1]$lsnrctl stop
  4.  
  5. [oracle@test Disk1]$ netstat -an | grep 1521
  6. [oracle@test Disk1]$ netstat -an | grep 1158
  7.  
  8. LSNRCTL> status
  9. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
  10. TNS-12541: TNS:no listener
  11.  TNS-12560: TNS:protocol adapter error
  12.   TNS-00511: No listener
  13.    Linux Error: 2: No such file or directory
  14. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bwst)(PORT=1521)))
  15. TNS-12541: TNS:no listener
  16.  TNS-12560: TNS:protocol adapter error
  17.   TNS-00511: No listener
  18.    Linux Error: 111: Connection refused

為資料庫做冷備份

… …

關閉資料庫例項及其相關程式

點選(此處)摺疊或開啟

  1. SQL> shutdown immediate
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5.  
  6. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
  7. With the Partitioning, OLAP and Data Mining options
  8. [oracle@test ~]$ ps -ef | grep ora_
  9. oracle 30516 27389 0 17:17 pts/2 00:00:00 grep ora_

將補丁包上傳到oracle server，解壓、安裝

… …

以圖形介面進入oracle server

點選(此處)摺疊或開啟

  1. [root@test ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh
  2. Running Oracle10 root.sh script...
  3.  
  4. The following environment variables are set as:
  5.     ORACLE_OWNER= oracle
  6.     ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1
  7.  
  8. Enter the full pathname of the local bin directory: [/usr/local/bin]:
  9. The file \"dbhome\" already exists in /usr/local/bin. Overwrite it? (y/n)
  10. [n]: y
  11.    Copying dbhome to /usr/local/bin ...
  12. The file \"oraenv\" already exists in /usr/local/bin. Overwrite it? (y/n)
  13. [n]: y
  14.    Copying oraenv to /usr/local/bin ...
  15. The file \"coraenv\" already exists in /usr/local/bin. Overwrite it? (y/n)
  16. [n]: y
  17.    Copying coraenv to /usr/local/bin ...
  18.  
  19. Entries will be added to the /etc/oratab file as needed by
  20. Database Configuration Assistant when a database is created
  21. Finished running generic part of root.sh script.
  22. Now product-specific root actions will be performed.

升級完成後以startup upgrade 方式開啟庫，升級資料字典

點選(此處)摺疊或開啟

  1. [oracle@test Disk1]$ sqlplus / as sysdba
  2.  
  3. SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 29 20:20:28 2014
  4.  
  5. Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
  6.  
  7. Connected to an idle instance.
  8.  
  9. SQL> startup upgrade;
  10. ORACLE instance started.
  11.  
  12. Total System Global Area 599785472 bytes
  13. Fixed Size 2085776 bytes
  14. Variable Size 163581040 bytes
  15. Database Buffers 427819008 bytes
  16. Redo Buffers 6299648 bytes
  17. Database mounted.
  18. Database opened.

將生成的日誌spool到一個檔案中。

點選(此處)摺疊或開啟

  1. SQL> spool /home/oracle/cpu_up.log
  2. SQL> @?/rdbms/admin/catupgrd.sql
  3. SQL> values (\'db_database_instance_overview\', \'en\', \'Database Instance Overview\');
  4. SQL> insert into MGMT_IP_REPORT_NLS_MAP (NLSID, LOCALE, NLS_STRING)
  5. SQL> values (\'db_database_and_instance_info\', \'en\', \'Database and Instance Information\');
  6. SQL> insert into MGMT_IP_REPORT_NLS_MAP (NLSID, LOCALE, NLS_STRING)
  7. SQL> values (\'db_sga_info\', \'en\', \'SGA Information\');
  8. SQL> insert into MGMT_IP_REPORT_NLS_MAP (NLSID, LOCALE, NLS_STRING)
  9. SQL> values (\'db_non_default_init_params\', \'en\', \'Non-Default Initialization Parameters\');
  10. SQL> END load_nls_strings;
  11. SQL>
  12. SQL>
  13. SQL> END mgmt_database_overview;
  14. SQL> /
  15. SQL>
  16. SQL> show errors;
  17. SQL> */
  18. SQL>
  19. SQL>
  20. SQL>
  21. SQL> --
  22. SQL> -- Recreate iAS procs (if EM_REPOS_MODE is CENTRAL)
  23. SQL> COLUMN :script_name NEW_VALUE ias_file NOPRINT
  24. SQL> VARIABLE script_name VARCHAR2(256)
  25. SQL> BEGIN
  26.   2 IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  27.   3 :script_name := \'&EM_SQL_ROOT/ias/ias_procs.sql\';
  28.   4 ELSE
  29.   5 :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  30.   6 END IF;
  31.   7 END;
  32.   8 /
  33. old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  34. new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
  35. old 3: :script_name := \'&EM_SQL_ROOT/ias/ias_procs.sql\';
  36. new 3: :script_name := \'?/sysman/admin/emdrep/sql/ias/ias_procs.sql\';
  37. old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  38. new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  39.  
  40. PL/SQL procedure successfully completed.
  41.  
  42. SQL>
  43. SQL> SELECT :script_name FROM DUAL;
  44.  
  45.  
  46.  
  47.  
  48. 1 row selected.
  49.  
  50. SQL>
  51. SQL> @&ias_file
  52. SQL> Rem
  53. SQL> Rem $Header: admin_do_nothing.sql 23-apr-2004.17:05:02 rpinnama Exp $
  54. SQL> Rem
  55. SQL> Rem admin_do_nothing.sql
  56. SQL> Rem
  57. SQL> Rem Copyright (c) 2004, Oracle Corporation. All rights reserved.
  58. SQL> Rem
  59. SQL> Rem NAME
  60. SQL> Rem admin_do_nothing.sql - <one-line expansion of the name>
  61. SQL> Rem
  62. SQL> Rem DESCRIPTION
  63. SQL> Rem <short description of component this file declares/defines>
  64. SQL> Rem
  65. SQL> Rem NOTES
  66. SQL> Rem <other useful comments, qualifications, etc.>
  67. SQL> Rem
  68. SQL> Rem MODIFIED (MM/DD/YY)
  69. SQL> Rem rpinnama 04/23/04 - rpinnama_backport_10.1.0.2.0_3535007
  70. SQL> Rem rpinnama 04/15/04 - Created
  71. SQL> Rem
  72. SQL>
  73. SQL>
  74. SQL>
  75. SQL> --
  76. SQL> -- Recreate OCS procs (if EM_REPOS_MODE is CENTRAL)
  77. SQL> COLUMN :script_name NEW_VALUE ocs_file NOPRINT
  78. SQL> VARIABLE script_name VARCHAR2(256)
  79. SQL> BEGIN
  80.   2 IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  81.   3 :script_name := \'&EM_SQL_ROOT/ocs/ocs_procs.sql\';
  82.   4 ELSE
  83.   5 :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  84.   6 END IF;
  85.   7 END;
  86.   8 /
  87. old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  88. new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
  89. old 3: :script_name := \'&EM_SQL_ROOT/ocs/ocs_procs.sql\';
  90. new 3: :script_name := \'?/sysman/admin/emdrep/sql/ocs/ocs_procs.sql\';
  91. old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  92. new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  93.  
  94. PL/SQL procedure successfully completed.
  95.  
  96. SQL>
  97. SQL> SELECT :script_name FROM DUAL;
  98.  
  99.  
  100.  
  101.  
  102. 1 row selected.
  103.  
  104. SQL>
  105. SQL> @&ocs_file
  106. SQL> Rem
  107. SQL> Rem $Header: admin_do_nothing.sql 23-apr-2004.17:05:02 rpinnama Exp $
  108. SQL> Rem
  109. SQL> Rem admin_do_nothing.sql
  110. SQL> Rem
  111. SQL> Rem Copyright (c) 2004, Oracle Corporation. All rights reserved.
  112. SQL> Rem
  113. SQL> Rem NAME
  114. SQL> Rem admin_do_nothing.sql - <one-line expansion of the name>
  115. SQL> Rem
  116. SQL> Rem DESCRIPTION
  117. SQL> Rem <short description of component this file declares/defines>
  118. SQL> Rem
  119. SQL> Rem NOTES
  120. SQL> Rem <other useful comments, qualifications, etc.>
  121. SQL> Rem
  122. SQL> Rem MODIFIED (MM/DD/YY)
  123. SQL> Rem rpinnama 04/23/04 - rpinnama_backport_10.1.0.2.0_3535007
  124. SQL> Rem rpinnama 04/15/04 - Created
  125. SQL> Rem
  126. SQL>
  127. SQL>
  128. SQL>
  129. SQL>
  130. SQL> --
  131. SQL> -- NOTE : External Component integrators should integrate their proc scripts here
  132. SQL> -- Integrators should check the repository mode EM_REPOS_MODE and decide
  133. SQL> -- whether to execute their component scripts or not
  134. SQL> --
  135. SQL>
  136. SQL>
  137. SQL>
  138. SQL> -- Recompile all the schema objects
  139. SQL> @&EM_SQL_ROOT/core/latest/admin/admin_recompile_invalid.sql &&EM_REPOS_USER
  140. SQL> Rem
  141. SQL> Rem $Header: admin_recompile_invalid.sql 27-feb-2003.17:05:37 rpinnama Exp $
  142. SQL> Rem
  143. SQL> Rem admin_recompile_invalid.sql
  144. SQL> Rem
  145. SQL> Rem Copyright (c) 2002, 2003, Oracle Corporation. All rights reserved.
  146. SQL> Rem
  147. SQL> Rem NAME
  148. SQL> Rem admin_recompile_invalid.sql - <one-line expansion of the name>
  149. SQL> Rem
  150. SQL> Rem DESCRIPTION
  151. SQL> Rem <short description of component this file declares/defines>
  152. SQL> Rem
  153. SQL> Rem NOTES
  154. SQL> Rem <other useful comments, qualifications, etc.>
  155. SQL> Rem
  156. SQL> Rem MODIFIED (MM/DD/YY)
  157. SQL> Rem rpinnama 02/27/03 - Parameterize the user name
  158. SQL> Rem rpinnama 09/26/02 - Remove exit at the end
  159. SQL> Rem rpinnama 05/16/02 - rpinnama_reorg_rep_scripts_2
  160. SQL> Rem rpinnama 05/16/02 - Created
  161. SQL> Rem
  162. SQL>
  163. SQL> Rem
  164. SQL> Rem This SQL script requires the following arguments
  165. SQL> Rem 1. Name of the repository user
  166. SQL> Rem
  167. SQL> DEFINE EM_REPOS_USER =\"&1\"
  168. SQL>
  169. SQL> DECLARE
  170.   2 BEGIN
  171.   3
  172.   4 FOR crec IN (SELECT object_name, object_type,
  173.   5 DECODE(object_type, \'FUNCTION\', 1,
  174.   6 \'PROCEDURE\', 2,
  175.   7 \'TRIGGER\', 3,
  176.   8 \'VIEW\', 4,
  177.   9 \'PACKAGE\', 5,
  178.  10 \'PACKAGE BODY\', 6,
  179.  11 9) object_type_seq
  180.  12 FROM all_objects
  181.  13 WHERE owner =\'&EM_REPOS_USER\'
  182.  14 AND status =\'INVALID\'
  183.  15 AND object_type IN (\'FUNCTION\', \'PACKAGE\', \'PACKAGE BODY\', \'PROCEDURE\',
  184.  16 \'TRIGGER\', \'VIEW\')
  185.  17 ORDER BY object_type_seq, object_type, created)
  186.  18 LOOP
  187.  19 BEGIN
  188.  20 DBMS_OUTPUT.PUT_LINE(\'CreateRep recompiling invalid \' ||
  189.  21 crec.object_type || \' \' || crec.object_name);
  190.  22
  191.  23 IF (crec.object_type = \'PACKAGE BODY\') THEN
  192.  24
  193.  25 -- If package body is invalid, just compile the body and not
  194.  26 -- the specification
  195.  27 EXECUTE IMMEDIATE \'ALTER PACKAGE \' ||
  196.  28 crec.object_name || \' compile body\';
  197.  29 ELSE
  198.  30 EXECUTE IMMEDIATE \'ALTER \' || crec.object_type || \' \' ||
  199.  31 crec.object_name || \' compile\';
  200.  32 END IF;
  201.  33 EXCEPTION
  202.  34 WHEN OTHERS THEN
  203.  35 DBMS_OUTPUT.PUT_LINE(\'CreateRep failed - Unable to compile \' ||
  204.  36 crec.object_name );
  205.  37
  206.  38 RAISE;
  207.  39 END;
  208.  40 END LOOP;
  209.  41
  210.  42 END;
  211.  43 /
  212. old 13: WHERE owner =\'&EM_REPOS_USER\'
  213. new 13: WHERE owner =\'SYSMAN\'
  214.  
  215. PL/SQL procedure successfully completed.
  216.  
  217. SQL>
  218. SQL>
  219. SQL>
  220. SQL>
  221. SQL> --
  222. SQL> -- Upgrade CORE data
  223. SQL> @&EM_SQL_ROOT/core/core_data_upgrade.sql
  224. SQL> Rem
  225. SQL> Rem $Header: core_data_upgrade.sql 13-jul-2006.13:50:09 sadattaw Exp $
  226. SQL> Rem
  227. SQL> Rem core_data_upgrade.sql
  228. SQL> Rem
  229. SQL> Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.
  230. SQL> Rem
  231. SQL> Rem NAME
  232. SQL> Rem core_data_upgrade.sql - <one-line expansion of the name>
  233. SQL> Rem
  234. SQL> Rem DESCRIPTION
  235. SQL> Rem <short description of component this file declares/defines>
  236. SQL> Rem
  237. SQL> Rem NOTES
  238. SQL> Rem <other useful comments, qualifications, etc.>
  239. SQL> Rem
  240. SQL> Rem MODIFIED (MM/DD/YY)
  241. SQL> Rem sadattaw 07/13/06 - add condition for 10.1.0.6.0
  242. SQL> Rem rkpandey 07/17/06 - Add 10.1.0.6 support
  243. SQL> Rem jsadras 08/08/05 - add 10.1.0.5 support
  244. SQL> Rem rpinnama 09/21/04 - Support upgrade to 10.1.0.4.0
  245. SQL> Rem rpinnama 04/23/04 - rpinnama_backport_10.1.0.2.0_3535007
  246. SQL> Rem rpinnama 04/15/04 - Created
  247. SQL> Rem
  248. SQL>
  249. SQL> Rem Select upgrade script to run
  250. SQL> Rem
  251. SQL>
  252. SQL> COLUMN :script_name NEW_VALUE data_upgrade_file NOPRINT
  253. SQL> VARIABLE script_name VARCHAR2(256)
  254. SQL>
  255. SQL> DECLARE
  256.   2 l_core_ver VARCHAR2(16);
  257.   3 BEGIN
  258.   4 BEGIN
  259.   5 SELECT version INTO l_core_ver
  260.   6 FROM MGMT_VERSIONS
  261.   7 WHERE component_name = \'CORE\';
  262.   8 EXCEPTION
  263.   9 WHEN NO_DATA_FOUND THEN
  264.  10 l_core_ver := \'10.1.0.2.0\';
  265.  11 END;
  266.  12
  267.  13 IF (substr(l_core_ver, 1, 8) = \'10.1.0.2\') THEN
  268.  14 -- Upgrading from 10.1.0.2
  269.  15 :script_name := \'&EM_SQL_ROOT/core/v10102/core_data_upgrade.sql\';
  270.  16 ELSIF (substr(l_core_ver, 1, 8) = \'10.1.0.3\') THEN
  271.  17 -- Upgrading from 10.1.0.3
  272.  18 :script_name := \'&EM_SQL_ROOT/core/v101040/core_data_upgrade.sql\';
  273.  19 ELSIF (substr(l_core_ver, 1, 8) = \'10.1.0.4\') THEN
  274.  20 -- Upgrading from 10.1.0.4
  275.  21 :script_name := \'&EM_SQL_ROOT/core/v101050/core_data_upgrade.sql\';
  276.  22 ELSIF (substr(l_core_ver, 1, 8) = \'10.1.0.5\') THEN
  277.  23 -- Upgrading from 10.1.0.5
  278.  24 :script_name := \'&EM_SQL_ROOT/core/v101060/core_data_upgrade.sql\';
  279.  25 ELSE
  280.  26 :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  281.  27 END IF;
  282.  28 END;
  283.  29 /
  284. old 15: :script_name := \'&EM_SQL_ROOT/core/v10102/core_data_upgrade.sql\';
  285. new 15: :script_name := \'?/sysman/admin/emdrep/sql/core/v10102/core_data_upgrade.sql\';
  286. old 18: :script_name := \'&EM_SQL_ROOT/core/v101040/core_data_upgrade.sql\';
  287. new 18: :script_name := \'?/sysman/admin/emdrep/sql/core/v101040/core_data_upgrade.sql\';
  288. old 21: :script_name := \'&EM_SQL_ROOT/core/v101050/core_data_upgrade.sql\';
  289. new 21: :script_name := \'?/sysman/admin/emdrep/sql/core/v101050/core_data_upgrade.sql\';
  290. old 24: :script_name := \'&EM_SQL_ROOT/core/v101060/core_data_upgrade.sql\';
  291. new 24: :script_name := \'?/sysman/admin/emdrep/sql/core/v101060/core_data_upgrade.sql\';
  292. old 26: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  293. new 26: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  294.  
  295. PL/SQL procedure successfully completed.
  296.  
  297. SQL>
  298. SQL> SELECT :script_name FROM DUAL;
  299.  
  300.  
  301.  
  302.  
  303. 1 row selected.
  304.  
  305. SQL> (
  306.   2 @&data_upgrade_file
  307.   2 Rem
  308.   3 Rem $Header: core_data_upgrade.sql 13-jul-2006.13:52:19 sadattaw Exp $
  309.   4 Rem
  310.   5 Rem core_data_upgrade.sql
  311.   6 Rem
  312.   7 Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.
  313.   8 Rem
  314.   9 Rem NAME
  315.  10 Rem core_data_upgrade.sql - <one-line expansion of the name>
  316.  11 Rem
  317.  12 Rem DESCRIPTION
  318.  13 Rem <short description of component this file declares/defines>
  319.  14 Rem
  320.  15 Rem NOTES
  321.  16 Rem <other useful comments, qualifications, etc.>
  322.  17 Rem
  323.  18 Rem MODIFIED (MM/DD/YY)
  324.  19 Rem sadattaw 07/13/06 - add chaining cond for 10.1.0.6.0
  325.  20 Rem rkpandey 07/17/06 - Chain to 101060
  326.  21 Rem andyao 02/23/06 - add website 3.0 response/status metric so
  327.  22 Rem 10.1.0.5 OMS can work with 10.2 agent
  328.  23 Rem spahuja 11/21/05 - calling core_jobTypes for upgrade
  329.  24 Rem rpinnama 08/30/05 - Add basic data upgrade
  330.  25 Rem pmodi 08/29/05 - Comment out target_data_upgrade
  331.  26 Rem dcawley 08/19/05 - Include user model
  332.  27 Rem shianand 08/16/05 -
  333.  28 Rem rpinnama 06/27/05 - Created
  334.  29 Rem neearora 08/10/05 - Backport of bug 4500972. added call for
  335.  30 Rem core_post_creation.sql
  336.  31 Rem jsadras 08/08/05 - jsadras_backport_10.1.0.5.0_4520348
  337.  32 Rem jsadras 08/08/05 - Created
  338.  33 Rem
  339.  34
  340. SQL> -- core_post_creation should be the on top always.
  341. SQL> @&EM_SQL_ROOT/core/v101050/basic/basic_data_upgrade.sql
  342. SQL> Rem
  343. SQL> Rem $Header: basic_data_upgrade.sql 30-aug-2005.15:41:46 rpinnama Exp $
  344. SQL> Rem
  345. SQL> Rem basic_data_upgrade.sql
  346. SQL> Rem
  347. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  348. SQL> Rem
  349. SQL> Rem NAME
  350. SQL> Rem basic_data_upgrade.sql - <one-line expansion of the name>
  351. SQL> Rem
  352. SQL> Rem DESCRIPTION
  353. SQL> Rem <short description of component this file declares/defines>
  354. SQL> Rem
  355. SQL> Rem NOTES
  356. SQL> Rem <other useful comments, qualifications, etc.>
  357. SQL> Rem
  358. SQL> Rem MODIFIED (MM/DD/YY)
  359. SQL> Rem rpinnama 08/23/05 - Fix 4132656 : Auto update tzrgn
  360. SQL> Rem bkesavan 08/08/05 - bkesavan_backport_10.1.0.5.0_4032726_2
  361. SQL> Rem bkesavan 08/08/05 - Created
  362. SQL> Rem
  363. SQL>
  364. SQL> Rem Upgrade script for bug 4032726
  365. SQL> DECLARE
  366.   2 ptype MGMT_PURGE_POLICY.POLICY_TYPE%TYPE;
  367.   3 BEGIN
  368.   4 SELECT policy_type
  369.   5 INTO ptype
  370.   6 FROM MGMT_PURGE_POLICY
  371.   7 WHERE policy_name = \'MGMT_METRIC_ERRORS\';
  372.   8
  373.   9 IF ptype = 1 THEN
  374.  10 EM_PURGE.drop_purge_policy(\'MGMT_METRIC_ERRORS\');
  375.  11 EM_PURGE.add_purge_policy(\'MGMT_METRIC_ERRORS\',
  376.  12 EM_PURGE.G_POLICY_TYPE_SYSTEM,
  377.  13 \'EMD_LOADER.METRIC_ERROR_PURGE\',
  378.  14 180*24,
  379.  15 NULL,
  380.  16 \'Purge policy for Metric errors.\',
  381.  17 EM_PURGE.G_RETENTION_GROUP_ALERTS
  382.  18 );
  383.  19
  384.  20 END IF;
  385.  21 EXCEPTION
  386.  22 WHEN OTHERS THEN
  387.  23 DBMS_OUTPUT.PUT_LINE(\'***** INFO : Error adding purge policy MGMT_METRIC_ERRORS\');
  388.  24 NULL;
  389.  25
  390.  26 END;
  391.  27 /
  392.  
  393. PL/SQL procedure successfully completed.
  394.  
  395. SQL>
  396. SQL> DECLARE
  397.   2 ptype MGMT_PURGE_POLICY.POLICY_TYPE%TYPE;
  398.   3 BEGIN
  399.   4 SELECT policy_type
  400.   5 INTO ptype
  401.   6 FROM MGMT_PURGE_POLICY
  402.   7 WHERE policy_name = \'MGMT_STRING_METRIC_HISTORY\';
  403.   8
  404.   9 IF ptype = 1 THEN
  405.  10 EM_PURGE.drop_purge_policy(\'MGMT_STRING_METRIC_HISTORY\');
  406.  11 EM_PURGE.add_purge_policy(\'MGMT_STRING_METRIC_HISTORY\',
  407.  12 EM_PURGE.G_POLICY_TYPE_SYSTEM,
  408.  13 \'EMD_LOADER.STRING_HISTORY_PURGE\',
  409.  14 31*24,
  410.  15 NULL,
  411.  16 \'Purge policy for String metric history.\',
  412.  17 EM_PURGE.G_RETENTION_GROUP_LVL2_SUMMARY
  413.  18 );
  414.  19
  415.  20 END IF;
  416.  21 EXCEPTION
  417.  22 WHEN OTHERS THEN
  418.  23 DBMS_OUTPUT.PUT_LINE(\'***** INFO : Error adding purge policy MGMT_STRING_METRIC_HISTORY\');
  419.  24 NULL;
  420.  25
  421.  26 END;
  422.  27 /
  423.  
  424. PL/SQL procedure successfully completed.
  425.  
  426. SQL>
  427. SQL> Rem Check to see if agent tzrgn has to be updated or not
  428. SQL> Rem
  429. SQL>
  430. SQL> COLUMN :script_name NEW_VALUE update_tzrgn_script NOPRINT
  431. SQL> VARIABLE script_name VARCHAR2(256)
  432. SQL>
  433. SQL> DECLARE
  434.   2 BEGIN
  435.   3 IF (NOT (\'&&EM_REPOS_MODE\' = \'CENTRAL\') ) THEN
  436.   4 :script_name := \'&&EM_SQL_ROOT/core/v101050/basic/basic_update_agent_tzrgn.sql\';
  437.   5 ELSE
  438.   6 :script_name := \'&&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  439.   7 END IF;
  440.   8 END;
  441.   9 /
  442. old 3: IF (NOT (\'&&EM_REPOS_MODE\' = \'CENTRAL\') ) THEN
  443. new 3: IF (NOT (\'SYSAUX\' = \'CENTRAL\') ) THEN
  444. old 4: :script_name := \'&&EM_SQL_ROOT/core/v101050/basic/basic_update_agent_tzrgn.sql\';
  445. new 4: :script_name := \'?/sysman/admin/emdrep/sql/core/v101050/basic/basic_update_agent_tzrgn.sql\';
  446. old 6: :script_name := \'&&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  447. new 6: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  448.  
  449. PL/SQL procedure successfully completed.
  450.  
  451. SQL>
  452. SQL> SELECT :script_name FROM DUAL;
  453.  
  454.  
  455.  
  456.  
  457. 1 row selected.
  458.  
  459. SQL>
  460. SQL> @@&&update_tzrgn_script
  461. SQL> Rem
  462. SQL> Rem $Header: basic_update_agent_tzrgn.sql 07-sep-2005.23:31:36 rpinnama Exp $
  463. SQL> Rem
  464. SQL> Rem basic_update_agent_tzrgn.sql
  465. SQL> Rem
  466. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  467. SQL> Rem
  468. SQL> Rem NAME
  469. SQL> Rem basic_update_agent_tzrgn.sql - <one-line expansion of the name>
  470. SQL> Rem
  471. SQL> Rem DESCRIPTION
  472. SQL> Rem <short description of component this file declares/defines>
  473. SQL> Rem
  474. SQL> Rem NOTES
  475. SQL> Rem <other useful comments, qualifications, etc.>
  476. SQL> Rem
  477. SQL> Rem MODIFIED (MM/DD/YY)
  478. SQL> Rem rpinnama 09/07/05 - rpinnama_bug-4132656
  479. SQL> Rem rpinnama 08/23/05 - Created
  480. SQL> Rem
  481. SQL>
  482. SQL> PROMPT
  483.  
  484. SQL> PROMPT Source new_agent_tzrgn.sql
  485. Source new_agent_tzrgn.sql
  486. SQL> PROMPT
  487.  
  488. SQL> Rem Source template first so that it will get default value..
  489. SQL> @&EMDW_HOME/sysman/emdrep/config/new_agent_tzrgn.sql.template
  490. SQL>
  491. SQL> Rem
  492. SQL> Rem The new agent tzrgn
  493. SQL> Rem If this is empty, this SHOULD be defaulted to: _NOT_AVAILABLE_
  494. SQL> Rem
  495. SQL> DEFINE EM_AGENT_TZRGN=_NOT_AVAILABLE_
  496. SQL>
  497. SQL> @&EMDW_HOME/sysman/emdrep/config/new_agent_tzrgn.sql
  498. SQL>
  499. SQL>
  500. SQL> DECLARE
  501.   2 BEGIN
  502.   3
  503.   4 IF (NOT (\'&&EM_AGENT_TZRGN\' = \'_NOT_AVAILABLE_\') ) THEN
  504.   5
  505.   6 FOR crec in (SELECT target_name from mgmt_targets
  506.   7 WHERE target_type = \'oracle_emd\')
  507.   8 LOOP
  508.   9 mgmt_target.set_agent_tzrgn(crec.target_name, \'&&EM_AGENT_TZRGN\');
  509.  10 commit;
  510.  11 END LOOP;
  511.  12
  512.  13 END IF;
  513.  14
  514.  15 END;
  515.  16 /
  516. old 4: IF (NOT (\'&&EM_AGENT_TZRGN\' = \'_NOT_AVAILABLE_\') ) THEN
  517. new 4: IF (NOT (\'_NOT_AVAILABLE_\' = \'_NOT_AVAILABLE_\') ) THEN
  518. old 9: mgmt_target.set_agent_tzrgn(crec.target_name, \'&&EM_AGENT_TZRGN\');
  519. new 9: mgmt_target.set_agent_tzrgn(crec.target_name, \'_NOT_AVAILABLE_\');
  520.  
  521. PL/SQL procedure successfully completed.
  522.  
  523. SQL>
  524. SQL>
  525. SQL>
  526. SQL> @&EM_SQL_ROOT/core/v101050/core_post_creation.sql
  527. SQL> Rem
  528. SQL> Rem $Header: core_post_creation.sql 20-dec-2005.05:30:21 rkpandey Exp $
  529. SQL> Rem
  530. SQL> Rem core_post_creation.sql
  531. SQL> Rem
  532. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  533. SQL> Rem
  534. SQL> Rem NAME
  535. SQL> Rem core_post_creation.sql - <one-line expansion of the name>
  536. SQL> Rem
  537. SQL> Rem DESCRIPTION
  538. SQL> Rem <short description of component this file declares/defines>
  539. SQL> Rem
  540. SQL> Rem NOTES
  541. SQL> Rem <other useful comments, qualifications, etc.>
  542. SQL> Rem
  543. SQL> Rem MODIFIED (MM/DD/YY)
  544. SQL> Rem rkpandey 12/20/05 - Add basic_post_creation
  545. SQL> Rem neearora 08/11/05 - neearora_backport_10.1.0.5.0_4500972
  546. SQL> Rem neearora 08/09/05 - Created
  547. SQL> Rem
  548. SQL>
  549. SQL> @&EM_SQL_ROOT/core/v101050/basic/basic_post_creation.sql
  550. SQL> Rem
  551. SQL> Rem $Header: basic_post_creation.sql 22-dec-2005.20:28:22 rkpandey Exp $
  552. SQL> Rem
  553. SQL> Rem basic_post_creation.sql
  554. SQL> Rem
  555. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  556. SQL> Rem
  557. SQL> Rem NAME
  558. SQL> Rem basic_post_creation.sql - <one-line expansion of the name>
  559. SQL> Rem
  560. SQL> Rem DESCRIPTION
  561. SQL> Rem <short description of component this file declares/defines>
  562. SQL> Rem
  563. SQL> Rem NOTES
  564. SQL> Rem <other useful comments, qualifications, etc.>
  565. SQL> Rem
  566. SQL> Rem MODIFIED (MM/DD/YY)
  567. SQL> Rem rkpandey 12/20/05 - Bug 4357890: make oc4j composite
  568. SQL> Rem rkpandey 12/20/05 - Created
  569. SQL> Rem
  570. SQL>
  571. SQL> BEGIN
  572.   2
  573.   3 MGMT_TARGET.add_target_type_properties(p_target_type_in => MGMT_GLOBAL.G_OC4J_TARGET_TYPE,
  574.   4 p_type_property_list_in =>
  575.   5 SMP_EMD_NVPAIR_ARRAY (SMP_EMD_NVPAIR (MGMT_GLOBAL.G_IS_COMPOSITE_PROP, \'1\')));
  576.   6 END;
  577.   7 /
  578.  
  579. PL/SQL procedure successfully completed.
  580.  
  581. SQL>
  582. SQL> @&EM_SQL_ROOT/core/v101050/ecm/ecm_post_creation.sql
  583. SQL> Rem
  584. SQL> Rem $Header: ecm_post_creation.sql 11-aug-2005.02:44:14 neearora Exp $
  585. SQL> Rem
  586. SQL> Rem ecm_post_creation.sql
  587. SQL> Rem
  588. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  589. SQL> Rem
  590. SQL> Rem NAME
  591. SQL> Rem ecm_post_creation.sql - <one-line expansion of the name>
  592. SQL> Rem
  593. SQL> Rem DESCRIPTION
  594. SQL> Rem <short description of component this file declares/defines>
  595. SQL> Rem
  596. SQL> Rem NOTES
  597. SQL> Rem <other useful comments, qualifications, etc.>
  598. SQL> Rem
  599. SQL> Rem MODIFIED (MM/DD/YY)
  600. SQL> Rem neearora 08/11/05 - neearora_backport_10.1.0.5.0_4500972
  601. SQL> Rem neearora 08/09/05 - Created
  602. SQL> Rem
  603. SQL>
  604. SQL> rem
  605. SQL> rem Register valid preLoad and postLoad callbacks.
  606. SQL> rem
  607. SQL> BEGIN
  608.   2 MGMT_LOADER.register_pre_load_callback(\'ECM_CT.PRELOAD_CALLBACK\');
  609.   3 MGMT_LOADER.register_post_load_callback(\'ECM_CT.POSTLOAD_CALLBACK\');
  610.   4 COMMIT;
  611.   5 END;
  612.   6 /
  613.  
  614. PL/SQL procedure successfully completed.
  615.  
  616. SQL>
  617. SQL>
  618. SQL> @&EM_SQL_ROOT/core/v101050/severity_fix_data_3901347.sql
  619. SQL> Rem
  620. SQL> Rem $Header: severity_fix_data_3901347.sql 16-aug-2005.04:11:20 shianand Exp $
  621. SQL> Rem
  622. SQL> Rem severity_fix_data_3901347.sql
  623. SQL> Rem
  624. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  625. SQL> Rem
  626. SQL> Rem NAME
  627. SQL> Rem severity_fix_data_3901347.sql - <one-line expansion of the name>
  628. SQL> Rem
  629. SQL> Rem DESCRIPTION
  630. SQL> Rem <short description of component this file declares/defines>
  631. SQL> Rem
  632. SQL> Rem NOTES
  633. SQL> Rem <other useful comments, qualifications, etc.>
  634. SQL> Rem
  635. SQL> Rem MODIFIED (MM/DD/YY)
  636. SQL> Rem shianand 08/16/05 - shianand_backport_10.1.0.5.0_3901347_1
  637. SQL> Rem rpinnama 06/27/05 - Created
  638. SQL> Rem
  639. SQL>
  640. SQL>
  641. SQL> PROMPT Fixing severity duration (bug 3901347)
  642. Fixing severity duration (bug 3901347)
  643. SQL> SELECT to_char(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\') FROM DUAL;
  644.  
  645. TO_CHAR(SYSDATE,\'YY
  646. -------------------
  647. 2014-10-29 20:48:32
  648.  
  649. 1 row selected.
  650.  
  651. SQL>
  652. SQL>
  653. SQL> DECLARE
  654.   2 type rawtab IS TABLE OF RAW(16) index by binary_integer ;
  655.   3 type varchar256tab IS TABLE OF VARCHAR2(256) index by binary_integer ;
  656.   4 type datetab IS TABLE OF DATE index by binary_integer ;
  657.   5 type numtab is TABLE of NUMBER index by binary_integer;
  658.   6
  659.   7 l_host_resp_stat_guid mgmt_metrics.metric_guid%TYPE;
  660.   8 l_commit_frequency NUMBER := 50000 ;
  661.   9
  662.  10 l_target_guids rawtab ;
  663.  11 l_metric_guids rawtab ;
  664.  12 l_collection_tss datetab ;
  665.  13 l_severity_guids rawtab ;
  666.  14 l_severity_codes numtab ;
  667.  15 l_severity_durs numtab ;
  668.  16
  669.  17 l_upd_cnt number := 0;
  670.  18 l_upd_durs numtab;
  671.  19 l_upd_vguids rawtab;
  672.  20
  673.  21 l_calc_duration mgmt_severity.severity_duration%TYPE;
  674.  22
  675.  23 l_new_tgt_start NUMBER := 0;
  676.  24 l_cnt NUMBER := 0;
  677.  25
  678.  26 CURSOR avail_severities_cur IS
  679.  27 SELECT target_guid, metric_guid, collection_timestamp,
  680.  28 severity_code, severity_duration, severity_guid
  681.  29 FROM mgmt_severity
  682.  30 WHERE metric_guid IN (SELECT DISTINCT metric_guid
  683.  31 FROM mgmt_metrics
  684.  32 WHERE metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
  685.  33 AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN)
  686.  34 AND key_value = \' \'
  687.  35 ORDER BY target_guid, collection_timestamp, load_timestamp,
  688.  36 DECODE(severity_code,
  689.  37 MGMT_GLOBAL.G_SEVERITY_CLEAR, 1,
  690.  38 MGMT_GLOBAL.G_SEVERITY_WARNING, 3,
  691.  39 MGMT_GLOBAL.G_SEVERITY_CRITICAL, 4,
  692.  40 MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
  693.  41 MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, 6,
  694.  42 9) DESC;
  695.  43 l_prev_rec avail_severities_cur%rowtype ;
  696.  44
  697.  45 BEGIN
  698.  46
  699.  47 BEGIN
  700.  48 SELECT parameter_value
  701.  49 INTO l_commit_frequency
  702.  50 FROM mgmt_parameters
  703.  51 WHERE parameter_name = \'viol_duration_upgrade_batchsize\' ;
  704.  52 EXCEPTION
  705.  53 WHEN NO_DATA_FOUND THEN
  706.  54 l_commit_frequency := 10000 ;
  707.  55 END ;
  708.  56
  709.  57 OPEN avail_severities_cur;
  710.  58
  711.  59 FETCH avail_severities_cur BULK COLLECT INTO
  712.  60 l_target_guids, l_metric_guids, l_collection_tss, l_severity_codes,
  713.  61 l_severity_durs, l_severity_guids
  714.  62 LIMIT l_commit_frequency;
  715.  63
  716.  64 IF ( (l_target_guids IS NOT NULL) or (l_target_guids.COUNT > 0) ) THEN
  717.  65 -- Dummy up a guid, so that it wont match the first target
  718.  66 l_prev_rec.target_guid := HEXTORAW(\'0000000000000000000000000000\');
  719.  67
  720.  68 END IF;
  721.  69
  722.  70 WHILE ( (l_target_guids IS NOT NULL) AND (l_target_guids.COUNT > 0) )
  723.  71 LOOP
  724.  72
  725.  73 FOR i IN l_target_guids.FIRST..l_target_guids.LAST
  726.  74 LOOP
  727.  75
  728.  76 l_cnt := l_cnt + 1;
  729.  77 l_new_tgt_start := 0;
  730.  78
  731.  79 IF (l_target_guids(i) != l_prev_rec.target_guid) THEN
  732.  80 l_new_tgt_start := 1;
  733.  81 END IF;
  734.  82
  735.  83 IF (l_new_tgt_start = 0) THEN
  736.  84
  737.  85 l_calc_duration := (l_collection_tss(i) - l_prev_rec.collection_timestamp) * 24;
  738.  86
  739.  87 IF ( (l_calc_duration != l_prev_rec.severity_duration) ) THEN
  740.  88
  741.  89 l_upd_cnt := l_upd_cnt + 1;
  742.  90 l_upd_durs(l_upd_cnt) := l_calc_duration;
  743.  91 l_upd_vguids(l_upd_cnt) := l_prev_rec.severity_guid;
  744.  92
  745.  93 END IF;
  746.  94
  747.  95 ELSE
  748.  96
  749.  97 l_calc_duration := 0;
  750.  98
  751.  99 END IF;
  752. 100
  753. 101 l_prev_rec.target_guid := l_target_guids(i);
  754. 102 l_prev_rec.metric_guid := l_metric_guids(i);
  755. 103 l_prev_rec.collection_timestamp := l_collection_tss(i);
  756. 104 l_prev_rec.severity_code := l_severity_codes(i);
  757. 105 l_prev_rec.severity_duration := NVL(l_severity_durs(i), -1);
  758. 106 l_prev_rec.severity_guid := l_severity_guids(i);
  759. 107
  760. 108 END LOOP; -- FOR loop
  761. 109
  762. 110 IF (l_upd_cnt > 0) THEN
  763. 111
  764. 112 /**
  765. 113 DBMS_OUTPUT.PUT_LINE(\'Fixing \' || l_upd_cnt || \' violations \');
  766. 114
  767. 115 FOR k IN 1..l_upd_cnt
  768. 116 LOOP
  769. 117 DBMS_OUTPUT.PUT_LINE(\' \' || k ||
  770. 118 \' Viol GUID - \' || l_upd_vguids(k) ||
  771. 119 \' New Dur - \' || ROUND(l_upd_durs(k), 4) );
  772. 120 END LOOP;
  773. 121 **/
  774. 122
  775. 123 FORALL j IN 1..l_upd_cnt
  776. 124 UPDATE MGMT_SEVERITY
  777. 125 SET severity_duration = l_upd_durs(j)
  778. 126 WHERE severity_guid = l_upd_vguids(j);
  779. 127
  780. 128 COMMIT;
  781. 129
  782. 130
  783. 131 END IF;
  784. 132
  785. 133
  786. 134
  787. 135 FETCH avail_severities_cur BULK COLLECT INTO
  788. 136 l_target_guids, l_metric_guids, l_collection_tss, l_severity_codes,
  789. 137 l_severity_durs, l_severity_guids
  790. 138 LIMIT l_commit_frequency;
  791. 139
  792. 140 l_cnt := 0;
  793. 141 l_upd_cnt := 0;
  794. 142
  795. 143 END LOOP; -- WHILE loop
  796. 144
  797. 145 CLOSE avail_severities_cur;
  798. 146
  799. 147 END;
  800. 148 /
  801.  
  802. PL/SQL procedure successfully completed.
  803.  
  804. SQL>
  805. SQL> PROMPT Done fixing severity duration
  806. Done fixing severity duration
  807. SQL> SELECT to_char(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\') FROM DUAL;
  808.  
  809. TO_CHAR(SYSDATE,\'YY
  810. -------------------
  811. 2014-10-29 20:48:32
  812.  
  813. 1 row selected.
  814.  
  815. SQL>
  816. SQL>
  817. SQL>
  818. SQL> /*
  819. SQL> ** 25/08/2005 - Commenting out this changes for broken target as few issues
  820. SQL> ** have been detected in 10.2GC due to this. We will re-visit this once it
  821. SQL> ** gets stable in 10.2GC.
  822. SQL> **
  823. SQL> ** @&EM_SQL_ROOT/core/v101050/target_data_upgrade.sql
  824. SQL> */
  825. SQL>
  826. SQL> @&EM_SQL_ROOT/core/v101050/beacon_data_upgrade.sql
  827. SQL> Rem
  828. SQL> Rem $Header: beacon_data_upgrade.sql 23-feb-2006.16:38:01 andyao Exp $
  829. SQL> Rem
  830. SQL> Rem beacon_data_upgrade.sql
  831. SQL> Rem
  832. SQL> Rem Copyright (c) 2006, Oracle. All rights reserved.
  833. SQL> Rem
  834. SQL> Rem NAME
  835. SQL> Rem beacon_data_upgrade.sql - <one-line expansion of the name>
  836. SQL> Rem
  837. SQL> Rem DESCRIPTION
  838. SQL> Rem <short description of component this file declares/defines>
  839. SQL> Rem
  840. SQL> Rem NOTES
  841. SQL> Rem <other useful comments, qualifications, etc.>
  842. SQL> Rem
  843. SQL> Rem MODIFIED (MM/DD/YY)
  844. SQL> Rem andyao 02/23/06 - add website 3.0 response/status metric so
  845. SQL> Rem 10.1.0.5 OMS can work with 10.2 agent
  846. SQL> Rem andyao 02/23/06 - Created
  847. SQL> Rem
  848. SQL>
  849. SQL> BEGIN
  850.   2 EMD_BCN_ADMIN.GEN_BEACON_RESP_METRICS(\'website\');
  851.   3 COMMIT;
  852.   4 END;
  853.   5 /
  854.  
  855. PL/SQL procedure successfully completed.
  856.  
  857. SQL>
  858. SQL> @&EM_SQL_ROOT/core/v101050/user_model_data_upgrade.sql
  859. SQL> Rem
  860. SQL> Rem $Header: user_model_data_upgrade.sql 22-aug-2005.04:37:15 dcawley Exp $
  861. SQL> Rem
  862. SQL> Rem user_model_data_upgrade.sql
  863. SQL> Rem
  864. SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
  865. SQL> Rem
  866. SQL> Rem NAME
  867. SQL> Rem user_model_data_upgrade.sql - <one-line expansion of the name>
  868. SQL> Rem
  869. SQL> Rem DESCRIPTION
  870. SQL> Rem <short description of component this file declares/defines>
  871. SQL> Rem
  872. SQL> Rem NOTES
  873. SQL> Rem <other useful comments, qualifications, etc.>
  874. SQL> Rem
  875. SQL> Rem MODIFIED (MM/DD/YY)
  876. SQL> Rem dcawley 08/22/05 - dcawley_bug-4562785
  877. SQL> Rem dcawley 08/19/05 - Created
  878. SQL> Rem
  879. SQL>
  880. SQL> Rem
  881. SQL> Rem Rebuild the flat role grants
  882. SQL> Rem
  883. SQL> BEGIN
  884.   2 FOR role in (SELECT role_name FROM MGMT_ROLES)
  885.   3 LOOP
  886.   4 MGMT_USER.UPDATE_FLAT_ROLE_GRANTS(role.role_name);
  887.   5 COMMIT;
  888.   6 END LOOP;
  889.   7 END;
  890.   8 /
  891.  
  892. PL/SQL procedure successfully completed.
  893.  
  894. SQL>
  895. SQL> @&EM_SQL_ROOT/core/latest/jobTypes_sql/core_jobTypes.sql
  896. SQL> REM
  897. SQL> REM THIS FILE IS MACHINE GENERATED: DO NOT EDIT
  898. SQL> REM
  899. SQL> REM
  900. SQL>
  901. SQL> PROMPT Registering job types
  902. Registering job types
  903. SQL>
  904. SQL> @&EM_SQL_ROOT/core/latest/jobTypes_sql/AddTargetJob.sql
  905. SQL> REM############################################################################
  906. SQL> REM
  907. SQL> REM Copyright (c) 2002, 2003, Oracle Corporation. All rights reserved.
  908. SQL> REM
  909. SQL> REM
  910. SQL> REM PRODUCT
  911. SQL> REM Oracle Enterprise Manager
  912. SQL> REM
  913. SQL> REM THIS FILE IS MACHINE GENERATED. DO NOT
  914. SQL> REM
  915. SQL> REM JOB TYPE SQL FOR JOB TYPE: AddTargetJob
  916. SQL> REM
  917. SQL> REM#############################################################################
  918. SQL>
  919. SQL> SET ECHO OFF
  920. old 16: :script_name := \'&EM_SQL_ROOT/db/v10102/db_data_upgrade.sql\';
  921. new 16: :script_name := \'?/sysman/admin/emdrep/sql/db/v10102/db_data_upgrade.sql\';
  922. old 19: :script_name := \'&EM_SQL_ROOT/db/v101040/db_data_upgrade.sql\';
  923. new 19: :script_name := \'?/sysman/admin/emdrep/sql/db/v101040/db_data_upgrade.sql\';
  924. old 22: :script_name := \'&EM_SQL_ROOT/db/v102010/db_data_upgrade.sql\';
  925. new 22: :script_name := \'?/sysman/admin/emdrep/sql/db/v102010/db_data_upgrade.sql\';
  926. old 24: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  927. new 24: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  928.  
  929.  
  930.  
  931. No errors.
  932. No errors.
  933. No errors.
  934. No errors.
  935. No errors.
  936. No errors.
  937. Registering job types
  938. old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  939. new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
  940. old 3: :script_name := \'&EM_SQL_ROOT/ias/ias_data_upgrade.sql\';
  941. new 3: :script_name := \'?/sysman/admin/emdrep/sql/ias/ias_data_upgrade.sql\';
  942. old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  943. new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  944.  
  945.  
  946.  
  947. old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  948. new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
  949. old 3: :script_name := \'&EM_SQL_ROOT/ocs/ocs_data_upgrade.sql\';
  950. new 3: :script_name := \'?/sysman/admin/emdrep/sql/ocs/ocs_data_upgrade.sql\';
  951. old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
  952. new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
  953.  
  954.  
  955.  
  956. Submitting DBMS jobs.
  957. old 5: IF NOT (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
  958. new 5: IF NOT (\'SYSAUX\' = \'CENTRAL\') THEN
  959.  
  960. TIMESTAMP
  961. --------------------------------------------------------------------------------
  962. COMP_TIMESTAMP EM 2014-10-29 20:48:38
  963. DBUA_TIMESTAMP EM VALID 2014-10-29 20:48:38
  964.  
  965.  
  966.  
  967.  
  968. .. loading the Expression Filter/BRM Java library
  969. .. creating Rule Manager catalog views
  970. .. creating Rule Manager package/type implementations
  971. No errors.
  972. No errors.
  973. No errors.
  974. No errors.
  975. No errors.
  976. No errors.
  977. No errors.
  978. No errors.
  979. No errors.
  980. No errors.
  981. No errors.
  982. No errors.
  983. No errors.
  984. No errors.
  985. No errors.
  986. No errors.
  987. No errors.
  988. No errors.
  989. No errors.
  990. No errors.
  991.  
  992. TIMESTAMP
  993. --------------------------------------------------------------------------------
  994. COMP_TIMESTAMP RUL 2014-10-29 20:48:43
  995. DBUA_TIMESTAMP RUL VALID 2014-10-29 20:48:43
  996.  
  997.  
  998.  
  999.  
  1000.  
  1001. TIMESTAMP
  1002. --------------------------------------------------------------------------------
  1003. COMP_TIMESTAMP UPGRD_END 2014-10-29 20:48:43
  1004. .
  1005. Oracle Database 10.2 Upgrade Status Utility 10-29-2014 20:48:43
  1006. .
  1007. Component Status Version HH:MM:SS
  1008. Oracle Database Server VALID 10.2.0.4.0 00:06:48
  1009. JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:18
  1010. Oracle XDK VALID 10.2.0.4.0 00:00:18
  1011. Oracle Database Java Packages VALID 10.2.0.4.0 00:00:16
  1012. Oracle Text VALID 10.2.0.4.0 00:00:18
  1013. Oracle XML Database VALID 10.2.0.4.0 00:01:12
  1014. Oracle Workspace Manager VALID 10.2.0.4.3 00:00:33
  1015. Oracle Data Mining VALID 10.2.0.4.0 00:00:17
  1016. OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:15
  1017. OLAP Catalog VALID 10.2.0.4.0 00:00:40
  1018. Oracle OLAP API VALID 10.2.0.4.0 00:00:29
  1019. Oracle interMedia VALID 10.2.0.4.0 00:02:22
  1020. Spatial VALID 10.2.0.4.0 00:01:02
  1021. Oracle Expression Filter VALID 10.2.0.4.0 00:00:07
  1022. Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:26
  1023. Oracle Rule Manager VALID 10.2.0.4.0 00:00:05
  1024. .
  1025. Total Upgrade Time: 00:18:35
  1026. DOC>#######################################################################
  1027. DOC>#######################################################################
  1028. DOC>
  1029. DOC> The above PL/SQL lists the SERVER components in the upgraded
  1030. DOC> database, along with their current version and status.
  1031. DOC>
  1032. DOC> Please review the status and version columns and look for
  1033. DOC> any errors in the spool log file. If there are errors in the spool
  1034. DOC> file, or any components are not VALID or not the current version,
  1035. DOC> consult the Oracle Database Upgrade Guide for troubleshooting
  1036. DOC> recommendations.
  1037. DOC>
  1038. DOC> Next shutdown immediate, restart for normal operation, and then
  1039. DOC> run utlrp.sql to recompile any invalid application objects.
  1040. DOC>
  1041. DOC>#######################################################################
  1042. DOC>#######################################################################
  1043. DOC>#

正常啟動資料庫，編譯失效的物件。

點選(此處)摺疊或開啟

  1. SQL> shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SQL> startup;
  6. ORACLE instance started.
  7.  
  8. Total System Global Area 599785472 bytes
  9. Fixed Size 2085776 bytes
  10. Variable Size 226495600 bytes
  11. Database Buffers 364904448 bytes
  12. Redo Buffers 6299648 bytes
  13. Database mounted.
  14. Database opened.
  15. SQL> @?/rdbms/admin/utlrp.sql
  16.  
  17. TIMESTAMP
  18. --------------------------------------------------------------------------------
  19. COMP_TIMESTAMP UTLRP_BGN 2014-10-29 20:59:41
  20. DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
  21. DOC> objects in the database. Recompilation time is proportional to the
  22. DOC> number of invalid objects in the database, so this command may take
  23. DOC> a long time to execute on a database with a large number of invalid
  24. DOC> objects.
  25. DOC>
  26. DOC> Use the following queries to track recompilation progress:
  27. DOC>
  28. DOC> 1. Query returning the number of invalid objects remaining. This
  29. DOC> number should decrease with time.
  30. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
  31. DOC>
  32. DOC> 2. Query returning the number of objects compiled so far. This number
  33. DOC> should increase with time.
  34. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
  35. DOC>
  36. DOC> This script automatically chooses serial or parallel recompilation
  37. DOC> based on the number of CPUs available (parameter cpu_count) multiplied
  38. DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
  39. DOC> On RAC, this number is added across all RAC nodes.
  40. DOC>
  41. DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
  42. DOC> recompilation. Jobs are created without instance affinity so that they
  43. DOC> can migrate across RAC nodes. Use the following queries to verify
  44. DOC> whether UTL_RECOMP jobs are being created and run correctly:
  45. DOC>
  46. DOC> 1. Query showing jobs created by UTL_RECOMP
  47. DOC> SELECT job_name FROM dba_scheduler_jobs
  48. DOC> WHERE job_name like \'UTL_RECOMP_SLAVE_%\';
  49. DOC>
  50. DOC> 2. Query showing UTL_RECOMP jobs that are running
  51. DOC> SELECT job_name FROM dba_scheduler_running_jobs
  52. DOC> WHERE job_name like \'UTL_RECOMP_SLAVE_%\';
  53. DOC>#
  54.  
  55. TIMESTAMP
  56. --------------------------------------------------------------------------------
  57. COMP_TIMESTAMP UTLRP_END 2014-10-29 21:00:30
  58. DOC> The following query reports the number of objects that have compiled
  59. DOC> with errors (objects that compile with errors have status set to 3 in
  60. DOC> obj$). If the number is higher than expected, please examine the error
  61. DOC> messages reported with each object (using SHOW ERRORS) to see if they
  62. DOC> point to system misconfiguration or resource constraints that must be
  63. DOC> fixed before attempting to recompile these objects.
  64. DOC>#
  65.  
  66. OBJECTS WITH ERRORS
  67. -------------------
  68.                   0
  69. DOC> The following query reports the number of errors caught during
  70. DOC> recompilation. If this number is non-zero, please query the error
  71. DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
  72. DOC> are due to misconfiguration or resource constraints that must be
  73. DOC> fixed before objects can compile successfully.
  74. DOC>#
  75.  
  76. ERRORS DURING RECOMPILATION
  77. ---------------------------
  78.                           0

檢測升級後情況，重新修改相容性引數

點選(此處)摺疊或開啟

  1. SQL> select comp_name , version, status from dba_registry;
  2.  
  3. COMP_NAME VERSION STATUS
  4. ------------------------------ ------------------------------ -----------
  5. Oracle Enterprise Manager 10.2.0.4.0 VALID
  6. Spatial 10.2.0.4.0 VALID
  7. Oracle interMedia 10.2.0.4.0 VALID
  8. OLAP Catalog 10.2.0.4.0 VALID
  9. Oracle XML Database 10.2.0.4.0 VALID
  10. Oracle Text 10.2.0.4.0 VALID
  11. Oracle Expression Filter 10.2.0.4.0 VALID
  12. Oracle Rule Manager 10.2.0.4.0 VALID
  13. Oracle Workspace Manager 10.2.0.4.3 VALID
  14. Oracle Data Mining 10.2.0.4.0 VALID
  15. Oracle Database Catalog Views 10.2.0.4.0 VALID
  16. Oracle Database Packages and T 10.2.0.4.0 VALID
  17. ypes
  18.  
  19. JServer JAVA Virtual Machine 10.2.0.4.0 VALID
  20. Oracle XDK 10.2.0.4.0 VALID
  21. Oracle Database Java Packages 10.2.0.4.0 VALID
  22. OLAP Analytic Workspace 10.2.0.4.0 VALID
  23. Oracle OLAP API 10.2.0.4.0 VALID
  24.  
  25. SQL> show parameter comp
  26.  
  27. NAME TYPE VALUE
  28. ------------------------------------ ----------- ------------------------------
  29. compatible string 10.2.0.1.0
  30. nls_comp string
  31. plsql_compiler_flags string INTERPRETED, NON_DEBUG
  32. plsql_v2_compatibility boolean FALSE
  33.  
  34. 確認在新的版本資料庫下應用的沒有問題，那麼
  35.  
  36. alter system set compatible=\'10.2.0.4.0\' scope=spfile;
  37.  
  38. 如果是滾動升級主備庫切換之前不要設定該引數，否則日誌不對應。
  39.  
  40. SQL> show parameter comp
  41.  
  42. NAME TYPE VALUE
  43. ------------------------------------ ----------- ------------------------------
  44. compatible string 10.2.0.4.0
  45. nls_comp string
  46. plsql_compiler_flags string INTERPRETED, NON_DEBUG
  47. plsql_v2_compatibility boolean FALSE

升級回退【沒做】

點選(此處)摺疊或開啟

  1. SQL> STARTUP DOWNGRADE
  2. SQL> SPOOL downgrade.log
  3. SQL> @catdwgrd.sql（10.2.10 執行的是這個，而 10.1 降級用的是 d92000.sql，即 dold_release.sql）
  4. Sql>spool off
  5. Sql>shutdown immediate

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1314342/，如需轉載，請註明出處，否則將追究法律責任。

相關文章