DBA日记

hpux+10.2.0.4RAC

数据库挂起

alert日志如下：

虽然bug的版本差点，不过看现象差不多。重启节点2后正常。

*************************************************************
This article is being delivered in Draft form and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************

  >> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<

...
>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<
row cache enqueue: session: 70000001b542d48, mode: N, request: S
  row cache parent object: address=700000036f27628 cid=0(dc_tablespaces)
  hash=a6840aa5 typ=9 transaction=0 flags=00008000
...

环境：10.2.0.1 RAC
         RHEL 5.2

用户发现系统性能下降厉害，检查日志发现：

节点2报错：

Tue Mar 24 17:16:44 2009 GES: Potential blocker (pid=373) on resource TX-0x370008-0x147; enqueue info in file /u01/app/oracle/admin/jldb/udump/jldb2_ora_24975.trc and D IAG trace file Tue Mar 24 17:20:41 2009 GES: Potential blocker (pid=7664) on resource TX-0x490011-0x2a; enqueue info in file /u01/app/oracle/admin/jldb/bdump/jldb2_lmd0_29076.trc and DIAG trace file

trc文件中内容如下

*** 2009-03-23 21:24:58.021 user session for deadlock lock 0x1807f86f0   pid=217 serial=431 audsid=89970 user: 64/JLDBCS   O/S info: user: Administrator, term: unknown, ospid: , machine: APP3             program: JDBC Thin Client   application name: JDBC Thin Client, hash value=0   Current SQL Statement: update multisalvationobject mso set mso_order  = :"SYS_B_0", mso_sign   = :"SYS_B_1", mso_time   = sysdate, mso_emp    = :"SYS_B_2", mso_result = :"SYS_B_3", mso.mso_money =(select o.tempmoney from opttemp o where o.sign = :"SYS_B_4" and mso.mso_ot_id=o.tempid) where  exists (select tempid from opttemp o where o.sign = :"SYS_B_5" and so.mso_ot_id=tempid ) and mso.sb_id = :"SYS_B_6" and mso.st_id= :"SYS_B_7" and mso.mso_objecttype = :"SYS_B_8" and mso.mso_flag = :"SYS_B_9" Global blockers dump start:--------------------------------- DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x620020][0x15],[TX] ----------resource 0x0x16cb8cdc8---------------------- resname       : [0x620020][0x15],[TX] Local node    : 1 dir_node      : 1 master_node   : 1 hv idx        : 9 hv last r.inc : 6 current inc   : 12 hv status     : 0 hv master     : 0 open options  : dd grant_bits    : KJUSERNL KJUSEREX grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX count         : 1         0         0         0         0         1 val_state     : KJUSERVS_NOVALUE valblk        : 0x00000000000000000000000000000000 . access_node   : 1 vbreq_state   : 0 state         : x0 resp          : 0x16cb8cdc8 On Scan_q?    : N Total accesses: 77 Imm.  accesses: 58 Granted_locks : 1 Cvting_locks  : 1 value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 GRANTED_Q : lp 0x1807f85a0 gl KJUSEREX rp 0x16cb8cdc8 [0x620020][0x15],[TX]   master 1 gl owner 0x18ada82e0 possible pid 12343 xid DB000-0002-00000008 bast 0 rseq 1 mseq 0 history 0x14951495   open opt KJUSERDEADLOCK CONVERT_Q: lp 0x1807f86f0 gl KJUSERNL rl KJUSEREX rp 0x16cb8cdc8 [0x620020][0x15],[TX]   master 1 gl owner 0x183db6e80 possible pid 11575 xid D9000-0002-0000002F bast 0 rseq 1 mseq 0 history 0x1495149a   convert opt KJUSERGETVALUE ----------enqueue 0x0x1807f85a0------------------------ lock version     : 309 Owner node       : 1 grant_level      : KJUSEREX req_level        : KJUSEREX bast_level       : KJUSERNL notify_func      : (nil) resp             : 0x16cb8cdc8 procp            : 0x1801090e8 ................. kjdrchkdrm: found an RM request in the request queue

同时，另外一个节点报：

Tue Mar 24 17:26:41 2009 Global Enqueue Services Deadlock detected. More info in file /u01/app/oracle/admin/jldb/bdump/jldb1_lmd0_11622.trc. Tue Mar 24 17:28:04 2009 Global Enqueue Services Deadlock detected. More info in file

trace文件内容：

