yangtingkun

11.2数据库监听的改变

Posted on 2011/09/06 by yangtingkun

11.2由于RAC中GRID的分离以及实现了SCAN功能，使得监听的优先级发生了改变。
一个11.2环境的单实例数据库，配置了ASM，随后在同一个服务器上又新建了一个数据库，同样使用这个ASM，但是发现连接这个新的数据库时出现了错误。

[oracle@dbserver1 ~]$ export ORACLE_SID=al32utf8
[oracle@dbserver1 ~]$ sqlplus / AS sysdba
SQL*Plus: Release 11.2.0.2.0 Production ON Mon Sep 5 14:04:57 2011
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options
SQL> SET pages 100 LINES 120
SQL> SHOW parameter service_names
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      al32utf8
SQL> conn thams/thams@192.168.20.101/al32utf8
ERROR:
ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor
Warning: You are no longer connected TO ORACLE.

用这种简易连接的方式连接这个服务器上第一个创建的数据库实例，没有任何问题。检查监听的状态：

[oracle@dbserver1 ~]$ lsnrctl STATUS 
LSNRCTL FOR Linux: Version 11.2.0.2.0 - Production ON 05-SEP-2011 17:50:09
Copyright (c) 1991, 2010, Oracle.  ALL rights reserved.
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver1)(PORT=1521)))
STATUS OF the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR FOR Linux: Version 11.2.0.2.0 - Production
START DATE                31-AUG-2011 17:26:15
Uptime                    5 days 0 hr. 23 MIN. 53 sec
Trace Level               off
Security                  ON: LOCAL OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbserver1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "fhacdb1" has 1 instance(s).
  Instance "fhacdb1", STATUS READY, has 1 handler(s) FOR this service...
Service "fhacdbXDB" has 1 instance(s).
  Instance "fhacdb1", STATUS READY, has 1 handler(s) FOR this service...
The command completed successfully

可以看到，数据库的监听并没有包含新建的al32utf8的服务，尝试在数据库中进行手工注册操作，错误依旧。
这时想起数据库配置了GRID，切换到grid用户，果然发现了问题所在：

[oracle@dbserver1 ~]$ su - grid
Password: 
[grid@dbserver1 ~]$ lsnrctl STATUS
LSNRCTL FOR Linux: Version 11.2.0.2.0 - Production ON 05-SEP-2011 17:50:59
Copyright (c) 1991, 2010, Oracle.  ALL rights reserved.
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS OF the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR FOR Linux: Version 11.2.0.2.0 - Production
START DATE                04-AUG-2011 16:14:28
Uptime                    32 days 1 hr. 36 MIN. 33 sec
Trace Level               off
Security                  ON: LOCAL OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0/gridhome_1/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/dbserver1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver1)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", STATUS READY, has 1 handler(s) FOR this service...
Service "al32utf8" has 1 instance(s).
  Instance "al32utf8", STATUS READY, has 1 handler(s) FOR this service...
Service "al32utf8XDB" has 1 instance(s).
  Instance "al32utf8", STATUS READY, has 1 handler(s) FOR this service...
