eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle官方Patch Set / Bugs Fixed 文档参考列表 | Blog首页 | Hugepages,hugetlb_shm_group与ORA-27125 »

数据库升级 - 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

供参考。


历史上的今天...
    >> 2016-12-01文章:
    >> 2009-12-01文章:
    >> 2008-12-01文章:
    >> 2007-12-01文章:
           繁忙的一周 装修的进展
    >> 2006-12-01文章:
    >> 2005-12-01文章:
           我的DBA飞走了

无觅

By eygle on 2011-12-01 15:27 | Comments (0) | FAQ | 2920 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com