企业官网建设流程全解析

-------altersystemsetundo_tablespace=undotbs01 sid='cxldb1'scope=both ;如果指定不存在的tablespace，重启报错，所以rolling restart

alter session set container=XXX

1.createundo tablespace undotbs02

2.altersystemsetundo_tablespace=undotbs01 sid='cxldb1'scope=both;

drop ---in use drop 不掉可以switch 到CDB，close abort ,open, abort会在open后自动recover 不会影响drop，但是很容易忘记切换到PDB drop，drop CDB的undo，由于CDB UNDO在使用，没多大问题，所以其他tablespace name不能叫undo，否则被drop掉了。

show spparameters undo 可以查看各个instance对应的undo.

----- PDB 级 undo 不需要指定的，让oracle自动选择undo tbs,可能inst1 使用undotbs2

show parameter undo;

We can reset the undo_tablespace value using

alter system reset undo_tablespace scope=spfile;

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 command

alter system set undo_tablespace=UNDO5 sid='instance1' scope=both;

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表空间

二、确认对应实例undo的文件位置

三、确认回滚段使用情况

#如果为空则证明该表空间可以被删除，否则要放其他时间处理尤其生产环境下。

四、检查undo segment状态

五、创建新的undo表空间并进行切换

六、等待原undo表空间segment状态变更为offline

七、删除原undo表空间以及数据文件

Applies To

All Users

Summary

NOTE: 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 mode
alter session set container=cdb$root;
column PROPERTY_NAME format a40
column PROPERTY_VALUE format a30
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
---------------------------------------- ------------------------------
LOCAL_UNDO_ENABLED TRUE


show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
5 PDB1 READ WRITE NO

-- Check undo_tablespace
set linesize 200
column VALUE format a30
select NAME,VALUE,CON_ID from V$SYSTEM_PARAMETER where NAME = 'undo_tablespace';

NAME VALUE CON_ID
---------------------------------------- ------------------------------ ----------
undo_tablespace UNDOTBS1 0
undo_tablespace 2
undo_tablespace UNDOTBS1 3 <----(*)UNDOTBS1
undo_tablespace UNDOTBS1 5 <----(*)UNDOTBS1

====NODE2
show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
5 PDB1 READ WRITE NO


-- Check undo_tablespace
set linesize 200
column VALUE format a30
select NAME,VALUE,CON_ID from V$SYSTEM_PARAMETER where NAME = 'undo_tablespace';

NAME VALUE CON_ID
---------------------------------------- ------------------------------ ----------
undo_tablespace UNDOTBS2 0 <----(*)UNDOTBS2
undo_tablespace 2
undo_tablespace UNDO_2 3 <----(*)UNDO_2
undo_tablespace UNDO_2 5 <----(*)UNDO_2

Solution

If 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:

1. Check the current UNDO tablespace.

   -- Connect to and execute the PDB on the target node.
   alter session set container=<pdb_name>;
   show con_name
   -- Check the current UNDO tablespace.
   show parameter undo_tablespace

   
   
2. If there is no UNDO tablespace to change to, create a new UNDO tablespace.

   CREATE UNDO TABLESPACE<new_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;

   
   
3. Change the UNDO tablespace.

   alter system set undo_tablespace= <new_UNDO_tablespace_name>container=currentsid='<sid_name>' scope=spfile ;current 可以不要

   
   example:

   alter system set undo_tablespace=UNDOTBS2 container=current sid='orcl2' scope=spfile ;

   
   
4. Restart the PDB and confirm that you have switched to the new tablespace.
   
   Restart the PDB:

   shutdown PDB下可以吗，还没试。。。
   startup

   
   Please 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_id=vr.usn;

   
   
5. 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 :

Cause

Created 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_2

ENH 32197045 is still in progress and has not been published.

---------------CDB PDB 命令一样

PDB 如果起不来呢。。。。。。 mount 下改？

Summary

A 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 type

Solution

Undo 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:
10gR2
11gR1
11gR2

2. Use ALTER SYSTEM to set UNDO_TABLESPACE to the new tablespace for the new SID.

For example:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02 SCOPE=BOTH SID='<SID2>';

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 :

Cause

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