RAC修改字符集

字符集修改做过几次了，这次感觉还是有点不顺，走了弯路，再记一遍
【概况】
准备搭建rac+rac dg，发现两端字符集不大一致，担心到时出问题。

【目标】
将备库nls_nchar_characterset修改成与主库一致。
--备
nls_nchar_characterset utf8
修改为
--主
nls_nchar_characterset al16utf16

0、备库 修改前
primary-sys@testdb2>set pagesize 100
primary-sys@testdb2>col value$ for a30
primary-sys@testdb2>select name,value$ from props$ where name like '%nls%';

name value$
------------------------------------------------------------------------------------------ ------------------------------
nls_language american
nls_territory america
nls_currency $
nls_iso_currency america
nls_numeric_characters .,
nls_characterset zhs16gbk
nls_calendar gregorian
nls_date_format dd-mon-rr
nls_date_language american
nls_sort binary
nls_time_format hh.mi.ssxff am
nls_timestamp_format dd-mon-rr hh.mi.ssxff am
nls_time_tz_format hh.mi.ssxff am tzr
nls_timestamp_tz_format dd-mon-rr hh.mi.ssxff am tzr
nls_dual_currency $
nls_comp binary
nls_length_semantics byte
nls_nchar_conv_excp false
nls_nchar_characterset utf8
nls_rdbms_version 11.2.0.4.0

20 rows selected.

