ORA-00054 和ORA-08104

最新推荐文章于 2023-04-12 17:21:57 发布

文档搬运工

最新推荐文章于 2023-04-12 17:21:57 发布

阅读量299

点赞数

分类专栏： 11gR2

本文链接：https://blog.csdn.net/xxzhaobb/article/details/115758262

版权

11gR2 专栏收录该内容

116 篇文章 2 订阅

订阅专栏

在一个繁忙的业务系统中，创建索引的时候，会提示ORA-00054告警。加上online语句即可。关于online语句，官方有以下解释：

参考文档：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE

ONLINE

Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.

Restrictions on Online Index Building

Online index building is subject to the following restrictions:

Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, then Oracle Database returns an error.
You can specify ONLINE for a bitmap index or a cluster index as long as COMPATIBLE is set to 10 or higher.
You cannot specify ONLINE for a conventional index on a UROWID column.
For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

See Also:

Oracle Database Concepts for a description of online index building and rebuilding

官方的解释，指定了online操作，是在创建索引的时候，允许进行dml操作，这就比较适合一个繁忙的业务系统上创建索引。当然，online也是有一些限制的。（具体可以看上面的内容）

关于Online，在官方MOS ORA-54 While Alter Index Unusable Online (Doc ID 2437540.1) 上有一些解释

The error is expected if DML operations for the underlying object are not completed within the specified DDL_LOCK_TIMEOUT value:

ONLINE Clause is a new enhancement introduced in 12C to make index UNUSABLE, ALTER INDEX UNUSABLE ONLINE will not impact ongoing DML. DDL operations can wait for a user configurable time period (DDL_LOCK_TIMEOUT), if underlying resource is busy, and is supported at system and session levels.

当一个繁忙的业务系统，通过online的方式创建一个索引，但是因为其他原因，会话中断了（xshell或者securecrt被断开了）。这个时候，再去重新创建索引，或者删除索引的时候，会提示ORA-08104告警。

出现这个问题，有两种方式来处理

方式1 ：继续等待，直到索引创建完毕（在实际生产中碰到过2次，环境为RDBMS 11.2.0.3，测试环境中测试RDBMS12.2.0.1，中断后均会继续创建索引）

方式2：使用dbms_repair的ONLINE_INDEX_CLEAN 函数来处理（记得之前在上产上RDBMS11.2.0.4上处理过一次成功了，但是在RDBMS11.2.0.3上没有成功，最后是等待索引自己建立完毕）

在官方MOS How to Cleanup and Rebuild an Interrupted Online Index Rebuild - ORA-8104 , ORA-8106 (Doc ID 272735.1) 上有解决方法：

SQL> conn / as sysdba
SQL> DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN();
END;
/

关于报ORA-08104告警，官方是这样解释的：

Sometimes an online rebuild of an index reports an ORA-8104.
There can be many reasons for that.

For instance in a highly active database with many transactions the likelyhood of uncommitted transactions on a table is high. So if such a table needs to rebuild an index we may see this error
due to uncommitted transactions. In other cases we may see a dead process with an uncommitted transaction holding a lock on the table. This also prevents us from rebuilding the index. This is
all by design of the online rebuild functionality. However, we can't leave a database in this state forever so SMON has been designed to cleanup these cases.

A process could end up dead if it has been killed from the OS with the command kill -9.

If the process was killed while doing an:
ALTER INDEX <ind> REBUILD ONLINE;
then the index could end up in a state where it needs clearing up.

One would think a drop of the index would solve the problem. However,
that will only return an error:ORA-8104 "This index object %s is being online built or rebuilt."

关于这个Package的用法，可以参考官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_REPAIR.html#GUID-05BE0402-6B4F-479E-8B91-3E3031E0CBF5

在多年前的时候，处理过类似的问题，是在RDBMS 11.2.0.4下，使用dbms_repair进行成功处理了。但是前段时间，在RDBMS11.2.0.3下，处理不了。直到所以自己建立完毕（此时xshell已经断开了）。

今天特意在RDBMS12.2.0.1下测试了下，发现创建索引命令发出后，中断xshell，索引的创建在后台的确是可以继续进行。

-- 创建测试表，插入数据。

create table t tablespace users as select * from dba_objects;
insert into t select * from t; -- 执行多次 
commit;

SYS@test>create table t tablespace users as select * from dba_objects;

Table created.

SYS@test>insert into t select * from t;

72929 rows created.

SYS@test>/

145858 rows created.

SYS@test>/

291716 rows created.

SYS@test>/

583432 rows created.

SYS@test>/

1166864 rows created.

SYS@test>/

2333728 rows created.

SYS@test>/

4667456 rows created.

SYS@test>commit;

Commit complete.

SYS@test>

-- 创建索引，然后中断远程工具

create index idx_t_ojbid on t (object_id) tablespace users online;

SYS@test>create index idx_t_ojbid on t (object_id) tablespace users online;

--然后再在另一个会话中创建索引，直接提示，索引已经存在了（这个时候，drop或者rebuild是同样的告警错误）

SYS@test>create index idx_t_ojbid on t (object_id) tablespace users online;
create index idx_t_ojbid on t (object_id) tablespace users online
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SYS@test>

-- 经过几分钟的等待（因为数据量不是太大，基本上没有业务，所以几分钟就创建完毕了。如果业务很繁忙，可能会很久后创建完成），发现索引创建完毕（如果索引没有创建完毕，会看到索引的状态是valid，但是看不到last_analyzed值）。

SYS@test>select status,last_analyzed from dba_indexes where index_name='IDX_T_OJBID';

STATUS   LAST_ANALYZED
-------- -------------------
VALID    2021-04-16 11:14:01

SYS@test>

当然，还存在一种情况，就是索引创建过程中，是真真正正的corrupted的了。就需要通过dbms_repair来进行处理了。可能这种中断了xshell这种，不算是彻底的corrupted（也许那种kill session的算是侧彻底底的corrupted,下次有机会的时候，kill掉建立索引的session测试下）。

end