Oracle Database Character Set modification

The Oracle character set is a collection of symbols that interpret a byte of data, have a size, and have a mutual containment relationship. Oracle's support for national language architectures allows you to store, process, and retrieve data using localized languages. In general, the database character set is specified when the DB instance is installed, and it is not recommended to modify the database character set after the installation is complete, in theory it cannot be changed . If there is a problem with character compatibility, the database character set can also be modified.

Oracle's character set naming follows the following naming conventions: <language><bit size><encoding>: < languages >< bits >< encodings; language specifies the language of the server message, territory specifies the date and number format of the server, CharSet specifies the character set. such as Chinese_china. zhs32gb18030.

Database character Set design three aspects: (1) server side (2) client side (3) DMP file

To view the server-side character Set method:

1) SELECT * from Nls_database_parameters;

2) SELECT Userenv (' language ') from Dual;

To view the client-side character Set method:

Windows methods:

C:\users\zhang>set Nls_lang
Nls_lang=simplified Chinese_china. Zhs16gbk

Unix method: Echo $NLS _lang

To view the DMP file character set:

The DMP file exported by Oracle's EXP tool also contains character set information. The 2nd and 3rd bytes of a DMP file record the character set of the DMP file. If the DMP file is not large, you can open it with UltraEdit (16 binary), look at the 2nd 3rd byte of the content, such as 0354, and then use the following SQL to isolate its corresponding character set:

Sql>select nls_charset_name (to_number (' 0354 ', ' xxxx ')) from Dual;
Nls_charset_name (To_number (' 0354 ', ' XXXX ')
----------------------------------------
Zhs16gbk

If the DMP file is large as above 2G, you can view (on the UNIX host) with the following command: Cat dbexp.dmp |od-x|head-1|awk ' {print $3-6} ' |cut-c

I. Modifying the database character set of a single instance

The following is an example of modifying the database's character set from ZHS16GBK to zhs32gb18030.

The procedure is as follows:

--Stop monitoring
Lsnrctl stop
--Close the database
Sql>shutdown immediate;
--Open database to Mount state
Sql>startup Mount;
--sql_trace is a powerful auxiliary diagnostic tool provided by Oracle for SQL tracing. In the diagnosis and resolution of daily database problems, Sql_trace is a very common method.
--sql_trace =true The global enable Sql_trace will cause all process activities to be tracked, including background processes and all user processes, which often results in serious performance issues, so use caution in production environments, which are dynamic after 10g and can be adjusted at any time , which is very effective in some diagnoses.
--Hint: by enabling sql_trace globally, we can track the activity of all background processes, many abstract descriptions in the documentation, and by tracking the real-time changes in the file, we can clearly see the close coordination between the various processes.
Sql>alter session Set Sql_trace=true;
--alter SYSTEM DISABLE RESTRICTED session to cancel the restricted state of the database. In a database-restricted process, only users who have create session permissions on the database can connect to the database. This state facilitates the backup, recovery, import, export and other operations of the database.
Sql>alter system enable restricted session;
--First set job_queue_processes=0,oracle will kill CJQ0 and the corresponding job process
Sql>alter system set job_queue_processes=0;
The--aq_tm_processes value is selectable between 1 and 10, and 0 is the shutdown queue monitor
Sql>alter system set aq_tm_processes=0;
--Use the Internal_use keyword to modify the locale setting to allow the Oracle database to bypass the checksum of the subset and the superset
Sql>alter database Character set Internal_use zhs32gb18030;
--Open Database
Sql>alter database open;
--Close the database again
Sql>shutdown immediate;
--Open the database again
Sql>startup;
Sql>set Linesize 200
Sql>select *
From Nls_database_parameters A
WHERE a.parameter = ' nls_characterset ';
Sql>select ' Zhang Wanglizhao ' from dual;

Second, modify the database character set in the RAC environment

Modify the database character set method in a RAC environment in ORACLE11GR2:

(1) Stop monitoring
Ps-ef |grep Pmon
Lsnrctl stop
(2) Stop all instances
CD $ORACLE _home/crs_1/bin
./srvctl Stop database-d TestDB
or use
./srvctl Stop instance-d testdb-i Rac1
./srvctl Stop instance-d testdb-i RAC2
(3) View cluster status
Crs_stat-t
(4) can only operate on one node, such as Rac1, otherwise will be reported ORA-12720 error, meaning for this operation can only be operated under a single instance.
Startup
(5) First put the database in a non-clustered state
Alter system set Cluster_database=false scope=spfile sid= ' TestDB ';
(6) Modifying the database character set
Shutdown immediate;
startup Mount;
Alter session set Sql_trace=true;
Alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
ALTER DATABASE character set Internal_use zhs32gb18030;
(7) Put the database in the cluster state again
Alter system set CLUSTER_DATABASE=TRUE Scope=spfile;
(8) Closing the database
Shutdown immediate;
(9) First to start a node for verification, each node needs to be verified.
Startup
Sqlplus/as SYSDBA
SELECT ' Zhang Wanglizhao ' from Dual;
(10) Restore the cluster status
./srvctl Start database-d TestDB
(11) View cluster status
Crs_stat–t

When you install a database, you need to consider the issue of the database character set, preferably when you install the instance, and do not recommend subsequent modifications to the database character set.