yangtingkun

11.2 sqlplus新增连接是指定版本

Posted on 2011/09/26 by yangtingkun

在11.2中，Oracle新增了版本的功能，而随之一些工具都对于版本功能有所支持，比如11.2中，sqlplus在连接数据库时就可以指定当前数据库连接到哪个版本上。
需要说明的是，这种连接版本方式的优先级是最高的，它的优先级高于ORA_EDITION环境变量，更高于数据库默认版本，看一个简单的例子：

D:\>sqlplus / AS sysdba
SQL*Plus: Release 11.2.0.1.0 Production ON 星期二 9月 27 08:21:47 2011
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
连接到:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
SQL> SET pages 100 LINES 120
SQL> SELECT username FROM dba_users WHERE editions_enabled = 'Y';
未选定行
SQL> CREATE USER u_e IDENTIFIED BY u_e DEFAULT tablespace test enable editions;
用户已创建。
SQL> GRANT CONNECT, resource, dba TO u_e;
授权成功。
SQL> CREATE edition e1;
版本已创建。
SQL> conn u_e/u_e
已连接。
SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE
SQL> conn u_e/u_e edition=e1
已连接。
SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
E1
SQL> ALTER DATABASE DEFAULT edition = e1;
数据库已更改。
SQL> conn u_e/u_e
已连接。
SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
E1
SQL> conn u_e/u_e edition=ora$base
已连接。
SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE

除了sqlplus之外，OCI和JDBC也支持在连接时选择版本进行连接。

Posted in BUG, ORACLE | Tagged 11.2, connect, edition, sqlplus | Leave a comment

连接用户获取权限信息

Posted on 2011/09/25 by yangtingkun

有客户对AWR中报告中出现的一个查询GLOBAL_NAME的SQL存在疑问，详细分析后发现是客户端建立连接后获取权限信息的语句。

客户存在疑问的SQL是：

SELECT VALUE$ FROM props$ WHERE
name = 'GLOBAL_DB_NAME'

而这个SQL出现在AWR报告中执行次数部分，可以看到这个SQL运行次数接近26W次。

