Category Archives: Study

设置Outlook客户端收发Gmail邮件

此篇记录对Outlook客户端的设置,以使其收发Gmail邮件。
配置方法详见:【Gmail IMAP】在公司、家裡用Outlook同步收發Gmail郵件!
此文语言简单明了,虽为繁体软体说明,但是稍微留意,与简体差异不大。

由于权限问题无法生成AWR报告

生成AWR报告的时候遇到如下错误:

~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_7796_7797.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_7796_7797.html
 select output from table(dbms_workload_repository.awr_report_html( :dbid,
 *
 ERROR at line 1:
 ORA-00904: : invalid identifier
Report written to awrrpt_1_7796_7797.html

这是因为用户没有生成AWR报告的权限。如果希望一个用户能生成AWR报告,最少需要几个权限呢?
我认为应是三个:
1.connect/create session
2.select any dictionary(查询字表)
3.execute on any dbms_workload_repository(负载信息库)

通过以下实验证明:

Last login: Tue Dec 13 16:00:29 2011 from 58.242.2.52
 [[email protected] ~]$ sql
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 14 07:51:45 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user test1 cascade;
User dropped.
SQL> create user awr identified by awr;
User created.
SQL> grant connect to awr;
Grant succeeded.
SQL> grant select any dictionary to awr;
Grant succeeded.
SQL> grant execute on DBMS_WORKLOAD_REPOSITORY to awr;
Grant succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
 ----------- ------------ -------- ------------
 1288916317 ORCL 1 orcl
Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Would you like an HTML report, or a plain text report?
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
 ------------ -------- ------------ ------------ ------------
 * 1288916317 1 ORCL orcl hpserver
Using 1288916317 for database Id
 Using 1 for instance number
Specify the number of days of snapshots to choose from
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Entering the number of days (n) will result in the most recent
 (n) days of snapshots being listed. Pressing without
 specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
 Instance DB Name Snap Id Snap Started Level
 ------------ ------------ --------- ------------------ -----
 orcl ORCL 2136 14 Dec 2011 00:00 1
 2137 14 Dec 2011 01:00 1
 2138 14 Dec 2011 02:00 1
 2139 14 Dec 2011 03:00 1
 2140 14 Dec 2011 04:00 1
 2141 14 Dec 2011 05:00 1
 2142 14 Dec 2011 06:00 1
 2143 14 Dec 2011 07:00 1
Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Enter value for begin_snap: 2142
 Begin Snapshot Id specified: 2142
Enter value for end_snap: 2143
 End Snapshot Id specified: 2143
Specify the Report Name
 ~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_2142_2143.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_2142_2143.html
 …省略…
End of Report
Report written to awrrpt_1_2142_2143.html

网上有人说连查询字典表的权限都不需要,我们测试下:

SQL> revoke select any dictionary from awr;
Revoke succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
 v$instance i
 *
 ERROR at line 6:
 ORA-00942: table or view does not exist
Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Would you like an HTML report, or a plain text report?
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 from dba_hist_database_instance wr, v$database cd, v$instance ci
 *
 ERROR at line 14:
 ORA-00942: table or view does not exist

报错,提示没有此表或view。在什么情况下会提示呢?
1.真的没有这个对象(我们知道,这个可以有)
2.用户没权限查看此对象。

综上所述,如果一个用户需要生成awr,需要至少对他进行如下赋权:
grant connect to [user];
grant select any dictionary to [user];
grant execute on DBMS_WORKLOAD_REPOSITORY to [user];

参考资料:
http://cyzhang1983.itpub.net/post/40572/499634
http://www.itpub.net/thread-1330158-1-1.html

ORA-600 [kqlnrc_1]错误分析

群里朋友提出一个问题:数据库遇到ORA-00600: internal error code, arguments: [kqlnrc_1], [0x7000000DCA26B38], [], [], [], [], [], []错误,请求解决。
MOS上关于ORA-600 [kqlnrc_1]错误有详细的文档说明:How To Find The Object That Causing ORA-600 [kqlnrc_1] [ID 1190673.1]

根据文档说明,一步一步寻找问题的根源:
1.Trace文件中找到ORA-600报错,顺便看了下,是AIX上的10.2.0.4.0的库

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 ORACLE_HOME = /oracle/app/product/10.2.0/db_1
 System name: AIX
 Node name: tobadb88
 Release: 3
 Version: 5
 Machine: 00CCA0B44C00
 Instance name: han_tocco
 Redo thread mounted by this instance: 1
 Oracle process number: 85
 Unix process pid: 1737624, image: [email protected]
*** 2011-12-12 16:02:56.457
 ksedmp: internal or fatal error
 ORA-00600: internal error code, arguments: [kqlnrc_1], [0x7000000DCA26B38], [], [], [], [], [], []
 Current SQL statement for this session:

2.查询0x7000000DCA26B38
直接搜索没有任何结果,经分析,在trace文件内的handle对应的值字母为小写,故搜索不到,重新搜索即可找到INVALID对象:

SO: 7000005d0479c98, type: 53, owner: 7000005e51bf988, flag: INIT/-/-/0x00
 LIBRARY OBJECT LOCK: lock=7000005d0479c98 handle=7000000dca26b38 mode=S
 call pin=7000005d6d0e638 session pin=0 hpc=0000 hlc=0000
 htl=7000005d0479d18[7000005d9176060,7000005d9176060] htb=7000005d9176060 ssga=7000005d9175aa8
 user=70000060b786c98 session=70000060b786c98 count=1 flags=PNC/[0400] savepoint=0x1cb8
 LIBRARY OBJECT HANDLE: handle=7000000dca26b38 mtx=7000000dca26c68(0) cdp=0
 [email protected]_DBGIS
 hash=01539fc30d3e6ae740e53e09dddbce4a timestamp=04-25-2009 17:35:49
 namespace=TABL flags=REM/KGHP/TIM/SML/[02020000]
 kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=17 hpc=0002 hlc=0002
 lwt=7000000dca26be0[7000000dca26be0,7000000dca26be0] ltm=7000000dca26bf0[7000000dca26bf0,7000000dca26bf0]
 pwt=7000000dca26ba8[7000000dca26ba8,7000000dca26ba8] ptm=7000000dca26bb8[7000000dca26bb8,7000000dca26bb8]
 ref=7000000dca26c10[7000000dca26c10,7000000dca26c10] lnd=7000000dca26c28[7000000dca26c28,7000000dca26c28]
 LIBRARY OBJECT: object=7000000d69413b0
 type=SYNM flags=EXS/LOC[0005] pflags=[0000] status=INVL load=0

Library cache中的失效对象为CRM_INTF.NW_SYN_INTERFACE
由于不是自己的库,后面的compile过程无法实现,不过已经找到了失效对象,剩下的工作已经不再复杂。

总结:
大小写,空格,减号,下划线,每一点,一个细节,都要务必严谨。
BTW,我是通过status=INVL找到的,有时,换一种角度思考,也会有不错的效果。

64bit-AIX平台下,Oracle10g数据库 使用SQLPLUS登陆报错

登陆客户数据库报错:

SQL> conn xxx/xxx
 ERROR:
 ORA-01034: ORACLE not available
 ORA-27121: unable to determine size of shared memory segment
 IBM AIX RISC System/6000 Error: 13: Permission denied
 [zwq_kfdb1:/home/oraeye/enmotech]uname -a
 AIX zwq_kfdb1 1 6 00C5C4764C00

Google了下,很多文章说是由于“重建oracle用户和组引起的ORA-27121错误”,还有说是内存问题,需要重启。大致翻了一些,感觉都不太靠谱,故上MOS搜索,输入“ORA-27121”,第一篇文章即为:Cannot Use SQLPlus as a Non-Oracle User on AIX 64 Bit ORA-01034 and ORA-27121 [ID 1058928.1],内容如下:

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.3 to 11.1.0.7 – Release: 10.2 to 11.1
IBM AIX on POWER Systems (64-bit)
Symptoms
On AIX 64 Bit, using Oracle 10.2 or 11.1, unable to use SQLPLUS as any user other than the UNIX user Oracle. The following occurs:
ERROR:
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
IBM AIX RISC System/6000 Error: 13: Permission denied

Symptoms associated with this issue are:
– no errors in alert.log
– cannot connect to sqlplus other than as UNIX user oracle
– platform is 212 AIX 64 Bit
– errors that occur in a core dump are
ORA-1034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment

Changes
Running Oracle version 10.2.0.3, 10.2.0.4, 11.1.0.6 or 11.1.0.7

Cause
Bug 6973208 which is a duplicate of Bug 6800649
Abstract: AIX: Client side “map:permission denied” or “sh: /usr/bin/procmap: not found”
============
Client side executables may report errors such as
sh: /usr/bin/procmap: not found
or if procmap exists but the client executable is setuid then
the client may report an error like:
map:permission denied

Solution
Apply Patch 6800649 for effected Oracle version
Workaround:
For the case “sh: /usr/bin/procmap: not found” install “procmap”.
This is an AIX executable. See Note:435576.1 for more details.
For the case of “map:permission denied” make sure that the client
executable is not marked as setuid to some other user.

解决方案:
修改oracle文件权限:

[xxx:/oracle10/app/product/db/10.2.0/bin]ls -l oracle
 -rwsr-s--x 1 oracle10 oinstall 133933734 Jan 16 2011 oracle

ORACLE服务器进程中的LOCAL=NO和LOCAL=YES

[[email protected]]:/home/ora> ps -ef |grep LOCAL
oraacc 1315270 1 0 23:08:27 - 0:00 oracleacc2 (LOCAL=NO)
 oraacc 1671544 1 0 22:52:06 - 0:00 oracleacc2 (LOCAL=NO)
 oraacc 1704368 1 0 15:20:37 - 0:00 oracleacc2 (LOCAL=NO)
 oraacc 1999344 1 0 22:57:47 - 0:00 oracleacc2 (LOCAL=NO)
 oraacc 2203930 1 0 22:30:33 - 0:00 oracleacc2 (LOCAL=NO)
 oraeye 2458056 774186 0 23:55:20 pts/9 0:00 grep LOCAL
 oraacc 2630046 1 0 22:53:21 - 0:00 oracleacc2 (LOCAL=NO)
 oraacc 303852 1687962 0 21:49:05 - 0:00 oracleacc2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
 oraacc 647714 1 0 Oct 14 - 0:02 oracleacc2 (LOCAL=NO)
 oraacc 1196796 1 0 Oct 14 - 0:02 oracleacc2 (LOCAL=NO)
 oraacc 1491610 1 0 Nov 28 - 0:00 oracleacc2 (LOCAL=NO)

使用ps -ef |grep LOCAL命令可以查看连接到数据库服务器上的进程。
LOCAL=NO和YES分别代表什么含义呢?
LOCAL=NO :非本地连接,即网络连接。 它是通过Listener 连接到服务器的。 客户端的应用通过客户端的监听向服务器的监听发送请求,服务器的监听接收后,在与数据库连接,执行相关操作,在把结果返回给客户端。 这是通过监听的流程。 所以在客户端需要配置监听,即配置tnsnames.ora。
LOCAL=YES:本地连接。 本地连接不走监听,所以在服务监听没有启动的情况下,通过本地的sqlplus 还是可以连上数据库的。

一个工作中的小问题,记录之。

参考:
http://blog.csdn.net/tianlesoftware/article/details/5675404

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress