适用范围

oracle 12c及以上cdb模式

问题概述

正常重启数据库pdb启动在 RESTRICTED模式
使用普通用户连接，得到一下错误:

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

查询pdb状态：

select CON_ID,NAME,OPEN_MODE,RESTRICTED,OPEN_TIME from v$pdbs;

CON_ID NAME OPEN_MODE RESTRICTED OPEN_TIME <<<<<<<<<<< RESTRICTED
3 PDB1 READ WRITE YES 31.01.19 10:22:46,761000000 GMT

查询pdb_plug_in_violations，查看报错信息：

select name, cause, type, message, status from pdb_plug_in_violations where type = 'ERROR' and status !='RESOLVED' ;

PDB1 Sync Failure ERROR "Sync PDB failed with ORA-65177 during 'alter user "xxx" ...' " PENDING<<<<<<<<<

问题原因

Possible reasons for this errors are :
Delete a common user
Having two set of local user ,one in CDB and other in PDB
Create / duplicate a PDB

解决方案

To solve the issue ,delete the offending statement (alter user “xxx”) from PDB_SYNC$ for both CDB and PDB.
On CDB root ，备份table PDB_SYNC$ 并删除offending rows

sqlplus / as sysdba
SQL> col NAME format a30
SQL> col SQLSTMT format a100
SQL> Select CTIME,SQLSTMT,NAME,FLAGS,OPCODE,REPLAY# from PDB_SYNC$;
SQL> create table BKPPDB_SYNC$ as select * from PDB_SYNC$;
SQL> delete from PDB_SYNC$ where SQLSTMT like ('%alter user "xxx"%');
SQL> commit;

On PDB ，备份table PDB_SYNC$ 并删除offending rows,stop/start the PDB

SQL> alter session set container=PDB;
SQL> col NAME format a30
SQL> col SQLSTMT format a100
SQL> Select CTIME,SQLSTMT,NAME,FLAGS,OPCODE,REPLAY# from PDB_SYNC$;
SQL> create table BKPPDB_SYNC$ as select * from PDB_SYNC$;
SQL> delete from PDB_SYNC$ where SQLSTMT like ('%alter user "xxx"%');
SQL> commit;
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database PDB close;
SQL> alter pluggable database PDB open;

参考文档