Executions	Rows Processed	Rows per Exec	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
546,016	8,942,073	16.38	170.26	28.28	0.00	cm5vu20fhtnq1		select /*+ connect_by_filterin…
270,653	545,943	2.02	125.71	16.34	0.00	0k8522rmdzg4k		select privilege# from sysauth…
259,736	259,753	1.00	64.03	45.20	0.00	459f3z9u4fb3u		select value$ from props$ wher…
259,720	259,716	1.00	35.67	56.40	0.00	5ur69atw3vfhj		select decode(failover_method,…
259,660	259,715	1.00	121.55	50.67	0.00	0ws7ahf1d78qa		select SYS_CONTEXT(‘USERENV’, …

出现在这个报告中前几位的SQL除了第一个以外，剩下的执行次数基本上一致，而第一个显然是其他的2倍，那么这显然不是一个巧合。

根据报告的运行时间：

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	661	14-Sep-11 13:00:13	318	1.6
End Snap:	666	14-Sep-11 18:00:52	375	1.8
Elapsed:		300.64 (mins)

以及每秒登录数据库的次数：

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	3.1	0.7	0.01	0.01
DB CPU(s):	2.4	0.6	0.01	0.01
Redo size:	17,771.2	4,136.4
Logical reads:	70,747.7	16,467.1
Block changes:	103.0	24.0
Physical reads:	156.0	36.3
Physical writes:	49.0	11.4
User calls:	440.1	102.4
Parses:	264.6	61.6
Hard parses:	49.1	11.4
W/A MB processed:	25.7	6.0
Logons:	15.2	3.5
Executes:	262.3	61.0
Rollbacks:	0.0	0.0
Transactions:	4.3

计算数据库中这段时间内登录总次数：

SQL&gt; SELECT 300.64*60*15.2 FROM dual;
 
300.64*60*15.2
 
--------------
 
274183.68

显然包括SQL：select value$ from props$ where name = ‘GLOBAL_DB_NAME’在内的这些执行次数最多的SQL，基本上都是每登录一次就运行一次，而对于SQL_ID为cm5vu20fhtnq1的语句，每次登录需要运行两次。

这个SQL就是Oracle在登录阶段获取权限和连接信息的SQL，而且根据SQL语句在metalink上文档ID
730066.1中也找到的官方的说明：

SELECT VALUE$ FROM
props$ WHERE name = 'GLOBAL_DB_NAME'
 
SELECT
privilege#,level FROM sysauth$ CONNECT BY grantee#=prior privilege#
 
AND privilege#&gt;0 START WITH grantee#=:1 AND privilege#&gt;0
 
SELECT
SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
 
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV',
'SERVICE_NAME'),
 
INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN')
 
FROM v$instance WHERE INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
 
SELECT privilege#
FROM sysauth$ WHERE (grantee#=:1 OR grantee#=1) AND privilege#&gt;0
 
ALTER SESSION SET
NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$'
 
NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR=
'GREGORIAN'
 
NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY'
TIME_ZONE= '+02:00'
 
NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM'
NLS_TIMESTAMP_FORMAT=
 
'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR'
NLS_TIMESTAMP_TZ_FORMAT=
 
'DD-MON-RR HH.MI.SSXFF AM TZR'

这是10g环境中Oracle在登录阶段会执行的SQL语句，可以看到在当前11.2.0.2环境中，这些SQL大部分没有改变只是去掉了ALTER SESSION语句，取代其的是一个查询SERVICE的语句，此外其中一个增加了HINT。在AWR报告中，这些SQL完整信息为：

cm5vu20fhtnq1	select /+ connect_by_filtering / privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
0k8522rmdzg4k	select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
459f3z9u4fb3u	select value$ from props$ where name = ‘GLOBAL_DB_NAME’
5ur69atw3vfhj	select decode(failover_method, NULL, 0 , ‘BASIC’, 1, ‘PRECONNECT’, 2 , ‘PREPARSE’, 4 , 0), decode(failover_type, NULL, 1 , ‘NONE’, 1 , ‘SESSION’, 2, ‘SELECT’, 4, 1), failover_retries, failover_delay, flags from service$ where name = :1
0ws7ahf1d78qa	select SYS_CONTEXT(‘USERENV’, ‘SERVER_HOST’), SYS_CONTEXT(‘USERENV’, ‘DB_UNIQUE_NAME’), SYS_CONTEXT(‘USERENV’, ‘INSTANCE_NAME’), SYS_CONTEXT(‘USERENV’, ‘SERVICE_NAME’), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT(‘USERENV’, ‘DB_DOMAIN’) from v$instance where INSTANCE_NAME=SYS_CONTEXT(‘USERENV’, ‘INSTANCE_NAME’)

对于这种大数据量出现的SQL语句，多半都和Oracle内部的递归调用有关，只要仔细分析就不难找到其出处。

Posted in ORACLE | Leave a comment

在接触一个新的系统，或对一个陌生的环境进行迁移备份时，需要了解这个数据库中业务用户是哪些，系统用户有哪些，以区别对待，但有时这个工作并不太容易。
最简单的办法莫过于直接询问对数据库了解的DBA或开发人员，但是有时如果数据库中用户众多，那么没有人可以确定所有的用户范围。同样的道理，通过检查程序也有可能遗漏。何况在有些情况下，并没有人对数据库用户十分了解，也就根本无从问起。
既然所有的用户都存储在数据库中，那么利用数据库本身的方法应该可以区分哪些是业务用户，哪些是系统用户。
如果对于Oracle的用户很熟悉，那么直接通过查询DBA_USERS获取用户名列表，就可以判断的八九不离十：

SQL> SELECT * FROM v$version; BANNER ---------------------------------------------------------------- Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS FOR 32-bit Windows: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 – Production SQL> SELECT username FROM dba_users; USERNAME ------------------------------ OLAPSYS SI_INFORMTN_SCHEMA MGMT_VIEW ORDPLUGINS XDB DIP OUTLN ANONYMOUS CTXSYS MDDATA TSMSYS ORACLE_OCM WMSYS DMSYS EXFSYS ORDSYS MDSYS SYSTEM SYS TEST SYSMAN U1 USER_A DBSNMP TEST2 已选择25行。
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for 32-bit Windows: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 – Production SQL> select username from dba_users; USERNAME ------------------------------ OLAPSYS SI_INFORMTN_SCHEMA MGMT_VIEW ORDPLUGINS XDB DIP OUTLN ANONYMOUS CTXSYS MDDATA TSMSYS ORACLE_OCM WMSYS DMSYS EXFSYS ORDSYS MDSYS SYSTEM SYS TEST SYSMAN U1 USER_A DBSNMP TEST2 已选择25行。

不过这种方法容易产生遗漏或误判，尤其是用户众多的时候，而且Oracle各个版本中的系统用户变化很大，在安装系统的时候不同的选件也会导致创建的用户不同，所以这种方法一是只对资深DBA有效，二是准确性不好控制。
其实DBA_USERS视图中还有很多其他信息可以用来辅助判断，比如创建时间和默认表空间：

SQL> ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; 会话已更改。 SQL> SELECT username, created FROM dba_users ORDER BY 2; USERNAME CREATED ------------------------------ ------------------- TEST 2010-12-24 14:22:44 USER_A 2010-12-24 15:09:18 SYS 2010-12-25 15:56:16 SYSTEM 2010-12-25 15:56:17 OUTLN 2010-12-25 15:56:18 DIP 2010-12-25 15:59:19 TSMSYS 2010-12-25 16:01:33 DBSNMP 2010-12-25 16:02:23 ORACLE_OCM 2010-12-25 16:02:26 WMSYS 2010-12-25 16:02:51 EXFSYS 2010-12-25 16:06:14 DMSYS 2010-12-25 16:06:27 CTXSYS 2010-12-25 16:06:41 ANONYMOUS 2010-12-25 16:07:02 XDB 2010-12-25 16:07:02 ORDSYS 2010-12-25 16:08:00 MDSYS 2010-12-25 16:08:00 ORDPLUGINS 2010-12-25 16:08:00 SI_INFORMTN_SCHEMA 2010-12-25 16:08:00 OLAPSYS 2010-12-25 16:12:20 MDDATA 2010-12-25 16:13:06 SYSMAN 2010-12-25 16:15:13 MGMT_VIEW 2010-12-25 16:16:14 U1 2011-08-16 15:17:08 TEST2 2011-09-01 17:47:08 已选择25行。 SQL> SELECT username, default_tablespace FROM dba_users ORDER BY 2; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ OLAPSYS SYSAUX SI_INFORMTN_SCHEMA SYSAUX DBSNMP SYSAUX SYSMAN SYSAUX MDSYS SYSAUX ORDSYS SYSAUX EXFSYS SYSAUX DMSYS SYSAUX WMSYS SYSAUX CTXSYS SYSAUX ANONYMOUS SYSAUX XDB SYSAUX ORDPLUGINS SYSAUX MGMT_VIEW SYSTEM SYS SYSTEM OUTLN SYSTEM SYSTEM SYSTEM TEST2 USERS DIP USERS TEST USERS U1 USERS MDDATA USERS TSMSYS USERS ORACLE_OCM USERS USER_A USERS 已选择25行。
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; 会话已更改。 SQL> select username, created from dba_users order by 2; USERNAME CREATED ------------------------------ ------------------- TEST 2010-12-24 14:22:44 USER_A 2010-12-24 15:09:18 SYS 2010-12-25 15:56:16 SYSTEM 2010-12-25 15:56:17 OUTLN 2010-12-25 15:56:18 DIP 2010-12-25 15:59:19 TSMSYS 2010-12-25 16:01:33 DBSNMP 2010-12-25 16:02:23 ORACLE_OCM 2010-12-25 16:02:26 WMSYS 2010-12-25 16:02:51 EXFSYS 2010-12-25 16:06:14 DMSYS 2010-12-25 16:06:27 CTXSYS 2010-12-25 16:06:41 ANONYMOUS 2010-12-25 16:07:02 XDB 2010-12-25 16:07:02 ORDSYS 2010-12-25 16:08:00 MDSYS 2010-12-25 16:08:00 ORDPLUGINS 2010-12-25 16:08:00 SI_INFORMTN_SCHEMA 2010-12-25 16:08:00 OLAPSYS 2010-12-25 16:12:20 MDDATA 2010-12-25 16:13:06 SYSMAN 2010-12-25 16:15:13 MGMT_VIEW 2010-12-25 16:16:14 U1 2011-08-16 15:17:08 TEST2 2011-09-01 17:47:08 已选择25行。 SQL> select username, default_tablespace from dba_users order by 2; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ OLAPSYS SYSAUX SI_INFORMTN_SCHEMA SYSAUX DBSNMP SYSAUX SYSMAN SYSAUX MDSYS SYSAUX ORDSYS SYSAUX EXFSYS SYSAUX DMSYS SYSAUX WMSYS SYSAUX CTXSYS SYSAUX ANONYMOUS SYSAUX XDB SYSAUX ORDPLUGINS SYSAUX MGMT_VIEW SYSTEM SYS SYSTEM OUTLN SYSTEM SYSTEM SYSTEM TEST2 USERS DIP USERS TEST USERS U1 USERS MDDATA USERS TSMSYS USERS ORACLE_OCM USERS USER_A USERS 已选择25行。

由于大部分系统用户都是在数据库创建时刻创建，因此他们的创建时间比较接近，且与SYS和SYSTEM用户创建时间相去不远，这可以作为一个主要的判断依据。但是有些系统用户可能是在数据库运行一段时间后安装的，或者业务用户在创建数据库后马上创建，甚至是修改系统时间，都会导致仅根据创建时间来判断系统用户容易产生查漏，比如上面的例子中，就是由于系统时间变动，导致业务用户的创建时间甚至早于数据库的创建时间。
默认表空间信息同样可以起到辅助判断的作用，在9i以前，各个组件都会创建独立的表空间，而在10g以后，大部分组件的默认表空间都是SYSAUX，因此可以比较容易的判断大部分系统用户。不过默认表空间是可以修改的，而且虽然不推荐，但是并不能阻止业务用户将默认表空间设置为SYS或SYSAUX，此外，从上面的例子也可以看出，仍然有一部分系统用户的默认表空间不是SYS或SYSAUX，因此这个方法也不能作为唯一的依据。
除了上面几个方法外，在Oracle中，专门记录了每个组件对应的系统用户，可以通过下面的查询获取：

SQL> SELECT CID, CNAME, NAME 2 FROM 3 (SELECT CID, CNAME, SCHEMA# FROM SYS.REGISTRY$ 4 UNION ALL 5 SELECT A.CID, CNAME, B.SCHEMA# FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B 6 WHERE A.CID = B.CID) A, 7 SYS.USER$ B 8 WHERE A.SCHEMA# = B.USER#; CID CNAME NAME ---------- --------------------------------------------- -------------------- XOQ Oracle OLAP API SYS APS OLAP Analytic Workspace SYS CATJAVA Oracle DATABASE Java Packages SYS XML Oracle XDK SYS JAVAVM JServer JAVA Virtual Machine SYS CATPROC Oracle DATABASE Packages AND Types SYS CATALOG Oracle DATABASE Catalog Views SYS CATPROC Oracle DATABASE Packages AND Types SYSTEM CATPROC Oracle DATABASE Packages AND Types OUTLN ODM Oracle DATA Mining DMSYS CATPROC Oracle DATABASE Packages AND Types DBSNMP OWM Oracle Workspace Manager WMSYS RUL Oracle Rules Manager EXFSYS EXF Oracle Expression FILTER EXFSYS CONTEXT Oracle Text CTXSYS XDB Oracle XML DATABASE XDB EM Oracle Enterprise Manager SYSMAN AMD OLAP Catalog OLAPSYS ORDIM Oracle interMedia ORDSYS ORDIM Oracle interMedia ORDPLUGINS ORDIM Oracle interMedia SI_INFORMTN_SCHEMA SDO Spatial MDSYS 已选择22行。
SQL> SELECT CID, CNAME, NAME 2 FROM 3 (SELECT CID, CNAME, SCHEMA# FROM SYS.REGISTRY$ 4 UNION ALL 5 SELECT A.CID, CNAME, B.SCHEMA# FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B 6 WHERE A.CID = B.CID) A, 7 SYS.USER$ B 8 WHERE A.SCHEMA# = B.USER#; CID CNAME NAME ---------- --------------------------------------------- -------------------- XOQ Oracle OLAP API SYS APS OLAP Analytic Workspace SYS CATJAVA Oracle Database Java Packages SYS XML Oracle XDK SYS JAVAVM JServer JAVA Virtual Machine SYS CATPROC Oracle Database Packages and Types SYS CATALOG Oracle Database Catalog Views SYS CATPROC Oracle Database Packages and Types SYSTEM CATPROC Oracle Database Packages and Types OUTLN ODM Oracle Data Mining DMSYS CATPROC Oracle Database Packages and Types DBSNMP OWM Oracle Workspace Manager WMSYS RUL Oracle Rules Manager EXFSYS EXF Oracle Expression Filter EXFSYS CONTEXT Oracle Text CTXSYS XDB Oracle XML Database XDB EM Oracle Enterprise Manager SYSMAN AMD OLAP Catalog OLAPSYS ORDIM Oracle interMedia ORDSYS ORDIM Oracle interMedia ORDPLUGINS ORDIM Oracle interMedia SI_INFORMTN_SCHEMA SDO Spatial MDSYS 已选择22行。

虽然这种方法也没有包括所有的系统用户，但是这个信息无疑是最准确的，在配合用户名，系统表空间和创建时间，就可以比较轻易的判断所有非系统用户了：

SQL> SELECT USERNAME, CREATED, DEFAULT_TABLESPACE 2 FROM DBA_USERS 3 WHERE USERNAME NOT IN 4 (SELECT NAME 5 FROM 6 (SELECT SCHEMA# 7 FROM SYS.REGISTRY$ 8 UNION ALL 9 SELECT B.SCHEMA# 10 FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B 11 WHERE A.CID = B.CID) A, 12 SYS.USER$ B 13 WHERE A.SCHEMA# = B.USER#) 14 ORDER BY 3, 2; USERNAME CREATED DEFAULT_TABLESPACE ------------------------------ ------------------- ----------------------- ANONYMOUS 2010-12-25 16:07:02 SYSAUX MGMT_VIEW 2010-12-25 16:16:14 SYSTEM TEST 2010-12-24 14:22:44 USERS USER_A 2010-12-24 15:09:18 USERS DIP 2010-12-25 15:59:19 USERS TSMSYS 2010-12-25 16:01:33 USERS ORACLE_OCM 2010-12-25 16:02:26 USERS MDDATA 2010-12-25 16:13:06 USERS U1 2011-08-16 15:17:08 USERS TEST2 2011-09-01 17:47:08 USERS 已选择10行。
SQL> SELECT USERNAME, CREATED, DEFAULT_TABLESPACE 2 FROM DBA_USERS 3 WHERE USERNAME NOT IN 4 (SELECT NAME 5 FROM 6 (SELECT SCHEMA# 7 FROM SYS.REGISTRY$ 8 UNION ALL 9 SELECT B.SCHEMA# 10 FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B 11 WHERE A.CID = B.CID) A, 12 SYS.USER$ B 13 WHERE A.SCHEMA# = B.USER#) 14 ORDER BY 3, 2; USERNAME CREATED DEFAULT_TABLESPACE ------------------------------ ------------------- ----------------------- ANONYMOUS 2010-12-25 16:07:02 SYSAUX MGMT_VIEW 2010-12-25 16:16:14 SYSTEM TEST 2010-12-24 14:22:44 USERS USER_A 2010-12-24 15:09:18 USERS DIP 2010-12-25 15:59:19 USERS TSMSYS 2010-12-25 16:01:33 USERS ORACLE_OCM 2010-12-25 16:02:26 USERS MDDATA 2010-12-25 16:13:06 USERS U1 2011-08-16 15:17:08 USERS TEST2 2011-09-01 17:47:08 USERS 已选择10行。

在这个例子中，业务用户只有TEST、USER_A、U1和TEST2。

Posted in ORACLE | Tagged REGISTRY$, REGISTRY$SCHEMAS, USER | Leave a comment

AIX环境dd迁移控制文件出现ORA-202和ORA-27047错误

Posted on 2011/09/23 by yangtingkun

客户尝试利用dd来迁移裸设备上的控制文件，结果出现了这个错误。
利用dd命令将控制文件从旧存储的裸设备迁移到新存储上，命令如下：

dd IF=/dev/oldcontrol OF=/dev/newcontrol bs=2048k

然后尝试启动数据库，在MOUNT时报错：

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.5032E+10 bytes
Fixed SIZE 2046960 bytes
Variable SIZE 2483029008 bytes
DATABASE Buffers 1.2533E+10 bytes
Redo Buffers 14729216 bytes
SQL> ALTER DATABASE mount;
ALTER DATABASE mount
*
ERROR at line 1:
ORA-00205: error IN identifying control file, CHECK alert log FOR more info

检查告警日志，发现错误信息为：

ORA-00202: control file: '/dev/newcontrol' 
ORA-27047: unable TO READ the header block OF file 
Additional information: 2 
ORA-205 signalled during: ALTER DATABASE MOUNT...

查询了metalink，并没有发现和当前问题完全一致的问题，但是怀疑问题可能和AIX系统裸设备的偏移量有关。不过当前操作系统的版本并没有变化，仅仅是存储的改变，按道理来说不应该导致裸设备的偏移量不一致。
为了规避这个问题，不使用dd来进行控制文件的迁移，而改为使用rman来拷贝控制文件，问题得以解决。

Posted in BUG | Tagged control file, dd, ora-202, ora-205, ora-27047, rman | Leave a comment

运行ocrconfig replace ocrmirror出现PROT-22错误

Posted on 2011/09/22 by yangtingkun

这个错误和前面的PROT-16是同时碰到的。
运行ocrconfig replace ocr出现PROT-16错误：https://yangtingkun.net/?p=116
由于碰到了上一篇文章提到的PROT-16错误，尝试先创建ocrmirror脚本：

# ./ocrconfig -REPLACE ocrmirror /dev/ocr2
PROT-22: Storage too small

现在ocr2的配置已经和要迁移的原ocr对应裸设备具有相同的大小，仍然导致了错误产生，而且根据错误信息不难看出，导致问题产生的原因是空间不足。
查询metalink发现，这是一个10.2.0.1上的bug，要求ocrmirror设备的大小要比原始ocr设备大128M，详细描述可以参考：ID 317628.1。
将ocrmirror对应的目标裸设备大小增加128M后，再次运行ocrconfig –replace ocrmirror，命令成功执行。

Posted in BUG | Tagged ocr mirror, OCRCONFIG, PROT-22, REPLACE | Leave a comment

Mini版Exadata发布——Oracle Database Appliance

Posted on 2011/09/21 by yangtingkun

由于Oracle的Exadata在大型应用中的成功，Oracle针对中小型企业推出了Exadata的mini版——Oracle Database Appliance。
Oracle Database Appliance主要针对中小企业，目标是降低中小企业的首次购买成本，因此满配的Appliance也要低于1/4配置的 Exadata，产品的主要优势是软件硬件一体化，同一个产品中集成了硬件、软件和网络，对于Oracle的License可以按需划分，最小仅需要支付4 Core的License，最多支持到24 Core。此外为了提高一体机的性能，服务器中还包含了4块73G的SSD来存储redo log，用以提高redo的读写速度。此外这款一体机的主推的功能是简化性，Oracle通过专门的部署和升级模块，使得Appliance的高可用环境部署变得异常简单，只需要2个小时左右的时间，即可配置好整个软件环境。而且以后的补丁管理同样十分方便，充分了体现了一体机简化安装简化管理的目标。另外为了降低首次购买成本，硬件费用虽然无法减少，但是Oracle的软件可以最少购买4 Core的License，而随着业务的增长可以以4 Core为最小单位增加License，最终可以扩展到24 Core。虽然Appliance面对中小型客户，但是即使是最低的4 Core单实例的配置，软件加上硬件总共也需要900K左右，而这对于中小企业而言，也绝对不是一个小数目。
虽然架构和EXADATA比较类似，也是软件硬件集成在一起，但是和EXADATA不同的是，所有的软件并非在出厂的时候全部部署好，而是除了操作系统外，没有任何其他软件，需要用户自己下载和安装。同时由于APPLIANCE的主要目标并非是性能，因此EXADATA特有的存储管理模板在APPLIANCE中并没有集成进去。而对于APPLIANCE更大的限制在于硬件无法扩展。
Oracle Database Appliance硬件包括：
4RU Chassis；
2 Server Nodes per Chassis:
2 Socket x86 per server；
6 Cores per socket 12 cores per server；
96 GB of memory per server；
2*500G SATA boot disks per server (mirrored)；
Shared disk:
24 SAS dual ported disk slots；
20*600G triple mirrored 12T RAW, 4T usable；
Networking:
2*1G internal private network；
2*10G public network；
6*1G public network；
RAS features:
Redundant cool and power。
Oracle Database Appliance部署的软件包括：
Oracle Linux 5.5；
Choice of Oracle Database EE, RAC or RAC One Node(11.2.0.2)；
Oracle Grid Infrastructure 11g Release 2 (11.2.0.2):
Oracle Clusterware；
Oracle Automatic Storage Management；
Oracle Enterprise Manager Database Control；
Oracle Automatic Service Requests；
Oracle Appliance Manager:
Configurator Module；
Deployment Module；
Storage Management Module；
Patching Module；
Validation & Diagnostic Tools Module。

Posted in NEWS | Leave a comment

运行ocrconfig replace ocr出现PROT-16错误

Posted on 2011/09/20 by yangtingkun

利用crsctl工具替换当前ocr时，出现了这个错误。
错误信息为：

#./ocrconfig -REPLACE ocr /dev/ocr1 
PROT-16: Internal Error

出现了一下metalink，发现导致这个错误的原因可能有两个，一个是当前的OCR裸设备空间的要求，Oracle推荐是128M、256M等等，如果原本OCR设备的大小小于128M，则OCR新的设备取128M，如果原本设备大小在128M到256M之间，则取256M大小。
原始设备大小为256M，而当前设置为512M，可能就是这个原因导致了错误的产生。
讲新的设备/dev/ocr1设置为相同的512M后，再次尝试上面的OCRCONFIG命令，错误依旧。
再次检查metalink，如果只有1个OCR设备，则REPLACE命令无效，因此需要首先执行./ocrconfig -replace ocrmirror /dev/ocr2，然后才能重新运行./ocrconfig -replace ocr /dev/ocr1命令。

Posted in BUG | Tagged Internal error, OCR, ocr mirror, OCRCONFIG, PROT-16, REPLACE | 1 Comment

ORA-600(kkoipt:invalid join method)错误

Posted on 2011/09/19 by yangtingkun

客户的11.2.0.2数据库碰到了这个错误。
详细错误信息如下：

Fri Sep 16 15:23:52 2011
Errors IN file /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident=169704):
ORA-00600: 内部错误代码, 参数: [kkoipt:invalid JOIN method], [1], [0], [], [], [], [], [], [], [], [], []
Incident details IN: /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc
Fri Sep 16 15:24:00 2011
Dumping diagnostic DATA IN directory=[cdmp_20110916152400], requested BY (instance=1, osid=20382140), summary=[incident=169704].
USE ADRCI OR Support Workbench TO package the incident.
See Note 411.1 at My Oracle Support FOR error AND packaging details.
而对应的详细TRACE如下：
bash-3.2$ more /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc
Dump file /u01/diag/rdbms/ora1/ora11/incident/incdir_169704/ora1_ora_20382140_i169704.trc
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, REAL Application Clusters, Automatic Storage Management, OLAP,
DATA Mining AND REAL Application Testing options
ORACLE_HOME = /u01/product/11.2.0/dbhome_1
System name:    AIX
Node name:      node1
Release:        1
Version:        6
Machine:        00F6CD264C00
Instance name: ora1
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 193
Unix process pid: 20382140, image: oracle@s180
*** 2011-09-16 15:23:52.275
*** SESSION ID:(29.7169) 2011-09-16 15:23:52.275
*** CLIENT ID:() 2011-09-16 15:23:52.275
*** SERVICE NAME:(ora1) 2011-09-16 15:23:52.275
*** MODULE NAME:(TOAD 10.5.0.41) 2011-09-16 15:23:52.275
*** ACTION NAME:() 2011-09-16 15:23:52.275
Dump continued FROM file: /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc
ORA-00600: 内部错误代码, 参数: [kkoipt:invalid JOIN method], [1], [0], [], [], [], [], [], [], [], [], []
========= Dump FOR incident 169704 (ORA 600 [kkoipt:invalid JOIN method]) ========
*** 2011-09-16 15:23:52.336
dbkedDefDump(): Starting incident DEFAULT dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7ukzmn3p6zby6) -----
SELECT *
  FROM (
  SELECT   a.childpolicyno, a.policyno, e.exportno corpno, a.effectdate,
                 TO_CHAR (a.lapsedate, 'yyyy-mm-dd') lapsedate, e.chnname,
                 e.address, e.keyflag, e.specialflag, g.empname,
                 g.secdeptname, f.TYPE, ROWNUM AS ID
            FROM t_schildpolicy a,
                 t_spolicy f,
                 t_exportcorp e,
                 t_nodeinfo d,
                 v_employeecustomer g
           WHERE f.policyno = a.policyno
             AND f.insurantno = e.exportno
             AND f.policyno = g.productid(+)
             AND d.nodeid = f.nodeid
             AND e.chnname LIKE '%%'
             AND ((d.corpid = '3502'))
             AND ROWNUM <= 10
        ORDER BY a.policyno)
 WHERE ID BETWEEN 1 AND 10
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
skdstdst()+40        bl       107b6e01c            FFFFFFFFFFECCA8 ? 000002004 ?
                                                   000000001 ? 000000003 ?
                                                   000000000 ? 000000002 ?
                                                   000000001 ? 000000000 ?
ksedst1()+104        CALL     skdstdst()           FFFFFFFFFFEBCB0 ? 000002004 ?
                                                   110A597A0 ? 10A027B2C ?
                                                   110A597A0 ? 000000000 ?
                                                   FFFFFFFFFFEBDE0 ? 700000007 ?
ksedst()+40          CALL     ksedst1()            3030000000000 ? 002050033 ?
                                                   10A027B20 ? 700000000025C ?
                                                   000000000 ? 000000000 ?
                                                   10A027180 ? 000000000 ?
dbkedDefDump()+2828  CALL     ksedst()             FFFFFFFFFFEBE90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 300000003 ?
ksedmp()+76          CALL     dbkedDefDump()       310A597A0 ? 1100010C8 ?
                                                   FFFFFFFFFFEC490 ?
                                                   28444040FFFEC66C ?
                                                   100148568 ? 1096635A8 ?
                                                   FFFFFFFFFFEC4E0 ? 11064B598 ?
ksfdmp()+88          CALL     ksedmp()             000000000 ? 000000000 ?
                                                   0096635C3 ? 109CB2C50 ?
                                                   200000000000000 ? 000000000 ?
                                                   110C221E8 ? 110A597A0 ?
dbgexPhaseII()+1212  CALL     ksfdmp()             000002004 ? 110A597A0 ?
                                                   000000000 ? FFFFFFFFFFEC658 ?
                                                   FFFFFFFFFFEC580 ?
                                                   FFFFFFFFFFECCA8 ? 1001D04B8 ?
                                                   110C221E8 ?
dbgexProcessError()  CALL     dbgexPhaseII()       110A597A0 ? 110C203F8 ?
+3604                                              0000296E8 ? 200000000 ?
                                                   FFFFFFFFFFED258 ? 00000006A ?
                                                   000000000 ? 000000000 ?
dbgeExecuteForError  CALL     dbgexProcessError()  110A597A0 ? 110C221E8 ?
()+72                                              100000000 ? 000000000 ?
                                                   110D01C88 ? 000000000 ?
                                                   110CADC78 ? 110C23F30 ?
dbgePostErrorKGE()+  CALL     dbgeExecuteForError  FFFFFFFFFFF0830 ?
1152                          ()                   B7417335409B9B1B ?
                                                   FFFFFFFFFFF06F0 ? 0409B9800 ?
                                                   10524EA10 ?
                                                   2147AE154168E65F ?
                                                   10524EA10 ? 000000000 ?
dbkePostKGE_kgsf()+  CALL     dbgePostErrorKGE()   002050000 ? 001160000 ?
64                                                 25810001330 ? 000000000 ?
                                                   110001330 ? FFFFFFFFFFF1510 ?
                                                   109613CD0 ? 110CB3F18 ?

Fri Sep 16 15:23:52 2011 Errors in file /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident=169704): ORA-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], [] Incident details in: /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc Fri Sep 16 15:24:00 2011 Dumping diagnostic data in directory=[cdmp_20110916152400], requested by (instance=1, osid=20382140), summary=[incident=169704]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 而对应的详细TRACE如下： bash-3.2$ more /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc Dump file /u01/diag/rdbms/ora1/ora11/incident/incdir_169704/ora1_ora_20382140_i169704.trc Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/product/11.2.0/dbhome_1 System name: AIX Node name: node1 Release: 1 Version: 6 Machine: 00F6CD264C00 Instance name: ora1 Redo thread mounted by this instance: 1 Oracle process number: 193 Unix process pid: 20382140, image: oracle@s180 *** 2011-09-16 15:23:52.275 *** SESSION ID:(29.7169) 2011-09-16 15:23:52.275 *** CLIENT ID:() 2011-09-16 15:23:52.275 *** SERVICE NAME:(ora1) 2011-09-16 15:23:52.275 *** MODULE NAME:(TOAD 10.5.0.41) 2011-09-16 15:23:52.275 *** ACTION NAME:() 2011-09-16 15:23:52.275 Dump continued from file: /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc ORA-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], [] ========= Dump for incident 169704 (ORA 600 [kkoipt:invalid join method]) ======== *** 2011-09-16 15:23:52.336 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=7ukzmn3p6zby6) ----- SELECT * FROM ( SELECT a.childpolicyno, a.policyno, e.exportno corpno, a.effectdate, TO_CHAR (a.lapsedate, 'yyyy-mm-dd') lapsedate, e.chnname, e.address, e.keyflag, e.specialflag, g.empname, g.secdeptname, f.TYPE, ROWNUM AS ID FROM t_schildpolicy a, t_spolicy f, t_exportcorp e, t_nodeinfo d, v_employeecustomer g WHERE f.policyno = a.policyno AND f.insurantno = e.exportno AND f.policyno = g.productid(+) AND d.nodeid = f.nodeid AND e.chnname LIKE '%%' AND ((d.corpid = '3502')) AND ROWNUM <= 10 ORDER BY a.policyno) WHERE ID BETWEEN 1 AND 10 ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- skdstdst()+40 bl 107b6e01c FFFFFFFFFFECCA8 ? 000002004 ? 000000001 ? 000000003 ? 000000000 ? 000000002 ? 000000001 ? 000000000 ? ksedst1()+104 call skdstdst() FFFFFFFFFFEBCB0 ? 000002004 ? 110A597A0 ? 10A027B2C ? 110A597A0 ? 000000000 ? FFFFFFFFFFEBDE0 ? 700000007 ? ksedst()+40 call ksedst1() 3030000000000 ? 002050033 ? 10A027B20 ? 700000000025C ? 000000000 ? 000000000 ? 10A027180 ? 000000000 ? dbkedDefDump()+2828 call ksedst() FFFFFFFFFFEBE90 ? 000000000 ? 000000000 ? 000000000 ? 000000000 ? 000000000 ? 000000000 ? 300000003 ? ksedmp()+76 call dbkedDefDump() 310A597A0 ? 1100010C8 ? FFFFFFFFFFEC490 ? 28444040FFFEC66C ? 100148568 ? 1096635A8 ? FFFFFFFFFFEC4E0 ? 11064B598 ? ksfdmp()+88 call ksedmp() 000000000 ? 000000000 ? 0096635C3 ? 109CB2C50 ? 200000000000000 ? 000000000 ? 110C221E8 ? 110A597A0 ? dbgexPhaseII()+1212 call ksfdmp() 000002004 ? 110A597A0 ? 000000000 ? FFFFFFFFFFEC658 ? FFFFFFFFFFEC580 ? FFFFFFFFFFECCA8 ? 1001D04B8 ? 110C221E8 ? dbgexProcessError() call dbgexPhaseII() 110A597A0 ? 110C203F8 ? +3604 0000296E8 ? 200000000 ? FFFFFFFFFFED258 ? 00000006A ? 000000000 ? 000000000 ? dbgeExecuteForError call dbgexProcessError() 110A597A0 ? 110C221E8 ? ()+72 100000000 ? 000000000 ? 110D01C88 ? 000000000 ? 110CADC78 ? 110C23F30 ? dbgePostErrorKGE()+ call dbgeExecuteForError FFFFFFFFFFF0830 ? 1152 () B7417335409B9B1B ? FFFFFFFFFFF06F0 ? 0409B9800 ? 10524EA10 ? 2147AE154168E65F ? 10524EA10 ? 000000000 ? dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 002050000 ? 001160000 ? 64 25810001330 ? 000000000 ? 110001330 ? FFFFFFFFFFF1510 ? 109613CD0 ? 110CB3F18 ?

这个SQL错误的奇特之处在于，如果将ORDER BY语句中的a.policyno变成其他列，则不会导致错误，甚至是换成与之相等关联的f.policyno，同样不会报错。
这显然是一个bug，查询metalink发现，居然目前唯一确认影响的版本就是11.2.0.2，这个Bug的描述为Bug 12591252 Query with ORDER BY fails with ORA-600 [kkoipt:invalid join method]。而Oracle的解释是，优化器试图通过索引来避免排序操作时，可能引发这个bug。这正好说明了为什么SQL中换成其他的列就不会导致错误。
目前这个bug在Windows平台的11.2.0.2的patch 10中被fixed，其他平台只能通过单独的12591252补丁来解决这个问题。当然如果能通过改写SQL来实现这个目的，无疑代价是最小的。

Posted in BUG | Tagged "kkoipt:invalid join method", 11.2.0.2, ORA-600, ORDER BY | Leave a comment

ROWNUM固化外部表结果集存在问题（二）

Posted on 2011/09/18 by yangtingkun

在客户的11.2.0.2环境中碰到了这个问题，Oracle在处理包含ROWNUM固化的外部表加载数据时返回错误的结果。
这一篇描述利用ROW_NUMBER来避免错误的产生。
外部表构造描述可以参考：https://yangtingkun.net/?p=35
ROWNUM固化外部表结果集存在问题：：https://yangtingkun.net/?p=38
这个例子是在本机上运行，因此只是告警日志的名称发生了变化，其他的语法和上一篇文章中的一致，当然D_ALERT目录需要指向background_dump_dest参数指定的路径：

SQL> CREATE TABLE T_ALERT
  2  (TEXT VARCHAR2(4000)
  3  )
  4  ORGANIZATION EXTERNAL
  5  (TYPE ORACLE_LOADER
  6  DEFAULT DIRECTORY D_ALERT
  7  ACCESS PARAMETERS
  8  (RECORDS DELIMITED BY NEWLINE
  9  FIELDS (TEXT (1:255) CHAR))
 10  LOCATION (' alert_ytk102.log'));
TABLE created.
SQL> SELECT * FROM T_ALERT WHERE ROWNUM <= 10;
TEXT
--------------------------------------------------------------------------
Dump file d:\oracle\product\admin\ytk102\bdump\alert_ytk102.log
Sat DEC 25 15:55:43 2010
ORACLE V10.2.0.5.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.1
CPU : 4 - TYPE 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1594M/2995M, Ph+PgF:4318M/5989M, VA:1911M/2047M
Sat DEC 25 15:55:43 2010
Starting ORACLE instance (normal)
已选择10行。

如果我们用ROW_NUMBER() OVER()分析函数来代替ROWNUM伪列，则上一篇提到的错误不再会出现，而且两种写法都可以得到正确的结果。
需要注意的是，ROW_NUMBER() OVER()分析函数需要一个ORDER BY列，而在这个例子中，没有明确的排序列，是根据读取顺序的，因此这里选择了ROWID进行排序：

SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT)
  2  SELECT * FROM A B 
  3  WHERE B.RN >= (SELECT MIN(C.RN) FROM A C WHERE TEXT = 'Mon Sep 19 07:18:21  2011');
        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 DATABASE Characterset IS ZHS16GBK
      5745 Opening WITH internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started WITH pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: ALTER DATABASE OPEN
已选择12行。
SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT)
  2  SELECT B.* 
  3  FROM A B, 
  4   (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21  2011') C
  5  WHERE B.RN >= C.RN;
        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 DATABASE Characterset IS ZHS16GBK
      5745 Opening WITH internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started WITH pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: ALTER DATABASE OPEN
已选择12行。
SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  2  SELECT B.* 
  3  FROM A B, 
  4   (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21  2011') C
  5  WHERE B.RN >= C.RN;
        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 DATABASE Characterset IS ZHS16GBK
      5745 Opening WITH internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started WITH pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: ALTER DATABASE OPEN
已选择12行。

SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT) 2 SELECT * FROM A B 3 WHERE B.RN >= (SELECT MIN(C.RN) FROM A C WHERE TEXT = 'Mon Sep 19 07:18:21 2011'); RN TEXT ---------- -------------------------------------------------------------------------------- 5739 Mon Sep 19 07:18:21 2011 5740 Successfully onlined Undo Tablespace 1. 5741 Mon Sep 19 07:18:21 2011 5742 SMON: enabling tx recovery 5743 Mon Sep 19 07:18:21 2011 5744 Database Characterset is ZHS16GBK 5745 Opening with internal Resource Manager plan 5746 replication_dependency_tracking turned off (no async multimaster replication found) 5747 Starting background process QMNC 5748 QMNC started with pid=17, OS id=5412 5749 Mon Sep 19 07:18:29 2011 5750 Completed: alter database open 已选择12行。 SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT) 2 SELECT B.* 3 FROM A B, 4 (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21 2011') C 5 WHERE B.RN >= C.RN; RN TEXT ---------- -------------------------------------------------------------------------------- 5739 Mon Sep 19 07:18:21 2011 5740 Successfully onlined Undo Tablespace 1. 5741 Mon Sep 19 07:18:21 2011 5742 SMON: enabling tx recovery 5743 Mon Sep 19 07:18:21 2011 5744 Database Characterset is ZHS16GBK 5745 Opening with internal Resource Manager plan 5746 replication_dependency_tracking turned off (no async multimaster replication found) 5747 Starting background process QMNC 5748 QMNC started with pid=17, OS id=5412 5749 Mon Sep 19 07:18:29 2011 5750 Completed: alter database open 已选择12行。 SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT) 2 SELECT B.* 3 FROM A B, 4 (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21 2011') C 5 WHERE B.RN >= C.RN; RN TEXT ---------- -------------------------------------------------------------------------------- 5739 Mon Sep 19 07:18:21 2011 5740 Successfully onlined Undo Tablespace 1. 5741 Mon Sep 19 07:18:21 2011 5742 SMON: enabling tx recovery 5743 Mon Sep 19 07:18:21 2011 5744 Database Characterset is ZHS16GBK 5745 Opening with internal Resource Manager plan 5746 replication_dependency_tracking turned off (no async multimaster replication found) 5747 Starting background process QMNC 5748 QMNC started with pid=17, OS id=5412 5749 Mon Sep 19 07:18:29 2011 5750 Completed: alter database open 已选择12行。

同时还修改了上一篇文章SQL的一个小bug，解决了ALERT读取中指定时间出现重复的问题。

Posted in BUG | Tagged external table, rownum, row_number, sqldr | Leave a comment

版本影响当前用户看到的对象

Posted on 2011/09/17 by yangtingkun

如果说版本影响当前用户可以访问的对象是正常的，但是对于DBA_OBJECTS而言，同一个用户下，在不同版本下看到的结果也是不一样的。
看一个简单的例子：

-bash-3.2$ sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production ON Thu Sep 15 21:04:58 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> SET pages 100 LINES 120
SQL> CREATE USER u1 IDENTIFIED BY u1 DEFAULT tablespace users enable editions;
USER created.
SQL> GRANT CONNECT, resource, dba TO u1;
GRANT succeeded.
SQL> conn u1/u1
Connected.
SQL> SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
------------------------------------------------------------------------
ORA$BASE
SQL> CREATE OR REPLACE PROCEDURE p1 AS 
  2  BEGIN
  3  NULL;
  4  END;
  5  /
PROCEDURE created.
SQL> CREATE OR REPLACE PROCEDURE p2 AS
  2  BEGIN
  3  NULL;
  4  END;
  5  /
PROCEDURE created.
SQL> CREATE OR REPLACE PROCEDURE p3 AS
  2  BEGIN
  3  NULL;
  4  END;
  5  /
PROCEDURE created.
SQL> CREATE edition e2;
Edition created.
SQL> SELECT COUNT(*) FROM dba_objects;
  COUNT(*)
----------
     13565
SQL> SELECT object_name, edition_name 
  2  FROM dba_objects
  3  WHERE owner = USER
  4  AND object_name LIKE 'P_';
OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE
SQL> ALTER SESSION SET edition = e2; 
SESSION altered.
SQL> SELECT COUNT(*) FROM dba_objects;
  COUNT(*)
----------
     13565
SQL> DROP PROCEDURE p2;
PROCEDURE dropped.
SQL> CREATE OR REPLACE PROCEDURE p1 AS
  2  BEGIN
  3  dbms_output.put_line('e2');
  4  END;
  5  /
PROCEDURE created.
SQL> SELECT COUNT(*) FROM dba_objects;
  COUNT(*)
----------
     13564
SQL> SELECT object_name, edition_name 
  2  FROM dba_objects
  3  WHERE owner = USER
  4  AND object_name LIKE 'P_';
OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P1                             E2
SQL> CREATE OR REPLACE PROCEDURE p4 AS
  2  BEGIN
  3  NULL;
  4  END;
  5  /
PROCEDURE created.
SQL> SELECT COUNT(*) FROM dba_objects;
  COUNT(*)
----------
     13565
SQL> SELECT object_name, edition_name
  2  FROM dba_objects
  3  WHERE owner = USER
  4  AND object_name LIKE 'P_';
OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P4                             E2
P1                             E2
SQL> ALTER SESSION SET edition = ora$base;
SESSION altered.
SQL> SELECT COUNT(*) FROM dba_objects;
  COUNT(*)
----------
     13565
SQL> SELECT object_name, edition_name
  2  FROM dba_objects
  3  WHERE owner = USER
  4  AND object_name LIKE 'P_';
OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE
SQL> conn test/test
Connected.
SQL> SELECT COUNT(*) FROM dba_objects;
  COUNT(*)
----------
     13565
SQL> SELECT object_name, edition_name
  2  FROM dba_objects
  3  WHERE owner = 'U1'
  4  AND object_name LIKE 'P_';
OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE

-bash-3.2$ sqlplus test/test SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 15 21:04:58 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> set pages 100 lines 120 SQL> create user u1 identified by u1 default tablespace users enable editions; User created. SQL> grant connect, resource, dba to u1; Grant succeeded. SQL> conn u1/u1 Connected. SQL> select sys_context('USERENV', 'CURRENT_EDITION_NAME') from dual; SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') ------------------------------------------------------------------------ ORA$BASE SQL> create or replace procedure p1 as 2 begin 3 null; 4 end; 5 / Procedure created. SQL> create or replace procedure p2 as 2 begin 3 null; 4 end; 5 / Procedure created. SQL> create or replace procedure p3 as 2 begin 3 null; 4 end; 5 / Procedure created. SQL> create edition e2; Edition created. SQL> select count(*) from dba_objects; COUNT(*) ---------- 13565 SQL> select object_name, edition_name 2 from dba_objects 3 where owner = user 4 and object_name like 'P_'; OBJECT_NAME EDITION_NAME ------------------------------ ------------------------------ P3 ORA$BASE P2 ORA$BASE P1 ORA$BASE SQL> alter session set edition = e2; Session altered. SQL> select count(*) from dba_objects; COUNT(*) ---------- 13565 SQL> drop procedure p2; Procedure dropped. SQL> create or replace procedure p1 as 2 begin 3 dbms_output.put_line('e2'); 4 end; 5 / Procedure created. SQL> select count(*) from dba_objects; COUNT(*) ---------- 13564 SQL> select object_name, edition_name 2 from dba_objects 3 where owner = user 4 and object_name like 'P_'; OBJECT_NAME EDITION_NAME ------------------------------ ------------------------------ P3 ORA$BASE P1 E2 SQL> create or replace procedure p4 as 2 begin 3 null; 4 end; 5 / Procedure created. SQL> select count(*) from dba_objects; COUNT(*) ---------- 13565 SQL> select object_name, edition_name 2 from dba_objects 3 where owner = user 4 and object_name like 'P_'; OBJECT_NAME EDITION_NAME ------------------------------ ------------------------------ P3 ORA$BASE P4 E2 P1 E2 SQL> alter session set edition = ora$base; Session altered. SQL> select count(*) from dba_objects; COUNT(*) ---------- 13565 SQL> select object_name, edition_name 2 from dba_objects 3 where owner = user 4 and object_name like 'P_'; OBJECT_NAME EDITION_NAME ------------------------------ ------------------------------ P3 ORA$BASE P2 ORA$BASE P1 ORA$BASE SQL> conn test/test Connected. SQL> select count(*) from dba_objects; COUNT(*) ---------- 13565 SQL> select object_name, edition_name 2 from dba_objects 3 where owner = 'U1' 4 and object_name like 'P_'; OBJECT_NAME EDITION_NAME ------------------------------ ------------------------------ P3 ORA$BASE P2 ORA$BASE P1 ORA$BASE

可以看到，DBA_OBJECT视图是版本化的视图，即使是拥有DBA权限的用户在查询这个视图的时候也只能看到当前版本可见的视图，因此DBA_OBJECTS视图不在包括数据库中所有的对象，如果想要获取数据库中各个版本的所有对象，查询DBA_OBJECTS_AE视图：

SQL> SELECT object_name, edition_name
  2  FROM dba_objects_ae
  3  WHERE owner = 'U1'
  4  AND object_name LIKE 'P_';
OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE
P4                             E2
P2                             E2
P1                             E2
6 ROWS selected.

Posted in ORACLE | Tagged dba_objects, dba_objects_ae, edition | Leave a comment

yangtingkun

11.2 sqlplus新增连接是指定版本

连接用户获取权限信息

获取业务用户列表

AIX环境dd迁移控制文件出现ORA-202和ORA-27047错误

运行ocrconfig replace ocrmirror出现PROT-22错误

Mini版Exadata发布——Oracle Database Appliance

运行ocrconfig replace ocr出现PROT-16错误

ORA-600(kkoipt:invalid join method)错误

ROWNUM固化外部表结果集存在问题（二）

版本影响当前用户看到的对象

Recent Posts

Recent Comments

Archives

Categories

Meta