1.手动升级前工作
(1).备份数据库
(2).运行pre-upgrade information tool(如果是使用DBUA升级，会自动执行该脚本工具)
$ cd /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin $ sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 6 08:26:57 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @preupgrd.sql Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in YB... *************************************************************************** ************************************************************ ====>> ERRORS FOUND for YB <<==== The following are *** ERROR LEVEL CONDITIONS *** that must be addressed prior to attempting your upgrade. Failure to do so will result in a failed upgrade. You MUST resolve the above errors prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for YB <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /u01/app/oracle/cfgtoollogs/yb/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /u01/app/oracle/cfgtoollogs/yb/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /u01/app/oracle/cfgtoollogs/yb/preupgrade/postupgrade_fixups.sql ************************************************************ *************************************************************************** Pre-Upgrade Checks in YB Completed. *************************************************************************** *************************************************************************** *************************************************************************** SQL> @/u01/app/oracle/cfgtoollogs/yb/preupgrade/preupgrade_fixups.sql Pre-Upgrade Fixup Script Generated on 2015-08-06 08:39:48 Version: 12.1.0.2 Build: 006 Beginning Pre-Upgrade Fixups... Executing in container YB ********************************************************************** Check Tag: DEFAULT_PROCESS_COUNT Check Summary: Verify min process count is not too low Fix Summary: Review and increase if needed, your PROCESSES value. ********************************************************************** Fixup Returned Information: WARNING: --> Process Count may be too low Database has a maximum process count of 150 which is lower than the default value of 300 for this release. You should update your processes value prior to the upgrade to a value of at least 300. For example: ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE or update your init.ora file. ********************************************************************** ********************************************************************** [Pre-Upgrade Recommendations] ********************************************************************** ***************************************** ********* Dictionary Statistics ********* ***************************************** Please gather dictionary statistics 24 hours prior to upgrading the database. To gather dictionary statistics execute the following command while connected as SYSDBA: EXECUTE dbms_stats.gather_dictionary_stats; ^^^ MANUAL ACTION SUGGESTED ^^^ ************************************************** ************* Fixup Summary ************ 1 fixup routine generated an INFORMATIONAL message that should be reviewed. **************** Pre-Upgrade Fixup Script Complete ********************* SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
(3).将当前库的参数文件拷贝到新的oracle_home下。使用了asm就不需要进行该步骤
$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileyb.ora /u01/app/oracle12/product/12.1.0/dbhome_1/dbs/
2.手动升级过程
(1).关闭源数据库
SQL> shutdown immediate;
如果是windows：停掉oracle服务、使用oradim删除相关服务、使用oradim创建新的服务
(2).修改/etc/oratab(修改为目标库的ORACLE_HOME)
yb:/u01/app/oracle12/product/12.1.0/dbhome_1:N
(3).设置好目标库的参数(ORACLE_HOME、ORACLE_BASE、ORACLE_SID、PATH)
(4).拷贝参数文件到新的ORACLE_HOME/dbs目录
(5).进入$ORACLE_HOME/rdbms/admin
$ cd $ORACLE_HOME/rdbms/admin
(6).登陆sql*plus，以sys用户
$ sqlplus '/as sysdba'
(7).startup upgrade。（可能需要指定参数文件的路径）
SQL> startup upgrade;
(8).运行catctl.pl脚本
$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql Argument list for [catctl.pl] SQL Process Count n = 4 SQL PDB Process Count N = 0 Input Directory d = 0 Phase Logging Table t = 0 Log Dir l = 0 Script s = 0 Serial Run S = 0 Upgrade Mode active M = 0 Start Phase p = 0 End Phase P = 0 Log Id i = 0 Run in c = 0 Do not run in C = 0 Echo OFF e = 1 No Post Upgrade x = 0 Reverse Order r = 0 Open Mode Normal o = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 Display Phases y = 0 Child Process I = 0 catctl.pl version: 12.1.0.2.0 Oracle Base = /u01/app/oracle12 Analyzing file catupgrd.sql Log files in /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin catcon: ALL catcon-related output will be written to catupgrd_catcon_24051.lst catcon: See catupgrd*.log files for output generated by scripts catcon: See catupgrd_*.lst files for spool files, if any Number of Cpus = 8 SQL Process Count = 4 ------------------------------------------------------ Phases [0-73] Serial Phase #: 0 Files: 1 Time: 96s Serial Phase #: 1 Files: 5 Time: 30s Restart Phase #: 2 Files: 1 Time: 0s Parallel Phase #: 3 Files: 18 Time: 4s Restart Phase #: 4 Files: 1 Time: 0s Serial Phase #: 5 Files: 5 Time: 15s Serial Phase #: 6 Files: 1 Time: 8s Serial Phase #: 7 Files: 4 Time: 5s Restart Phase #: 8 Files: 1 Time: 1s Parallel Phase #: 9 Files: 62 Time: 17s Restart Phase #:10 Files: 1 Time: 0s Serial Phase #:11 Files: 1 Time: 10s Restart Phase #:12 Files: 1 Time: 0s Parallel Phase #:13 Files: 91 Time: 6s Restart Phase #:14 Files: 1 Time: 0s Parallel Phase #:15 Files: 111 Time: 11s Restart Phase #:16 Files: 1 Time: 0s Serial Phase #:17 Files: 3 Time: 1s Restart Phase #:18 Files: 1 Time: 0s Parallel Phase #:19 Files: 32 Time: 13s Restart Phase #:20 Files: 1 Time: 0s Serial Phase #:21 Files: 3 Time: 6s Restart Phase #:22 Files: 1 Time: 0s Parallel Phase #:23 Files: 23 Time: 58s Restart Phase #:24 Files: 1 Time: 0s Parallel Phase #:25 Files: 11 Time: 26s Restart Phase #:26 Files: 1 Time: 0s Serial Phase #:27 Files: 1 Time: 0s Restart Phase #:28 Files: 1 Time: 0s Serial Phase #:30 Files: 1 Time: 0s Serial Phase #:31 Files: 257 Time: 14s Serial Phase #:32 Files: 1 Time: 0s Restart Phase #:33 Files: 1 Time: 0s Serial Phase #:34 Files: 1 Time: 3s Restart Phase #:35 Files: 1 Time: 0s Restart Phase #:36 Files: 1 Time: 0s Serial Phase #:37 Files: 4 Time: 37s Restart Phase #:38 Files: 1 Time: 0s Parallel Phase #:39 Files: 13 Time: 36s Restart Phase #:40 Files: 1 Time: 0s Parallel Phase #:41 Files: 10 Time: 5s Restart Phase #:42 Files: 1 Time: 0s Serial Phase #:43 Files: 1 Time: 7s Restart Phase #:44 Files: 1 Time: 0s Serial Phase #:45 Files: 1 Time: 4s Serial Phase #:46 Files: 1 Time: 0s Restart Phase #:47 Files: 1 Time: 0s Serial Phase #:48 Files: 1 Time: 84s Restart Phase #:49 Files: 1 Time: 0s Serial Phase #:50 Files: 1 Time: 30s Restart Phase #:51 Files: 1 Time: 0s Serial Phase #:52 Files: 1 Time: 15s Restart Phase #:53 Files: 1 Time: 0s Serial Phase #:54 Files: 1 Time: 284s Restart Phase #:55 Files: 1 Time: 0s Serial Phase #:56 Files: 1 Time: 47s Restart Phase #:57 Files: 1 Time: 0s Serial Phase #:58 Files: 1 Time: 93s Restart Phase #:59 Files: 1 Time: 0s Serial Phase #:60 Files: 1 Time: 457s Restart Phase #:61 Files: 1 Time: 0s Serial Phase #:62 Files: 1 Time: 38s Restart Phase #:63 Files: 1 Time: 0s Serial Phase #:64 Files: 1 Time: 1s Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle12/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle12/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err returned from sqlpatch Time: 27s Serial Phase #:66 Files: 1 Time: 28s Serial Phase #:68 Files: 1 Time: 0s Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle12/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle12/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err returned from sqlpatch Time: 34s Serial Phase #:70 Files: 1 Time: 66s Serial Phase #:71 Files: 1 Time: 0s Serial Phase #:72 Files: 1 Time: 0s Serial Phase #:73 Files: 1 Time: 20s Grand Total Time: 1640s LOG FILES: (catupgrd*.log) Upgrade Summary Report Located in: /u01/app/oracle12/product/12.1.0/dbhome_1/cfgtoollogs/yb/upgrade/upg_summary.log Grand Total Upgrade Time: [0d:0h:27m:20s]
默认会并发执行该脚本进行升级。可以手动取消并发执行。
(9).重启数据库。上一步运行成功会关闭数据库
(10).运行utlu121s.sql并查看结果
SQL> @/u01/app/oracle12/product/12.1.0/dbhome_1/rdbms/admin/utlu121s.sql PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. CATCTL REPORT = /u01/app/oracle12/product/12.1.0/dbhome_1/cfgtoollogs/yb/upgrade/upg_summary.log PL/SQL procedure successfully completed. Oracle Database 12.1 Post-Upgrade Status Tool 08-06-2015 09:36:08 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:06:52 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:01:23 Oracle Workspace Manager VALID 12.1.0.2.0 00:00:36 OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:14 Oracle OLAP API VALID 12.1.0.2.0 00:00:12 Oracle XDK VALID 12.1.0.2.0 00:00:30 Oracle Text VALID 12.1.0.2.0 00:00:37 Oracle XML Database VALID 12.1.0.2.0 00:04:06 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:09 Oracle Multimedia VALID 12.1.0.2.0 00:01:32 Spatial UPGRADED 12.1.0.2.0 00:07:36 Oracle Application Express VALID 4.2.5.00.08 00:00:00 Final Actions 00:00:52 Post Upgrade 00:01:02 Total Upgrade Time: 00:26:15 PL/SQL procedure successfully completed. SQL> SQL> -- SQL> -- Update Summary Table with con_name and endtime. SQL> -- SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName, 2 con_name = SYS_CONTEXT('USERENV','CON_NAME'), 3 endtime = SYSDATE 4 WHERE con_id = -1; 1 row updated. SQL> commit; Commit complete.
要确保catuppst.sql脚本执行成功。如果没有成功，需要手动运行。($ORACLE_HOME/rdbms/admin/catuppst.sql)
如果没有错误发生，升级结束。
3.手动升级后期的工作
(1).运行utlrp.sql编译无效对象
(1).运行utlrp.sql编译无效对象
SQL> @/u01/app/oracle12/product/12.1.0/dbhome_1/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, 2014, 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 pyam 05/12/14 - Backport pyam_bug-18478064 from main SQL> Rem pyam 04/08/14 - 18478064: factor out to reenable_indexes.sql SQL> Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL 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 2015-08-06 09:38:08 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 2015-08-06 09:39:31 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 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 SQL> SQL> Rem ===================================================================== SQL> Rem Reenable indexes that may have been disabled, based on the SQL> Rem table SYS.ENABLED$INDEXES SQL> Rem ===================================================================== SQL> SQL> @@?/rdbms/admin/reenable_indexes.sql SQL> Rem SQL> Rem $Header: rdbms/admin/reenable_indexes.sql /st_rdbms_12.1/1 2014/05/20 10:49:30 pyam Exp $ SQL> Rem SQL> Rem reenable_indexes.sql SQL> Rem SQL> Rem Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem reenable_indexes.sql - <one-line expansion of the name> SQL> Rem SQL> Rem DESCRIPTION SQL> Rem <short description of component this file declares/defines> SQL> Rem SQL> Rem NOTES SQL> Rem <other useful comments, qualifications, etc.> SQL> Rem SQL> Rem BEGIN SQL_FILE_METADATA SQL> Rem SQL_SOURCE_FILE: rdbms/admin/reenable_indexes.sql SQL> Rem SQL_SHIPPED_FILE: SQL> Rem SQL_PHASE: SQL> Rem SQL_STARTUP_MODE: NORMAL SQL> Rem SQL_IGNORABLE_ERRORS: NONE SQL> Rem SQL_CALLING_FILE: SQL> Rem END SQL_FILE_METADATA SQL> Rem SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem pyam 04/03/14 - Reenable indexes based on sys.enabled$indexes SQL> Rem (formerly in utlprp.sql) SQL> Rem pyam 04/03/14 - Created SQL> Rem 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> SET serveroutput off SQL> SQL> SQL> Rem ===================================================================== SQL> Rem Run component validation procedure SQL> Rem ===================================================================== SQL> SQL> SET serveroutput on SQL> EXECUTE dbms_registry_sys.validate_components; ...Database user "SYS", database schema "APEX_040200", user# "91" 09:39:42 ...Compiled 0 out of 3014 objects considered, 0 failed compilation 09:39:42 ...271 packages ...263 package bodies ...452 tables ...11 functions ...16 procedures ...3 sequences ...457 triggers ...1320 indexes ...211 views ...0 libraries ...6 types ...0 type bodies ...0 operators ...0 index types ...Begin key object existence check 09:39:42 ...Completed key object existence check 09:39:42 ...Setting DBMS Registry 09:39:42 ...Setting DBMS Registry Complete 09:39:43 ...Exiting validate 09:39:43 PL/SQL procedure successfully completed. SQL> SET serveroutput off SQL> SQL> SQL> Rem =========================================================================== SQL> Rem END utlrp.sql SQL> Rem =========================================================================== SQL>
(2).utluiobj.sql，确认是否有包和class无效
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql SQL> Rem SQL> Rem $Header: rdbms/admin/utluiobj.sql /main/2 2012/07/31 19:37:48 cdilling Exp $ SQL> Rem SQL> Rem utluiobj.sql SQL> Rem SQL> Rem Copyright (c) 2007, 2012, Oracle and/or its affiliates. SQL> Rem All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem utluiobj.sql - UTility Upgrade Invalid OBJects Tool SQL> Rem SQL> Rem DESCRIPTION SQL> Rem This script provides information about invalid objects after the SQL> Rem upgrade. It outputs the difference between the invalid objects SQL> Rem that exist after the upgrade and invalid objects that existed SQL> Rem prior to upgrade. SQL> Rem SQL> Rem NOTES SQL> Rem Run connected AS SYSDBA to the database that was upgraded SQL> Rem If there were more than 5000 non-system invalid objects then SQL> Rem there were too many invalid non-system objects to track. SQL> Rem SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem cdilling 07/17/12 - update version to 12.1 SQL> Rem cdilling 12/13/07 - Created SQL> Rem SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE 2 type cursor_t IS REF CURSOR; 3 invobj_cursor cursor_t; 4 p_owner VARCHAR2(30); 5 p_obj_name VARCHAR2(30); 6 p_obj_type VARCHAR2(30); 7 no_such_table EXCEPTION; 8 PRAGMA exception_init(no_such_table, -942); 9 10 BEGIN 11 -- Display the list of objects that are invalid after the upgrade 12 -- but were not invalid prior to upgrade. 13 -- registry$sys_inv_objs table is created by the pre-upgrade 14 -- utility file (utlu111i.sql) 15 DBMS_OUTPUT.PUT_LINE('.'); 16 DBMS_OUTPUT.PUT_LINE( 17 'Oracle Database 12.1 Post-Upgrade Invalid Objects Tool ' || 18 TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')); 19 DBMS_OUTPUT.PUT_LINE('.'); 20 DBMS_OUTPUT.PUT_LINE( 21 'This tool lists post-upgrade invalid objects that were not invalid'); 22 DBMS_OUTPUT.PUT_LINE( 23 'prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).'); 24 DBMS_OUTPUT.PUT_LINE('.'); 25 DBMS_OUTPUT.PUT_LINE( 26 LPAD('Owner', 32) || LPAD('Object Name',32) || LPAD('Object Type', 32)); 27 DBMS_OUTPUT.PUT_LINE('.'); 28 29 BEGIN 30 OPEN invobj_cursor FOR 31 'select owner, object_name, object_type 32 from dba_objects 33 where status !=''VALID'' AND owner in (''SYS'',''SYSTEM'') 34 MINUS 35 select owner, object_name, object_type 36 from registry$sys_inv_objs 37 order by owner, object_name'; 38 39 LOOP 40 FETCH invobj_cursor INTO p_owner, p_obj_name, p_obj_type; 41 EXIT WHEN invobj_cursor%NOTFOUND; 42 DBMS_OUTPUT.PUT_LINE(LPAD(p_owner, 32) || LPAD(p_obj_name, 32) || LPAD(p_obj_type, 32)); 43 END LOOP; 44 CLOSE invobj_cursor; 45 EXCEPTION 46 WHEN no_such_table THEN 47 DBMS_OUTPUT.PUT_LINE( 48 'The table registry$sys_inv_objs does not exist. The pre-upgrade tool,'); 49 DBMS_OUTPUT.PUT_LINE( 50 'utlu111i.sql, creates and populates registry$sys_inv_objs. To use this'); 51 DBMS_OUTPUT.PUT_LINE( 52 'post-upgrade tool, you must have run utlu111i.sql prior to upgrading'); 53 DBMS_OUTPUT.PUT_LINE( 54 'the database. '); 55 END; 56 57 BEGIN 58 OPEN invobj_cursor FOR 59 'select owner, object_name, object_type 60 from dba_objects 61 where status !=''VALID'' AND owner NOT in (''SYS'',''SYSTEM'') 62 MINUS 63 select owner, object_name, object_type 64 from registry$nonsys_inv_objs 65 order by owner, object_name'; 66 LOOP 67 FETCH invobj_cursor INTO p_owner, p_obj_name, p_obj_type; 68 EXIT WHEN invobj_cursor%NOTFOUND; 69 DBMS_OUTPUT.PUT_LINE(LPAD(p_owner, 32) || LPAD(p_obj_name, 32) || LPAD(p_obj_type, 32)); 70 END LOOP; 71 CLOSE invobj_cursor; 72 73 EXCEPTION 74 WHEN no_such_table THEN NULL; 75 END; 76 77 END; 78 79 / . Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 08-06-2015 09:40:28 . This tool lists post-upgrade invalid objects that were not invalid prior to upgrade (it ignores pre-existing pre-upgrade invalid objects). . Owner Object Name Object Type . PL/SQL procedure successfully completed. SQL> SQL> SET SERVEROUTPUT OFF SQL>
(3).收集固定对象的统计信息：dbms_stats.gather_fixed_object_stats
(4).再次运行ut1u121s.sql脚本
(5).如果需要使用32k扩展数据类型：
--修改参数max_string_size
--重启数据库：startup upgrade。
--执行$ORACLE_HOME/rdbms/admin/utl32k.sql
--shutdown immediate; --startup
(6).备份库