oracle升级无效对象,【排故篇】_Oracle迁移/升级库后无效对象如何破？

【排故篇】_啥是无效对象，出现时如何破？

【前言】

大家都知道，在数据库日常运维中，数据库中一些对象(如：Package、Procedure、Function、View、同义词等会失效,状态为INVALID，需定期检查数据库中存在哪些失效对象。自动失效的对象，一般会在下次调用的时候，会被重新编译，所以一般也不需要人工干预。对于存在异常的对象需要重新编译了；还有一个场景就是数据迁移导也会导致无效对象的产生。

最近做了一次oracle数据库版本升级，版本有11.2.0.4版本升级到两节点RAC架构的12c版本。升级完后例行排查问题，使用如下命令发现升级后的失效对象激增，这是啥原因，怎么解决，本文将进行解答。

本文将回答如下问题：

1. 为什么对象突然会失效？

2. 如何快速、高效的编译失效对象？

3. 如何编辑分区表的无效对象？

一、 对象为啥突然失效

数据库对象失效，大致归纳如下原因：

1: 被引用对象结构发生变化

被引用对象的结构发生改变时，其相关的依赖对象会变为INVALID状态。

数据库中的对象(存储过程，函数，包，视图，触发器)，它们往往需要直接或者间接的引用其它对象，对象的依赖包括直接和间接二种，其中直接依赖是指存储对象直接依赖于被引用对象，而间接依赖是指对象间接依赖于被引用对象。

要查看被引用的对象，可以通过下面SQL查看

select * from dba_dependencies where name=’&objectname’;

select * from all_dependencies where name=’&objectname’;

select * from user_dependencies where name=’&objectname’;

实际生产中，不管视图，像存储过程，函数、包等，只是引用的对象发生了变化，均会导致调用对象失效，但并不影响调用，原因为ORACLE在调用时会自动重新编译对象；

Oracle 会自动维护分区索引，对于全局索引，如果在对分区表进行truncate/drop/exchange 操作分区时，此时若没有使用update index，则会导致全局索引失效，需要重建。后续会着重分析下哪些情况下会导致分区表的全局索引和局部索引失效。

注意：

如果对象变化后导致编译有错误，那么此时调用对象时重新编译后也是错误并处于失效状态，调用会出错。

2：被编辑对象出错

发布SQL脚本时(包、存储过程、函数等)，测试不充分编译时出错，这时对象变为无效。

3: 数据库升级、迁移时，出现大量无效对象；

个人推断为：数据升级或迁移时，对象之间倚赖关系被破坏，导致被引用对象失效；看过数据泵导出导入过程的朋友都知道，日志中的对象生成顺序、命令执行顺序是不一致的，尤其是开了parallel并发度时。在对象创建前后顺序的错乱，被引用的对象尚未创建时便被引用，从而引无效对象。所以，通常在数据库迁移后，需要检查下无效对象，并引用$ORACLE_HOME/rdbms/admin/utlrp.sql进行重新编译。

二、如何快速、高效的编译失效对象？

1. 使用如下命令查看无效对象：

–统计查看无效对象SQL：

–查看具体失效对象

2. 编译失效对象的方法

主要有三种方法：

方法1：

使用ALTER *** COMPLIE语句手工进行编译

使用如下命令进行：

【注意】

此方法适用于少数、个别对象失效时使用。如果失效对象较多，手动一个个的调用太费时劳力。

方法2：

使用dbms_utility.compile_schema(user,false)

ORACLE提供了自动编译的接口dbms_utility.compile_schema(user,false); 调用该过程可编译某用户下所有失效的过程、函数、触发器、包。该方式一般在对某些用户迁移至他库时进行手动调用；其实不调用也没问题，原因为失效的对象，一般会在下次调用的时候，会被重新编译。手动编辑’SCOTT’ 用户下的失效对象，可使用如下命令：

Sql> exec dbms_utility.compile_schema( ‘SCOTT’ );

方法3：

执行utlrp.sql脚本编译数据库失效对象

utlrp脚本可用来重编译数据库的所有无效对象。该脚本为官方自带，Oracle强烈推荐在migration/upgrade/downgrade之后，通过运行此脚本编译失效对象。utlrp.sql只是调用utlprp.sql,utlrp.sql在调用utlprp.sql的时候会传递给它一个参数,默认为0,参数为并行度,其实utlprp.sql调用的是utl_recomp.recomp_parallel:0 - 基于CPU_COUNT参数设置并行度.1 - 以串行方式编译对象,一次编译一个.N - 以N个并行度进行编译.

脚本需要以sys用户或者有sysdba权限的用户来运行，执行如下即可：

Sql> @ORACLE_HOME/rdbms/admin/utlrp.sql

Oracle highly recommends running this script towards the end of of any migration/upgrade/downgrade.

utlrp.sql 里面其实调用了ORACLE_HOME/rdbms/admin/utlrcmp.sql来编译失效对象

【注意】

utlrp.sql脚本通常在数据库的升级或迁移后执行，原因为升级或迁移活成会导致数据库中的对象失效。而对象之间的倚赖关系复杂，所以使用方法1中的“ALTER *** COMPLIE”语句手工编译耗时费力。另注意：脚本调用过程中，最好不要有活动事物或DDL操作，否则极容易导致死锁的出现。

参考链接2中给出了两种编译失效对象的脚本

SQL 1: 编译失效对象

set heading off;

set feedback off;

set echo off;Set lines 999;

–使用spool命令生成方法1中的ALTER *** COMPLIE语句

Spool run_invalid.sql

SELECT ‘ALTER ’

|| OBJECT_TYPE

|| ’ ’

|| OWNER

|| ‘.’

|| OBJECT_NAME

|| ’ COMPILE;’

FROM dba_objects

WHERE status = ‘INVALID’

AND object_type IN (‘PACKAGE’,

‘FUNCTION’,

‘PROCEDURE’,

‘TRIGGER’,

‘JAVA SOURCE’,

‘JAVA CLASS’,

‘’);

set heading on;

set feedback on;

set echo on;

##调用生成的run_invalid.sql重新编辑失效对象

sql> @run_invalid.sql

SQL 2: 在上面的方法中，只能知道某某编译失败，不清楚失败原因，可以用PL/SQL实现更详细的错误信息

DECLARE

v_objname user_objects.object_name%TYPE;

v_objtype user_objects.object_type%TYPE;

CURSOR cur

IS

SELECT object_name, object_type

FROM USER_OBJECTS

WHERE status = ‘INVALID’

AND object_type IN (‘FUNCTION’,

‘JAVA SOURCE’,

‘JAVA CLASS’,

‘PROCEDURE’,

‘PACKAGE’,

‘TRIGGER’);

BEGIN

OPEN cur;

LOOP

FETCH cur INTO v_objname, v_objtype;

EXIT WHEN cur%NOTFOUND;

BEGIN

EXECUTE IMMEDIATE

'alter ' || v_objtype || ' ' || v_objname || ' Compile';

DBMS_OUTPUT.put_line (

'编译' || v_objtype || ' ' || v_objname || '()成功');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'编译'

|| v_objtype

|| ' '

|| v_objname

|| '()失败.'

|| SQLERRM);

