RAC 修改字符集 AL32UTF8-->UTF8

最新推荐文章于 2023-06-05 14:37:50 发布

babymouse1212

最新推荐文章于 2023-06-05 14:37:50 发布

阅读量509

点赞数

分类专栏：经验收集

本文链接：https://blog.csdn.net/babymouse1212/article/details/71079045

版权

经验收集专栏收录该内容

84 篇文章 0 订阅

订阅专栏

大体步骤如下（2节点):

关闭第一个节点

SQL> shutdown immediate
./crsctl stop crs

第二个节点:

SQL> alter system set cluster_database=false scope=spfile;
SQL> shutdown immediate
SQL> startup mount
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> alter database open;
SQL> ALTER DATABASE character set INTERNAL_USE UTF8;
SQL> alter system set cluster_database=true scope=spfile;
SQL> shutdown immediate
SQL> startup

启动第二个节点，并查看新字符集

./crsctl start crs

SQL> startup

SQL> col parameter format a28
SQL> col value format a35
select * from nls_database_parameters;

具体操作：

Data Mining and Real Application Testing options

SQL> show parameter clu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3522E+11 bytes
Fixed Size                  2220072 bytes
Variable Size            6.8719E+10 bytes
Database Buffers         6.6035E+10 bytes
Redo Buffers              465129472 bytes
Database mounted.
SQL> show parameter clu

System altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> alter database open;

Database altered.

SQL> show parameter clu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> ALTER DATABASE character set INTERNAL_USE UTF8;
ALTER DATABASE character set INTERNAL_USE UTF8
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30556: functional index is defined on the column to be modified

--报错，由函数索引引起，查出来Drop掉

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
UTF8

SQL> SET LONG 2000000
SQL> SET pagesize 0
SQL> EXECUTE dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_metadata.get_ddl('INDEX',index_name,owner)
FROM dba_indexes
WHERE index_type IN ('DOMAIN','FUNCTION-BASED NORMAL','FUNCTION-BASED NORMAL/REV','FUNCTION-BASED BITMAP')
AND table_name IN
(SELECT UNIQUE (x.table_name) FROM dba_tab_columns x WHERE x.char_used ='C' AND x.data_type IN ('CHAR','VARCHAR2')); 2 3 4 5
ERROR:
ORA-31603: object "W_DAY_D_M39" of type INDEX not found in schema "PU_VIEW"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

no rows selected

SQL> SELECT owner,
index_name,
table_owner,
table_name,
status,
index_type
FROM dba_indexes
WHERE index_type IN ('DOMAIN','FUNCTION-BASED NORMAL','FUNCTION-BASED NORMAL/REV','FUNCTION-BASED BITMAP')
AND table_name IN
(SELECT UNIQUE (x.table_name) FROM dba_tab_columns x WHERE x.char_used ='C' AND x.data_type IN ('CHAR','VARCHAR2'))
ORDER BY 1,2
2    3    4    5    6    7    8    9   10   11   12
SQL> SELECT owner,
index_name,
table_owner,
table_name,
status,
index_type
FROM dba_indexes
WHERE index_type IN ('DOMAIN','FUNCTION-BASED NORMAL','FUNCTION-BASED NORMAL/REV','FUNCTION-BASED BITMAP')
AND table_name IN
(SELECT UNIQUE (x.table_name) FROM dba_tab_columns x WHERE x.char_used ='C' AND x.data_type IN ('CHAR','VARCHAR2'))
ORDER BY 1,2
2    3    4    5    6    7    8    9   10   11   12 /
PU_VIEW                        W_DAY_D_M39
PU_VIEW                        W_DAY_D                        VALID
FUNCTION-BASED NORMAL

SQL> drop index PU_VIEW.W_DAY_D_M39;

Index dropped.

SQL> ALTER DATABASE character set INTERNAL_USE UTF8;

Database altered.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.3522E+11 bytes
Fixed Size                  2220072 bytes
Variable Size            6.8719E+10 bytes
Database Buffers         6.6035E+10 bytes
Redo Buffers              465129472 bytes
Database mounted.
Database opened.
SQL> show parameter job
job_queue_processes                  integer     0
SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
NLS_CHARACTERSET
UTF8