Shulou(Shulou.com)05/31 Report--

This article is about how to delete UNDO tablespaces and deal with ORA-01548 problems. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preparatory work

1. Find all the tablespace files, here is to find the plan to delete the undo tablespace

Select * from dba_data_files

two。 Create a new undo tablespace, change the tablespace name and physical file name, and adjust the self-expanding parameters.

Create undo tablespace UNDOTBS3 datafile'/ u01 size size autoextend on next 100m oradata maxsize unlimited

3. Modify the undo table space of the system to the newly created undo table space

Alter system set undo_tablespace=UNDOTBS3

4. View the current UNDO tablespace

SYS@cams > show parameter undo; NAME TYPE VALUE---undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS3 problem description

5. At this point, delete the original undo tablespace and tablespace files directly, and encounter the error of 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, terminatedropping tablespace implementation solution

6. Because the environment for this operation is a test environment, I chose the simplest and fastest method, tablespace offline+drop.

If you are worried about data corruption, it is recommended to do an expdp/exp logical backup.

SYS@cams > alter tablespace UNDOTBS1 offline; Tablespace altered. SYS@cams > drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped.

Note: if you are not in a hurry, you can modify the default undo table space of the database and run for a period of time to view the dba_rollback_segs table. When there is only segment in the state of "offline" in the original undo table space (if there is a transaction that has not been committed, you can execute the commit force command to force it to commit), and it can be deleted smoothly.

7. Check whether it has been deleted successfully.

SYS@cams > select count (*) from dba_tablespaces where tablespace_name='UNDOTBS1' COUNT (*)-0 reference scheme

There is a classified discussion on ORA-01548 in MOS. Interested readers can read it carefully.

Master Note: Troubleshooting ORA-1548 error (document ID 1577988.1) goes to the bottom

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. Diagnostic Information to be Collected While Raising a Service Request.

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 STEPSWhat 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.

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 is responsible for 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

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.

Thank you for reading! This is the end of the article on "how to delete UNDO tablespaces and deal with ORA-01548 problems". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.