Author Archives: 惜分飞

Fatal NI connect error 12170

Posted on by

今天在一台服务器的日志文件中，发现如下信息：

Fatal NI connect error 12170.

  VERSION INFORMATION:
	TNS for Linux: Version 11.1.0.7.0 - Production
	Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.1.0.7.0 - Production
	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
  Time: 08-NOV-2011 13:57:10
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.100.107.62)(PORT=52357))

查看mos，有幸发现关于该错误的相关文章
Fatal NI connect error 12170′, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log [ID 1286376.1]
做了一些摘要，算是给自己做个记录，也给不能访问mos的朋友一个参考

1、适用范围

Oracle Net Services - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2   [Release: 11.1 to 11.2]
Information in this document applies to any platform.

2、问题原因

These time out related messages are mostly informational in nature.  The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:

#define ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110

ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:

ETIMEDOUT 238 /* Connection timed out */

For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Description:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default.

3、解决问题

To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

   - Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

说明：这个问题是由于Automatic Diagnostic Repository中的 Oracle Net diagnostic在默认的情况下是开启的，当数据库和客户端的连接超过特定时间，就会把这样的信息写入到alert日志中，所以这不是一个致命的问题，如果偶尔出现，可以忽略有点类此ora-3136的错误

议rman的crosscheck和obsolete

Posted on by

今天有朋友对于crosscheck和obsolete理解的不太清楚，网上查找了一些资料，也发现很多错误，其中典型的理解就是:crosscheck 可以检测/删除违背(obsolete)备份策略的备份集

--登录rman
[oracle@node1 ~]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Nov 8 13:39:17 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ECP (DBID=1669273445)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2048 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/db_1/dbs/snapcf_ecp.f'; # default
--发现配置的策略是保留1份

--全库备份
RMAN> list backup summary;
--当前数据库无备份集备份

RMAN> backup database format '/opt/backup/ecp_full_%U';

Starting backup at 2011-11-08 13:22:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=534 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/opt/oracle/oradata/ecp/users01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/ecp/sysaux01.dbf
input datafile fno=00008 name=/opt/oracle/oradata/ecp/TS_Public_1.003.dbf
input datafile fno=00009 name=/opt/oracle/oradata/ecp/TS_Index_Base.001.dbf
input datafile fno=00010 name=/opt/oracle/oradata/ecp/TS_Index_Base.002.dbf
input datafile fno=00011 name=/opt/oracle/oradata/ecp/TS_Index_Base.003.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:07
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/opt/oracle/oradata/ecp/undotbs01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/ecp/system01.dbf
input datafile fno=00012 name=/opt/oracle/oradata/ecp/OGG.001.dbf
input datafile fno=00005 name=/opt/oracle/oradata/ecp/example01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/ecp/TS_Public_1.001.dbf
input datafile fno=00007 name=/opt/oracle/oradata/ecp/TS_Public_1.002.dbf
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:22:07
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:22
piece handle=/opt/backup/ecp_full_11mr52bv_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:23
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:24
piece handle=/opt/backup/ecp_full_13mr52ce_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:25
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:26
piece handle=/opt/backup/ecp_full_14mr52cg_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:22:41
piece handle=/opt/backup/ecp_full_12mr52bv_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:34
Finished backup at 2011-11-08 13:22:41

--备份sysdata01.dbf文件
RMAN> backup datafile 1 format '/opt/backup/ecp_system_%U';

Starting backup at 2011-11-08 13:23:44
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/ecp/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:23:45
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:23:45
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:23:46
piece handle=/opt/backup/ecp_system_16mr52f1_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:23:47
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:23:48
piece handle=/opt/backup/ecp_system_17mr52f2_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:24:13
piece handle=/opt/backup/ecp_system_15mr52f1_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 2011-11-08 13:24:13
--注：所有备份system空间，都是会自动备份spfile和控制文件

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        2011-11-08 13:22:19 1       1       YES        TAG20111108T132207
29      B  F  A DISK        2011-11-08 13:22:23 1       1       YES        TAG20111108T132207
30      B  F  A DISK        2011-11-08 13:22:25 1       1       YES        TAG20111108T132207
31      B  F  A DISK        2011-11-08 13:22:35 1       1       YES        TAG20111108T132207
32      B  F  A DISK        2011-11-08 13:23:45 1       1       YES        TAG20111108T132344
33      B  F  A DISK        2011-11-08 13:23:47 1       1       YES        TAG20111108T132344
34      B  F  A DISK        2011-11-08 13:24:03 1       1       YES        TAG20111108T132344
--查看所有备份集情况

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
--查看全部有效

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
--因为system01.dbf备份了两次，所以违背了备份策略

drwxr-xr-x  2 root    root      4096 07-12 15:54 vmdir
[root@node1 opt]# cd /opt/backup/
[root@node1 backup]# ll
总计 265356
-rw-r----- 1 oracle oinstall  47497216 11-08 13:22 ecp_full_11mr52bv_1_1
-rw-r----- 1 oracle oinstall 129433600 11-08 13:22 ecp_full_12mr52bv_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:22 ecp_full_13mr52ce_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:22 ecp_full_14mr52cg_1_1
-rw-r----- 1 oracle oinstall  92012544 11-08 13:24 ecp_system_15mr52f1_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:23 ecp_system_16mr52f1_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:23 ecp_system_17mr52f2_1_1
[root@node1 backup]# mv ecp_system_15mr52f1_1_1 ecp_system_15mr52f1_1_1_bak
[root@node1 backup]# ll
总计 265356
-rw-r----- 1 oracle oinstall  47497216 11-08 13:22 ecp_full_11mr52bv_1_1
-rw-r----- 1 oracle oinstall 129433600 11-08 13:22 ecp_full_12mr52bv_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:22 ecp_full_13mr52ce_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:22 ecp_full_14mr52cg_1_1
-rw-r----- 1 oracle oinstall  92012544 11-08 13:24 ecp_system_15mr52f1_1_1_bak
-rw-r----- 1 oracle oinstall   1130496 11-08 13:23 ecp_system_16mr52f1_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:23 ecp_system_17mr52f2_1_1
--对备份集中的其中一个文件重命名

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
Crosschecked 6 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
Crosschecked 1 objects
--发现一个无效的备份集

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        2011-11-08 13:22:19 1       1       YES        TAG20111108T132207
29      B  F  A DISK        2011-11-08 13:22:23 1       1       YES        TAG20111108T132207
30      B  F  A DISK        2011-11-08 13:22:25 1       1       YES        TAG20111108T132207
31      B  F  A DISK        2011-11-08 13:22:35 1       1       YES        TAG20111108T132207
32      B  F  A DISK        2011-11-08 13:23:45 1       1       YES        TAG20111108T132344
33      B  F  A DISK        2011-11-08 13:23:47 1       1       YES        TAG20111108T132344
34      B  F  X DISK        2011-11-08 13:24:03 1       1       YES        TAG20111108T132344
--也标志为无效'X'

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
--策略还是显示这两个备份集违背规则

RMAN> DELETE NOPROMPT OBSOLETE;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
deleted backup piece
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
deleted backup piece
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
Deleted 2 objects

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found
--删除违法策略的备份集

RMAN> delete  NOPROMPT  expired backup;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
34      34      1   1   EXPIRED     DISK        /opt/backup/ecp_system_15mr52f1_1_1
deleted backup piece
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
Deleted 1 EXPIRED objects

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
Crosschecked 4 objects
--删除无效的备份集

通过实验说明：crosscheck只能够检测备份集是否有效(最常见的情况就是物理上是否还存在),并且可以用它来删除失效(expired)的备份集，而不是用来删除违背备份策略(obsolete)的备份集，如果要删除违背备份策略(废弃)的备份集，需要使用obsolete操作。出现这个问题的主要原因应该是expired和obsolete翻译成中文的时候理解的出入导致。

清空schema中所有表的comment信息

Posted on by

今天中午一朋友问我怎么清空一个用户下面所有的表的comment信息（估计是系统要发布或者买出去，不想让人知道表结构的含义），我当时的感觉就是直接去基表中去修改，这样可以一次性实现，于是就做了下面试验，并给他提供了相关sql语句

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 8 12:17:24 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--我们可以通过DBA_COL_COMMENTS视图（或者同义词）查询到表的comment信息
--那么我们通过这个表找到comment的基表是什么表
SQL> set long 100000
SQL> set pages 0
SQL> SELECT DBMS_METADATA.get_ddl('VIEW','DBA_COL_COMMENTS','SYS') FROM DUAL;

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_COL_COMMENTS" ("OWNER", "TABLE_NAME",
  select u.name, o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
where o.owner# = u.user#
  and o.type# in (2, 4)
  and o.obj# = c.obj#
  and c.obj# = co.obj#(+)
  and c.intcol# = co.col#(+)
  and bitand(c.property, 32) = 0 /* not hidden column */

--通过上面的语句，我们发现col$是存储commet的基表
SQL> desc sys.com$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 COL#                                               NUMBER
 COMMENT$                                           VARCHAR2(4000)

--查询CHF用户下面表的commet情况
SQL> col comment$ for a30
SQL> SELECT *
  2    FROM SYS.COM$ A
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = A.OBJ#)
  8     AND COMMENT$ IS NOT NULL;

      OBJ#       COL# COMMENT$
---------- ---------- ------------------------------
     67405          1 xifenfei1
     67405          2 xifenfei2
     67405          3 xifenfei3
     67405          8 惜分飞
     67405         13 chf
     67405         17 xifenfei88
     71926          1 feifei
     71926          2 chf
     71926          3 xff
     70870          1 xifenfei

10 rows selected.

--更新基表的comment$的信息为null
SQL> UPDATE SYS.COM$
  2     SET COMMENT$ = NULL
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = OBJ#)
  8     AND COMMENT$ IS NOT NULL;

10 rows updated.

SQL> commit;

Commit complete.

--验证更新成功，chf下面的所有comment信息都变成了null
SQL> SELECT *
  2    FROM SYS.COM$ A
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = A.OBJ#)
  8     AND COMMENT$ IS NOT NULL;

no rows selected

SQL> SELECT * FROM DBA_COL_COMMENTS WHERE comments IS NOT NULL AND owner='CHF';

no rows selected

暴力破解Oracle数据库密码

Posted on by

一、验证不能通过修改用户的password实现登录不知道密码的用户

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 7 12:22:46 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> grant create session to xff identified by xifenfei;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> grant create session to chf identified by xifenfei;

Grant succeeded.

SQL> conn chf/xifenfei
Connected.

SQL> conn / as sysdba
Connected.
SQL> desc user$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER#                                     NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 DATATS#                                   NOT NULL NUMBER
 TEMPTS#                                   NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 PTIME                                              DATE
 EXPTIME                                            DATE
 LTIME                                              DATE
 RESOURCE$                                 NOT NULL NUMBER
 AUDIT$                                             VARCHAR2(38)
 DEFROLE                                   NOT NULL NUMBER
 DEFGRP#                                            NUMBER
 DEFGRP_SEQ#                                        NUMBER
 ASTATUS                                   NOT NULL NUMBER
 LCOUNT                                    NOT NULL NUMBER
 DEFSCHCLASS                                        VARCHAR2(30)
 EXT_USERNAME                                       VARCHAR2(4000)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE

SQL> select name,password from user$ where name in('XFF','CHF');

NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            F3CF2F0CB35CB6CA
XFF                            1B60F4BFF1DAB500

SQL> alter user xff identified by values 'F3CF2F0CB35CB6CA';

User altered.

SQL> select name,password from user$ where name in('XFF','CHF');

NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            F3CF2F0CB35CB6CA
XFF                            F3CF2F0CB35CB6CA

SQL> conn xff/xifenfei
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝


Warning: You are no longer connected to ORACLE.
SQL> conn chf/xifenfei
Connected.

SQL> conn / as sysdba
Connected.
SQL> alter user xff identified by values '1B60F4BFF1DAB500';

User altered.

SQL> conn xff/xifenfei
Connected.

