某发票开发测试库升级 升级前准备，此次升级只是很对测试环境数据库升级，所以没有事先一个月来获取系统，数据库的统计信息，机器性能比对为了加快升级只是清理了以下信息01.截断SYS.AUD$基表:SQL>TRUNCATE TABLE SYS.AUD$;02.清理DBA回收站:SQL>purge DBA_RECYCLEBIN;1.升级开始，升级前首先断开测试环境的中间件应用查看本机的ORACLE_HOME[oracle@Oel_10 ~]$ echo $ORACLE_HOME/opt/product/10.2.0/db_12.把10.2.0.5的补丁p8202632_10205_Linux-x86-64.zip放到/oinstall目录下[root@Oel_10 ~]# cd /oinstall/[root@Oel_10 oinstall]# unzip p8202632_10205_Linux-x86-64.zip 解压完成后，再赋予相应的权限，Disk1为10.2.0.5的解压的PATCH目录[root@Oel_10 ~]# chown -R oracle:oinstall /oinstall/[root@Oel_10 oinstall]# ls -al total 1221952drwxr-xr-x 5 oracle oinstall 4096 Apr 29 2010 Disk1-rw-r--r-- 1 oracle oinstall 1249857866 Nov 22 17:24 p8202632_10205_Linux-x86-64.zip3.确定当前的数据库版本SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production5.在做升级之前先要进行备份，备份数据库软件与数据文件，备份重中之重01.数据库软件用tar 命令备份即可（略）02.数据库本身备份利用RMAN即可（略）03.备份完成后开始停止相应的服务instance SQL>shutdown immediatelistener $lsnrctl stopEM $emctl stop dbconsole6.开始安装补丁，首以root用户先进入图形化界面[root@Oel_10 ~]# xhost +[root@Oel_10 ~]# su - oracle[oracle@Oel_10 ~]$ cd /oinstall/[oracle@Oel_10 oinstall]$ cd Disk1/[oracle@Oel_10 Disk1]$ lsinstall patch_note.htm response runInstaller stage开始安装补丁[oracle@Oel_10 Disk1]$ ./runInstaller . .图形化安装，与安装oracle软件一致，此处忽略安装过程 . .7.安装完成后，以upgrade模式启动数据库，升级数据字典SQL>startup upgradeSQL> select status from v$instance;STATUS------------------------OPEN MIGRATE确认此时instance状态为OPEN MIGRATE（） 升级数据库的大版本或大的patch的时候总是需要升级现有数据库的数据字典，升级数据字段的原因是因为随着Oracle版本的升级，某些对象的属性需要改变，以便保证系统的的数据词典的完整性和有效性这些变更都是在升级脚本$ORACLE_HOME/rdbms/admin/catupgrd.sql中。以升级模式启动数据库后，开始升级数据字典SQL>@/opt/product/10.2.0/db_1/rdbms/catupgrd.sql升级数据字典时，可以看到相应的操作PL/SQL procedure successfully completed.Type created.Grant succeeded.Package created.No errors.Table created.PL/SQL procedure successfully completed.View created.Synonym created.进而进一步验证了catupgrd.sql作用，升级完数据字典后，sqlplus下会出现以下信息TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP RUL 2012-11-23 06:56:47DBUA_TIMESTAMP RUL VALID 2012-11-23 06:56:47COMP_TIMESTAMP UPGRD_END 2012-11-23 06:58:01.Oracle Database 10.2 Upgrade Status Utility 11-23-2012 06:58:01.Component Status Version HH:MM:SSOracle Database Server VALID 10.2.0.5.0 00:07:52JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:00:56Oracle XDK VALID 10.2.0.5.0 00:00:23Oracle Database Java Packages VALID 10.2.0.5.0 00:00:12Oracle Text VALID 10.2.0.5.0 00:00:18Oracle XML Database VALID 10.2.0.5.0 00:02:02Oracle Workspace Manager VALID 10.2.0.5.0 00:00:36Oracle Data Mining VALID 10.2.0.5.0 00:00:15OLAP Analytic Workspace VALID 10.2.0.5.0 00:00:16OLAP Catalog VALID 10.2.0.5.0 00:00:42Oracle OLAP API VALID 10.2.0.5.0 00:00:31Oracle interMedia VALID 10.2.0.5.0 00:02:21Spatial VALID 10.2.0.5.0 00:01:32Oracle Expression Filter VALID 10.2.0.5.0 00:00:06Oracle Enterprise Manager VALID 10.2.0.5.0 00:00:30Oracle Rule Manager VALID 10.2.0.5.0 00:00:06.Total Upgrade Time: 00:20:01DOC>#######################################################################DOC>#######################################################################DOC>DOC> The above PL/SQL lists the SERVER components in the upgradedDOC> database, along with their current version and status.DOC>DOC> Please review the status and version columns and look forDOC> any errors in the spool log file. If there are errors in the spoolDOC> file, or any components are not VALID or not the current version,DOC> consult the Oracle Database Upgrade Guide for troubleshootingDOC> recommendations.DOC>DOC> Next shutdown immediate, restart for normal operation, and thenDOC> run utlrp.sql to recompile any invalid application objects.DOC>DOC>#######################################################################DOC>#######################################################################8.再次重启数据库SQL> shutdown immediate SQL> startupORACLE instance started.9.编译无效对象脚本utlrp.sqlutlrp.sql脚本可以在数据库运行的状态下执行以编译、数据库中的invalid对象,oracle建议在对数据库进行迁移、升级、降级后都运行一遍utlrp.sql以编译无效对象。以sysdba登陆来执行脚本[oracle@Oel_10 ~]$ sqlplus / as sysdbaSQL> @/opt/product/11.2.0/db_1/rdbms/admin/utlrp.sqlTIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN 2012-11-23 07:04:44DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC> objects in the database. Recompilation time is proportional to theDOC> number of invalid objects in the database, so this command may takeDOC> a long time to execute on a database with a large number of invalidDOC> objects.DOC>DOC> Use the following queries to track recompilation progress:DOC>DOC> 1. Query returning the number of invalid objects remaining. ThisDOC> number should decrease with time.DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);DOC>DOC> 2. Query returning the number of objects compiled so far. This numberDOC> should increase with time.DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC> This script automatically chooses serial or parallel recompilationDOC> based on the number of CPUs available (parameter cpu_count) multipliedDOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).DOC> On RAC, this number is added across all RAC nodes.DOC>DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallelDOC> recompilation. Jobs are created without instance affinity so that theyDOC> can migrate across RAC nodes. Use the following queries to verifyDOC> whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC> 1. Query showing jobs created by UTL_RECOMPDOC> SELECT job_name FROM dba_scheduler_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>DOC> 2. Query showing UTL_RECOMP jobs that are runningDOC> SELECT job_name FROM dba_scheduler_running_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>#PL/SQL procedure successfully completed.TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END 2012-11-23 07:05:46PL/SQL procedure successfully completed.DOC> The following query reports the number of objects that have compiledDOC> with errors (objects that compile with errors have status set to 3 inDOC> obj$). If the number is higher than expected, please examine the errorDOC> messages reported with each object (using SHOW ERRORS) to see if theyDOC> point to system misconfiguration or resource constraints that must beDOC> fixed before attempting to recompile these objects.DOC>#OBJECTS WITH ERRORS------------------- 0DOC> The following query reports the number of errors caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC>#ERRORS DURING RECOMPILATION--------------------------- 0PL/SQL procedure successfully completedSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 7516192768 bytesFixed Size 2107936 bytesVariable Size 1258292704 bytesDatabase Buffers 6241124352 bytesRedo Buffers 14667776 bytesDatabase mounted.Database opened.SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production到此为止，升级工作初步完成