Type: Change Request
Affects Version/s: None
Fix Version/s: None
Component/s: Installation (I)
Environment:RHEL7, Oracle 19.3
Sprint:Sprint 59 (Dec 2019), Sprint 60 (Jan 2020), Sprint 61 (Feb 2020), Sprint 62 (Mar 2020), Sprint 63 (Apr 2020), Sprint 64 (May 2020), Sprint 65 (Jun 2020), Sprint 66 (Jul 2020)
If you try to create in Oracle RDBMS Zabbix users, table space i.e from provided Zabbix creation scripts on modern Oracle RDBMS (let say starting from 12c (12.2) and have not read the related Zabbix documentation that we need a 1-2byte unicode (UTF-8) on Unicode national characters probably You will fail with error like that.
The NVARCHAR2 datatype was introduced a long time ago by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database (which uses VARCHAR2). The NVARCHAR2 is a Unicode-only datatype. It was actual with RDBMS version < 10. Theoretically it was more relevant in old RDBMS versions where used non-Unicode encoding on DB - but instead use national encoding. For an example: BLT8MSWIN1257 (Baltic states 8 bit windows-1257 code page).
Starting with Oracle Database 12c Release 2 (12.2), the default database character set of a database created from the General Purpose/Transaction Processing or the Data Warehousing template is Unicode AL32UTF8 and Unicode national character set is AL16UTF16 by default. Oracle recommends that you use Unicode AL32UTF8 as the database character set and AL16UTF16 as national character set.
The NVARCHAR2 column, which uses the NLS_NCHAR_CHARACTERSET which will almost always be AL16UTF16 for now is really only useful in a couple of cases. The first, and by far most common, is when you have a legacy application whose NLS_CHARACTERSET doesn't support Unicode and cannot be migrated to Unicode but where you want to support Unicode data in a handful of columns. That's not the case here. The second would be when the database does support Unicode but you want to use the UTF-16 encoding for some reason such as the ability to store primarily Japanese and Chinese data using mostly 2 bytes per character in UTF-16 rather than 3 bytes per character in UTF-8. But if you do that, you're still (barring 12.2 and the ability to have 32k VARCHAR2 columns) limited to 4000 bytes (2000 characters in a UTF-16 column). Hence the error when you try to define NVARCHAR2(4000) which would need at least 8000 bytes to store 4000 characters. If you do need to store more than 4000 bytes of data and you're using Oracle 11.2 or later, you'd have to use a LOB data type (NCLOB).
If your NLS_CHARACTERSET is AL32UTF8, any VARCHAR2 column will support Unicode (via the UTF-8 encoding). In 99.9% of cases, you'd never want to create an NVARCHAR2 column at all now days.
So but what to do if You still want to use Zabbix provided scripts on already working root container CDB database. Thanks Oracle that now we can use different encoding in a root container database (CDB) an pluggable database (PDB). So we can create another PDB with NLS_NCHAR_CHARACTERSET=UTF-8 - there is no problem.
But what to do when You can't do that or Your DBA will not do that. Is it possible change current (working CDB) from AL16UTF16 to UTF-8?
It's possible...but do it on your own risk. I'm recommend that you perform a full database backup before changing the national character set!!! It is always not better choice to use 'ALTER DATABASE' command to change the character set
Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work.
One of the options:
If there is no Errors like:
Regarding to TIP - read it what to do.
Otherwise You could try to do the following in this place:
Let's check again:
We are done!
But instead of this all - my recommendation could be change NVARCHAR2 and NCLOB column data types to VARCHAR2 and CLOB.
Oracle recommendation is that you create your database with the AL32UTF8 database character set, and move all your national character set data to normal VARCHAR2 and CHAR columns