如何删除UNDO表空间并处理ORA-01548问题

技术如何删除UNDO表空间并处理ORA-01548问题这篇文章给大家分享的是有关如何删除UNDO表空间并处理ORA-01548问题的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。准备工作1.找到

这篇文章给大家分享的是有关如何删除取消表空间并处理ORA-01548问题的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。

准备工作

1.找到所有的表空间文件,这里主要是找到计划删除取消表空间

从dba _ data _ files中选择*;2.新建一个取消表空间,表空间名字和物理文件名字都换一个,自拓展的参数自行调整

createUndotblaseundotbs 3 DATafile '/u01/app/Oracle/ora data/cams/Undotbs 03。DBF的size100 matextendonnecxt 100 maxSizeUnlimited;3.修改系统的取消表空间为新创建的取消表空间

altersystemsetundo _ table space=UNDOTBS3;4.查看当前的取消表空间

SYS @ camsshowparameterundo

NAMETYPEVALUE

-

undo _ managementstringAUTO

undo_retentioninteger900

undo _ tablespacestringdotbs 3

问题说明

5 .此时直接删除原取消表空间和表空间文件,遇到ORA-01548的错误。

系统@包含内容和数据文件;

DropTableSpaceUndotbs 1包含内容和数据文件

*

错误线路1:

ORA-01548:活动回滚区域' _ syssmu 9 _ 1650507775 $ '找到,终止

删除表空间

实操方案

6 .因为本次操作的环境是测试环境,所以选择了一个最简单快速的方法,表空间脱机投递。

如果担心数据损坏,建议做一个出口PDP/出口逻辑备份。

SYS @ camsalterablepaceondtbs 1 ffline;

Tablespacealtered已更改。

系统@包含内容和数据文件;

表空间减少了.注:如果不着急的话,可以将数据库的默认undo表空间修改之后运行一段时间,查看dba_rollback_segshttp://,可以顺利删除。

7.完事检查是否已经成功删除

SYS @ cam从DBA _ tablespaceswheretablespace _ name=' UNDOTBS1 '中选择count(*)项

计数(*)

-

0

参考方案

金属氧化物半导体中对ORA-015

48问题进行分类讨论,感兴趣的读者可以仔细阅读

Master Note: Troubleshooting ORA-1548 error (文档 ID 1577988.1) 如何删除UNDO表空间并处理ORA-01548问题转到底部 如何删除UNDO表空间并处理ORA-01548问题

如何删除UNDO表空间并处理ORA-01548问题

In this Document

PurposeTroubleshooting Steps What is ORA-1548  Case 1 : An active or dead transaction present in the Undo Segment. Case 2: Undo segment held by a dead distributed transaction Case 3: When Undo Segment requires recovery Case 4: If Event 10513 is set. Case 5: When FlashBack Data Archiver (FBDA) is enabled Case 6: FlashBack Data Archiver (FBDA) was enabled in the past. <Internal_Only> Diagnostic Information to be Collected While Raising a Service Request. </Internal_Only>


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.1 [Release 10.2 to 12.1]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
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.

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.

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.

You can check for the Undo Segment in use, using the query:

 

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>

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

 Case 1 : An active or dead transaction present in the Undo Segment.

 

1). Check the status of the undo segments that are not Online or Offline:

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.

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.

Once the transaction is recovered, you can try dropping the Undo tablespace

Case 2: Undo segment held by a dead distributed transaction

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.

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.

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

Please Contact Oracle Support to clean the stranded dba_2pc transactions

 

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

 

Case 3: When Undo Segment requires recovery

 

 Check the status of the 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
Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery (
Doc ID 1295294.1
)

Case 4: If Event 10513 is set.

 Check the alert log file if the event 10513 is set.

 

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.

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

  

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.

 

Case 5: When FlashBack Data Archiver (FBDA) is enabled

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

 

 1. Check current_scn from v$database after updating tracked table

 

2. Do not change undo_tablespace parameter

 

3. select barrierscn from sys_fba_barrierscn;

 

wait until this scn is greater than scn at step 1

 

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

 

5. Then change undo_tablespace, and drop old one.

 

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

 

Case 6: FlashBack Data Archiver (FBDA) was enabled in the past.

 

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.

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

 

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.

感谢各位的阅读!关于“如何删除UNDO表空间并处理ORA-01548问题”这篇文章就分享到这里了,希望

内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/77271.html

(0)

相关推荐

  • JavaScript编程语言概述

    技术JavaScript编程语言概述 JavaScript编程语言概述产生背景JavaScript最初由Netscape的Brendan Eich设计,最初将其脚本语言命名为LiveScript,后来N

    礼包 2021年11月22日
  • 深蓝色裤子配什么颜色上衣,深蓝裤子配什么颜色衣服

    技术深蓝色裤子配什么颜色上衣,深蓝裤子配什么颜色衣服1.蓝色牛仔裤比较百搭,最好不要配同色系的上衣,感觉那样搭配显不出个性来,裤子是深蓝色,上衣可以浅色系的,如粉色,米色,白色,灰色,黑色也是可以搭配的 深蓝色裤子配什么

    生活 2021年10月24日
  • 怎样进行Unity中GUI控件的调用

    技术怎样进行Unity中GUI控件的调用怎样进行Unity中GUI控件的调用,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。GUI控件:1.在

    攻略 2021年11月11日
  • 为什么需要日本服务器搭建电商网站

    技术为什么需要日本服务器搭建电商网站越来越多的企业正在考虑以在日本地区开展跨境电商业务,通过搭建一个独立商城网站来提供相应的产品或服务是相对比较有效的方式。它将帮助您提高在日本地区本地市场收益,并获得更多潜在客户和收入。

    礼包 2021年12月24日
  • VSCode如何进行规范配置

    技术VSCode如何进行规范配置这篇文章主要介绍了VSCode如何进行规范配置,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。使用这些配置规范并格式化你的代

    攻略 2021年11月1日
  • Python代码格式化工具pycodestyle怎么使用

    技术Python代码格式化工具pycodestyle怎么使用本篇内容介绍了“Python代码格式化工具pycodestyle怎么使用”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学

    攻略 2021年11月29日