APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
*** Checked for relevance on 05-Apr-2016 ***

PURPOSE

This troubleshooting guide is for resolving ORA-1548 error reported while dropping UNDO tablespace.

本疑难解答指南用于解决删除 UNDO tablespace 时报告的ORA-1548错误

The main purpose is to provide the Database Administrators an understanding of the issue and steps to resolve the same. It also provides you the diagnostic information to be collected before raising a Service Request with Oracle Support.

主要目的是使数据库管理员了解该问题及其解决步骤。它还向您提供在使用 Oracle Support 提出服务请求之前要收集的诊断信息

TROUBLESHOOTING STEPS

What is ORA-1548

ORA-1548 is the error reported when you try to drop a tablespace that contains active rollback segments ie, the segments which will be required for a rollback or when a dead transaction is present which requires a transaction recovery. The tablespace can be dropped only after clearing the active (or dead) transaction accessing the Undo Segment. The issue could also happen if the transaction cannot be rolled back due to various reasons like Undo datafile is offline or not accessible, Undo requires recovery and so on.

当您尝试删除包含活动回滚段的表空间（即，回滚所需的段或存在需要事务恢复的无效事务）时，ORA-1548是报告的错误。仅在清除活动（或无效）事务以访问 Undo Segment 之后，才能删除表空间。如果由于各种原因而无法回滚事务，例如Undo数据文件处于offline状态或不可访问，Undo需要恢复等，也会发生此问题

You can check for the Undo Segment in use, using the query:  您可以使用以下查询来检查正在使用的Undo Segment

select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

If the above query returns 'ONLINE' or 'PARTLY AVAILABLE', it means that there are still pending transaction entries in the rollback. You must wait until the corresponding transactions are either committed or rolled back. Repeat the above query again until the status becomes 'OFFLINE'. The status means that a dead transaction exists that still needs to be rolled back. There is no way we can simply forget about it, it would leave logical corruptions in the database.</span></span>

如果以上查询返回  'ONLINE' or 'PARTLY AVAILABLE' ，则表示回滚中仍存在未决的事务条目。您必须等待，直到相应的事务被提交或回滚。再次重复上述查询，直到状态变为'OFFLINE'。该状态表示存在无效事务，仍然需要回滚。我们没有办法简单地忘记它，它会在数据库中留下逻辑损坏

A 'NEEDS RECOVERY' status means that there are problems with the rollback. See Case 3 in this document

'NEEDS RECOVERY' 状态表示回滚存在问题。请参阅本案例中的Case 3

Case 1 : An active or dead transaction present in the Undo Segment.  Undo Segment 中存在active或无效事务

1). Check the status of the undo segments that are not Online or Offline:  检查不是Online or Offline的undo segments的状态

Select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;

2. To check for dead transactions:  要检查无效事务

 select KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA, KTUXECFL, KTUXESIZ

 from x$ktuxe

 where KTUXESTA='ACTIVE' and KTUXECFL='DEAD';

Once the transaction is recovered, you can try dropping the Undo tablespace  恢复事务后，您可以尝试删除Undo tablespace

Case 2: Undo segment held by a dead distributed transaction   无效的分散事务持有Undo Segment

In this case , the issue is caused by dead distributed transactions. We have to clear the dead distributed transactions before trying to drop the Undo tablespace.

在这种情况下，问题是由无效的分布式事务引起的。在尝试删除 Undo tablespace 之前，我们必须清除无效的分布式事务

To check for any active transactions on a rollback segment:  要检查回滚段上的任何活动事务，请执行以下操作

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */

KTUXESTA Status,

KTUXECFL Flags

FROM x$ktuxe

WHERE ktuxesta!='INACTIVE';

b. You can use the following queries to check for in doubt transactions: 您可以使用以下查询来检查有疑问的事务

SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;

SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;

At this point you have to cleanup Distributed transaction without corresponding dba_2pc entries

此时，您必须清除没有相应的dba_2pc条目的分布式事务

Please Contact Oracle Support to clean the stranded dba_2pc transactions

请联系 Oracle Support 以清除搁浅的dba_2pc事务

Once these transactions are cleared, you should be able to drop the undo tablespace without encountering the ORA-1548 error.

清除这些事务后，您应该能够删除undo tablespace而不会遇到ORA-1548错误

Case 3: When Undo Segment requires recovery  Undo Segment 需要恢复时

Check the status of the undo segments 检查undo segments的状态

Select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;

Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery (Doc ID 1295294.1)

Case 4: If Event 10513 is set.  如果设置了事件10513

Check the alert log file if the event 10513 is set.  如果设置了事件10513，请检查alert log文件

event="10513 trace name context forever, level 2"

This can be set dynamically as: 可以动态设置为

SQL> oradebug setorapid <SMON's Oracle PID>

SQL> oradebug event 10513 trace name context forever, level 2

SQL> oradebug setorapid <SMON's Oracle PID>

SQL> oradebug event 10513 trace name context off

Once the event is reset, you can monitor the trasaction recovery progress using:

重置事件后，可以使用以下方法监视事务恢复进度

select KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA, KTUXECFL, KTUXESIZ

 from x$ktuxe

 where KTUXESTA='ACTIVE' and KTUXECFL='DEAD';

You can monitor the recovery using the column KTUXESIZ . This will decrease as the recovery progresses.

您可以使用列KTUXESIZ监视恢复。随着恢复的进行，这将减少

Case 5: When FlashBack Data Archiver (FBDA) is enabled  启用FlashBack Data Archiver（FBDA）时

Undo tablespace drop operation is blocked since some transaction is marked to have to be archived for flashback.

因为某些事务被标记为必须进行闪回归档，所以Undo tablespace删除操作被阻止

1. Check current_scn from v$database after updating tracked table  更新跟踪表后，从v$database中检查current_scn

For step 3, FBDA will update sys_fba_barrierscn after a while. If you don't want to wait, fastest way is restarting instance.

对于步骤3，FBDA将在一段时间后更新sys_fba_barrierscn。如果您不想等待，最快的方法是重新启动实例

Case 6: FlashBack Data Archiver (FBDA) was enabled in the past.  过去已启用FlashBack Data Archiver（FBDA）

In this case also, the Undo marked to have to be archived for flashback. The extents used by transactions with the FBA bit "on" are considered "active" until the archiver has mined the undo and recorded the history for the flashback archive enabled tables as these extents cannot be recycled until the history is recorded.

同样在这种情况下，撤消标记为必须进行闪回存档。FBA位为“ on”的事务所使用的扩展盘区被认为是“活动的”，直到存档器已发现撤消并记录了启用了闪回存档的表的历史记录为止，因为这些扩展盘区在记录历史记录之前无法回收

If the instance parameter "_disable_flashback_archiver" is set, the FBDA processs wont be starting and hence wont process these undo extents.

如果设置了实例参数“ _disable_flashback_archiver”，则FBDA进程将不会启动，因此将不会处理这些撤消范围。

To verify:  验证

SQL> SELECT a.ksppinm "Parameter",

            b.ksppstvl "Session Value",

            c.ksppstvl "Instance Value"

     FROM sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

     WHERE a.indx = b.indx

     AND a.indx = c.indx

     AND a.ksppinm LIKE '%flashback%';

This must be reset and the instance must be restarted.   必须将其重置，并且必须重新启动实例

SQL> ALTER SYSTEM SET "_disable_flashback_archiver"=0 SCOPE=SPFILE;