Monday, September 11, 2017

Why is my PDB seemingly stuck in RESTRICTED mode?


Check out the view pdb_plug_in_violations - it will point you in the right direction.

In my case, I had created a pluggable database some days after my CDB was finished.

I had completely forgotten about a common user that I had created at that time:

-- create a common user for all CONTAINERS
-- common users must use the prefix c##
create user C##dba identified by MySecretPasswor
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
container=all;

The new PDB was created without the USERS tablespace, which prevented the new PDB to be synchronized with the parent container.
The pdb_plug_in_violations contained the following message:

Sync PDB failed with ORA-959 during 'create user C##dba identified by *default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
container=all'


To resolve the situation, connect to the container with the missing tablespace:
alter session set container=pdbveg2;
Make sure my session OMF parameter is correctly set:
select name,value,DEFAULT_VALUE,ISDEFAULT,ISPDB_MODIFIABLE, ISSES_MODIFIABLE, DESCRIPTION
from V$PARAMETER where name like '%create_file_dest%';
NAME VALUE DEFAULT_VALUE ISDEFAULT ISPDB_MODIFIABLE ISSES_MODIFIABLE DESCRIPTION
db_create_file_dest /u02/oradata/cdbveg/pdbveg2 NONE TRUE TRUE TRUE default database location

Create the missing tablespace:
create tablespace USERS
datafile size 8M autoextend on next 2M maxsize 2G;
Finally, close and reopen your pluggable database:
alter pluggable database pdbveg2 close;
alter pluggable database pdbveg2 open read write;
Check status:
select CON_ID,name,OPEN_MODE, RESTRICTED
from v$containers;
CON_ID NAME OPEN_MODE RESTRICTED
4 PDBVEG2 READ WRITE NO

No comments:

Post a Comment