- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
4#
发表于 2012-4-12 15:04:13
How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Goal
After performing an upgrade, how to diagnose components in the DBA_REGISTRY which are showing status INVALID, LOADED, UPGRADED, NO SCRIPT, etc.
For Example :
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
Oracle Enterprise Manager EM 10.2.0.3.0 VALID
Spatial SDO 9.2.0.6.0 NO_SCRIPT
Oracle interMedia ORDIM 10.2.0.3.0 VALID
OLAP Catalog AMD 10.2.0.3.0 VALID
Oracle XML Database XDB 10.2.0.3.0 INVALID
Oracle Text CONTEXT 10.2.0.3.0 VALID
Oracle Expression Filter EXF 10.2.0.3.0 VALID
Oracle Rule Manager RUL 10.2.0.3.0 VALID
Oracle Workspace Manager OWM 10.2.0.1.0 UPGRADED
Oracle Data Mining ODM 10.2.0.3.0 REMOVED
Oracle Database Catalog Views CATALOG 10.2.0.3.0 VALID
Oracle Database Packages and T CATPROC 10.2.0.3.0 VALID
JServer JAVA Virtual Machine JAVAVM 10.2.0.3.0 LOADED
Oracle XDK XML 10.2.0.3.0 VALID
Oracle Database Java Packages CATJAVA 10.2.0.3.0 VALID
OLAP Analytic Workspace APS 10.2.0.3.0 INVALID
Oracle OLAP API XOQ 10.2.0.3.0 VALID
Oracle Real Application Cluster RAC 10.2.0.3.0 OPTION OFF
Solution
First of all, check the current status in dba_registry and the current list of invalid objects.
The output from the utlu102s.sql or utlu112s.sql script (or the last section of the catupgrd.sql output) may not give correct results.
Refer to note Note 456845.1 UTLU102S.SQL May Show Different Results Than Select From DBA_REGISTRY
The following script can be used for collecting complete database information:
Note 556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
1. Check if the component is installed/linked at the binary level. If the component is not installed at the binary level then dba_registry will show the status of this component as NO SCRIPT or sometimes it may result in INVALID status. This happens because in the process of upgrading the component, required upgrade scripts were not found.
FOR UNIX ENVIROMENTS
There are few components which require to be linked with the OPTION ON apart from being installed.
If the component is not linked in the binaries, sometimes the status is shown as 'OPTION OFF'
For example RAC:
Even if RAC is installed at the binary level it may be shown as INVALID/OPTION OFF in dba_registry because oracle binary has not been linked with RAC ON.
After executing following commands, v$option will show RAC is true and now running the catclust.sql will validate RAC in dba_registry.
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle rac_on
If the status is shown as REMOVED then this means component is successfully removed. In case of UPGRADE, the component was not upgraded because it was removed from the database.
Is the component installed (and linked if it is RAC) at the binary level?
No:
----
Please install the component and run the component specific script given below in check 6.
Yes:
-----
Proceed to check 2.
2. Check if component status was VALID in source database:
Please note that with VALID status of the component in the source database, it is also very important that you do not have any invalid objects related to the component in the source database. If there are then also you need to diagnose them in the source database. Following query can be used to find invalid objects:
select object_name,object_type,owner from dba_objects where status = 'INVALID' order by owner,object_type,object_name;
If the invalid objects belong to some user defined schema which is not provided by Oracle then it can be ignored.
Was the component VALID in the source database?
No:
----
1. Restore the database and make the component valid and then perform the upgrade once again.
or
2. Reinstall the component using information given in the following note:
Note.472937.1 Information On Installed Database Components and Schemas
Please note that NOT all the components can be reinstalled. Also reinstalling a component
may result in losing your customized settings for this component. So the decision of reinstalling
should only be taken after considering all other ways to correct the component.
Yes:
-----
1. Proceed to check 3.
3. Check if ultrp.sql was run:
Have you run the following script after the upgrade?
$ORACLE_HOME/rdbms/admin/utlrp.sql
No:
-----
Please run it in normal database mode and check the status once again.
Yes:
-----
Proceed to check 4.
4. Check component dependencies:
Sometimes a component is dependent on another component, so even if there is nothing wrong
with the current component, it may not be VALID because some other component is INVALID
on which it is depending.
Following is the list of dependencies:
Component Name
Required Component
Oracle Data Vault
Oracle Label Security (OLS )
Oracle Expression Filter
JServer JAVA Virtual Machine (JAVAVM)
Oracle Data Mining
JServer JAVA Virtual Machine (JAVAVM)
Oracle XML Database (XDB)
InterMedia
JServer JAVA Virtual Machine (JAVAVM)
Oracle XML Database (XDB)
Oracle XDK (XML)
Oracle Rule Manager
Oracle Expression Filter (EXF)
Oracle XML Database (XDB)
Spatial
JServer JAVA Virtual Machine (JAVAVM)
Oracle XML Database (XDB)
Oracle XDK (XML)
Oracle interMedia (ORDIM)
Ultra Search
JServer JAVA Virtual Machine (JAVAVM)
XDK for JAVA
JServer JAVA Virtual Machine (JAVAVM)
Check if required component is VALID:
No:
-----
Go to check 1 and follow the instructions for the NOT VALID case.
Yes:
-----
Proceed to check 5.
5. Check for any errors reported in the upgrade log files:
Check the upgrade log file to see if any errors occurred during the component upgrade.
You can find the beginning of the upgrade for a specific component by searching in the log for the
following text:
"Rem Upgrade <Component Name>"
For example :
Rem Upgrade Oracle Data Mining
Rem Upgrade Spatial
Rem Upgrade Real Application Cluster and so on.
You need to check this section for any errors until the upgrade of the next section does not start.
If there is an error reported in the particular component section of the upgrade log, then check to see if it
could be caused by an environmental issue (e.g. not able to extend system tablespace). After resolving
the issue, proceed to check 6.
If this error is not user environment specific then search for a bug or note which addresses the
same error message. Also with the error message check invalid objects and see if there are any known
issue associated with that. For example:
Note 782735.1 Oracle Database Server Component Invalid After Upgrade To 10.2.0.4, 11.1.0.6 Or 11.1.0.7
In this case you will find following error in the catupgrd.sql spooled output:
SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
113/5 PL/SQL: SQL Statement ignored
118/44 PL/SQL: ORA-00904: "OTHER_XML": invalid identifier "
And DBMS_SQLPA will be invalid causing "Oracle Server" component to be invalid.
Are there any errors reported?
Yes:
-----
Try to resolve the errors as discussed in the above section.
No:
-----
Proceed to check 6.
6. Rerun the component specific upgrade script:
After completing the previous steps, if the component is still not VALID, then run the specific component
patch script as described below to try to validate the component. These scripts are present even in the
base version even if no patchset has been applied. For example 10.2.0.1.0.
It is advisable to start up the database in UPGRADE mode and connect ‘/as sysdba’ to
execute any of the following scripts.
JServer:
SQL> @?/javavm/install/jvmpatch.sql
XDK for Java:
SQL> @?/xdk/admin/xmlpatch.sql
Java Supplied Packages
SQL> @?/rdbms/admin/catjava.sql
Oracle Text
SQL> @?/ctx/admin/ctxpatch.sql
Oracle XML Database
SQL> @?/rdbms/admin/xdbpatch.sql
Real Application Clusters
SQL> @?/rdbms/admin/catclust.sql
Oracle Workspace Manager
SQL> @?/rdbms/admin/owmpatch.sql
Oracle Data Mining
SQL> @?/rdbms/admin/odmpatch.sql
Messaging Gateway
SELECT dbms_registry_sys.patch_script('MGW') AS patch_name FROM DUAL;
@<Complete path of the Script shown as a result of above command>
OLAP Analytic Workspace
SQL> @?/olap/admin/apspatch.sql
OLAP Catalog
SQL> @?/olap/admin/amdpatch.sql
OLAP API
SQL> @?/olap/admin/xoqpatch.sql
Intermedia
SQL> @?/ord/im/admin/impatch.sql
Spatial
SQL> @?/md/admin/sdopatch.sql
Ultrasearch
SQL> @?/ultrasearch/admin/wkpatch.sql
Oracle Label Security
SELECT dbms_registry_sys.patch_script('OLS') AS patch_name FROM DUAL;
@<Complete path of the Script shown as a result of above command>
Expression Filter
SQL> @?/rdbms/admin/exfpatch.sql
Enterprise Manager Repository
SQL> @?/sysman/admin/emdrep/sql/empatch.sql
Rule Manager
SQL> @?/rdbms/admin/rulpatch.sql
After running any of the above scripts run utlrp.sql once again and see if the component is validated or not.
If not then see if there were any errors in the spooled output of script execution and according to the error proceed further.
References
NOTE:456845.1 - UTLU102S.SQL, UTLU111S.SQL and UTLU112S.SQL May Show Different Results Than Select From DBA_REGISTRY
NOTE:472937.1 - Information On Installed Database Components and Schemas
NOTE:556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) |
|
发表于 2012-4-12 14:19:43
|
查看: 6671|
回复: 3
|手机版|Archiver|Oracle数据库数据恢复、性能优化
IP卡
狗仔卡
分享
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡
