Oracle字符集从GBK升级到Utf8的方法是什么

本篇内容介绍了"Oracle字符集从GBK升级到Utf8的方法是什么"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1、导出前准备(单实例单监听)

a.关闭zabbix 监控

配置--主机--primary-new-qpdb

b.停应用停监听杀会话

lsnrctl stop LISTENER

杀会话:

export ORACLE_SID=qpprips -ef|grep $ORACLE_SID|grep -v ORA_|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

c.确保无用户会话、无事务:

set linesize 1000set pagesize 3000col event for a30col status for a10col blkses for 99999col username for a14col module for a45col program for a40col machine for a25col state for a20col cmd for a23col sql_id for a20select s.inst_id,s.sid,s.event,s.state,s.status,s.last_call_et lcet,s.sql_id,s.username,c.command_name cmd,s.module,s.program,s.machinefrom gv$session s,gv$sqlcommand cwhere s.type='USER'and s.inst_id=c.inst_idand s.command=c.command_typeorder by s.username,s.sql_id,s.module,s.program;select 'alter system disconnect session '''||sid||','||serial#||''' immediate;' from v$session where type='USER';alter system disconnect session '2375,38626' immediate;select inst_id,START_TIME,(sysdate-to_date(START_TIME,'mm/dd/yy hh34:mi:ss'))*24 eslaped_hours,USED_UBLK*8/1024 MB from gv$transaction order by 4,3;

d.主库切几次日志,并确保备库无延迟,设置主库远程归档路径为defer

主库：

alter system switch logfile;

备库：

col name for a20col value for a20col unit for a30col TIME_COMPUTED for a20col DATUM_TIME for a20set linesize 1000select * from v$dataguard_stats;

主库：

alter system set log_archive_dest_state_2='defer' scope=both;

e.备库停止应用,并关闭

alter database recover managed standby database cancel;shutdown immmediate;

f.导出数据

vi exp_qp.sh#!/bin/bash. /home/oracle/.bash_profileexport ORACLE_SID=qppriexport NLS_LANG=American_America.AL32UTF8#####variable SCHES#######SCHES="CONFIG,DEVQ_DP,ACCOUNT,BOPS,CONFIGSVR,MESSAGE,OPENFIRE,MON,IDEXCHANGE,QPWEB,QP_MAPI_BASE,QP_MJCORE_BASE,QP_CORE_BASE,QP_CRM_BASE,CIF_BASE,IDEXCHANGE_BASE,GOLD_COIN_CORE_BASE,SS_CORE_BASE,SS_SYN_OUT_BASE,BENCH_CAPTCHA_BASE,CONFIG_SERVER_DISTRIB_BASE,DEV_BANXIA,BOPS_BASE,BOPS_COMMON_BASE,OA_FRONT_BASE,KEY_CORE_BASE,BOPS_QP_BASE"expdp \'/ as sysdba\' schemas=${SCHES}  directory=EXPDP_DATA dumpfile=qpasdwsx_%U.dmp parallel=4 logfile=expdp_qp.log compression=all

g.查出存在直方图统计信息的列：

export NLS_LANG=American_America.AL32UTF8vi zft.sqlset echo offset termout offset linesize 1000col cmd for a160set pagesize 0set feedback offset heading offset trimout onset trimspool onspool   '/home/oracle/cy/stats.sql'select 'select '||column_name||' from '||owner||'.'||table_name||' where '||column_name||' is not null and 1=2;' cmdfrom dba_tab_col_statistics where histogram <>'NONE'and table_name not like 'BIN$%'and owner in ('CONFIG','DEVQ_DP','ACCOUNT','BOPS','CONFIGSVR','MESSAGE','OPENFIRE','MON','IDEXCHANGE','DW_QPDB','QPWEB','QP_MAPI_BASE','QP_MJCORE_BASE','QP_CORE_BASE','QP_CRM_BASE','CIF_BASE','IDEXCHANGE_BASE','GOLD_COIN_CORE_BASE','SS_CORE_BASE','SS_SYN_OUT_BASE','BENCH_CAPTCHA_BASE','CONFIG_SERVER_DISTRIB_BASE','DW_USER','DEV_BANXIA','BOPS_BASE','BOPS_COMMON_BASE','OA_FRONT_BASE','KEY_CORE_BASE','BOPS_QP_BASE');spool offset feedback onset heading onset termout onset echo on

h.删除数据

vi duser.sqldrop user CONFIG                         cascade; drop user DEVQ_DP                        cascade;drop user ACCOUNT                        cascade;drop user BOPS                           cascade;drop user CONFIGSVR                      cascade;drop user MESSAGE                        cascade;drop user OPENFIRE                       cascade;drop user MON                            cascade;drop user IDEXCHANGE                     cascade;drop user DW_QPDB                        cascade;drop user QPWEB                          cascade;drop user QP_MAPI_BASE                   cascade;drop user QP_MJCORE_BASE                 cascade;drop user QP_CORE_BASE                   cascade;drop user QP_CRM_BASE                    cascade;drop user CIF_BASE                       cascade;drop user IDEXCHANGE_BASE                cascade;drop user GOLD_COIN_CORE_BASE            cascade;drop user SS_CORE_BASE                   cascade;drop user SS_SYN_OUT_BASE                cascade;drop user BENCH_CAPTCHA_BASE             cascade;drop user CONFIG_SERVER_DISTRIB_BASE     cascade;drop user DEV_BANXIA                     cascade;drop user BOPS_BASE                      cascade;drop user BOPS_COMMON_BASE               cascade;drop user OA_FRONT_BASE                  cascade;drop user KEY_CORE_BASE                  cascade;drop user BOPS_QP_BASE                   cascade;

i.手动删除DW_USER用户的视图：

select lOWER(OWNER)||'.'||lower(view_name) from dba_views where owner='DW_USER';vi dview.sqldrop view dw_user.qpmjc_message_push_setting               ;drop view dw_user.qpmjc_login_info                         ;drop view dw_user.qpmjc_game_winning_rule_link             ;drop view dw_user.qpmjc_game_winning_rule_config           ;drop view dw_user.qpmjc_game_rule_config                   ;drop view dw_user.qpmjc_client_push_msg_his                ;drop view dw_user.qpmjc_client_push_msg                    ;drop view dw_user.qpmjc_board_wall_card                    ;drop view dw_user.qpmjc_board_user_settle_detail           ;drop view dw_user.qpmjc_board_user_settle                  ;drop view dw_user.qpmjc_board_user                         ;drop view dw_user.qpmjc_board_ro_usr_act_fbd               ;drop view dw_user.qpmjc_board_round_match_group            ;drop view dw_user.qpmjc_board_round_match                  ;drop view dw_user.qpmjc_board_round                        ;drop view dw_user.qpmjc_board_meld_group_card              ;drop view dw_user.qpmjc_board_meld_group                   ;drop view dw_user.qpmjc_board_hand_card                    ;drop view dw_user.qpmjc_board_discard                      ;drop view dw_user.qpmjc_board_act_msg_seq                  ;drop view dw_user.qpmjc_board_act_card_link                ;drop view dw_user.qpmjc_board_action                       ;drop view dw_user.qpmjc_board                              ;drop view dw_user.qpmjc_base_card_config                   ;drop view dw_user.qpc_room_user                            ;drop view dw_user.qpc_room_purchase_fund_bill              ;drop view dw_user.qpc_room_property_config_link            ;drop view dw_user.qpc_room_dismiss_apply                   ;drop view dw_user.qpc_room_dismiss_apl_user_chos           ;drop view dw_user.qpc_room                                 ;drop view dw_user.qpc_role_user_link                       ;drop view dw_user.qpc_role_authority_link                  ;drop view dw_user.qpc_role                                 ;drop view dw_user.qpc_property_value_config                ;drop view dw_user.qpc_property_config                      ;drop view dw_user.qpc_group_user_invite_join_his           ;drop view dw_user.qpc_group_user_invite_join               ;drop view dw_user.qpc_group_user_apply_join_his            ;drop view dw_user.qpc_group_user_apply_join                ;drop view dw_user.qpc_group_user                           ;drop view dw_user.qpc_group_type_config                    ;drop view dw_user.qpc_group_play_prop_cfg_link             ;drop view dw_user.qpc_group_playway_room_link              ;drop view dw_user.qpc_group_playway                        ;drop view dw_user.qpc_group_notice                         ;drop view dw_user.qpc_group                                ;drop view dw_user.qpc_game_sort                            ;drop view dw_user.qpc_game                                 ;drop view dw_user.qpc_area_sort                            ;drop view dw_user.qpcm_staff_role_link                     ;drop view dw_user.qpcm_staff_role                          ;drop view dw_user.qpcm_staff_group                         ;drop view dw_user.qpcm_staff                               ;drop view dw_user.gldcoin_trans_code                       ;drop view dw_user.gldcoin_sub_trans_code                   ;drop view dw_user.gldcoin_general_account                  ;drop view dw_user.gldcoin_freeze_type                      ;drop view dw_user.gldcoin_freeze                           ;drop view dw_user.gldcoin_deposit_type                     ;drop view dw_user.gldcoin_deposit                          ;drop view dw_user.gldcoin_charge_biz_type                  ;drop view dw_user.gldcoin_account_type                     ;drop view dw_user.gldcoin_account_log                      ;drop view dw_user.gldcoin_account                          ;drop view dw_user.cif_user                                 ;

