数据库升级 - Pre-Upgrade Information Tool

在数据库的补丁升级过程中，可以运行一个Pre-Upgrade Information Tool，列举编译前的信息，进行相关条件检查。

SQL> STARTUP UPGRADE
Set the system to spool results to a log file for later analysis:
SQL> SPOOL upgrade_info.log
Run the Pre-Upgrade Information Tool:
SQL> @?/rdbms/admin/utlu102i.sql
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF

以下是刚刚升级的数据库输出信息：

SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 12-01-2011 14:58:11
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL10G
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 278 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 382 MB
.... AUTOEXTEND additional space required: 182 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 380 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Label Security [upgrade] VALID
--> EM Repository [upgrade] VALID
.

PL/SQL procedure successfully completed.

SQL> spool off

在这之后，可以执行upgrade过程，前者不是必须的，后者则非常重要，必须成功执行：
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

在catupgrd.sql脚本运行之后，会给出前面升级组件的升级结果：

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2011-12-01 15:13:37
.
Oracle Database 10.2 Upgrade Status Utility 12-01-2011 15:13:37
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:09:21
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:31
Oracle Label Security VALID 10.2.0.4.0 00:00:05
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:05
.
Total Upgrade Time: 00:11:04

当然，在随后数据库启动之后，还应当执行脚本去编译失效对象，完成最后一个环节：

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
159

SQL> select object_name from dba_objects where status='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------
DBA_LOCK_INTERNAL
DBA_LOCK_INTERNAL
DBA_DDL_LOCKS
DBA_DDL_LOCKS
AQ$_AQ_SRVNTFN_TABLE_F
AQ$AQ_SRVNTFN_TABLE
AQ$_SCHEDULER$_JOBQTAB_F
AQ$SCHEDULER$_JOBQTAB
AQ$SCHEDULER$_JOBQTAB_R
AQ$_SCHEDULER$_EVENT_QTAB_F
AQ$SCHEDULER$_EVENT_QTAB_R

OBJECT_NAME
--------------------------------------------------------------------------------
AQ$_AQ$_MEM_MC_F
AQ$_ALERT_QT_F
AQ$ALERT_QT_R
TO_LBAC_LABEL
TO_NUMERIC_LABEL
TO_LBAC_DATA_LABEL
TO_NUMERIC_DATA_LABEL
LBAC_LABEL_TO_CHAR
NUMERIC_LABEL_TO_CHAR
NUMERIC_TO_LBAC
LBAC_TO_NUMERIC

OBJECT_NAME
--------------------------------------------------------------------------------
LABEL_LIST_TO_CHAR
LABEL_LIST_TO_NAMED_CHAR
PRIVS_TO_CHAR
OID_ENABLED
DBA_LBAC_POLICIES
DBA_LBAC_SCHEMA_POLICIES
DBA_LBAC_TABLE_POLICIES
DBA_LBAC_USERS
DBA_LBAC_PROGRAMS
DBA_LBAC_USER_LABELS
DBA_LBAC_USER_PRIVS

OBJECT_NAME
--------------------------------------------------------------------------------
DBA_LBAC_PROG_LABELS
DBA_LBAC_PROG_PRIVS
DBA_LBAC_LABELS
DBA_LBAC_DATA_LABELS
DBA_LBAC_AUDIT_OPTIONS
DBA_LBAC_LABEL_TAGS
DBA_SA_POLICIES
DBA_SA_LABELS
DBA_SA_DATA_LABELS
DBA_SA_LEVELS
DBA_SA_COMPARTMENTS

OBJECT_NAME
--------------------------------------------------------------------------------
DBA_SA_GROUPS
DBA_SA_GROUP_HIERARCHY
DBA_SA_USERS
DBA_SA_USER_LEVELS
DBA_SA_USER_COMPARTMENTS
DBA_SA_USER_GROUPS
DBA_SA_USER_LABELS
DBA_SA_USER_PRIVS
DBA_SA_PROG_PRIVS
USER_SA_SESSION
DBA_SA_TABLE_POLICIES

OBJECT_NAME
--------------------------------------------------------------------------------
DBA_SA_SCHEMA_POLICIES
DBA_SA_AUDIT_OPTIONS
ALL_SA_POLICIES
ALL_SA_DATA_LABELS
ALL_SA_LEVELS
ALL_SA_COMPARTMENTS
ALL_SA_GROUPS
ALL_SA_GROUP_HIERARCHY
ALL_SA_USERS
ALL_SA_USER_LEVELS
ALL_SA_USER_COMPARTMENTS

