huojia

Patch availability

The following table lists the availability of Oracle patch sets and patch set exceptions for Microsoft Windows (32-Bit, 64-Bit x64 and 64-Bit Itanium). Where applicable target release dates have been included, these dates should be considered to be provisional but can be used as a guideline for planning although they may change at any time.

For additional status information about patch sets please refer to Note 161818.1 . For information about which versions new patches can be requested upon please refer to Note 209768.1 and for details of patch set availability and grace periods please refer to Note 742060.1.

Customers wishing to directly access the following patches should click on the related Patch: link.

32-Bit Patches :

64-Bit x64 Patches :

64-Bit Itanium Patches :

希望11g R2能好用些

1/ 重启Instance后，执行 dbms_scheduler.dropjob 失败

2/ 11g 上面 menthod 4 Pro*C 程序做Fetch失败

3/ Oracle Text Rich Text Conversation fail

We’re working on windows 64bit support for our 32bit application. But we found there is no extproc32 and lib32 in the ORACLE_HOME. Then I open

SR Number
7410009.992

and get the following informaion from Oracle.

—————————————————————————–

QUESTION
========
HOW TO RUN 32-BIT PRO*C EXTERNAL PROCEDURE ON 64-BIT WINDOWS ?
ANSWER
======
Hi,
It is not possible To Call 32Bit Libraries Using External Procedure Agent Extproc On Windows
64Bit.
Because there is no 32bit "extproc" agent available on Windows 64bit.
Oracle currently does not support running the 32-Bit versions of its database software on Windows 64-Bit (AMD64 / EM64T, Itanium)
As extproc is part of the server there is no 32bit of it available for Windows 64bit.
If you try to run a 32bit application, you will get the following error :
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
Please note that there is already an enhancement request is filed with Oracle
development for the same.

安装Statspack
创建Statspack使用的表空间

C:\oracle\ora10g\RDBMS\ADMIN>sqlplus sys/fvt6eip as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 8 01:36:24 2009
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace perfstat datafile 'c:\oracle\oradata\perfstat.dbf'
size 500M extent management local;
Tablespace created.

安装Statspack:执行前，确认Temp Database的名字，本例使用temp.

SQL> @spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: fvt6eip
fvt6eip
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
ICMLFQ32                       PERMANENT
ICMLNF32                       PERMANENT
ICMLSNDX                       PERMANENT
ICMLSTXT                       PERMANENT
ICMSFQ04                       PERMANENT
ICMVFQ04                       PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: perfstat
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *
Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: temp
Using tablespace temp as PERFSTAT temporary tablespace.
...........
...........
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

抓取Statspack Snapshot

SQL> execute statspack.snap
PL/SQL procedure successfully completed.

确认抓取Snapshot步骤成功

SQL> select snap_id from stats$snapshot;
SNAP_ID
----------
1
1 row selected.

生成Statsnap 报告:至少有两次的Snapshot 才能生成报告, 输入相同的snapshotid 会导致ORA-20200: End Snapshot Id 1 must be greater than Begin Snapshot Id 1。
生成第二个snapshot

SQL> execute statspack.snap
PL/SQL procedure successfully completed.

产生报告@spreport.sql ,出入开始的snapsshot和结束的snapshot，以及report名称即可

SQL> alter system set remote_os_authent = true scope=spfile;

SQL> startup force
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Solution

To remove the deprecated parameter from the spfile issue:

Oracle 11g 使用了Automatic Diagnostic Repository (ADR)来存储所有的诊断日志信息(trace, alert logs). ADR Base(adr_base)的位置通过参数 “diagnostic_dest” 控制. 参数 background_dump_dest, core_dump_dest, user_dump_dest (in previous releases) 被 deprecate 并被一个单独的参数 “diagnostic_dest” 所取代.

如果强制使用user_dump_dest , backgroud_dump_dest,coreate_dump_dest，在 C:\app\Administrator\admin\icmnlsdb\bdump\alter.log 会看到如下警告信息

Dump file c:\app\administrator\admin\icmnlsdb\bdump\alert_icmnlsdb.log

Tue Jan 06 10:58:52 2009

ORACLE V11.1.0.6.0 – Production vsnsta=0

vsnsql=15 vsnxtr=3

Windows NT Version V5.2 Service Pack 2

CPU : 1 – type 586, 1 Physical Cores

Process Affinity : 0×00000000

Memory (Avail/Total): Ph:898M/2047M, Ph+PgF:2100M/3943M, VA:1230M/2047M

Tue Jan 06 10:58:52 2009

WARNING: The background_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the background_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the background logs and traces is c:\app\administrator\diag\rdbms\icmnlsdb\icmnlsdb\trace

Tue Jan 06 10:58:52 2009

WARNING: The user_dump_dest init.ora parameter has been deprecated.

WARNING: Please remove the user_dump_dest parameter from the init.ora file.

WARNING: The diagnostic_dest init.ora parameter now determines the location of the diagnostic data

WARNING: The new location for the user logs and traces is c:\app\administrator\diag\rdbms\icmnlsdb\icmnlsdb\trace

ADR Base的默认位置与系统环境变量$ORACLE_BASE 一致。如果 “diagnostic_dest”在spfile中被设置，那么这个值成为ADR的位置。否则，Oracle默认使用$ORACLE_BASE值作为ADR Base的位置。

ADR home (adr_home)是一个通用的目录结果，用以存储 trace files，alter logs，core files 和incident information (instance specific)。 <adr_home> 总是位于 <adr_base>下. <adr_home>的地址是: <ADR_BASE>/diag/rdbms/<DB_name>/<instance_id>.