*** 2009-03-23 11:23:17.051 user session for deadlock lock 0x17f6c90a8   pid=362 serial=3 audsid=86656 user: 64/JLDBCS   O/S info: user: Administrator, term: unknown, ospid: , machine: APP3             program: JDBC Thin Client   application name: JDBC Thin Client, hash value=0   Current SQL Statement:   delete multisalvationobject ss  where ss.mso_order= :"SYS_B_0" and exists (sel ect :"SYS_B_1" from family f where f.f_familyid = ss.mso_ot_id and f.on_no like :"SYS_B_2" ) ENQUEUE DUMP REQUEST: from 1.22126 on [0x2f0029][0x10a],[TX] for reason 3 mtype 0 DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x2f0029][0x10a],[TX] ----------resource 0x0x16dab1890---------------------- resname       : [0x2f0029][0x10a],[TX] Local node    : 0 dir_node      : 1 master_node   : 1 hv idx        : 7 hv last r.inc : 6 current inc   : 8 hv status     : 0 hv master     : 0 open options  : dd Held mode     : KJUSERNL Cvt mode      : KJUSEREX Next Cvt mode : KJUSERNL msg_seq       : 0x1 res_seq       : 3 grant_bits    : KJUSERNL grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX count         : 1         0         0         0         0         0 val_state     : KJUSERVS_NOVALUE valblk        : 0x00000000000000000000000000000000 . access_node   : 1 vbreq_state   : 0 state         : x8 resp          : 0x16dab1890 On Scan_q?    : N Total accesses: 73 Imm.  accesses: 62 Granted_locks : 0 Cvting_locks  : 1 value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 GRANTED_Q : CONVERT_Q: lp 0x17f6c90a8 gl KJUSERNL rl KJUSEREX rp 0x16dab1890 [0x2f0029][0x10a],[TX]   master 1 gl owner 0x18ad7ab00 possible pid 32590 xid 16A000-0001-00000002 bast 0 rseq 3 mseq 0 history 0x1495149a   convert opt KJUSERGETVALUE ----------enqueue 0x0x17f6c90a8------------------------ lock version     : 3709 Owner node       : 0 grant_level      : KJUSERNL req_level        : KJUSEREX bast_level       : KJUSERNL notify_func      : (nil) group lock owner : 0x18ad7ab00 possible pid     : 32590 xid              : 16A000-0001-00000002 dd_time          : 74.0 secs dd_count         : 3 timeout          : 0.0 secs On_timer_q?      : N On_dd_q?         : Y lock_state       : OPENING CONVERTING Open Options     : KJUSERDEADLOCK Convert options  : KJUSERGETVALUE History          : 0x1495149a Msg_Seq          : 0x0 res_seq          : 3 valblk           : 0x00000000000000000000000000000000 . DUMP LOCAL BLOCKER: initiate state dump for   possible owner[362.32590] Submitting asynchronized dump request [28] Global blockers dump start:--------------------------------- DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x2f0029][0x10a],[TX] ----------resource 0x0x16dab1890---------------------- resname       : [0x2f0029][0x10a],[TX] Local node    : 0 dir_node      : 1 master_node   : 1 hv idx        : 7 hv last r.inc : 6 current inc   : 8 hv status     : 0 hv master     : 0 open options  : dd Held mode     : KJUSERNL Cvt mode      : KJUSEREX Next Cvt mode : KJUSERNL msg_seq       : 0x1 res_seq       : 3 grant_bits    : KJUSERNL grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX count         : 1         0         0         0         0         0 val_state     : KJUSERVS_NOVALUE valblk        : 0x00000000000000000000000000000000 . access_node   : 1 vbreq_state   : 0 state         : x8 resp          : 0x16dab1890 On Scan_q?    : N Total accesses: 75 Imm.  accesses: 63 Granted_locks : 0 Cvting_locks  : 1 value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 GRANTED_Q : CONVERT_Q: lp 0x17f6c90a8 gl KJUSERNL rl KJUSEREX rp 0x16dab1890 [0x2f0029][0x10a],[TX]   master 1 gl owner 0x18ad7ab00 possible pid 32590 xid 16A000-0001-00000002 bast 0 rseq 3 mseq 0 history 0x1495149a   convert opt KJUSERGETVALUE ----------enqueue 0x0x17f6c90a8------------------------

metalink上查询了trace文件中很多关键字，出来都是为解决的bug信息。

因为只有这个模块会报错，所以考虑调整这个模块的性能，希望能减少死锁的发生。昨天做了如下工作：

1.	删除opttemp表内遗留的错误数据(80多万条),并在sign列上加索引
2.	在mso表上增加索引2个

