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

一 准备工作1. 确认字符集为保证数据一致，新旧数据库的字符集必须统一。查询语句：select * from V$NLS_PARAMETERS where parameter in (NLS_CHARACTERSET,NLS_NCHAR_CHARACTERSET);MES 10g数据库字符集为PARAMETERVALUENLS_CHARACTERSETZHS16GBKNLS_NCHAR_CHARACTERSETAL16UTF162. 确认用户角色首先在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_DBANOYESMESPRODCONNECTNOYES3. 新建表空间在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;4. 新建用户在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小时，如果在服务器上导出，需要修改路径)exp mesprod/mesprod10.10.10.100:1521/MES full=y file=e:mesprod.dmp log=e:mesprodlog owner=(MESPROD,LBLPROD) 导出过程中，遇到的报错及解决方式：报错1：EXP-00008: ORACLE error 6550 encountered ORA-06550: line 1, column 18: PLS-00201: identifier SYS.DBMS_DEFER_IMPORT_INTERNAL must be declared 解决方法：GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO mesprod ;GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO lblprod ;报错2：EXP-00008: ORACLE error 6510 encountered ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at SYS.DBMS_EXPORT_EXTENSION, line 50解决方法：GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO mesprod;GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO lblprod ;PS：导出过程中的exp00091的错误，通常修改nls_lang环境变量即可解决，可以直接忽略这个错误。三 导入数据库导入语句：(耗时约2.5小时，注意路径)imp mesprod/mesprod full=y file=/opt/mesprod.dmp log=/opt/importlog.log 测试时，由于修改了数据库IP，所以dblink没有新建成功，需要重建，语句如下(正式导入时，应该不需要，注意service_name)1. 在MESPROD用户下重建dblink TOLABEL- Create database link create database link TOLABEL connect to LBLPROD identified by lblprod using (DESCRIPTION = (ADDRESS_LIST = (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.22.236) (PORT=1521) ) (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.22.237) (PORT=1521) ) )(CONNECT_DATA =(SERVICE_NAME = mesdb) );2. 在MESPROD用户下重建物化视图MV_LABEL_HISTORY- Create database linkCREATE MATERIALIZED VIEW MV_LABEL_HISTORYREFRESH FAST ON DEMANDSTART WITH TO_DATE(11-03-2016 13:17:49, DD-MM-YYYY HH24:MI:SS) NEXT SYSDATE+1/1440 ASSELECT ID OBJECT_RRN, lot, custid, prodid, qty, mesfld01, mesfld02, mesfld03, mesfld04, mesfld05, mesfld06, mesfld07, mesfld08, mesfld09, mesfld10, packrelid, deleted, ctime, creator, 0 as org_rrn, Y as is_active FROM tbl_pdatatolabel;3. 在LBLPROD用户下重建dblink TOMES - Create database link create database link TOMES connect to MESPROD