opatch java.lang.OutOfMemoryError:Java heap space错误一例

2011/03/11 by 1 Comment

一套11.1.0.6的RAC系统在在使用opatch工具时出现了OutOfMemoryError: Java heap space错误，其错误日志如下:

opatch lsinventory
Invoking OPatch 11.1.0.6.2

Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/oracle/db/11.1.0
Central Inventory : /u01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /u01/oracle/db/11.1.0/oui
Log file location : /u01/oracle/db/11.1.0/cfgtoollogs/opatch/opatch2011-03-11_03-05-22AM.log

Lsinventory Output file location : /u01/oracle/db/11.1.0/cfgtoollogs/opatch/lsinv/lsinventory2011-03-11_03-05-22AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 11g                                                  11.1.0.6.0
Oracle Database 11g Patch Set 1                                      11.1.0.7.0

Interim patches (3) :

Patch  8496830      : applied on Wed Mar 30 07:11:26 CDT 2011
   Created on 27 Aug 2009, 01:59:52 hrs PST8PDT
   Bugs fixed:
     8496830

Patch  7327166      : applied on Wed Oct 20 11:25:00 CDT 2010
   Created on 31 Dec 2008, 10:49:56 hrs PST8PDT
   Bugs fixed:
     7327166

Patch  7253531      : applied on Wed Jul 28 14:45:44 CDT 2010
   Created on 24 Nov 2008, 03:52:49 hrs PST8PDT
   Bugs fixed:
     7253531
Exception in thread "Thread-7" Exception in thread "Thread-3" java.lang.OutOfMemoryError: Java heap space
java.lang.OutOfMemoryError: Java heap space

事后发现是由于opatch期间1号节点无法使用ssh服务登陆到其他节点上引起的，修复该故障后问题消失了。

另外提一句我们建议使用最新版本的opatch工具来管理数据库补丁，最新版本的opatch工具可以从<OPatch – Where Can I Find the Latest Version of OPatch?>下载到！

Filed Under: Oracle, Oracle补丁patch Tagged With: , ,

DEFERRED_SEGMENT_CREATION

2011/03/11 by Leave a Comment

deferred_segment_creation 延迟段创建是11.2的新特性， 当创建一个 空表或者空分区时，实际的表段table segment被延迟到第一行数据插入的时候。
该功能通过DEFERRED_SEGMENT_CREATION参数启用，默认为TRUE.

由DEFERRED_SEGMENT_CREATION启用的新的空间分配方式的优势在于：

若系统安装后存在大量空表，而这些空表将从不会加载任何数据；假设一个空表占用64k(db_block_size=8,一个extent)的空间，则若存在一万张这样的空表
就要消耗625M的空间

当应用程序初次安装需要创建大量空表时，因为延迟了数据段创建的过程，所以可以节约大量时间
当向这些空表或者空分区插入第一行数据时，对应的表、LOb字段和索引的segment段会被创建。
在段创建过程中，相关表上的游标将失效invalidation。 官方认为在这里游标失效对性能的影响很小。
而且需要注意的使用deferred_segment_creation=true的情况下，更应当注意表空间的使用率，说不定哪一天这些空表全被用上了，哪就有点老母鸡变鸭了。

可以通过dba_tables、dba_indexes视图的SEGMENT_CREATED 字段确定该对象的 段是否被创建了。

SQL> select distinct segment_created from dba_tables;

SEG
—
NO
YES
N/A

 

用户可以通过2种方式来控制段segment的创建：

1.通过在session/system级别设置 DEFERRED_SEGMENT_CREATION 参数:

SQL> alter system set DEFERRED_SEGMENT_CREATION=false;

System altered.

SQL> alter session set DEFERRED_SEGMENT_CREATION=false;

Session altered.
2. 通过创建表create table时指定SEGMENT CREATION子句来控制：

SEGMENT CREATION DEFERRED 若如此指定，则延迟段的创建，在11gR2中为默认表现
SEGMENT CREATION IMMEDIATE 若如此指定，则不会延迟段的创建，回归到11gR2之前的默认表现
注意对于 延迟段创建的表可以通过alter table..move来使之立即生成segment
但是目前无法做到控制相关依赖对象例如索引的段创建，这些从属对象继承他们父对象表的属性。

 

也可以通过alter table allocate extent来促使创建段：

SQL> conn maclean/oracle
Connected.
SQL>
SQL> create table mac_defer(t1 int) tablespace users;

Table created.

SQL> select segment_created from dba_tables where table_name=’MAC_DEFER’;

SEG
—
NO

SQL> alter table mac_defer allocate extent (size 1M);

Table altered.

SQL> select segment_created from dba_tables where table_name=’MAC_DEFER’;

SEG
—
YES

 

 
该deferred_segment_creation特性的缺点是：

1. 由于要在第一次INSERT或其他加载数据方式时才产生segment段，而段的创建需要在表空间上分配空间allocate space，若短期内
大量空表存在插入的需求，则可能在短期内出现空间分配争用

2. deferred_segment_creation引入了少量的BUG.

以下是11.2.0.3上存在的一些bug,部分在psu或者bp中修复了：

NB Bug Fixed Description
15866428 11.2.0.4, 12.1.0.0 ORA-14766 / ORA-14403 during concurrent partition maintenance
14252187 12.1.0.0 ORA-600 [qesmaGetTblSeg1] from deferred segment creation in RAC
13986244 11.2.0.3.BP14, 11.2.0.4, 12.1.0.0 Various ORA-600 seen with deferred segment creation in RAC
13611310 12.1.0.0 Parallel DML with LOBs fails with ORA-7445 [qesmaGetFromLocalOrQCCache]
12614714 11.2.0.4, 12.1.0.0 ORA-1950 occurs when executing DML after EXCHANGE PARTITION and DROP USER
13649031 11.2.0.3.4, 11.2.0.3.BP06, 11.2.0.4, 12.1.0.0 ORA-10637 occurs on SHRINK of a partitioned table with deferred segments
13497523 11.2.0.3.BP15, 11.2.0.4, 12.1.0.0 Errors from SQLLDR loads into non-partitioned tables with deferred segment creation
* 13326736 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3.3, 11.2.0.3.BP05, 11.2.0.4, 12.1.0.0 Dictionary corruption / ORA-959 due to DROP TABLESPACE
12535346 11.2.0.3.3, 11.2.0.3.BP07, 12.1.0.0 ORA-7445 [kxccexi] using referential integrity constraints with deferred segments or interval partitions
12358753 12.1.0.0 INDEX_STATS has wrong values for ANALYZE of deferred segmentindexes
11930350 12.1.0.0 Deadlock / undetected FK violation from DML on REFERENCE partitioned table
建议：

