OCI PDB ORA-01035: ORACLE only available to users with RESTRICTED SESSION due...

刘泽宇

现象：
PDB in restricted mode ,getting following error while trying to connect  :

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
SQL> 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

Pending Errors in pdb_plug_in_violations

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

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

原因：
PDB PRODUCES SYNC FAILURE

处理方法：
To solve the issue ,delete the offending statement (alter user "xxx") from PDB_SYNC$ for both CDB and PDB

On CDB root ,make a backup of PDB_SYNC$ table and delete de 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 make a backup of PDB_SYNC$ table and delete de 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;