This note is protected by password and is a Read Only.

Oracle XML DB (XDB) Installation

  1. Determine if XDB is Being Used in the Database

  2. XDB Installation
  3. XDB will be installed on SYSAUX tablespace, please verify that there are at lease 200mb of space alocated to the repository tablespace datafile. Ensure that the shared_pool size is at least 15mb.

    Installation
    conn / as sysdba
    spool catqm.log
    @?/rdbms/admin/catqm.sql <XDB_user_password> SYSAUX <temp_ tablespace_name> NO
    spool off

    1nd parameter = XDB user/schema password
    2st parameter = tablespace
    3rd parameter = Temp tablespace name
    4th parameter = Yes / No for SECUREFILE feature (we pick NO)

    Verify the XDB installation, save this script to an *.sql file and execute it.
    set pagesize 0
    set serveroutput on
    --Begin XDB health and usage check
    declare
    --define cursors
    --check for version
    cursor c_ver is select version from v$instance;
    --check for invalids owned by XDB
    cursor c_inval is select * from dba_objects where status='INVALID' and OWNER in ('SYS','XDB');
    -- Check status of other database features
    cursor c_feat is select comp_name,status,version from dba_registry;
    --check for xml type tables
    cursor c_xml_tabs is select owner,storage_type,count(*) "TOTAL" from dba_xml_tables group by owner,storage_type;
    --check for xml type colmns
    cursor c_xml_tab_cols is select owner,storage_type,count(*) "TOTAL" from dba_xml_tab_cols group by owner,storage_type;
    --check for xml type views
    cursor c_xml_vw is select owner,count(*) "TOTAL" from dba_xml_views group by owner;
    --check for xml type Indexes
    cursor c_xml_idx is select index_owner,type,count(*) "TOTAL" from dba_xml_indexes group by index_owner,type;
    --check for API's bbuilt with XML API's
    cursor c_api is select owner,name,type from dba_dependencies where referenced_name in (select object_name from dba_objects where object_name like 'DBMS_XML%' or object_name like 'DBMS_XSL%') and TYPE !='SYNONYM' and owner !='SYS';
    --check for registered Schemas
    cursor c_xml_schemas is select owner,count(*) "TOTAL" from dba_xml_schemas group by owner;
    --check for user defined resources in the repository
    cursor c_res is select distinct (a.username) "USER",count (r.xmldata) "TOTAL" from dba_users a, xdb.xdb$resource r where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) =a.USER_ID group by a.username;
    -- check xdbconfig.xml values
    cursor c_config is select value(x).GETROOTELEMENT() NODENAME, extractValue(value(x),'/*') NODEVALUE from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'//*[text()]'))) x;
    --check for Network ACLs
    cursor c_net_acls is select * from dba_network_acls;
    --define variables for fetching data from cursors
    v_ver c_ver%ROWTYPE;
    v_inval c_inval%ROWTYPE;
    v_feat c_feat%ROWTYPE;
    v_xml_tabs c_xml_tabs%ROWTYPE;
    v_xml_tab_cols c_xml_tab_cols%ROWTYPE;
    v_xml_vw c_xml_vw%rowtype;
    v_xml_idx c_xml_idx%rowtype;
    v_api c_api%rowtype;
    v_c_net_acls c_net_acls%rowtype;
    v_xml_schemas c_xml_schemas%rowtype;
    v_res c_res%ROWTYPE;
    v_config c_config%rowtype;
    -- Static variables
    v_errcode NUMBER := 0;
    v_errmsg varchar2(50) := ' ';
    l_dad_names DBMS_EPG.varchar2_table;
    --stylesheet for xdbconfig.xml reading
    v_style clob :='';
    begin open c_ver;
    fetch c_ver into v_ver;
    --check minimum XDB requirements
    if dbms_registry.version('XDB') in ('9.2.0.1.0','9.2.0.2.0') then
    DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!! UNSUPPORTED VERSION !!!!!!!!!!!!!');
    DBMS_OUTPUT.PUT_LINE('Minimun version is 9.2.0.3.0. actual version is: '||dbms_registry.version('XDB'));
    end if;
    if v_ver.version like '11.%' then DBMS_OUTPUT.PUT_LINE(' Doing '||v_ver.version||' checks');
    -- Print XDB status
    DBMS_OUTPUT.PUT_LINE('############# Status/Version #############');
    DBMS_OUTPUT.PUT_LINE('XDB Status is: '||dbms_registry.status('XDB')||' at version '||dbms_registry.version('XDB'));
    end if;
    if v_ver.version != dbms_registry.version('XDB') then
    DBMS_OUTPUT.PUT_LINE('Database is at version '||v_ver.version||' XDB is at version '||dbms_registry.version('XDB'));
    end if;
    --Check Status if invalid gather invalid objects list and check for usage if valid simply check for usage
    if dbms_registry.status('XDB') != 'VALID' then
    DBMS_OUTPUT.PUT_LINE('############# Invalid Objects #############');
    open c_inval;
    loop
    fetch c_inval into v_inval;
    DBMS_OUTPUT.PUT_LINE('Type: '||v_inval.object_type||' '||v_inval.owner||'.'||v_inval.object_name);
    exit when c_inval%NOTFOUND;
    end loop;
    close c_inval;
    end if;
    -- Check XDBCONFIG.XML paramareters
    DBMS_OUTPUT.PUT_LINE('############# OTHER DATABASE FEATURES #############');
    open c_feat;
    loop
    fetch c_feat into v_feat;
    exit when c_feat%NOTFOUND;
    if c_feat%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_feat.comp_name||' is '||v_feat.status||' at version '||v_feat.version);
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_feat;
    -- Check XDBCONFIG.XML paramareters
    DBMS_OUTPUT.PUT_LINE('############# XDBCONFIG INFORMATION #############');
    open c_config;
    loop
    fetch c_config into v_config;
    exit when c_config%NOTFOUND;
    if c_config%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_config.NODENAME||'= = = '||v_config.NODEVALUE);
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_config;
    -- Check if they have any xmltype tables or columns and if they are schema based, clob or binary
    DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Tables #############');
    open c_xml_tabs;
    loop
    fetch c_xml_tabs into v_xml_tabs;
    exit when c_xml_tabs%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_xml_tabs.owner||' has '||v_xml_tabs.TOTAL||' XMLTYPE TABLES stored as '||v_xml_tabs.storage_type);
    end loop;
    close c_xml_tabs;
    DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Columns #############');
    open c_xml_tab_cols;
    loop
    fetch c_xml_tab_cols into v_xml_tab_cols;
    exit when c_xml_tab_cols%NOTFOUND;
    if c_xml_tab_cols%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_tab_cols.owner||' has '||v_xml_tab_cols.TOTAL||' XMLTYPE Columns stored as ' ||v_xml_tab_cols.storage_type);
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_xml_tab_cols;
    DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Views #############');
    open c_xml_vw;
    loop
    fetch c_xml_vw into v_xml_vw;
    exit when c_xml_vw%NOTFOUND;
    if c_xml_vw%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_vw.owner||' has '||v_xml_vw.TOTAL||' XMLTYPE Views');
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_xml_vw;
    DBMS_OUTPUT.PUT_LINE('############# XMLTYPE INDEXES #############');
    open c_xml_idx;
    loop
    fetch c_xml_idx into v_xml_idx;
    exit when c_xml_idx%NOTFOUND;
    if c_xml_idx%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_idx.index_owner||' has '||v_xml_idx.TOTAL||' XMLTYPE Indexes of type '||v_xml_idx.type);
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_xml_idx;
    DBMS_OUTPUT.PUT_LINE('############# Items built with XML API''s #############');
    open c_api;
    loop
    fetch c_api into v_api;
    exit when c_api%NOTFOUND;
    if c_api%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_api.type||' '||v_api.owner||'.'||v_api.name);
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_api;
    DBMS_OUTPUT.PUT_LINE('############# XML SCHEMAS #############');
    open c_xml_schemas;
    loop
    fetch c_xml_schemas into v_xml_schemas;
    exit when c_xml_schemas%NOTFOUND;
    if c_xml_schemas%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_schemas.owner||' has '||v_xml_schemas.TOTAL||' registered.');
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_xml_schemas;
    -- Check for repository resources
    DBMS_OUTPUT.PUT_LINE('############# Repository Resources #############');
    open c_res;
    loop
    fetch c_res into v_res;
    exit when c_res%NOTFOUND;
    if c_res%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_res.USER||' has '||v_res.TOTAL||' resources.');
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    end loop;
    close c_res;
    -- Check Network ACLS
    DBMS_OUTPUT.PUT_LINE('############# Network ACLs Configured #############');
    open c_net_acls;
    loop
    fetch c_net_acls into v_c_net_acls;
    if c_net_acls%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_c_net_acls.host||' has network acls configured for ports '||v_c_net_acls.lower_port||' through '|| v_c_net_acls.upper_port);
    else DBMS_OUTPUT.PUT_LINE('No Data Found');
    end if;
    exit when c_net_acls%NOTFOUND;
    end loop;
    close c_net_acls;
    --Check DAD configuration to see if DBMS_EPG is being used
    DBMS_OUTPUT.put_line('############# DBMS_EPG DAD USAGE #############');
    DBMS_EPG.GET_DAD_LIST (l_dad_names);
    FOR i IN 1 .. l_dad_names.count LOOP
    DBMS_OUTPUT.put_line(l_dad_names(i));
    END LOOP;
    close c_ver;
    EXCEPTION
    WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('No Data Found');
    WHEN others THEN
    v_errcode := sqlcode;
    v_errmsg := SUBSTR(sqlerrm, 1, 50);
    DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
    end;
    /
    --End XDB health and usage check