1、节点2 先停掉，在节点1修改完成后再启动
[root@node2 ~]# ls -l /u01/app/11.2.0/grid/bin/crsctl
-rwxr-xr-x 1 root oinstall 8576 jan 13 2017 /u01/app/11.2.0/grid/bin/crsctl
[root@node2 ~]#
[root@node2 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster
crs-2673: attempting to stop 'ora.crsd' on 'node2'
crs-2790: starting shutdown of cluster ready services-managed resources on 'node2'
crs-2673: attempting to stop 'ora.listener_scan1.lsnr' on 'node2'
crs-2673: attempting to stop 'ora.listener.lsnr' on 'node2'
crs-2673: attempting to stop 'ora.crsdg.dg' on 'node2'
crs-2673: attempting to stop 'ora.testdb.db' on 'node2'
crs-2677: stop of 'ora.listener_scan1.lsnr' on 'node2' succeeded
crs-2673: attempting to stop 'ora.scan1.vip' on 'node2'
crs-2677: stop of 'ora.listener.lsnr' on 'node2' succeeded
crs-2673: attempting to stop 'ora.node2.vip' on 'node2'
crs-2677: stop of 'ora.scan1.vip' on 'node2' succeeded
crs-2672: attempting to start 'ora.scan1.vip' on 'node1'
crs-2677: stop of 'ora.node2.vip' on 'node2' succeeded
crs-2672: attempting to start 'ora.node2.vip' on 'node1'
crs-2677: stop of 'ora.testdb.db' on 'node2' succeeded
crs-2673: attempting to stop 'ora.data.dg' on 'node2'
crs-2673: attempting to stop 'ora.fra.dg' on 'node2'
crs-2677: stop of 'ora.data.dg' on 'node2' succeeded
crs-2677: stop of 'ora.fra.dg' on 'node2' succeeded
crs-2676: start of 'ora.scan1.vip' on 'node1' succeeded
crs-2672: attempting to start 'ora.listener_scan1.lsnr' on 'node1'
crs-2676: start of 'ora.node2.vip' on 'node1' succeeded
crs-2676: start of 'ora.listener_scan1.lsnr' on 'node1' succeeded
crs-2677: stop of 'ora.crsdg.dg' on 'node2' succeeded
crs-2673: attempting to stop 'ora.asm' on 'node2'
crs-2677: stop of 'ora.asm' on 'node2' succeeded
crs-2673: attempting to stop 'ora.ons' on 'node2'
crs-2677: stop of 'ora.ons' on 'node2' succeeded
crs-2673: attempting to stop 'ora.net1.network' on 'node2'
crs-2677: stop of 'ora.net1.network' on 'node2' succeeded
crs-2792: shutdown of cluster ready services-managed resources on 'node2' has completed
crs-2677: stop of 'ora.crsd' on 'node2' succeeded
crs-2673: attempting to stop 'ora.ctssd' on 'node2'
crs-2673: attempting to stop 'ora.evmd' on 'node2'
crs-2673: attempting to stop 'ora.asm' on 'node2'
crs-2677: stop of 'ora.evmd' on 'node2' succeeded
crs-2677: stop of 'ora.asm' on 'node2' succeeded
crs-2673: attempting to stop 'ora.cluster_interconnect.haip' on 'node2'
crs-2677: stop of 'ora.cluster_interconnect.haip' on 'node2' succeeded
crs-2677: stop of 'ora.ctssd' on 'node2' succeeded
crs-2673: attempting to stop 'ora.cssd' on 'node2'
crs-2677: stop of 'ora.cssd' on 'node2' succeeded
[root@node2 ~]#

2、节点1

primary-sys@testdb1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string +data/testdb/parameterfile/spf
ile.344.1016736315
primary-sys@testdb1>create pfile from spfile;
--这样的话就直接修改上面生成的pfile文件中cluster_database=false 用pfile mount +修改internal_use + open ，然后再创建spfile共节点2一起使用

--下面没必要修改spfile，保持spfile（两节点共享的）中cluster_database=true
--alter system set cluster_database=false;
primary-sys@testdb1>alter system set cluster_database=false scope=spfile;

system altered.

--需要【重启】才能生效，尽管上面已经修改了
primary-sys@testdb1>show parameter cluster_database

name type value
------------------------------------ --------------------------------- ------------------------------
cluster_database boolean true
cluster_database_instances integer 2
primary-sys@testdb1>shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.

--mv inittestdb1.ora inittestdb1.ora.bak，最后又mv回来了，没改回就报下面的错了
primary-sys@testdb1>startup mount;
ora-01078: failure in processing system parameters
lrm-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_home1/dbs/inittestdb1.ora'

primary-sys@testdb1>startup mount;
oracle instance started.

total system global area 7.4826e+10 bytes
fixed size 2261048 bytes
variable size 4.6976e+10 bytes
database buffers 2.7649e+10 bytes
redo buffers 199049216 bytes
database mounted.
primary-sys@testdb1>alter system enable restricted session;

system altered.

primary-sys@testdb1>alter system set job_queue_processes=0;

system altered.

primary-sys@testdb1>alter system set aq_tm_processes=0;

system altered.

primary-sys@testdb1>alter database open;

database altered.
--这一步是【重点要修改的】
primary-sys@testdb1>alter database national character set internal_use al16utf16;

database altered.

--pfile启动了，没法修改spfile了
primary-sys@testdb1>alter system set cluster_database=true scope=spfile sid='*';
alter system set cluster_database=true scope=spfile sid='*'
*
error at line 1:
ora-32001: write to spfile requested but no spfile is in use

primary-sys@testdb1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string

--手动修改inittestdb1.ora中的cluster_database=true，重建spfile
primary-sys@testdb1>create spfile from pfile='/u01/app/oracle/product/11.2.0/db_home1/dbs/inittestdb1.ora';

file created.

primary-sys@testdb1>shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
primary-sys@testdb1>startup mount;
oracle instance started.

total system global area 7.4826e+10 bytes
fixed size 2261048 bytes
variable size 4.6976e+10 bytes
database buffers 2.7649e+10 bytes
redo buffers 199049216 bytes
database mounted.
--还得改回去，0->1
primary-sys@testdb1>alter system disable restricted session;

system altered.

primary-sys@testdb1>alter system set job_queue_processes=1;

system altered.

primary-sys@testdb1>alter system set aq_tm_processes=1;

system altered.

primary-sys@testdb1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_home1/dbs/spfiletestdb1.or
a
primary-sys@testdb1>alter system set cluster_database=true scope=spfile sid='*';

system altered.

primary-sys@testdb1>alter database open;

database altered.

--cluster_database【重启】才生效

primary-sys@testdb1>show parameter cluster_database

name type value
------------------------------------ --------------------------------- ------------------------------
cluster_database boolean false
cluster_database_instances integer 1
primary-sys@testdb1>shut immediate
database closed.
database dismounted.
oracle instance shut down.
primary-sys@testdb1>
primary-sys@testdb1>
primary-sys@testdb1>
primary-sys@testdb1>startup
oracle instance started.

total system global area 7.4826e+10 bytes
fixed size 2261048 bytes
variable size 4.9392e+10 bytes
database buffers 2.5233e+10 bytes
redo buffers 199049216 bytes
database mounted.
database opened.
primary-sys@testdb1>show parameter cluster_database

name type value
------------------------------------ --------------------------------- ------------------------------
cluster_database boolean true
cluster_database_instances integer 2
primary-sys@testdb1>

primary-sys@testdb1>set pagesize 100
primary-sys@testdb1>col value$ for a30
primary-sys@testdb1>select name,value$ from props$ where name like '%nls%';

name value$
------------------------------------------------------------------------------------------ ------------------------------
nls_language american
nls_territory america
nls_currency $
nls_iso_currency america
nls_numeric_characters .,
nls_characterset zhs16gbk
nls_calendar gregorian
nls_date_format dd-mon-rr
nls_date_language american
nls_sort binary
nls_time_format hh.mi.ssxff am
nls_timestamp_format dd-mon-rr hh.mi.ssxff am
nls_time_tz_format hh.mi.ssxff am tzr
nls_timestamp_tz_format dd-mon-rr hh.mi.ssxff am tzr
nls_dual_currency $
nls_comp binary
nls_length_semantics byte
nls_nchar_conv_excp false
--发现已【修改】成功
nls_nchar_characterset al16utf16
nls_rdbms_version 11.2.0.4.0

20 rows selected.

primary-sys@testdb1>

3、第二个节点启动
[root@node2 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster
crs-2672: attempting to start 'ora.cssdmonitor' on 'node2'
crs-2676: start of 'ora.cssdmonitor' on 'node2' succeeded
crs-2672: attempting to start 'ora.cssd' on 'node2'
crs-2672: attempting to start 'ora.diskmon' on 'node2'
crs-2676: start of 'ora.diskmon' on 'node2' succeeded
crs-2676: start of 'ora.cssd' on 'node2' succeeded
crs-2672: attempting to start 'ora.ctssd' on 'node2'
crs-2676: start of 'ora.ctssd' on 'node2' succeeded
crs-2672: attempting to start 'ora.evmd' on 'node2'
crs-2672: attempting to start 'ora.cluster_interconnect.haip' on 'node2'
crs-2676: start of 'ora.evmd' on 'node2' succeeded
crs-2676: start of 'ora.cluster_interconnect.haip' on 'node2' succeeded
crs-2672: attempting to start 'ora.asm' on 'node2'
crs-2676: start of 'ora.asm' on 'node2' succeeded
crs-2672: attempting to start 'ora.crsd' on 'node2'
crs-2676: start of 'ora.crsd' on 'node2' succeeded

--设置了自动重启，所以失败。。。
primary-sys@testdb2>startup mount
ora-10997: another startup/shutdown operation of this instance inprogress
ora-09968: unable to lock file
linux-x86_64 error: 11: resource temporarily unavailable
additional information: 169786
。。。自启动了。。。

--稍等发现已启动ok
primary-sys@testdb2>select inst_id,instance_name,status from gv$instance;

inst_id instance_name status
---------- ------------------------------------------------ ------------------------------------
2 testdb2 open
1 testdb1 open

2 rows selected.

自此两个节点都ok了

【总结】
上面可能说的有点乱，捋一捋。。。不知道说的对不对
0、做事之前要盘算计划好，眼高手低是技术一大障碍，说来都很美好，做起来总不是那么一帆风顺的，稍微一个错误浪费的时间比事前多花点时间准备好多了，当然牛人除外，能够及时处理。
1、根据节点1生成的pfile，修改cluster_database=false启动修改，然后再改回来是不是少点麻烦
2、修改字符集要关闭一个节点，在另外一个节点修改，修改前要把这个节点的cluster_database改成false（别改spfile，spfile是两个节点公用的，改了等下又要改回来，重复工作！），重启（才生效），修改时按照上面mount之后操作即可，修改后再把0改成1，cluster_database再改成true，重启（生效），启动节点2（还是修改之前的spfile额，cluster_database仍为true），结束。

【小插曲】两节点不从asm中的spfile启动了
primary-sys@dinpay1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_home1/dbs/spfiledinpay1.or
a
primary-sys@dinpay1>create pfile from spfile;

file created.

primary-sys@dinpay1>create spfile from pfile='/u01/app/oracle/product/11.2.0/db_home1/dbs/initdinpay1.ora';
create spfile from pfile='/u01/app/oracle/product/11.2.0/db_home1/dbs/initdinpay1.ora'
*
error at line 1:
ora-32002: cannot create spfile already being used by the instance

primary-sys@dinpay1>shut immediate

primary-sys@dinpay1>startup pfile='/u01/app/oracle/product/11.2.0/db_home1/dbs/initdinpay1.ora';

primary-sys@dinpay1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string
primary-sys@dinpay1>create spfile='+data' from pfile='/u01/app/oracle/product/11.2.0/db_home1/dbs/initdinpay1.ora';

file created.
primary-sys@dinpay1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string
primary-sys@dinpay1>shut immediate

--grid登陆查找生成spfile位置
asmcmd> cd +data/dinpay/parameterfile/
asmcmd> ls
spfile.282.1016709123
spfile.343.1016734531
spfile.344.1016736315
spfile.346.1025548589
--刚刚生成的
+data/dinpay/parameterfile/spfile.346.1025548589

--更新pfile，别这样create pfile from spfile;指定pfile生成位置
[oracle@zhjlrac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_home1/dbs
[oracle@szml02-db01 dbs]$ cat initdinpay1.ora
spfile='+data/dinpay/parameterfile/spfile.346.1025548589'

primary-sys@dinpay1>startup
oracle instance started.

total system global area 7.4826e+10 bytes
fixed size 2261048 bytes
variable size 4.9124e+10 bytes
database buffers 2.5501e+10 bytes
redo buffers 199049216 bytes
database mounted.
database opened.
primary-sys@dinpay1>

primary-sys@dinpay1>show parameter pfile;

name type value
------------------------------------ --------------------------------- ------------------------------
spfile string +data/dinpay/parameterfile/spf
ile.346.1025548589
另外一个节点页如上指向这个spfile，重启ok。

如果直接使用create pfile from spfile;命令创建pfile，那么生成的pfile 文件将覆盖原有$oracle_home/dbs 目录下的pfile 文件。 而在之前的pfile文件里面值保留了一条指向spfile存放位置的记录。 这样修改之后，就会造成数据库启动时会因为找不到spfile文件而读取本地的pfile文件，而不是共享设备上的spfile文件。这样对参数管理上就会带来麻烦，也带来其他的隐患。
所以对于rac，要慎用 create pfile from spfile; 来创建pfile 文件， 在创建的时候，尽量指定pfile的生成位置。