![Chanel [K]](http://www.dbform.com/wp-content/chanelk.png)
Archive for October, 2009
How to resolve ORA-15025 when creating ASM diskgroup on Solaris
操作系统版本：Solaris10 Sparc 64bit
数据库版本：Oracle 10.2.0.4
在创建ASM Diskgroup的时候出现如下图报错。
检查当前系统裸设备的设置。
partition> p Current partition table (original): Total disk cylinders available: 53154 + 2 (reserved cylinders) Part Tag Flag Cylinders Size Blocks 0 root wm 0 0 (0/0/0) 0 1 swap wu 0 0 (0/0/0) 0 2 backup wu 0 - 53153 1.99TB (53154/0/0) 4269595050 3 unassigned wm 0 0 (0/0/0) 0 4 unassigned wm 0 0 (0/0/0) 0 5 unassigned wm 0 0 (0/0/0) 0 6 usr wm 0 - 53153 1.99TB (53154/0/0) 4269595050 7 unassigned wm 0 0 (0/0/0) 0
注意到被使用的裸设备d0s6的扇区划分是从0开始的，这在Solaris操作系统中将会导致Oracle无法读取该裸设备，因此引发了上面的错误。
解决方法是分配出一小部分空间给其它分区。比如分配100M给d0s3。
partition> p Current partition table (unnamed): Total disk cylinders available: 53154 + 2 (reserved cylinders) Part Tag Flag Cylinders Size Blocks 0 root wm 0 0 (0/0/0) 0 1 swap wu 0 0 (0/0/0) 0 2 backup wu 0 - 53153 1.99TB (53154/0/0) 4269595050 3 unassigned wm 0 - 2 117.66MB (3/0/0) 240975 4 unassigned wm 0 0 (0/0/0) 0 5 unassigned wm 0 0 (0/0/0) 0 6 usr wm 3 - 26112 1000.06GB (26110/0/0) 2097285750 7 unassigned wm 0 0 (0/0/0)
再次重新创建ASM磁盘组，成功。
How to resolve ORA-24005 when drop tablespace
SQL> DROP tablespace EXAMPLE INCLUDING CONTENTS; DROP tablespace EXAMPLE INCLUDING CONTENTS * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: must USE DBMS_AQADM.DROP_QUEUE_TABLE TO DROP queue TABLES
EXAMPLE表空间是Oracle的示例表空间，通常保存着诸如OE, SH, IX, HR等示例用户的表数据，在尝试删除这个表空间的时候遇到上述的错误。
实际上错误是由于IX用户下的高级队列表对象引起的，高级队列表对象无法使用常规的drop命令删除，无论是删除包含AQ的表空间还是用户，都会报ORA-24005错误。
SQL> DROP user IX cascade; DROP user IX cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: must USE DBMS_AQADM.DROP_QUEUE_TABLE TO DROP queue TABLES SQL> SELECT table_name,tablespace_name FROM dba_tables WHERE owner='IX'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SYS_IOT_OVER_53828 EXAMPLE AQ$_ORDERS_QUEUETABLE_S EXAMPLE SYS_IOT_OVER_53842 EXAMPLE AQ$_STREAMS_QUEUE_TABLE_S EXAMPLE ORDERS_QUEUETABLE EXAMPLE STREAMS_QUEUE_TABLE EXAMPLE AQ$_STREAMS_QUEUE_TABLE_T AQ$_STREAMS_QUEUE_TABLE_I AQ$_STREAMS_QUEUE_TABLE_H AQ$_STREAMS_QUEUE_TABLE_G AQ$_STREAMS_QUEUE_TABLE_C AQ$_ORDERS_QUEUETABLE_T AQ$_ORDERS_QUEUETABLE_I AQ$_ORDERS_QUEUETABLE_H AQ$_ORDERS_QUEUETABLE_G
常规的方法是按照提示，使用DBMS_AQADM.DROP_QUEUE_TABLE存储过程来完成。
SQL> conn ix/ix SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('ORDERS_QUEUETABLE',true); SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('STREAMS_QUEUE_TABLE',true);
在某些时候运行完上述存储过程，用户下仍然还会存在一些AQ$表，此时仍然无法正常删除用户或者表空间。
SQL> SELECT table_name,tablespace_name FROM dba_tables WHERE owner='IX'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ AQ$_STREAMS_QUEUE_TABLE_C
此时需要通过10851 event来进行手工删除，操作顺序如下，更加详细的步骤可以参看Metalink Note 203225.1。
SQL> conn ix/ix SQL> SELECT object_name, object_type FROM user_objects WHERE object_name LIKE '%STREAMS_QUEUE_TABLE%'; OBJECT_NAME OBJECT_TYPE ---------------------------------------- ------------------- AQ$_STREAMS_QUEUE_TABLE_C TABLE AQ$_STREAMS_QUEUE_TABLE_Y INDEX SQL> ALTER SESSION SET EVENTS '10851 trace name context forever, level 2'; Session altered SQL> DROP TABLE AQ$_STREAMS_QUEUE_TABLE_C cascade constraints; TABLE dropped SQL> SELECT object_name, object_type FROM user_objects WHERE object_name LIKE '%STREAMS_QUEUE_TABLE%'; OBJECT_NAME OBJECT_TYPE ---------------------------------------- ------------------- SQL> DROP user ix cascade; User dropped SQL> DROP tablespace example; Tablespace dropped