注：这个实验使用11g证明，其实10g也是同样的结果；在oracle 9i中可以通过修改password的values值实现登录

二、使用orabf破解数据库密码
1、修改数据库密码

SQL> conn / as sysdba
Connected.
SQL> alter user xff identified by xff01;

User altered.

SQL> alter user chf identified by chf00; 

User altered.

SQL> select name,password from user$ where name in('XFF','CHF');

NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            05BD6F8AB28BD8CA
XFF                            A51B3879056B3DDD

2、orabf使用

C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------

usage: orabf [hash]:[username] [options]

options:
-c [num]  complexity: a number in [1..6] or a filename
   -      read words from stdin
   [file] read words from file
   1      numbers
   2      alpha
   3      alphanum
   4      standard oracle (alpha)(alpha,num,_,#,$)... (default)
   5      entire keyspace (' '..'~')
   6      custom (charset read from first line of file: charset.orabf)
-m [num]  max pwd len: must be in the interval [1..14] (default: 14)
-n [num]  min pwd len: must be in the interval [1..14] (default: 1)
-r        resume: tries to resume a previous session


C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf A51B3879056B3DDD:XFF

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done

Starting brute force session using charset:
#$0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_

press 'q' to quit. any other key to see status

current password: D9X50
9229361 passwords tried. elapsed time 00:00:13. t/s:697938

current password: HI0QJ
18967617 passwords tried. elapsed time 00:00:27. t/s:698403

current password: OB#QD
34743632 passwords tried. elapsed time 00:00:49. t/s:698844

password found: XFF:XFF01

55826385 passwords tried. elapsed time 00:01:19. t/s:704047


C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf 05BD6F8AB28BD8CA:CHF -c 3 -n 4 -m 6

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done

Starting brute force session using charset:
0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ

press 'q' to quit. any other key to see status

password found: CHF:CHF00

22647601 passwords tried. elapsed time 00:00:31. t/s:719113

说明：-c 6不能正常运行，不清楚是不是因为我的win 7系统原因导致

三、使用ops_sse2破解数据库密码
1、sys用户的password

SQL> select password from user$ where name='SYS';

PASSWORD
------------------------------
18698BFD1A045BCC

2、ops_sse2使用

C:\Users\XIFENFEI\Downloads\ops_SIMD_win32>ops_sse2
Oracle passwords (DES) solver 0.3 (SSE2) -- Dennis Yurichev <dennis@conus.info>
Compiled @ Apr  5 2011 12:13:15
Demo version, supporting only SYS usernames.
Usage:

  ops_sse2.exe --hashlist=filename.txt
    [--min=min_password_length] [--max=max_password_length]
    [--first_symbol_charset=characters] [--charset=characters]
    [--results=filename.txt]

hashlist file format:
username:hash:comment_or_SID

By default, results are dumped to stdout.
This can be changed by setting --results option

Default values:
  min_password_length=1
  max_password_length=8
  first_symbol_charset=ABCDEFGHIJKLMNOPQRSTUVWXYZ
  charset=ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789#$_

#ops_file.txt内容
SYS:18698BFD1A045BCC:xff

C:\Users\XIFENFEI\Downloads\ops_SIMD_win32>ops_sse2 --hashlist=ops_file.txt --min=6  --charset=CDEFNHITX
Oracle passwords (DES) solver 0.3 (SSE2) -- Dennis Yurichev <dennis@conus.info>
Compiled @ Apr  5 2011 12:13:15
Demo version, supporting only SYS usernames.
username=SYS: 1 unsolved hash(es) left
Checking 6-symbol passwords for username SYS
overall progress=  0%
username=SYS: 1 unsolved hash(es) left
Checking 7-symbol passwords for username SYS
overall progress= 98% / time remaining:
time elapsed: 12s, ~ 1160449 passwords/hashes per second
username=SYS: 1 unsolved hash(es) left
Checking 8-symbol passwords for username SYS
overall progress= 91% / time remaining: 8s
time elapsed: 1m31s, ~ 1248875 passwords/hashes per second
SYS/xff: Found password: XIFENFEI
SYS:XIFENFEI:xff

说明：Demo version只能使用于破解sys用户的密码，而且秘密长度不能超过8.

综合说明的试验，虽然都有缺陷，但是相对而已还是orabf破解更加的给力点
orabf-v0.7.6下载
ops_SIMD_win32
ops_SIMD_linux86

参考：忘记oracle 用户密码怎么办？

ORA-600 [12235]

Posted on by

今天早上例行对各个区域数据库服务器进行检查，发现内蒙古电信的数据库服务器出现ORA-600 [12235]错误
一、错误现象

alert_txzldb.log日志：
Sun Nov  6 09:34:57 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_8253.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
txzldb_ora_8253.trc内容：
Oracle program name: oracle@database.localdomain
*** 2011-11-06 09:34:57.530
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp()+269         call     ksedst()+0           0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksfdmp()+14          call     ksedmp()+0           3 ? BFFFECA0 ? 98584A4 ?
                                                   AD58F60 ? 3 ? A4B929C ?
kgeriv()+188         call     ksfdmp()+0           AD58F60 ? 3 ?
kgesiv()+113         call     kgeriv()+0           AD58F60 ? 0 ? 2FCB ? 0 ?
                                                   BFFFED0C ?
ksesic0()+39         call     kgesiv()+0           AD58F60 ? 0 ? 2FCB ? 0 ?
                                                   BFFFED0C ? 2FCB ? 0 ?
                                                   BFFFED0C ?
opirip()+519         call     ksesic0()+0          2FCB ? AD5903C ? BFFFF6AC ?
                                                   FFFFFFFF ? BFFFF814 ? 1 ?
opidrv()+462         call     opirip()+0           32 ? 0 ? 0 ?
sou2o()+25           call     opidrv()+0           32 ? 0 ? 0 ?
main()+355           call     sou2o()+0            BFFFF814 ? 32 ? 0 ? 0 ?
                                                   BFFFF840 ? 0 ?
__libc_start_main()  call     main()+0             1 ? BFFFF894 ? BFFFF89C ?
+161                                               96DFD4 ? 1 ? 8208E40 ?
 
--------------------- Binary Stack Dump ---------------------
 
========== FRAME [1] (ksedmp()+269 -> ksedst()+0) ==========
Dump of memory from 0xBFFFEB64 to 0xBFFFEC64
BFFFEB60          BFFFEC64 0820B6F8 00000000      [d..... .....]
BFFFEB70 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
………………

二、数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

三、mos信息
根据mos的建议，这个错误可以忽略，不用过多关注

ORA-600 [12235] "Oracle process has no purpose in life !" [ID 33174.1]
ERROR:            
  ORA-600 [12235] [a] [b] 1[/c] [d] [e]

VERSIONS:
  versions 7.0 to 9.2

DESCRIPTION:      
  This error shows up when Oracle detects an Oracle defunct process.
  When an Oracle process starts up, it reads data from the SGA that defines
  what type of process it should become.
  If the process does not locate any valid customization data, it reports 
  ORA-600 [12235] and exits.
  On a heavily loaded system, ORA-600 [12235] may be a symptom that the server 
  process was too slow in starting. That is, the process that initiated the 
  new server may timeout waiting for the new process to start and abandon the 
  new server request.  In the new server process it is possible that the new 
  server reaches the code to customize its operation before the os request to 
  kill the process is actioned.  The messages "ksbsrv: No startup 
  acknowledgement from forked process ..." and "Timed out trying to start 
  shared server ..." may be reported to trace and alert files.

SUGGESTIONS:
  Ignore the error.
  One of the most common reasons for this error to be reported is that 
  someone typed 'oracle' manually at the OS prompt.
  If this error is reported regularly and appears not to be explained
  by comments made in this note, contact Oracle Support Services.

四、错误重现
根据mos中的讲述，在系统级别执行oracle，也会出现该错误提示，使用oracle重现结果

[oracle@database ~]$ oracle
[oracle@database ~]$ oracle
[oracle@database ~]$ tail  $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Mon Nov  7 11:10:25 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_27108.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
Mon Nov  7 11:10:27 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_1281.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []