Different block sizes when you plugin?

Interesting case Roy came across a week ago with a customer in the US. The customer received a plugin violation because of different DB_BLOCK_SIZE between the source and the destination database. This happened regardless of unplug/plug or plugging in a non-CDB into a CDB. Hence the question came up: Can you have different block sizes when you plugin?

It should be possible – but you may need a tiny little workaround to make it happen.

The documentation as far as I can see was not helpful. MOS Note: 1935365.1 (Multitenant Unplug/Plug Best Practices) wasn’t very helpful either.

Different block sizes when you plugin?

You can have databases of different block sizes (DB_BLOCK_SIZE) between 2k and 32k. We generally recommend 8k as the standard but especially historically it is not unlikely that you may have databases with different block sizes.

But what happens when you’d like to plugin a database (or PDB) with a different DB_BLOCK_SIZE than the destination?

For a quick test I create a new CDB FRANZ in Oracle 12.2.0.1 with a DB_BLOCK_SIZE of 16k. Then I create a FRANZ_PDB11 – which has 16k block size of course as well. I will unplug it and try to plug it into another CDB having the standard DB_BLOCK_SIZE of 8k. Lets see what happens:

Then I unplug the PDB and do the compatibility check:

alter session set container=cdb$root;
Session altered.

alter pluggable database FRANZ_PDB11 close;
Pluggable database altered.

alter pluggable database FRANZ_PDB11 unplug into '/home/oracle/FRANZ_PDB11.xml';
Pluggable database altered.

This creates the XML manifest file separately. Then I run the compatibility check in the destination CDB:

SET SERVEROUTPUT ON

DECLARE
  compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  pdb_descr_file => '/home/oracle/FRANZ_PDB11.xml',
  pdb_name => 'FRANZ_PDB11')
  WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

PDB_PLUG_IN_VIOLATIONS

The result is “NO” (as it is quite often with this call). But I can check PDB_PLUG_IN_VIOLATIONS for further details. It can be purged since Oracle Database 12.2.0.1. And here it tells me:

column message format a50
column status format a9
column type format a9
column con_id format 9

select con_id, type, message, status
  from PDB_PLUG_IN_VIOLATIONS
 where status<>'RESOLVED'
 order by time;

CON_ID TYPE	 MESSAGE					    STATUS
------ --------- -------------------------------------------------- ---------
     2 WARNING	 Database option CATJAVA mismatch: PDB installed ve PENDING
		 rsion NULL. CDB installed version 12.2.0.1.0.

     2 WARNING	 Database option JAVAVM mismatch: PDB installed ver PENDING
		 sion NULL. CDB installed version 12.2.0.1.0.

     2 WARNING	 Database option OLS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 12.2.0.1.0.

     2 WARNING	 Database option XML mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 12.2.0.1.0.

     1 ERROR	 system tablespace block size(16384) does not match PENDING
		  configured block sizes, plug in is not allowed

     1 WARNING	 Database option CATJAVA mismatch: PDB installed ve PENDING
		 rsion NULL. CDB installed version 12.2.0.1.0.

     1 WARNING	 Database option JAVAVM mismatch: PDB installed ver PENDING
		 sion NULL. CDB installed version 12.2.0.1.0.

     1 WARNING	 Database option OLS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 12.2.0.1.0.

     1 WARNING	 Database option XML mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 12.2.0.1.0.

     1 WARNING	 CDB parameter db_block_size mismatch: Previous 163 PENDING
		 84 Current 8192

     1 WARNING	 CDB parameter pga_aggregate_target mismatch: Previ PENDING
		 ous 400M Current 120M

In addition to the usual nonsense warnings (see here) you see the really important one above: The ERROR for the SYSTEM tablespace having a wrong block size. An attempt to plugin will fail without further treatment.

The solution

The solution is quite simple thus not obvious:
You have to define a DB_n_CACHE_SIZE parameter matching the DB_BLOCK_SIZE in the destination CDB.

alter system set DB_16k_CACHE_SIZE=100M scope=both;

Afterwards you can plugin. The copy operation happens automatically:

create pluggable database FRANZ_PDB11 using '/home/oracle/FRANZ_PDB11.xml'
file_name_convert=('/u02/oradata/FRANZ/FRANZ_PDB11','/u02/oradata/CDB2/FRANZ_PDB11');
Pluggable database created.

alter pluggable database FRANZ_PDB11 open;
Pluggable database altered.

alter pluggable database franz_pdb11 save state;
Pluggable database altered.

show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 5 FRANZ_PDB11			  READ WRITE NO

Done.
With no errors. That’s why I consider the warning of PDB_PLUG_IN_VIOLATIONS useless and obsolete.

And the Oracle Cloud (OCI-Classic, DBCS, DBaaS)?

As expected the default setting for a database in the Oracle OCI-Classic is 8k:

[oracle@MIKE18C ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:57:21 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> show parameter db_block_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_size			     integer	 8192

Nothing unexpected or unusual – just be aware of it when you plugin.

Finally …

At the end of my journey I found the following articles on MOS – but as I haven’t attempted to plugin due to the error condition I haven’t seen the ORA- error. But without the error you have to know what to look for on MOS. Of course I could have found the second note below – if I would have searched on MOS upfront.

• MOS Note:1985930.1
  ORA-65154: “system tablespace block size (16384) does not match root’s block size” When Plugging in a PDB
• MOS Note: 2027614.1
  Can I Plug in a PDB to a CDB If Their Values of the DB_BLOCK_SIZE Parameter are Different

–Mike