Oracle创建新undo表空间最佳实践(包含段检查)

Best practice to create a new undo tablespace.his method includes segment check. Create pfile from spfile to editSQL> Create pfile='/tmp/initsid.ora' from spfile; Shutdown the instance set the following parameters in the pfile /tmp/initsid.ora undo_management = manual event = '10513 trace name context forever, level 2' SQL>>startup restrict pfile='/tmp/initsid.ora' SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';This is critical - we are looking for all undo segments to be offline - System will always be online.If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.If all offline then continue to the next step Create new undo tablespace - exampleSQL>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M; Drop old undo tablespaceSQL>drop tablespace <old undo tablespace> including contents and datafiles; SQL>shutdown immediate;SQL>startup nomount; --> Using your Original spfile. Modify the spfile with the new undo tablespace nameSQL> Alter system set undo_tablespace = '<new tablespace created in step 6>' scope=spfile;. SQL>shutdown immediate;. SQL>startup; --> Using spfile