11.2.0.2补丁集安装体验

使用了Out-of-place Upgrade方式，安装图形界面沿袭了11.2.0.1的风格:
升级数据字典流程:
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
[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.