2、改字符集

shutdown immediate;startup mount;alter system enable restricted session;alter system set job_queue_processes=0; alter system set aq_tm_processes=0; alter database open; ALTER DATABASE character set INTERNAL_USE AL32UTF8;ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;shutdown immediate;startup;set lines 1000;select * from nls_database_parameters;alter system set job_queue_processes=1000;alter system set aq_tm_processes=1;

3、导入数据

导入元数据

vi imp_qp.sh#!/bin/bash. /home/oracle/.bash_profileexport ORACLE_SID=qppriexport NLS_LANG=American_America.AL32UTF8impdp \'/ as sysdba\' directory=EXPDP_DATA dumpfile=qpasdwsx_%U.dmp parallel=4 logfile=impdp_qp.log content=metadata_only

修改字段

因为字符集不同字段宽度不够，需要提前测试

alter table CONFIG.CFG_CN_CHAR_DICT modify value CHAR(3);alter table DATA_HANYU_CORE_BASE.HANZI modify HANZI VARCHAR2(6);... ...alter table BOPS_BASE.BPBS_SORT_MENU modify SORT_MENU_NAME VARCHAR2(48);alter table SS_SYN_OUT_BASE.OABASE_ROLE modify ROLE_NAME VARCHAR2(48);

导入数据

impdp \'/ as sysdba\'  directory=EXPDP_DATA dumpfile=qpasdwsx_%U.dmp parallel=4 logfile=impdp_qp.log exclude=statistics table_exists_action=append

创建DW_USER的视图：

运行脚本创建非加密视图：

dw_user.qpmjc_game_winning_rule_linkdw_user.qpmjc_game_winning_rule_configdw_user.qpmjc_game_rule_configdw_user.qpmjc_client_push_msg_hisdw_user.qpmjc_client_push_msgdw_user.qpmjc_board_wall_carddw_user.qpmjc_board_user_settle_detaildw_user.qpmjc_board_user_settledw_user.qpmjc_board_userdw_user.qpmjc_board_ro_usr_act_fbddw_user.qpmjc_board_round_match_groupdw_user.qpmjc_board_round_matchdw_user.qpmjc_board_rounddw_user.qpmjc_board_meld_group_carddw_user.qpmjc_board_meld_groupdw_user.qpmjc_board_hand_carddw_user.qpmjc_board_discarddw_user.qpmjc_board_act_msg_seqdw_user.qpmjc_board_act_card_linkdw_user.qpmjc_board_actiondw_user.qpmjc_boarddw_user.qpmjc_base_card_configdw_user.qpc_room_userdw_user.qpc_room_purchase_fund_billdw_user.qpc_room_property_config_linkdw_user.qpc_room_dismiss_applydw_user.qpc_room_dismiss_apl_user_chosdw_user.qpc_roomdw_user.qpc_role_user_linkdw_user.qpc_role_authority_linkdw_user.qpc_roledw_user.qpc_property_value_configdw_user.qpc_property_configdw_user.qpc_group_user_invite_join_hisdw_user.qpc_group_user_invite_joindw_user.qpc_group_user_apply_join_hisdw_user.qpc_group_user_apply_joindw_user.qpc_group_userdw_user.qpc_group_type_configdw_user.qpc_group_play_prop_cfg_linkdw_user.qpc_group_playway_room_linkdw_user.qpc_group_playwaydw_user.qpc_group_noticedw_user.qpc_groupdw_user.qpc_game_sortdw_user.qpc_gamedw_user.qpc_area_sortdw_user.qpcm_staff_role_linkdw_user.qpcm_staff_roledw_user.qpcm_staff_groupdw_user.qpcm_staffdw_user.gldcoin_trans_codedw_user.gldcoin_sub_trans_codedw_user.gldcoin_general_accountdw_user.gldcoin_freeze_typedw_user.gldcoin_freezedw_user.gldcoin_deposit_typedw_user.gldcoin_depositdw_user.gldcoin_charge_biz_typedw_user.gldcoin_account_typedw_user.gldcoin_account_logdw_user.gldcoin_accountdw_user.qpmjc_message_push_setting   视图失效，引用的表对象不存在，无需创建dw_user.qpmjc_login_info             视图失效，引用的表对象不存在，无需创建gold_coin_core_base.gldcoin_accountgold_coin_core_base.gldcoin_account_loggold_coin_core_base.gldcoin_account_typegold_coin_core_base.gldcoin_charge_biz_typegold_coin_core_base.gldcoin_depositgold_coin_core_base.gldcoin_deposit_typegold_coin_core_base.gldcoin_freezegold_coin_core_base.gldcoin_freeze_typegold_coin_core_base.gldcoin_general_accountgold_coin_core_base.gldcoin_sub_trans_codegold_coin_core_base.gldcoin_trans_codeqp_core_base.qpc_area_sortqp_core_base.qpc_gameqp_core_base.qpc_game_sortqp_core_base.qpc_groupqp_core_base.qpc_group_noticeqp_core_base.qpc_group_playwayqp_core_base.qpc_group_playway_room_linkqp_core_base.qpc_group_play_prop_cfg_linkqp_core_base.qpc_group_type_configqp_core_base.qpc_group_userqp_core_base.qpc_group_user_apply_joinqp_core_base.qpc_group_user_apply_join_hisqp_core_base.qpc_group_user_invite_joinqp_core_base.qpc_group_user_invite_join_hisqp_core_base.qpc_property_configqp_core_base.qpc_property_value_configqp_core_base.qpc_roleqp_core_base.qpc_role_authority_linkqp_core_base.qpc_role_user_linkqp_core_base.qpc_roomqp_core_base.qpc_room_dismiss_apl_user_chosqp_core_base.qpc_room_dismiss_applyqp_core_base.qpc_room_property_config_linkqp_core_base.qpc_room_purchase_fund_billqp_core_base.qpc_room_userqp_crm_base.qpcm_staffqp_crm_base.qpcm_staff_groupqp_crm_base.qpcm_staff_roleqp_crm_base.qpcm_staff_role_linkqp_mjcore_base.qpmjc_base_card_configqp_mjcore_base.qpmjc_boardqp_mjcore_base.qpmjc_board_actionqp_mjcore_base.qpmjc_board_act_card_linkqp_mjcore_base.qpmjc_board_act_msg_seqqp_mjcore_base.qpmjc_board_discardqp_mjcore_base.qpmjc_board_hand_cardqp_mjcore_base.qpmjc_board_meld_groupqp_mjcore_base.qpmjc_board_meld_group_cardqp_mjcore_base.qpmjc_board_roundqp_mjcore_base.qpmjc_board_round_matchqp_mjcore_base.qpmjc_board_round_match_groupqp_mjcore_base.qpmjc_board_ro_usr_act_fbdqp_mjcore_base.qpmjc_board_userqp_mjcore_base.qpmjc_board_user_settleqp_mjcore_base.qpmjc_board_user_settle_detailqp_mjcore_base.qpmjc_board_wall_cardqp_mjcore_base.qpmjc_client_push_msgqp_mjcore_base.qpmjc_client_push_msg_hisqp_mjcore_base.qpmjc_game_rule_configqp_mjcore_base.qpmjc_game_winning_rule_configqp_mjcore_base.qpmjc_game_winning_rule_link

