How To Manually Install XML DB On Oracle 11g

BACKGROUND & OVERVIEW

Oracle XML DB is a feature of the Oracle Database. It provides a high-performance, native XML storage and retrieval technology. It is also a pre-requisite for Oracle's 11g implementation of fine-grained network security.

Oracle XML DB is installed automatically in the following situations:

  • If Database Configuration Assistant (DBCA) is used to build Oracle Database using the general-purpose template
  • If the $ORACLE_HOME/rdbms/admin/catqm.sql SQL script is used to install XML DB.

If the database already exists, and was not built via DBCA, maybe through the use of a manual CREATE DATABASE script then rebuilding it via the DBCA approach is probably not an option. Unfortunately, due to Oracle Bug 9818995, if the catqm.sql solution is used then not all installation steps are performed by this script. As a result, when the creation of an ACL is attempted using the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL package then this will error with the following message:

ERROR at line 1:
ORA-46105: Unable to load security class http://xmlns.oracle.com/plsql:network
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 252
ORA-06512: at line 1

This document explains the full set of steps that need to be taken in order to perform a complete and working installation.

ASSUMPTIONS & PRE-REQUISITES

This document expects and assumes the following:

  • The instructions are carried out by a qualified DBA, fully conversant with Oracle.
  • A fully working database without XML DB exists.
  • All references to SID should be replaced with correct database name as derived using the database naming standard.

STEP-BY-STEP GUIDE

  1. Confirm XML DB is not already installed.
    • SELECT username FROM DBA_USERS where username = 'XDB'; returns 0 rows.
    • DESCRIBE RESOURCE_VIEW fails.
  2. As SYS, perform the XML DB installation - Note: this will not be a complete build due to Oracle Bug 9818995:
    • @?/rdbms/admin/catqm.sql xdb <xml db tablespace> <xdb temp tablespace> <use secure files yes|no>
  3. As SYS, grant the relevent permissions to the XDB user:
    • GRANT EXECUTE ON utl_file TO xdb;
    • GRANT EXECUTE ON dbms_lob TO xdb;
  4. As SYS, ensure their are no invalid objects:
    • @?/rdbms/admin/utlrp.sql
  5. Assuming an spfile is in use, as SYS configure the XDB dispatcher:
    • ALTER SYSTEM SET DISPATCHERS="(PROTOCOL=TCP)(SERVICE=<SID>XDB)" SCOPE=both;
    • If init.ora is in use, edit the init.ora file instead.
  6. As SYS, install the missing contents caused by Oracle Bug 9818995:
    • SHUTDOWN IMMEDIATE;
    • STARTUP UPGRADE
    • @?/rdbms/admin/xsdbmig.sql
    • SHUTDOWN IMMEDIATE;
    • STARTUP
  7. As SYS, ensure their are no invalid objects:
    • @?/rdbms/admin/utlrp.sql
  8. As SYS, confirm the XML DB is now fully installed by running the following query and returning the following output:
SELECT 
   object_value
FROM 
   xdb.xs$securityclass
WHERE 
   EXTRACTVALUE(object_value, '/securityClass/@targetNamespace')='http://xmlns.oracle.com/plsql'
AND
   EXTRACTVALUE(object_value, '/securityClass/@name')='network'
/
 
OBJECT_VALUE
--------------------------------------------------------------------------------
<securityClass xmlns="http://xmlns.oracle.com/xs" xmlns:dav="DAV:" xmlns:plsql="
© copyright 2001-2014 ABCdba.com | all rights reserved