oracle-10.2.0.1升级10.2.0.4报错解决办法

oracle 升级到Oracle 10g R2 安装配置Oracle Patch Set for Windows 32BIT升级步骤升级测试环境：服务器：P4 3.0 1G RAM操作系统：Windows 2003 Server EE SP2 32BIT数据库：Oracle 升级补丁包：Oracle (Oracle 10g R2 Patch Set3)数据库存储：NTFS文件系统一、Oracle Patch Set for Windows 32BIT系统环境要求? 硬件要求： Intel Pentium or AMD 32 bit 处理器? 操作系统要求：o Windows Server 2003o Windows Server 2003 R2o Windows 2000 (Service Pack 2 or later)o Windows XP Professionalo Windows Vista with Service Pack 1 or later? 数据库版本要求：Oracle Database 10g () or later二、 升级安装前准备工作1 关闭数据库:SQL CONNECT SYS AS SYSDBAEnter password: SYS_passwordSQL SHUTDOWN注意：如果shutdown的时候，长时间没有反应，可能是因为部分服务没有关闭。请先关闭服务。如在升级中遇到96%处的错误时可能于下列服务有关。关闭下列服务。SQLemctl stop dbconsole2 停止所有服务1. 关闭所有可能访问数据库的工具, 例如 Oracle Enterprise Manager Database Control or iSQL*Plus:2. C:E:oracleprod t10.2.0db_1binemctl stop dbconsole3. C:E:oracleprod t10.2.0db_1binisqlplusctl stop4. C: lsnrctl stop5. C: sqlplus /NOLOG6. SQL CONNECT SYS AS SYSDBA7. Enter password:SYS_Password8. SQL SHUTDOWN使用控制面板中的服务关闭 Oracle Database service OracleServiceSID SID is the system identifier of the database.三、备份系统备份内容包括： Oracle Inventory, Oracle 10g home 和 Oracle 10g Database.四、安装升级包软件1. 以具有操作系统管理员权限的用户登陆系统.2. 解压升级包，运行setup.exe，画面如下：3. 在欢迎画面单击“下一步”.注意：选择home14. 在指定主目录详细信息画面，选择你想升级的Oracle home，或者选择“路径”, 然后单击“下一步”。5. “下一步”.6. 不启用Oracle Configuration Manager, 单击“下一步”。7. 单击“安装”.8. 进行安装，安装完成后如下画面：9. 在安装结束画面, 单击“退出”, 然后确认退出 Oracle Universal Installer，至此，ORACLE升级包软件安装结束五、运行脚本升级数据库（注意：这步很重要，不可以忽视）如果只安装了升级包，没有进行数据库字典信息升级，启动数据库将报ORA-01092 ORACLE 例程终止。强行断开连接。（这和Oracle9i有点不同，对Oracle9i做了升级后，不升级数据库字典信息，仍然可以启动数据库使用，但对exp/imp,RMAN之类的工具不能使用）。1运行数据库预升级检查( Pre-Upgrade Information Tool)Conn / as sysdbaSTARTUP UPGRADESPOOL upgrade_info.log?/rdbms/admin/utl?i.sql（？代表oracle的盘符路径）SPOOL OFF2 进行数据库字典信息升级（Upgrading a Release 10.2 Database）启动监听器：lsnrctl startsqlplus /NOLOGSQL CONNECT / AS SYSDBASTARTUP UPGRADESQL SPOOL patch.log （打印日志，方便如果发生错误的时候，查找问题）SQL ORACLE_BASEORACLE_HOMErdbmsadmincatupgrd.sqlSQL SPOOL OFF（关闭日志）检查patch.log 文件，如果有问题，重新运行catupgrd.sql。3 重启动数据库:SQL SHUTDOWN IMMEDIATESQL STARTUP4 运行utlrp.sql脚本重新编译无效PL/SQL包SQL ORACLE_BASEORACLE_HOMErdbmsadminutlrp.sql5 运行命令检查所有组件是否升级成功:SQL SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;所有输出为VALID表示升级成功.6 如果使用Oracle Recovery Manager catalog, 需要对catalog进行升级，如下:? C: rman catalog username/passwordalias? RMAN UPGRADE CATALOG; 六、检查基本的数据库工具可用性对数据库中的用户进行EXP和IMP操作，对数据库用RMAN进行备份和恢复操作。确认备份恢复没问题。*新安装了一台数据库服务器，版本是的，因为现在最新的是的。 版本低了，bug多。所以就对数据库做了一个升级。 服务器是windows 2003的系统，都是可视话操作。 下一步的问题。 的patchset 里面也有详细的安装说明。 之做个总结，是因为这次用了DBUA工具，以前做升级，都是用脚本来的。 这次常个新鲜。基本步骤还是差不多。升级步骤如下：1. 从metalink上下载 的patchset， 编号是：p68101892. 关闭数据库， 做个冷备份，防止安装失败。3. 安装patchset 软件, 这里要注意，ORACLE_HOME 要和以前的一样。 4. 执行脚本 或者用 DBUA 工具5. 检查确认升级是否成功。 下面是readme上面的说明：Upgrading a Release 10.2 DatabaseAfter you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:Note:If you do not run the catupgrd.sql script as described in this section and you start up a database for normal operation, then ORA-01092: ORACLE instance terminated. Disconnection forced errors will occur and the error ORA-39700: database must be opened with UPGRADE option will be in the alert log. 1. Log in with administrator privileges.2. For Oracle RAC installations, start listener on each node of the cluster as follows:C: ORACLE_BASEORACLE_HOMEbinsrvctl start listener -n node3. If you are using Automatic Storage Management, start the Automatic Storage Management instance.4. For single-instance installations, start the listener as follows:C: lsnrctl start5. For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:C: sqlplus /NOLOGSQL CONNECT SYS AS SYSDBAEnter password:SYS_passwordUsers of single-instance installations now proceed to step 7.6. For Oracle RAC installations:A. Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:C: sqlplus /NOLOGSQL CONNECT SYS AS SYSDBAEnter password: SYS_passwordSQL STARTUP NOMOUNTB. Set the CLUSTER_DATABASE initialization parameter to FALSE:SQL ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile; C. Shut down the database:SQL SHUTDOWN7. Enter the following SQL*Plus commands:SQL STARTUP UPGRADESQL SPOOL patch.logSQL ORACLE_BASEORACLE_HOMErdbmsadmincatupgrd.sqlSQL SPOOL OFF8. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.This list provides the version and status of each SERVER component in the database.9. If necessary, rerun the catupgrd.sql script after correcting any problems.10. Restart the database:SQL SHUTDOWN IMMEDIATESQL STARTUP11. Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.SQL ORACLE_BASEORACLE_HOMErdbmsadminutlrp.sqlNote:When the patch set is applied to an Oracle Database 10g Standard Edition database, there may be 54 invalid objects after the utlrp.sql script runs. These objects belong to the unsupported components and do not affect the database operation. Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following:BIN$4lzljWIt9gfgMFeM2hVSoA=$0 12. Run the following command to check the status of all the components after the upgrade:SQL SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.13. If you are using the Oracle Recovery Manager catalog, enter the following command:C: rman catalog username/passwordalias RMAN UPGRADE CATALOG; 14. For Oracle RAC installations:A. Set the CLUSTER_DATABASE initialization parameter to TRUE: SQL ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile; B. Restart the database:SQL SHUTDOWN IMMEDIATESQL STARTUPC. Start any database services that you want to use:C: ORACLE_BASEORACLE_HOMEbinsrvctl start service -d db_name -s service_name15. To configure and secure Enterprise Manager follow these steps:Ensure the database and Listener are operational.In the case of a single instance, executeemca -upgrade dbIn the case of Oracle Real Application Clusters (RAC), executeemca -upgrade db -cluster DBUA 工具提供了一个可视化的界面，它的作用等同于执行catupgrd.sql等脚本，dbua工具能升级Oracle 的一些组件。组件升级之后，该工具会自动编译无效的对象， 这个功能还是挺方便的。 省得自己手动的去执行编译脚本了。 SQLSELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;COMP_NAME VERSION STATUSOracle Database Catalog Views .0 VALIDOracle Database Packages and Types .0 VALIDOracle Workspace Manager .3 VALIDJServer JAVA Virtual Machine .0 VALIDOracle XDK .0 VALIDOracle Database Java Packages .0 VALIDOracle Expression Filter .0 VALIDOracle Data Mining .0 VALIDOracle Text .0 VALIDOracle XML Database .0 VALIDOracle Rule Manager .0 VALIDOracle interMedia .0 VALIDOLAP Analytic Workspace .0 VALIDOracle OLAP API .0 VALIDOLAP Catalog .0 VALIDSpatial .0 VALIDOracle Enterprise Manager .0 VALID*这两天做数据库的升级，从升级到，升级安装很顺利，但安装完成后再用常用用户连接数据库，出现如下提示错误：3sNews博客f A4n7*J0h X3sNews博客pi TZ;T h ORA-12514：TNS：监听程序当前无法识别连接描述符中请求的服务。j IJ!g)e.E)|h0 3sNews博客 uan S-I 改了好几个参数都没用，后来找到了解决方法，请先把与之相关的服务全部停掉，如sde、arcims、server等，具体操作步骤说明如下：3sNews博客0vQ4LyJ UqOf6G2h1Z Ff%Z_$|01、删除以前的oracle实例3sNews博客%tE)W!cH-V:qA3sNews博客8i4tNn y c:oradim -delete -sid wxgis 3sNews博客!YVQF) Da;&z!K#V*vw& k02、重建oracle实例3sNews博客nCt b F C4gM;M.k*Kh$N0c:oradim -new -sid wxgis -startmode auto -pfile d:oracleproduct10.2.0db_1databaseinitwxgis.ora3sNews博客 O1|,k7PCq| QLUr$|2X03、登陆sqlplus3sNews博客)cDFbJ.U QP3sNews博客v*EP3B8dG c:sqlplus /nolog3sNews博客(yotf_5Oz3sNews博客)mb;iGT w9V sqlconn /as sysdbaW8E#U!x y;B+0 FDv,h!q k &h-h0sqlstartup upgradeA;w4n Bd(ZL/0 3sNews博客V:L3U;Z*g(T8i!R 4、运行catupgrd.slq6X*b t_4k iXPL!Y0 3sNews博客Z$A#S0M;R sqlspool upgrade.log3sNews博客 m( _F(U)T(S3sNews博客/xKKT#n5p(V sqld:oraclepro