10G 打补丁的时候出现ORA-12801 ORA-00018

   WINDOWS 2008 r2 64 位操作系统,ORACLE原来为10204 ,打10205 的patch  64位的。打补丁的时候,2台机器使用upgrade assistant (DBUA) 半路也出错,索性还是手动更新。

更新的时候，安装补丁的过程都很顺利，需要选择在原有安装系统的home下。在进行安装后期操作的时候，1台机器使用手动更新顺利完成。反而是先装的那台机器，手动更新了好几次，除提示某个部件没装之外，还出现 mgmt_targets 出错，又反复执行了2次，在重新编译INVALID 包的时候出错：

第 1 行出现错误:

ORA-12801: 并行查询服务器 P064 中发出错误信号

ORA-00018: 超出最大会话数

ORA-06512: 在 "SYS.UTL_RECOMP", line 662

ORA-06512: 在 line 4

使用SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;看到 oracle database packages and types 、oracle database java packages 等4个部件都是invalid。

查看网上，有说CPU个数太多的，后来有说更改PROCESSES参数，因此将其中一台机器的批rocesses改为300，再次运行UTLRP.SQL ，成功完成：

SQL〉alter system set processes=300 scope=spfile;

SQL〉shutdown immediate;

SQL〉startup;

SQL〉@%ORACLE_HOME%\rdbms\admin\utlrp.sql

TIMESTAMP                                               

-------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2013-08-29 08:49:55                                  

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      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 number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script. automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   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 parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>        WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>        WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL 过程已成功完成。

TIMESTAMP                                               

-------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END  2013-08-29 08:50:18                                  

终于成功完成。

SQL〉SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;  --查看升级部件的状态

COMP_NAME                                               

--------------------------------------------------------

VERSION                        STATUS                                          

------------------------------ ----------------------   

Spatial                                                                        

10.2.0.5.0                     VALID                                           

Oracle interMedia                                                              

10.2.0.5.0                     VALID                                           

OLAP Catalog                                                                   

10.2.0.5.0                     VALID                                           

Oracle Enterprise Manager                                                      

10.2.0.5.0                     VALID                                            

Oracle XML Database                                                            

10.2.0.5.0                     VALID                                           

Oracle Text                                                                    

10.2.0.5.0                     VALID                                           

Oracle Expression Filter                                                       

10.2.0.5.0                     VALID                                           

Oracle Rule Manager                                                       

10.2.0.5.0                     VALID                                            

Oracle Workspace Manager                                                       

10.2.0.5.0                     VALID                                           

COMP_NAME                                               

-------------------------------------------------------------------------------

VERSION                        STATUS                                          

------------------------------ ----------------------                          

Oracle Data Mining                                                              

10.2.0.5.0                     VALID                                           

Oracle Database Catalog Views                                                  

10.2.0.5.0                     VALID                                           

Oracle Database Packages and Types                                             

10.2.0.5.0                     VALID                                           

JServer JAVA Virtual Machine                                                   

10.2.0.5.0                     VALID                                           

Oracle XDK                                                                      

10.2.0.5.0                     VALID                                           

Oracle Database Java Packages                                                   

10.2.0.5.0                     VALID                                           

OLAP Analytic Workspace                                                        

10.2.0.5.0                     VALID                                           

Oracle OLAP API                                                                

10.2.0.5.0                     VALID                                           

已选择17行。

SQL> select count(*) cnt from utl_recomp_compiled;

CNT                                                 

----------                                             

 246                                                                     

SQL> select count(*) cnt from obj$ where status in (4,5,6);

CNT

----------                                         

 0                                                                     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7177735/viewspace-769472/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7177735/viewspace-769472/