Create XMLTable with Virtual Column

以出院病歷要來說, 為了管理大量的電子病歷，增加資料存取的速度，可以借助Oracle Partitioning機制，來達到上列的需求。 要決定要做Partitioning Table的欄位，就整份文件來看，以文件列印日期可以做為Table Partitioning Key。 XML node: /ClinicalDocument/effectiveTime 的 value 屬性. 重點是namespace的宣告

XML Type的表格，可以以XML欄位的內容，定義虛擬欄位

CREATE TABLE CDA_DOCS OF XMLType
XMLTYPE STORE AS BINARY XML
VIRTUAL COLUMNS
(DATE_COL AS (XMLCast(XMLQuery('declare default element namespace "urn:hl7-org:v3";  (: :)
declare namespace voc="http://www.hl7.org/v3/voc";  (: :)
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";  (: :)
/ClinicalDocument/effectiveTime/@value' PASSING OBJECT_VALUE RETURNING CONTENT)
AS varchar2(12))));

insert into CDA_DOCS select cda from XML_DOCS;
commit;

SELECT DATE_COL FROM CDA_DOCS;

RAC ASM with EVA Business Copy

Install Oracle Clusterware and RAC
To create an additional disk group using ASMCA:
1. Prepare the disks or devices for use with ASM, as described in "Configuring
Installation Directories and Shared Storage" on page 2-17.
2. Start the Automatic Storage Configuration Assistant (ASMCA) from the Grid
home:
/u01/grid/bin/asmca
The ASM Configuration Assistant starts, and displays the Disk Groups window.
3. Click the Create button at the bottom left-hand side of the window to create a new
disk group.
The Create Disk Group window appears.
4. Provide the following information:
■ In the Disk Group Name field, enter a name for the new disk group, for
example, FRA.
■ Choose a Redundancy level, for example, Normal.
■ Select the disks to include in the new disk group.
If you used ASMLIB to configure the disks for use with ASM, then the
available disks are displayed if you have the Show Eligible option selected,
and they have a Header Status of PROVISIONED.
After you have provided all the information, click OK. A progress window titled
DiskGroup: Creation appears. After a few minutes, a message appears indicating
the disk group was created successfully. Click OK to continue.
5. Repeat Step 3 and 4 to create additional disk groups, or click Exit, then select Yes
to exit the utility.

Using Oracle Universal Installer to Install Oracle RAC
After you have configured the operating system environment, you can use Oracle
Universal Installer to install the Oracle Database software and create an Oracle RAC
database.
To install Oracle Database software on your cluster and create a clustered
database:
1. As the oracle user, use the following commands to start OUI, where
staging_area is the location of the staging area on disk, or the location of the
mounted installation disk:
cd /staging_area
./runInstaller


<Case I> directly recovery from EVA/BC S-Vols to P-Vols
1. shutdown ASM/RAC/CRS on fgodb03m and fgodb04m
2. un-present all BC/S-Vols from fgodb03m and fgodb04m
3. EVA/BC restore mirrorclone from S-Vols to P-Vols
4. after completed restored, then “fractured” all S-Vols
5. startup ASM/RAC/CRS on fgodb03m and fgodb04m

<Case II> directly startup ASM/RAC/CRS from EVA/BC S-Vols

1.          ASM/RAC/CRS online mode, EVA/BC re-sync from P-Vols to S-Vols

2.          fgodb03m# su – oracle

3.          fgodb03m> run_begin_backup.sh    (enable begin backup mode)

4.          “fractured” all S-Vols

5.          fgodb03m> run_end_backup.sh      (end of backup mode)

6.          present all BC/S-Vols to fgodb03m and fgodb04m

7.          #ioscan –f  (on fgodb03m and fgodb04m)

8.          #insf –e  (on fgodb03m and fgodb04m)

Vdisks WWW	fgodb03m diskname	fgodb04m diskname	Old ASM diskname
00dd (asm using)	disk66 (c?t1d3) (new asm)	disk61 (c?t1d3) (new asm)	disk38 (grid:asmoper)
00c9	disk71 (c?t1d4)	disk66 (c?t1d4)	disk33
00cc	disk75 (c?t1d5)	disk71 (c?t1d5)	disk34
00d1	disk79 (c?t1d6)	disk75 (c?t1d6)	disk35
00d4	disk86 (c?t1d7)	disk79 (c?t1d7)	disk36

9.          fgodb03m#cd /dev/rdisk/

10.      fgodb03m #chown grid:asmoper disk66 disk71 disk75 disk79 disk86

11.      fgodb03m #chmod 664 disk66 disk71 disk75 disk79 disk86

12.      fgodb04m#cd /dev/rdisk/

13.      fgodb04m #chown grid:asmoper disk61 disk66 disk71 disk75 disk79

14.      fgodb04m #chmod 664 disk61 disk66 disk71 disk75 disk79

15.      shutdown ASM/RAC/CRS on fgodb03m and fgodb04m

16.      chown bin:sys /dev/rdisk/disk38 (asm) on fgodb03m and fgodb04m

17.      login as root on each nodes (fgodb03m,fgodb04m)

18.      change directory to $GRID_HOME/bin (/opt/oracle/grid/bin)

19.      run “#./crsctl start crs –excl”; “#./crsctl stop crs –f” on each nodes (fgodb03m run 1^st , after done then run on fgodb04m) (./crsctl start crs –excl help ASM to find OCR and Voting disks)

20.      root@fgodb03m:/opt/oracle/grid/bin#./crsctl query css votedisk

21.      login as oracle and connect to ideal database instance

22.      SQL> startup mount

23.      SQL> alter database end backup;

24.      SQL> shutdown immediate

25.      start all crs and database resources

26.      #./crsctl start crs (on each nodes fgodb03m,fgodb04m)

27.      #./crsctl start resource -all

Run GDAL on Oracle Enterprise Linux

How to recompile GDAL reference document

Document reference URL http://http://www.oracle.com/technetwork/database/enterprise-edition/gdal-howto-compile-linux-130782.txt?ssSourceSiteId=otncn

For GDAL
========

Make sure your $ORACLE_HOME environment variable is set.  If it is, SDO_GEORASTER will automatically be part of the GDAL build.


http://trac.osgeo.org/gdal/
Click on BuildHints
Click on DownloadSource
click on http://download.osgeo.org/gdal 
click on daily
stable is stable code, trunk is code under development (you choose which one you want to compile).
                       trunk may contain fixes to SDO_GEORASTER extension that are not in stable.
.gz files are source code for UNIX/Linux, .zip files are source code for Windows
Unzip the source
cd to top level directory of source tree 


mkdir full_path_to_GDAL_INSTALL_directory/GDAL_INSTALL

Call configure. Backslash ('\') is the continuation character for a UNIX command line
./configure --help

./configure \
--prefix=full_path_to_GDAL_INSTALL_directory/GDAL_INSTALL \
--with-ecw=full_path_to_GDAL_INSTALL_directory/ECW_INSTALL

setenv LD_LIBRARY_PATH full_path_to_GDAL_INSTALL_directory/ECW_INSTALL/lib
make
make install

Add full_path_to_GDAL_INSTALL_directory/GDAL_INSTALL/bin to your path environent variable.
To test install, georaster should appear as one of the gdalinfo formats.
For example, run the gdalinfo command as follows:

gdalinfo --formats

  --  ECW (rw): ERMapper Compressed Wavelets
  --  GeoRaster (rw+): Oracle Spatial GeoRaster

Load I0022461-WGS84.GRE.ers into raster_table FIRST time (create a raster_table and column)
gdal_translate -of georaster I0022461-WGS84.BLU.ers georaster:scott/tiger,,raster_table,raster \
>  -co "DESCRIPTION=(image_name varchar2(45), raster MDSYS.SDO_GEORASTER)" \
> -co "INSERT=VALUES('I0022461-WGS84.BLU',SDO_GEOR.INIT())" \
> -co "INTERLEAVE=BAND" -co "SRID=8307"

Load two more raster files into same table
gdal_translate -of georaster I0022461-WGS84.GRE.ers georaster:scott/tiger,,raster_table,raster \
-co "INSERT=VALUES('I0022461-WGS84.GRE',SDO_GEOR.INIT('RDT_1$', 3))" \
-co "INTERLEAVE=BAND" -co "SRID=8307"

gdal_translate -of georaster I0022461-WGS84.RED.ers georaster:scott/tiger,,raster_table,raster \
-co "INSERT=VALUES('I0022461-WGS84.RED',SDO_GEOR.INIT('RDT_1$', 4))" \
-co "INTERLEAVE=BAND"  -co "SRID=8307"

After load files, update Geometry Metadata
insert into USER_SDO_GEOM_METADATA values('RASTER_TABLE', 'RASTER.SPATIALEXTENT',
SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 119.214515610089, 119.794140610089, 0.005),
    SDO_DIM_ELEMENT('Y', 23.1398648460264, 23.8549398460264, 0.005)
), 8307);
  commit;

