DBA Tips Archive for Oracle

Installing and Configuring XML DB

by Jeff Hunter, Sr. Database Administrator

Contents

1. Oracle9i (Release 1 / 9.0.1)
2. Oracle9i (Release 2 / 9.2.0)

Oracle9i (Release 1 / 9.0.1)

Not applicable. Oracle XML DB was not introduced until Oracle9i Release 2.

Oracle9i (Release 2 / 9.2.0)

Oracle XML DB can be installed with or without Oracle Database Configuration Assistant (DBCA). This section of the article will provide the steps required to successfully install Oracle XML DB on Oracle9i/R2 with and without DBCA.

Installing a New Oracle XML DB with DBCA

Oracle XML DB is installed and configured with default settings as part of the seed database using DBCA. In general, no additional steps are required to install Oracle XML DB, however, if you choose to install a "Customized" database, you can configure the Oracle XML DB tablespace and FTP, HTTP, and WebDAV port numbers. Click here of how to use XML DB Custom Option when creating the database through DBCA.

By default DBCA performs the following tasks related to XML DB:

• Creates an Oracle XML DB tablespace for Oracle XML DB Repository
• Enables all protocol access
• Configures FTP at port 2100
• Configures HTTP/WebDAV at port 8080

The Oracle XML DB tablespace holds the data that is stored in Oracle XML DB Repository. This includes data that is stored in the Repository using:

• SQL, for example using resource_view and path_view
• Protocols such as FTP, HTTP, and WebDAV.

It is possible to store data in tables outside this tablespace and access that data through the Repository by having REFs to that data stored in the tables in this tablespace.

NOTE: The Oracle XML DB tablespace (XDB by default) should not be dropped. If it is dropped it renders all Repository data inaccessible.

POST INSTALLATION: Oracle XML DB uses dynamic protocol registration to setup the FTP and HTTP listener service with the local listener. So, make certain that the listener is up, when accessing any Oracle XML DB protocols. Also remember that XML DB

To allow for unauthenticated access to your Oracle XML DB Repository data through HTTP, you must unlock the ANONYMOUS user account.

Installing a New Oracle XML DB Manually Without DBCA

After the database installation, you must run the following SQL scripts in $ORACLE_HOME/rdbms/admin connecting as "SYS as SYSDBA" to install Oracle XML DB after creating a new tablespace for the Oracle XML DB Repository. Here is the syntax for this:

  catqm.sql <xdb_pass> <XDB_TS_NAME> <TEMP_TS_NAME>

For example:

  % cd $ORACLE_HOME/rdbms/admin

  % sqlplus "/ as sysdba"

  SQL> CREATE TABLESPACE "XDB"
    2  LOGGING DATAFILE '/u10/app/oradata/MYDB/xdb01.dbf' SIZE 50M REUSE
    3  AUTOEXTEND ON   NEXT 50M   MAXSIZE 1500M
    4  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

  SQL> @catqm.sql change_on_install XDB TEMP

Reconnect to SYS again and run the following:

  SQL> @catxdbj.sql

After successfully running catqm.sql and catxdbj.sql, you will need to perform the following tasks:

1. Make sure that the database is started with Oracle9i Release 2 (9.2.0) compatibility or higher.

2. Add the following dispatcher entry to the init.ora file:

       dispatchers="(PROTOCOL=TCP) (SERVICE=<sid>XDB)"

3. Restart database and listener to enable Oracle XML DB protocol access.

4. To allow for unauthenticated access to your Oracle XML DB Repository data through HTTP, you must also unlock the ANONYMOUS user account.

Reinstalling Oracle XML DB

To reinstall Oracle XML DB, run following SQL commands connecting to SYS to drop Oracle XML DB user and tablespace:

  DROP USER xdb CASCADE;
  ALTER TABLESPACE <XDB_TS_NAME> OFFLINE;
  DROP TABLESPACE <XDB_TS_NAME> INCLUDING CONTENTS AND DATAFILES;

NOTE: All user data stored in Oracle XML DB Repository is also last when you drop XDB user!

Now continue the re-installation by following the instructions in Installing a New Oracle XML DB Manually Without DBCA in this article.