END;

END LOOP;

CLOSE cur;

END;

3. 如何编辑分区表的无效对象？

当对分区表进行truncate/drop/exchange 操作时，会造成索引失效。

exchange 的临时表没有索引，或有索引时，但没用including indexes的关键字，会导致某个分区的索引失效。重建局部索引只能用alter index local_idx rebuild partition p1;

分区表SPLIT时，如MAX区中已经有记录，此时SPLIT就会导致有记录的新增分区的局部索引失效。

查寻某个分区表中 各个分区 索引状态  USABLE/UNUSABLE

SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = ‘indexName’;

–重建索引

–local索引重建

SELECT b.table_name,

a.INDEX_NAME,

a.PARTITION_NAME,

a.STATUS,

'alter index ’

|| a.index_name

|| ’ rebuild partition ’

|| partition_name

|| ‘;’

重建列

FROM USER_IND_PARTITIONS a, user_part_indexes b

WHERE a.index_name = b.index_name

AND b.TABLE_NAME IN (‘PART_TAB_SPLIT’)

AND STATUS = ‘UNUSABLE’

ORDER BY b.table_name, a.INDEX_NAME, a.PARTITION_NAME;

–全局索引重建

alter index idx_part_split_col3 rebuild;

对truncate等 操作时直接更新 index 也可以搞定。

alter table part_tab_trunc truncate partition p2 Update GLOBAL  indexes;

【总结】

1. 本文主要讲述了失效对象的原因，重新编辑失效对象的几种方法，以及分区表发生变化时，局部索引和全局索引的失效对象编辑方式；

2. Oracle强烈推荐在migration/upgrade/downgrade之后，通过运行此脚本编译失效对象；

3. 参考链接2中给出了两种编译失效对象的脚本，个人感觉在平时的库运维中很有帮助，大家可重点参读。

【参考1】

https://www.233.com/oracle/jishu/20071014/101911246.html

【参考2】

https://www.cnblogs.com/kerrycode/p/3723715.html

【参考3】

https://www.cnblogs.com/javaMan/p/3980469.html?utm_source=tuicool&utm_medium=referral

【参考4】

http://jzhil2004.blog.163.com/blog/static/275585042010117113214172/

【参考5】

http://blog.csdn.net/tianlesoftware/article/details/4843600

【参考6】

http://www.233.com/oracle/jishu/20071014/101911246.html