共計 7813 個字符，預計需要花費 20 分鐘才能閱讀完成。
這篇文章給大家分享的是有關如何刪除 UNDO 表空間并處理 ORA-01548 問題的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。
準備工作
1. 找到所有的表空間文件，這里主要是找到計劃刪除 undo 表空間
select * from dba_data_files;2. 新建一個 undo 表空間，表空間名字和物理文件名字都換一個，自拓展的參數自行調整
create undo tablespace UNDOTBS3 datafile /u01/app/oracle/oradata/cams/undotbs03.dbf size 100M autoextend on next 100M maxsize unlimited;3. 修改系統的 undo 表空間為新創建的 undo 表空間
alter system set undo_tablespace=UNDOTBS3;4. 查看當前的 UNDO 表空間
SYS@cams show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3問題說明
5. 此時直接刪除原 undo 表空間和表空間文件，遇到 ORA-01548 的錯誤。
SYS@cams drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
ERROR at line 1:
ORA-01548: active rollback segment _SYSSMU9_1650507775$ found, terminate
dropping tablespace實操方案
6. 因為本次操作的環境是測試環境，所以選擇了一個最簡單快速的方法，表空間 offline+drop。
如果擔心數據損壞，建議做一個 expdp/exp 邏輯備份。
SYS@cams alter tablespace UNDOTBS1 offline;
Tablespace altered.
SYS@cams drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.注：如果不著急的話，可以將數據庫的默認 undo 表空間修改之后運行一段時間，查看 dba_rollback_segs 表，等到原 undo 表空間中只剩下“offline”狀態的 segment 時（如果有事務未提交，可以執行 commit force 命令強制提交），可以順利刪除。
7. 完事檢查是否已經成功刪除
SYS@cams select count(*) from dba_tablespaces where tablespace_name= UNDOTBS1
COUNT(*)
----------
0參考方案
MOS 中對 ORA-01548 問題進行分類討論，感興趣的讀者可以仔細閱讀
Master Note: Troubleshooting ORA-1548 error (文檔 ID 1577988.1) 轉到底部
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;
感謝各位的閱讀！關于“如何刪除 UNDO 表空間并處理 ORA-01548 問題”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
