-------altersystemsetundo_tablespaceundotbs01 sidcxldb1scopeboth ;如果指定不存在的tablespace重启报错所以rolling restartalter session set containerXXX1.createundo tablespace undotbs022.altersystemsetundo_tablespaceundotbs01 sidcxldb1scopeboth;drop ---in use drop 不掉可以switch 到CDBclose abort ,open, abort会在open后自动recover 不会影响drop但是很容易忘记切换到PDB dropdrop CDB的undo由于CDB UNDO在使用没多大问题所以其他tablespace name不能叫undo否则被drop掉了。show spparameters undo 可以查看各个instance对应的undo.----- PDB 级 undo 不需要指定的让oracle自动选择undo tbs,可能inst1 使用undotbs2show parameter undo;We can reset the undo_tablespace value usingalter system reset undo_tablespace scopespfile;There is really no need to set undo tablespace at all for PDB . And it is better to let oracle create and manage the undo tablespace.But if customer really wants to set table space in PDB ,We can use the below commandalter system set undo_tablespaceUNDO5 sidinstance1 scopeboth;https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/administering-a-cdb-with-sql-plus.html#GUID-E5218804-4478-4384-B03F-5A2BDECAEA6E-----------前言一、确认对应实例的undo表空间1show parameter undo二、确认对应实例undo的文件位置123selectfile_name, bytes / 1024 / 1024 / 1024fromdba_data_fileswheretablespace_namelikeUNDOTBS%;三、确认回滚段使用情况#如果为空则证明该表空间可以被删除否则要放其他时间处理尤其生产环境下。123456selects.username, u.namefromgv$transactiont, gv$rollstat r, v$rollname u, gv$session swheres.taddr t.addrandt.xidusn r.usnandr.usn u.usnorderbys.username;四、检查undo segment状态12345678selectusn,xacts,status,rssize / 1024 / 1024,hwmsize / 1024 / 1024,shrinksfromv$rollstatorderbyrssize;五、创建新的undo表空间并进行切换1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859SQLcreatepfile/home/oracle/t1.txtfromspfile;ファイルが作成されました。SQLSQLSQLcreateundo tablespace undotbs02 datafileDATAsize10240m autoextendonnext10m;表領域が作成されました。SQLSQLcreateundo tablespace undotbs01 datafileDATAsize10240m autoextendonnext10m;表領域が作成されました。SQLSQLSQLaltersystemsetundo_tablespaceundotbs02 sidcxldb2scopeboth;システムが変更されました。SQLSQLSQL show parameter undoNAMETYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled booleanFALSEundo_management string AUTOundo_retentioninteger10800undo_tablespace string UNDOTBS02SQL##inst_id1SQLSQL show parameter undoNAMETYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled booleanFALSEundo_management string AUTOundo_retentioninteger10800undo_tablespace string UNDOTBS1SQLSQLSQLaltersystemsetundo_tablespaceundotbs01 sidcxldb1scopeboth;システムが変更されました。SQLSQL show parameter undoNAMETYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled booleanFALSEundo_management string AUTOundo_retentioninteger10800undo_tablespace string UNDOTBS01SQL六、等待原undo表空间segment状态变更为offline1234selectt.segment_name, t.tablespace_name, t.segment_id, t.statusfromdba_rollback_segs twheret.tablespace_namein(UNDOTBS1,UNDOTBS2)andt.status !OFFLINE;七、删除原undo表空间以及数据文件123456789SQLdroptablespace UNDOTBS1 including contentsanddatafiles;表領域が削除されました。SQLdroptablespace UNDOTBS2 including contentsanddatafiles;表領域が削除されました。SQLApplies ToAll UsersSummaryNOTE: In the images and/or the document content above, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.In a local undo-enabled RAC environment, the PDB undo tablespace is created with the name UNDO_2.NODE1-- Check local UNDO modealter session set containercdb$root;column PROPERTY_NAME format a40column PROPERTY_VALUE format a30SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LOCAL_UNDO_ENABLED;PROPERTY_NAME PROPERTY_VALUE---------------------------------------- ------------------------------LOCAL_UNDO_ENABLED TRUEshow pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB READ WRITE NO5 PDB1 READ WRITE NO-- Check undo_tablespaceset linesize 200column VALUE format a30select NAME,VALUE,CON_ID from V$SYSTEM_PARAMETER where NAME undo_tablespace;NAME VALUE CON_ID---------------------------------------- ------------------------------ ----------undo_tablespace UNDOTBS1 0undo_tablespace 2undo_tablespace UNDOTBS1 3 ----(*)UNDOTBS1undo_tablespace UNDOTBS1 5 ----(*)UNDOTBS1NODE2show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB READ WRITE NO5 PDB1 READ WRITE NO-- Check undo_tablespaceset linesize 200column VALUE format a30select NAME,VALUE,CON_ID from V$SYSTEM_PARAMETER where NAME undo_tablespace;NAME VALUE CON_ID---------------------------------------- ------------------------------ ----------undo_tablespace UNDOTBS2 0 ----(*)UNDOTBS2undo_tablespace 2undo_tablespace UNDO_2 3 ----(*)UNDO_2undo_tablespace UNDO_2 5 ----(*)UNDO_2SolutionIf you need to change the UNDO tablespace of the PDB on node 2 from UNDO_2, do the following in the target PDB on node2:Check the current UNDO tablespace.-- Connect to and execute the PDB on the target node.alter session set containerpdb_name;show con_name-- Check the current UNDO tablespace.show parameter undo_tablespaceIf there is no UNDO tablespace to change to, create a new UNDO tablespace.CREATE UNDO TABLESPACEnew_UNDO_tablespace_name datafile_path SIZE file_size AUTOEXTEND ON ;example:CREATE UNDO TABLESPACE UNDOTBS2 datafile SIZE 10M AUTOEXTEND ON ;Verify that the undo tablespace that you want to change has been created.SELECT * FROM DBA_DATA_FILES;Change the UNDO tablespace.alter system set undo_tablespace new_UNDO_tablespace_namecontainercurrentsidsid_name scopespfile ;current 可以不要example:alter system set undo_tablespaceUNDOTBS2 containercurrent sidorcl2 scopespfile ;Restart the PDB and confirm that you have switched to the new tablespace.Restart the PDB:shutdown PDB下可以吗还没试。。。startupPlease confirm that you have switched to the new tablespace.See if the segment for the old undo tablespace (UNDO_2) disappears.select dr.tablespace_name, dr.segment_name, vr.status from dba_rollback_segs dr, v$rollstat vr where dr.segment_idvr.usn;Drop the old UNDO tablespace.drop tablespace UNDO_2 including contents and datafiles;Verify that the original undo tablespace has been dropped.SELECT * FROM DBA_DATA_FILES;Attachments :CauseCreated a PDB in a RAC environment with local undo enabled.Normal operation as current product.In a RAC environment with local undo enabled, the UNDO tablespace for the PDB on node 2 is named UNDO_2.Enhancement requests are registered to be created automatically in a more appropriate undo tablespace (UNDOTBS2 in the example above).ENH 32197045 - ENHANCEMENT TO AUTOMATICALLY NAME LOCAL UNDO TABLESPACE INSTEAD OF UNDO_2ENH 32197045 is still in progress and has not been published.---------------CDB PDB 命令一样PDB 如果起不来呢。。。。。。 mount 下改SummaryA new instance of an existing RAC database was created - not using DBCA. The new database instance will not start. Attempting to start the new instance throws ORA-30012 on startup.ORA-30012: undo tablespace UNDOTBS_02 does not exist or of wrong typeSolutionUndo tablespaces in the Oracle RAC database are assigned by specifying a different value for the UNDO_TABLESPACE parameter for each instance in the SPFILE or in individual PFILEs.It is not necessary to shut down the other instances in order to fix this problem. Each instance has a different undo tablespace, so a new one can be created and assigned to the new instance without affecting the the running instances.1.Use CREATE UNDO TABLESPACE statement to create a new undo tablespace for the new instance.For example:CREATE UNDO TABLESPACE undotbs_02 DATAFILE DGNAME1 size 1000M autoextend on;Documentation references for CREATE UNDO TABLESPACE syntax:10gR211gR111gR22. Use ALTER SYSTEM to set UNDO_TABLESPACE to the new tablespace for the new SID.For example:ALTER SYSTEM SET UNDO_TABLESPACE undotbs_02 SCOPEBOTH SIDSID2;The new instance should have SID.UNDO_TABLESPACE set to the name of the new undo tablespace just created. Each of the other instances of the database should keep its undo tablespace as the same as what it was before.3. The new instance should now start without throwing ORA-30012.Note: For RAC, it is NOT necessary to follow the steps in KB117198, which instruct you to set undo management to Manual, as long as at least one other instance is up. This is not the best procedure for RAC, because the undo management has to be the same for all the instances. To use manual undo management, one would have to shut down all the instances of the database and reopen with manual undo mgmt. This is not necessary for RAC because in RAC the new undo tablespace can simply be created and assigned from another, already running instance.Attachments :CauseA new instance of an existing RAC database was created - not using DBCA.The new database instance will not start because a new UNDO tablespace was not created and assigned to it.Each instance in a RAC database must have its own UNDO tablespace, specified in the pfile/spfile.When a new instance is added using DBCA, DBCA should automatically create an UNDO tablespace for the new instance. If a new instance is added manually (not recommended), then the new UNDO tablespace must also be manually created and manually assigned to the new instance. The recommended way to add a new RAC instance is to use DBCA.
UNDO tablespace 切换 19C RAC undo tbs drop掉靠RECO恢复不了，因为需要前镜像
发布时间：2026/5/23 19:56:07
-------altersystemsetundo_tablespaceundotbs01 sidcxldb1scopeboth ;如果指定不存在的tablespace重启报错所以rolling restartalter session set containerXXX1.createundo tablespace undotbs022.altersystemsetundo_tablespaceundotbs01 sidcxldb1scopeboth;drop ---in use drop 不掉可以switch 到CDBclose abort ,open, abort会在open后自动recover 不会影响drop但是很容易忘记切换到PDB dropdrop CDB的undo由于CDB UNDO在使用没多大问题所以其他tablespace name不能叫undo否则被drop掉了。show spparameters undo 可以查看各个instance对应的undo.----- PDB 级 undo 不需要指定的让oracle自动选择undo tbs,可能inst1 使用undotbs2show parameter undo;We can reset the undo_tablespace value usingalter system reset undo_tablespace scopespfile;There is really no need to set undo tablespace at all for PDB . And it is better to let oracle create and manage the undo tablespace.But if customer really wants to set table space in PDB ,We can use the below commandalter system set undo_tablespaceUNDO5 sidinstance1 scopeboth;https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/administering-a-cdb-with-sql-plus.html#GUID-E5218804-4478-4384-B03F-5A2BDECAEA6E-----------前言一、确认对应实例的undo表空间1show parameter undo二、确认对应实例undo的文件位置123selectfile_name, bytes / 1024 / 1024 / 1024fromdba_data_fileswheretablespace_namelikeUNDOTBS%;三、确认回滚段使用情况#如果为空则证明该表空间可以被删除否则要放其他时间处理尤其生产环境下。123456selects.username, u.namefromgv$transactiont, gv$rollstat r, v$rollname u, gv$session swheres.taddr t.addrandt.xidusn r.usnandr.usn u.usnorderbys.username;四、检查undo segment状态12345678selectusn,xacts,status,rssize / 1024 / 1024,hwmsize / 1024 / 1024,shrinksfromv$rollstatorderbyrssize;五、创建新的undo表空间并进行切换1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859SQLcreatepfile/home/oracle/t1.txtfromspfile;ファイルが作成されました。SQLSQLSQLcreateundo tablespace undotbs02 datafileDATAsize10240m autoextendonnext10m;表領域が作成されました。SQLSQLcreateundo tablespace undotbs01 datafileDATAsize10240m autoextendonnext10m;表領域が作成されました。SQLSQLSQLaltersystemsetundo_tablespaceundotbs02 sidcxldb2scopeboth;システムが変更されました。SQLSQLSQL show parameter undoNAMETYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled booleanFALSEundo_management string AUTOundo_retentioninteger10800undo_tablespace string UNDOTBS02SQL##inst_id1SQLSQL show parameter undoNAMETYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled booleanFALSEundo_management string AUTOundo_retentioninteger10800undo_tablespace string UNDOTBS1SQLSQLSQLaltersystemsetundo_tablespaceundotbs01 sidcxldb1scopeboth;システムが変更されました。SQLSQL show parameter undoNAMETYPE VALUE------------------------------------ ----------- ------------------------------temp_undo_enabled booleanFALSEundo_management string AUTOundo_retentioninteger10800undo_tablespace string UNDOTBS01SQL六、等待原undo表空间segment状态变更为offline1234selectt.segment_name, t.tablespace_name, t.segment_id, t.statusfromdba_rollback_segs twheret.tablespace_namein(UNDOTBS1,UNDOTBS2)andt.status !OFFLINE;七、删除原undo表空间以及数据文件123456789SQLdroptablespace UNDOTBS1 including contentsanddatafiles;表領域が削除されました。SQLdroptablespace UNDOTBS2 including contentsanddatafiles;表領域が削除されました。SQLApplies ToAll UsersSummaryNOTE: In the images and/or the document content above, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.In a local undo-enabled RAC environment, the PDB undo tablespace is created with the name UNDO_2.NODE1-- Check local UNDO modealter session set containercdb$root;column PROPERTY_NAME format a40column PROPERTY_VALUE format a30SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LOCAL_UNDO_ENABLED;PROPERTY_NAME PROPERTY_VALUE---------------------------------------- ------------------------------LOCAL_UNDO_ENABLED TRUEshow pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB READ WRITE NO5 PDB1 READ WRITE NO-- Check undo_tablespaceset linesize 200column VALUE format a30select NAME,VALUE,CON_ID from V$SYSTEM_PARAMETER where NAME undo_tablespace;NAME VALUE CON_ID---------------------------------------- ------------------------------ ----------undo_tablespace UNDOTBS1 0undo_tablespace 2undo_tablespace UNDOTBS1 3 ----(*)UNDOTBS1undo_tablespace UNDOTBS1 5 ----(*)UNDOTBS1NODE2show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB READ WRITE NO5 PDB1 READ WRITE NO-- Check undo_tablespaceset linesize 200column VALUE format a30select NAME,VALUE,CON_ID from V$SYSTEM_PARAMETER where NAME undo_tablespace;NAME VALUE CON_ID---------------------------------------- ------------------------------ ----------undo_tablespace UNDOTBS2 0 ----(*)UNDOTBS2undo_tablespace 2undo_tablespace UNDO_2 3 ----(*)UNDO_2undo_tablespace UNDO_2 5 ----(*)UNDO_2SolutionIf you need to change the UNDO tablespace of the PDB on node 2 from UNDO_2, do the following in the target PDB on node2:Check the current UNDO tablespace.-- Connect to and execute the PDB on the target node.alter session set containerpdb_name;show con_name-- Check the current UNDO tablespace.show parameter undo_tablespaceIf there is no UNDO tablespace to change to, create a new UNDO tablespace.CREATE UNDO TABLESPACEnew_UNDO_tablespace_name datafile_path SIZE file_size AUTOEXTEND ON ;example:CREATE UNDO TABLESPACE UNDOTBS2 datafile SIZE 10M AUTOEXTEND ON ;Verify that the undo tablespace that you want to change has been created.SELECT * FROM DBA_DATA_FILES;Change the UNDO tablespace.alter system set undo_tablespace new_UNDO_tablespace_namecontainercurrentsidsid_name scopespfile ;current 可以不要example:alter system set undo_tablespaceUNDOTBS2 containercurrent sidorcl2 scopespfile ;Restart the PDB and confirm that you have switched to the new tablespace.Restart the PDB:shutdown PDB下可以吗还没试。。。startupPlease confirm that you have switched to the new tablespace.See if the segment for the old undo tablespace (UNDO_2) disappears.select dr.tablespace_name, dr.segment_name, vr.status from dba_rollback_segs dr, v$rollstat vr where dr.segment_idvr.usn;Drop the old UNDO tablespace.drop tablespace UNDO_2 including contents and datafiles;Verify that the original undo tablespace has been dropped.SELECT * FROM DBA_DATA_FILES;Attachments :CauseCreated a PDB in a RAC environment with local undo enabled.Normal operation as current product.In a RAC environment with local undo enabled, the UNDO tablespace for the PDB on node 2 is named UNDO_2.Enhancement requests are registered to be created automatically in a more appropriate undo tablespace (UNDOTBS2 in the example above).ENH 32197045 - ENHANCEMENT TO AUTOMATICALLY NAME LOCAL UNDO TABLESPACE INSTEAD OF UNDO_2ENH 32197045 is still in progress and has not been published.---------------CDB PDB 命令一样PDB 如果起不来呢。。。。。。 mount 下改SummaryA new instance of an existing RAC database was created - not using DBCA. The new database instance will not start. Attempting to start the new instance throws ORA-30012 on startup.ORA-30012: undo tablespace UNDOTBS_02 does not exist or of wrong typeSolutionUndo tablespaces in the Oracle RAC database are assigned by specifying a different value for the UNDO_TABLESPACE parameter for each instance in the SPFILE or in individual PFILEs.It is not necessary to shut down the other instances in order to fix this problem. Each instance has a different undo tablespace, so a new one can be created and assigned to the new instance without affecting the the running instances.1.Use CREATE UNDO TABLESPACE statement to create a new undo tablespace for the new instance.For example:CREATE UNDO TABLESPACE undotbs_02 DATAFILE DGNAME1 size 1000M autoextend on;Documentation references for CREATE UNDO TABLESPACE syntax:10gR211gR111gR22. Use ALTER SYSTEM to set UNDO_TABLESPACE to the new tablespace for the new SID.For example:ALTER SYSTEM SET UNDO_TABLESPACE undotbs_02 SCOPEBOTH SIDSID2;The new instance should have SID.UNDO_TABLESPACE set to the name of the new undo tablespace just created. Each of the other instances of the database should keep its undo tablespace as the same as what it was before.3. The new instance should now start without throwing ORA-30012.Note: For RAC, it is NOT necessary to follow the steps in KB117198, which instruct you to set undo management to Manual, as long as at least one other instance is up. This is not the best procedure for RAC, because the undo management has to be the same for all the instances. To use manual undo management, one would have to shut down all the instances of the database and reopen with manual undo mgmt. This is not necessary for RAC because in RAC the new undo tablespace can simply be created and assigned from another, already running instance.Attachments :CauseA new instance of an existing RAC database was created - not using DBCA.The new database instance will not start because a new UNDO tablespace was not created and assigned to it.Each instance in a RAC database must have its own UNDO tablespace, specified in the pfile/spfile.When a new instance is added using DBCA, DBCA should automatically create an UNDO tablespace for the new instance. If a new instance is added manually (not recommended), then the new UNDO tablespace must also be manually created and manually assigned to the new instance. The recommended way to add a new RAC instance is to use DBCA.
