Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-5335

Change Oracle table schemes NVARCHAR2/NCLOB column types

    XMLWordPrintable

    Details

    • Type: Change Request
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Installation (I)
    • Environment:
      RHEL7, Oracle 19.3
    • Team:
      Team C
    • 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)

      Description

      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.

      description nvarchar2(2048) DEFAULT '' ,
      *
      ERROR at line 5:
      ORA-00910: specified length too long for its datatype

      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:

      [[email protected] root]$ rlwrap sqlplus / AS SYSDBA
      SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTERSET' or parameter='NLS_NCHAR_CHARACTERSET';
      
      PARAMETER
      ----------------------------------------------------------------
      VALUE
      ----------------------------------------------------------------
      NLS_CHARACTERSET
      AL32UTF8
      
      NLS_NCHAR_CHARACTERSET
      AL16UTF16
      
      SQL> Select owner, table_name, column_name from dba_tab_columns where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and owner != 'SYS' and owner != 'SYSTEM';
      TIP for information

      If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data. You have an option ALTER those columns to non national encoding, remove data etc. So if there is no data ALTER DATABASE NATIONAL CHARACTER SET UTF8 will work.

      SQL> SHUTDOWN IMMEDIATE;
      SQL> STARTUP MOUNT;
      SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
      SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE = MEMORY;
      SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE = MEMORY;
      SQL> ALTER DATABASE OPEN;
      SQL> ALTER DATABASE NATIONAL CHARACTER SET UTF8;

      If there is no Errors like:

      ALTER DATABASE NATIONAL CHARACTER SET UTF8
      *
      ERROR at line 1:
      ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR
      or NVARCHAR2 data exists

      Regarding to TIP - read it what to do.

      Otherwise You could try to do the following in this place:

      SQL> update props$ set value$='UTF8' where name in ('NLS_NCHAR_CHARACTERSET');
      SQL> commit;

      After:

      SQL> SHUTDOWN IMMEDIATE;
      SQL> STARTUP;

      Let's check again:

      SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTERSET' or parameter='NLS_NCHAR_CHARACTERSET';
      
      PARAMETER
      ----------------------------------------------------------------
      VALUE
      ----------------------------------------------------------------
      NLS_CHARACTERSET
      AL32UTF8
      
      NLS_NCHAR_CHARACTERSET
      UTF8

      We are done!

      But instead of this all - my recommendation could be change NVARCHAR2 and NCLOB column data types to VARCHAR2 and CLOB.

      cd /zabbix/install/scripts/with/oracle/scheme
      sed -i 's/nvarchar2/varchar2/g; s/nclob/clob/g;' *

      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

        Attachments

          Activity

            People

            Assignee:
            MVekslers Michael Veksler
            Reporter:
            radix Gatis Rumbens
            Votes:
            7 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated: