db_block_checksum & db_block_checking

首先看两个参数的定义：
DB_BLOCK_CHECKSUM

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is true and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log.

If this parameter is set to false, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces.

Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. Turning on this feature typically causes only an additional 1% to 2% overhead. Therefore, Oracle Corporation recommends that you set DB_BLOCK_CHECKSUM to true.

DB_BLOCK_CHECKING

DB_BLOCK_CHECKING controls whether Oracle performs block checking for data blocks. When this parameter is set to true, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform block checking for blocks in the user tablespaces. However, block checking for the SYSTEM tablespace is always turned on.

Oracle checks a block by going through the data on the block, making sure it is self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to true if the performance overhead is acceptable.

这两个参数的含义经常让人混淆，虽然都是对block进行检查。
db_block_checksum 是在将数据块写到数据文件的时候对block内数据做一个校验写在块头，当读入时候重新计算校验和写出时候的校验对比，如果不同则认为是块损坏。这通常应该是由于脱离oracle以外在os或者硬件中出现了损坏，如果设置为false则只对系统表空间有效。从8i开始设置为true的时候也同时对log block进行校验。

db_block_checking 是当block发生任何变化的时候进行逻辑上的完整性和正确性检查，这在内存中进行，当发现错误就立即回退，设置为false则只对系统表空间有效。

这意味着，如果db_block_checking = false ,非系统表空间中数据在逻辑上可能已经损坏，但是 db_block_checksum 却是无法检查出来的，原样写到磁盘原样读到内存，因为它只校验块在写出后和读入之间是否发生变化而不检查写出前是否存在 逻辑上的正确。

比如有时索引块损坏，造成通过索引无法获得数据，但是读索引块的时候并没有出1578错误，可能就是这个原因。

关于这两个参数，也可以参考tom的解释：
http://asktom.oracle.com/pls/ask/f?p=4950:8:17926674857995971206::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1094433279412

oracle关于锁问题的一个bug

关于oracle锁问题的阐述，有两篇文章非常值得一看：
1、Oracle多粒度封锁机制研究；
2、DB2和 Oracle的并发控制（锁）比较.

我最近遇到的问题是：
selelct ... for update 获得的是 row exclusive lock,按照官方文档因该获得row share。
（测试的版本是oracle 9.2.0.8,winxp,row_locking 等于always）

后经过证实这是oracle的一个bug：4115353，是在修复bug:3646162时引入的，这个问题困惑了我好几天，特记录在此备忘。

(20060913)今日学习

1、join methods
2、Positional Versus Named Notation for Subprogram Parameters 及其限制 (官方);
(只是针对函数，sqlplus中用execute执行存储过程可以使用Named Notation.)
3、dbms_plan工具的用法:
(1) itpub: 使用dbms_xplan工具查看执行计划;
(2) eygle: Oracle10gR2 autotrace function was changed and enhanced ;
(3) builder.com.cn: 用Oracle的 DBMS_XPLAN函数轻松格式化EXPLAIN PLAN结果

关于oracle7的一点资料

这一周多时间研究了一下oracle7,发现oracle从7到8、8i、9i、10g，在内存管理、存储等等方面确实发生了很大的变化；我开始接触oracle是在99年，8.0.4的版本，买的盗版光盘，装在windowns上，打开sqlplus，就是进不去，不知道用户名和密码，真是着急啊！相信不少人有同样的经历，当我知道oracle安装后有两个默认的用户(system/manager、sys/change_on_install)的时候，oracle才向我敞开了大门。
因为oracle7的版本实在是有点老了，找到安装的介质颇费了一番周折，我把相关的url列在下面：
1、oracle734 personal for winnt
2、oracle734 server from sco unix
3、sco unix 5.0.7
4、oracle734 for sco unix安装文档

关于安装我就不详细写了，网上有很多现成的文档，我在windows 2000/xp、sco unix 5.0.7 (on vmware)上都安装成功了，需要说明的是在windows 2000/xp上安装oracle7需要先安装Oracle Installer v. 3.3.1.1.0C，然后再用这个版本的Installer安装oracle7.

可以在metalink上下载Oracle Installer v. 3.3.1.1.0C (patch 826393).

在oracle9i中监控索引的使用

索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在Oracle9i之前，要知道一个索引是否被使用是困难的，而Oracle 9i中提供了一个有效的监控方法:ALTER INDEX MONITORING USAGE。

但是使用这个特性时需要注意：
"To execute the MONITORING USAGE clause, the index must be in your own schema."

具体说明请参考online document。

另外，有一篇很好的文章：http://echo.sharera.com/blog/BlogTopic/5630.htm,不过作者的脚本有一些错误:V$ALL_OBJECT_USAGE因该替换为V\$ALL_OBJECT_USAGE.

sqlplus 中set autotrace on 出现sp2-0618错误的解决过程

今天在sqlplus中设置set autotrace 时出错：
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

解决过程请参考asktom文章：http://asktom.oracle.com/~tkyte/article1/autotrace.html

WIN: Manually Removing all Oracle Components on Microsoft Windows Platforms

oracle生产库运行在windows下的情况较少，昨天解决安装问题时，看到metalink一篇文章，讲如何在windows下彻底删除oracle，贴在这里，方便大家查阅。

(Metalink Notes:124353.1)

Title: Manually Removing all Oracle Components on Microsoft Windows Platforms

PURPOSE
-------
This article describes the procedure how to manually perform the actions to
create a "clean machine" on Microsoft Windows 95/98/NT/2000/XP and how to
manually remove all Oracle components (e.g.: oracle RDBMS database server,
IAS, OEM, client installations, etc) and services from your computer.


SCOPE & APPLICATION
-------------------
This article is intended for DBA's on the Microsoft Windows platform.

WARNING:

1. Remove all Oracle components from your computer only as a last resort,
and only if you want to remove ALL Oracle components from your system.

2. These instructions remove all Oracle components, services, and registry
entries from your computer. In addition, any database files in the
subdirectories under ORACLE_BASE\ORADATA\ are also removed.

3. These procedures will also remove Oracle network configuration files,
user written scripts and any other user generated files that may be stored
in Oracle_Base directories.

4. Exercise extreme care when removing registry entries. Removing incorrect
entries can severely compromise your computer.


MANUALLY REMOVING ALL ORACLE COMPONENTS AND SERVICES FROM YOUR COMPUTER
-----------------------------------------------------------------------
Section A (steps 1-6) describes the removal of all Oracle components on
Windows NT,2000 and XP.
Section B (steps 1-4) describes the removal of all Oracle components on
Windows 95 and Windows 98.


A. Removing Components on Windows NT/2000/XP.
---------------------------------------------
To remove all Oracle components from a computer on Windows NT/2000/XP:

1. Check privileges:
-----------------
1.a. Ensure you are logged in as a user with Administrator privileges.


2. Stop all Oracle services (if any are running):
----------------------------------------------
2.a. NT: Choose Start > Settings > Control Panel > Services.
2000,XP: Right click My Computer > Manage > Services and Applications >
> Services

2.b. If any Oracle services (their names begin with Oracle) exist and have
the status Started, select the service and click Stop.

2.c. Click Close to exit the Services window.

2.d. Close the Control Panel/Computer Management window.


3. Remove the entries in the Windows registry:
-------------------------------------------
3.a. Start the registry editor:
Choose Start > Run > regedit

Note: On Windows NT you can use regedt32 instead. The searching
capabilities of regedt32 is limited compared to regedit. It will be
needed to be able to edit 32-bit entries in the registry. Since we
are not going to update any 32-bit entry it is not needed.


3.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the
Oracle Universal Installer (OUI). The default location is
C:\Program Files\Oracle\Inventory. If this value is different, make
note of it, so we can delete these files later.
Delete this ORACLE key.

3.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and
remove all keys under here which are related with the
"Oracle ODBC Driver"

3.d. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and remove
all keys under here that begin with ORACLE or ORAWEB.

3.e. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\...
...\Application and remove all keys under here that begin with ORACLE.

3.f. Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\...
...\Uninstall and remove any entries related to Oracle.

3.g. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).

3.h. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Classes, remove all keys that begin
with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).

3.i. Go to HKEY_CURRENT_USER\Software\Oracle, delete this ORACLE key.

3.j. Go to HKEY_USERS\…\Software\Oracle, delete this ORACLE key.

3.k. Close the registry.


4. Clean up the environment settings:
----------------------------------
4.a. NT: Choose Start > Settings > Control Panel > System > Environment tab
2000,XP: Choose Start > Settings > Control Panel > System >
Advanced tab > Environment variables.

4.b. At "System Variables" click on the variable PATH in order to modify
the value. For example, you may see a path similar to this one:
C:\ORACLE\ORA81\BIN;C:\PROGRAM FILES\ORACLE\JRE\1.1.7\BIN

4.c. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN path.

4.d. If JRE was installed by Oracle, remove the JRE path.

4.e. If there is a CLASSPATH variable under "System Variables", first make
note of the path defined, then delete it. This variable can be added
back at a later date if needed.

4.f. Check if there are any other Oracle variables set in "System Variables",
ORACLE_HOME, ORACLE_SID, TNS_ADMIN, JSERV or WV_GATEWAY_CFG. If these
exist, delete them also.

4.g. Click on APPLY and OK.

4.h. Close the Control Panel window.


5. Delete the software and icons:
------------------------------
5.a. NT: Choose Start > Programs > Windows NT Explorer.
2000,XP: Choose Start > Programs > Accessories > Windows Explorer.

5.b. NT: Go to %SystemDrive%\WINNT\PROFILES\ALL USERS\START MENU\PROGRAMS
2000,XP: Go to %SystemDrive%\DOCUMENTS AND SETTINGS\ALL USERS\...
...\START MENU\PROGRAMS
Note 1: These locations depend on whether OS was upgraded from NT, or
this was a fresh install of 2000/XP.
Note 2: To locate your System Drive, type in DOS-box: echo %SystemDrive%

and delete the following icons:
- Oracle Installation Products
- PRODUCT_NAME - HOME_NAME e.g.
Oracle for Windows NT - Dev6i
Oracle Reports 6i - Dev6i
Oracle Olap Client 2.2 - Dev6i
Oracle9i Lite
Oracle - OraHome92


5.c. Go to %SystemDrive%\Program Files\Oracle or the location of INST_LOC as
noted earlier in step 3.b. and delete this directory.
Note: In order to successfully delete all files, you may have to reboot
your computer first, in order to clear Operating System locks
on those files.

5.d. Go to the temporary directory and delete all files and directories in
here (see note in 5.c.).
NT: %SystemDrive%\Temp
2000,XP:
%SystemDrive%\Documents and Settings\\Local Settings\Temp\

5.e. Go to the drive where the Oracle software is installed on your machine
and delete all ORACLE_BASE directories on your hard drive (see note in
5.c.).

5.f. Close the Windows Explorer / Windows NT Explorer.


6. Finish the removal:
-------------------
6.a. Empty the recycle bin
Right click on recycle bin > Empty Recycle Bin.

6.b. Reboot your computer.

6.c. Optionally: If you are on Windows 2000 or XP run the System Defragmenter
utility:
- from Control Panel, select Administrative Tools > Computer Management
- expand Storage, then select Disk Defragmenter
- highlight each virtual drive, in turn, and click Defragment
- reboot your computer when finished.


B. Removing Components on Windows 95 or Windows 98.
---------------------------------------------------
To remove all Oracle components from a computer on Windows 95/Windows 98:

1. Remove the entries in the Windows registry:
-------------------------------------------
1.a. Start the registry editor at the MS-DOS command prompt:
C:\> REGEDIT

1.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the Oracle
Universal Installer. The default location is
C:\Program Files\Oracle\Inventory
If this value is different, make note of it, so we can delete these files
later. Delete this ORACLE key.

1.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and
remove all keys under here which are related with the
"Oracle ODBC Driver".

1.d. Go to:
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall
and remove any entries related to Oracle.

1.e. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).

1.f. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Classes, remove all keys that begin
with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).

1.g. Go to HKEY_CURRENT_USER\Software\Oracle, delete this ORACLE key.

1.h. Go to HKEY_USERS\…\Software\Oracle, delete this ORACLE key.

1.i. Close the registry.


2. Clean up the environment settings:
----------------------------------
2.a. Open your AUTOEXEC.BAT with an editor to modify the PATH variable.
For example, you may see a path similar to this one:
C:\ORACLE\ORA81\BIN;G:\PROGRAM FILES\ORACLE\JRE\1.1.7\BIN .

