数据库(10g to 11g)迁移流程.docx

准备工作确认字符集为保证数据一致，新旧数据库的字符集必须统一。查询语句：select * from V$NLS_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');MES 10g数据库字符集为PARAMETERVALUENLS_CHARACTERSETZHS16GBKNLS_NCHAR_CHARACTERSETAL16UTF16确认用户角色首先在10g数据库上查询当前用户的角色，之后在11g库中查询刚才的用户所拥有的角色在11g库中是否存在。查询语句：select * from dba_role_privs where grantee IN ('MESPROD','LBLPROD');每个应用用户均需查询，如果发现有系统默认没有的用户自建角色，需要在11g库中新建该角色，角色创建语句可以在10g库中由plsql developer软件进行自动生成。MES 10g数据库用户角色为（无自建角色）GRANTEEGRANTED_ROLEADMIN_OPTIONDEFAULT_ROLELBLPRODRESOURCENOYESLBLPRODCONNECTNOYESLBLPRODMW_ROLE_DBANOYESLBLPRODDBANOYESMESPRODDBANOYESMESPRODRESOURCENOYESMESPRODMW_ROLE_DBANOYESMESPRODCONNECTNOYES新建表空间在11g数据库中新建以下表空间： CREATE TABLESPACE "TS_MES_DAT" SIZE 1G maxsize unlimited; CREATE TABLESPACE "TS_HISTORY_DAT" SIZE 1G maxsize unlimited; CREATE TABLESPACE "TS_MES_IDX" SIZE 1G maxsize unlimited; CREATE TABLESPACE "TS_HISTORY_IDX" SIZE 1G maxsize unlimited; CREATE TABLESPACE "TS_LABEL_DAT" SIZE 1G maxsize unlimited; CREATE TABLESPACE "TS_LABEL_IDX" SIZE 1G maxsize unlimited;新建用户在11g数据库中新建以下用户：-- Create the user create user MESPROD identified by mesprod default tablespace USERS temporary tablespace TEMP profile DEFAULT;-- Grant/Revoke object privileges grant execute on SYS.DBMS_DEFER_IMPORT_INTERNAL to MESPROD;grant execute on SYS.DBMS_EXPORT_EXTENSION to MESPROD;-- Grant/Revoke role privileges grant connect to MESPROD;grant dba to MESPROD;grant mw_role_dba to MESPROD;grant resource to MESPROD;-- Grant/Revoke system privileges grant create any index to MESPROD;grant create any table to MESPROD;grant drop any table to MESPROD;grant unlimited tablespace to MESPROD;-- Create the user create user LBLPROD identified by lblprod default tablespace USERS temporary tablespace TEMP profile DEFAULT;-- Grant/Revoke role privileges grant connect to LBLPROD;grant dba to LBLPROD;grant mw_role_dba to LBLPROD;grant resource to LBLPROD;-- Grant/Revoke system privileges grant unlimited tablespace to LBLPROD;导出数据库导出语句：(耗时约1小时，如果在服务器上导出，需要修改路径)ex