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步骤
检查当前版本
点击(此处)折叠或打开
- SQL> select * from v$version;
-
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
- PL/SQL Release 10.2.0.1.0 - Production
- CORE 10.2.0.1.0 Production
- TNS for Linux: Version 10.2.0.1.0 - Production
- NLSRTL Version 10.2.0.1.0 – Production
检查磁盘空间(system必须要少于10m)
调整内存参数(share pool和java pool加快升级速度)
调整sga大小【没做】
点击(此处)折叠或打开
- SQL> alter system set sga_max_size = 1300M scope=spfile;
-
- System altered.
-
- SQL> alter system set sga_target = 1000m scope=spfile;
-
- System altered.
调整共享池的大小【没做】
点击(此处)折叠或打开
- SQL> show parameter shared
-
- NAME TYPE VALUE
- ------------------------------ -------------- ------------------------
- hi_shared_memory_address integer 0
- max_shared_servers integer
- shared_memory_address integer 0
- shared_pool_reserved_size big integer 8178892
- shared_pool_size big integer 0
- shared_server_sessions integer
- shared_servers integer 1
-
- SQL> alter system set shared_pool_size=200m scope=spfile;
-
- System altered.
调整java池的大小【没做】
点击(此处)折叠或打开
- SQL> show parameter java
-
- NAME TYPE VALUE
- -------------------------- ------------------ -------------
- java_max_sessionspace_size integer 0
- java_pool_size big integer 300M
- java_soft_sessionspace_limit integer 0
-
- SQL> alter system set java_pool_size=300M scope=spfile;
-
- System altered.
-
- SQL> startup force nomount;
- ORACLE instance started.
-
- Total System Global Area 1375731712 bytes
- Fixed Size 2020704 bytes
- Variable Size 872417952 bytes
- Database Buffers 486539264 bytes
- Redo Buffers 14753792 bytes
- SQL> show parameter sga
-
- NAME TYPE VALUE
- ------------------------ ------------------------ -------
- lock_sga boolean FALSE
- pre_page_sga boolean FALSE
- sga_max_size big integer 1312M
- sga_target big integer 1008M
-
- SQL> show parameter shared
- NAME TYPE VALUE
- ---------------------------- ------------------- -----------------
- shared_pool_size big integer 208M
-
- SQL> show parameter java
-
- NAME TYPE VALUE
- --------------------------- ------------------------- -----------
- java_pool_size big integer 304M
-
-
- SQL> show parameter shared
-
- NAME TYPE VALUE
- ------------------------------------ --------------------------------- ------------------------------
- hi_shared_memory_address integer 0
- max_shared_servers integer
- shared_memory_address integer 0
- shared_pool_reserved_size big integer 10905190
- shared_pool_size big integer 208M
- shared_server_sessions integer
- shared_servers integer 1
- SQL> show parameter disp
-
- NAME TYPE VALUE
- --------------------------- -------------------- ----------------
- dispatchers string (PROTOCOL=TCP) (SERVICE=prodXDB) max_dispatchers integer
-
- SQL> alter system set dispatchers=\'\';
-
- System altered.
-
- SQL> alter system set shared_servers= 0;
-
- System altered.
关闭监听 em
点击(此处)折叠或打开
- [oracle@test Disk1]$emctl stop dbconsole
- [oracle@test Disk1]$isqlplusctl stop
- [oracle@test Disk1]$lsnrctl stop
-
- [oracle@test Disk1]$ netstat -an | grep 1521
- [oracle@test Disk1]$ netstat -an | grep 1158
-
- LSNRCTL> status
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
- TNS-12541: TNS:no listener
- TNS-12560: TNS:protocol adapter error
- TNS-00511: No listener
- Linux Error: 2: No such file or directory
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bwst)(PORT=1521)))
- TNS-12541: TNS:no listener
- TNS-12560: TNS:protocol adapter error
- TNS-00511: No listener
- Linux Error: 111: Connection refused
为数据库做冷备份
… …
关闭数据库实例及其相关进程
点击(此处)折叠或打开
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
-
- Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
- With the Partitioning, OLAP and Data Mining options
- [oracle@test ~]$ ps -ef | grep ora_
- oracle 30516 27389 0 17:17 pts/2 00:00:00 grep ora_
将补丁包上传到oracle server,解压、安装
… …
以图形界面进入oracle server
![](http://img.blog.itpub.net/blog/attachment/201410/30/29990276_1414637266LJQx.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201410/30/29990276_14146372703j0F.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201410/30/29990276_1414637276167U.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201410/30/29990276_1414637282bB7v.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201410/30/29990276_141463728864jO.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201410/30/29990276_1414637294p8o1.jpg?x-oss-process=style/bb)
点击(此处)折叠或打开
- [root@test ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh
- Running Oracle10 root.sh script...
-
- The following environment variables are set as:
- ORACLE_OWNER= oracle
- ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1
-
- Enter the full pathname of the local bin directory: [/usr/local/bin]:
- The file \"dbhome\" already exists in /usr/local/bin. Overwrite it? (y/n)
- [n]: y
- Copying dbhome to /usr/local/bin ...
- The file \"oraenv\" already exists in /usr/local/bin. Overwrite it? (y/n)
- [n]: y
- Copying oraenv to /usr/local/bin ...
- The file \"coraenv\" already exists in /usr/local/bin. Overwrite it? (y/n)
- [n]: y
- Copying coraenv to /usr/local/bin ...
-
- 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.sh script.
- Now product-specific root actions will be performed.
升级完成后以startup upgrade 方式打开库,升级数据字典
点击(此处)折叠或打开
- [oracle@test Disk1]$ sqlplus / as sysdba
-
- SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 29 20:20:28 2014
-
- Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
-
- Connected to an idle instance.
-
- SQL> startup upgrade;
- ORACLE instance started.
-
- Total System Global Area 599785472 bytes
- Fixed Size 2085776 bytes
- Variable Size 163581040 bytes
- Database Buffers 427819008 bytes
- Redo Buffers 6299648 bytes
- Database mounted.
- Database opened.
将生成的日志spool到一个文件中。
点击(此处)折叠或打开
- SQL> spool /home/oracle/cpu_up.log
- SQL> @?/rdbms/admin/catupgrd.sql
- SQL> values (\'db_database_instance_overview\', \'en\', \'Database Instance Overview\');
- SQL> insert into MGMT_IP_REPORT_NLS_MAP (NLSID, LOCALE, NLS_STRING)
- SQL> values (\'db_database_and_instance_info\', \'en\', \'Database and Instance Information\');
- SQL> insert into MGMT_IP_REPORT_NLS_MAP (NLSID, LOCALE, NLS_STRING)
- SQL> values (\'db_sga_info\', \'en\', \'SGA Information\');
- SQL> insert into MGMT_IP_REPORT_NLS_MAP (NLSID, LOCALE, NLS_STRING)
- SQL> values (\'db_non_default_init_params\', \'en\', \'Non-Default Initialization Parameters\');
- SQL> END load_nls_strings;
- SQL>
- SQL>
- SQL> END mgmt_database_overview;
- SQL> /
- SQL>
- SQL> show errors;
- SQL> */
- SQL>
- SQL>
- SQL>
- SQL> --
- SQL> -- Recreate iAS procs (if EM_REPOS_MODE is CENTRAL)
- SQL> COLUMN :script_name NEW_VALUE ias_file NOPRINT
- SQL> VARIABLE script_name VARCHAR2(256)
- SQL> BEGIN
- 2 IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- 3 :script_name := \'&EM_SQL_ROOT/ias/ias_procs.sql\';
- 4 ELSE
- 5 :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- 6 END IF;
- 7 END;
- 8 /
- old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
- old 3: :script_name := \'&EM_SQL_ROOT/ias/ias_procs.sql\';
- new 3: :script_name := \'?/sysman/admin/emdrep/sql/ias/ias_procs.sql\';
- old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> SELECT :script_name FROM DUAL;
-
-
-
-
- 1 row selected.
-
- SQL>
- SQL> @&ias_file
- SQL> Rem
- SQL> Rem $Header: admin_do_nothing.sql 23-apr-2004.17:05:02 rpinnama Exp $
- SQL> Rem
- SQL> Rem admin_do_nothing.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2004, Oracle Corporation. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem admin_do_nothing.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rpinnama 04/23/04 - rpinnama_backport_10.1.0.2.0_3535007
- SQL> Rem rpinnama 04/15/04 - Created
- SQL> Rem
- SQL>
- SQL>
- SQL>
- SQL> --
- SQL> -- Recreate OCS procs (if EM_REPOS_MODE is CENTRAL)
- SQL> COLUMN :script_name NEW_VALUE ocs_file NOPRINT
- SQL> VARIABLE script_name VARCHAR2(256)
- SQL> BEGIN
- 2 IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- 3 :script_name := \'&EM_SQL_ROOT/ocs/ocs_procs.sql\';
- 4 ELSE
- 5 :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- 6 END IF;
- 7 END;
- 8 /
- old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
- old 3: :script_name := \'&EM_SQL_ROOT/ocs/ocs_procs.sql\';
- new 3: :script_name := \'?/sysman/admin/emdrep/sql/ocs/ocs_procs.sql\';
- old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> SELECT :script_name FROM DUAL;
-
-
-
-
- 1 row selected.
-
- SQL>
- SQL> @&ocs_file
- SQL> Rem
- SQL> Rem $Header: admin_do_nothing.sql 23-apr-2004.17:05:02 rpinnama Exp $
- SQL> Rem
- SQL> Rem admin_do_nothing.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2004, Oracle Corporation. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem admin_do_nothing.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rpinnama 04/23/04 - rpinnama_backport_10.1.0.2.0_3535007
- SQL> Rem rpinnama 04/15/04 - Created
- SQL> Rem
- SQL>
- SQL>
- SQL>
- SQL>
- SQL> --
- SQL> -- NOTE : External Component integrators should integrate their proc scripts here
- SQL> -- Integrators should check the repository mode EM_REPOS_MODE and decide
- SQL> -- whether to execute their component scripts or not
- SQL> --
- SQL>
- SQL>
- SQL>
- SQL> -- Recompile all the schema objects
- SQL> @&EM_SQL_ROOT/core/latest/admin/admin_recompile_invalid.sql &&EM_REPOS_USER
- SQL> Rem
- SQL> Rem $Header: admin_recompile_invalid.sql 27-feb-2003.17:05:37 rpinnama Exp $
- SQL> Rem
- SQL> Rem admin_recompile_invalid.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2002, 2003, Oracle Corporation. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem admin_recompile_invalid.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rpinnama 02/27/03 - Parameterize the user name
- SQL> Rem rpinnama 09/26/02 - Remove exit at the end
- SQL> Rem rpinnama 05/16/02 - rpinnama_reorg_rep_scripts_2
- SQL> Rem rpinnama 05/16/02 - Created
- SQL> Rem
- SQL>
- SQL> Rem
- SQL> Rem This SQL script requires the following arguments
- SQL> Rem 1. Name of the repository user
- SQL> Rem
- SQL> DEFINE EM_REPOS_USER =\"&1\"
- SQL>
- SQL> DECLARE
- 2 BEGIN
- 3
- 4 FOR crec IN (SELECT object_name, object_type,
- 5 DECODE(object_type, \'FUNCTION\', 1,
- 6 \'PROCEDURE\', 2,
- 7 \'TRIGGER\', 3,
- 8 \'VIEW\', 4,
- 9 \'PACKAGE\', 5,
- 10 \'PACKAGE BODY\', 6,
- 11 9) object_type_seq
- 12 FROM all_objects
- 13 WHERE owner =\'&EM_REPOS_USER\'
- 14 AND status =\'INVALID\'
- 15 AND object_type IN (\'FUNCTION\', \'PACKAGE\', \'PACKAGE BODY\', \'PROCEDURE\',
- 16 \'TRIGGER\', \'VIEW\')
- 17 ORDER BY object_type_seq, object_type, created)
- 18 LOOP
- 19 BEGIN
- 20 DBMS_OUTPUT.PUT_LINE(\'CreateRep recompiling invalid \' ||
- 21 crec.object_type || \' \' || crec.object_name);
- 22
- 23 IF (crec.object_type = \'PACKAGE BODY\') THEN
- 24
- 25 -- If package body is invalid, just compile the body and not
- 26 -- the specification
- 27 EXECUTE IMMEDIATE \'ALTER PACKAGE \' ||
- 28 crec.object_name || \' compile body\';
- 29 ELSE
- 30 EXECUTE IMMEDIATE \'ALTER \' || crec.object_type || \' \' ||
- 31 crec.object_name || \' compile\';
- 32 END IF;
- 33 EXCEPTION
- 34 WHEN OTHERS THEN
- 35 DBMS_OUTPUT.PUT_LINE(\'CreateRep failed - Unable to compile \' ||
- 36 crec.object_name );
- 37
- 38 RAISE;
- 39 END;
- 40 END LOOP;
- 41
- 42 END;
- 43 /
- old 13: WHERE owner =\'&EM_REPOS_USER\'
- new 13: WHERE owner =\'SYSMAN\'
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL>
- SQL>
- SQL>
- SQL> --
- SQL> -- Upgrade CORE data
- SQL> @&EM_SQL_ROOT/core/core_data_upgrade.sql
- SQL> Rem
- SQL> Rem $Header: core_data_upgrade.sql 13-jul-2006.13:50:09 sadattaw Exp $
- SQL> Rem
- SQL> Rem core_data_upgrade.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem core_data_upgrade.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem sadattaw 07/13/06 - add condition for 10.1.0.6.0
- SQL> Rem rkpandey 07/17/06 - Add 10.1.0.6 support
- SQL> Rem jsadras 08/08/05 - add 10.1.0.5 support
- SQL> Rem rpinnama 09/21/04 - Support upgrade to 10.1.0.4.0
- SQL> Rem rpinnama 04/23/04 - rpinnama_backport_10.1.0.2.0_3535007
- SQL> Rem rpinnama 04/15/04 - Created
- SQL> Rem
- SQL>
- SQL> Rem Select upgrade script to run
- SQL> Rem
- SQL>
- SQL> COLUMN :script_name NEW_VALUE data_upgrade_file NOPRINT
- SQL> VARIABLE script_name VARCHAR2(256)
- SQL>
- SQL> DECLARE
- 2 l_core_ver VARCHAR2(16);
- 3 BEGIN
- 4 BEGIN
- 5 SELECT version INTO l_core_ver
- 6 FROM MGMT_VERSIONS
- 7 WHERE component_name = \'CORE\';
- 8 EXCEPTION
- 9 WHEN NO_DATA_FOUND THEN
- 10 l_core_ver := \'10.1.0.2.0\';
- 11 END;
- 12
- 13 IF (substr(l_core_ver, 1, 8) = \'10.1.0.2\') THEN
- 14 -- Upgrading from 10.1.0.2
- 15 :script_name := \'&EM_SQL_ROOT/core/v10102/core_data_upgrade.sql\';
- 16 ELSIF (substr(l_core_ver, 1, 8) = \'10.1.0.3\') THEN
- 17 -- Upgrading from 10.1.0.3
- 18 :script_name := \'&EM_SQL_ROOT/core/v101040/core_data_upgrade.sql\';
- 19 ELSIF (substr(l_core_ver, 1, 8) = \'10.1.0.4\') THEN
- 20 -- Upgrading from 10.1.0.4
- 21 :script_name := \'&EM_SQL_ROOT/core/v101050/core_data_upgrade.sql\';
- 22 ELSIF (substr(l_core_ver, 1, 8) = \'10.1.0.5\') THEN
- 23 -- Upgrading from 10.1.0.5
- 24 :script_name := \'&EM_SQL_ROOT/core/v101060/core_data_upgrade.sql\';
- 25 ELSE
- 26 :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- 27 END IF;
- 28 END;
- 29 /
- old 15: :script_name := \'&EM_SQL_ROOT/core/v10102/core_data_upgrade.sql\';
- new 15: :script_name := \'?/sysman/admin/emdrep/sql/core/v10102/core_data_upgrade.sql\';
- old 18: :script_name := \'&EM_SQL_ROOT/core/v101040/core_data_upgrade.sql\';
- new 18: :script_name := \'?/sysman/admin/emdrep/sql/core/v101040/core_data_upgrade.sql\';
- old 21: :script_name := \'&EM_SQL_ROOT/core/v101050/core_data_upgrade.sql\';
- new 21: :script_name := \'?/sysman/admin/emdrep/sql/core/v101050/core_data_upgrade.sql\';
- old 24: :script_name := \'&EM_SQL_ROOT/core/v101060/core_data_upgrade.sql\';
- new 24: :script_name := \'?/sysman/admin/emdrep/sql/core/v101060/core_data_upgrade.sql\';
- old 26: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 26: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> SELECT :script_name FROM DUAL;
-
-
-
-
- 1 row selected.
-
- SQL> (
- 2 @&data_upgrade_file
- 2 Rem
- 3 Rem $Header: core_data_upgrade.sql 13-jul-2006.13:52:19 sadattaw Exp $
- 4 Rem
- 5 Rem core_data_upgrade.sql
- 6 Rem
- 7 Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.
- 8 Rem
- 9 Rem NAME
- 10 Rem core_data_upgrade.sql - <one-line expansion of the name>
- 11 Rem
- 12 Rem DESCRIPTION
- 13 Rem <short description of component this file declares/defines>
- 14 Rem
- 15 Rem NOTES
- 16 Rem <other useful comments, qualifications, etc.>
- 17 Rem
- 18 Rem MODIFIED (MM/DD/YY)
- 19 Rem sadattaw 07/13/06 - add chaining cond for 10.1.0.6.0
- 20 Rem rkpandey 07/17/06 - Chain to 101060
- 21 Rem andyao 02/23/06 - add website 3.0 response/status metric so
- 22 Rem 10.1.0.5 OMS can work with 10.2 agent
- 23 Rem spahuja 11/21/05 - calling core_jobTypes for upgrade
- 24 Rem rpinnama 08/30/05 - Add basic data upgrade
- 25 Rem pmodi 08/29/05 - Comment out target_data_upgrade
- 26 Rem dcawley 08/19/05 - Include user model
- 27 Rem shianand 08/16/05 -
- 28 Rem rpinnama 06/27/05 - Created
- 29 Rem neearora 08/10/05 - Backport of bug 4500972. added call for
- 30 Rem core_post_creation.sql
- 31 Rem jsadras 08/08/05 - jsadras_backport_10.1.0.5.0_4520348
- 32 Rem jsadras 08/08/05 - Created
- 33 Rem
- 34
- SQL> -- core_post_creation should be the on top always.
- SQL> @&EM_SQL_ROOT/core/v101050/basic/basic_data_upgrade.sql
- SQL> Rem
- SQL> Rem $Header: basic_data_upgrade.sql 30-aug-2005.15:41:46 rpinnama Exp $
- SQL> Rem
- SQL> Rem basic_data_upgrade.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem basic_data_upgrade.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rpinnama 08/23/05 - Fix 4132656 : Auto update tzrgn
- SQL> Rem bkesavan 08/08/05 - bkesavan_backport_10.1.0.5.0_4032726_2
- SQL> Rem bkesavan 08/08/05 - Created
- SQL> Rem
- SQL>
- SQL> Rem Upgrade script for bug 4032726
- SQL> DECLARE
- 2 ptype MGMT_PURGE_POLICY.POLICY_TYPE%TYPE;
- 3 BEGIN
- 4 SELECT policy_type
- 5 INTO ptype
- 6 FROM MGMT_PURGE_POLICY
- 7 WHERE policy_name = \'MGMT_METRIC_ERRORS\';
- 8
- 9 IF ptype = 1 THEN
- 10 EM_PURGE.drop_purge_policy(\'MGMT_METRIC_ERRORS\');
- 11 EM_PURGE.add_purge_policy(\'MGMT_METRIC_ERRORS\',
- 12 EM_PURGE.G_POLICY_TYPE_SYSTEM,
- 13 \'EMD_LOADER.METRIC_ERROR_PURGE\',
- 14 180*24,
- 15 NULL,
- 16 \'Purge policy for Metric errors.\',
- 17 EM_PURGE.G_RETENTION_GROUP_ALERTS
- 18 );
- 19
- 20 END IF;
- 21 EXCEPTION
- 22 WHEN OTHERS THEN
- 23 DBMS_OUTPUT.PUT_LINE(\'***** INFO : Error adding purge policy MGMT_METRIC_ERRORS\');
- 24 NULL;
- 25
- 26 END;
- 27 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> DECLARE
- 2 ptype MGMT_PURGE_POLICY.POLICY_TYPE%TYPE;
- 3 BEGIN
- 4 SELECT policy_type
- 5 INTO ptype
- 6 FROM MGMT_PURGE_POLICY
- 7 WHERE policy_name = \'MGMT_STRING_METRIC_HISTORY\';
- 8
- 9 IF ptype = 1 THEN
- 10 EM_PURGE.drop_purge_policy(\'MGMT_STRING_METRIC_HISTORY\');
- 11 EM_PURGE.add_purge_policy(\'MGMT_STRING_METRIC_HISTORY\',
- 12 EM_PURGE.G_POLICY_TYPE_SYSTEM,
- 13 \'EMD_LOADER.STRING_HISTORY_PURGE\',
- 14 31*24,
- 15 NULL,
- 16 \'Purge policy for String metric history.\',
- 17 EM_PURGE.G_RETENTION_GROUP_LVL2_SUMMARY
- 18 );
- 19
- 20 END IF;
- 21 EXCEPTION
- 22 WHEN OTHERS THEN
- 23 DBMS_OUTPUT.PUT_LINE(\'***** INFO : Error adding purge policy MGMT_STRING_METRIC_HISTORY\');
- 24 NULL;
- 25
- 26 END;
- 27 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> Rem Check to see if agent tzrgn has to be updated or not
- SQL> Rem
- SQL>
- SQL> COLUMN :script_name NEW_VALUE update_tzrgn_script NOPRINT
- SQL> VARIABLE script_name VARCHAR2(256)
- SQL>
- SQL> DECLARE
- 2 BEGIN
- 3 IF (NOT (\'&&EM_REPOS_MODE\' = \'CENTRAL\') ) THEN
- 4 :script_name := \'&&EM_SQL_ROOT/core/v101050/basic/basic_update_agent_tzrgn.sql\';
- 5 ELSE
- 6 :script_name := \'&&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- 7 END IF;
- 8 END;
- 9 /
- old 3: IF (NOT (\'&&EM_REPOS_MODE\' = \'CENTRAL\') ) THEN
- new 3: IF (NOT (\'SYSAUX\' = \'CENTRAL\') ) THEN
- old 4: :script_name := \'&&EM_SQL_ROOT/core/v101050/basic/basic_update_agent_tzrgn.sql\';
- new 4: :script_name := \'?/sysman/admin/emdrep/sql/core/v101050/basic/basic_update_agent_tzrgn.sql\';
- old 6: :script_name := \'&&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 6: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> SELECT :script_name FROM DUAL;
-
-
-
-
- 1 row selected.
-
- SQL>
- SQL> @@&&update_tzrgn_script
- SQL> Rem
- SQL> Rem $Header: basic_update_agent_tzrgn.sql 07-sep-2005.23:31:36 rpinnama Exp $
- SQL> Rem
- SQL> Rem basic_update_agent_tzrgn.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem basic_update_agent_tzrgn.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rpinnama 09/07/05 - rpinnama_bug-4132656
- SQL> Rem rpinnama 08/23/05 - Created
- SQL> Rem
- SQL>
- SQL> PROMPT
-
- SQL> PROMPT Source new_agent_tzrgn.sql
- Source new_agent_tzrgn.sql
- SQL> PROMPT
-
- SQL> Rem Source template first so that it will get default value..
- SQL> @&EMDW_HOME/sysman/emdrep/config/new_agent_tzrgn.sql.template
- SQL>
- SQL> Rem
- SQL> Rem The new agent tzrgn
- SQL> Rem If this is empty, this SHOULD be defaulted to: _NOT_AVAILABLE_
- SQL> Rem
- SQL> DEFINE EM_AGENT_TZRGN=_NOT_AVAILABLE_
- SQL>
- SQL> @&EMDW_HOME/sysman/emdrep/config/new_agent_tzrgn.sql
- SQL>
- SQL>
- SQL> DECLARE
- 2 BEGIN
- 3
- 4 IF (NOT (\'&&EM_AGENT_TZRGN\' = \'_NOT_AVAILABLE_\') ) THEN
- 5
- 6 FOR crec in (SELECT target_name from mgmt_targets
- 7 WHERE target_type = \'oracle_emd\')
- 8 LOOP
- 9 mgmt_target.set_agent_tzrgn(crec.target_name, \'&&EM_AGENT_TZRGN\');
- 10 commit;
- 11 END LOOP;
- 12
- 13 END IF;
- 14
- 15 END;
- 16 /
- old 4: IF (NOT (\'&&EM_AGENT_TZRGN\' = \'_NOT_AVAILABLE_\') ) THEN
- new 4: IF (NOT (\'_NOT_AVAILABLE_\' = \'_NOT_AVAILABLE_\') ) THEN
- old 9: mgmt_target.set_agent_tzrgn(crec.target_name, \'&&EM_AGENT_TZRGN\');
- new 9: mgmt_target.set_agent_tzrgn(crec.target_name, \'_NOT_AVAILABLE_\');
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL>
- SQL>
- SQL> @&EM_SQL_ROOT/core/v101050/core_post_creation.sql
- SQL> Rem
- SQL> Rem $Header: core_post_creation.sql 20-dec-2005.05:30:21 rkpandey Exp $
- SQL> Rem
- SQL> Rem core_post_creation.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem core_post_creation.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rkpandey 12/20/05 - Add basic_post_creation
- SQL> Rem neearora 08/11/05 - neearora_backport_10.1.0.5.0_4500972
- SQL> Rem neearora 08/09/05 - Created
- SQL> Rem
- SQL>
- SQL> @&EM_SQL_ROOT/core/v101050/basic/basic_post_creation.sql
- SQL> Rem
- SQL> Rem $Header: basic_post_creation.sql 22-dec-2005.20:28:22 rkpandey Exp $
- SQL> Rem
- SQL> Rem basic_post_creation.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem basic_post_creation.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rkpandey 12/20/05 - Bug 4357890: make oc4j composite
- SQL> Rem rkpandey 12/20/05 - Created
- SQL> Rem
- SQL>
- SQL> BEGIN
- 2
- 3 MGMT_TARGET.add_target_type_properties(p_target_type_in => MGMT_GLOBAL.G_OC4J_TARGET_TYPE,
- 4 p_type_property_list_in =>
- 5 SMP_EMD_NVPAIR_ARRAY (SMP_EMD_NVPAIR (MGMT_GLOBAL.G_IS_COMPOSITE_PROP, \'1\')));
- 6 END;
- 7 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> @&EM_SQL_ROOT/core/v101050/ecm/ecm_post_creation.sql
- SQL> Rem
- SQL> Rem $Header: ecm_post_creation.sql 11-aug-2005.02:44:14 neearora Exp $
- SQL> Rem
- SQL> Rem ecm_post_creation.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem ecm_post_creation.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem neearora 08/11/05 - neearora_backport_10.1.0.5.0_4500972
- SQL> Rem neearora 08/09/05 - Created
- SQL> Rem
- SQL>
- SQL> rem
- SQL> rem Register valid preLoad and postLoad callbacks.
- SQL> rem
- SQL> BEGIN
- 2 MGMT_LOADER.register_pre_load_callback(\'ECM_CT.PRELOAD_CALLBACK\');
- 3 MGMT_LOADER.register_post_load_callback(\'ECM_CT.POSTLOAD_CALLBACK\');
- 4 COMMIT;
- 5 END;
- 6 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL>
- SQL> @&EM_SQL_ROOT/core/v101050/severity_fix_data_3901347.sql
- SQL> Rem
- SQL> Rem $Header: severity_fix_data_3901347.sql 16-aug-2005.04:11:20 shianand Exp $
- SQL> Rem
- SQL> Rem severity_fix_data_3901347.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem severity_fix_data_3901347.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem shianand 08/16/05 - shianand_backport_10.1.0.5.0_3901347_1
- SQL> Rem rpinnama 06/27/05 - Created
- SQL> Rem
- SQL>
- SQL>
- SQL> PROMPT Fixing severity duration (bug 3901347)
- Fixing severity duration (bug 3901347)
- SQL> SELECT to_char(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\') FROM DUAL;
-
- TO_CHAR(SYSDATE,\'YY
- -------------------
- 2014-10-29 20:48:32
-
- 1 row selected.
-
- SQL>
- SQL>
- SQL> DECLARE
- 2 type rawtab IS TABLE OF RAW(16) index by binary_integer ;
- 3 type varchar256tab IS TABLE OF VARCHAR2(256) index by binary_integer ;
- 4 type datetab IS TABLE OF DATE index by binary_integer ;
- 5 type numtab is TABLE of NUMBER index by binary_integer;
- 6
- 7 l_host_resp_stat_guid mgmt_metrics.metric_guid%TYPE;
- 8 l_commit_frequency NUMBER := 50000 ;
- 9
- 10 l_target_guids rawtab ;
- 11 l_metric_guids rawtab ;
- 12 l_collection_tss datetab ;
- 13 l_severity_guids rawtab ;
- 14 l_severity_codes numtab ;
- 15 l_severity_durs numtab ;
- 16
- 17 l_upd_cnt number := 0;
- 18 l_upd_durs numtab;
- 19 l_upd_vguids rawtab;
- 20
- 21 l_calc_duration mgmt_severity.severity_duration%TYPE;
- 22
- 23 l_new_tgt_start NUMBER := 0;
- 24 l_cnt NUMBER := 0;
- 25
- 26 CURSOR avail_severities_cur IS
- 27 SELECT target_guid, metric_guid, collection_timestamp,
- 28 severity_code, severity_duration, severity_guid
- 29 FROM mgmt_severity
- 30 WHERE metric_guid IN (SELECT DISTINCT metric_guid
- 31 FROM mgmt_metrics
- 32 WHERE metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
- 33 AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN)
- 34 AND key_value = \' \'
- 35 ORDER BY target_guid, collection_timestamp, load_timestamp,
- 36 DECODE(severity_code,
- 37 MGMT_GLOBAL.G_SEVERITY_CLEAR, 1,
- 38 MGMT_GLOBAL.G_SEVERITY_WARNING, 3,
- 39 MGMT_GLOBAL.G_SEVERITY_CRITICAL, 4,
- 40 MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
- 41 MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, 6,
- 42 9) DESC;
- 43 l_prev_rec avail_severities_cur%rowtype ;
- 44
- 45 BEGIN
- 46
- 47 BEGIN
- 48 SELECT parameter_value
- 49 INTO l_commit_frequency
- 50 FROM mgmt_parameters
- 51 WHERE parameter_name = \'viol_duration_upgrade_batchsize\' ;
- 52 EXCEPTION
- 53 WHEN NO_DATA_FOUND THEN
- 54 l_commit_frequency := 10000 ;
- 55 END ;
- 56
- 57 OPEN avail_severities_cur;
- 58
- 59 FETCH avail_severities_cur BULK COLLECT INTO
- 60 l_target_guids, l_metric_guids, l_collection_tss, l_severity_codes,
- 61 l_severity_durs, l_severity_guids
- 62 LIMIT l_commit_frequency;
- 63
- 64 IF ( (l_target_guids IS NOT NULL) or (l_target_guids.COUNT > 0) ) THEN
- 65 -- Dummy up a guid, so that it wont match the first target
- 66 l_prev_rec.target_guid := HEXTORAW(\'0000000000000000000000000000\');
- 67
- 68 END IF;
- 69
- 70 WHILE ( (l_target_guids IS NOT NULL) AND (l_target_guids.COUNT > 0) )
- 71 LOOP
- 72
- 73 FOR i IN l_target_guids.FIRST..l_target_guids.LAST
- 74 LOOP
- 75
- 76 l_cnt := l_cnt + 1;
- 77 l_new_tgt_start := 0;
- 78
- 79 IF (l_target_guids(i) != l_prev_rec.target_guid) THEN
- 80 l_new_tgt_start := 1;
- 81 END IF;
- 82
- 83 IF (l_new_tgt_start = 0) THEN
- 84
- 85 l_calc_duration := (l_collection_tss(i) - l_prev_rec.collection_timestamp) * 24;
- 86
- 87 IF ( (l_calc_duration != l_prev_rec.severity_duration) ) THEN
- 88
- 89 l_upd_cnt := l_upd_cnt + 1;
- 90 l_upd_durs(l_upd_cnt) := l_calc_duration;
- 91 l_upd_vguids(l_upd_cnt) := l_prev_rec.severity_guid;
- 92
- 93 END IF;
- 94
- 95 ELSE
- 96
- 97 l_calc_duration := 0;
- 98
- 99 END IF;
- 100
- 101 l_prev_rec.target_guid := l_target_guids(i);
- 102 l_prev_rec.metric_guid := l_metric_guids(i);
- 103 l_prev_rec.collection_timestamp := l_collection_tss(i);
- 104 l_prev_rec.severity_code := l_severity_codes(i);
- 105 l_prev_rec.severity_duration := NVL(l_severity_durs(i), -1);
- 106 l_prev_rec.severity_guid := l_severity_guids(i);
- 107
- 108 END LOOP; -- FOR loop
- 109
- 110 IF (l_upd_cnt > 0) THEN
- 111
- 112 /**
- 113 DBMS_OUTPUT.PUT_LINE(\'Fixing \' || l_upd_cnt || \' violations \');
- 114
- 115 FOR k IN 1..l_upd_cnt
- 116 LOOP
- 117 DBMS_OUTPUT.PUT_LINE(\' \' || k ||
- 118 \' Viol GUID - \' || l_upd_vguids(k) ||
- 119 \' New Dur - \' || ROUND(l_upd_durs(k), 4) );
- 120 END LOOP;
- 121 **/
- 122
- 123 FORALL j IN 1..l_upd_cnt
- 124 UPDATE MGMT_SEVERITY
- 125 SET severity_duration = l_upd_durs(j)
- 126 WHERE severity_guid = l_upd_vguids(j);
- 127
- 128 COMMIT;
- 129
- 130
- 131 END IF;
- 132
- 133
- 134
- 135 FETCH avail_severities_cur BULK COLLECT INTO
- 136 l_target_guids, l_metric_guids, l_collection_tss, l_severity_codes,
- 137 l_severity_durs, l_severity_guids
- 138 LIMIT l_commit_frequency;
- 139
- 140 l_cnt := 0;
- 141 l_upd_cnt := 0;
- 142
- 143 END LOOP; -- WHILE loop
- 144
- 145 CLOSE avail_severities_cur;
- 146
- 147 END;
- 148 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> PROMPT Done fixing severity duration
- Done fixing severity duration
- SQL> SELECT to_char(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\') FROM DUAL;
-
- TO_CHAR(SYSDATE,\'YY
- -------------------
- 2014-10-29 20:48:32
-
- 1 row selected.
-
- SQL>
- SQL>
- SQL>
- SQL> /*
- SQL> ** 25/08/2005 - Commenting out this changes for broken target as few issues
- SQL> ** have been detected in 10.2GC due to this. We will re-visit this once it
- SQL> ** gets stable in 10.2GC.
- SQL> **
- SQL> ** @&EM_SQL_ROOT/core/v101050/target_data_upgrade.sql
- SQL> */
- SQL>
- SQL> @&EM_SQL_ROOT/core/v101050/beacon_data_upgrade.sql
- SQL> Rem
- SQL> Rem $Header: beacon_data_upgrade.sql 23-feb-2006.16:38:01 andyao Exp $
- SQL> Rem
- SQL> Rem beacon_data_upgrade.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2006, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem beacon_data_upgrade.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem andyao 02/23/06 - add website 3.0 response/status metric so
- SQL> Rem 10.1.0.5 OMS can work with 10.2 agent
- SQL> Rem andyao 02/23/06 - Created
- SQL> Rem
- SQL>
- SQL> BEGIN
- 2 EMD_BCN_ADMIN.GEN_BEACON_RESP_METRICS(\'website\');
- 3 COMMIT;
- 4 END;
- 5 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> @&EM_SQL_ROOT/core/v101050/user_model_data_upgrade.sql
- SQL> Rem
- SQL> Rem $Header: user_model_data_upgrade.sql 22-aug-2005.04:37:15 dcawley Exp $
- SQL> Rem
- SQL> Rem user_model_data_upgrade.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2005, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem user_model_data_upgrade.sql - <one-line expansion of the name>
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem <short description of component this file declares/defines>
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem <other useful comments, qualifications, etc.>
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem dcawley 08/22/05 - dcawley_bug-4562785
- SQL> Rem dcawley 08/19/05 - Created
- SQL> Rem
- SQL>
- SQL> Rem
- SQL> Rem Rebuild the flat role grants
- SQL> Rem
- SQL> BEGIN
- 2 FOR role in (SELECT role_name FROM MGMT_ROLES)
- 3 LOOP
- 4 MGMT_USER.UPDATE_FLAT_ROLE_GRANTS(role.role_name);
- 5 COMMIT;
- 6 END LOOP;
- 7 END;
- 8 /
-
- PL/SQL procedure successfully completed.
-
- SQL>
- SQL> @&EM_SQL_ROOT/core/latest/jobTypes_sql/core_jobTypes.sql
- SQL> REM
- SQL> REM THIS FILE IS MACHINE GENERATED: DO NOT EDIT
- SQL> REM
- SQL> REM
- SQL>
- SQL> PROMPT Registering job types
- Registering job types
- SQL>
- SQL> @&EM_SQL_ROOT/core/latest/jobTypes_sql/AddTargetJob.sql
- SQL> REM############################################################################
- SQL> REM
- SQL> REM Copyright (c) 2002, 2003, Oracle Corporation. All rights reserved.
- SQL> REM
- SQL> REM
- SQL> REM PRODUCT
- SQL> REM Oracle Enterprise Manager
- SQL> REM
- SQL> REM THIS FILE IS MACHINE GENERATED. DO NOT
- SQL> REM
- SQL> REM JOB TYPE SQL FOR JOB TYPE: AddTargetJob
- SQL> REM
- SQL> REM#############################################################################
- SQL>
- SQL> SET ECHO OFF
- old 16: :script_name := \'&EM_SQL_ROOT/db/v10102/db_data_upgrade.sql\';
- new 16: :script_name := \'?/sysman/admin/emdrep/sql/db/v10102/db_data_upgrade.sql\';
- old 19: :script_name := \'&EM_SQL_ROOT/db/v101040/db_data_upgrade.sql\';
- new 19: :script_name := \'?/sysman/admin/emdrep/sql/db/v101040/db_data_upgrade.sql\';
- old 22: :script_name := \'&EM_SQL_ROOT/db/v102010/db_data_upgrade.sql\';
- new 22: :script_name := \'?/sysman/admin/emdrep/sql/db/v102010/db_data_upgrade.sql\';
- old 24: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 24: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
-
-
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- Registering job types
- old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
- old 3: :script_name := \'&EM_SQL_ROOT/ias/ias_data_upgrade.sql\';
- new 3: :script_name := \'?/sysman/admin/emdrep/sql/ias/ias_data_upgrade.sql\';
- old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
-
-
- old 2: IF (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- new 2: IF (\'SYSAUX\' = \'CENTRAL\') THEN
- old 3: :script_name := \'&EM_SQL_ROOT/ocs/ocs_data_upgrade.sql\';
- new 3: :script_name := \'?/sysman/admin/emdrep/sql/ocs/ocs_data_upgrade.sql\';
- old 5: :script_name := \'&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql\';
- new 5: :script_name := \'?/sysman/admin/emdrep/sql/core/latest/admin/admin_do_nothing.sql\';
-
-
-
- Submitting DBMS jobs.
- old 5: IF NOT (\'&EM_REPOS_MODE\' = \'CENTRAL\') THEN
- new 5: IF NOT (\'SYSAUX\' = \'CENTRAL\') THEN
-
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP EM 2014-10-29 20:48:38
- DBUA_TIMESTAMP EM VALID 2014-10-29 20:48:38
-
-
-
-
- .. loading the Expression Filter/BRM Java library
- .. creating Rule Manager catalog views
- .. creating Rule Manager package/type implementations
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
- No errors.
-
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP RUL 2014-10-29 20:48:43
- DBUA_TIMESTAMP RUL VALID 2014-10-29 20:48:43
-
-
-
-
-
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP UPGRD_END 2014-10-29 20:48:43
- .
- Oracle Database 10.2 Upgrade Status Utility 10-29-2014 20:48:43
- .
- Component Status Version HH:MM:SS
- Oracle Database Server VALID 10.2.0.4.0 00:06:48
- JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:18
- Oracle XDK VALID 10.2.0.4.0 00:00:18
- Oracle Database Java Packages VALID 10.2.0.4.0 00:00:16
- Oracle Text VALID 10.2.0.4.0 00:00:18
- Oracle XML Database VALID 10.2.0.4.0 00:01:12
- Oracle Workspace Manager VALID 10.2.0.4.3 00:00:33
- Oracle Data Mining VALID 10.2.0.4.0 00:00:17
- OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:15
- OLAP Catalog VALID 10.2.0.4.0 00:00:40
- Oracle OLAP API VALID 10.2.0.4.0 00:00:29
- Oracle interMedia VALID 10.2.0.4.0 00:02:22
- Spatial VALID 10.2.0.4.0 00:01:02
- Oracle Expression Filter VALID 10.2.0.4.0 00:00:07
- Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:26
- Oracle Rule Manager VALID 10.2.0.4.0 00:00:05
- .
- Total Upgrade Time: 00:18:35
- DOC>#######################################################################
- DOC>#######################################################################
- DOC>
- DOC> The above PL/SQL lists the SERVER components in the upgraded
- DOC> database, along with their current version and status.
- DOC>
- DOC> Please review the status and version columns and look for
- DOC> any errors in the spool log file. If there are errors in the spool
- DOC> file, or any components are not VALID or not the current version,
- DOC> consult the Oracle Database Upgrade Guide for troubleshooting
- DOC> recommendations.
- DOC>
- DOC> Next shutdown immediate, restart for normal operation, and then
- DOC> run utlrp.sql to recompile any invalid application objects.
- DOC>
- DOC>#######################################################################
- DOC>#######################################################################
- DOC>#
正常启动数据库,编译失效的对象。
点击(此处)折叠或打开
- SQL> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup;
- ORACLE instance started.
-
- Total System Global Area 599785472 bytes
- Fixed Size 2085776 bytes
- Variable Size 226495600 bytes
- Database Buffers 364904448 bytes
- Redo Buffers 6299648 bytes
- Database mounted.
- Database opened.
- SQL> @?/rdbms/admin/utlrp.sql
-
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP UTLRP_BGN 2014-10-29 20:59:41
- DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
- DOC> objects in the database. Recompilation time is proportional to the
- DOC> number of invalid objects in the database, so this command may take
- DOC> a long time to execute on a database with a large number of invalid
- DOC> objects.
- DOC>
- DOC> Use the following queries to track recompilation progress:
- DOC>
- DOC> 1. Query returning the number of invalid objects remaining. This
- DOC> number should decrease with time.
- DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
- DOC>
- DOC> 2. Query returning the number of objects compiled so far. This number
- DOC> should increase with time.
- DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
- DOC>
- DOC> This script automatically chooses serial or parallel recompilation
- DOC> based on the number of CPUs available (parameter cpu_count) multiplied
- DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
- DOC> On RAC, this number is added across all RAC nodes.
- DOC>
- DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
- DOC> recompilation. Jobs are created without instance affinity so that they
- DOC> can migrate across RAC nodes. Use the following queries to verify
- DOC> whether UTL_RECOMP jobs are being created and run correctly:
- DOC>
- DOC> 1. Query showing jobs created by UTL_RECOMP
- DOC> SELECT job_name FROM dba_scheduler_jobs
- DOC> WHERE job_name like \'UTL_RECOMP_SLAVE_%\';
- DOC>
- DOC> 2. Query showing UTL_RECOMP jobs that are running
- DOC> SELECT job_name FROM dba_scheduler_running_jobs
- DOC> WHERE job_name like \'UTL_RECOMP_SLAVE_%\';
- DOC>#
-
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP UTLRP_END 2014-10-29 21:00:30
- DOC> The following query reports the number of objects that have compiled
- DOC> with errors (objects that compile with errors have status set to 3 in
- DOC> obj$). If the number is higher than expected, please examine the error
- DOC> messages reported with each object (using SHOW ERRORS) to see if they
- DOC> point to system misconfiguration or resource constraints that must be
- DOC> fixed before attempting to recompile these objects.
- DOC>#
-
- OBJECTS WITH ERRORS
- -------------------
- 0
- DOC> The following query reports the number of errors caught during
- DOC> recompilation. If this number is non-zero, please query the error
- DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
- DOC> are due to misconfiguration or resource constraints that must be
- DOC> fixed before objects can compile successfully.
- DOC>#
-
- ERRORS DURING RECOMPILATION
- ---------------------------
- 0
检测升级后情况,重新修改兼容性参数
点击(此处)折叠或打开
- SQL> select comp_name , version, status from dba_registry;
-
- COMP_NAME VERSION STATUS
- ------------------------------ ------------------------------ -----------
- Oracle Enterprise Manager 10.2.0.4.0 VALID
- Spatial 10.2.0.4.0 VALID
- Oracle interMedia 10.2.0.4.0 VALID
- OLAP Catalog 10.2.0.4.0 VALID
- Oracle XML Database 10.2.0.4.0 VALID
- Oracle Text 10.2.0.4.0 VALID
- Oracle Expression Filter 10.2.0.4.0 VALID
- Oracle Rule Manager 10.2.0.4.0 VALID
- Oracle Workspace Manager 10.2.0.4.3 VALID
- Oracle Data Mining 10.2.0.4.0 VALID
- Oracle Database Catalog Views 10.2.0.4.0 VALID
- Oracle Database Packages and T 10.2.0.4.0 VALID
- ypes
-
- JServer JAVA Virtual Machine 10.2.0.4.0 VALID
- Oracle XDK 10.2.0.4.0 VALID
- Oracle Database Java Packages 10.2.0.4.0 VALID
- OLAP Analytic Workspace 10.2.0.4.0 VALID
- Oracle OLAP API 10.2.0.4.0 VALID
-
- SQL> show parameter comp
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- compatible string 10.2.0.1.0
- nls_comp string
- plsql_compiler_flags string INTERPRETED, NON_DEBUG
- plsql_v2_compatibility boolean FALSE
-
- 确认在新的版本数据库下应用的没有问题,那么
-
- alter system set compatible=\'10.2.0.4.0\' scope=spfile;
-
- 如果是滚动升级主备库切换之前不要设置该参数,否则日志不对应。
-
- SQL> show parameter comp
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- compatible string 10.2.0.4.0
- nls_comp string
- plsql_compiler_flags string INTERPRETED, NON_DEBUG
- plsql_v2_compatibility boolean FALSE
升级回退【没做】
点击(此处)折叠或打开
- SQL> STARTUP DOWNGRADE
- SQL> SPOOL downgrade.log
- SQL> @catdwgrd.sql(10.2.10 运行的是这个,而 10.1 降级用的是 d92000.sql,即 dold_release.sql)
- Sql>spool off
- Sql>shutdown immediate
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1314342/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-1314342/