Tales from the field: potential reasons for PDB plug-in violations part 1

Container Databases have been an area that I have researched intensively over the past years. With this post (and hopefully some others that follow) I would like to demonstrate some of the new situations the DBA might be confronted with. Please don’t use this post to give the new 12c architecture a hard time: standardised deployments (which I love) help you a lot. Not only do your DBA scripts work reliably everywhere, but the error condition I am showing in this post should be a lot less likely.

At the end of the post I’ll show an alternative approach using a standardised way of creating PDBs.

Environment

Setting the scene, my environment is as follows:

• Oracle Linux 7.2 with UEK4 (4.1.12-37.5.1.el7uek.x86_64 GNU/Linux)
• Single Instance databases CDB1 and CDB3
• Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
• Database Patch Set Update : 12.1.0.2.160419 (22291127)
• Oracle Managed Files (OMF) but no use of ASM

These aren’t in VMs for a change.

Scenario

The following steps lead me to the discovery of the plug-in violation. In CDB1 I created a common user, let’s call it c##martin for the sake of argument.

SQL> select sys_context('userenv','con_name') as con_name, name, cdb
  2  from v$database
  3  /

CON_NAME                       NAME      CDB
------------------------------ --------- ---
CDB$ROOT                       CDB1      YES

SQL> create user c##martin identified by xxxxxxxxxx 
  2  default tablespace tools  temporary tablespace temp
  3  quota unlimited on tools account unlock
  4  container = ALL;

User created.

So far nothing too exciting. Grant privileges as needed.

The Problem

Some time later I tried to create a PDB from the seed, and it failed:

SQL> create pluggable database pdb10 
  2  admin user pdbadmin identified by xxxxxxxxxx
  3  /

Pluggable database created.

SQL> alter pluggable database pdb10 open;

Warning: PDB altered with errors.

SQL> 

Altered with errors? The command I just typed is probably the simplest and most basic way to create a PDB, what could possibly go wrong with it? Fist stop is the alert.log, and this is what has been recorded:

2016-06-29 09:48:38.261000 +01:00
create pluggable database pdb10
admin user pdbadmin identified by *
 APEX_040200.WWV_FLOW_ADVISOR_CHECKS (CHECK_STATEMENT) - CLOB populated
2016-06-29 09:48:44.549000 +01:00
****************************************************************
Pluggable Database PDB10 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB10 is WE8MSWIN1252
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#19 to file$(old file#5)
Adding new file#20 to file$(old file#7)
Successfully created internal service pdb10 at open
2016-06-29 09:48:45.629000 +01:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB10 with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdb10
admin user pdbadmin identified by *
2016-06-29 09:53:48.816000 +01:00
alter pluggable database pdb10 open
Pluggable database PDB10 dictionary check beginning
Pluggable Database PDB10 Dictionary check complete
Database Characterset for PDB10 is WE8MSWIN1252
2016-06-29 09:53:51.317000 +01:00
Opening pdb PDB10 (3) with no Resource Manager plan active
Pluggable database PDB10 opened read write
Completed: alter pluggable database pdb10 open

OK so there’s nothing in there. But wait-am I not trying to create a new PDB and plug it into the CDB? And aren’t problems in there recorded in a view? So let’s give that a try:

SQL> select cause, message from pdb_plug_in_violations where name = 'PDB10';

CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
Sync Failure
Sync PDB failed with ORA-959 during 'create user c##martin identified by *defaul
t tablespace tools  temporary tablespace temp
quota unlimited on tools account unlock
container = ALL'

Got you! A sync failure: the common user I created earlier (container = ALL) can’t be created on the PDB because of a missing tablespace. Is the PDB in an unrecoverable state? No, but it is opened in restricted mode. Connections to it won’t be possible:

SQL> select name,open_mode,restricted from v$pdbs 
  2  where name = 'PDB10';

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB10                          READ WRITE YES

SQL> conn pdbadmin/xxxxxxxxxx@localhost/PDB10
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

NB: the same can happen when you create a common user in the CDB while a PDB that doesn’t meet the criteria for it to created is closed. For example, if all PDBs are open and you try to create c#martin and there is a PDB without the tools tablespace the create user command fails. It will not fail if a PDB doesn’t have a tools tablepsace but is closed at the time the user is created. The command to open the PDB will throw the same error as shown above when you try to open it.

The Fix

Dead easy-a common user cannot be created due to a missing tablespace. So let’s create it and see if we can open the PDB:

SQL> conn / as sysdba
Connected.
SQL> alter session set container = PDB10;

Session altered.

SQL> create tablespace tools datafile size 10m;

Tablespace created.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb10 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb10 open;

Pluggable database altered.

SQL> select name,open_mode,restricted from v$pdbs 
  2   where name = 'PDB10';

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB10                          READ WRITE NO

SQL> 

Fixed! But I would still argue that a different approach – a “gold image PDB” might be better suited for production work than a clone from the seed.

A potentially better Approach

I personally think that creating a PDB from the seed is not something that should be done in production, but that is – a personal opinion…

The problem you just read about could be avoided with a Golden Image CDB/PDB. I wrote about a way to create such a golden database image in my last post. Following the procedure I created CDB3 using dbca feeding it my template. It contains the goldenImage PDB which has been created as follows:

SQL> select sys_context('userenv','con_name') as con_name, name, cdb
  2  from v$database;

CON_NAME                       NAME      CDB
------------------------------ --------- ---
CDB$ROOT                       CDB3      YES

SQL> create pluggable database goldenImage 
  2  admin user pdbadmin identified by xxxxxxxx
  3  default tablespace users datafile size 10m
  4  storage (maxsize 20G);

Pluggable database created.

SQL> alter pluggable database goldenImage open;

Pluggable database altered.

SQL> alter session set container = goldenImage;

Session altered.

SQL> create tablespace tools datafile size 10m;

Tablespace created.

CDB3 also features a user C##MARTIN which has been created exactly as in CDB1:

SQL> select u.username, u.account_status, nvl(p.name, 'CDB$ROOT') con_name
  2  from cdb_users u, v$pdbs p
  3  where u.username = 'C##MARTIN'
  4 and u.con_id = p.con_id(+);

USERNAME             ACCOUNT_STATUS       CON_NAME
-------------------- -------------------- ------------------------------
C##MARTIN            OPEN                 CDB$ROOT
C##MARTIN            OPEN                 GOLDENIMAGE

With everything in place I can go about cloning goldenImage and avoid the problem I wrote about in the first part of this post altogether.

SQL> create pluggable database pdb10 from goldenImage;

Pluggable database created.

SQL> alter pluggable database pdb10 open;

Pluggable database altered.

SQL> select cause,message from pdb_plug_in_violations where name = 'PDB10';

no rows selected

SQL> conn c##martin/xxxxxxxxxx@localhost/pdb10
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB10

Standards win again.

Advertisement