8. 登录查看，升级后版本
[oracle@11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 16:43:38 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set linesize 150;
set pagesize 9999;
col comp_name format a40;
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
Oracle Database Packages and Types 12.2.0.1.0 UPGRADED
JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
Oracle XDK 12.2.0.1.0 UPGRADED
Oracle Database Java Packages 12.2.0.1.0 UPGRADED
OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
Oracle Workspace Manager 12.2.0.1.0 UPGRADED
Oracle Text 12.2.0.1.0 UPGRADED
Oracle XML Database 12.2.0.1.0 UPGRADED
Oracle Multimedia 12.2.0.1.0 UPGRADED
Spatial 12.2.0.1.0 UPGRADED
Oracle OLAP API 12.2.0.1.0 UPGRADED
OLAP Catalog 11.2.0.4.0 OPTION OFF
Oracle Application Express 5.0.4.00.12 UPGRADED
14 rows selected.
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
8357
SQL> select * from utl_recomp_errors;
no rows selected
编译失效对象
SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql
Session altered.
--多次执行utlrp.sql，失效对象数量不再变化
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
44
统计失效的对象：
SQL> set lin 300;
set pagesize 300;
col owner for a30;
select owner, object_type, status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type;
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ----------------------- ------- ----------
SYS FUNCTION INVALID 2
SYS PROCEDURE INVALID 5
SYS TRIGGER INVALID 2
SYS VIEW INVALID 1
TBTB FUNCTION INVALID 13
TBTB PROCEDURE INVALID 3
TBTB VIEW INVALID 18
查看具体失效对象
col owner for a20;
col object_name for a32;
col object_type for a16;
col status for a8;
select owner, object_name, object_type, status
from dba_objects
where status='INVALID'
order by 1,2,3;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- -------------------------------- ---------------- --------
SYS DEPEND_RELATIONSHIP PROCEDURE INVALID
SYS EVENT_TABLE PROCEDURE INVALID
SYS FN_GETSQLBYSQLID FUNCTION INVALID
SYS GET_SQLTEXT FUNCTION INVALID
SYS OLAPISHUTDOWNTRIGGER TRIGGER INVALID
SYS OLAPISTARTUPTRIGGER TRIGGER INVALID
SYS P_COLL_DML_INSERT PROCEDURE INVALID
SYS P_MONI_INVALID_INDEX PROCEDURE INVALID
SYS P_MONI_SQL PROCEDURE INVALID
SYS V$SQLAREA_TOTAL VIEW INVALID
TBTB CONTRACT_CALV FUNCTION INVALID
TBTB CONTRACT_CONTTYPE FUNCTION INVALID
TBTB CONTRACT_GDQX FUNCTION INVALID
TBTB EMPLEVEL_LT FUNCTION INVALID
TBTB EMPLEVEL_LZ FUNCTION INVALID
TBTB EMPLEVEL_QC FUNCTION INVALID
TBTB EMPLEVEL_XJRS FUNCTION INVALID
TBTB GETTRMOBJ_BYOID FUNCTION INVALID
TBTB GETUNITFULLNAME FUNCTION INVALID
TBTB GET_CONTRACTOIDBYEMPOID FUNCTION INVALID
TBTB GET_EMPDATA FUNCTION INVALID
TBTB JLSXTG PROCEDURE INVALID
TBTB LUOC FUNCTION INVALID
TBTB PROC_COPYTEMPLATE PROCEDURE INVALID
TBTB REP_1 FUNCTION INVALID
TBTB SET_EMPDATA PROCEDURE INVALID
TBTB TB_CNB_PAYRECORDINFO VIEW INVALID
TBTB TB_V_CRPT_SELCOUNT VIEW INVALID
TBTB TB_V_EMPASSIGNEDJOB VIEW INVALID
TBTB TB_V_EMPPOSITION VIEW INVALID
TBTB TB_V_LEADERLEVEL VIEW INVALID
TBTB TB_V_LEADER_SET VIEW INVALID
TBTB TB_V_ORGUNIT VIEW INVALID
TBTB TB_V_PMG_EVALUESCORE VIEW INVALID
TBTB TB_V_PMG_REWARDANDPUNISH VIEW INVALID
TBTB TB_V_REC_CANDIDATE VIEW INVALID
TBTB TB_V_TABLE VIEW INVALID
TBTB TB_V_TMG_MONTHREPORT VIEW INVALID
TBTB TB_V_TRM_CLASSINFO VIEW INVALID
TBTB TB_V_TRM_CLASSMONEY VIEW INVALID
TBTB TB_V_TRM_COURSE VIEW INVALID
TBTB VIEW_BIZPROCINST VIEW INVALID
TBTB VIEW_BIZWORKITEM VIEW INVALID
TBTB VIEW_ORG_PERSON_ROLE_UNIT VIEW INVALID
44 rows selected.
手动单个单个编译失效对象，结果每个报 Warning: Procedure altered with compilation errors.
ALTER PROCEDURE SYS.DEPEND_RELATIONSHIP COMPILE;
ALTER PROCEDURE SYS.P_MONI_INVALID_INDEX COMPILE;
ALTER PROCEDURE SYS.P_MONI_SQL COMPILE;
ALTER PROCEDURE SYS.P_COLL_DML_INSERT COMPILE;
ALTER FUNCTION SYS.GET_SQLTEXT COMPILE;
ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER COMPILE;
ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER COMPILE;
ALTER FUNCTION TBTB.GET_EMPDATA COMPILE;
ALTER FUNCTION TBTB.GETUNITFULLNAME COMPILE;
ALTER PROCEDURE TBTB.SET_EMPDATA COMPILE;
ALTER PROCEDURE TBTB.JLSXTG COMPILE;
ALTER PROCEDURE TBTB.PROC_COPYTEMPLATE COMPILE;
ALTER FUNCTION TBTB.GETTRMOBJ_BYOID COMPILE;
ALTER FUNCTION TBTB.LUOC COMPILE;
ALTER FUNCTION TBTB.REP_1 COMPILE;
ALTER FUNCTION TBTB.GET_CONTRACTOIDBYEMPOID COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_LT COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_LZ COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_QC COMPILE;
ALTER FUNCTION TBTB.CONTRACT_CALV COMPILE;
ALTER FUNCTION TBTB.CONTRACT_CONTTYPE COMPILE;
ALTER FUNCTION TBTB.CONTRACT_GDQX COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_XJRS COMPILE;
ALTER FUNCTION SYS.FN_GETSQLBYSQLID COMPILE;
ALTER PROCEDURE SYS.EVENT_TABLE COMPILE;
至此失效对象无法valid，推测是版本升级废弃对象，这里先不讨论。
9. 修改compatible参数，若修改完成，此次升级操作就无法回退，一定要在应用经过测试之后修改。
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4
noncdb_compatible boolean FALSE
QL> create spfile from pfile;
File created.
SQL> shutdown immediate;
SQL> startup;
SQL> alter system set compatible='12.2.0.1.0' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0.1.0
noncdb_compatible boolean FALSE
SQL> set linesize 150;
set pagesize 9999;
col comp_name format a40;
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;