Oracle XDB 组件 重建 说明
一. XDB 组件说明
1.1 官网说明:
XDB 全称XML DB,官网的说明如下:
http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb01int.htm
SearchXML Data using Oracle Text
BuildMessaging Applications using Oracle Streams Advanced Queuing
StandardsSupported by Oracle XML DB
OracleXML DB Technical Support
OracleXML DB Examples Used in This Manual
FurtherOracle XML DB Case Studies and Demonstrations
这里我们简单的看一下overview,具体内容,感兴趣的自己研究。
OracleXML DB is a set of Oracle Database technologies related tohigh-performance handling of XML data: storing, generating, accessing,searching, validating, transforming, evolving, and indexing. Itprovides native XML support by encompassing both the SQL and XML data models inan interoperable way. Oracle XML DB is included as part of Oracle Databasestarting with Oracle9i Release 2 (9.2).
--Oracle 从9.2中引入XDB,XDB 是关于高性能处理XML 数据的一个技术的集合,其包含storing,generating, accessing,searching, validating, transforming, evolving, and indexing。 XDB提供本地XML支持。
1.2 MOS 说明:
Oracle8i/9i/10g/11g 组件(Components) 说明
http://blog.csdn.net/tianlesoftware/article/details/5937382
ExtensibleMarkup Language (XML) is a tag-based markup language that lets developerscreate their own tags to describe data that's exchanged between applicationsand systems. XML is widely adopted as the common language of informationexchange between companies. It is human-readable; that is, it is plain text.Because it is plain text, XML documents and XML-based messages can be senteasily using common protocols, such as HTTP or FTP.
Oracle XML DBtreats XML as a native datatype in the database. Oracle XML DB is not aseparate server. The XML data model encompasses both unstructured content andstructured data. Applications can use standard SQL and XML operators togenerate complex XML documents from SQL queries and to store XML documents.
Oracle XML DB provides capabilities forboth content-oriented and data-oriented access. For developers who see XML asdocuments (news stories, articles, and so on), Oracle XML DB provides an XMLrepository accessible from standard protocols and SQL.
The Oracle XMLdeveloper's kits (XDK) contain the basic building blocks for reading,manipulating, transforming, and viewing XML documents, whether on a file systemor stored in a database. They are available for Java, C, and C++. Unlike manyshareware and trial XML components, the production Oracle XDKs are fullysupported and come with a commercial redistribution license.
Schema:
---------
XDB: Stores XML database related schema objects.
--XDB compent 对应的用户,其用来存储相关对象。
1.3 使用XDB 组件的注意事项
(1)Please note that 9.2.0.3 is a mandatoryminimum patch level for XDB. Customers using XDB must be on Oracle databaseversion 9.2.0.3 or higher.
(2)It is recommended to take a fullbackup of the database before removing / reinstalling XDB.
(3)Before installing or upgrading XDB,make sure the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable isset correctly. That is, the first directory referenced should be$ORACLE_HOME/lib. This environment variable is used to resolve thelocation of the shared library "libxdb.so".
(4)XDB must have execute permissionson the DBMS_LOB and UTL_FILE packages. XDB automatically has theseprivileges because they are granted to PUBLIC by default. If theseprivileges have been revoked from PUBLIC for security reasons, errors will be reportedduring the installation / upgrade of XDB and many XDB objects will becomeinvalid, making the component itself invalid. Therefore, grant executeprivileges on these packages back to PUBLIC before installing / upgrading XDBor if you do not wish for PUBLIC to have these permissions, after the install /upgrade, grant execute permissions on these packages directly to XDB and run$ORACLE_HOME/rdbms/admin/utlrp.sql to recompile the invalid objects.
1.4 查看XDB 组件
可以用如下SQL 查看一下DB中的组件:
SQL> col comp_id for a15
SQL> col version for a15
SQL> col comp_name for a30
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
--------------------------------------------- --------------- -----------------
AMD OLAP Catalog 11.2.0.3.0 VALID
EM Oracle Enterprise Manager 11.2.0.3.0 VALID
SDO Spatial 11.2.0.3.0 VALID
ORDIM Oracle Multimedia 11.2.0.3.0 INVALID
XDB Oracle XML Database 11.2.0.3.0 INVALID
CONTEXT Oracle Text 11.2.0.3.0 VALID
ODM Oracle Data Mining 11.2.0.3.0 VALID
EXF Oracle Expression Filter 11.2.0.3.0 VALID
RUL Oracle Rules Manager 11.2.0.3.0 VALID
OWM Oracle Workspace Manager 11.2.0.3.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID
COMP_ID COMP_NAME VERSION STATUS
--------------------------------------------- --------------- -----------------
CATPROC Oracle Database Packages and T11.2.0.3.0 INVALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 VALID
XML Oracle XDK 11.2.0.3.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.3.0 VALID
XOQ Oracle OLAP API 11.2.0.3.0 VALID
APS OLAP Analytic Workspace 11.2.0.3.0 VALID
17 rows selected.
注意这里的2个组件:
(1)XDB 是无效的。
(2)ORDIM 也是无效的。
OracleMultimedia (11g之前叫 interMedia)is a feature of Oracle Database Standard Edition and Enterprise Edition thatenables efficient management and retrieval of multimedia (images, audio, andvideo) data in an integrated fashion with other enterprise information. OracleMultimedia provides comprehensive support for DICOM, the widely adoptedstandard for medical images, videos and structured reports in Oracle Database.Oracle Multimedia extends Oracle Database reliability, availability, security,and data management capabilities to multimedia content in traditional,Internet, electronic commerce, medical, and media-rich applications.
--Oracle Multimedia 是Oracle 标准版和企业版里的一个特性,其可以用来管理images, audio, and video.
更多内容参考MOS:
Master Note forOracle Multimedia/interMedia [ID 1078222.1]
这里ORDIM 无效是因为XDB 导致的,因为Oracle Multimedia组件的信息是保存在XDB Schemas中的,所以XDB无效,也会导致Multimedia 组件无效。在我们重建XDM组件之后,在reload 一下ORDIM组件就可以了。
二. 重建XDB 组件
XDB 组件的重建,参考:
Master Note for Oracle XML Database (XDB)Install / Deinstall [ID 1292089.1]
2.1 Reloading XDB
The reloadprocedure recreates all of the PL/SQL packages and types. An XDB reload isalways preferred over an XDB removal and reinstall. Since xdbrelod.sql iscalled in xdbpatch.sql, you can alternatively run xdbpatch.sql to recreate allof the XDB related packages.
--Reload 过程将重建所有的PL/SQL 包和类型,Reload 比removal和reinstall 更具有优势。在考虑重建之前可以先尝试Reload。 因为xdbreload.sql 在xdbpatch.sql脚本中会自动调用,所以也可以运行xdbpatch.sql 来创建所有的XDB 关联包。
2.1.1 Oracle 9i - XDB Reload
SQL> spool xdbreload.log
SQL> connect / as sysdba
SQL> set echo on;
SQL> shutdown immediate;
SQL> startup migrate;
SQL> @?/rdbms/admin/xdbrelod.sql
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
2.1.2 Oracle 10.1 and above(10g 以上版本) - XDBReload
SQL> spool xdbreload.log
SQL> connect / as sysdba
SQL> set echo on;
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @?/rdbms/admin/xdbrelod.sql
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
2.2 Deinstalling and Reinstalling XDB
2.2.1 相关的注意事项
(1) Use XDB removal and reinstallonly if not using this feature or under the direction of Oracle Support afterit has been verified which objects will need to be recreated.
(2) For database releases 10.1.xand above, XDB is mandatory in order to use any of the XMLTYPE functions. Thisis true even if the XDB repository is not being used and/or there are noregistered schemas.
(3) Prior to Oracle 11.1, a validinstallation of JAVA Virtual Machine (JVM).
(4) Prior to Oracle 10.2, a validinstallation of XDK is also required
(5) Allocate at least 200 MB forthe XDB repository tablespace datafile.
(6) Ensure that theSHARED_POOL_SIZE and JAVA_POOL_SIZE is set to at least 150 MB.
(7) If you have any doubts/concernswith reinstalling XDB or you need further assistance, please contact OracleSupport and log a Service Request.
2.2.2 Oracle 9.2 - XDB Removal and Reinstall
(1)XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
(2)XDB Installation
The catqm.sql script requires the following parameters be passed to it whenrun:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified. Thespecified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
Therefore, the syntax to run catqm.sql is thefollowing:
SQL> @?/rdbms/admin/catqm.sql A B C
For example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP
## IMPORTANT: You must shutdown and restart thedatabase between removal and reinstall ##
--注意:在removal 和reinstall 之间必须重启DB.
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs>
SQL> @?/rdbms/admin/catxdbj.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
2.2.3 Oracle 10g - XDB Removal and Reinstall
(1)XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
(2)XDB Installation
The catqm.sql script requires the following parameters be passed to it whenrun:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can bespecified.
The specified tablespace must already exist prior torunning the script.)
C. XDB user temporary tablespace
Therefore, the syntax to run catqm.sql is the following:
SQL> @?/rdbms/admin/catqm.sql A B C
For example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP
## IMPORTANT: You must shutdown and restart the database between removal andreinstall ##
--注意:在removal 和reinstall 之间必须重启DB.
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
2.2.4 Oracle 11g - XDB Removal and Reinstall
2.2.4.1 注意事项:
(1)When a binary XMLType is created,the data is stored in a proprietary format on disk that represents a post parsepersistence model. This requires the need to store information about how totransverse the XML data without having to parse it again. The data that isstored in order to accomplish this is maintained in dictionary type tables inthe XDB user schema, not in the user schema that created the table. What thismeans is that the removal script drops the XDB user and in turn loses thisinformation from both Binary and Object-Relational xmltype tables/columns.So if directed to remove and reinstall XDB with the catnoqm.sql and catqm.sqlscripts, run the following code block to verify that no Binary and/or ObjectRelational XMLType tables and columns exist:
connect / as sysdba
--
-- Check the storage of XMLType tables.
--
select owner, table_name
from dba_xml_tables
where storage_type in ('OBJECT-RELATIONAL', 'BINARY');
-- A default seed database with the example schemas installed
-- will have ones owned by XDB, MDSYS and OE.
--
-- Check the storage of XMLType columns.
--
select owner, table_name
from dba_xml_tab_cols
where storage_type in ('OBJECT-RELATIONAL', 'BINARY');
-- A default seed database with the example schemas installed
-- will have ones owned by XDB, MDSYS, ORDDATA, APEX_030200 and OE.
-- Please see the following section as it relates to ORDDATA and APEX_030200
(2)If it is necessary to re-installXDB and you would like to put back all binary data into the database withouthaving to reload this from XML files, please review the following note on howto do this:
(Doc ID 1405457.1) How to move XMLType tables/columns withBinary XML Storage between schemas/databases
(3)What if the database is using theDICOM and/or Oracle Application Express (APEX) features?
If the above code block has objects ownedby ORDDATA and/or APEX_030200, it means those components are installed in thedatabase. If those components are being used in a production capacity, XDBshould not be removed and reinstalled as data that is maintained in the XDBuser schema will be lost.
(4)The default XMLType storage modelis used if a storage model is not specified when creating an XMLType table orcolumn. Prior to Oracle Database 11g Release 2, unstructured (CLOB) storage wasused by default. The default storage model is now binary XML storage.
Please see the following document for moreinformation:
(DocID 1207893.1) Change in default storage model of XMLType to BINARYXML in 11.2.0.2
(5)Beginning with 11g, JAVA VirtualMachine (JVM) is no longer required for a successful installation of XDB.However, if an attempt is made to run XQUERY statements which use a functionalevaluation path, an error will be thrown stating that JVM is not installed.Also note that JVM must be installed for XDK functionality.
(6)Beginning with 11.2, XDB nowsupports SecureFiles. To use SecureFiles, compatibility must be set to 11.2. IfSecureFiles will be used, the tablespace specified for the XDB repository mustbe using Automatic Segment Space Management (ASSM).
SinceSecureFiles is now supported with 11.2, an additional parameter was added tothe catqm.sql script in that release.
2.2.4.2 Oracle 11.1 - XDB Removal and Reinstall
(1)XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
(2)XDB Installation
The catqm.sql script requires the following parameters be passed to it whenrun:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can bespecified.
The specified tablespace must already existprior to running the script.)
C. XDB user temporary tablespace
Therefore, the syntax to run catqm.sql is the following:
SQL> @?/rdbms/admin/catqm.sql A B C
For example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP
## IMPORTANT: You must shutdown and restart thedatabase between removal and reinstall ##
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
2.2.4.3 Oracle 11.2 - XDB Removal and Reinstall
(1)XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
(2)XDB Installation
The catqm.sql script requires the following parameters be passed to it whenrun:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP canbe specified.
The specified tablespace must already exist prior torunning the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will useSecureFile storage.
If NO is specified, LOBS will beused.
To use SecureFiles, compatibility must beset to 11.2.
The tablespace specified for the XDBrepository must be using
Automatic Segment Space Management (ASSM)for SecureFiles to be used.)
Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D
For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES
## IMPORTANT: You must shutdown and restart the database between removal andreinstall ##
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs> <SecureFiles = YES/NO>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
2.3 Verify XDB Installation –验证XDB 安装
spool xdb_status.txt
set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25
-- Check status of XDB
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
-- Check for invalid objects owned by XDB
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';
spool off;
三. Reload ORDIM 组件
在前面提到Oracle Multimedia(ORDIM)组件需要使用XDB组件,在我们重建XDB组件之后需要Reload 一些ORDIM组件。
Oracle 11g参考:
How To Reload Oracle Multimedia RelatedInformation When XML Database (=XDB) Has Been Reinstalled [ID 965892.1]
Oracle 10g:参考:
How To Re-register XML Schemas After XDBHas Been Re-installed? [ID 558834.1]
3.1 Oracle 10g 版本
The XDB schemais also used by other products like interMedia and Spatial to register theirXML Schemas. When XDB has been deinstalled and reinstalled for whatever reasonthe XML Schemas for these products will have to be reinstalled as well.
--XDB Schema 被其他的组件所用,比如interMedia(11g中叫Multimedia)和Spatial组件,他们注册自己的信息在XML Schemas中。 当XDB 被删除或者重建时,其他组件在XML中的信息也被卸载,这就导致其他组件的无效。
-- Register XML Schemas for the Rules Manager component (part of CATPROC)
--在XMLSchemas中注册Rules Manager 组件信息:
connect / as sysdba
alter session setcurrent_schema = EXFSYS;
@?/rdbms/admin/rulpbs.sql
-- Register XML Schemas for the interMedia product
--在XMLSchemas 中注册interMedia组件信息:
connect / as sysdba
alter session set current_schema=ORDSYS;
@?/ord/im/admin/imxreg.sql
-- Register XML Schemas for the Locator/Spatial product
--在XMLSchemas中注册Locator/Spatial 组件信息
connect / as sysdba
alter session set current_schema=MDSYS;
@?/md/admin/sdogmlsc.sql
@?/md/admin/sdoepsgx.sql
@?/md/admin/sdogrxml.sql -- not to be run for a Locatorinstallation
3.2 Oracle 11g 版本
OracleMultimedia requires Oracle XML Database (=XDB) to be installed. In cases whereOracle Multimedia is not in use and XDB has been reinstalled, the below stepswill assist to reinstall the part of Oracle Multimedia that uses XDB.
--在进行如下操作之前,记得先备份:
(1)Run SQL*Plus and connect as SYSDBA:
SQL> alter session setcurrent_schema="ORDSYS";
SQL> @<ORACLE_HOME>/ord/im/admin/imxreg.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/impbs.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/impvs.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/imtyb.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/implb.sql;
SQL> @<ORACLE_HOME>/ord/im/admin/imxrepos.sql;
(2)Now run validate_ordim connected asSYSDBA to verify if Oracle Multimedia is valid:
SQL> set serveroutput on
SQL> exec validate_ordim;
SQL> select status, version from DBA_REGISTRY where comp_id = 'ORDIM';
This shouldshow, that Oracle Multimedia is VALID and at the same version as the database.
四.重建XDB组件后,还有无效的XDB对象
如果我们重建XDB组件后,还有有无效的XDB对象,处理方法下:
Invalid XDB Objects After XDB Install [ID429551.1]
当我们重建了XDB后,仍然有一些XDB 对象无效,比如:
DBMS_XMLDOM
DBMS_XMLPARSER
DBMS_XMLSCHEMA
当我们尝试编译时,又报如下错误:
PLS-00201: identifier 'DBMS_LOB'
or
PLS-00201: identifier 'UTL_FILE' must be declared
XDB lacks execute permissions onDBMS_LOB and UTL_FILE package. This is a permission granted bydefault.
--导致这个这个问题的原因是因为XDB 在DBMS_LOB和 UTL_FILE包上没有执行权限。
解决方法有两种:
(1)方法一
SQL> CONN / AS SYSDBA (Connect asSYSDBA)
SQL> DESC DBMS_LOB
SQL> DESC UTL_FILE
SQL> GRANT EXECUTE ON DBMS_LOB TO XDB;
SQL> GRANT EXECUTE ON UTL_FILE TO XDB;
SQL> @?/rdbms/admin/utlrp.sql -- Run this afew times
SQL> SELECT * FROM DBA_ERRORS;
SQL> SELECT COMP_ID, COMP_NAME, STATUS FROM DBA_REGISTRY;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS FROM DBA_OBJECTS WHEREOWNER = 'XDB' AND STATUS != 'VALID';
(2)方法二:
a) Grant following privileges to XDBuser
SQL> GRANTEXECUTE ON DBMS_LOB TO XDB;
SQL> GRANT EXECUTE ON UTL_FILE TO XDB;
b) Reload the XDB Component (usingxdbrelod.sql).
- Refer Master Note for Oracle XMLDatabase (XDB) Installation (Doc ID 1292089.1)
-------------------------------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Skype: tianlesoftware
Email: tianlesoftware@gmail.com
Blog: http://www.tianlesoftware.com
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请----
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群:83829929 DBA5群: 142216823
DBA6 群:158654907 DBA7 群:172855474 DBA总群:104207940
posted on 2012-03-06 00:34 spring3mvc学习笔记 阅读(855) 评论(0) 编辑 收藏 举报