ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

2021年1月26日 1024点热度 1人点赞 0条评论

一体机的19c新数据库,由于项目人员修改undo,导致出现有的pdb无法open的情况:

2021-01-26T12:03:43.957991+08:00
PDBAPP(3):Endian type of dictionary set to little
PDBAPP(3):Undo initialization recovery: err:0 start: 2574325 end: 2574327 diff: 2 ms (0.0 seconds)
2021-01-26T12:03:44.456447+08:00
PDBAPP(3):Undo initialization online undo segments: err:30013 start: 2574327 end: 2574579 diff: 252 ms (0.3 seconds)
Pdb PDBAPP hit error 30013 during open read write (1) and will be closed.
2021-01-26T12:03:44.457035+08:00
Errors in file /u01/app/oracle/diag/rdbms/wydb/wydb2/trace/wydb2_ora_23282.trc:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

oerr查看报错信息:

// *Cause:   the specified undo tablespace is currently used by another
//           instance.
// *Action:  Wait for the undo tablespace to become available or
//           change to another name and reissue the statement.

可见问题比较清楚,undo被别的实例占用了,这样的话,原因基本确认是修改undo配置时出了问题。
因此需确认目前的配置,改正即可。
当前数据库建库时采用了默认本地undo的方式:

select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
LOCAL_UNDO_ENABLED             TRUE

确认当前2个节点上的pdbs的状态如下:

SYS@wydb1> show pdbs
              2 PDB$SEED                       READ ONLY  NO
              3 PDBAPP                         READ WRITE NO
              4 PDBHIS                         MOUNTED
              6 PDBCOL                         READ WRITE NO
SYS@wydb2> show pdbs
              2 PDB$SEED                       READ ONLY  NO
              3 PDBAPP                         MOUNTED
              4 PDBHIS                         READ WRITE NO
              6 PDBCOL                         READ WRITE NO

可见部分有2个pdb只在一个节点打开了。
19c每个pdb的undo配置信息如果做了修改,在pdb_spfile$视图可以看到:

col SID for a10
col NAME for a20
col VALUE$ for a50
col PDB_NAME for a50
select a.SID,a.NAME,a.VALUE$,b.PDB_NAME from sys.pdb_spfile$ a,cdb_pdbs b where a.PDB_UID=b.CON_UID;
SID        NAME                 VALUE$                                             PDB_NAME
---------- -------------------- -------------------------------------------------- --------------------------------------------------
*          db_securefile        'PREFERRED'                                        PDB$SEED
*          db_securefile        'PREFERRED'                                        PDBCOL
*          db_create_file_dest  '+DATA2'                                           PDBCOL
*          db_securefile        'PREFERRED'                                        PDBAPP
*          undo_tablespace      UNDOTBS2                                           PDBAPP
*          db_securefile        'PREFERRED'                                        PDBHIS
*          db_create_file_dest  '+DATA3'                                           PDBHIS
*          undo_tablespace      UNDOTBS2                                           PDBHIS

可见2个pdb的undo配置错了。
具体修改的方式如下示范,进入所在pdb的活的实例,修改参数:

SYS@wydb1> alter pluggable database pdbhis open;
ALTER SYSTEM SET undo_tablespace='UNDOTBS1' SCOPE=BOTH SID='wydb1';
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH SID='wydb2';
ALTER SYSTEM RESET  undo_tablespace         SCOPE=BOTH SID='*';【这一句没有生效,待查】

然后就可以顺利打开pdb了。

liking

我是雪人

文章评论