手动创建加密视图：

create or replace view cif_base.cif_user_view as select     USER_ID,             LOGIN_NAME,          (encryptor(LOGIN_PASSWORD)) LOGIN_PASSWORD,                                                        (encryptor(REAL_NAME))  REAL_NAME,        STATUS,              (encryptor(EMAIL)) EMAIL,                                                                 (encryptor(QQ))  QQ,                  CAN_LOGIN,           CERT_TYPE,           (encryptor(CERT_NO)) cert_no,     substr(CERT_NO,1,6) certno6, substr(CERT_NO,-12,8) birthdaynum,substr(CERT_NO,-2,1) sexnum,                                                                 GMT_CREATE,          GMT_MODIFIED,       EMAIL_VALIDATE,      QQ_VALIDATE,         (encryptor(cell)) cell,    substr(cell,1,7) cell7,             CELL_VALIDATE,       (encryptor(ACCOUNT_PASSWORD)) ACCOUNT_PASSWORD,    NICK_NAME,           USER_TYPE_NAME,      DOMAIN,              SUB_DOMAIN,          GMT_CHANGE_IDENTITY, SEX ,                BIRTHDAY,            ONE_AUTH_ID from cif_base.cif_user with read only;         grant select on cif_base.cif_user_view to dw_user;create view dw_user.cif_user as select *  from cif_base.cif_user_view with read only;

以下视图本不存在，无需创建

