探索Oracle之数据库升级八 12c Downgrade 11gR2
探索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>
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。