![Chanel [K]](http://www.dbform.com/wp-content/chanelk.png)
Archive for the ‘Oracle RDBMS’ Category
“Alter Database Recover” Vs “Recover Database”
操作系统版本：Solaris 5.8 Sparc 64bit
数据库版本：Oracle 9.2.0.1
一直以为”alter database recover”和”recover database”这两个命令除了前者是SQL命令后者是SQL*PLUS命令之外，其它都是相同的，实际上却有所出入。
在所有需要的归档日志都存在的相同前提下。alter database recover命令无法继续。
SQL> ALTER DATABASE recover USING backup controlfile until cancel; ALTER DATABASE recover USING backup controlfile until cancel * ERROR at line 1: ORA-00279: CHANGE 10402260063 generated at 10/31/2009 01:30:52 needed FOR thread 1 ORA-00289: suggestion : /xf_arch1/log9565_1.arc ORA-00280: CHANGE 10402260063 FOR thread 1 IS IN sequence #9565
但是recover database命令却可以让DBA继续输入log file的名称。
SQL> recover DATABASE USING backup controlfile until cancel; ORA-00279: CHANGE 10402260063 generated at 10/31/2009 01:30:52 needed FOR thread 1 ORA-00289: suggestion : /xf_arch1/log9565_1.arc ORA-00280: CHANGE 10402260063 FOR thread 1 IS IN sequence #9565 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /xf_arch1/log9565_1.arc ORA-00279: CHANGE 10402260063 generated at 10/30/2009 23:24:14 needed FOR thread 2 ORA-00289: suggestion : /xf_arch1/log10763_2.arc ORA-00280: CHANGE 10402260063 FOR thread 2 IS IN sequence #10763 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /xf_arch1/log10763_2.arc ORA-00279: CHANGE 10402782605 generated at 10/31/2009 06:21:30 needed FOR thread 1 ORA-00289: suggestion : /xf_arch1/log9566_1.arc ORA-00280: CHANGE 10402782605 FOR thread 1 IS IN sequence #9566 ORA-00278: log file '/xf_arch1/log9565_1.arc' no longer needed FOR this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. SQL> SQL> ALTER DATABASE open resetlogs; DATABASE altered.
在上例中如果要想alter database recover成功，需要如下的语法：
ALTER DATABASE RECOVER automatic DATABASE until CHANGE 10404040058 USING backup controlfile;
或者：
ALTER DATABASE recover logfile '/xf_arch1/log9565_1.arc' USING backup controlfile;
a) use ALTER DATABASE if you want to do the recoverying step by step, manually — issueing each and every single solitary command to recover the database (apply logfiles).
b) use the SQLPLUS ‘recover’ command to have sqlplus automate the steps in a) for you.
更加详细的描述可以参看AskTOM的解释。
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