create or replace view cif_base.cif_one_auth_view as select     ONE_AUTH_ID,AUTH_NAME,AUTH_NAME_TYPE,GMT_CREATE,GMT_MODIFIED,(encryptor(CELL))  CELL,CELL_VALIDATE,DOMAIN,SUB_DOMAIN,DEFAULT_USER_ID,LOGIN_PASSWORD,CAN_LOGIN, ACCOUNT_PASSWORD,(encryptor(REAL_NAME)) REAL_NAME,(encryptor(EMAIL))  EMAIL,(encryptor(QQ))  QQ, CERT_TYPE,(encryptor(CERT_NO))  CERT_NO, EMAIL_VALIDATE, QQ_VALIDATE, GMT_CHANGE_IDENTITY, SEX, (encryptor(BIRTHDAY))  BIRTHDAY    from    cif_base.cif_one_auth;grant select on cif_base.cif_one_auth_view to dw_user;create view dw_user.cif_one_auth as select *  from cif_base.cif_one_auth_view with read only;授权DW_USER用户：grant CONNECT to DW_USER;grant RESOURCE to DW_USER;grant SELECT on QP_CORE_BASE.QPC_GROUP to DW_USER;grant SELECT on QP_CORE_BASE.QPC_ROOM to DW_USER;grant SELECT on QP_CORE_BASE.QPC_GROUP_USER to DW_USER;grant SELECT on QP_CORE_BASE.QPC_ROOM_PURCHASE_FUND_BILL to DW_USER;grant SELECT on QP_CORE_BASE.QPC_ROOM_USER to DW_USER;grant SELECT on QP_CORE_BASE.QPC_GROUP_PLAYWAY_ROOM_LINK to DW_USER;grant SELECT on QP_CORE_BASE.QPC_GROUP_PLAYWAY to DW_USER;grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD to DW_USER;grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD_USER_SETTLE_DETAIL to DW_USER;grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD_USER to DW_USER;grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD_USER_SETTLE to DW_USER;grant SELECT on QP_CRM_BASE.QPCM_STAFF to DW_USER;grant SELECT on QP_CRM_BASE.QPCM_STAFF_ROLE_LINK to DW_USER;grant SELECT on QP_CRM_BASE.QPCM_STAFF_GROUP to DW_USER;grant SELECT on QP_CRM_BASE.QPCM_STAFF_ROLE to DW_USER;grant SELECT on CIF_BASE.CIF_USER_VIEW to DW_USER;grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_DEPOSIT_TYPE to DW_USER;grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_ACCOUNT_TYPE to DW_USER;grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_ACCOUNT to DW_USER;grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_ACCOUNT_LOG to DW_USER;grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_DEPOSIT to DW_USER;grant UNLIMITED TABLESPACE to DW_USER;

编译失效对象:

@?/rdbms/admin/utlrp.sqlselect owner,object_name,subobject_name,status from dba_objects where status='INVALID';

收集统计信息：

export NLS_LANG=American_America.AL32UTF8@/home/oracle/cy/stats.sqlvi gstats.sh#!/bin/bash. /home/oracle/.bash_profileexport ORACLE_SID=qpprisqlplus / as sysdba < 'gather',degree => 16);exit;ASDEOF

开启监听：

lsnrctl start LISTENER

开启zabbix监控：

配置---主机----primary-new-qpdb

通知应用

导出：10分钟 采用压缩9G 导入：54min

问题：

ORA-39082: Object type ALTER_FUNCTION:"QP_MJCORE_BASE"."SPLIT" created with compilation warnings 原本就无效 ORA-39082: Object type VIEW:"CIF_BASE"."CIF_USER_VIEW" created with compilation warnings 缺少加密函数

原本无效的对象：

OWNER              OBJECT_NAME            SUBOBJECT_NAME             STATUS------------------ --------------------- -------------------------- -------DW_QPDB            CIF_USER_LOG                                     INVALIDDW_QPDB            CIF_USER                                         INVALID... ...115 rows selected.

获取dw_user的权限：

/home/oracle/cy/schemas.txtDW_USER/home/oracle/cy/cuser.sh#!/bin/bash. /home/oracle/.bash_profileexport ORACLE_SID=qppriecho > /home/oracle/cy/cuserfin.sqlcat /home/oracle/cy/schemas.txt|while read line;doline=`echo $line | tr '[:lower:]' '[:upper:]'`rm -f /home/oracle/cy/cuser1.sqlsqlplus -s  / as sysdba <> /home/oracle/cy/cuserfin.sqlecho "                                                                                                                  " >> /home/oracle/cy/cuserfin.sqlcat /home/oracle/cy/cuser1.sql >> /home/oracle/cy/cuserfin.sqlecho "                                                                                                                  " >> /home/oracle/cy/cuserfin.sqlecho "------------------------------------------  SCHEMA: ${line}  END   -----------------------------------------------" >> /home/oracle/cy/cuserfin.sqlecho "                                                                                                                  " >> /home/oracle/cy/cuserfin.sqldone

"Oracle字符集从GBK升级到Utf8的方法是什么"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！