2.b. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN path.

2.c. If there is a CLASSPATH variable set in the AUTOEXEC.BAT, first make note
of the path defined, then delete it. This variable can be added back at
a later date if needed.

2.d. If JRE was installed by Oracle, remove the JRE path.

2.e. Save the AUTOEXEC.BAT file and close the edit.


3. Delete the software and icons:
------------------------------
3.a. Choose Start > Programs > Windows Explorer.

3.b. Go to SYSTEM_DRIVE:\WINDOWS\START MENU\PROGRAMS
and delete the following icons:
- Oracle - HOME_NAME
where HOME_NAME is the previous Oracle home name.
- Oracle Installation Products

3.c. Go to SYSTEM_DRIVE:\Program Files\Oracle or the location of INST_LOC as
noted earlier in step 1.b and delete this directory.

3.d Go to SYSTEM_DRIVE:\Temp and delete all files and directories in here.

3.e. Go to the drive where the Oracle software is installed on your machine
and delete all ORACLE_BASE directories on your hard drive.

3.f. Remove any Oracle-related .INI files that may exist in the Windows
directory. The Windows directory may be found by entering
"echo %WINDIR%" from a command prompt.
Typical Oracle .INI files include ORADIM73.INI, ORADIM80.INI,
ORACLE.INI, ORAODBC.INI

3.g. Close the Windows Explorer.


4. Finish the removal:
-------------------
4.a. Empty the recycle bin
Right click on recycle bin > Empty Recycle Bin

4.b. Reboot your computer.

RELATED DOCUMENTS
-----------------
Oracle Installation Guides for Windows.
SEARCH WORDS
------------
WIN95, WIN98, WINNT, WIN2000, Windows95, Windows98, WindowsNT, Windows2000,
de-install, deinstall, re-install, reinstall

解决oracle9i patchsets 9.2.0.8在winxp上安装时遇到的问题。

昨天晚上在winxp(sp2)上安装oracle9i patchsets 9.2.0.8的时候，出现以下错误:


















郁闷的是中文乱码，我搞不清楚到底出了什么错，无从下手。
夜也深了，睡觉先。
早上起来，第一件事是解决错误提示中中文乱码问题，经过努力，终于消除了乱码，方法就是:
开始->控制面板->日期、时间、语言和区域设置->区域和语言选项->设置“非Unicode程序的语言”为英文,重新启动winxp，再启动oracle 安装程序，就是英文显示了，记住看到错误提示后要将刚才的设置改为中文。
现在可以看到错误提示的真面目了：


















原来错误就是：Certain Files which need to be Reinstalled by Oracle Universal Installer are Being Used by one or more runing services.
现在就可以借助google来帮我们解决问题了。

总结一下解决这个问题的方法就是：
1、找到使用oracle_home目录中文件的windows services，停止这些服务；
(其实找到这些服务很难，你也不大可能停止提示列出的所有services,可以借助工具Process Explorer解决这个问题，用这个工具可以找到使用oracle_home目录中文件的进程，kill这些进程即可。)
照理说，这个时候就可以正常安装了，保险的话可以继续下面步骤。
2、将oracle_home对应的目录(例如：D:\oracle\ora92)改名;
3、重起机器后，将oracle_home对应的目录名改回来，就可以正常安装了。

参考文档：Metalink Note: 117672.1
另外Process Explorer这个工具也是这次的一个意外收获，以前想找到正在使用windows某个文件的特定进程，试过几个工具，比较下来还是process Explorer方便。

开场白

学习使用oracle也有5、6个年头了，解决过不少实际工作中遇到的问题，有时候也记录些心得体会，不过分散在电脑上，常常过了段时间就丢了，昨天晚上上metalink的时候发现oracle已经发布了Oracle 9i RDBMS 的Patchset 9.2.0.8，这可能是oracle9i最后的补丁集，但是当我在我的笔记本上安装的时候却无法继续，今天上午我又费了很大的劲才解决了这个问题，当时感觉很有成就感，这是我写此blog的直接动因；另外我也希望能和更多的朋友在这里交流学习和使用oracle中遇到的各种问题。 明天去参加oracle官方培训Oracle9i Database Performance Tuning R2 Ed 2，这是好不容易从公司争取到培训费用的，在这里也庆祝一下。