Oracle10G - RAC环境下字符集修改总结

Oracle 10g rac环境数据库字符集修改总结

根据海西ORACLE 10G RAC环境字符集修改成功案列编写此技术文档，为以后工程师再次遇到此类问题时及时果断的解决提供可靠依据。具体操作步骤如下：

第一步，首先解除RAC集群环境。

[oracle@dbserver1 ~]# su – oracle

[oracle@dbserver1 ~]$ cd /u01/app/oracle/product/10.2/crs_1/bin

[oracle@dbserver1 bin]$ ./srvctl stop instance -d ltaiswk -i ltaiswk1 [oracle@dbserver1 bin]$ ./srvctl stop instance -d ltaiswk -i ltaiswk2 [oracle@dbserver1 bin]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SM1.asm application ONLINE ONLINE dbserver1 ora....R1.lsnr application ONLINE ONLINE dbserver1 ora....er1.gsd application ONLINE ONLINE dbserver1 ora....er1.ons application ONLINE ONLINE dbserver1 ora....er1.vip application ONLINE ONLINE dbserver1 ora....SM2.asm application ONLINE ONLINE dbserver2 ora....R2.lsnr application ONLINE ONLINE dbserver2 ora....er2.gsd application ONLINE ONLINE dbserver2 ora....er2.ons application ONLINE ONLINE dbserver2 ora....er2.vip application ONLINE ONLINE dbserver2 ora.orcl.db application OFFLINE OFFLINE ora....l1.inst application OFFLINE OFFLINE ora....l2.inst application OFFLINE OFFLINE

第二步，在节点一启动数据库。

[oracle@dbserver1 bin]$ ./ srvctl start instance -d ltaiswk -i ltaiswk1 [oracle@dbserver1 bin]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SM1.asm application ONLINE ONLINE dbserver1 ora....R1.lsnr application ONLINE ONLINE dbserver1 ora....er1.gsd application ONLINE ONLINE dbserver1 ora....er1.ons application ONLINE ONLINE dbserver1 ora....er1.vip application ONLINE ONLINE dbserver1 ora....SM2.asm application ONLINE ONLINE dbserver2 ora....R2.lsnr application ONLINE ONLINE dbserver2 ora....er2.gsd application ONLINE ONLINE dbserver2 ora....er2.ons application ONLINE ONLINE dbserver2 ora....er2.vip application ONLINE ONLINE dbserver2 ora.orcl.db application ONLINE ONLINE dbserver2

ora....l1.inst application ONLINE ONLINE dbserver1 ora....l2.inst application OFFLINE OFFLINE

第三步，连接库，修改spfile文件里的参数cluster_database=false。 [oracle@dbserver1 bin]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 15 18:36:06 2011

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

SQL> conn /as sysdba; Connected. SQL> alter system set cluster_database=false scope=spfile; System altered.

第四步，查看当前字符集。

SQL> select * from nls_database_parameters where

parameter='NLS_CHARACTERSET';

PARAMETER ------------------------------------------------------------ VALUE ---------------------------------------------------------------- ---------------- NLS_CHARACTERSET WE8ISO8859P1

第五步，开始修改。

SQL> shutdown immediate; 关闭库

SQL> startup mount; 启动到MOUNT状态

SQL> alter system enable restricted session; 启用限制会话 System altered.

SQL> alter system set JOB_QUEUE_PROCESSES=0; 修改参数 System altered.

SQL>alter system set AQ_TM_PROCESSES=0; 修改参数 System altered.

SQL>alter system set AQ_TM_PROCESSES=0; 修改参数 System altered.

SQL>alter database open; 开启数据库

SQL> alter database character set INTERNAL_USE ZHS16GBK; 修改字符集 System altered.

SQL> alter system set cluster_database=true scope=spfile; 再改回参数cluster_database=true 。 System altered.

SQL> shutdown immediate; 关闭库 Database closed.

Database dismounted. ORACLE instance shut down. SQL> startup; 开启库

ORACLE instance started. Total System Global Area 1.2583E+10 bytes Fixed Size 2042128 bytes Variable Size 1828718320 bytes Database Buffers 1.0737E+10 bytes Redo Buffers 14733312 bytes Database mounted. Database opened.

SQL> select value from nls_database_parameters where

parameter='NLS_CHARACTERSET'; 查询修改后的字符集 VALUE

--------------------------------------------------------------------- ZHS16GBK

至此，字符集修改完毕。

第六步，开启实例，把RAC恢复到正常状态

[oracle@dbserver1 bin]$ ./srvctl start instance -d ltaiswk -i ltaiswk2 [oracle@dbserver1 bin]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SM1.asm application ONLINE ONLINE dbserver1 ora....R1.lsnr application ONLINE ONLINE dbserver1 ora....er1.gsd application ONLINE ONLINE dbserver1 ora....er1.ons application ONLINE ONLINE dbserver1 ora....er1.vip application ONLINE ONLINE dbserver1 ora....SM2.asm application ONLINE ONLINE dbserver2 ora....R2.lsnr application ONLINE ONLINE dbserver2 ora....er2.gsd application ONLINE ONLINE dbserver2 ora....er2.ons application ONLINE ONLINE dbserver2 ora....er2.vip application ONLINE ONLINE dbserver2 ora.orcl.db application ONLINE ONLINE dbserver2 ora....l1.inst application ONLINE ONLINE dbserver1 ora....l2.inst application OFFLINE ONLINE dbserver2

此时，再次用PLSQL去查看表里的中文数据时，会由原来的 “？ ？”变成了“靠靠靠”， 解决办法为，重新导入数据，将会正常显示为中文。