Oracle 18c ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

场景:Oracle 18c 创建pdb，连接用户时提示受限制，在此之前做过共享undo模式转换为本地undo模式参考:https://www.cndba.cn/Expect-le/article/1786，后把pdb删除，再次创建新的pdb。提示以下信息:

SQL> conn hr/oracle@pdbcndba
ERROR:
ORA-01035: ORACLE ֻՊѭ¾ࠓΠRESTRICTED SESSION ȨОµœû§ʹԃ

Warning: You are no longer connected to ORACLE.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     4 PDBCNDBA              READ WRITE YES

查看日志：

 

Post plug operations are now complete.
Pluggable database PDBCNDBA with pdb id - 3 is now marked as NEW.
****************************************************************
PDBCNDBA(3):Database Characterset for PDBCNDBA is AL32UTF8
PDBCNDBA(3):Accessing dba_registry_sqlpatch hit error: 942
Violations: Type: 2, Count: 3
PDBCNDBA(3):***************************************************************
PDBCNDBA(3):WARNING: Pluggable Database PDBCNDBA with pdb id - 3 is
PDBCNDBA(3):         altered with errors or warnings. Please look into
PDBCNDBA(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
PDBCNDBA(3):***************************************************************
2018-06-29T02:51:11.639362+08:00
PDBCNDBA(3):Encountered error:13509 during AWR registration for pdb:PDBCNDBA
PDBCNDBA(3):Opening pdb with no Resource Manager plan active
PDBCNDBA(3):ORA-7452: resource plan 'INTERNAL_PLAN' does not exist
PDBCNDBA(3):Resource Manager failed to initialize on pdb 3
2018-06-29T02:51:11.788948+08:00
PDBCNDBA(3):LOGMINER: Encountered error:6553 during dictionary build for pdb:PDBCNDBA
PDBCNDBA(3):Logminer will be unable to deliver changes for this PDB.
PDBCNDBA(3):Logical standby users will need to unplug and re-plug this PDB after resolving this error.
PDBCNDBA(3):GoldenGate users already replicating this PDB will need to drop and re-add this PDB in extract.
PDBCNDBA(3):Unable to obtain resource (0) def from self or template (2)
Pluggable database PDBCNDBA opened read write
Completed: alter pluggable database pdbcndba open
2018-06-29T02:56:00.642634+08:00

根据日志提示查看PDB_PLUG_IN_VIOLATIONS提示如下错误信息：

 

SQL> select MESSAGE from PDB_PLUG_IN_VIOLATIONS;

MESSAGE
--------------------------------------------------------------------------------
CDB is using local undo, but no undo tablespace found in the PDB.
Database option CATALOG mismatch: PDB installed version NULL. CDB installed vers
ion 18.0.0.0.0.

解决方法：

提示信息说明因为现在是local undo 模式需要为pdb数据库创建本地undo表空间
在对应的pdb下创建undo表空间
SQL> alter session set container=pdbcndba;

Session altered.
create undo tablespace undo datafile  '/u01/app/oracle/oradata/pdbcndba/undo01.dbf' size 2000m autoextend off ;
重启pdb数据库
alter pluggable database pdbcndba close;
alter pluggable database pdbcndba open;
数据库显示正常
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     4 PDBCNDBA              READ WRITE NO