The command completed successfully
[grid@dbserver1 ~]$ exit
logout
[oracle@dbserver1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production ON Mon Sep 5 17:51:20 2011
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
SQL> conn thams/thams@192.168.20.101:1522/al32utf8
Connected.
SQL> SELECT name FROM v$database;
NAME
---------
AL32UTF8
SQL> SELECT instance_name FROM v$instance;
INSTANCE_NAME
----------------
al32utf8

原来新建的数据库实例注册到grid用户下的监听上了。之所以和另外一个数据库不同，是因为那个数据库是在grid安装之前，因此数据库使用ORACLE_HOME本身的监听。而一旦配置了grid，则grid用户下的监听启动，而Oracle的优先级显然是先找grid下的监听，然后再去考虑ORACLE_HOME下的监听。
当然，对于没有使用grid的情况下，11.2的监听和之前的版本没有什么区别。

Posted in ORACLE | Tagged 11.2, GRID, LISTENER, PORT | Leave a comment

运行csscan出现loading shared libraries错误

Posted on 2011/09/05 by yangtingkun

在一个11.2.0.2 for Linux X86-64环境中，运行csscan工具报错。
错误信息为：

[oracle@dbserver2 ~]$ cd $ORACLE_HOME/bin
[oracle@dbserver2 bin]$ csscan help=y
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot OPEN shared object file: No such file OR directory

检查metalink，在文档ID 742070.1中提到，导致这个问题的原因是没有正确的设置LD_LIBRARY_PATH环境变量，将$ORACLE_HOME/lib添加到这个环境变量中可以避免错误的产生：

[oracle@dbserver2 bin]$ env|grep LD_LIB
LD_LIBRARY_PATH=/etc/emc/rsa/cst/lib
[oracle@dbserver2 bin]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[oracle@dbserver2 bin]$ csscan help=y
CHARACTER SET Scanner v2.2 : Release 11.2.0.2.0 - Production ON Mon Jun 6 17:46:04 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
You can let Scanner prompt you FOR parameters BY entering the CSSCAN        
command followed BY your username/password:                                 
  Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"                          
Or, you can control how Scanner runs by entering the CSSCAN command         
followed by various parameters. To specify parameters, you use keywords:    
  Example:                                                                  
    CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3  
Keyword    Default Prompt Description                                       
---------- ------- ------ ------------------------------------------------- 
USERID             yes    username/password                                 
FULL       N       yes    scan entire database                              
USER               yes    owner of tables to be scanned                     
TABLE              yes    list of tables to scan                            
COLUMN             yes    list of columns to scan                            
EXCLUDE                   list of tables to exclude from scan               
TOCHAR             yes    new database character set name                   
FROMCHAR                  current database character set name               
TONCHAR                   new national character set name                   
FROMNCHAR                 current national character set name               
ARRAY      1024000 yes    size of array fetch buffer                        
PROCESS    1       yes    number of concurrent scan process                 
MAXBLOCKS                 split table if block size exceed MAXBLOCKS        
CAPTURE    N              capture convertible data                          
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows                      
BOUNDARIES                list of column size boundaries for summary report 
LASTRPT    N              generate report of the last database scan         
LOG        scan           base file name of report files                    
PARFILE                   parameter file name                               
PRESERVE   N              preserve existing scan results                    
LCSD       N       no     enable language and character set detection       
LCSDDATA   LOSSY   no     define the scope of the detection                 
HELP       N              show help screen (this screen)                    
QUERY      N              select clause to scan subset of tables or columns 
---------- ------- ------ ------------------------------------------------- 
Scanner terminated successfully.

[oracle@dbserver2 bin]$ env|grep LD_LIB LD_LIBRARY_PATH=/etc/emc/rsa/cst/lib [oracle@dbserver2 bin]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH [oracle@dbserver2 bin]$ csscan help=y Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Mon Jun 6 17:46:04 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. You can let Scanner prompt you for parameters by entering the CSSCAN command followed by your username/password: Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" Or, you can control how Scanner runs by entering the CSSCAN command followed by various parameters. To specify parameters, you use keywords: Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3 Keyword Default Prompt Description ---------- ------- ------ ------------------------------------------------- USERID yes username/password FULL N yes scan entire database USER yes owner of tables to be scanned TABLE yes list of tables to scan COLUMN yes list of columns to scan EXCLUDE list of tables to exclude from scan TOCHAR yes new database character set name FROMCHAR current database character set name TONCHAR new national character set name FROMNCHAR current national character set name ARRAY 1024000 yes size of array fetch buffer PROCESS 1 yes number of concurrent scan process MAXBLOCKS split table if block size exceed MAXBLOCKS CAPTURE N capture convertible data SUPPRESS maximum number of exceptions logged for each table FEEDBACK report progress every N rows BOUNDARIES list of column size boundaries for summary report LASTRPT N generate report of the last database scan LOG scan base file name of report files PARFILE parameter file name PRESERVE N preserve existing scan results LCSD N no enable language and character set detection LCSDDATA LOSSY no define the scope of the detection HELP N show help screen (this screen) QUERY N select clause to scan subset of tables or columns ---------- ------- ------ ------------------------------------------------- Scanner terminated successfully.

采用这个方法确实解决了问题。不过奇怪的问题是，另外一台服务器采用相同方式安装的数据库，并不存在这个问题，而且这台服务器上的LD_LIBRARY_PATH的设置与出现问题的服务器完全一致：

[oracle@dbserver1 ~]$ cd $ORACLE_HOME/bin
[oracle@dbserver1 bin]$ csscan help=y
CHARACTER SET Scanner v2.2 : Release 11.2.0.2.0 - Production ON Mon Sep 5 17:47:18 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
You can let Scanner prompt you FOR parameters BY entering the CSSCAN        
command followed BY your username/password:                                 
  Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"                          
Or, you can control how Scanner runs by entering the CSSCAN command         
followed by various parameters. To specify parameters, you use keywords:    
  Example:                                                                  
    CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3  
Keyword    Default Prompt Description                                       
---------- ------- ------ ------------------------------------------------- 
USERID             yes    username/password                                 
FULL       N       yes    scan entire database                              
USER               yes    owner of tables to be scanned                     
TABLE              yes    list of tables to scan                            
COLUMN             yes    list of columns to scan                            
EXCLUDE                   list of tables to exclude from scan               
TOCHAR             yes    new database character set name                   
FROMCHAR                  current database character set name               
TONCHAR                   new national character set name                   
FROMNCHAR                 current national character set name               
ARRAY      1024000 yes    size of array fetch buffer                        
PROCESS    1       yes    number of concurrent scan process                 
MAXBLOCKS                 split table if block size exceed MAXBLOCKS        
CAPTURE    N              capture convertible data                          
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows                      
BOUNDARIES                list of column size boundaries for summary report 
LASTRPT    N              generate report of the last database scan         
LOG        scan           base file name of report files                    
PARFILE                   parameter file name                               
PRESERVE   N              preserve existing scan results                    
LCSD       N       no     enable language and character set detection       
LCSDDATA   LOSSY   no     define the scope of the detection                 
HELP       N              show help screen (this screen)                    
QUERY      N              select clause to scan subset of tables or columns 
---------- ------- ------ ------------------------------------------------- 
Scanner terminated successfully.
[oracle@dbserver1 bin]$ env|grep LD_LIB
LD_LIBRARY_PATH=/etc/emc/rsa/cst/lib

[oracle@dbserver1 ~]$ cd $ORACLE_HOME/bin [oracle@dbserver1 bin]$ csscan help=y Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Mon Sep 5 17:47:18 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. You can let Scanner prompt you for parameters by entering the CSSCAN command followed by your username/password: Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" Or, you can control how Scanner runs by entering the CSSCAN command followed by various parameters. To specify parameters, you use keywords: Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3 Keyword Default Prompt Description ---------- ------- ------ ------------------------------------------------- USERID yes username/password FULL N yes scan entire database USER yes owner of tables to be scanned TABLE yes list of tables to scan COLUMN yes list of columns to scan EXCLUDE list of tables to exclude from scan TOCHAR yes new database character set name FROMCHAR current database character set name TONCHAR new national character set name FROMNCHAR current national character set name ARRAY 1024000 yes size of array fetch buffer PROCESS 1 yes number of concurrent scan process MAXBLOCKS split table if block size exceed MAXBLOCKS CAPTURE N capture convertible data SUPPRESS maximum number of exceptions logged for each table FEEDBACK report progress every N rows BOUNDARIES list of column size boundaries for summary report LASTRPT N generate report of the last database scan LOG scan base file name of report files PARFILE parameter file name PRESERVE N preserve existing scan results LCSD N no enable language and character set detection LCSDDATA LOSSY no define the scope of the detection HELP N show help screen (this screen) QUERY N select clause to scan subset of tables or columns ---------- ------- ------ ------------------------------------------------- Scanner terminated successfully. [oracle@dbserver1 bin]$ env|grep LD_LIB LD_LIBRARY_PATH=/etc/emc/rsa/cst/lib

虽然metalink给出的解决方法确实有效，但是导致这个问题的原因和可能是多个方面的，至少文档没有给出为什么同样配置同样版本的两个数据库，在一个上没有问题，而另一个上运行就出现错误。

Posted in BUG | Tagged csscan, LD_LIBRARY_PATH, loading shared libraries | Leave a comment

Oracle工具——csscan

Posted on 2011/09/04 by yangtingkun

工具csscan用于检查从一个字符集转换到另一个字符集，数据库中的数据是否会产生丢失、截断等现象。
这个工具很早就出现了，不过由于长久以来牵制到字符集转换的工作不是很多，因此对于这个工具没什么研究，这次需要将ZHS16GBK转换到AL32UTF8，尝试了一下csscan的功能，发现这个工具还是很方便的。

[oracle@dbserver1 bin]$ csscan userid=thams/thams TABLE=libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722
CHARACTER SET Scanner v2.2 : Release 11.2.0.2.0 - Production ON Wed Aug 31 10:54:15 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
CSS-00127: USER thams does NOT have DBA privilege
Scanner TERMINATED unsuccessfully.

错误信息很明显，连接用户不是DBA角色，如果用system用户连接进行这个命令：

[oracle@dbserver1 bin]$ csscan userid=system/oracle TABLE=thams.libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722
CHARACTER SET Scanner v2.2 : Release 11.2.0.2.0 - Production ON Wed Aug 31 10:54:45 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options
CSS-00107: CHARACTER SET migration utility schema NOT installed
Scanner TERMINATED unsuccessfully.

导致这个错误是由于CSSCAN工具需要在数据库中建立一个CSMIG用户：

[oracle@dbserver1 bin]$ sqlplus / AS sysdba
SQL*Plus: Release 11.2.0.2.0 Production ON Wed Aug 31 10:57:28 2011
Copyright (c) 1982, 2010, Oracle. ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options
SQL> @?/rdbms/admin/csminst
USER created.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
USER altered.
1 ROW created.
1 ROW updated.
TABLE created.
.
.
.
VIEW created.
VIEW created.
VIEW created.
VIEW created.
GRANT succeeded.
GRANT succeeded.
Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options
[oracle@dbserver1 bin]$ csscan userid=system/oracle TABLE=thams.libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722
CHARACTER SET Scanner v2.2 : Release 11.2.0.2.0 - Production ON Wed Aug 31 10:59:35 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates. ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options
Enter array fetch buffer SIZE: 1024000 > 4096000
Enter NUMBER OF scan processes TO utilize(1..64): 1 >
Enumerating TABLE TO scan...
. process 1 scanning THAMS.LIBFILE722[AAAP2KAAAAAB7XYAAA]
Creating DATABASE Scan Summary Report...
Creating Individual Exception Report...
Scanner TERMINATED successfully.

调用csminst.sql脚本创建辅助用户和对象后，再次运行csscan工具，对表中数据进行转换前的扫描。
工具csscan的调用有命令行方式，和交互两种，当命令行没有提供足够的参数，工具会以交互方式获取其他参数。
操作完成后，可以检查对应的日志信息：

[oracle@dbserver1 bin]$ more /home/oracle/scan_722.err 
DATABASE Scan Individual Exception Report
[DATABASE Scan Parameters]
Parameter                      VALUE                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  fhacdb1                                         
DATABASE Version               11.2.0.2.0                                      
Scan TYPE                      Selective TABLES                                
Scan CHAR DATA?                YES                                             
DATABASE CHARACTER SET         ZHS16GBK                                        
FROMCHAR                       ZHS16GBK                                        
TOCHAR                         AL32UTF8                                        
Scan NCHAR DATA?               NO                                              
Array fetch buffer SIZE        4096000                                         
NUMBER OF processes            1                                               
Capture convertible DATA?      NO                                              
------------------------------ ------------------------------------------------
[DATA Dictionary individual exceptions]
[Application DATA individual exceptions]
USER  : THAMS
TABLE : LIBFILE722
COLUMN: F4
TYPE  : VARCHAR2(42)
NUMBER OF Exceptions         : 1         
MAX Post Conversion DATA SIZE: 45        
ROWID              Exception TYPE      SIZE Cell DATA(FIRST 30 bytes)     
------------------ ------------------ ----- ------------------------------
AAAP2KAAAAAB+u0AAE exceed COLUMN SIZE    45 巡视台湾兼理学政陕西道监察御史
------------------ ------------------ ----- ------------------------------
[oracle@dbserver1 bin]$ more /home/oracle/scan_722.out 
CHARACTER SET Scanner v2.2 : Release 11.2.0.2.0 - Production ON Wed Aug 31 10:59:35 2011
Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, DATA Mining
AND REAL Application Testing options
Enter array fetch buffer SIZE: 1024000 > 
Enter NUMBER OF scan processes TO utilize(1..64): 1 > 
Enumerating TABLE TO scan...
. process 1 scanning THAMS.LIBFILE722[AAAP2KAAAAAB7XYAAA]
Creating DATABASE Scan Summary Report...
Creating Individual Exception Report...
Scanner TERMINATED successfully.
[oracle@dbserver1 bin]$ more /home/oracle/scan_722.txt 
DATABASE Scan Summary Report
TIME Started  : 2011-08-31 10:59:48
TIME Completed: 2011-08-31 10:59:50
Process ID         TIME Started       TIME Completed
---------- -------------------- --------------------
         1  2011-08-31 10:59:49  2011-08-31 10:59:49
---------- -------------------- --------------------
[DATABASE SIZE]
Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            581.81M         118.19M         700.00M            .00K
SYSAUX                            521.38M          78.63M         600.00M            .00K
UNDOTBS1                            7.44M      20,699.56M      20,707.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                           1,930.38M         139.88M       2,070.25M            .00K
LOB_AU2M                        8,176.13M   1,937,423.88M   1,945,600.00M         212.00K
LOB_AU8M                       10,074.69M     501,925.31M     512,000.00M            .00K
DATA_ARCHIVE                    5,332.06M   1,018,667.94M   1,024,000.00M            .00K
LOB_AU32M                       4,772.38M     507,227.63M     512,000.00M            .00K
LOB_AU64M                       4,548.38M     507,451.63M     512,000.00M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                          35,944.63M   4,493,732.63M   4,529,677.25M         212.00K
[DATABASE Scan Parameters]
Parameter                      VALUE                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  fhacdb1                                         
DATABASE Version               11.2.0.2.0                                      
Scan TYPE                      Selective TABLES                                
Scan CHAR DATA?                YES                                             
DATABASE CHARACTER SET         ZHS16GBK                                        
FROMCHAR                       ZHS16GBK                                        
TOCHAR                         AL32UTF8                                        
Scan NCHAR DATA?               NO                                              
Array fetch buffer SIZE        4096000                                         
NUMBER OF processes            1                                               
Capture convertible DATA?      NO                                              
------------------------------ ------------------------------------------------
[Scan Summary]
SOME CHARACTER TYPE application DATA are NOT convertible TO the NEW CHARACTER SET
[DATA Dictionary Conversion Summary]
DATA Dictionary TABLES:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                             0                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                                0                0                0                0
Total IN percentage              0.000%           0.000%           0.000%           0.000%
XML CSX Dictionary TABLES:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                             0                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                                0                0                0                0
Total IN percentage              0.000%           0.000%           0.000%           0.000%
[Application DATA Conversion Summary]
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                       222,683           28,308                1                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                          222,683           28,308                1                0
Total IN percentage             88.721%          11.278%           0.000%           0.000%
[Distribution OF Convertible, Truncated AND Lossy DATA BY TABLE]
DATA Dictionary TABLES:
USER.TABLE                                    Convertible       Truncation            Lossy
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary TABLES:
USER.TABLE                                    Convertible       Truncation            Lossy
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
Application DATA:
USER.TABLE                                    Convertible       Truncation            Lossy
---------------------------------------- ---------------- ---------------- ----------------
THAMS.LIBFILE722                                   28,308                1                0
---------------------------------------- ---------------- ---------------- ----------------
[Distribution OF Convertible, Truncated AND Lossy DATA BY COLUMN]
DATA Dictionary TABLES:
USER.TABLE|COLUMN                             Convertible       Truncation            Lossy
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary TABLES:
USER.TABLE|COLUMN                             Convertible       Truncation            Lossy
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
Application DATA:
USER.TABLE|COLUMN                             Convertible       Truncation            Lossy
---------------------------------------- ---------------- ---------------- ----------------
THAMS.LIBFILE722|F11                                4,648                0                0
THAMS.LIBFILE722|F2                                 4,643                0                0
THAMS.LIBFILE722|F3                                 4,648                0                0
THAMS.LIBFILE722|F46                                  222                0                0
THAMS.LIBFILE722|F6                                 4,312                0                0
THAMS.LIBFILE722|F7                                   565                0                0
THAMS.LIBFILE722|TITLE                              4,642                0                0
---------------------------------------- ---------------- ---------------- ---------------
[Indexes TO be Rebuilt]
USER.INDEX ON USER.TABLE(COLUMN)                                                         
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

[oracle@dbserver1 bin]$ more /home/oracle/scan_722.err Database Scan Individual Exception Report [Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name fhacdb1 Database Version 11.2.0.2.0 Scan type Selective tables Scan CHAR data? YES Database character set ZHS16GBK FROMCHAR ZHS16GBK TOCHAR AL32UTF8 Scan NCHAR data? NO Array fetch buffer size 4096000 Number of processes 1 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Data Dictionary individual exceptions] [Application data individual exceptions] User : THAMS Table : LIBFILE722 Column: F4 Type : VARCHAR2(42) Number of Exceptions : 1 Max Post Conversion Data Size: 45 ROWID Exception Type Size Cell Data(first 30 bytes) ------------------ ------------------ ----- ------------------------------ AAAP2KAAAAAB+u0AAE exceed column size 45 巡视台湾兼理学政陕西道监察御史 ------------------ ------------------ ----- ------------------------------ [oracle@dbserver1 bin]$ more /home/oracle/scan_722.out Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Wed Aug 31 10:59:35 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Enter array fetch buffer size: 1024000 > Enter number of scan processes to utilize(1..64): 1 > Enumerating table to scan... . process 1 scanning THAMS.LIBFILE722[AAAP2KAAAAAB7XYAAA] Creating Database Scan Summary Report... Creating Individual Exception Report... Scanner terminated successfully. [oracle@dbserver1 bin]$ more /home/oracle/scan_722.txt Database Scan Summary Report Time Started : 2011-08-31 10:59:48 Time Completed: 2011-08-31 10:59:50 Process ID Time Started Time Completed ---------- -------------------- -------------------- 1 2011-08-31 10:59:49 2011-08-31 10:59:49 ---------- -------------------- -------------------- [Database Size] Tablespace Used Free Total Expansion ------------------------- --------------- --------------- --------------- --------------- SYSTEM 581.81M 118.19M 700.00M .00K SYSAUX 521.38M 78.63M 600.00M .00K UNDOTBS1 7.44M 20,699.56M 20,707.00M .00K TEMP .00K .00K .00K .00K USERS 1,930.38M 139.88M 2,070.25M .00K LOB_AU2M 8,176.13M 1,937,423.88M 1,945,600.00M 212.00K LOB_AU8M 10,074.69M 501,925.31M 512,000.00M .00K DATA_ARCHIVE 5,332.06M 1,018,667.94M 1,024,000.00M .00K LOB_AU32M 4,772.38M 507,227.63M 512,000.00M .00K LOB_AU64M 4,548.38M 507,451.63M 512,000.00M .00K ------------------------- --------------- --------------- --------------- --------------- Total 35,944.63M 4,493,732.63M 4,529,677.25M 212.00K [Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name fhacdb1 Database Version 11.2.0.2.0 Scan type Selective tables Scan CHAR data? YES Database character set ZHS16GBK FROMCHAR ZHS16GBK TOCHAR AL32UTF8 Scan NCHAR data? NO Array fetch buffer size 4096000 Number of processes 1 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Scan Summary] Some character type application data are not convertible to the new character set [Data Dictionary Conversion Summary] Data Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 0 0 0 0 CHAR 0 0 0 0 LONG 0 0 0 0 VARRAY 0 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 0 0 0 0 Total in percentage 0.000% 0.000% 0.000% 0.000% XML CSX Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 0 0 0 0 CHAR 0 0 0 0 LONG 0 0 0 0 VARRAY 0 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 0 0 0 0 Total in percentage 0.000% 0.000% 0.000% 0.000% [Application Data Conversion Summary] Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 222,683 28,308 1 0 CHAR 0 0 0 0 LONG 0 0 0 0 VARRAY 0 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 222,683 28,308 1 0 Total in percentage 88.721% 11.278% 0.000% 0.000% [Distribution of Convertible, Truncated and Lossy Data by Table] Data Dictionary Tables: USER.TABLE Convertible Truncation Lossy ---------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE Convertible Truncation Lossy ---------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE Convertible Truncation Lossy ---------------------------------------- ---------------- ---------------- ---------------- THAMS.LIBFILE722 28,308 1 0 ---------------------------------------- ---------------- ---------------- ---------------- [Distribution of Convertible, Truncated and Lossy Data by Column] Data Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy ---------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy ---------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE|COLUMN Convertible Truncation Lossy ---------------------------------------- ---------------- ---------------- ---------------- THAMS.LIBFILE722|F11 4,648 0 0 THAMS.LIBFILE722|F2 4,643 0 0 THAMS.LIBFILE722|F3 4,648 0 0 THAMS.LIBFILE722|F46 222 0 0 THAMS.LIBFILE722|F6 4,312 0 0 THAMS.LIBFILE722|F7 565 0 0 THAMS.LIBFILE722|TITLE 4,642 0 0 ---------------------------------------- ---------------- ---------------- --------------- [Indexes to be Rebuilt] USER.INDEX on USER.TABLE(COLUMN) ----------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------

工具csscan会生成三个日志，一个err记录错误信息，比如这个例子中，一个列的长度需要变长，否则无法容纳数据长度的扩展；一个log记录操作步骤；而txt则是最终的汇总信息。

Posted in ORACLE | Tagged characterset, csscan | 1 Comment

Oracle高级安全管理手册

Posted on 2011/09/03 by yangtingkun

目前Oracle的文档已经看过不少了，而安全相关的内容是Oracle整体架构中不太熟悉的部分，最近一些项目会与安全方面有关，现在正好抽时间补一补。
这篇文档主要介绍两个方面的内容，一方面介绍数据的加密和完整性；另一方面介绍Oracle高级安全的认证。从技术上讲，主要包括透明数据加密、网络传输数据加密、RADIUS认证、Kerberos认证、SSL认证、ORACLE Wallet等。
和其他文档不太一样，这篇文档介绍的内容其实大部分都听说过，但是确实知之甚少，更不用说在真实环境下的使用经验了，不过根据个人的经验，大部分技术都需要你了解甚至是熟悉后，才会开始使用。或者说在你进行设计或实现的时候，基本没有什么可能性去使用一个你都不了解的技术，你的设计和方案是建立在你现有的知识体系上的。因此，绝大部分情况下，学习要放在使用的前面。
这篇文档的官方连接：http://www.oracle.com/pls/db112/to_toc?pathname=network.112%2Fe10746%2Ftoc.htm&remark=portal+%28Books%29

Posted in BOOKS | Leave a comment

临时表的表空间

Posted on 2011/09/02 by yangtingkun

简单描述临时表的存储特点。
临时表的数据存储在临时表空间中，这一点很显然，不过为什么临时表在DBA_TABLES视图中显示的表空间为空，以前确实一直不是很清楚。
前两天在和KAMUS讨论临时表的实现时，突然意识到这一点，由于临时表可以被多个用户访问，而不同用户的默认临时表空间的设置可能不同，这就意味着临时表的多个临时段可能存储在多个临时表空间中，正是这个原因，导致了临时表对应的表空间信息为空。
下面这个简单的例子说明了这种情况：

SQL> CONN / AS SYSDBA
已连接。
SQL> SET PAGES 100 LINES 120
SQL> COL FILE_NAME FORMAT A50
SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_TEMP_FILES;
TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------
TEMP                           D:\ORACLE\ORADATA\YTK102\TEMP01.DBF
SQL> SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME = 'TEST';
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST                           TEMP
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\ORACLE\ORADATA\YTK102\TEMP201.DBF' SIZE 100M;
表空间已创建。
SQL> CREATE USER TEST2 IDENTIFIED BY TEST2 TEMPORARY TABLESPACE TEMP2;
用户已创建。
SQL> GRANT CREATE SESSION TO TEST2;
授权成功。
SQL> CONN TEST/TEST
已连接。
SQL> CREATE GLOBAL TEMPORARY TABLE TMP1 (ID NUMBER);
表已创建。
SQL> GRANT ALL ON TMP1 TO TEST2;
授权成功。
SQL> CONN TEST2/TEST2
已连接。
SQL> INSERT INTO TEST.TMP1 VALUES (1);
已创建 1 行。
SQL> CONN TEST/TEST
已连接。
SQL> INSERT INTO TMP1 VALUES (2);
已创建 1 行。
SQL> ALTER TABLESPACE TEMP2 TEMPFILE OFFLINE;
表空间已更改。
SQL> CONN TEST/TEST
已连接。
SQL> INSERT INTO TMP1 VALUES (3);
已创建 1 行。
SQL> CONN TEST2/TEST2
已连接。
SQL> INSERT INTO TEST.TMP1 VALUES (4);
INSERT INTO TEST.TMP1 VALUES (4)
                 *
第 1 行出现错误:
ORA-01652: 无法通过 128 (在表空间 TEMP2 中) 扩展 temp 段

各个会话在访问临时表的时候，都会拥有自己独立的临时表段，而当多个用户的默认临时表空间不同时，临时表就可能在多个临时表空间上建立临时段，正是这个原因使得临时段的表空间属性无法确定。

Posted in ORACLE | Tagged temporary tablespace | Leave a comment

Oracle技术嘉年华即将召开

Posted on 2011/09/01 by yangtingkun

2011年10月21日到22日，“Oracle技术嘉年华”（OTN China Tour 2011）将在北京国宾饭店召开。
这次大会的主办方为：ACOUG、云和恩墨、ITPUB、IT168。
和以往国内组织的Oracle技术大会不同，ACOUG得到了Oracle及OUG用户组社区的支持，将OTN的亚太区巡回演讲的第二站安排在北京，届时一批国外的知名ACE Director和ACE将与我们零距离的分享Oracle技术。除了从欧美请来的国外演讲者外，我们同样请来大量国内的技术高手给大家分享技术。
大会的更多详细信息，请参考活动官方网址：http://tech.it168.com/topic/2011/8-24/otn/index.html

Posted in NEWS | Leave a comment

内存数据库缓存用户手册总结

Posted on 2011/08/31 by yangtingkun

最近准备拣拣TIMESTEN的东西。
第一次看TIMESTEN相关文档是在06年的时候，那时似乎Oracle刚收购TIMESTEN时间不长，而整好TIMESTEN还推出了一个新的版本，于是当时研究了一下。由于主要的精力还是在Oracle上面，于是主要关注的是利用TIMESTEN来提高查询和数据处理的速度，而TIMESTEN本身的语法和功能到并没有过多关注。
研究一段时间后发现，无论是TIMESTEN到ORACLE的数据同步功能还是TIMESTEN本身提供的功能，都与我的预期存在较大差异，因此TIMESTEN的研究就一直放下了。
过了5年的时间，TIMESTEN和Oracle的技术融合工作应该已经比较成熟了，而且似乎前不久的新版本中TIMESTEN也开始支持PL/SQL了，所以开始陆续重读TIMESTEN的文档。
至于这本内存数据库缓存用户手册，更像是数据库缓存的管理员手册，记录了数据库缓存环境的建立、管理、删除等的操作。

Posted in BOOKS | Leave a comment

AL32UTF8和UTF8字符集

Posted on 2011/08/30 by yangtingkun

客户的环境需要使用UTF8字符集，那么是使用AL32UTF8还是直接使用UTF8，这是一个问题。
Oracle的UTF8字符集由来已久，至少在8的时候就已经存在了，而对应的是UNICODE 3.0。而AL32UTF8字符集是9i才出现的，其对应的是UNICODE 5.0。
这两种字符集的区别在于，UNICODE 5.0与3.0相比，又增加了一些新的补充字符。但是在实际当中，使用到这些新增字符的可能性非常小，因此绝大部分情况下，选择UTF8也是足够的。
而对于数据库的访问而言，二者还是存在一定差异的。前面提到了AL32UTF8字符集是9i才出现的，那么对于9i以后的版本访问没有任何问题，但是对于8i及以前的版本，则不认识这个字符集。这就使得8i及更低版本的客户端在访问9i以上AL32UTF8的数据库时，会碰到各种各样的问题。因此，Oracle建议在选择AL32UTF8和UTF8字符集时，最关键的一点就是是否有8i及以下版本的客户端会登录到数据库中，如果没有则可以选择AL32UTF8，如果存在这种客户端，那么需要选择UTF8字符集。
随着现在版本11g逐渐开始称为主流版本，8i客户端的情况已经越来越少见了，因此在11.2的DBCA中，UTF8已经不是推荐字符集列表中的一员了。

Posted in ORACLE | Tagged AL32UTF8, UNICODE, UTF8 | Leave a comment

10g以后Oracle不支持ZHS32GB18030

Posted on 2011/08/29 by yangtingkun

在9i中Oracle存在字符集ZHS32GB18030，而10g以后，这个字符集在安装数据库的时候已经不可选了。
由于客户的环境需要输入大量的生僻字，要求客户端采用GB18030编码，这使得数据库无法使用ZHS16GBK字符集。
查询了一下字符编码方面的资料，最早推出的GB2312-80编码，包含了大约6000多个汉字，而对应的Oracle字符集编码为ZHS16CGB231280。这6000多个汉字对应日常应用足够，但是稍微生僻一些的汉字就无法在系统中显示。
此后推出了GBK编码，所支持的汉字超过了20000，这对于大部分情况来说足够使用了，其对应的Oracle数据库字符集就是中文中最常用的ZHS16GBK。GBK包含的所有GB2312编码中的汉字，但是二者并非严格意义上的超集关系。
在2000年的时候，出现了GB18030编码，它使用4位字符编码，因此覆盖的汉字达到了60000以上，这时GB18030中编码符合UNICODE 3.0。到2005年的时候，GB18030-2005又收录了一些新的汉字或图形，这时符合UNICODE 4.0编码。在Oracle9i中，存在字符集ZHS32GB18030，对于GB18030编码，但是从10g开始，数据库字符集不再支持ZHS32GB18030字符集了。虽然包括metalink在内介绍了先创建US7ASCII字符集在通过修改数据库字符集的方法将数据库字符集转化为ZHS32GB18030，但是这种方法毕竟不是官方推荐的方法，如果说10g的数据库安装过程中不能选择ZHS32GB18030字符集，是Oracle漏掉了这个字符集，那么在11.2中，同样无法选择这个字符集，就明确说明了Oracle的态度了。事实上，从10g开始，ZHS32GB18030变为客户端字符集，而数据库中之所以还可以创建这个字符集，是Oracle为了后向兼容性，确保9i中ZHS32GB18030字符集的数据库可以顺利的升级。
10g中不再支持ZHS32GB18030字符集，因此Oracle建议用户更改字符集为AL32UTF8或UTF8字符集，详细文档可以参考ID 1144903.1。不过在11.2中UTF8同样是不推荐的字符集之一，那么如果需要在客户端使用GB18030编码，那么推荐使用AL32UTF8字符集。如果客户端使用GB18030-2000编码，那么可以在数据库中选择AL32UTF8字符集，而客户端字符集选择ZHS32GB18030，所有的客户端字符都可以顺利的保存到服务器端或从服务器端读取。如果客户端选择GB18030-2005编码，那么没有专门的客户端字符集与之对应，因此客户端应该与数据库保持一致，都选择AL32UTF8字符集。

Posted in ORACLE | Tagged AL32UTF8, UNICODE, ZHS32GB18030 | 1 Comment

ORA-600(krvxdds: duplicated session not）错误

Posted on 2011/08/28 by yangtingkun

在客户的告警日志中发现这个错误信息。
这个错误信息是第一次看到，而且在metalink中也没有找到任何相关的描述，详细的错误信息如下：

Fri Nov 19 11:47:47 2010
<krvrd.c:krvrdqgov>: Invalid dictionary process cntxt.
Fri Nov 19 11:47:47 2010
Errors IN file /oracle/db/admin/B1MODDB/udump/b1moddb1_ora_4488.trc:
ORA-00600: internal error code, arguments: [krvxdds: duplicated SESSION NOT ], [], [], [], [], [], [], []
ORA-01334: invalid OR missing logminer dictionary processes context
Fri Nov 19 11:48:05 2010
Trace dumping IS performing id=[cdmp_20101119114805]
Fri Nov 19 11:48:05 2010
Errors IN file /oracle/db/admin/B1MODDB/udump/b1moddb1_ora_4488.trc:
ORA-07445: exception encountered: core dump [lsfcln()+49] [SIGSEGV] [Address NOT mapped TO object] [0x4200757400696D65] [] []
ORA-00600: internal error code, arguments: [krvxdds: duplicated SESSION NOT ], [], [], [], [], [], [], []
ORA-01334: invalid OR missing logminer dictionary processes context

在ORA-600错误出现之前，报了一个C语言的错误，显然这是导致ORA-600错误的原因。分析ORA-600错误随后的ORA-1334错误，基本可以确定，根本错误原因是ORA-1334，而600错误是这个错误所引发的。

ORA-01334: invalid OR missing logminer dictionary processes context
Cause: Unexpected internal error condition
Action: NONE

而这个ORA-1334同样是一个内容错误，既没有解释错误原因，也没有说明解决方法。
继续检查对应的TRACE信息：

/oracle/db/admin/B1MODDB/udump/b1moddb1_ora_4488.trc
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, REAL Application Clusters, Oracle Label Security, DATA Mining
AND REAL Application Testing options
ORACLE_HOME = /oracle/db
System name: HP-UX
Node name: MODDB1
Release: B.11.23
Version: U
Machine: ia64
Instance name: B1MODDB1
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 0
Unix process pid: 4488, image: oracle@MODDB1
 
Ioctl ASYNC_CONFIG error, errno = 1
/oracle/db/admin/B1MODDB/udump/b1moddb1_ora_4488.trc
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
WITH the Partitioning, REAL Application Clusters, Oracle Label Security, DATA Mining
AND REAL Application Testing options
ORACLE_HOME = /oracle/db
System name: HP-UX
Node name: MODDB1
Release: B.11.23
Version: U
Machine: ia64
Instance name: B1MODDB1
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 159
Unix process pid: 4488, image: oracle@MODDB1
*** ACTION NAME:() 2010-11-19 11:47:47.799
*** MODULE NAME:(TOAD 9.7.0.51) 2010-11-19 11:47:47.799
*** SERVICE NAME:(B1MODDB) 2010-11-19 11:47:47.799
*** SESSION ID:(473.17735) 2010-11-19 11:47:47.799
*** 2010-11-19 11:47:47.799
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [krvxdds: duplicated SESSION NOT ], [], [], [], [], [], [], []
ORA-01334: invalid OR missing logminer dictionary processes context
CURRENT SQL statement FOR this SESSION:
SELECT * FROM V$LOGMNR_CONTENTS 
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+64          CALL     ksedst1()            000000000 ? 000000001 ?
ksedmp()+2176        CALL     ksedst()             000000000 ?
                                                   C000000000000C9F ?
                                                   4000000003EDCDE0 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ksfdmp()+48          CALL     ksedmp()             000000003 ?
kgerinv()+304        CALL     ksfdmp()             C000000000000612 ?
                                                   000000003 ?
                                                   400000000938F250 ?
                                                   00001603B ? 000000000 ?
                                                   000000000 ?
kgeasnmierr()+144    CALL     kgerinv()            6000000000031370 ?
                                                   40000000018E6B10 ?
                                                   6000000000032428 ?
                                                   40000000018E6B10 ?
                                                   9FFFFFFFFFFF62E0 ?
krvxdds()+368        CALL     kgeasnmierr()        6000000000031370 ?
                                                   60000000001CF510 ?
                                                   60000000001CF520 ?
                                                   60000000000327A0 ?
                                                   40000000012AFBB0 ?
krvfpsc_PostSelectC  CALL     krvxdds()            9FFFFFFFBF3AABB0 ?
leanup()+432                                       9FFFFFFFFFFF6318 ?
                                                   C000000000000896 ?
                                                   4000000007161A50 ?
                                                   9FFFFFFFFFFF6308 ?
krvfcact()+4496      CALL     krvfpsc_PostSelectC  9FFFFFFFBF37A0B8 ?
                              leanup()             C0000000000013AE ?
                                                   4000000007162EC0 ?
                                                   0000160FB ?
                                                   9FFFFFFFBC8044A8 ?
                                                   9FFFFFFFFFFF6320 ?
                                                   9FFFFFFFFFFF6310 ?
qerfxFetch()+1040    CALL     krvfcact()           9FFFFFFFFFFF6930 ?
                                                   9FFFFFFFBEF80500 ?
                                                   9FFFFFFFFFFF6330 ?
                                                   C0000000000015B3 ?
                                                   0000000CA ?
                                                   9FFFFFFFBD480738 ?
                                                   4000000002D65350 ?
                                                   9FFFFFFFFFFF6330 ?
opifch2()+9632       CALL     qerfxFetch()         C0000002D9618B28 ?
                                                   4000000001AD7CF0 ?
                                                   9FFFFFFFFFFF6B90 ?
                                                   0000001F4 ?
                                                   60000000000AAC20 ?
                                                   C000000000001F46 ?
                                                   4000000002D43740 ?
                                                   000018371 ?
opifch()+112         CALL     opifch2()            9FFFFFFFFFFF7B50 ?
                                                   4000000002DBFA70 ?
                                                   000018287 ?

/oracle/db/admin/B1MODDB/udump/b1moddb1_ora_4488.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Oracle Label Security, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/db System name: HP-UX Node name: MODDB1 Release: B.11.23 Version: U Machine: ia64 Instance name: B1MODDB1 Redo thread mounted by this instance: 1 Oracle process number: 0 Unix process pid: 4488, image: oracle@MODDB1 Ioctl ASYNC_CONFIG error, errno = 1 /oracle/db/admin/B1MODDB/udump/b1moddb1_ora_4488.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Oracle Label Security, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/db System name: HP-UX Node name: MODDB1 Release: B.11.23 Version: U Machine: ia64 Instance name: B1MODDB1 Redo thread mounted by this instance: 1 Oracle process number: 159 Unix process pid: 4488, image: oracle@MODDB1 *** ACTION NAME:() 2010-11-19 11:47:47.799 *** MODULE NAME:(TOAD 9.7.0.51) 2010-11-19 11:47:47.799 *** SERVICE NAME:(B1MODDB) 2010-11-19 11:47:47.799 *** SESSION ID:(473.17735) 2010-11-19 11:47:47.799 *** 2010-11-19 11:47:47.799 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [krvxdds: duplicated session not ], [], [], [], [], [], [], [] ORA-01334: invalid or missing logminer dictionary processes context Current SQL statement for this session: SELECT * FROM V$LOGMNR_CONTENTS ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst()+64 call ksedst1() 000000000 ? 000000001 ? ksedmp()+2176 call ksedst() 000000000 ? C000000000000C9F ? 4000000003EDCDE0 ? 000000000 ? 000000000 ? 000000000 ? ksfdmp()+48 call ksedmp() 000000003 ? kgerinv()+304 call ksfdmp() C000000000000612 ? 000000003 ? 400000000938F250 ? 00001603B ? 000000000 ? 000000000 ? kgeasnmierr()+144 call kgerinv() 6000000000031370 ? 40000000018E6B10 ? 6000000000032428 ? 40000000018E6B10 ? 9FFFFFFFFFFF62E0 ? krvxdds()+368 call kgeasnmierr() 6000000000031370 ? 60000000001CF510 ? 60000000001CF520 ? 60000000000327A0 ? 40000000012AFBB0 ? krvfpsc_PostSelectC call krvxdds() 9FFFFFFFBF3AABB0 ? leanup()+432 9FFFFFFFFFFF6318 ? C000000000000896 ? 4000000007161A50 ? 9FFFFFFFFFFF6308 ? krvfcact()+4496 call krvfpsc_PostSelectC 9FFFFFFFBF37A0B8 ? leanup() C0000000000013AE ? 4000000007162EC0 ? 0000160FB ? 9FFFFFFFBC8044A8 ? 9FFFFFFFFFFF6320 ? 9FFFFFFFFFFF6310 ? qerfxFetch()+1040 call krvfcact() 9FFFFFFFFFFF6930 ? 9FFFFFFFBEF80500 ? 9FFFFFFFFFFF6330 ? C0000000000015B3 ? 0000000CA ? 9FFFFFFFBD480738 ? 4000000002D65350 ? 9FFFFFFFFFFF6330 ? opifch2()+9632 call qerfxFetch() C0000002D9618B28 ? 4000000001AD7CF0 ? 9FFFFFFFFFFF6B90 ? 0000001F4 ? 60000000000AAC20 ? C000000000001F46 ? 4000000002D43740 ? 000018371 ? opifch()+112 call opifch2() 9FFFFFFFFFFF7B50 ? 4000000002DBFA70 ? 000018287 ?

可以看到，导致错误产生的SQL是查询V$LOGMNR_CONTENTS视图的语句。除了这个错误信息外，还发现执行这个语句的客户端工具是TOAD：

   (FOB) flags=2 fib=c00000032fa3a498 incno=0 pending i/o cnt=0
     fname=/dev/vg12/rsystem
     fno=1 lblksz=8192 fsiz=397311
    ----------------------------------------
    SO: c00000033f60dfb0, TYPE: 4, owner: c00000033f45b248, flag: INIT/-/-/0x00
    (SESSION) sid: 473 trans: 0000000000000000, creator: c00000033f45b248, flag: (8000041) USR/- BSY/-/-/-/-/-
              DID: 0001-009F-00012412, short-term DID: 0001-009F-00012413
              txn branch: 0000000000000000
              oct: 3, prv: 0, SQL: c0000002967468c0, psql: c0000002eed195f0, USER: 0/SYS
    service name: B1MODDB
    O/S info: USER: Ly, term: LIYAN, ospid: 3404:2428, machine: MSHOME\LIYAN
              program: toad.exe
    application name: TOAD 9.7.0.51, hash VALUE=1244923487
    LAST wait FOR 'SQL*Net message from client' blocking sess=0x0000000000000000 seq=753 wait_time=16896447 seconds since wait started=18
                driver id=54435000, #bytes=1, =0

配合ORA-600的第一个错误参数：krvxdds: duplicated session not，怀疑可能是TOAD在运行LOGMNR命令时，使用了多个窗口或者指定的字典有误，从而导致了这个错误出现。在告警日志中，出现了多次的LOGMNR的相关信息，但是这个错误只出现了一次，而这个错误在metalink都找不到，也说明这个错误很难触发。看来这可能是工具或者会话状态不正常引起的错误，只需要重新连接数据库，错误就不会再现，而且对于数据库而言基本没有影响。

Posted in BUG | Tagged krvxdds: duplicated session not, logminer, ORA-600 | Leave a comment

yangtingkun

11.2数据库监听的改变

运行csscan出现loading shared libraries错误

Oracle工具——csscan

Oracle高级安全管理手册

临时表的表空间

Oracle技术嘉年华即将召开

内存数据库缓存用户手册总结

AL32UTF8和UTF8字符集

10g以后Oracle不支持ZHS32GB18030

ORA-600(krvxdds: duplicated session not）错误

Recent Posts

Recent Comments

Archives

Categories

Meta