例如：

SQL> show parameter diag

NAME TYPE VALUE

———————————— ———– ——————————

diagnostic_dest string C:\APP\ADMINISTRATOR

The trace file location of icmnlsdb is C:\app\Administrator\diag\rdbms\icmnlsdb\icmnlsdb

Shared Pool Tunnign advice

SQL> desc v$shared_pool_advice;

Name                                      Null?    Type

—————————————– ——– —————————-

SHARED_POOL_SIZE_FOR_ESTIMATE                      NUMBER

SHARED_POOL_SIZE_FACTOR                            NUMBER

ESTD_LC_SIZE                                       NUMBER

ESTD_LC_MEMORY_OBJECTS                             NUMBER

ESTD_LC_TIME_SAVED                                 NUMBER

ESTD_LC_TIME_SAVED_FACTOR                          NUMBER

ESTD_LC_LOAD_TIME                                  NUMBER

ESTD_LC_LOAD_TIME_FACTOR                           NUMBER

ESTD_LC_MEMORY_OBJECT_HITS                         NUMBER

SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE,SHARED_POOL_SIZE_FACTOR,ESTD_LC_TIME_SAVED from v$shared_pool_advice;

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED

—————————– ———————– ——————

120                   .6667              16457

140                   .7778              16494

160                   .8889              16502

180                       1              16502

200                  1.1111              16503

220                  1.2222              16503

240                  1.3333              16503

260                  1.4444              16506

280                  1.5556              16509

300                  1.6667              16509

320                  1.7778              16512

340                  1.8889              16512

360                       2              16512

13 rows selected.

当前Shared Pool大小180M， 第一行表示120M时候的工作时间，最后一行表示2倍360M时候的工作时间。对当前数据库没明显需要调整的地方。

Database cache advice:

SQL> desc v$db_cache_advice;

Name                                      Null?    Type

—————————————– ——– —————————-

ID                                                 NUMBER

NAME                                               VARCHAR2(20)

BLOCK_SIZE                                         NUMBER

ADVICE_STATUS                                      VARCHAR2(3)

SIZE_FOR_ESTIMATE                                  NUMBER

SIZE_FACTOR                                        NUMBER

BUFFERS_FOR_ESTIMATE                               NUMBER

ESTD_PHYSICAL_READ_FACTOR                          NUMBER

ESTD_PHYSICAL_READS                                NUMBER

ESTD_PHYSICAL_READ_TIME                            NUMBER

ESTD_PCT_OF_DB_TIME_FOR_READS                      NUMBER

ESTD_CLUSTER_READS                                 NUMBER

ESTD_CLUSTER_READ_TIME                             NUMBER

SQL> select name,size_for_estimate, size_factor, estd_physical_reads

2  from v$db_cache_advice;

NAME                 SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS

——————– —————– ———– ——————-

DEFAULT                             36       .0918               45692

DEFAULT                             72       .1837               38220

DEFAULT                            108       .2755               34390

DEFAULT                            144       .3673               34297

DEFAULT                            180       .4592               34297

DEFAULT                            216        .551               34297

DEFAULT                            252       .6429               34297

DEFAULT                            288       .7347               34297

DEFAULT                            324       .8265               34297

DEFAULT                            360       .9184               34297

DEFAULT                            392           1               34297

DEFAULT                            396      1.0102               34297

DEFAULT                            432       1.102               34297

DEFAULT                            468      1.1939               34297

DEFAULT                            504      1.2857               34297

DEFAULT                            540      1.3776               34297

DEFAULT                            576      1.4694               34297

DEFAULT                            612      1.5612               34297

DEFAULT                            648      1.6531               34297

DEFAULT                            684      1.7449               34297

DEFAULT                            720      1.8367               34297

21 rows selected.

当前db_cache大小360M， 第一行表示36M时候的工作时间，最后一行表720M时候的工作时间。对当前数据库db_cache<70M性能下降明显。

Oracle confirmed  Bug 7205619 for extproc32 missing on HP-Unix.

Workaround:
Manually append text “-o $@” to macro “REDEFINES32″ in file $ORACLE_HOME/rdbms/lib/env_rdbms.mk
REDEFINES32=LIBDIR=lib32 LDFLAGS=’$$(LDFLAGS32) -o $@ ’
Perform another relink as below :-
$relink all

• National Language Support 即Globalization 分为5个 levels，这个排序是按照parameter的作用倒序排列的。简单说，一个statement的NLS设置，可以覆盖之前的4个级别的设置。

• The database
• The instance
• The client environment
• The session
• The statement

• 如果想获得当前DB运行时的NLS 设置可以看 v$nls_parameters 试图。参数的含义可以在Oracle 文档库很容易查到含义。

SQL> select *  from v$nls_parameters;

PARAMETER                      VALUE
—————————— —————————-
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               UTF8
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

• 针对以上提高的五个level可以分别看其对应的table来看设置，以session举例。

select ’session’loc,parameter,value from nls_session_parameters;

• NLS所有有效的value可以通过查询NLS_VALIDE_VALUES获得。
  SQL> select * from v$nls_valid_values where parameter=’TERRITORY’;
  PARAMETER VALUE
  —————————— ——————————
  TERRITORY AMERICA
  TERRITORY UNITED KINGDOM
  TERRITORY GERMANY
  <output abbreviated…>
  TERRITORY SERBIA AND MONTENEGRO
  TERRITORY ARGENTINA
  TERRITORY ECUADOR
  TERRITORY PHILIPPINES
• 如果想了解更多Oracle NLS和字符集的基本知识，可以参看Eygle的文章《 字符集问题的初步探讨》