#############. sample 1
install guide
below step is only for oracle 11g database installation, 10g database did't need xdb install
step 1.Verify XDB Installation
add in f_getlist function
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
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB');
spool off;
step 2.
## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##
###FOR 11G DB
step 2.1
First, find out the current value of these parameters:
connect / as sysdba
show parameter aq_tm_processes
1
show parameter job_queue_processes
1000
Next, change the value to 0:
connect / as sysdba
alter system set aq_tm_processes=0 scope=both;
alter system set job_queue_processes=0 scope=both;
step 2.2
spool xdb_install.log
set echo on;
connect / as sysdba
shutdown immediate;
startup;
## /rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs> <XDB temporary tbs> -- substitute the parameters with appropriate values
@?/rdbms/admin/catqm.sql xdb SYSAUX TEMP YES
@?/rdbms/admin/utlrp.sql
spool off
time: 12minutes
15:08 ~ 15:30
step 2.3
connect / as sysdba
alter system set aq_tm_processes=1 scope=both;
alter system set job_queue_processes=1000 scope=both;
shutdown immediate;
startup;
#######
step 3.after install :
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
-- Check for invalid objects
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB');
--Get current XDB tablespace
set serveroutput on
declare
v_xdbtbs varchar2(30);
begin
select dbms_xdb.getxdb_tablespace into v_xdbtbs from dual;
dbms_output.put_line(a => 'Actual XDB tablespace is:'||v_xdbtbs);
end;
/
Actual XDB tablespace is:SYSAUX
http://www.dba-oracle.com/t_packages_dbms_current_XDB_tablespace.htm
##v1
select owner, object_name, object_type, status
from dba_objects
where
owner in ('XDB');
########2 XDB re-install
XDB install meet hang issue , waiting for Streams AQ: waiting for messages in the queue
1. Deinstall XDB by implementing the steps in the following document:
(Doc ID 1292089.1) Master Note for Oracle XML Database (XDB) Install / Deinstall
spool xdb_removal.log
set echo on;
connect / as sysdba
shutdown immediate;
startup
@?/rdbms/admin/catnoqm.sql
@?/rdbms/admin/utlrp.sql
spool off;
2.2. There will be invalid SYS objects after the removal of XDB. Validate them by running the scripts in the document below:
(Doc ID 1269470.1) XDB Deinstallation script catnoqm.sql leads to Invalid SYS Objects
SQL> connect / as sysdba
-- Make XDB Dummy views
start ?/rdbms/admin/catxdbdv.sql
-- update Data Pump related objects and KU$_ views
start ?/rdbms/admin/dbmsmeta.sql
start ?/rdbms/admin/dbmsmeti.sql
start ?/rdbms/admin/dbmsmetu.sql
start ?/rdbms/admin/dbmsmetb.sql
start ?/rdbms/admin/dbmsmetd.sql
start ?/rdbms/admin/dbmsmet2.sql
start ?/rdbms/admin/catmeta.sql
start ?/rdbms/admin/prvtmeta.plb
start ?/rdbms/admin/prvtmeti.plb
start ?/rdbms/admin/prvtmetu.plb
start ?/rdbms/admin/prvtmetb.plb
start ?/rdbms/admin/prvtmetd.plb
start ?/rdbms/admin/prvtmet2.plb
start ?/rdbms/admin/catmet2.sql
@?/rdbms/admin/utlrp.sql
REM Check to verify that all components are valid
select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;
3. Change the value for init.ora parameters AQ_TM_PROCESSES and JOB_QUEUE_PROCESSES to 0 and restart the database in restricted mode.
First, find out the current value of these parameters:
connect / as sysdba
show parameter aq_tm_processes
1
show parameter job_queue_processes
1000
Next, change the value to 0:
connect / as sysdba
alter system set aq_tm_processes=0 scope=both;
alter system set job_queue_processes=0 scope=both;
shutdown immediate;
startup restrict;
4.
@?/rdbms/admin/catqm.sql xdb SYSAUX TEMP YES
@?/rdbms/admin/utlrp.sql
connect / as sysdba
alter system set aq_tm_processes=x scope=both;
alter system set job_queue_processes=x scope=both;
shutdown immediate;
startup;