Master Note: Troubleshooting ORA-1548 error (Doc ID 1577988.1)

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') ;

If there are segments with status 'Partly Available' , it means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. In case of a dead transaction you will have to wait till the recovery is done.

如果存在状态为 'Partly Available' 的段，则表示它们仍具有未决的活动事务，并且您不能删除表空间，直到事务被提交或回滚。如果事务失败，则必须等到恢复完成

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

 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监视恢复。随着恢复的进行，这将减少

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';

If the status shows as 'Prepared', It implies this is a distributed transaction , which should be committed or rolled back.

如果状态显示为 'Prepared' ，则表示这是一个分布式事务，应提交或回滚

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') ;

If the Undo Segment status shows as 'Needs Recovery' Please refer the document to recover the Undo segment

如果 Undo Segment 状态显示为 'Needs Recovery' ，请参考文档以恢复撤消分段

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

If event 10513 is set, transaction recovery from SMON will be disabled and hence the Undo Segment status wont change. So, if the event is set, restart the database after removing the event and monitor the recovery of the dead transactions.

如果设置了事件10513，则将从SMON进行的事务恢复将被禁用，因此 Undo Segment 状态将不会更改。因此，如果设置了事件，请在删除事件后重新启动数据库并监视无效事务的恢复

Remove the event from the init.ora and restart the instance. If it is set dynamically you can turn it off as :  

从init.ora中的删除事件，并重新启动实例。如果动态设置，则可以将其关闭

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

2. Do not change undo_tablespace parameter  不要更改undo_tablespace参数

3. select barrierscn from sys_fba_barrierscn;  从sys_fba_barrierscn中选择barrierscn

wait until this scn is greater than scn at step 1  等到此scn大于步骤1中的scn

4. After barrierscn become greater, wait one more minute so that FBDA can update transaction table extension

在barrierscn变大之后，请等待一分钟，以便 FBDA 可以更新事务表扩展

5. Then change undo_tablespace, and drop old one.  然后更改undo_tablespace，并删除旧的

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;

and restart the instance. This will return the parameter to its default value.

并重新启动实例。这会将参数恢复为其默认值