1. 对于存在较多空表或空分区且存在空间压力的，对性能、响应时间没有太高要求的系统可以考虑使用该特性
2. 对对性能、响应时间有较高要求的库建议关闭该特性，deferred_segment_creation=false

 

SQL> show parameter defer

NAME TYPE
———————————— ———————-
VALUE
——————————
deferred_segment_creation boolean
TRUE
SQL>
SQL> create table Maclean_nonpartition (“OBJECT_ID” NUMBER) ;

表已创建。

SQL> create table Maclean_partition (“OBJECT_ID” NUMBER) partition by range(object_id)
2 (partition p1 values less than (99999) tablespace users,
3 partition p2 values less than (maxvalue) tablespace users);

表已创建。

SQL> select segment_name,partition_name from dba_segments where segment_name like ‘%MACLEAN%’ and owner=’MACLEAN’;

未选定行

SQL> insert into Maclean_nonpartition values(1);

已创建 1 行。

SQL> insert into Maclean_partition values(1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select segment_name,partition_name from dba_segments where segment_name like ‘%MACLEAN%’ and owner=’MACLEAN’;

SEGMENT_NAME PARTITION_NAME
—————————— ————————————————————
MACLEAN_PARTITION P1
MACLEAN_NONPARTITION

 

 

create table Maclean_nonpartition (“OBJECT_ID” NUMBER);

create table Maclean_partition (“OBJECT_ID” NUMBER) partition by range(object_id)
(partition p1 values less than (99999) tablespace users,
partition p2 values less than (maxvalue) tablespace users);

 

Filed Under: Oracle, Oracle 11g新特性

Script:脚本获得用户User DDL包括system、object grant

2011/03/09 by Leave a Comment

以下脚本用于获得用户User DDL包括system、object grant:

 

 

set head off
set pages 0
set long 9999999
spool user_script.sql

-- USER DDL
SELECT DBMS_METADATA.GET_DDL('USER', '&USERNAME') from dual;                         

-- ROLE GRANT 角色授权

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&USERNAME')  from dual;

-- SYSTEM_GRANT 系统权限
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&USERNAME') from dual;

-- OBJECT_GRANT 对象权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&USERNAME') from dual;

spool off
Filed Under: Oracle, Oracle脚本script Tagged With: , ,

【图】Hadoop与商用数据库性能对比

2011/02/28 by Leave a Comment

Hadoop与商用数据库性能对比

 

hadoop vs rdbms

 

 

Hadoop对开发的巨大挑战：以计算用户总应收为例

 

hadoop vs rdbms dev1

 

 

两种“云”中的分布式数据

 

 

nosql vs oracle rdbms
Oracle数据库与Hbase数据库

Filed Under: Oracle

Recreate failovered primary database using Flashback Database

2011/02/24 by Leave a Comment

很多朋友一直对DataGuard的fast-start failover持怀疑的态度;通过observer的观察，(Fast-Start Failover)FSFO提供了一种在primary数据库不可用情况下自动故障切换到standby数据库的能力。造成很多朋友不愿意使用FSFO的原因之一是故障切换后不得不重建原primary数据库，不过如果我们能配合使用10g中的闪回数据库特性的话，这一重建数据库的工作便也可以一劳永逸了。

/* 启用fast_start failover的前提之一是在primary和standby库上都启用flashback database */

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

/* 当前的primary库情况 */

SQL> select open_mode ,database_role,flashback_on from v$database;
OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ WRITE           PRIMARY          YES


SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';

Session altered.

SQL> select OLDEST_FLASHBACK_TIME  from v$flashback_database_log;

OLDEST_FLASHBACK_TI
-------------------
2011-02-19 22:40:39

/* 当前的standby库情况 */
SQL> select open_mode ,database_role,flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY YES

SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';

Session altered.
SQL> select OLDEST_FLASHBACK_TIME  from v$flashback_database_log;

OLDEST_FLASHBACK_TI
-------------------
2011-02-19 22:34:56


DGMGRL> edit configuration set property faststartfailoverthreshold=10;
Property "faststartfailoverthreshold" updated

DGMGRL> show configuration verbose;

Configuration - pro

  Protection Mode: MaxAvailability
  Databases:
    sbdb - Primary database
    prod - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

/* 另开一个终端启动observer */

DGMGRL> start observer
Observer started

DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        10 seconds
  Target:           (none)
  Observer:         rh3.oracle.com
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)


/* 启动快速故障切换  */

DGMGRL> enable fast_start failover
Enabled.

/* 记录实际切换前的时间 */

[maclean@rh3 ~]$ date
Sat Feb 19 23:35:44 CST 2011

[maclean@rh2 ~]$ ps -ef|grep pmon|grep -v grep
maclean  25165     1  0 22:58 ?        00:00:00 ora_pmon_SBDB
maclean  26080     1  0 23:04 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 ~]$ kill -9 25165

/* 通过kill pmon进程造成主库crash，引发fast-start failover */

/* observer观察到primary库意外终止后，实施了快速故障切换 */

23:37:27.92  Saturday, February 19, 2011
Initiating Fast-Start Failover to database "prod"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "prod"
23:37:33.69  Saturday, February 19, 2011
23:39:17.98  Saturday, February 19, 2011
Initiating reinstatement for database "sbdb"...
Reinstating database "sbdb", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "sbdb" failed
23:39:26.30  Saturday, February 19, 2011

23:40:05.00  Saturday, February 19, 2011
Initiating reinstatement for database "sbdb"...
Reinstating database "sbdb", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "sbdb" failed
23:40:09.24  Saturday, February 19, 2011

/* 接下来我们通过闪回数据库来将失败切换后的primary库flashback到faliover之前以便重用*/

SQL> startup mount;

SQL> flashback database to timestamp to_timestamp('2011-02-19 23:30:44','YYYY-MM-DD hh24:mi:ss');
flashback database to timestamp to_timestamp('2011-02-19 23:35:44','YYYY-MM-DD hh24:mi:ss')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1143987 to SCN End-of-Redo
ORA-38761: redo log sequence 10 in thread 1, incarnation 3 could not be
accessed

/* 缺少最近的归档日志而无法完成闪回，需要到目前的primary库中查找 */

SQL> select name from v$archived_log where FIRST_CHANGE#<1143987 and NEXT_CHANGE#>1143987;

NAME
--------------------------------------------------------------------------------
/s01/fast_recovery_area/PROD/archivelog/2011_02_19/o1_mf_1_10_6ozpzh9c_.arc