Reference:

Oracle notes: 887279.1, 733667.1



None Private Note List:

URL Note Summary Days before delete
AllowedHtml <ul> <li><br> - Line break.</li> <li><o... 30137
BigDataFishig <b>#RealTimeData + #BigData + #Fishing</b>...teach... 30975
ChatSample Lee: Hello there, so can you explain how to chat u... 30137
Command-Line-Shortcuts <h3>Basic Keyboard Shortcuts</h3><b>Up/Down Arrows... 30223
dba <h2 style="display: inline;">Timeline:</h2> 21-Ja... 30166
dst_upgrade <h1>Oracle 11.2.0.2 DST Upgrade</h1><ol><li><b>che... 30201
Feedback ... 30081
hadoop_class_at_ebay eBay's North Campus, Parley room 2161 N. First ... 30260
mysql Get the size of a MYSQL Dababase: [code]SELECT ta... 30136
outlook-meeting-assassin <h1>Outlook Meeting Assassin</h1>A Simple Outlook ... 30695
Programming-Quotes <h3>Programming Quotes</h3> Java is to JavaScri... 30223
scrum <h2 style="margin: 0px 0px 0px 0px; padding: 0px 0... 867
vi <h2>vi cheat sheet</h2><b><u>Starting & ending com... 30142
websiteTricks-transperent [code]<!-- div.background { width:500px; h... 30081
xdb_installation <h1>Oracle XML DB (XDB) Installation</h1><ol> <li... 30201
zzzzzzzzzzz whycghkghfjjfhkctshsaqzmypqgrehghtmfggfhgoygvfjknm... 652

What else can I do with this?