Oracle XDB 组件 重建 说明

一．    XDB 组件说明

1.1 官网说明：

XDB 全称XML DB，官网的说明如下：

http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb01int.htm

 

Overviewof Oracle XML DB

OracleXML DB Architecture

OracleXML DB Features

OracleXML DB Benefits

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)