OBJECT_NAME
--------------------------------------------------------------------------------
ALL_SA_USER_GROUPS
ALL_SA_USER_LABELS
ALL_SA_USER_PRIVS
ALL_SA_PROG_PRIVS
ALL_SA_LABELS
ALL_SA_TABLE_POLICIES
ALL_SA_SCHEMA_POLICIES
ALL_SA_AUDIT_OPTIONS
SETEMVIEWUSERCONTEXT
MGMT$ALERT_CURRENT
MGMT$ALERT_HISTORY

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$AVAILABILITY_CURRENT
MGMT$AVAILABILITY_HISTORY
MGMT$BLACKOUT_HISTORY
MGMT$CLUSTER_INTERCONNECTS
MGMT$CSA_CLIENTS
MGMT$CSA_HOST_COOKIES
MGMT$CSA_HOST_CPUS
MGMT$CSA_HOST_CUSTOM
MGMT$CSA_HOST_IOCARDS
MGMT$CSA_HOST_NICS
MGMT$CSA_HOST_OS_COMPONENTS

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$CSA_HOST_OS_FILESYSTEMS
MGMT$CSA_HOST_OS_PROPERTIES
MGMT$CSA_HOST_SW
MGMT$DB_CONTROLFILES
MGMT$DB_DATAFILES
MGMT$DB_DBNINSTANCEINFO
MGMT$DB_FEATUREUSAGE
MGMT$DB_INIT_PARAMS
MGMT$DB_LICENSE
MGMT$DB_REDOLOGS
MGMT$DB_ROLLBACK_SEGS

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$DB_SGA
MGMT$DB_TABLESPACES
MGMT$DELTA_COMPONENTS
MGMT$DELTA_COMPONENT_DETAILS
MGMT$DELTA_FS_MOUNT
MGMT$DELTA_HARDWARE
MGMT$DELTA_HOST_CONFIG
MGMT$DELTA_INIT
MGMT$DELTA_ONEOFF_PATCHES
MGMT$DELTA_ORACLE_HOME
MGMT$DELTA_OS_COMPONENTS

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$DELTA_OS_COMP_DETAILS
MGMT$DELTA_OS_KERNEL_PARAMS
MGMT$DELTA_PATCHSETS
MGMT$DELTA_PATCHSET_DETAILS
MGMT$DELTA_TABLESPACES
MGMT$DELTA_VENDOR_SW
MGMT$DELTA_VIEW
MGMT$DELTA_VIEW_DETAILS
MGMT$ECM_CURRENT_SNAPSHOTS
MGMT$ECM_VISIBLE_SNAPSHOTS
MGMT$GROUP_DERIVED_MEMBERSHIPS

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$GROUP_FLAT_MEMBERSHIPS
MGMT$GROUP_MEMBERS
MGMT$HA_BACKUP
MGMT$HA_FILES
MGMT$HA_INFO
MGMT$HA_INIT_PARAMS
MGMT$HA_MTTR
MGMT$HA_RMAN_CONFIG
MGMT$HW_NIC
MGMT$INTERFACE_STATS
MGMT$METRIC_COLLECTION

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$METRIC_CURRENT
MGMT$METRIC_DAILY
MGMT$METRIC_DETAILS
MGMT$METRIC_HOURLY
MGMT$MISSING_TARGETS
MGMT$MISSING_TARGETS_IN_GROUPS
MGMT$OS_COMPONENTS
MGMT$OS_FS_MOUNT
MGMT$OS_HW_SUMMARY
MGMT$OS_KERNEL_PARAMS
MGMT$OS_PATCHES

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$OS_SUMMARY
MGMT$RACDB_INTERCONNECTS
MGMT$SOFTWARE_COMPONENTS
MGMT$SOFTWARE_COMPONENT_ONEOFF
MGMT$SOFTWARE_COMP_PATCHSET
MGMT$SOFTWARE_DEPENDENCIES
MGMT$SOFTWARE_HOMES
MGMT$SOFTWARE_ONEOFF_PATCHES
MGMT$SOFTWARE_OTHERS
MGMT$SOFTWARE_PATCHES_IN_HOMES
MGMT$SOFTWARE_PATCHSETS

OBJECT_NAME
--------------------------------------------------------------------------------
MGMT$TARGET
MGMT$TARGET_COMPONENTS
MGMT$TARGET_COMPOSITE
MGMT$TARGET_PROPERTIES
MGMT$TARGET_TYPE

159 rows selected.

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2011-12-01 15:47:04

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>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2011-12-01 15:47:08


PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). 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>#

OBJECTS WITH ERRORS
-------------------
0

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>#

ERRORS DURING RECOMPILATION
---------------------------
0


PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

供参考。