提示不支持ocfs2做ocr和votingdisk。
ocfs2就是为rac开发的，不可能不支持，所以一定是遗漏了什么。

查看手册，原来ocfs2给redolog、dafafile、controlfile、ocr、votingdisk等使用时，需要在mount时加上datavolume选项：

mount -t ocfs2 -o datavolume /dev/emcpowera /oracluster

运行vipca也报错了，网上找到了解决办法：

在oracle2上执行root.sh时，会提示一个错误，关于vipca无法执行的。于是乎，我手动运行/oracle/product/10.2.0/crs/bin/vipca，结果又告诉我找不到libpthread.so.0。晕～～

你也应该遇到吧！！ 别急这是个bug，我们这样来解决：

现在再运行vipca试试，什么？又出现个错误？

是不是类似下面的错误：

Error 0(Native: listNetInterfaces:[3])
   [Error 0(Native: listNetInterfaces:[3])]

阿弥佗佛,压住火气，这样来解决：

Dearxxx,
Congratulations! on the successful completion of the Oracle DBA 10g Certified Master practicum.
You are now a member of an elite group of Oracle professionals. You will receive your Oracle DBA 10g Certified Master fulfillment kit that includes a congratulations letter, OCM certificate, OCM ID card, and denim OCM shirt to the mailing address mentioned in your Oracle DBA 10g OCM Hands-on course requirement Form.
Address : xxxxxxxxxxxxxxxx,
Changchun City Jilin Province
China
mobile phone:xxxxxxxxxxx/ (86)0431xxxxxxxx
Shirt Size: L
Please feel free to contact the OCP team at ocpexam_ww@oracle.com if you have questions.
Kind regards,
Oracle Certification Program

-bash-2.05b$ ./emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0. 
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
Starting agent ..... failed.
The agentTZRegion value in /u01/app/oracle/OracleHomes/agent10g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(<agent_name>, <new_tz_rgn>) to get the value propagated to repository.
Consult the log files in: /u01/app/oracle/OracleHomes/agent10g/sysman/log

-bash-2.05b$ ./emdctl validateTZ agent PST8PDT
tzOffset for PST8PDT is -480(min), but agent is runnning with tzOffset -420(min)
trying again after waiting for 1 sec to account for daylight transition
tzOffset for PST8PDT is -480(min), but agent is runnning with tzOffset -420(min)

查看sysman/config/emd.properties里面，时区设置的也是PST8PDT

可以用这个下面命令解决：

