摘要：
假设您通过应用中间补丁11.1.0.7从Oracle11.1.0.6升级到Oracle12c（12.1.0），并且降级只能直接运行升级版本号。假设无论从升级的主文件夹执行的源数据库应用了什么补丁，卸载和回滚补丁的步骤都记录在所涉及补丁的自述文件中。未能卸载和回滚修补程序可能导致降级失败，并且没有无效组件###检查每个组件的状态SQL>；
探索Oracle之数据库升级八
12c Downgrade 11gR2
前言：
我们前面已经完毕了11gR2 upgrade to 12c 的升级，整个过程还是比較顺利的，尽管和曾经版本号升级有些不太一样，可是整个难度不是太大。可是因为太多的特性不同，大大的加长了升级的时间。
可是对于Oracle 12c降回到之前版本号。会有些限制，仅仅能降级到升级前所用的版本号和补丁级别。
假设我们是直接从10.2.0.5、11.1.0.7或版本号11.2.0.2及更高版进行升级。那么将无法降级到10.2.0.5。
假设通过应用中间补丁程序11.1.0.7 从Oracle 11.1.0.6升级到Oracle 12c（12.1.0），则不能降级到Oracle 11.1.0.6。
降级仅仅能对直接运行升级版本号运行。
例外：
尽管能够对 10.2.0.5 直接升级。但降级不适用于 10.2.0.5。
这是由于在升级过程中，compatible 參数已设置为最低 11.0.0。
这使得无法降级到 10.2.0.5。能够降级的版本号为 11.1.0.7、11.2.0.2、11.2.0.3 或更高版本号。
假设有不论什么补丁程序应用到了从升级后的主文件夹执行的源数据库，则须要先回退，然后才干開始降级过程。
卸载和回退补丁程序的步骤记录在所涉及补丁程序的自述文件里。
未能卸载和回退补丁程序可能会导致无法降级，包含无法又一次验证字典对象。
Exadata 捆绑补丁程序演示样例，其过程为：
卸载补丁程序
演示样例： $ opatch auto /u01/app/oracle/patches/14103267 -rollback
回退不论什么在补丁程序应用过程中应用的 SQL：
演示样例： SQL> @rdbms/admin/catbundle_EXA_<数据库 SID>_ROLLBACK.sql，用于回退 SQL 更改。
一、開始运行降级前的检查操作
升级前确保全部组件状态正常，且没有无效组件
###检查各组件状态
SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- --------
DV Oracle Database Vault 12.1.0.1.0 VALID
APEX Oracle Application Express 4.2.0.00.2 VALID
OLS Oracle Label Security 12.1.0.1.0 VALID
SDO Spatial 12.1.0.1.0 VALID
ORDIM Oracle Multimedia 12.1.0.1.0 VALID
CONTEXT Oracle Text 12.1.0.1.0 VALID
OWM Oracle Workspace Manager 12.1.0.1.0 VALID
XDB Oracle XML Database 12.1.0.1.0 VALID
CATALOG Oracle Database Catalog Views 12.1.0.1.0 VALID
CATPROC Oracle Database Packages and T 12.1.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.1.0.1.0 VALID
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- --------
XML Oracle XDK 12.1.0.1.0 VALID
CATJAVA Oracle Database Java Packages 12.1.0.1.0 VALID
APS OLAP Analytic Workspace 12.1.0.1.0 VALID
XOQ Oracle OLAP API 12.1.0.1.0 VALID
RAC Oracle Real Application Cluste 12.1.0.1.0 OPTION O
FF
16 rows selected.
#####检查无效组件
SQL> select owner, count(object_name) "Invalid object count" from dba_objects where status!='VALID' and owner in ('SYS','SYSTEM') group by owner;
no rows selected
SQL>二、開始运行降级操作
###2.1 关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
###2.1 将数据库启动到downgrade模式
SQL> startup downgrade;
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed Size 2298736 bytes
Variable Size 1040190608 bytes
Database Buffers 3976200192 bytes
Redo Buffers 8695808 bytes
Database mounted.
Database opened.
SQL>
###2.2 执行降级脚本
SQL> spool downgrade_12c_to_11g.log
SQL> @$ORACLE_HOME/rdbms/admin/catdwgrd.sql
………
PL/SQL procedure successfully completed.
SQL>
SQL> -- lrg 7259350: objects created in this script need their flags that won't fit
SQL> -- in a ub2 cleared
SQL> update obj$mig set flags=bitand(flags,65535) where flags>65535;
222 rows updated.
SQL>
SQL> /*****************************************************************************/
SQL> /* Step 9 - Swap bootstrap$mig with bootstrap$
SQL> */
SQL> /*****************************************************************************/
SQL> /* According to JKLEIN, performing 3 count(*) will ensure there are
SQL> no dirty itl's present in bootstrap$. */
SQL> select count(*) from bootstrap$;
COUNT(*)
----------
60
1 row selected.
SQL> select count(*) from bootstrap$;
COUNT(*)
----------
60
1 row selected.
SQL> select count(*) from bootstrap$;
COUNT(*)
----------
60
1 row selected.
SQL> select count(*) from bootstrap$mig;
COUNT(*)
----------
60
1 row selected.
SQL> select count(*) from bootstrap$mig;
COUNT(*)
----------
60
1 row selected.
SQL> select count(*) from bootstrap$mig;
COUNT(*)
----------
60
1 row selected.
SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> begin
2
3 -- Now we can do the swap.
4 declare
5 procedure swap_bootstrap(replacement_tbl_name IN VARCHAR2)
6 is language c library DBMS_DDL_INTERNAL_LIB
7 name "swap_bootstrap"
8 with context
9 parameters(context, replacement_tbl_name String,
10 replacement_tbl_name LENGTH ub2,
11 replacement_tbl_name INDICATOR sb2);
12 begin
13 swap_bootstrap('BOOTSTRAP$MIG');
14 end;
15
16 -- We've completed the swap.
17 -- Remove the BOOTSTRAP_DOWNGRADE_ERROR entry in props$.
18 delete from props$ where name = 'BOOTSTRAP_DOWNGRADE_ERROR';
19 commit;
20 end;
21 /
PL/SQL procedure successfully completed.
SQL>
SQL> Rem ***********************************************************************
SQL> Rem END catdwgrd.sql
SQL> Rem ***********************************************************************
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@woo ~]三、改动/etc/oratab 为Oracle 12gR2 HOME文件夹
[root@woo ~]# vi /etc/oratab woo:/DBSoft/oracle/product/11.2.4/dbhome_1
四、改动系统环境变量
[root@woo oracle]# vi ~/.bash_profile [oracle@woo ~]$ echo $ORACLE_HOME /DBSoft/oracle/product/11.2.4/dbhome_1
五、启动到upgrade模式。从降级版本号$ORACLE_HOME/rdbms/admin文件夹中运行catrelod脚本
[oracle@woo ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 23 18:13:17 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 5027385344 bytes Fixed Size 2261888 bytes Variable Size 1191185536 bytes Database Buffers 3825205248 bytes Redo Buffers 8732672 bytes Database mounted. Database opened. SQL>SQL> $ORACLE_HOME/rdbms/admin/catrelod.sql …….. SQL> r 1 SELECT comp_name, status, substr(version,1,10) as version 2* from dba_server_registry order by modified COMP_NAME STATUS ----------------------------------- -------------------------------------------- VERSION ---------------------------------------- OWB VALID 11.2.0.4.0 Oracle Database Packages and Types VALID 11.2.0.4.0 Oracle Database Catalog Views INVALID 11.2.0.4.0 JServer JAVA Virtual Machine VALID 11.2.0.4.0 Oracle XDK VALID 11.2.0.4.0 Oracle Database Java Packages VALID 11.2.0.4.0 Oracle Text VALID 11.2.0.4.0 Oracle XML Database VALID 11.2.0.4.0 Oracle Workspace Manager VALID 11.2.0.4.0 OLAP Analytic Workspace VALID 11.2.0.4.0 Oracle OLAP API VALID 11.2.0.4.0 Oracle Multimedia VALID 11.2.0.4.0 Spatial INVALID 11.2.0.4.0 Oracle Application Express INVALID 3.2.1.00.1
六、运行utlrp.sql脚本又一次编译先前处于INVALID状态的全部现有PL/SQL模块
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL*PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
SQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem gviswana 06/25/02 - Add documentation
SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem rburns 11/12/01 - validate all components after compiles
SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
SQL> Rem rburns 09/29/01 - use 9.2.0
SQL> Rem rburns 09/20/01 - add check for CATPROC valid
SQL> Rem rburns 07/06/01 - get version from instance view
SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem skabraha 09/25/00 - validate is now a keyword
SQL> Rem kosinski 06/14/00 - Persistent parameters
SQL> Rem skabraha 06/05/00 - validate tables also
SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem rshaikh 09/22/99 - quote name for recompile
SQL> Rem ncramesh 08/04/98 - change for sqlplus
SQL> Rem usundara 06/03/98 - merge from 8.0.5
SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
SQL> Rem Mark Ramacher (mramache) was the original
SQL> Rem author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> @@utlprp.sql 0
SQL> Rem Copyright (c) 2003, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlprp.sql - Recompile invalid objects in the database
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem This script is typically used to recompile invalid objects
SQL> Rem remaining at the end of a database upgrade or downgrade.
SQL> Rem
SQL> Rem Although invalid objects are automatically recompiled on demand,
SQL> Rem running this script ahead of time will reduce or eliminate
SQL> Rem latencies due to automatic recompilation.
SQL> Rem
SQL> Rem This script is a wrapper based on the UTL_RECOMP package.
SQL> Rem UTL_RECOMP provides a more general recompilation interface,
SQL> Rem including options to recompile objects in a single schema. Please
SQL> Rem see the documentation for package UTL_RECOMP for more details.
SQL> Rem
SQL> Rem INPUTS
SQL> Rem The degree of parallelism for recompilation can be controlled by
SQL> Rem providing a parameter to this script. If this parameter is 0 or
SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate
SQL> Rem level of parallelism based on Oracle parameters cpu_count and
SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential
SQL> Rem recompilation is used. Please see the documentation for package
SQL> Rem UTL_RECOMP for more details.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem apfwkr 01/03/12 - Backport apfwkr_blr_backport_13059165_11.2.0.3.0
SQL> Rem from st_rdbms_11.2.0
SQL> Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
SQL> Rem not needed.
SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
SQL> Rem cdilling 01/21/08 - add support for ORA-30552
SQL> Rem cdilling 08/27/07 - check disabled indexes only
SQL> Rem cdilling 05/22/07 - add support for ORA-38301
SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
SQL> Rem rburns 03/17/05 - use dbms_registry_sys
SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics
SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability
SQL> Rem rburns 09/20/04 - fix validate_components
SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here
SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808
SQL> Rem gviswana 05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2014-12-23 18:06:58
1 row selected.
SQL>
SQL> DOC
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>#
SQL>
SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2014-12-23 18:08:59
1 row selected.
SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
SQL> select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;
OBJECTS WITH ERRORS
-------------------
0
1 row selected.
SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
1 row selected.
SQL>
SQL>
SQL> Rem
SQL> Rem Declare function local_enquote_name to pass FALSE
SQL> Rem into underlying dbms_assert.enquote_name function
SQL> Rem
SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
2 return varchar2 is
3 begin
4 return dbms_assert.enquote_name(str, FALSE);
5 end local_enquote_name;
6 /
Function created.
SQL> Rem
SQL> Rem If sys.enabled$index table exists, then re-enable
SQL> Rem list of functional indexes that were enabled prior to upgrade
SQL> Rem The table sys.enabled$index table is created in catupstr.sql
SQL> Rem
SQL> SET serveroutput on
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 p_null CHAR(1);
5 p_schemaname VARCHAR2(30);
6 p_indexname VARCHAR2(30);
7 rebuild_idx_msg BOOLEAN := FALSE;
8 non_existent_index exception;
9 recycle_bin_objs exception;
10 cannot_change_obj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
22 SELECT NULL INTO p_null FROM DBA_OBJECTS
23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
24 object_type = 'TABLE' and rownum <=1;
25
26 -- Select indices to be re-enabled
27 EXECUTE IMMEDIATE q'+
28 SELECT 'ALTER INDEX ' ||
29 local_enquote_name(e.schemaname) || '.' ||
30 local_enquote_name(e.indexname) || ' ENABLE'
31 FROM enabled$indexes e, ind$ i
32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
33 bitand(i.property, 16) != 0+'
34 BULK COLLECT INTO commands;
35
36 IF (commands.count() > 0) THEN
37 FOR i IN 1 .. commands.count() LOOP
38 BEGIN
39 EXECUTE IMMEDIATE commands(i);
40 EXCEPTION
41 WHEN NON_EXISTENT_INDEX THEN NULL;
42 WHEN RECYCLE_BIN_OBJS THEN NULL;
43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
44 END;
45 END LOOP;
46 END IF;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
51 IF rebuild_idx_msg THEN
52 BEGIN
53 DBMS_OUTPUT.PUT_LINE
54 ('The following indexes could not be re-enabled and may need to be rebuilt:');
55
56 OPEN reg_cursor FOR
57 'SELECT e.schemaname, e.indexname
58 FROM enabled$indexes e, ind$ i
59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
60
61 LOOP
62 FETCH reg_cursor INTO p_schemaname, p_indexname;
63 EXIT WHEN reg_cursor%NOTFOUND;
64 DBMS_OUTPUT.PUT_LINE
65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
66 END LOOP;
67 CLOSE reg_cursor;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
72 WHEN OTHERS THEN CLOSE reg_cursor; raise;
73 END;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP function local_enquote_name;
Function dropped.
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components;
...Database user "SYS", database schema "APEX_030200", user# "77" 18:09:01
...Compiled 0 out of 2210 objects considered, 0 failed compilation 18:09:01
...189 packages
...181 package bodies
...360 tables
...12 functions
...19 procedures
...4 sequences
...366 triggers
...946 indexes
...125 views
...0 libraries
...4 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 18:09:01
...Completed key object existence check 18:09:01
...Setting DBMS Registry 18:09:01
...Setting DBMS Registry Complete 18:09:01
...Exiting validate 18:09:01
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
##运行完以上脚本后再做检查，就没有无效对象了
SQL> set line 300
SQL> r
1* SELECT comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified
COMP_NAME STATUS VERSION
----------------------------------- -------------------------------------------- ----------------------------------------
Oracle Workspace Manager VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
Spatial INVALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA Virtual Machine VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
Oracle Application Express VALID 3.2.1.00.1
OWB VALID 11.2.0.4.0
14 rows selected.六、运行utlrp.sql脚本又一次编译先前处于INVALID状态的全部现有PL/SQL模块
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL*PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
SQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem gviswana 06/25/02 - Add documentation
SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem rburns 11/12/01 - validate all components after compiles
SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
SQL> Rem rburns 09/29/01 - use 9.2.0
SQL> Rem rburns 09/20/01 - add check for CATPROC valid
SQL> Rem rburns 07/06/01 - get version from instance view
SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem skabraha 09/25/00 - validate is now a keyword
SQL> Rem kosinski 06/14/00 - Persistent parameters
SQL> Rem skabraha 06/05/00 - validate tables also
SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem rshaikh 09/22/99 - quote name for recompile
SQL> Rem ncramesh 08/04/98 - change for sqlplus
SQL> Rem usundara 06/03/98 - merge from 8.0.5
SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
SQL> Rem Mark Ramacher (mramache) was the original
SQL> Rem author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> @@utlprp.sql 0
SQL> Rem Copyright (c) 2003, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlprp.sql - Recompile invalid objects in the database
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem This script is typically used to recompile invalid objects
SQL> Rem remaining at the end of a database upgrade or downgrade.
SQL> Rem
SQL> Rem Although invalid objects are automatically recompiled on demand,
SQL> Rem running this script ahead of time will reduce or eliminate
SQL> Rem latencies due to automatic recompilation.
SQL> Rem
SQL> Rem This script is a wrapper based on the UTL_RECOMP package.
SQL> Rem UTL_RECOMP provides a more general recompilation interface,
SQL> Rem including options to recompile objects in a single schema. Please
SQL> Rem see the documentation for package UTL_RECOMP for more details.
SQL> Rem
SQL> Rem INPUTS
SQL> Rem The degree of parallelism for recompilation can be controlled by
SQL> Rem providing a parameter to this script. If this parameter is 0 or
SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate
SQL> Rem level of parallelism based on Oracle parameters cpu_count and
SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential
SQL> Rem recompilation is used. Please see the documentation for package
SQL> Rem UTL_RECOMP for more details.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem apfwkr 01/03/12 - Backport apfwkr_blr_backport_13059165_11.2.0.3.0
SQL> Rem from st_rdbms_11.2.0
SQL> Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
SQL> Rem not needed.
SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
SQL> Rem cdilling 01/21/08 - add support for ORA-30552
SQL> Rem cdilling 08/27/07 - check disabled indexes only
SQL> Rem cdilling 05/22/07 - add support for ORA-38301
SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
SQL> Rem rburns 03/17/05 - use dbms_registry_sys
SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics
SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability
SQL> Rem rburns 09/20/04 - fix validate_components
SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here
SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808
SQL> Rem gviswana 05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2014-12-23 18:06:58
1 row selected.
SQL>
SQL> DOC
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>#
SQL>
SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2014-12-23 18:08:59
1 row selected.
SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
SQL> select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;
OBJECTS WITH ERRORS
-------------------
0
1 row selected.
SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
1 row selected.
SQL>
SQL>
SQL> Rem
SQL> Rem Declare function local_enquote_name to pass FALSE
SQL> Rem into underlying dbms_assert.enquote_name function
SQL> Rem
SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
2 return varchar2 is
3 begin
4 return dbms_assert.enquote_name(str, FALSE);
5 end local_enquote_name;
6 /
Function created.
SQL> Rem
SQL> Rem If sys.enabled$index table exists, then re-enable
SQL> Rem list of functional indexes that were enabled prior to upgrade
SQL> Rem The table sys.enabled$index table is created in catupstr.sql
SQL> Rem
SQL> SET serveroutput on
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 p_null CHAR(1);
5 p_schemaname VARCHAR2(30);
6 p_indexname VARCHAR2(30);
7 rebuild_idx_msg BOOLEAN := FALSE;
8 non_existent_index exception;
9 recycle_bin_objs exception;
10 cannot_change_obj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
22 SELECT NULL INTO p_null FROM DBA_OBJECTS
23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
24 object_type = 'TABLE' and rownum <=1;
25
26 -- Select indices to be re-enabled
27 EXECUTE IMMEDIATE q'+
28 SELECT 'ALTER INDEX ' ||
29 local_enquote_name(e.schemaname) || '.' ||
30 local_enquote_name(e.indexname) || ' ENABLE'
31 FROM enabled$indexes e, ind$ i
32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
33 bitand(i.property, 16) != 0+'
34 BULK COLLECT INTO commands;
35
36 IF (commands.count() > 0) THEN
37 FOR i IN 1 .. commands.count() LOOP
38 BEGIN
39 EXECUTE IMMEDIATE commands(i);
40 EXCEPTION
41 WHEN NON_EXISTENT_INDEX THEN NULL;
42 WHEN RECYCLE_BIN_OBJS THEN NULL;
43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
44 END;
45 END LOOP;
46 END IF;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
51 IF rebuild_idx_msg THEN
52 BEGIN
53 DBMS_OUTPUT.PUT_LINE
54 ('The following indexes could not be re-enabled and may need to be rebuilt:');
55
56 OPEN reg_cursor FOR
57 'SELECT e.schemaname, e.indexname
58 FROM enabled$indexes e, ind$ i
59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
60
61 LOOP
62 FETCH reg_cursor INTO p_schemaname, p_indexname;
63 EXIT WHEN reg_cursor%NOTFOUND;
64 DBMS_OUTPUT.PUT_LINE
65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
66 END LOOP;
67 CLOSE reg_cursor;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
72 WHEN OTHERS THEN CLOSE reg_cursor; raise;
73 END;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP function local_enquote_name;
Function dropped.
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components;
...Database user "SYS", database schema "APEX_030200", user# "77" 18:09:01
...Compiled 0 out of 2210 objects considered, 0 failed compilation 18:09:01
...189 packages
...181 package bodies
...360 tables
...12 functions
...19 procedures
...4 sequences
...366 triggers
...946 indexes
...125 views
...0 libraries
...4 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 18:09:01
...Completed key object existence check 18:09:01
...Setting DBMS Registry 18:09:01
...Setting DBMS Registry Complete 18:09:01
...Exiting validate 18:09:01
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
##运行完以上脚本后再做检查，就没有无效对象了
SQL> set line 300
SQL> set line 300
SQL> r
1* SELECT comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified
COMP_NAME STATUS VERSION
----------------------------------- -------------------------------------------- ----------------------------------------
Oracle Workspace Manager VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
Spatial INVALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA Virtual Machine VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
Oracle Application Express VALID 3.2.1.00.1
OWB VALID 11.2.0.4.0
14 rows selected.七、又一次登陆并open 11gR2 数据库
[oracle@woo ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 21 22:13:32 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 5027385344 bytes Fixed Size 2261888 bytes Variable Size 1107299456 bytes Database Buffers 3909091328 bytes Redo Buffers 8732672 bytes Database mounted. Database open. SQL>