• Home
• Applications
• Data
• Sequencing

CRIMSON

Download Oracle data dump: Oracle Dump (29 GB)

The dump was successfully loaded into the following Oracle database:
   Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options

PREPARING DATA FILES:

• The data dump needs to be extracted from the tar file. This will create a directory called "oracle.dump". The data files (*.dmp) are compressed with bzip2. You must uncompress them prior to loading them into Oracle, using bunzip2. The uncompressed data files will take about 2 TB of disk space. You will need another 2 TB of disk space for the data within the Oracle database. Once loaded into Orace, the oracle.dump directory can be deleted.

[oracle@o1 ~]$ tar xvf oracle.dump.tar; cd oracle.dump; bunzip2 *.bz2

LOADING DATA FILES INTO ORACLE:

• Set DB_CREATE_FILE_DEST, if necessary, specifying where data will go

SQL> alter system set DB_CREATE_FILE_DEST = '{location for Oracle managed datafiles}' scope=both;

• See the result with:

SQL> show parameters db_create;

• Load the create_cipress_tablespace.sql file to create the CIPRES tablespaces. Since the sizes were not specified, they are 100MB each, autoextend on, no max limit.

SQL> start create_cipress_tablespace;

• Specify the location of the data dump

SQL> create directory imp_dir as '{directory with data dump}';

• Load READONLY dataset

oracle@o1 ~]$ impdp DIRECTORY=imp_dir DUMPFILE=export_srb3_READONLY_CIPRES_12-01-2009_22935.dmp LOGFILE=import-readonly.log

• Load CIPRES dataset

oracle@o1 ~]$ impdp DIRECTORY=imp_dir DUMPFILE=export_srb3_cipres_11-18-2009_15511_1_01.dmp, export_srb3_cipres_11-18-2009_15511_2_01.dmp, export_srb3_cipres_11-18-2009_15511_3_01.dmp, export_srb3_cipres_11-18-2009_15511_4_01.dmp, export_srb3_cipres_11-18-2009_15511_5_01.dmp, export_srb3_cipres_11-18-2009_15511_6_01.dmp, export_srb3_cipres_11-18-2009_15511_7_01.dmp, export_srb3_cipres_11-18-2009_15511_8_01.dmp, export_srb3_cipres_11-18-2009_15511_9_01.dmp, export_srb3_cipres_11-18-2009_15511_10_01.dmp LOGFILE=import-cipres.log

Web Access

It is not necessary to use a web proxy to access the Oracle database. However if you wish to use a web proxy, one has been included below ("DatabaseProxy.php"). To open an Oracle database connection using a web proxy, include the proxy's URL as the database server (eg 'http://{your website}/DatabaseProxy.php'), with a blank username and password. From the Crimson command line, this can be done using the following commands:
      >>> setDBType("oracle")
      >>> openDatabase("", "", "http://{your website}/DatabaseProxy.php")
The following web proxy only allows for read-only access to the CIPRES database.

Web Proxy For Oracle Database (DatabaseProxy.php)

<?php

/*
 * Created on March 28, 2006 Henry Jaime
 *
 * Database proxy to return a list of specific values
 * NOTE: must modify results to return specific columns
 */

// THESE MUST BE DEFINIED
$OracleSID = '';
$OracleReadOnlyUserID = '';
$OracleReadOnlyPasswd = '';

define('DB_SID', $OracleSID);
$DB_USER = trim($_POST['user']);
$DB_PASS = $_POST['passwd'];

// if 'user' is empty, then use defaults above
if (empty($DB_USER)) {
  $DB_USER = $OralceReadOnlyUserID;
  $DB_PASS = $OracleReadOnlyPasswd;
}

$iDBConn = OCILogon($DB_USER, $DB_PASS, DB_SID) or die ("Could not connect.");

executeSql("alter session set current_schema=cipres");

//the sql statement must be the first statement to avoid compound statement sql injection
define('DB_SQL', $_POST['sql']);
list($sql, $other) = split(";", DB_SQL);

//only execute SELECT statements; optionally you can limit to specific tables for insert/delete
if (strpos(strtoupper($sql), 'SELECT') !== false) {
  //filter out malicious code here...
  //allow alpanumeric, space, - = * .
  $sql = preg_replace("/[^= .*a-zA-Z0-9'(,)_-]+/", "", $sql);
  
  executeSql($sql);
}

return;

function executeSql($sql) {
  global $iDBConn, $iStatement;
  
  $iStatement = @OCIParse($iDBConn, $sql);
  @OCIExecute($iStatement, OCI_DEFAULT);
  
  $arrError = OCIError($iStatement);
  
  if ($arrError['code']) {
	print $arrError['message'];
	exit;
  }
  
  $nrows = ocifetchstatement($iStatement, $results,0,-1,OCI_FETCHSTATEMENT_BY_ROW);
  // this will return one row of values per line with either
  // column delimited by ';'.  It is assumed that the calling
  // function knows the order of the columns and the column IDs.
  foreach (array_values($results) as $data) {
	while (list($key,$value) = each($data)) { echo "$value;"; }
	echo "\n";
  }
  
  /*
	while (OCIFetchInto($iStatement, &$results, OCI_ASSOC+OCI_RETURN_NULLS))
	{
      // returns ';' delimited string containing all keys and all values
      echo array_reduce(array_keys($results), "returnArray") . "\n";
	  echo array_reduce($results, "returnArray") . "\n";
	}
  */
}

// create a ';' delimited string containing all elements of the array
function returnArray($val0, $val1) { return $val0 . ";" . $val1; }

?>

HTML Test for Web Proxy

The following html code can be used to test the php proxy listed above.

<HTML>
<BODY>
<FORM method=post action="DatabaseProxy.php">

QUERY:
<INPUT ID=sql NAME=sql TYPE=TEXT SIZE=50 VALUE="select sysdate from dual;select again">
<br>
USER:
<INPUT ID=user NAME=user TYPE=TEXT SIZE=25 VALUE=''>
<BR>
PASS:
<INPUT ID=passwd NAME=passwd TYPE=PASSWORD SIZE=25 VALUE=''>
<INPUT TYPE=SUBMIT VALUE=SUBMIT>
</FORM>
</BODY>
</HTML>