老杨的庄碧阁

Oracle参数optimizer_index_cost_adj的取值范围在1到10000之间，它代表着一个百分比，表示索引扫描和全表扫描的成本比较。缺省值100表示索引扫描的COST等价于全表扫描，若小于<100，优化器在选择执行计划时会更趋向于走索引，若大于>100，则趋向于全表扫描。这个参数的取值可以通过以下语句进行估算

select trunc(a.average_wait*100/b.average_wait)
from v$system_event a, v$system_event b
where a.EVENT='db file sequential read' and b.event='db file scattered read';

在OLTP数据库上运行此语句，结果如下

SQL> select trunc(a.average_wait*100/b.average_wait)
2 from v$system_event a, v$system_event b
3 where a.EVENT='db file sequential read' and b.event='db file scattered read';

TRUNC(A.AVERAGE_WAIT*100/B.AVE
------------------------------
23

接着在OLAP数据库上运行一次

SQL> select trunc(a.average_wait*100/b.average_wait)
2 from v$system_event a, v$system_event b
3 where a.EVENT='db file sequential read' and b.event='db file scattered read';

TRUNC(A.AVERAGE_WAIT*100/B.AVE
------------------------------
500

两种类型数据库上的查询结果呈现了明显的差异，当然，这只是单个时间点上的比值，建议在不同时段进行采样，综合推算后再进行设定。

在部分介绍RMAN的资料上，忽略了一个重要的细节参数——control_file_record_keep_time，它的值决定了控制文件能够保留的RMAN备份信息天数上限。如果你采取了周期较长的增量备份策略，且没有选择catalog作为备份信息存储方式，那么就应该特别关注这个参数了。

此参数的默认值是7天，对于周期较长的增量备份显然是远远不够的

SQL> show parameter control_file_record_keep_time

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7

在这里将其改为60天

SQL> alter system set control_file_record_keep_time=60;

System altered.

观察alert日志中的变化

Wed Jun 2 21:05:20 2010
ALTER SYSTEM SET control_file_record_keep_time=60 SCOPE=BOTH;
Wed Jun 2 22:00:04 2010
Expanded controlfile section 28 from 141 to 548 records
Requested to grow by 407 records; added 3 blocks of records
Wed Jun 2 22:00:10 2010
Thread 1 advanced to log sequence 87311
Current log# 4 seq# 87311 mem# 0: /redo2/redo_new04.log
Current log# 4 seq# 87311 mem# 1: /backup/rwdata/redo/redo_new04.log
Wed Jun 2 22:00:13 2010
Expanded controlfile section 11 from 2800 to 13832 records
Requested to grow by 11032 records; added 394 blocks of records
Wed Jun 2 22:09:42 2010
Expanded controlfile section 13 from 744 to 2998 records
Requested to grow by 2254 records; added 101 blocks of records
Expanded controlfile section 12 from 409 to 2789 records
Requested to grow by 2380 records; added 6 blocks of records
Expanded controlfile section 15 from 4300 to 11180 records
Requested to grow by 6880 records; added 32 blocks of records
Wed Jun 2 22:09:53 2010
Expanded controlfile section 19 from 8180 to 16360 records
Requested to grow by 8180 records; added 10 blocks of records
Wed Jun 2 22:15:19 2010
Errors in file /oracle/admin/rwdata/bdump/rwdata_j000_14865.trc:
ORA-12012: error on auto execute of job 8887
ORA-44003: invalid SQL name
Wed Jun 2 22:44:37 2010
Expanded controlfile section 14 from 3948 to 15120 records
Requested to grow by 11172 records; added 80 blocks of records

如上所示，参数变更后，控制文件的大小并不会马上发生变化，当22点备份开始时，控制文件开始依照参数值进行扩容。需要提醒的是，控制文件的大小不宜过大，如果涉及到周期较长备份策略，可以考虑采用catalog存储备份信息。

在引入CBO的概念之后，默认情况下，Oracle对于SQL语句的解析都会基于统计信息进行，而我们也可以利用这个特性，通过在开发测试库上导入生产库的统计信息，模拟真实环境，获得更加准确的SQL执行计划。

以导出schema scott的统计信息为例，步骤如下：

1. 在生产库创建中间表，用于存放导出的统计信息，USERS是用于指定存放中间表的表空间

SQL> exec dbms_stats.create_stat_table('SCOTT', 'SCOTT_STAT_TABLE', 'USERS');

PL/SQL procedure successfully completed.

2. 导出统计信息至中间表

SQL> exec dbms_stats.export_schema_stats('SCOTT', 'SCOTT_STAT_TABLE');

PL/SQL procedure successfully completed.

3. 使用EXP导出中间表

[oracle@query backup]$ exp scott/tiger tables=scott_stat_table file=scott_stat_table.dmp

4. 在测试库上IMP导入上一步中的dmp

[oracle@test backup]$ imp scott/tiger file=scott_stat_table.dmp

5. 导入统计信息

SQL> exec dbms_stats.import_schema_stats('SCOTT', 'SCOTT_STAT_TABLE');

PL/SQL procedure successfully completed.

最初作为实验性质的GridControl逐渐在监控组件中占据了重要位置，于是将其重建至一台更稳定的服务器上(基于RHEL 5.3)，途中遇若干反贼，平定之。

1. 寻找libdb.so.2

安装时遇到下面的错误提示，如图：

提示OPMN无法启动，查看opmn status，HTTP_Server处于Down状态

[oracle@query ~]$ cd $OMS_HOME/opmn/bin
[oracle@query bin]$ ./opmnctl status

Processes in Instance: EnterpriseManager0.query.rwdata
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
HTTP_Server | HTTP_Server | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | home | 12214 | Alive
WebCache | WebCache | 12224 | Alive
WebCache | WebCacheAdmin | 12215 | Alive

关闭其他组件，重启OPMN，HTTP_Server依然杯具

[oracle@query bin]$ ./opmnctl stopproc process-type=home
opmnctl: stopping opmn managed processes...
[oracle@query bin]$ ./opmnctl stopproc process-type=WebCache
opmnctl: stopping opmn managed processes...
[oracle@query bin]$ ./opmnctl stopproc process-type=WebCacheAdmin
opmnctl: stopping opmn managed processes...

[oracle@query bin]$ ./opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=query.rwdata:6202
3 of 4 processes started.

ias-instance id=EnterpriseManager0.query.rwdata
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=12409)
在超过最大重试限制之后仍无法启动受管进程
Log:
/oracle/gridcontrol/oms10g/opmn/logs/HTTP_Server~1

检查日志/oracle/gridcontrol/oms10g/opmn/logs/HTTP_Server~1，貌似与libdb.so.2千丝万缕。。。

10/05/28 13:37:16 Start process
--------
/oracle/gridcontrol/oms10g/Apache/Apache/bin/apachectl start: execing httpd
/oracle/gridcontrol/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

尝试通过emctl启动OMS，libdb.so.2只闻其声不贱其人。。。

[oracle@query bin]$ cd $OMS_HOME/bin
[oracle@query bin]$ ./emctl start oms
Oracle Enterprise Manager 10g Release 3 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
opmnctl: opmn is already running

ADMN-202027
A problem has occurred reading the initial configuration and storing it into repository
Resolution:
Please refer to the base exception for resolution, or call Oracle support.
Base Exception:
/oracle/gridcontrol/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
Resolution:
Please make sure the values entered in OHS configuration files are correct.
oracle.ias.sysmgmt.exception.InvalidConfigurationException: Base Exception:
/oracle/gridcontrol/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
Resolution:
Please make sure the values entered in OHS configuration files are correct.
at oracle.ias.sysmgmt.repository.plugin.advanced.apache.StateTranslator.checkConfigFileValidity(Unknown Source)
at oracle.ias.sysmgmt.repository.plugin.advanced.apache.StateTranslator.validateConfigDuringEvaluate(Unknown Source)
at oracle.ias.sysmgmt.repository.plugin.advanced.apache.PlugInImpl.localConfigValidation(Unknown Source)
at oracle.ias.sysmgmt.repository.DcmPlugin.localConfigValidation(Unknown Source)
at oracle.ias.sysmgmt.repository.RepositoryImpl.performLocalValidation(Unknown Source)
at oracle.ias.sysmgmt.repository.SyncUpHandler._updatePluginConfigData(Unknown Source)
at oracle.ias.sysmgmt.repository.SyncUpHandler.syncUpFromLocalFiles(Unknown Source)
at oracle.ias.sysmgmt.repository.RepositoryImpl.syncUpFromLocalFiles(Unknown Source)
at oracle.ias.sysmgmt.utility.editpropagator.PropagateLocalEdit.repositoryInit(Unknown Source)
at oracle.ias.sysmgmt.persistence.utility.PMUtility.initConfiguration(Unknown Source)
at oracle.ias.sysmgmt.task.TaskMaster.initConfiguration(Unknown Source)
at oracle.ias.sysmgmt.task.TaskMaster.sysInit(Unknown Source)
at oracle.ias.sysmgmt.task.TaskMaster.sysInit(Unknown Source)
at oracle.ias.sysmgmt.task.InstanceManager.sysInit(Unknown Source)
at oracle.ias.sysmgmt.task.InstanceManager.init(Unknown Source)
at oracle.ias.sysmgmt.cmdline.DcmCmdLine.checkInit(Unknown Source)
at oracle.ias.sysmgmt.cmdline.DcmCmdLine.execute(Unknown Source)
at oracle.ias.sysmgmt.cmdline.DcmCmdLine.main(Unknown Source)
Starting HTTP Server ...
Starting Oracle Management Server ...
Checking Oracle Management Server Status ...
Oracle Management Server is not functioning because of the following reason:
Unexpected error occurred. Check error and log files.

解决方法：建立指向/usr/lib/libgdbm.so.2.0.0的符号链接/usr/lib/libdb.so.2

[oracle@query bin]$ su - root
Password:
[root@query ~]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
[root@query ~]# chmod 755 /usr/lib/libgdbm.so.2.0.0

重启OPMN，HTTP Server原地复活

[root@query ~]# su - oracle
[oracle@query ~]$ source .bash_profile_rwgrid
[oracle@query ~]$ cd $OMS_HOME/opmn/bin
[oracle@query bin]$ ./opmnctl stopproc process-type=home
opmnctl: stopping opmn managed processes...
[oracle@query bin]$ ./opmnctl stopproc process-type=WebCache
opmnctl: stopping opmn managed processes...
[oracle@query bin]$ ./opmnctl stopproc process-type=WebCacheAdmin
opmnctl: stopping opmn managed processes...
[oracle@query bin]$ ./opmnctl startall
opmnctl: starting opmn and all managed processes...
[oracle@query bin]$
[oracle@query bin]$
[oracle@query bin]$ ./opmnctl status

Processes in Instance: EnterpriseManager0.query.rwdata
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
HTTP_Server | HTTP_Server | 13829 | Alive
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | home | 13830 | Alive
WebCache | WebCache | 13841 | Alive
WebCache | WebCacheAdmin | 13832 | Alive

遗憾的是，中断的安装过程无法断点续传。。。只能选择重新来过 - -||

2. Agent返乡

新的OMS主公上位了，失散的Agent贤弟们需要重新吹响集结号。。。

方法也很简单，关闭Agent后，修改$AGENT_HOME/sysman/config/emd.properties，将其中对应的URL指向新的OMS，然后重启Agent

REPOSITORY_URL=https://query.rwdata:1159/em/upload
emdWalletSrcUrl=http://b2b.rwdata:4889/em/wallets/emd

部分文档上指出需要删除Agent原有的配置文件并重建，就10.2.0.5这个版本的测试情况来看，直接重启Agent即可，原有的主机、数据库及监瑞脑消金兽听名称都会保留。

当然，如果RP不佳。。。可能会出现需要重建Agent的状况。。。这时需要先删掉OMS中关于Agent和Target的配置信息，删除Target可以通过WEB界面操作，而删除Agent的PL/SQL代码如下

begin
mgmt_admin.cleanup_agent('主机名:端口号');
commit;
end;

建议先删除Target，然后再删Agent，不然有可能在OMS后台库的表中残留信息，导致后续添加Target时提示错误，如下：

java.sql.SQLException: ORA-20600: The specified target is in the process of being deleted.(target name = RWDB)(target type = oracle_database)(target guid = DBCB2D54577145C54B2A6188B5101F4F) ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 46 ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER' ORA-06512: at "SYSMAN.EM_TARGET", line 2117 ORA-06512: at "SYSMAN.MGMT_TARGET", line 2701 ORA-06512: at line 1

这时需要使用PL/SQL代码手动删除

begin
mgmt_admin.delete_target_internal('RWDB', 'oracle_database');
commit;
end;

begin
mgmt_admin.delete_target_internal('LISTENER_RWDB', 'oracle_listener');
commit;
end;

最后提醒一下，重建OMS后，不要忘了在OMS主机的/etc/hosts文件中添加Agent主机名及对应的IP。。。否则从WEB端操作或查看Agent信息会受到影响。。。不瞒您说。。。微臣粗心大意，被如此囧囧有神的问题困扰了整整两天。。。

很久没动笔了，随便写写，简单记录一下。

一直裸奔的B2C库上月终于批了几块硬盘，告别了提心吊胆的非归档；花了几天时间折腾增量备份脚本，每月第一个周六LEVEL 0，每周一LEVEL 1，剩下LEVEL 2，目前跑得还算稳定，发觉10g RMAN的压缩功能还是挺强悍的。。。虽然付出了CPU占用率和时间的代价，但对于空间紧张的库，留得青山在，不愁没柴烧。。。

前段日子突然陆续冒出的数据库安全隐患犹如一记闷棍。。。以前确实对这一块关注得太少了，往常总是想着如何调优、如何高可用、如何容灾。。。却忽略了如此基础而又重要的一环。。。记得有一期《程序员》杂志曾提到过，黄毛们相当重视数据库安全领域，很多研究越来越深入，而国内大多数企业在这一块还较为薄弱，有待跟进。。。仔细想想，这还真不是件可有可无的小事。。。

经验越多，胆子越小；书翻了不少，无知却有增无减。。。沿着山底的路往上爬，渐渐云雾缭绕。。。或许拼到最后也无法一睹全貌，但，还是尽力而为吧。。。

久闻Grid Control观星之功力，外加诸葛之诱惑并托蔡帮主之福，终决定观今夜之天象，Grid Control开始发育。。。8台服务器先行纳入控管；考虑到安全性问题，外网的监控暂打算改用shell脚本协管一哈子。这几日折腾下来，也算对Grid Control有了一些体验，心里会很自然地将其与dba的发展方向关联起来，一方面它将运维化繁为简，很多循环而又枯燥的巡检工作集中化、自动化、甚至智能化，而另一方面，这个新功能似乎从“某种程度”上降低了“技术含量”。微臣对此也不敢多言，还是给Grid Control投个赞成票吧，如果非要把Grid Control看成杯具，那么Oracle也就成了你的茶几。

Grid Control的安装主线并不复杂，更多的是需要在部署之前通过文档去了解一下它的架构和原理，在此简单记录部分安装过程中遇到的问题及注意事项，便于以后查阅。

1. 部署agent之前，应在安装agent的服务器上配置好oms主机名和IP地址的对应关系，linux下修改/etc/hosts即可，windows平台对应X:\WINDOWS\system32\drivers\etc\hosts。另外，Grid Control的安装自检对主机名有限定，因此不要选择类似于localhost之类的主机名

2. 如果agent安装在启用了iptables的linux平台，需要解封3872端口（默认值，也可自行指定），否则会影响oms与agent之间的通信

[root@rwdata ~]$ iptables -I INPUT -p tcp --dport 3872 -m state --state NEW,RELATED,ESTABLISHED -j ACCEPT
[root@rwdata ~]$ /etc/rc.d/init.d/iptables save

3. oms的资料可以选择放在现有的数据库上，但有一定的条件限制；如果资源允许，建议为其新建一个库

4. Grid Control版本与Oracle版本之间没有必然的联系，假设安装的Grid Control版本为10.2.0.4，升级10.2.0.5后，oms资料库对应的数据库版本仍为10.2.0.4；此外，通过插件的方式，Grid Control还提供了对db2、sqlserver等异构产品的支持

5. 在oem中添加agent所监控的oracle实例和监瑞脑消金兽听时，有可能会出现ORA-12560: TNS:protocol adapter error，从而无法获取实例和监瑞脑消金兽听信息；从目前的测试和实施结果来看，如果添加的oracle监瑞脑消金兽听使用的是静态注册，则出现此问题的机率较大（不是绝对）。解决的方法，在设置oracle实例和监瑞脑消金兽听信息时，主机名应指定IP地址，而不是hostname（注：oem默认使用hostname）

6. 针对agent与oms能够互通，但agent无法upload的状况，可尝试以下步骤解决

关闭agent

[oracle@rwdata ~]$ cd $AGENT_HOME/bin
[oracle@rwdata bin]$ ./emctl stop agent

删除旧的agent信息

[oracle@rwdata bin]$ rm -r /oracle/10.2.0/agent10g/sysman/emd/state/*
[oracle@rwdata bin]$ rm -r /oracle/10.2.0/agent10g/sysman/emd/collection/*
[oracle@rwdata bin]$ rm -r /oracle/10.2.0/agent10g/sysman/emd/upload/*
[oracle@rwdata bin]$ rm /oracle/10.2.0/agent10g/sysman/emd/lastupld.xml
[oracle@rwdata bin]$ rm /oracle/10.2.0/agent10g/sysman/emd/agntstmp.txt
[oracle@rwdata bin]$ rm /oracle/10.2.0/agent10g/sysman/emd/blackouts.xml
[oracle@rwdata bin]$ rm /oracle/10.2.0/agent10g/sysman/emd/protocol.ini
[oracle@rwdata bin]$ ./emctl clearstate agent

重新设定agent与oms的通讯密码

[oracle@rwdata bin]$ ./emctl secure agent

启动agent，手动执行upload并查看状态

[oracle@rwdata bin]$ ./emctl start agent
[oracle@rwdata bin]$ ./emctl upload agent
[oracle@rwdata bin]$ ./emctl status agent

后记：知天易，逆天难。。。观星虽好，天命难违。。。下次注意点（最近杀很大，中毒）

在关闭呼叫中心生产库(Oracle 10.2.0.1)时，数据库长时间挂起没有响应，告警日志持续报PMON failed to acquire latch：

Wed Jan 19 20:12:08 2010
Shutting down instance: further logons disabled
Wed Jan 19 20:12:08 2010
Stopping background process CJQ0
Wed Jan 19 20:12:08 2010
Stopping background process QMNC
Wed Jan 19 20:12:10 2010
Stopping background process MMNL
Wed Jan 19 20:12:11 2010
Stopping background process MMON
Wed Jan 19 20:12:13 2010
Shutting down instance (immediate)
License high water mark = 179
Wed Jan 19 20:12:13 2010
Stopping Job queue slave processes
Wed Jan 19 20:12:18 2010
Process OS id : 26272 alive after kill
Errors in file
Wed Jan 19 20:12:18 2010
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Wed Jan 19 20:12:30 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Wed Jan 19 20:12:42 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Wed Jan 19 20:12:54 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Wed Jan 19 20:13:06 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump

…………

metalink信息如下：

Bug 4632780 - PMON "failed to acquire latch" during shutdown

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected 9.2.0.7
10.1.0.5
10.2.0.1

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.8 (Server Patch Set)
10.2.0.2 (Server Patch Set)
11g (Future version)
Symptoms: Related To:
(None Specified)
Miscellaneous

Description
"PMON failed to acquire latch" messages can appear in the alert log during shutdown.

对应的解决方法：

*Note: 345197.1

1. set INBOUND_CONNECT_TIMEOUT_(listener name)= 0 in listener.ora
2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3. stop and start both listener and data base.
4. Re-run the Workflow Configuration Assistant