SQL> alter database register physical logfile '/s01/fast_recovery_area/PROD/archivelog/2011_02_19/o1_mf_1_10_6ozpzh9c_.arc';
Database altered.


SQL> flashback database to timestamp to_timestamp('2011-02-19 23:30:44','YYYY-MM-DD hh24:mi:ss');
Flashback complete.

/* 成功闪回后，observer将主动去尝试reinstate这个目前可用的standby库,如以下日志*/

23:46:20.16  Saturday, February 19, 2011
Initiating reinstatement for database "sbdb"...
Reinstating database "sbdb", please wait...
Operation requires shutdown of instance "SBDB" on database "sbdb"
Shutting down instance "SBDB"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "SBDB" on database "sbdb"
Starting instance "SBDB"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "sbdb" ...
Reinstatement of database "sbdb" succeeded
23:47:13.49  Saturday, February 19, 2011

DGMGRL> show configuration verbose

Configuration - pro

  Protection Mode: MaxAvailability
  Databases:
    prod - Primary database
    sbdb - (*) Physical standby database

  (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

  Threshold:        10 seconds
  Target:           sbdb
  Observer:         rh3.oracle.com
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

/*可以通过show configuration命令监控到当前primary与standby库都处于可用状态，
   且fast_start failover也为启用状态 */
Filed Under: Oracle Tagged With: , , ,

Setup Oracle Direct NFS Client

2011/02/23 by 2 Comments

在Oracle 11g中引入了Direct Network File System(Oracle Direct NFS)的新特性，通过一个打包在Oracle内核中的NFS客户机以改善实例使用NFS时的性能，同时进一步完善了通过NFS实现RAC的解决方案。常规的NFS客户端软件一般由操作系统供应商提供，这类NFS客户端不会专门为Oracle数据文件的IO做优化。而通过内建的Oracle Direct NFS，数据库将可以直接访问NFS服务器上的文件，避免由OS内核NFS造成的额外开销。Oracle宣称由以上优化所带来的性能提升，在DSS环境中超过40%，而在OLTP环境中超过10%(详见<Oracle Database 11g  Direct NFS Client a white paper>)。

接下来我们将通过实例来演示如何构建Oracle Direct NFS客户机，实际上这并不困难；Direct NFS客户机会以如下顺序查找装载点的设置信息:

  • $ORACLE_HOME/dbs/oranfstab 作用域为$ORACLE_HOME相关的数据库
  • /etc/oranfstab 作用域为主机上所有可用数据库
  • 最后为/etc/mtab配置文件，以确定可用的NFS装载点

我们一般推荐使用$ORACLE_HOME/dbs/oranfstab来配置Direct NFS客户机；该oranfstab配置文件可以包括Server,path,export以及mount参数，各参数代表的属性如下:

  • Server:NFS服务器名
  • Path:到达NFS服务器的最多4个网络路径，可以是IP或者主机名
  • Export:从NFS服务器导出的路径
  • Mount：NFS的本地装载点

需要注意的是如果是在使用NFS实现RAC的情景中，那么必须使用/etc/oranfstab配置文件。并且该配置文件应当在所有节点上时同步的。
在正式启用Direct NFS客户机前，NFS文件系统应当已由常规NFS方式mount并且可用。为了启用Direct NFS client，我们还需要将标准的Oracle磁盘管理库(Oracle Disk Manager (ODM) library)替换为支持Direct NFS client的NFS ODM。可以通过建立从标准ODM库指向NFS ODM库的符号链接来完成以上工作，但是需要注意的是以上操作仅能在实例关闭的情况下才能实施并且有效。

SQL> shutdown immediate;

[maclean@rh2 ~]$ cd $ORACLE_HOME/lib
[maclean@rh2 lib]$ mv libodm11.so libodm11.so.old
[maclean@rh2 lib]$ ln -s libnfsodm11.so libodm11.so
[maclean@rh2 lib]$ ls -l libodm11.so
lrwxrwxrwx 1 maclean oinstall 14 Feb 18 19:27 libodm11.so -> libnfsodm11.so

接下来我们将正式启用Direct NFS Client，并会简单测试其性能:

[root@rh2 ~]# showmount -e  nas
Export list for nas:
/d01 rh2

/* 以常规方式装载NFS文件系统 */
[root@rh2 ~]# mount -t nfs nas:/d01 /d01

[maclean@rh2 ~]$ cat $ORACLE_HOME/dbs/oranfstab
server: nas
path: 192.168.1.188
export: /d01  mount: /d01

SQL> startup;

启动阶段告警日志会出现以下信息:
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
.................
Direct NFS: attempting to mount /d01 on filer nas defined in oranfstab
Direct NFS: channel config is:
     channel id [0] local [] path [192.168.1.188]
Direct NFS: mount complete dir /d01 on nas mntport 998 nfsport 2049

SQL> desc v$dnfs_servers;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 SVRNAME                                            VARCHAR2(255)
 DIRNAME                                            VARCHAR2(1024)
 MNTPORT                                            NUMBER
 NFSPORT                                            NUMBER
 WTMAX                                              NUMBER
 RTMAX                                              NUMBER

/*可以通过查询V$DNFS_SERVERS动态视图来了解NFS客户机的装载情况 */

SQL> col svrname for a10
SQL> col dirname for a10
SQL> select svrname,dirname,mntport,nfsport from v$dnfs_servers;

SVRNAME    DIRNAME       MNTPORT    NFSPORT
---------- ---------- ---------- ----------
nas        /d01              998       2049

/* 此外V$DNFS_FILES视图提供了当前Direct NFS打开的文件
         V$DNFS_CHANNELS视图提供了DNFS为服务器打开的网络路径
         V$DNFS_STATS视图提供了DNFS的性能统计信息 */

SQL> set timing on;
SQL> create tablespace nfs_perf datafile '/d01/perf01.dbf' size 2000M;
Tablespace created.

Elapsed: 00:00:30.91

SQL> shutdown immediate;

/* 通过还原libodm11.so库文件和删除oranfstab配置文件可以禁用Direct NFS */

[maclean@rh2 lib]$ cp libodm11.so.old libodm11.so
[maclean@rh2 lib]$ rm $ORACLE_HOME/dbs/oranfstab

SQL> select svrname,dirname,mntport,nfsport from v$dnfs_servers;
no rows selected

SQL> set timing on;
SQL> create tablespace nfs_kernel datafile '/d01/kernel01.dbf' size 2000M;
Tablespace created.

Elapsed: 00:00:29.45

/* 似乎从简单的数据文件创建时间上体现不出Oracle Direct Nfs的优势...*/

That's ok
Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

expdp/impdp导入导出Long字段类型产生乱码问题

2011/02/22 by Leave a Comment

LONG作为一种数据类型，在LOB大对象出现之后就不再推荐使用了。Oracle对LONG的态度是废弃，以至于一些新的工具对LONG类型的支持也并不好。

例如10g中推出的数据泵功能data pump expdp/impdp，在2个字符集完全一致(ZHS16GBK AL16UTF16)的数据库间导入、导出LONG  字段也可能存在乱码，这个现象在10g中比较常见。

 

对于该expdp/impdp long字段产生乱码的问题， Workaroud 的是用 exp/imp 传统导入导出工具来替代expdp /impdp，虽然export/import工具的速度不如data pump，但是因为是传统工具所以对于LONG这种近乎废弃的数据类型支持较好。

在11g上测试了一下，该乱码问题似乎已经得到修复：

 

 


SQL*Plus: Release 11.2.0.3.0 Production on 星期五 2月 22 06:13:53 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> conn maclean/oracle
Connected.
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create table tlong (t1 int, t2 long);

Table created.


SQL> insert into tlong values(1,'Maclean的测试');

1 row created.

SQL> commit;

Commit complete.

SQL> select t2 from tlong;

T2
--------------------------------------------------------------------------------
Maclean的测试



  1* select name,value$ from sys.props$ where name like '%CHARACTERSET%'
SQL> /

NAME                           VALUE$
------------------------------ --------------------------------------------------
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         UTF8


[oracle@vmac1 ~]$ expdp maclean/oracle dumpfile=tmp:tlong.dmp tables=maclean.tlong 

Export: Release 11.2.0.3.0 - Production on 星期五 2月 22 06:18:55 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
启动 "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** dumpfile=tmp:tlong.dmp tables=maclean.tlong 
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "MACLEAN"."TLONG"                           5.421 KB       1 行
已成功加载/卸载了主表 "MACLEAN"."SYS_EXPORT_TABLE_01" 
******************************************************************************
MACLEAN.SYS_EXPORT_TABLE_01 的转储文件集为:
  /tmp/tlong.dmp
作业 "MACLEAN"."SYS_EXPORT_TABLE_01" 已于 06:19:14 成功完成



[oracle@vmac1 ~]$ impdp dumpfile=tmp:tlong.dmp remap_table=maclean.tlong:tlong1         

Import: Release 11.2.0.3.0 - Production on 星期五 2月 22 06:21:34 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: maclean
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
已成功加载/卸载了主表 "MACLEAN"."SYS_IMPORT_FULL_01" 
启动 "MACLEAN"."SYS_IMPORT_FULL_01":  maclean/******** dumpfile=tmp:tlong.dmp remap_table=maclean.tlong:tlong1 
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "MACLEAN"."TLONG1"                          5.421 KB       1 行
作业 "MACLEAN"."SYS_IMPORT_FULL_01" 已于 06:21:39 成功完成


SQL> select * from tlong1;




        T1
----------
T2
--------------------------------------------------------------------------------
         1
Maclean的测试
Filed Under: Oracle, Oracle Bug Tagged With: ,

Applying online patch on 11gr2

2011/02/22 by 1 Comment

在Oracle 11g中提出了online patch(也叫hot patch)的特性；Hot patching允许我们在实例始终在线的情况下安装，启用或禁用一个修复补丁或者诊断补丁。针对7*24在线的业务系统，hot patch为我们提供了一条既能避免当机时间而又可以实施补丁的途径。在Oracle 11g中我们可以使用Opatch命令行工具针对任意数据库实施在线补丁(前提是该补丁是一个hot patch)。一般来说在线补丁(hot patches)只能是那些代码修改范围小且复杂度很低的补丁，举例来说它们往往是一些诊断补丁(diagnostic patches)或者小bug的修复(small bug fixes)。值得注意的是hot patching将需要消耗额外的内存，决定其消耗内存数量的因素是:1.补丁本身的大小,2.实例中的进程总数；举例来说某个补丁的大小正好为一个OS page的大小(一般为4kB)，那么当实例中运行的进程总数为1000时，则该hot patching所额外消耗的内存总数为4kB*1000=4MB。

hot patches与常规Conventional patches对比具有可在线实施和安装快的特性，如下图:
online patching

在实际生产环境中，相信没有多少朋友实施过hot patching，一来国内目前还没有普及11g的使用，二来hot patching的数量在所有interim patch中只占极少数；一直以来都想写这样一篇关于hot patching的博文，唯一妨碍我写作的问题是在11.2.0.1下找不到可实施的online interim patch；以MOS->patches&upgrade目前的分类我们很难找出某个base release下可用的hot patch，当然这并不妨碍补丁专栏的使用。为了这个令人郁闷的问题，我特意去提交了一个Service Request，得到的回复:

I have tried to find the patches which support online patching on 11.2.0.1 version,
but I also can not find them because there are too many patches and there is no catalog for the patches
which support online patching, and I can only check the patch readme to confirm whether that patch supports online patching.

I found one patch which supports online patching, but this patch is for 11.2.0.2 version.
The patch no. is 10188727.

Sorry for the inconvenience brought to you. Hope the above update can help you.
If the above patch is not what you want, then please update the SR and I will continue for your issue.

这其中提到的patch 10188727，可以从Note<RDBMS Online Patching Aka Hot Patching [ID 761111.1]>中找到，另外一个可找到的hot patch是11.1.0.6上的6198642<DUMMY PATCH FOR TESTING DB11 PATCHING>，不过很可惜该补丁只有Linux x86一个平台版本的。所以我不得不先将11.2.0.1的测试库升级到了11.2.0.2上:

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

[maclean@rh2 OPatch]$ ps -ef|grep pmon|grep -v grep
maclean  22481     1  0 19:19 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 OPatch]$ pmap -d 22481
22481:   ora_pmon_PROD
Address           Kbytes Mode  Offset           Device    Mapping
0000000000400000  180232 r-x-- 0000000000000000 008:00002 oracle
000000000b602000    1820 rwx-- 000000000b002000 008:00002 oracle
000000000b7c9000     300 rwx-- 000000000b7c9000 000:00000   [ anon ]
000000000dbef000     436 rwx-- 000000000dbef000 000:00000   [ anon ]
0000000060000000 2050048 rwxs- 0000000000000000 000:00009   [ shmid=0x550001 ]
0000003e09a00000     112 r-x-- 0000000000000000 008:00001 ld-2.5.so
0000003e09c1b000       4 r-x-- 000000000001b000 008:00001 ld-2.5.so
0000003e09c1c000       4 rwx-- 000000000001c000 008:00001 ld-2.5.so
0000003e09e00000    1336 r-x-- 0000000000000000 008:00001 libc-2.5.so
0000003e09f4e000    2044 ----- 000000000014e000 008:00001 libc-2.5.so
0000003e0a14d000      16 r-x-- 000000000014d000 008:00001 libc-2.5.so
0000003e0a151000       4 rwx-- 0000000000151000 008:00001 libc-2.5.so
0000003e0a152000      20 rwx-- 0000003e0a152000 000:00000   [ anon ]
0000003e0a200000     520 r-x-- 0000000000000000 008:00001 libm-2.5.so
0000003e0a282000    2044 ----- 0000000000082000 008:00001 libm-2.5.so
0000003e0a481000       4 r-x-- 0000000000081000 008:00001 libm-2.5.so
0000003e0a482000       4 rwx-- 0000000000082000 008:00001 libm-2.5.so
0000003e0a600000       8 r-x-- 0000000000000000 008:00001 libdl-2.5.so
0000003e0a602000    2048 ----- 0000000000002000 008:00001 libdl-2.5.so
0000003e0a802000       4 r-x-- 0000000000002000 008:00001 libdl-2.5.so
0000003e0a803000       4 rwx-- 0000000000003000 008:00001 libdl-2.5.so
0000003e0aa00000      88 r-x-- 0000000000000000 008:00001 libpthread-2.5.so
0000003e0aa16000    2044 ----- 0000000000016000 008:00001 libpthread-2.5.so
0000003e0ac15000       4 r-x-- 0000000000015000 008:00001 libpthread-2.5.so
0000003e0ac16000       4 rwx-- 0000000000016000 008:00001 libpthread-2.5.so
0000003e0ac17000      16 rwx-- 0000003e0ac17000 000:00000   [ anon ]
0000003e0ae00000      28 r-x-- 0000000000000000 008:00001 librt-2.5.so
0000003e0ae07000    2048 ----- 0000000000007000 008:00001 librt-2.5.so
0000003e0b007000       4 r-x-- 0000000000007000 008:00001 librt-2.5.so
0000003e0b008000       4 rwx-- 0000000000008000 008:00001 librt-2.5.so
0000003e0da00000      84 r-x-- 0000000000000000 008:00001 libnsl-2.5.so
0000003e0da15000    2044 ----- 0000000000015000 008:00001 libnsl-2.5.so
0000003e0dc14000       4 r-x-- 0000000000014000 008:00001 libnsl-2.5.so
0000003e0dc15000       4 rwx-- 0000000000015000 008:00001 libnsl-2.5.so
0000003e0dc16000       8 rwx-- 0000003e0dc16000 000:00000   [ anon ]
00002abec920e000       8 rwx-- 00002abec920e000 000:00000   [ anon ]
00002abec9210000       4 r-x-- 0000000000000000 008:00002 libodmd11.so
00002abec9211000    1024 ----- 0000000000001000 008:00002 libodmd11.so
00002abec9311000       4 rwx-- 0000000000001000 008:00002 libodmd11.so
00002abec9312000     360 r-x-- 0000000000000000 008:00002 libcell11.so
00002abec936c000    1020 ----- 000000000005a000 008:00002 libcell11.so
00002abec946b000      36 rwx-- 0000000000059000 008:00002 libcell11.so
00002abec9474000       4 rwx-- 00002abec9474000 000:00000   [ anon ]
00002abec9475000     848 r-x-- 0000000000000000 008:00002 libskgxp11.so
00002abec9549000    1024 ----- 00000000000d4000 008:00002 libskgxp11.so
00002abec9649000       8 rwx-- 00000000000d4000 008:00002 libskgxp11.so
00002abec9665000       4 rwx-- 00002abec9665000 000:00000   [ anon ]
00002abec9666000    2580 r-x-- 0000000000000000 008:00002 libnnz11.so
00002abec98eb000    1020 ----- 0000000000285000 008:00002 libnnz11.so
00002abec99ea000     264 rwx-- 0000000000284000 008:00002 libnnz11.so
00002abec9a2c000       8 rwx-- 00002abec9a2c000 000:00000   [ anon ]
00002abec9a2e000      96 r-x-- 0000000000000000 008:00002 libclsra11.so
00002abec9a46000    1020 ----- 0000000000018000 008:00002 libclsra11.so
00002abec9b45000       4 rwx-- 0000000000017000 008:00002 libclsra11.so
00002abec9b46000       4 rwx-- 00002abec9b46000 000:00000   [ anon ]
00002abec9b47000     136 r-x-- 0000000000000000 008:00002 libdbcfg11.so
00002abec9b69000    1020 ----- 0000000000022000 008:00002 libdbcfg11.so
00002abec9c68000       8 rwx-- 0000000000021000 008:00002 libdbcfg11.so
00002abec9c6a000    6832 r-x-- 0000000000000000 008:00002 libhasgen11.so
00002abeca316000    1020 ----- 00000000006ac000 008:00002 libhasgen11.so
00002abeca415000     136 rwx-- 00000000006ab000 008:00002 libhasgen11.so
00002abeca437000      24 rwx-- 00002abeca437000 000:00000   [ anon ]
00002abeca43d000       8 r-x-- 0000000000000000 008:00002 libskgxn2.so
00002abeca43f000    1020 ----- 0000000000002000 008:00002 libskgxn2.so
00002abeca53e000       4 rwx-- 0000000000001000 008:00002 libskgxn2.so
00002abeca53f000       4 rwx-- 00002abeca53f000 000:00000   [ anon ]
00002abeca540000     656 r-x-- 0000000000000000 008:00002 libocr11.so
00002abeca5e4000    1020 ----- 00000000000a4000 008:00002 libocr11.so
00002abeca6e3000      12 rwx-- 00000000000a3000 008:00002 libocr11.so
00002abeca6e6000     628 r-x-- 0000000000000000 008:00002 libocrb11.so
00002abeca783000    1024 ----- 000000000009d000 008:00002 libocrb11.so
00002abeca883000       8 rwx-- 000000000009d000 008:00002 libocrb11.so
00002abeca885000      44 r-x-- 0000000000000000 008:00002 libocrutl11.so
00002abeca890000    1020 ----- 000000000000b000 008:00002 libocrutl11.so
00002abeca98f000       4 rwx-- 000000000000a000 008:00002 libocrutl11.so
00002abeca990000       4 rwx-- 00002abeca990000 000:00000   [ anon ]
00002abeca991000       4 r-x-- 0000000000000000 008:00001 libaio.so.1.0.1
00002abeca992000    2044 ----- 0000000000001000 008:00001 libaio.so.1.0.1
00002abecab91000       4 rwx-- 0000000000000000 008:00001 libaio.so.1.0.1
00002abecab92000      16 rwx-- 00002abecab92000 000:00000   [ anon ]
00002abecab96000      20 r-x-- 0000000000000000 008:00001 libnuma.so.1
00002abecab9b000    2044 ----- 0000000000005000 008:00001 libnuma.so.1
00002abecad9a000       4 rwx-- 0000000000004000 008:00001 libnuma.so.1
00002abecad9b000    1280 rwx-- 00002abecad9b000 000:00000   [ anon ]
00002abecaef5000      40 r-x-- 0000000000000000 008:00001 libnss_files-2.5.so
00002abecaeff000    2044 ----- 000000000000a000 008:00001 libnss_files-2.5.so
00002abecb0fe000       4 r-x-- 0000000000009000 008:00001 libnss_files-2.5.so
00002abecb0ff000       4 rwx-- 000000000000a000 008:00001 libnss_files-2.5.so
00002abecb100000    1700 rwx-- 00002abecb100000 000:00000   [ anon ]
00002abecb2a9000      28 rwx-- 0000000000000000 000:00011 zero
00002abecb2b0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2c0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2d0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2e0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2f0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb300000     164 rwx-- 0000000000057000 000:00011 zero
00002abecb329000       8 rwx-- 00002abecb329000 000:00000   [ anon ]
00002abecb32b000       4 rwxs- 0000000000000000 008:00002 hc_PROD.dat
00002abecb32c000      40 r-x-- 0000000000000000 008:00002 libnque11.so
00002abecb336000    1020 ----- 000000000000a000 008:00002 libnque11.so
00002abecb435000       4 rwx-- 0000000000009000 008:00002 libnque11.so
00002abecb436000    1048 rwx-- 00002abecb436000 000:00000   [ anon ]
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2291488K    writeable/private: 7840K    shared: 2050052K

[maclean@rh2 ~]$  cd $ORACLE_HOME/OPatch
[maclean@rh2 OPatch]$ unzip p10188727_112020_Linux-x86-64.zip
Archive:  p10188727_112020_Linux-x86-64.zip
   creating: 10188727/
   creating: 10188727/files/
   creating: 10188727/files/lib/
   creating: 10188727/files/lib/libserver11.a/
  inflating: 10188727/files/lib/libserver11.a/kkopq.o
   creating: 10188727/etc/
   creating: 10188727/etc/config/
  inflating: 10188727/etc/config/inventory.xml
  inflating: 10188727/etc/config/actions.xml
  inflating: 10188727/etc/config/deploy.xml
   creating: 10188727/etc/xml/
  inflating: 10188727/etc/xml/GenericActions.xml
  inflating: 10188727/etc/xml/ShiphomeDirectoryStructure.xml
  inflating: 10188727/README.txt
   creating: 10188727/online/
   creating: 10188727/online/files/
   creating: 10188727/online/files/hpatch/
  inflating: 10188727/online/files/hpatch/bug10188727.pch
   creating: 10188727/online/etc/
   creating: 10188727/online/etc/config/
  inflating: 10188727/online/etc/config/inventory.xml
  inflating: 10188727/online/etc/config/actions.xml
  inflating: 10188727/online/etc/config/deploy.xml
   creating: 10188727/online/etc/xml/
  inflating: 10188727/online/etc/xml/GenericActions.xml
  inflating: 10188727/online/etc/xml/ShiphomeDirectoryStructure.xml 

[maclean@rh2 OPatch]$ opatch query 10188727 -all
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_2
Central Inventory : /s01/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_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_20-05-21PM.log

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

--------------------------------------------------------------------------------
 Patch created on 2 Dec 2010, 01:44:13 hrs PST8PDT
 Need to shutdown Oracle instances: true
 Patch is roll-backable: true
 Patch is a "Patchset Update": false
 Patch is a rolling patch: true
 Patch has sql related actions: false
 Patch is an online patch: false
 Patch is a portal patch: false
 Patch is an "auto-enabled" patch: false

 List of platforms supported:
   226: Linux x86-64

 List of bugs to be fixed:
   10188727: AFTER UPGRADING TO 11.2.0.2 SOME SQLS FAIL WITH ORA-7445 [KKEIDC()+180] ERROR

 This patch is a "singleton" patch.

 This patch belongs to the "db" product family 

 List of executables affected:
   ORACLE_HOME/bin/oracle

 List of optional components:
   oracle.rdbms:  11.2.0.2.0

 List of optional actions:
   Update /s01/product/11.2.0/dbhome_2/lib/libserver11.a with /kkopq.o
   cd /s01/product/11.2.0/dbhome_2/rdbms/lib
     ; make -f ins_rdbms.mk ioracle ORACLE_HOME=/s01/product/11.2.0/dbhome_2

  Possible XML representation of the patch:

     10188727

--------------------------------------------------------------------------------
OPatch succeeded.

[maclean@rh2 OPatch]$ ./opatch query -is_online_patch 10188727
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_2
Central Inventory : /s01/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_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_19-45-33PM.log

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

--------------------------------------------------------------------------------
 Patch is an online patch: false

OPatch succeeded.

/* 虽然Opatch返回"online patch: false"，但实际上这是一个online patch,
    造成以上问题的原因可能是Opatch版本低 */

[maclean@rh2 OPatch]$ tree 10188727
10188727
|-- README.txt
|-- etc
|   |-- config
|   |   |-- actions.xml
|   |   |-- deploy.xml
|   |   `-- inventory.xml
|   `-- xml
|       |-- GenericActions.xml
|       `-- ShiphomeDirectoryStructure.xml
|-- files
|   `-- lib
|       `-- libserver11.a
|           `-- kkopq.o
`-- online
    |-- etc
    |   |-- config
    |   |   |-- actions.xml
    |   |   |-- deploy.xml
    |   |   `-- inventory.xml
    |   `-- xml
    |       |-- GenericActions.xml
    |       `-- ShiphomeDirectoryStructure.xml
    `-- files
        `-- hpatch
            `-- bug10188727.pch

/* 可以从以上目录结构中看到包含了online子目录，我们可以直接观察其inventory.xml信息文件 */

[maclean@rh2 OPatch]$ cat 10188727/online/etc/config/inventory.xml |grep instance
 <instance_shutdown>false</instance_shutdown>
 <instance_shutdown_message></instance_shutdown_message>

/* 以上instance_shutdown为false说明其可以作为online patch实施 */

[maclean@rh2 OPatch]$ cd 10188727

/* opatch online patching的具体语法如下 */
opatch apply online -connectString  <SID>:<USERNAME>:<PASSWORD>:<NODE1>, \
<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,... 

[maclean@rh2 10188727]$ opatch apply online -connectString PROD:sys:password
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_2
Central Inventory : /s01/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_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_19-49-44PM.log

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

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
ApplySession applying interim patch '10188727' to OH '/s01/product/11.2.0/dbhome_2'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '10188727' for restore. This might take a while...
Backing up files affected by the patch '10188727' for rollback. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10188727.pch', on database 'PROD'.

ApplySession adding interim patch '10188727' to inventory

Verifying the update...
Inventory check OK: Patch ID 10188727 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 10188727 are present in Oracle Home.
OPatch succeeded.

[maclean@rh2 OPatch]$ opatch lsinventory -detail|tail -21
Interim patches (1) :

Patch (online) 10188727: applied on Thu Feb 17 19:49:52 CST 2011
Unique Patch ID:  13202318
   Created on 2 Dec 2010, 01:44:15 hrs PST8PDT
   Bugs fixed:
     10188727
   Files Touched:
     bug10188727.pch --> ORACLE_HOME/hpatch/bug10188727.pch
   Instances Patched:
     PROD
   Patch Location in Inventory:
     /s01/product/11.2.0/dbhome_2/inventory/oneoffs/10188727
   Patch Location in Storage area:
     /s01/product/11.2.0/dbhome_2/.patch_storage/10188727_Dec_2_2010_01_44_15

告警日志alert.log中的信息:
Patch file bug10188727.pch is out of sync with oracle binary; performing fixup
Patch file bug10188727.pch has been synced with oracle binary
Patch bug10188727.pch Installed - Update #1
Patch bug10188727.pch Enabled - Update #2

[maclean@rh2 OPatch]$ ps -ef|grep pmon|grep -v grep
maclean  22481     1  0 19:19 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 OPatch]$ pmap -d 22481|tail -10
00002abecb435000       4 rwx-- 0000000000009000 008:00002 libnque11.so
00002abecb436000    1048 rwx-- 00002abecb436000 000:00000   [ anon ]
00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug10188727.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug10188727.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug10188727.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2297720K    writeable/private: 7848K    shared: 2050052K

/* 再次观察pmon进程的内存信息，可以看到pmap输出中多出了2个小的oracle正文镜像和
    名为bug10188727.so的共享库文件，而该后台进程的private memory由原来的7840k上升到7848k，
    实际增幅为8k */

/* 此外可以通过oradebug命令将该online patch禁用，虽然并不推荐这样做 */

SQL> oradebug patch disable  bug10188727.pch;
Statement processed.

SQL>  oradebug patch disable bug10188727.pch;
Patch file already disabled

[maclean@rh2 hpatch]$ pmap -d 22481|tail -8
00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug10188727.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug10188727.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug10188727.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2297720K    writeable/private: 7848K    shared: 2050052K

/* 但disable掉online patch并不会导致在线补丁额外消耗的内存被回收 */

/* 当然我们还可以很方便地启用它 */

SQL> oradebug patch enable bug10188727.pch;
Statement processed.
SQL>  oradebug patch enable bug10188727.pch;
Patch file already enabled

[maclean@rh2 ~]$ cd $ORACLE_HOME/hpatch

[maclean@rh2 hpatch]$ ls -l
total 368
-rw-r--r-- 1 maclean oinstall 177874 Feb 17 19:49 bug10188727.pch
-rwx------ 1 maclean oinstall      1 Feb 17 19:49 bug10188727.pchPROD.fixup
-rwx------ 1 maclean oinstall 176850 Feb 17 19:49 bug10188727.so
-rw------- 1 maclean oinstall    712 Feb 17 20:13 orapatchPROD.cfg

/* 注意不要在实例启动时删除以上hpatch目录及目录下任何文件，这可能导致instance出现意外 */

[maclean@rh2 hpatch]$ cd $ORACLE_HOME/OPatch

/* 我们还能够将online patch rollback回滚掉，如以下语法 */
opatch rollback -id <patchID> -connectString  <SID>:<USERNAME>:<PASSWORD>:<NODE1>, \
<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,  ...

[maclean@rh2 OPatch]$ opatch rollback -id 10188727 -connectString PROD:sys:password -invPtrLoc /s01/product/11.2.0/dbhome_2/oraInst.loc
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_2
Central Inventory : /s01/oraInventory
   from           : /s01/product/11.2.0/dbhome_2/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/product/11.2.0/dbhome_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_20-18-00PM.log

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

RollbackSession rolling back interim patch '10188727' from OH '/s01/product/11.2.0/dbhome_2'

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files affected by the patch '10188727' for restore. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be removed from active database instances.
Disabling and removing online patch 'bug10188727.pch', on database 'PROD'

RollbackSession removing interim patch '10188727' from inventory

OPatch succeeded.

告警日志alert.log中的remove信息:
Patch bug10188727.pch Disabled - Update #5
Patch bug10188727.pch Removed - Update #6
Thu Feb 17 20:18:07 2011
Online patch bug10188727.pch has been disabled
Online patch bug10188727.pch has been removed

[maclean@rh2 trace]$ pmap -d 22481|tail -8
00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug10188727.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug10188727.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug10188727.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2297720K    writeable/private: 7848K    shared: 2050052K

/* 显然rollback回滚掉该online interim patch也不足以回收内存，唯一的方法是重启实例 */

SQL> startup force;

[maclean@rh2 trace]$ ps -ef|grep pmon|grep -v grep
maclean  25563     1  0 20:22 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 trace]$ pmap -d 25563|tail -8
00002aaf2f324000       4 rwxs- 0000000000000000 008:00002 hc_PROD.dat
00002aaf2f325000      40 r-x-- 0000000000000000 008:00002 libnque11.so
00002aaf2f32f000    1020 ----- 000000000000a000 008:00002 libnque11.so
00002aaf2f42e000       4 rwx-- 0000000000009000 008:00002 libnque11.so
00002aaf2f42f000    1048 rwx-- 00002aaf2f42f000 000:00000   [ anon ]
00007fffdfa84000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2291488K    writeable/private: 7840K    shared: 2050052K

/* That's ok!
    ash to ash, dust to dust!　*/

References:<RDBMS Online Patching Aka Hot Patching [ID 761111.1]>

Filed Under: Oracle, Oracle 11g新特性, Oracle Bug, Oracle补丁patch Tagged With: , ,

几个关于oracle 11g ASM的问题

2011/02/21 by 4 Comments

Question:

1.11g Oracle Clusterware需要的OCR和Voting disk可以存储在ASM或者集群文件系统或者NFS中。对于全新安装，裸设备不再被支持(是否有办法使用裸设备？)。

2.使用ASM时，若相关存储上的磁盘路径(disk path)名前后不一致，是否仍然可以使用？需要什么调整？

Answer:

1.在11gr2 Grid Infrastructure全新安装时是没有办法使用裸设备的(You cannot install OCR or voting disk files on raw partitions. You can install only on Oracle ASM, or on supported network-attached storage or cluster file systems. The only use for raw devices is as Oracle ASM disks.);但可以通过后续的手段将OCR和VOTING DISK移动到裸设备上，如:

替换OCR:
ocrconfig -add rawdevice
ocrconfig -replace

替换voting disk
crsctl add votedisk css  -force
crsctl delete votedisk css  -force

具体可以参考Metalink文档<How to ADD/REMOVE/REPLACE/MOVE Oracle Cluster Registry (OCR) and Voting Disk>
实际上强烈不建议这样做。因为如果出现问题,Oracle GCS可以拒绝提供建议。

2.ASM是通过读取磁盘头部来了解磁盘内容的；磁盘路径名在安装时需要在所有节点一致，在安装完成后即便路径名改变也不会影响到ASM的使用。
需要注意的是在AIX操作平台上分配给ASM的磁盘(ASM DISK)，如果直接是HDISK形式的LUN则该HDISK不应当具有PVID(If the disk device has a PVID, then ASM will fail to mount the diskgroup created on the disk device.)。如果是裸的逻辑卷，那么所建VG应当是scaleable volume group。

Filed Under: Oracle, Oracle ASM Tagged With: , , ,

应用长短链接变更对于Oracle数据库性能的影响

2011/02/20 by Leave a Comment

Question:某客户的应用做过变更（短链变长链），现cpu利用率较之前有明显改善，参见附件中的awr报告。想咨询一下sql语句的执行时间，cpu Time等指标，是否会受到短链变长链影响，因为从awr报告看，性能有明显改善。

Load Profile
变更前:

Per Second Per Transaction
Redo size: 244,606.59 13,269.94
Logical reads: 5,964.59 323.58
Block changes: 1,278.41 69.35
Physical reads: 339.03 18.39
Physical writes: 35.30 1.92
User calls: 693.44 37.62
Parses: 241.46 13.10
Hard parses: 0.16 0.01
Sorts: 97.93 5.31
Logons: 16.05 0.87
Executes: 617.55 33.50
Transactions: 18.43

变更后:

Per Second Per Transaction
Redo size: 314,037.68 4,249.08
Logical reads: 7,939.19 107.42
Block changes: 1,629.35 22.05
Physical reads: 221.23 2.99
Physical writes: 41.85 0.57
User calls: 1,005.17 13.60
Parses: 76.15 1.03
Hard parses: 0.16 0.00
Sorts: 37.36 0.51
Logons: 0.36 0.00
Executes: 810.16 10.96
Transactions: 73.91

Top 5 Timed Events
变更前:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 2,430 68.6
db file sequential read 84,286 416 5 11.7 User I/O
log file sync 63,773 266 4 7.5 Commit
db file scattered read 74,972 235 3 6.6 User I/O
enq: TX – row lock contention 463 229 494 6.5 Application

变更后:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 1,661 74.0
log file sync 167,658 473 3 21.1 Commit
db file sequential read 91,101 411 5 18.3 User I/O
wait for scn ack 145,796 142 1 6.3 Other
log file parallel write 166,143 121 1 5.4 System I/O

Time Model Statistics

变更前:

Statistic Name Time (s) % of DB Time
sql execute elapsed time 2,603.73 73.47
DB CPU 2,430.37 68.58
connection management call elapsed time 511.90 14.45
parse time elapsed 163.60 4.62
PL/SQL execution elapsed time 84.88 2.40
hard parse elapsed time 27.08 0.76
sequence load elapsed time 17.88 0.50
hard parse (sharing criteria) elapsed time 0.01 0.00
repeated bind elapsed time 0.00 0.00
DB time 3,543.74
background elapsed time 513.68
background cpu time 351.72

变更后:

Statistic Name Time (s) % of DB Time
DB CPU 1,661.42 74.02
sql execute elapsed time 1,558.64 69.44
PL/SQL execution elapsed time 66.66 2.97
parse time elapsed 37.24 1.66
hard parse elapsed time 15.09 0.67
connection management call elapsed time 8.37 0.37
sequence load elapsed time 3.53 0.16
PL/SQL compilation elapsed time 0.49 0.02
hard parse (sharing criteria) elapsed time 0.08 0.00
failed parse elapsed time 0.08 0.00
repeated bind elapsed time 0.00 0.00
DB time 2,244.66
background elapsed time 669.28
background cpu time 382.82

性能分析:

从这2个awr报告对比来看修改为长连接后单位小时的CPU TIME与DB TIME均有所下降，CPU TIME从原来的2430s下降到1661s,降幅为769s。但分析2个报告中的每秒逻辑读可以发现修改为长连接后的逻辑读反而增加了。CPU TIME主要可以分为parse cpu,execute cpu和fetch cpu。短连接时一小时的parse time即解析时间为163s；另外因为短连接时每秒登录数达到16个，Oracle为建立连接(connection management call)耗时511s。

总结以下几点:
1.短连接情况下因为新建立的会话没有缓存游标信息，进而导致无法避免大量的软解析，解析消耗了163s的DB TIME。修改为长连接后解析数量明显减少，解析仅消耗37s的DB TIME。
2.短连接情况下每秒登录数达到16次，建立连接(connection)同样会消耗大量的CPU TIME，这里connection management call消耗了约500s的CPU TIME。修改为长连接后每秒Logons数为0.36，节约了大量无谓的CPU浪费。
3.改为长连接后Top SQL的平均每次逻辑读并未下降，部分Top SQL的执行次数还有所增加；可见通过减少不必要的解析和反复建立连接，系统的性能得到了释放
4.原短连接的AWR报告中显示该时段内出现行锁等待(row lock contention)共463次，总耗时为229s,该等待事件也是造成2个报告中DB TIME差异的一个因素。而长连接报告中则没有该等待出现，这可能是出于偶然，也可能是程序修改导致的。

Filed Under: Oracle, Oracle SQL性能调优 Tagged With: ,

沪ICP备14014813号-2

沪公网安备 31010802001379号