export TZ=GMT (用MDT也可以,PST8PDT不行,其他没尝试）

这时emd.properties里面的agentTZRegion=GMT，原先是PST8PDT.
emctl resetTZ agent
emctl start agent

需要启动flash recovery area，才能使用flashback database
在mount状态执行alter database flashback on；打开flashback database功能。
在mount状态执行flashback database to scn/timestamp语句闪回数据库。然后用resetlogs模式打开数据库。

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  801112064 bytes
Fixed Size                  1221972 bytes
Variable Size             432016044 bytes
Database Buffers          360710144 bytes
Redo Buffers                7163904 bytes
Database mounted.
Database opened.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20G scope=spfile;

System altered.

SQL>  alter system set DB_RECOVERY_FILE_DEST_SIZE=20G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest=/u01/app/oracle/flash_recovery_area scope=both;
alter system set db_recovery_file_dest=/u01/app/oracle/flash_recovery_area scope=both
                                        *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=both;

System altered.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  801112064 bytes
Fixed Size                  1221972 bytes
Variable Size             432016044 bytes
Database Buffers          360710144 bytes
Redo Buffers                7163904 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL>

1.启用&关闭回收站
   10gR1：_recyclebin=true/false (隐含参数)
   10gR2:   recyclebin=on/on  (常规动态参数)

2.视图  recyclebin,dba_recyclebin,user_recyclebin;

3.flashback table功能
   flashback table t1 to before drop rename to t2;

4.限制条件
    undo_management=auto
    删除user时，user下的表不会放到recyclebin中
    删除cluster时，cluster的成员表不会放到recyclebin中
    删除type时，相关的对象不会放到recyclebin中。

5.清除recyclebin中的对象。
   purge table BIN$jsleilx392mk2=293$0;
   purge table table_name；
   purge tablespace tbs1;
   purge recyclebin;( as sysdba)

oms突然显示agent无法连接，查emagent.log显示启动成功，有emagent.trc产生，报如下错误：

2006-08-24 11:17:48,140 [SeverityLoad https://qehtmp08.server.ha.org.hk:3872/emd/main/] ERROR
eml.XMLLoader markFileAsError.854 - Error repeated 346 times: ERROR-400|ORA-01801: date format is
too long for internal buffer

2006-08-24 11:17:48,187 [SeverityLoad https://qehtmp08.server.ha.org.hk:3872/emd/main/] ERROR
eml.XMLLoader markFileAsError.854 - Error repeated 347 times: ERROR-400|ORA-01801: date format is
too long for internal buffer

informatica安装完成好几天了，最近太忙，一直没用。
今天yyfx系统培训的时候LHM说pc的power designer无法连接服务器，报如下错误：
Unable to get repositories for domain Domain_jl2
Error：【PCSF_46008】Cannot connect to domain[Domain_jl2]to
look up service[coreservices/DomainConfigurationService].

尝试配置一下，和LHM遇到的错误一样。查到一篇文章http://www.dwway.com/bbs/showthread.php?t=20289，看了看没什么帮助。google了一下国外的文章，也没解决问题。只是知道了这个错误号一般和网络之类的问题有关。

检查windows firewall、网络端口等，都没有问题。
按理说市场占有率这么高的产品，不应该出这么低级的错误啊 ，正抓狂呢

发现原来hosts文件写的不对，informatica要求hosts里制定的服务器主机名必须和服务器真实主机名一致，而不是随便写一个，解析出IP就可以。

host改成 10.*.*.* jl2，搞定。

2008年5月5日9：20分，成功升级老婆给我生了一个9斤2两的男孩

.

Oa系统安装时遇到的现象
aix+fastT。
2号机：imporgvg报错（过去太久了，不记得错误号了），然后rmdev -dl hdisk3，再执行cfgmgr，输出正常。

lspv发现已经找到hdisk3，不过没有现实pvid。尝试多次仍然不成功。后无意中发现cfgmgr找到pvid了。一直没找到原因。

现在知道了，因为1号机当时vg没有正常varyoff，导致vg被锁，所以2号机cfgmgr得不到pv id。只要在1号机重新varyonvg，再varyoffvg，2号机就正常了。

内容摘要：
9i以前，字符类型长度最终转换成一定数量的bytes。这带来一个问题就是对于不同语言，比如英语和汉语，同一个字段可存储的字符数不同。
为了解决这个问题，9i引入了以下三种varchar2的定义方式。

1. VARCHAR2(20)
2. VARCHAR2(20 BYTE)
3. VARCHAR2(20 CHAR)

1表示使用NLS_LENGTH_SEMANTICS 参数指定的varchar2定义方式（默认是byte）
2表示定义varchar2时，长度使用byte，不管这个列能存储多少个字符。例子中，如果存ascii码能存20，存中文只能存10个。
3表示定义varchar2时，长度使用char，不管这些字符是由多少bytes组成的。例子中，不管ascii还是unicode，都能存20个。当然最后占用的byte是不一样的。

这些特性对char，nvarchar2，nchar同样适用

需要特别提一下，substr，length，instr对unicode有不同版本。使用时需要注意。

文中还特别提到，如何在byte  semantics 和character semantics 中切换一个schema的数据，步骤如下：

******************************************************

Technology SQL

Globalize with Character Semantics

By Jonathan Gennick

Character semantics help enable globalization and enhance code portability.

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

Globalization is a big word that refers to the practice of developing applications that work seamlessly across languages and cultures. The need for such applications is growing, driven in part by ever-larger transnational corporations and in part by a global internet that makes it possible for a user in, say, Iqaluit to run an application against a database that might be hosted in Dnipropetrovs'k.

One of the first issues you run up against when building a global application is the need to represent characters from more than one language. Ukrainians, for example, might prefer to see Dnipropetrovs'k in its Cyrillic form. When you develop an application to support multiple languages, it's increasingly likely that you'll end up using a multibyte character set. Unicode UTF-8, or one of the other Unicode character sets, is a likely choice.

Working with multibyte character sets can present some special challenges, especially if, like me, you've grown up using single-byte character sets such as US7ASCII. Look no further than Listing 1 for an example of what you might run into when you take an application developed under the implicit assumption of a single-byte character set and port it to a Unicode UTF-8 environment.

Listing 1 shows two different lengths for an ostensibly fixed-length character string. If you're used to thinking of CHAR variables (or database columns) as fixed-length strings, you'll need to readjust your world view. Why? Because there's a disconnect between characters and bytes, which I'll be exploring throughout this article. I'll also introduce Oracle9i's new support for character semantics. Seemingly simple on the surface, character semantics eliminate this disconnect and can make it tremendously easy to convert existing applications and database schemas to use multibyte character sets.

Bytes Versus Characters

Is "a" a byte or a character? Is there a difference? In the western world, with our simple alphabets and single-byte character sets, we've long been accustomed to thinking of bytes and characters as being one and the same. We often don't distinguish between the two as we should. We look at a declaration such as CHAR(3), and we see that as a three-character string. But there is a distinction to be made: a byte is a unit of storage, whereas a character is a symbol used in a language or system of writing. The declaration of CHAR(3) shown in Listing 1 resulted in a 3-byte string, which may or may not hold three characters.

Working with multibyte character sets when your declarations and string functions all operate in terms of bytes can be a bit of a challenge. Listing 1, for example, was generated on a database that used Unicode UTF-8 (AL32UTF8) as the database character set. The variable x was declared as CHAR(3), which resulted in an allocation of three bytes. When the single-byte value a was assigned to x, the other two bytes were padded with spaces (a space is also a single byte), and thus the resulting string was three characters long. When the two-byte character was assigned to x, only one byte remained to be padded with a single space. The result? A two-character-long string that happened to be using three bytes of storage.

Character Semantics

Recognizing the confusion surrounding bytes versus characters, Oracle formalized the distinction between byte and character semantics when it released Oracle9i Database. Rather than being forced to declare strings in terms of bytes and then work with them in terms of characters, you can choose whether to declare strings in terms of characters or bytes. You can write declarations such as the following:

x CHAR(3 BYTE)
x CHAR(3 CHAR)

The first of these declarations explicitly declares x as a 3-byte CHAR variable (or it could be a database column). The second explicitly declares x as a three-character CHAR variable. How much storage will be allocated for those three characters? That depends on the underlying character set. The general rule is that Oracle9i Database will look at the maximum number of bytes per character used by the underlying character set and will multiply that value by the number of characters specified in your declaration. In the case of Unicode UTF-8, three bytes will be allocated for each character, so a declaration of CHAR(3 CHAR) results in an allocation of nine bytes.

Declaring a variable or column in terms of bytes is referred to as using byte semantics. Declaring a variable or column in terms of characters is referred to as using character semantics.

The BYTE and CHAR qualifiers can also be applied to VARCHAR2 declarations:

x VARCHAR2(3 BYTE)

x VARCHAR2(3 CHAR)

However, you cannot specify the BYTE and CHAR qualifiers for NCHAR and NVARCHAR2 declarations. Neither of the following declarations is valid:

x NCHAR(3 BYTE)
x NVARCHAR2(3 CHAR)

In Oracle9i Database, declarations of NCHAR and NVARCHAR2 variables and columns are always in terms of characters. Thus, there's no need for the BYTE and CHAR qualifiers in such declarations.

Why Bother?

If a declaration of CHAR(3 CHAR) results in an allocation of nine bytes when using Unicode UTF-8 as the database character set, why not use CHAR(9 BYTE) or simply CHAR(9)? Are there reasons to bother with character semantics and the CHAR qualifier? The answer is yes; I think there are some compelling reasons to use character semantics. In fact, I'd go so far as to argue that you should use character semantics as your default allocation method.

When working with character strings, you do almost everything in terms of characters. In the western world, when you declare a string as CHAR(3), you usually do that because you want to put three characters into it. When you compute the length of a string, it's usually to find the number of characters in it. When you take the substring of a string, you want a substring of complete characters; you don't want only part of a multibyte character. When you search a string to see whether it contains a substring, you are typically looking for a substring of characters, not of random bytes. Given all this, it makes sense to extend the concept of working with characters all the way back to the beginning, to the initial declaration of the string.

Using byte semantics, when you move an application from US7ASCII to UTF-8 in order to support characters from different languages, your CHAR(3) strings may or may not hold three characters, depending on which three characters you happen to be storing at any given time. Under those circumstances, your application will likely fail in short order. You would then need to go through all your PL/SQL source code and all your CREATE TABLE statements and adjust all of your character string declarations to accommodate the new character set. That's not only a huge amount of unnecessary work, it's also a huge opportunity to make mistakes, thereby introducing bugs into what used to be a working application.

There's a better way: declare character variables using character semantics rather than byte semantics. A declaration of CHAR(3 CHAR) will support a maximum of three characters regardless of which character set you end up using. A CHAR(3 CHAR) variable will hold three characters when you use US7ASCII, and it will still hold exactly three characters when you convert your application to Unicode UTF-8. Thus, using character semantics greatly increases the portability of your applications and your database schemas.

The issue of exact length is an interesting one. A declaration of CHAR(3 CHAR) will always support a maximum of three characters, regardless of the number of bytes used to represent those characters. Use a declaration of CHAR(9 BYTE) to allow for up to three Unicode UTF-8 characters, and you'll find that the string will often hold more than three characters. Listing 2 illustrates this. Whether this is a problem or not depends on your application. It's more of a problem with CHAR variables than with VARCHAR2 variables, because CHAR variables are always padded with spaces to fill out their maximum length. Thus, when mixing byte semantics with multibyte character sets, CHAR variables suddenly appear to be variable-length. Using character semantics eliminates all that confusion. Listing 3 uses character semantics to declare x as a fixed, three-character string. The underlying character set is still UTF-8, but you really don't need to be concerned with knowing that. Using character semantics guarantees you a three-character string regardless of character set, so the string is consistently padded to exactly three characters, not bytes, in length.

Length Semantics and String Functions

Oracle supports several built-in functions that operate on characters within a string. These functions are:

• INSTR. Searches a string for a specified sequence of characters.
• LENGTH. Returns the number of characters in a string.
• SUBSTR. Returns a specified portion, or substring, of a string.

Your choice of character or byte semantics affects only the declaration of a PL/SQL variable or a database column. Regardless of how you declare a variable or a column, in Oracle9i the INSTR, LENGTH, and SUBSTR functions always operate in terms of characters. You've seen this already in the operation of the LENGTH function. In Listings 1, 2 and 3, LENGTH always returned the number of characters in the string—never the number of bytes. Listing 4 provides an even more comprehensive example of how these functions operate. UTF-8 is still the character set. The string ãa is two characters long but consumes three bytes because ã is a two-byte character in UTF-8. The INSTR function finds the letter a in the second character position, even though the bytecode for that letter occupies the third byte. As before, LENGTH returns the number of characters in the string (2). SUBSTR(x,1,1) actually returns two bytes of the string, because the first character happens to be a two-byte character.

It's good that INSTR, LENGTH, and SUBSTR operate in terms of characters. You can take an application using these functions and install that application on a database using a different character set—it doesn't matter what that character set is—and all your application's calls to these functions will still provide sane, sensible, and correct results.

There are times, however, when you do actually need to work with strings as sequences of bytes rather than characters. For this reason, Oracle provides the functions INSTRB, LENGTHB, and SUBSTRB, which are shown in Listing 5. Note the suffix B on all these function names, which indicates that these functions operate in terms of bytes, not characters. Other than this difference, the three functions are identical to their non-B counterparts.

Character Semantics Default

If all Oracle did was add the ability to use CHAR(3 CHAR) rather than CHAR(3), that would be good, but you'd still have your work cut out for you when converting existing applications and schemas to UTF-8 or some other multibyte character set. Instead of being faced with the task of expanding CHAR(3) to CHAR(9), you'd be faced with the equally dismal task of changing all CHAR(3)s to CHAR(3 CHAR)s. Fortunately, Oracle9i Database provides the ability to set the default character semantics on either the session or instance level using the NLS_LENGTH_SEMANTICS parameter.

To convert an existing schema and its associated data from byte semantics and a single-byte character set to character semantics and a multibyte character set, such as UTF-8, you need only follow these steps: [The following steps have been corrected since the magazine was printed.]

1. Export the schema.
2. Issue an ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH command on the target database.
3. Stop and restart the instance so that the parameter change takes effect.
4. Drop the original schema.
5. Recreate the original schema and its tables (you can use import's show=Y option to get the CREATE TABLE statements). Columns in the recreated tables will use character semantics, because that's now the default.
6. Import the schema into the target database using the IGNORE=Y import option.

Because step 2 changed the default length semantics from byte semantics to character semantics, any CHAR or VARCHAR2 declarations encountered during the import will be interpreted as if the CHAR qualifier was specified. For example, CHAR(3) will be interpreted as CHAR(3 CHAR). The ability to set the default-length semantics on an instance-wide or session basis enables you to easily convert existing schema definitions or PL/SQL code to use character semantics without the need to tediously go through and add the CHAR qualifier to all your declarations.

Note: This process applies only to schemas composed entirely of tables. If your schema includes stored PL/SQL code, you'll need to recompile all that code to use character semantics. If your schema includes object types, you should precreate those types in step 5. Be sure to run a test conversion before attempting to convert a production schema.

Next Steps

READ
about Unicode
unicode.org

You can check the current value of NLS_LENGTH_SEMANTICS at any time by querying the NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS views. Changing the ?arameter does not affect any existing column or variable definitions. Columns declared using byte semantics will not be changed simply by changing the parameter. The parameter affects only new column or variable definitions.

Be aware that converting a schema to use a multibyte character set may involve more work than simply rebuilding the schema and recompiling any PL/SQL code to use character semantics. For example, you'll need to look at any external programs, such as those written in C++ or Java, because they may need to be adjusted to handle the multibyte data now coming from the database. Due diligence is still required.

The NLS_LENGTH_SEMANTICS parameter gives you the option of switching to character semantics globally or on a column-by-column basis. To introduce character semantics on a column-by-column basis, leave NLS_LENGTH_SEMANTICS at its default value of BYTE, and use the CHAR qualifier when declaring variables and columns. Change the default when it makes sense to make a wholesale change—for example, when you are developing a brand-new schema and application.

Conclusion

Newly introduced in Oracle9i Database, character semantics makes it much easier than before to work with multibyte character sets. No longer must you declare variables and columns in terms of bytes and then try to keep straight the number of characters such variables and columns will hold. Using character semantics eliminates that confusion by allowing you to work consistently in terms of characters.

Jonathan Gennick (Jonathan@Gennick.com) is an experienced Oracle DBA and an Oracle Certified Professional. He likes to explore new Oracle technologies and recently completed work on the Oracle SQL*Plus Pocket Reference, Second Edition (O'Reilly & Associates, 2002).

UNICODE STRING FUNCTIONS

There are variations of the INSTR, LENGTH, and SUBSTR functions designed specifically for working with Unicode. Looking at the LENGTH function, you'll find the LENGTHC, LENGTH2, and LENGTH4 functions for Unicode.

These functions distinguish between Unicode characters, Unicode code points, and Unicode code units. A code point is the numeric value corresponding to an entry in a Unicode encoding table, and often, but not always, to a Unicode character. For example, 0x00E3 is the code point for the letter "ã". However, that same letter can also be expressed using the code point 0x0061 (for "a") followed by the code point 0x0303 (for "˜").

The standard LENGTH function will interpret code point 0x0061 followed by code point 0x0303 as two separate characters. The LENGTHC function, because it is designed for use with Unicode, will recognize that in Unicode, 0x0061 followed by 0x0303 really represents just one character.

The term code unit refers to the actual representation of a Unicode code point. For example, the UTF-8 representation of the code point 0x0061 is the single byte 0x61. That one byte is a code unit. The UTF-16 representation of 0x0061 is the two bytes 0x0061, and those two bytes form one code unit. Sometimes a single code point is large enough that it needs to be represented as multiple code units. For example, the code point 0x1D11E, which represents the musical symbol G-clef (available at http://www.unicode.org/charts/PDF/U1D100.pdf), is represented in UTF-16 using two code units: 0xD834 followed by 0xDD1E. Neither value by itself is a code point. Only together do these two code units represent a single code point.

The LENGTH2 and LENGTH4 functions enable you to distinguish between code units and code points. LENGTH2 returns the number of code units in a Unicode string. LENGTH4 returns the number of code points in a Unicode string.

The Unicode function variants of the INSTR and SUBSTR function families parallel the Unicode variants of the LENGTH function. Just as there are LENGTHC, LENGTH2, and LENGTH4 Unicode functions, there are also INSTRC, INSTR2, INSTR4, SUBSTRC, SUBSTR2, and SUBSTR4 Unicode functions.

1.共享的vg，需要在建立的时候选择concurrent 兼容模式。建立完成后，需要能使用varyonvg -c oravg在两边同时能挂载。否则会出现哪边ha先启动，vg就被后启动的ha里面的varyonvg命令踢掉的情况。

2.oravg在oadb1创建后，在oadb2上import时，需要制定和oadb1上一样的major number。否则同步时会报错。

3.同步时报错

ERROR: Service adapter oadb1_serv on network net_ether_01 is on the same subnet as at least one of the communication interfaces on this network.Service labels must be on a different subnet when the network is configured
根据错误提示，boot ip和service ip不能在同一个网段。
google之，发现使用IP Aaddress Takeover via alias方式时，boot和service不能在同一个网段；而使用5.1以前的传统方式IPAT va Rep，boot和service就没有这个限制。
在smitty hacmp--->Extended Configuration--->Extended Topology Configuration--->Configure Hacmp Networks--->Change/Show a Network in the Hacmp Cluster选项里，把Enable IP Address Takeover via IP Aliases改成no，再同步就没问题了。

20号晚上，加完班刚到家，就接到了一个来自吉林的电话，说数据库起不来了。

据了解，数据库为920X,hp-ux，rac
据说是hpux突然找不到vg，然后hp的工程师又手工恢复了vg信息。

但是数据启动报错，提示ora-201，ora-202，ora-210的意思是控制文件和oracle不兼容
出现这个问题有两个原因:
1.compitible参数设置错误
2.控制文件损坏
尝试从spfile里面创建pfile，结果pfile为空。进一步判断spfile损坏了。
寻找建库时使用pfile，启用数据库，错误一样。分别使用不同的控制文件，也同样是这两个错误。

因为控制文件没有备份，好在alert是完整的，于是从alert里提取数据库的文件名称，手工创建controlfile，结果发现回滚表空间的数据文件无法加到控制文件中，用dbv验证，证实这两个文件已经损坏了。同时损坏的还有一些其他的数据文件。

去掉这些文件，创建控制文件成功，用新控制文件启动数据库，open数据库时，提示需要media recovery。因为回滚表空间已经坏了，media recovery已经变成了 mission impossible了。

因为控制文件和回滚表空间同时损坏，这个数据库要修复已经很难了。

能想到的就是加_corrupt_rollback_segments参数强制打开数据库，然后尝试exp数据了。

因为责任原因，这个操作负责这个系统的工程师不敢尝试。

最后oracle派了现场工程师，不过好像到目前未知，结论是一样的。现在正在搭建临时环境，恢复10月份的数据，并把故障库的裸设备进行备份，备份成功后，才能尝试强制打开数据库。

事已至此，已经没什么说的了。不过还是要从故障为什么发生中学到点什么吧。
这次故障，是由于没有给阵列划分zone，导致某厂家（dt）的技术人员在工作时，没有使用划分给他的磁盘，而使用了oravg的磁盘导致的。这个vg是oracle建库时使用的，redolog，unto、control、spfile、默认表空间等都在这个vg上。

据说这是oracle在国内遇到的最严重的一次故障......

vmware:一开始尝试用rhel自带的工具修改分辨率，不成功。尝试修改/etc/XFreeConfig，也不行。

查了一堆文档，也没解决。后来发现运行一下vmware tools自带的vmware-config-tools.pl，重新配置x就可以了

在第二块硬盘上安装了rhel3asu8.同样只能使用800×600×16位色或者1024×768×256色。用得我这个郁闷啊

无论怎么调整都不行。后来发现原来是限制默认的集成显卡现存为1m了，根本支持不了更高的分辨率，修改后，使用redhat-config-xfree68配置就可以了。想想在vmware里使用这个命令无法提高分辨率应该也是同样的原因。

aix上c程序编译的时候，默认是有内存使用限制的。如果程序使用内存很大，有些程序会报类似内存不足，然后产生core dump。
至于什么样的程序报错，什么样的程序不报错就不知道了。
可以通过给cc编译器增加参数的方式修改内存限制，参数为 cc -.....  -q maxmem=-1
-1表示没有限制。

Visual Age C++ compiler reference.pdf

今天zf问我的这个问题，让我想起来刚参加工作的时候（不记得是02年末还是03年初了），我们作为服务商，进行系统和数据库的安装；dt负责应用程序开发。
当时就遇到了这个问题，我和齐哥在现场。分析问题时，无非系统、数据库、或者应用的问题。可是因为客户和dt现场人员关系比较好，无论我们怎么分析问题、怎么解释，客户都认为问题出在我们这里，而dt没有问题，其实dt的理由只有一条：同样的程序在其他地市的机器上运行正常，所以在这里有问题，就不会是程序的问题。。折腾了大概有1周多，也没解决问题，我甚至找同学专门写了一个大量分配内存的c程序，也运行正常。
期间客户多次给我们老总打电话，投诉我们两个，说我们俩技术不行，让公司把我们撤回来，最后我们两个部门的经理都到了现场协调关系，但是并没有让我们撤走。
最后还是我们帮dt找到问题的原因，加了这个参数就好了。
虽然问题解决了，可是我们背了那么久的黑锅并没拿下来，报告写得轻描淡写，客户的上级单位（其实也是我们的上级单位）看到报告后甚至都不知道问题其实并不出在我们这里，只是觉得事情过去了，没有必要再追究而已。

lscfg -vl hdisk0

lscfg命令可以看到硬盘容量368400mb，FRU、PART NUMBER等信息。据此可以准备备件。

查看配置试用prtconf