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
Thanks Mike for this great article (as usual) , i have posted in March in my blog about this exact topic (but not as good as you have written it 🙂 )
https://geodatamaster.com/2018/03/26/migrate-database-in-cdb-environment-with-different-db_block_size/
thanks again
Thanks Emad!!! And thanks for the pointer!
Cheers, Mike
Hello Mike,
just in case you have seen something similar before? We created an 16k CDB with April RU 18.2.0.0.180417. But we cannot create PDBs.
NAME TYPE VALUE
db_block_size integer 16384
enable_pluggable_database boolean true
CREATE PLUGGABLE DATABASE czebe ADMIN USER PDBADMIN IDENTIFIED BY ROLES=(CONNECT) file_name_convert=NONE;
Error starting at line : 1 in command –
CREATE PLUGGABLE DATABASE czebe ADMIN USER PDBADMIN IDENTIFIED BY ROLES=(CONNECT) file_name_convert=NONE
Error report –
ORA-00604: error occurred at recursive SQL level 1
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /u01/app/oracle/product/18.1.0.0/dbhome_1/QOpatch/qopiprep.bat encountered error “/u01/app/oracle/product/18.1.0.0/dbhome_1/QOpatch/qopiprep.bat: line 67: /u01/app/oracle/product/18.1.0.0/dbhome_1/rdbms/log/stout_36558.txt: Permission denied
”
00604. 00000 – “error occurred at recursive SQL level %s”
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
No problems with 8k CDBs and PDBs.
Best regards,
Dagmar
Hi Dagmar,
please check this post and let me know if this workaround did the magic:
https://mikedietrichde.com/2018/05/08/different-block-sizes-when-you-plugin/
But honestly, to me this sounds like a bug. When both, the CDB$ROOT and the PDB$SEED are created with 16k block size, I can’t see why a PDB shouldn’t be created as it gets cloned from the PDB$SEED anyway.
alter system set DB_16k_CACHE_SIZE=100M scope=both;
Thanks,
Mike
Thanks, Mike,
alter system set DB_16k_CACHE_SIZE fails with ORA-00380: cannot specify db_16k_cache_size since 16K is the standard block size
I’ll open an SR.
Hello Dagmar,
I did the following:
18.3.0
Create fresh CDB with DBCA (custom database, 16 block_size choosen, no PDBs created, characterset AL32UTF8)
It took quite a bit to have it created (over 1 hour!)
Then:
SQL*Plus: Release 18.0.0.0.0 – Production on Thu Jul 26 14:51:00 2018
Version 18.3.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.3.0.0.0
SQL> create pluggable database pdb1 admin user adm identified by adm file_name_convert=(‘/u02/oradata/HUGO/pdbseed’,’/u02/oradata/HUGO/pdb1′);
Pluggable database created.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> show parameter db_block_size
NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 16384
Mike
Dagmar,
can you check if this is true in your case:
I have seen this if customer has role separation enabled. If the linux-groups of GI owner or RDBMS owner does not have write permissions for $OH/rdbms/log , this issue is seen.
We already have an open bug 26301387 for this. Most of the symptoms match, but, to be exactly sure, I would like to know more info and fully confirm if it’s a duplicate of above bug.
Work-around is to give required permissions. “ chmod 775 $ORACLE_HOME/rdbms/log “
Can you let me know if this is the issue?
Thanks,
Mike
We do have role separation (user grid for clusterware and ASM, user oracle for databases).
ll -d $ORACLE_HOME/rdbms/log
drwxr-xr-x 31 oracle oinstall 20480 Jul 26 15:34 /u01/app/oracle/product/18.1.0.0/dbhome_1/rdbms/log
chmod 775 $ORACLE_HOME/rdbms/log
ll -d $ORACLE_HOME/rdbms/log
drwxrwxr-x 31 oracle oinstall 20480 Jul 26 16:35 /u01/app/oracle/product/18.1.0.0/dbhome_1/rdbms/log
czbc_exa3> CREATE PLUGGABLE DATABASE czebe ADMIN USER PDBADMIN IDENTIFIED BY… ROLES=(CONNECT) file_name_convert=NONE;
Pluggable database CZEBE created.
===> Thank you very much, Mike, you saved us many days with support!!!
Strange it did not happen with 8k block size dbs, but only 16k, who knows, what is different there.
Bug is apparently not (yet) customer visible.
Best regards,
Dagmar
Glad that it helped 🙂
Thanks!
Mike
Mike,
The db_nk_cache_size should be configured to match the PDB block size. In your example, the PDB block size is 8k but you configured db_16k_cache_size in the CDB. Should it be db_8k_cache_size?
Thanks,
Arun
Hi Arun,
my writing was not clear – but the technical content was correct. I made it now (hopefully) much more clear 🙂
I create a 16 block size CDB called FRANZ
I create a PDB in it called FRANZ_PDB11
I unplug FRANZ_PDB11 and try to plug it into my standard CDB2 in the lab which has 8k block size
Now in order to make this work, I need to define the cache as otherwise I can’t plugin FRANZ_PDB11 with 16k blocks into CDB2 with 8k blocks.
Sorry for the inconvenience and – as always – thanks for the hint!
Kind regards
Mike
Thanks for the clarification Mike. Another question. This method works only for a working PDB unplug/plug into another CDB of same Oracle version/patch level, right? In other words, I cannot unplug a 19c non-CDB (8k), plug it into another 19c CDB (16k) as a PDB and run noncdb_to_pdb. Is that correct? I am asking because I tried that and running noncdb_to_pdb.sql threw ORA-600.
Thanks,
Arun
Hi Arun,
please see this blog post here:
https://mikedietrichde.com/2019/08/02/database-migration-from-non-cdb-to-pdb-various-pitfalls/
Cheers,
Mike
Hi Mike,
Can we migrate non cdb 11g database with 2k block size to 12c or 19c pluggable database with 8k block size
What will the step with min downtime or zero downtime
Please check out this blog post series:
https://mikedietrichde.com/2019/07/22/database-migration-from-non-cdb-to-pdb-overview/
You will find everything you need to know.
Cheers,
Mike
Hi Mike,
I tried this workaround on a Exadata X8 system with a RAC database and it works.
But the first try to open the new PDB ended with ORA-600. A further try to open the PDB works.
But now I can’t close the PDB. I always get an ORA-600. A attempt to delete the PDB with dbca crashes too.
Currently the PDB is open and it is possible to connect to the PDB.
Here the errorstack from alert.log
ALTER PLUGGABLE DATABASE PLATODEV close INSTANCES=ALL
2021-05-19T17:37:59.795838+02:00
PLATODEV(4):JIT: pid 278788 requesting stop
PLATODEV(4):Closing sequence subsystem (7968913214506).
PLATODEV(4):Buffer Cache flush started: 4
2021-05-19T17:38:00.029909+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/trace/CDBDMDEV1_dbw0_278412.trc (incident=180525) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [kcbunlockbuffer_1], [65], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/incident/incdir_180525/CDBDMDEV1_dbw0_278412_i180525.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2021-05-19T17:38:00.032097+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/trace/CDBDMDEV1_dbw1_278419.trc (incident=180533) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [kcbunlockbuffer_1], [65], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/incident/incdir_180533/CDBDMDEV1_dbw1_278419_i180533.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2021-05-19T17:38:00.648786+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/trace/CDBDMDEV1_dbw1_278419.trc:
ORA-00600: internal error code, arguments: [kcbunlockbuffer_1], [65], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/trace/CDBDMDEV1_dbw1_278419.trc (incident=180534) (PDBNAME=CDB$ROOT):
ORA-471 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/incident/incdir_180534/CDBDMDEV1_dbw1_278419_i180534.trc
2021-05-19T17:38:00.669107+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/trace/CDBDMDEV1_dbw0_278412.trc:
ORA-00600: internal error code, arguments: [kcbunlockbuffer_1], [65], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/trace/CDBDMDEV1_dbw0_278412.trc (incident=180526) (PDBNAME=CDB$ROOT):
ORA-471 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/cdbdmdev/CDBDMDEV1/incident/incdir_180526/CDBDMDEV1_dbw0_278412_i180526.trc
2021-05-19T17:38:00.764924+02:00
Dumping diagnostic data in directory=[cdmp_20210519173800], requested by (instance=1, osid=278419 (DBW1)), summary=[incident=180533].
Hi Frank,
did you open an SR for this? This is not expected.
I did a quick check and found this issue:
BUG 31099312 – ORA-00600 [KCBUNLOCKBUFFER_1] DURING PLUG IN OPERATION NONCDB_TO_PDB.SQL FAILS
==> Fixed in 19.11.0
Are you on 19.11.0 already? If not, please try.
Cheers,
Mike
Hi Mike,
we have 19.10.0 in use. I have found Patch 31099312 for BUG 31099312 for version 19.10.0. I will try it first bevore upgrading to 19.11.0.
Many thanks!
Frank
Frank – did the patch fix your issue?
Hi Mike,
I have forgotten to say that in my case the CDB is 16k and the PDB 8K.
Best regards
Frank Szygula
Thanks Frank, please see my other reply. This is most likely below bug, and it is fixed in 19.11.0.
Cheers and sorry for the inconvenience,
Mike