Create pyramids for all the rasters in the table
DECLARE
  geor_v  sdo_georaster;
  status  varchar2(20);
BEGIN
  FOR r in (SELECT image_name FROM raster_table ORDER BY georid) LOOP
    SELECT raster INTO geor_v
    FROM raster_table
    WHERE image_name = r.image_name
    FOR UPDATE;

    sdo_geor.generatePyramid(geor_v, 'resampling=NN');

    UPDATE image_table
    SET raster = geor_v
    WHERE image_name = r.image_name;

    COMMIT;
  END LOOP;
END;
/


Create Spatial Index
create index raster_table_raster_sdo_extent
on raster_table(raster.spatialextent)
indextype
is mdsys.spatial_index;

Loading XML Documents

電子病歷是一份XML格式的文件。傳統上都是檔案的型式儲存於檔案伺服器上管理，然後在關聯式資料庫表格，用"pointer"指向儲存的檔案位置。在安全與查詢(指針對文件內容)都有諸多限制。
Oracle 資料庫提供原生性的XML欄位格式，提供以XQuery或是SQL view的方式提供查詢XML文件內容。下列的指令, 是如何將XML文件放入Oracle資料庫的步驟

1. Create a XML table. (如何建立XML表格)
建立一個XML資料表格，我的意思是一 XML Table只有一個欄位, XMLType欄位。
This is easy. As a normal table creation but define a column which its data type is XMLType.

  CREATE TABLE "CGMH"."XML_DOCS"
   ( "CDA" "SYS"."XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 XMLTYPE COLUMN "CDA" STORE AS BASICFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE READS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;

2. Load XML documents into XML table(將 XML文件載入 XML Table)
透過"目錄路徑"的物件，指定上載XML文件所在的路徑。在這裏我們首先建立"XMLDIR"的目錄路徑物件；然後再使用XMLType 函式載入文件。

Things to be noticed is the encoding. Or I will lost the characters in XML documents.

Login Oracle Database as system to create Directory(or check if any Directory are available to use)
SQL> create directory XMLDIR as '/u01/opt/XML';

SQL> grant read, write on directory XMLDIR to cgmh;
Login as a Database user to load XML documents

SQL> connect cgmh/cgmh

Connected.

SQL> select * from all_directories;

OWNER                          DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS                            XMLDIR

/u01/opt/XML

SQL> insert into XML_DOCS values

       2 (XMLType(bfilename('XMLDIR', 'CDABig5Sample.xml'), nls_charset_id('ZHT16MSWIN950')));

1 row created.

SQL> insert into XML_DOCS values

       2 (XMLType(bfilename('XMLDIR', 'CDASample.xml'), nls_charset_id('AL32UTF8')));

1 row created.

SQL> commit;

Commit complete.