Monday, September 7, 2009

How to deinstall and install Oracle XML database (XMLDB/XDB)?

If you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository, or registered schema aspects of the XDB feature. Before we begin to install it, let's check the steps of how to remove it manually.

====> Removal of XML DB

1) Shutdown and restart the database

2) Connect as sysdba and run catnoqm.sql script.
connect / as sysdba
drop trigger sys.xdb_installation_trigger;
drop trigger sys.dropped_xdb_instll_trigger;
drop table dropped_xdb_instll_tab;

3) Modify parameter values in init.ora or spfile.
shared_pool_size =150 MB # Or larger value
java_pool_size =150 MB # Or larger value

====> Installation of XML DB
Create XDB tablespace as XMLDB repository storage, make sure it has 150MB free space. Restart the database to make the parameters take effect.

Now we are ready to install a new XDB:

1) Connect as sysdba and run catqm.sql script.
set echo on
spool xdb_install.log
@?/rdbms/admin/catqm.sql xdb_user_pass xdb_tbs temp_tbs

2) If you are using Oracle 9.2, reconnect as SYSDBA and run catxdbj.sql script. Oracle 10g also has this script, but have nothing to do.

3) Change database system parameters in init.ora or spfile.

a) Non-RAC


b) RAC


4) Make sure there is no invalid objects in XDB schema, and check XMLDB status in DBA_REGISTRY.

select count(*) from dba_objects
where owner='XDB' and status='INVALID';

select comp_name, status, version from DBA_REGISTRY
where comp_name= 'Oracle XML Database';

5) Bounce the database to enable the XMLDB protocol.

No comments: