you are a new oracle dba and you encountered some errors with your XDB schema.This oracle metalink document explains about the steps to follow before deinstalling and installing XDB.
| Master Note for Oracle XML Database (XDB) Install / Deinstall [ID 1292089.1] | ||
|
|
||
| Modified 26-AUG-2011 Type ANNOUNCEMENT Status PUBLISHED | ||
In this Document
What is being announced?
What do you need to do?
Best Practices
Reloading XDB
Oracle 9i – XDB Reload
Oracle 10.1 and above – XDB Reload
Deinstalling and Reinstalling XDB
9.2 – XDB Removal and Reinstall
10g – XDB Removal and Reinstall
11g – XDB Removal and Reinstall
11.1 – XDB Removal and Reinstall
11.2 – XDB Removal and Reinstall
Verify XDB Installation
Known Issues
Who to contact for more information?
Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.3 to 11.2.0.2 – Release: 9.2 to 11.2
Information in this document applies to any platform.
What is being announced?
This master note provides information for DBA’s on removing and installing XML Database (XDB). This note covers releases 9.2.0.3 through 11.2.
What do you need to do?
Best Practices
- Please note that 9.2.0.3 is a mandatory minimum patch level for XDB. Customers using XDB must be on Oracle database version 9.2.0.3 or higher.
- If the XML DB features are currently being utilized and you have experienced an invalid XML DB repository or installation, point-in-time recovery to a point prior to when the problem occurred is recommended. If XDB is removed and reinstalled, and the feature is being used, data loss can occur. Removing XDB by running catnoqm.sql does the following:
- Deletes all information stored in the oracle XML DB repository and XDB database schema.
- Permanently invalidates any xmltype tables or columns that are associated with a registered XML schema. If the XDB user is dropped there is no way to recover the data in these xmltype tables or columns.
- Please confirm whether XDB is being used in your environment prior to attempting removal and reinstall. To accomplish this, refer to the following documents:
(Doc ID 742156.1) 9iR2: How to Determine if XDB is Being Used in the Database?
(Doc ID 742113.1) 10g: How to Determine if XDB is Being Used in the Database?
(Doc ID 733667.1) 11g: How to Determine if XDB is Being Used in the Database?
Please note, later versions of RDA provides some details on the current status of the XDB component. Please see the following document for more information on RDA:
(Doc ID 314422.1) Remote Diagnostic Agent (RDA) 4 – Getting Started
- The following database components / features also use XDB:
- Oracle Application Express (APEX)
- Oracle Expression Filter
- Oracle interMedia / Multimedia (including DICOM and EXIF metadata)
- Spatial (including Oracle Locator)
- OLAP
- Oracle Applications such as iRecruitment
- Any procedure using UTL_TCP, UTL_HTTP, UTL_SMTP
- XMLTYPE operations
- XBRL (Extensible Business Reporting Language) from 11.2.0.2 onwards
- It is recommended to take a full backup of the database before removing / reinstalling XDB. This is a good precautionary measure for situations where it is needed to go back to the original state of the database. Please see the following document for more information:
(Doc ID 858321.1) How To Backup XML Database (XDB)
- Be sure to follow the exact steps listed whenever reloading the XDB component or reinstalling XDB. This includes a startup/shutdown of the database, as failure to do so can cause the XDB installation to fail with an internal error similar to the following: ORA-7445 [qmr_hdl_copy()+48].
- Always review the compatibility parameter to ensure that it is set to the same version as the database so that all XDB functionality associated with that database version is available.
- Before installing or upgrading XDB, make sure the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable is set correctly. That is, the first directory referenced should be $ORACLE_HOME/lib. This environment variable is used to resolve the location of the shared library “libxdb.so”.
- XDB must have execute permissions on the DBMS_LOB and UTL_FILE packages. XDB automatically has these privileges because they are granted to PUBLIC by default. If these privileges have been revoked from PUBLIC for security reasons, errors will be reported during the installation / upgrade of XDB and many XDB objects will become invalid, making the component itself invalid. Therefore, grant execute privileges on these packages back to PUBLIC before installing / upgrading XDB or 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.
Reloading XDB
The reload procedure recreates all of the PL/SQL packages and types. An XDB reload is always preferred over an XDB removal and reinstall. Since xdbrelod.sql is called in xdbpatch.sql, you can alternatively run xdbpatch.sql to recreate all of the XDB related packages.
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
Oracle 10.1 and above – XDB Reload
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
Deinstalling and Reinstalling XDB
- Use XDB removal and reinstall only if not using this feature or under the direction of Oracle Support after it has been verified which objects will need to be recreated.
- For database releases 10.1.x and above, XDB is mandatory in order to use any of the XMLTYPE functions. This is true even if the XDB repository is not being used and/or there are no registered schemas.
- Prior to 11.1, a valid installation of JAVA Virtual Machine (JVM) and XDK is required for XDB to install successfully.
- Allocate at least 200 MB for the XDB repository tablespace datafile. To determine if the XDB tablespace has the necessary space to run the XDB installation, execute the following PL/SQL procedure:
set serveroutput on
DECLARE
v_exists number;
V_size number;
BEGIN
select count(*) into v_exists
from dba_tablespaces
where tablespace_name = ‘XDB’;
IF v_exists > 0 THEN
select bytes into v_size
from dba_data_files
where tablespace_name = ‘XDB’;
IF v_size > 209715200 then
dbms_output.put_line(‘XDB tablespace exists and is greater than 200 MB.
Please proceed with XDB install.’);
ELSE
dbms_output.put_line(‘XDB tablespace exists and but is smaller than
200 MB. If you wish to install all the XDB
metadata into the XDB tablespace, then please add
more space so that its greater than 200 MB before
installing XDB.’);
END IF;
ELSE
dbms_output.put_line(‘XDB tablespace does not exist. Please either
create XDB tablespace of at least 200 MB or
specify another tablespace when installing XDB.’);
END IF;
END;
/
- Ensure that the SHARED_POOL_SIZE and JAVA_POOL_SIZE is set to at least 150 MB.
- If you have any doubts/concerns with reinstalling XDB or you need further assistance, please contact Oracle Support and log a Service Request.
9.2 – XDB Removal and Reinstall
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;
XDB Installation
The catqm.sql script requires the following parameters be passed to it when run:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior 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 the database 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/catxdbj.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
10g – XDB Removal and Reinstall
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;
XDB Installation
The catqm.sql script requires the following parameters be passed to it when run:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior 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 the database 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
11g – XDB Removal and Reinstall
- When a binary XMLType is created, the data is stored in a proprietary format on disk that represents a post parse persistence model. This requires the need to store information about how to transverse the XML data without having to parse it again. The data that is stored in order to accomplish this is maintained in dictionary type tables in the XDB user schema, not in the user schema that created the table. What this means is that the removal script drops the XDB user and in turn loses this information from both Binary and Object-Relational xmltype tables/columns. So if directed to remove and reinstall XDB with the catnoqm.sql and catqm.sql scripts, run the following code block to verify that no Binary and/or Object Relational 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
- What if the database is using the DICOM and/or Oracle Application Express (APEX) features?
If the above code block has objects owned by ORDDATA and/or APEX_030200, it means those components are installed in the database. If those components are being used in a production capacity, XDB should not be removed and reinstalled as data that is maintained in the XDB user schema will be lost.
- The default XMLType storage model is used if a storage model is not specified when creating an XMLType table or column. Prior to Oracle Database 11g Release 2, unstructured (CLOB) storage was used by default. The default storage model is now binary XML storage.
Please see the following document for more information:
(Doc ID 1207893.1) Change in default storage model of XMLType to BINARY XML in 11.2.0.2
- Beginning with 11g, JAVA Virtual Machine (JVM) is no longer required for a successful installation of XDB. However, if an attempt is made to run XQUERY statements which use a functional evaluation path, an error will be thrown stating that JVM is not installed. Also note that JVM must be installed for XDK functionality.
- Beginning with 11.2, XDB now supports SecureFiles. To use SecureFiles, compatibility must be set to 11.2. If SecureFiles will be used, the tablespace specified for the XDB repository must be using Automatic Segment Space Management (ASSM).
Since SecureFiles is now supported with 11.2, an additional parameter was added to the catqm.sql script in that release.
11.1 – XDB Removal and Reinstall
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;
XDB Installation
The catqm.sql script requires the following parameters be passed to it when run:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior 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 the database 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
11.2 – XDB Removal and Reinstall
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;
XDB Installation
The catqm.sql script requires the following parameters be passed to it when run:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository 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 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> <SecureFiles = YES/NO>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
Verify XDB Installation
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;
Known Issues
- If for any reason the catqm.sql script fails, the steps can be repeated. However doing so may result in the following error:
ORA-04098: trigger ‘SYS.XDB_INSTALLATION_TRIGGER’ is invalid and failed re-validation.
If this occurs, implement the steps in the following document:
(Doc ID 331378.1) Running catqm.sql Leads to ORA-4098 Trigger ‘SYS.XDB_INSTALLATION_TRIGGER’ is Invalid
- Some times one or more of the following errors can be encountered when installing XDB, configuring APEX, running an Export, or selecting from xdb.xdb$resource:
ORA-31159: XML DB is in an invalid state
ORA-00600: internal error code, arguments: [unable to load XDB library]
ORA-00600: internal error code, arguments: [qmtGetBaseType]
ORA-00600: internal error code, arguments: [qmx: no ref]
ORA-00600: internal error code, arguments: [qmtInit1]
Errors of this sort generally occur when the init routines for the internal XDB functions are run in an invalid environment, causing memory corruption.
This can happen if the database was ever started with the LD_LIBRARY_PATH (LIBPATH for AIX or SHLIB_PATH for HP) pointing to the wrong $ORACLE_HOME/lib directory rather than to the correct location for the instance. The LD_LIBRARY_PATH/LIBPATH/SHLIB_PATH environment variable is used to resolve the location of the shared library “libxdb.so”.
To resolve this issue, please do the following:
1. Stop the listener and shutdown the database
2. Set LD_LIBRARY_PATH (LIBPATH for AIX or SHLIB_PATH for HP) as follows:
csh: setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:$ORACLE_HOME/lib32:<other paths>
ksh: export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:<other paths>
3. If a client connects to an 11g instance using a 10g listener, modify or add the ENVS= “LD_LIBRARY_PATH” to the listener.ora file
so that it points to the 11g instance:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
…
)
(SID_DESC =
(SID_NAME =11gSID)
(ORACLE_HOME =/opt/oracle/product/11.1.0)
(ENVS= “LD_LIBRARY_PATH=/opt/oracle/product/11.1.0/lib”)
)
)
4. If a client connects to a 10g instance using an 11g listener, modify or add the ENVS= “LD_LIBRARY_PATH” to the listener.ora file
so that it points to the 10g instance:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
…
)
(SID_DESC =
(SID_NAME =10gSID)
(ORACLE_HOME =/opt/oracle/product/10.2.0)
(ENVS= “LD_LIBRARY_PATH=/opt/oracle/product/10.2.0/lib”)
)
)
5. On AIX only, to remove any currently unused modules in the kernel and library memory, run /usr/sbin/slibclean as root.
6. Restart the database and the listener.
- XDB is invalid after installation or upgrade and there are invalid XDB objects which fail to compile with the following errors:
PLS-00201: identifier ‘DBMS_LOB’ must be declared
or
PLS-00201: identifier ‘UTL_FILE’ must be declared
XDB does not have execute permissions on the DBMS_LOB and UTL_FILE packages.
Please reference the following documents:
(Doc ID 429551.1) Invalid XDB Objects After XDB Install
(Doc ID 1105245.1) XDB Is INVALID In DBA_REGISTRY After Having Revoked Privileges: What Privileges Are Needed?
- (Doc ID 243555.1) Create DB or Installing XDB Fails ORA-22973
- (Doc ID 1168180.1) Error ORA-46105 When Trying To Create An ACL
- If the error message “ORA-04043: object XDB_DATASTORE_PROC does not exist” is encountered during XDB installation, this indicates that Oracle Text was not installed.
Please reference the following document for details:
(Doc ID 360907.1) Catupgrd.sql Gives ORA-4043 Error On XDB_DATASTORE_PROC
- Prior to 11.2, if XDB is deinstalled and not reinstalled, orphaned XSD objects can exist that will need to be dropped manually. Please review the document below:
(Doc ID 1273520.1) After de-installing XDB many XSD objects are invalid
On release 11.2 onwards, catnoqm.sql will remove these objects.
Specific to 11.2
- (Doc ID 1273944.1) Installation of XDB failed with ORA-20004: Tablespace system is not ASSM, ORA-43853
- (Doc ID 1086092.1) ORA-60019: Creating initial extent of size X installing XDB with SECUREFILES
- (Doc ID 1168180.1) Error ORA-46105 When Trying To Create An ACL
Who to contact for more information?
My Oracle Support Community for XDB
| Related
Products
Keywords
Errors
|
