oracle 补丁 mysql_11.2.0.2补丁集安装体验

使用了Out-of-place Upgrade方式，安装图形界面沿袭了11.2.0.1的风格:

升级数据字典流程:

[maclean@rh2 ~]$ cp /s01/11gdb/dbs/*SPROD /s01/product/11.2.0/dbhome_1/dbs

[maclean@rh2 ~]$ cd /s01/product/11.2.0/dbhome_1/dbs

[maclean@rh2 dbs]$ ls

init.ora lkSPROD orapwSPROD spfileSPROD.ora

[maclean@rh2 dbs]$ rm lkSPROD

[maclean@rh2 dbs]$ export ORACLE_HOME=/s01/product/11.2.0/dbhome_1

[maclean@rh2 dbs]$ export PATH=/s01/product/11.2.0/dbhome_1/bin:$PATH

[maclean@rh2 dbs]$ export ORACLE_SID=SPROD

[maclean@rh2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 15 21:21:42 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2227032 bytes

Variable Size 1023411368 bytes

Database Buffers 620756992 bytes

Redo Buffers 7122944 bytes

Database mounted.

Database opened.

@?/rdbms/admin/catupgrd

.........

/* catupgrd脚本运行一如既往地耗费时间 */

SQL> startup;

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2227032 bytes

Variable Size 1023411368 bytes

Database Buffers 620756992 bytes

Redo Buffers 7122944 bytes

Database mounted.

Database opened.

/* 尝试编译失效对象 */

SQL> @?/rdbms/admin/utlrp

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2010-09-15 22:02:54

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>#

PL/SQL procedure successfully completed.

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2010-09-15 22:08:12

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

/* 通过查询registry$history视图我们可以了解数据字典的升级历史 */

SQL> col namespace for a20;

SQL> col version for a15;

SQL> col comments for a40;

SQL> select namespace,version,comments from registry$history;

NAMESPACE VERSION COMMENTS

-------------------- --------------- ----------------------------------------

view invalidation

SERVER 11.2.0.2.0 Upgraded from 11.2.0.1.0

SQL> show parameter optimizer_features_enable

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_features_enable string 11.2.0.2

SQL> create table optimizer_features_11202 as

2 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

3 FROM SYS.x$ksppi x, SYS.x$ksppcv y

4 WHERE x.inst_id = USERENV ('Instance')

5 AND y.inst_id = USERENV ('Instance')

6 AND x.indx = y.indx;

Table created.

SQL> alter system set optimizer_features_enable='11.2.0.1';

System altered.

SQL> create table optimizer_features_11201 as

2 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

3 FROM SYS.x$ksppi x, SYS.x$ksppcv y

4 WHERE x.inst_id = USERENV ('Instance')

5 AND y.inst_id = USERENV ('Instance')

6 AND x.indx = y.indx;

Table created.

SQL> col name for a40

SQL> col "11.2.0.1" for a10;

SQL> col "11.2.0.2" for a10;

SQL> col describ for a50;

SQL> set linesize 300;

/* 通过隐藏参数我们可以了解，11.2.0.2中又启用了部分优化器特性，horrible news! */

SQL> select a.name, a.value "11.2.0.1" , b.value "11.2.0.2",a.describ

2 from optimizer_features_11201 a, optimizer_features_11202 b

3 where a.name = b.name

4 and a.value != b.value;

NAME 11.2.0.1 11.2.0.2 DESCRIB

---------------------------------------- ---------- ---------- --------------------------------------------------

optimizer_features_enable 11.2.0.1 11.2.0.2 optimizer plan compatibility parameter

_optimizer_undo_cost_change 11.2.0.1 11.2.0.2 optimizer undo cost change

_optimizer_extended_stats_usage_control 224 192 controls the optimizer usage of extended stats

_px_partition_scan_enabled FALSE TRUE enables or disables parallel partition-based scan

_optimizer_false_filter_pred_pullup FALSE TRUE optimizer false predicate pull up transformation

_optimizer_enable_table_lookup_by_nl FALSE TRUE consider table lookup by nl transformation

6 rows selected.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[maclean@rh2 OPatch]$ ./opatch lsinventory -detail

Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1

Copyright (c) 2009, Oracle Corporation. All rights reserved.

Oracle Home : /s01/product/11.2.0/dbhome_1

Central Inventory : /home/maclean/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.1

OUI version : 11.2.0.2.0

OUI location : /s01/product/11.2.0/dbhome_1/oui

Log file location : /s01/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2010-09-15_22-13-07PM.log

Patch history file: /s01/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /s01/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2010-09-15_22-13-07PM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0

There are 1 products installed in this Oracle Home.

Installed Products (136):

Agent Required Support Files 10.2.0.4.3

Assistant Common Files 11.2.0.2.0

Bali Share 1.1.18.0.0

Buildtools Common Files 11.2.0.2.0

Character Set Migration Utility 11.2.0.2.0

Cluster Verification Utility Common Files 11.2.0.2.0

Database Configuration and Upgrade Assistants 11.2.0.2.0

Database SQL Scripts 11.2.0.2.0

Database Workspace Manager 11.2.0.2.0

Deinstallation Tool 11.2.0.2.0

Enterprise Edition Options 11.2.0.2.0

Enterprise Manager Agent 10.2.0.4.3

Enterprise Manager Agent Core Files 10.2.0.4.3

Enterprise Manager Common Core Files 10.2.0.4.3

Enterprise Manager Common Files 10.2.0.4.3

Enterprise Manager Database Plugin -- Agent Support 11.2.0.2.0

Enterprise Manager Database Plugin -- Repository Support 11.2.0.2.0

Enterprise Manager Grid Control Core Files 10.2.0.4.3

Enterprise Manager plugin Common Files 11.2.0.2.0

Enterprise Manager Repository Core Files 10.2.0.4.3

Exadata Storage Server 11.2.0.1.0

Expat libraries 2.0.1.0.1

Generic Connectivity Common Files 11.2.0.2.0

HAS Common Files 11.2.0.2.0

HAS Files for DB 11.2.0.2.0

Installation Common Files 11.2.0.2.0

Installation Plugin Files 11.2.0.2.0

Installer SDK Component 11.2.0.2.0

JAccelerator (COMPANION) 11.2.0.2.0

LDAP Required Support Files 11.2.0.2.0

OLAP SQL Scripts 11.2.0.2.0

Oracle 11g Warehouse Builder Required Files 11.2.0.2.0

Oracle Advanced Security 11.2.0.2.0

Oracle Application Express 11.2.0.2.0

Oracle Call Interface (OCI) 11.2.0.2.0

Oracle Clusterware RDBMS Files 11.2.0.2.0

Oracle Code Editor 1.2.1.0.0I

Oracle Configuration Manager 10.3.3.1.1

Oracle Configuration Manager Client 10.3.2.1.0

Oracle Configuration Manager Deconfiguration 10.3.1.0.0

Oracle Containers for Java 11.2.0.2.0

Oracle Core Required Support Files 11.2.0.2.0

Oracle Data Mining RDBMS Files 11.2.0.2.0

Oracle Database 11g 11.2.0.2.0

Oracle Database 11g 11.2.0.2.0

Oracle Database 11g Multimedia Files 11.2.0.2.0

Oracle Database Deconfiguration 11.2.0.2.0

Oracle Database Gateway for ODBC 11.2.0.2.0

Oracle Database User Interface 2.2.13.0.0

Oracle Database Utilities 11.2.0.2.0

Oracle Database Vault J2EE Application 11.2.0.2.0

Oracle Database Vault option 11.2.0.2.0

Oracle DBCA Deconfiguration 11.2.0.2.0

Oracle Display Fonts 9.0.2.0.0

Oracle Enterprise Manager Console DB 11.2.0.2.0

Oracle Extended Windowing Toolkit 3.4.47.0.0

Oracle Globalization Support 11.2.0.2.0

Oracle Globalization Support 11.2.0.2.0

Oracle Help For Java 4.2.9.0.0

Oracle Help for the Web 2.0.14.0.0

Oracle Ice Browser 5.2.3.6.0

Oracle Internet Directory Client 11.2.0.2.0

Oracle Java Client 11.2.0.2.0

Oracle JDBC Server Support Package 11.2.0.2.0

Oracle JDBC/OCI Instant Client 11.2.0.2.0

Oracle JDBC/THIN Interfaces 11.2.0.2.0

Oracle JFC Extended Windowing Toolkit 4.2.36.0.0

Oracle JVM 11.2.0.2.0

Oracle Label Security 11.2.0.2.0

Oracle LDAP administration 11.2.0.2.0

Oracle Locale Builder 11.2.0.2.0

Oracle Message Gateway Common Files 11.2.0.2.0

Oracle Multimedia 11.2.0.2.0

Oracle Multimedia Annotator 11.2.0.2.0

Oracle Multimedia Client Option 11.2.0.2.0

Oracle Multimedia Java Advanced Imaging 11.2.0.2.0

Oracle Multimedia Locator 11.2.0.2.0

Oracle Multimedia Locator RDBMS Files 11.2.0.2.0

Oracle Net 11.2.0.2.0

Oracle Net Listener 11.2.0.2.0

Oracle Net Required Support Files 11.2.0.2.0

Oracle Net Services 11.2.0.2.0

Oracle Netca Client 11.2.0.2.0

Oracle Notification Service 11.2.0.2.0

Oracle Notification Service (eONS) 11.2.0.2.0

Oracle ODBC Driver 11.2.0.2.0

Oracle ODBC Driverfor Instant Client 11.2.0.2.0

Oracle OLAP 11.2.0.2.0

Oracle OLAP API 11.2.0.2.0

Oracle OLAP RDBMS Files 11.2.0.2.0

Oracle One-Off Patch Installer 11.2.0.0.2

Oracle Partitioning 11.2.0.2.0

Oracle Programmer 11.2.0.2.0

Oracle Quality of Service Management (Client) 11.2.0.2.0

Oracle RAC Deconfiguration 11.2.0.2.0

Oracle RAC Required Support Files-HAS 11.2.0.2.0

Oracle Real Application Testing 11.2.0.2.0

Oracle Recovery Manager 11.2.0.2.0

Oracle Security Developer Tools 11.2.0.2.0

Oracle Spatial 11.2.0.2.0

Oracle SQL Developer 11.2.0.2.0

Oracle Starter Database 11.2.0.2.0

Oracle Text 11.2.0.2.0

Oracle Text Required Support Files 11.2.0.2.0

Oracle UIX 2.2.24.6.0

Oracle Universal Connection Pool 11.2.0.2.0

Oracle Universal Installer 11.2.0.2.0

Oracle USM Deconfiguration 11.2.0.2.0

Oracle Wallet Manager 11.2.0.2.0

Oracle XML Development Kit 11.2.0.2.0

Oracle XML Query 11.2.0.2.0

Parser Generator Required Support Files 11.2.0.2.0

Perl Interpreter 5.10.0.0.1

Perl Modules 5.10.0.0.1

PL/SQL 11.2.0.2.0

PL/SQL Embedded Gateway 11.2.0.2.0

Platform Required Support Files 11.2.0.2.0

Precompiler Common Files 11.2.0.2.0

Precompiler Required Support Files 11.2.0.2.0

Provisioning Advisor Framework 10.2.0.4.3

RDBMS Required Support Files 11.2.0.2.0

RDBMS Required Support Files for Instant Client 11.2.0.2.0

RDBMS Required Support Files Runtime 11.2.0.2.0

regexp 2.1.9.0.0

Required Support Files 11.2.0.2.0

Sample Schema Data 11.2.0.2.0

Secure Socket Layer 11.2.0.2.0

SQL*Plus 11.2.0.2.0

SQL*Plus Files for Instant Client 11.2.0.2.0

SQL*Plus Required Support Files 11.2.0.2.0

SQLJ Runtime 11.2.0.2.0

SSL Required Support Files for InstantClient 11.2.0.2.0

Sun JDK 1.5.0.24.08

XDK Required Support Files 11.2.0.2.0

XML Parser for Java 11.2.0.2.0

XML Parser for Oracle JVM 11.2.0.2.0

There are 136 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.