Installing Oracle - ALICE DCS Pages

DCS Databases
(http://alicedcs.web.cern.ch/AliceDCS/)
Svetozár Kapusta
(CERN/Comenius University Bratislava)
Outline
Oracle Datatypes
z Setting up an Oracle client
z Getting PVSS to archive into Oracle
z Results of the Oracle server tests
z DB Access
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Oracle Built-in Data types
Character datatypes
z Numerical datatypes
z Time and date datatypes
z Binary datatypes
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Character datatypes
CHAR
z NCHAR
z VARCHAR
z VARCHAR2
z NVARCHAR2
z CLOB
z NCLOB
z LONG
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Numerical datatypes
BINARY_FLOAT
z BINARY_DOUBLE
z NUMBER
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Time and date datatypes:
DATE
z INTERVAL DAY TO SECOND
z INTERVAL YEAR TO MONTH
z TIMESTAMP
z TIMESTAMP WITH TIME ZONE
z TIMESTAMP WITH LOCAL TIME
ZONE
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Binary datatypes
BLOB
z BFILE
z RAW
z LONG RAW
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Setting up an Oracle client
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Setting up an Oracle client
z
Download Oracle Instant Client packages
for your platform from
http://www.oracle.com/technology/softwar
e/tech/oci/instantclient/index.html
– Instant Client Package - Basic: All files
required to run OCI, OCCI, and JDBC-OCI
applications
– Instant Client Package - SQL*Plus:
Additional libraries and executable for
running SQL*Plus with Instant Client
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring the client (1/2)
z
z
Unzip into a single directory such as
"instantclient“
Set the library loading path in your environment
to the directory ("instantclient").
– LD_LIBRARY_PATH on UNIX platforms
– PATH on Windows
z
Set environment variable SQLPATH=
"instantclient"
z Set environment variable TNS_ADMIN=
"instantclient"
z Copy or create tnsnames.ora in "instantclient"
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring the client (2/2)
z
Edit tnsnames.ora as in example:
z
DCS006 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.39.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DCSDEV.ALIDCS.CERN.CH)
(INSTANCE_NAME = DCSDEV)
(GLOBAL_NAME = DCSDEV.ALIDCS.CERN.CH)
)
)
z
z
z
z
z
z
z
z
z
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Getting PVSS to archive into
Oracle
Configuring the Oracle DB server for
PVSS
z Configuring PVSS-Oracle archiving
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring the Oracle DB server
for PVSS (1/5)
New environment variable:
ORACLE_HOME=your_oracle_home_path
z Copy the file (PVSSOraExt.dll for win,
PVSSOraExt.so for linux) contained in the
PVSS patch to your_orahome\BIN
z Create directories archive_path\
z
–
–
–
–
alert
backup
event
history
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring the Oracle DB server
for PVSS (2/5)
z
Edit tnsnames.ora in the
orahome\NETWORK\ADMIN directory
z
z
z
z
z
z
z
z
z
z
your_connect_identifier =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = service_name)
)
)
z
z
z
z
z
z
z
z
z
z
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
z
Configuring the Oracle DB server
for PVSS (3/5)
Edit listener.ora in the orahome\NETWORK\ADMIN directory
z
z
z
z
z
z
z
z
z
z
z
z
z
z
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = orahome)
(PROGRAM = extproc)
(ENV="EXTPROC_DLLS=ANY")
)
(SID_DESC =
(GLOBAL_NAME = global_name)
(ORACLE_HOME = orahome)
(SID_NAME = sid_name)
)
)
z
LISTENER =
z
(DESCRIPTION_LIST =
z
(DESCRIPTION =
z
(ADDRESS_LIST =
z
(ADDRESS = (PROTOCOL
z
)
z
(ADDRESS_LIST =
z
(ADDRESS = (PROTOCOL
z
)
z
)
13.06.2005
ALICE
DCS Workshop
z
)
= TCP)(HOST = host)(PORT = 1521))
= IPC)(KEY = EXTPROC))
Svetozár Kapusta
Configuring the Oracle DB server
for PVSS (4/5)
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
Patch PVSS up to patch 45
Unzip rdbsetup from PVSS patch
Edit rdb_arcsite.sql:
begin
-- lokal92@liwrk024
-- 1.
ArchiveControl.CreateSite('dcsdev', 'dcs006', '192.168.39.6', 'd:\oracle10g\pvss\backup\');
-- 2.
i := ArchiveControl.SetConfig('def_dbfile_path', 'C', 'd:\oracle10g\pvss\history\');
dbms_output.put_line('Setting Default Database File Path Returned: '||i);
-- create standard groups
i := ArchiveControl.CreateNewGroup('ALERT','ALERT', 'd:\oracle10g\pvss\alert\');
dbms_output.put_line('Anlage dynamisch ALERT: '||i);
i := ArchiveControl.CreateNewGroup('EVENT', 'EVENT', 'd:\oracle10g\pvss\event\');
dbms_output.put_line('Anlage dynamisch EVENT: '||i);
ArchiveControl.Init_Jobs;
end;
/
commit;
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring the Oracle DB server
for PVSS (5/5)
z
Run setup.bat
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring PVSS-Oracle
archiving (1/3)
z
Edit the config file (restart all managers):
z
useRDBArchive = 1
z
z
z
z
[ValueArchiveRDB]
dbuser = "pvssa"
dbtype = "oracle"
db = "dcs006"
z
z
z
z
[ui]
queryRDBdirect = 1
CtrlDLL = "CtrlRDBArchive.dll"
CtrlDLL = "CtrlRDBCompr.dll"
z
z
z
z
[ctrl]
queryRDBdirect = 1
CtrlDLL = "CtrlRDBArchive.dll"
CtrlDLL = "CtrlRDBCompr.dll"
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Configuring PVSS-Oracle
archiving (2/3)
Create new PVSS project
z Stop and remove all archive managers
z Append new manager (rdb archive
manager) with option –num 99, start mode
manual
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
z
z
z
z
z
Configuring PVSS-Oracle
archiving (3/3)
Run the project
In the PVSS systems management window,
database tab - Set the host, user and password
as chosen after running setup.bat
Restart PVSS
Start all managers
Start the RDB archive manager
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Results of Oracle server tests
Inserts
z Configuration Data download
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Results of Oracle server tests
(Inserts)
z
2 column table (number(5),varchar2(128)
no index)
OCCI autocommit
~500/s
z PL/SQL (bind variables) ~10000/s
z PL/SQL (vararrays)
~>50000/s
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Results of Oracle server tests
(Data download)
z
z
150MB of configuration data, 3 clients loading
data in parallel
DCS Private 100Mbit/s network adapters
connected to 1 Gb backbone:
–
–
–
–
z
Bfile
Blob
Blob, stream
Blob, ADO.NET
~10.59 MB/s
~10.40 MB/s
~10.93 MB/s
~10.10 MB/s
CERN 10Mbit/s network:
–
–
–
–
Bfile
Blob
Blob, stream
Blob, ADO.NET
13.06.2005 ALICE DCS Workshop
~0.81 MB/s
~0.78 MB/s
~0.81 MB/s
~0.77 MB/s
Svetozár Kapusta
DB Access
z
z
SQL *Plus
Web access
– Enterprise Manager Console
– iSQL *Plus
z
z
Web access with PHP code (PHP-ApacheOCI)
C/C++ access based on OCI, OCCI, ADO
or ADO.NET
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
DB Access (SQL *Plus)
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
DB Access (Enterprise Manager Console)
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
DB Access (iSQL *Plus)
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
DB Access (PHP-Apache-OCI)
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
<?php
if ($c=OCILogon("scott", "tiger", "dcsconf")) {echo "Successfully connected to
Oracle.<br>";
$stmt=OCIParse($c, "select * from scott.dcs");
OCIExecute($stmt, OCI_DEFAULT);
while (ocifetchinto($stmt, $row, OCI_BOTH)) {
echo $row[0]." and ".$row['ID']." is the same<br>";
echo $row[1]." and ".$row['CISLO']." is the same<br>";}
echo ocirowcount($stmt) . " rows<br />";
$committed = OCICommit($c);
if (!$committed) {$error = OCIerror($c);
echo 'Commit failed. Oracle reports: ' . $error['message'];}
OCILogoff($c);}
else {
$err = OCIError();
echo "Oracle Connect Error " . $err[text];
}
?>
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
DB Access (C++ OCCI)
z
z
z
z
z
z
#include "stdafx.h"
#include <occi.h>
using namespace oracle::occi;
using namespace std;
int main()
{
z
z
z
z
z
z
z
z
z
z
Environment* env;
oracle::occi::Connection*
conn;
Statement*
stmt;
ResultSet*
rs;
try
{
env = Environment::createEnvironment(Environment::DEFAULT);
conn = env->createConnection( "SCOTT", "TIGERR","DCS006");
stmt = conn->createStatement();
std::string QueryString = "SELECT PIC from SCOTT.CAT WHERE ID=1";
rs=stmt->executeQuery (QueryString);
z
rs=stmt->getResultSet();
while(rs->next())
stmt->closeResultSet(rs);
–
–
–
–
–
–
–
–
–
–
–
}
catch (SQLException &e)
{
std::cout << "SQL exception :" << e.getMessage() << std::endl;
}
conn->terminateStatement(stmt);
env->terminateConnection(conn);
Environment::terminateEnvironment(env);
getchar();
return 0;
}
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
DB Access (C++ ADO)
z
z
z
z
z
z
#include "stdafx.h"
#include <afxdisp.h>
#import "c:\program files\common files\system\ado\msado15.dll" rename ("EOF","adoEOF") no_namespace
using namespace std;
_RecordsetPtr svetest;
_ConnectionPtr spCON;
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
try{
#define CREATEiNSTANCE(sp,riid) { HRESULT _hr =sp .CreateInstance( __uuidof( riid ) ); \
if (FAILED(_hr)) _com_issue_error(_hr); }
#define RsITEM(rs,x) rs->Fields->Item[_variant_t(x)]->Value
#define UC (char *)
struct InitOle {
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;
Char connstring[]="Driver={Microsoft ODBC for Oracle};";
CREATEiNSTANCE(spCON,Connection);
spCON->ConnectionString = connstring;
spCON->Open("DSN=dcsconf","scott","tiger",0);
CREATEiNSTANCE(svetest,Recordset)
svetest->PutRefActiveConnection( spCON );
sprintf(a, "SELECT * FROM scott.dcs");
svetest->Open(a, vtMissing,adOpenKeyset,adLockOptimistic, -1);
svetest->MoveFirst();
while(svetest->adoEOF == false) {
ID=(unsigned long)RsITEM(svetest,"ID");
svetest->MoveNext();}
svetest->Close();
}
catch( _com_error &e){
_bstr_t bstrSource(e.Source());
_bstr_t bs = _bstr_t(" Error: ") + _bstr_t(e.Error()) + _bstr_t(" Msg: ")
+ _bstr_t(e.ErrorMessage()) + _bstr_t(" Description: ")
+ _bstr_t(e.Description());
z
z
z
z
z
z
z
MessageBox(0,bs,bstrSource, MB_OK);
}
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
z
z
z
z
z
z
z
z
z
z
z
z
z
z
z
DB Access (C++ ADO.NET)
#include "stdafx.h"
#using <mscorlib.dll>
#using <System.dll>
#using <System.Data.dll>
#using <System.Data.OracleClient.dll>
#using <System.Xml.dll>
using namespace System;
using namespace System::Data;
using namespace System::Data::OracleClient;
OracleConnection* connection;
OracleCommand* command;
OracleDataReader* dataReader;
try
{
connection = new OracleConnection("Data Source=dcs006;User
Id=scott;Password=tiger;Integrated Security=no;");
connection->Open();
Console::WriteLine(S"Connected to Oracle {0}",connection->ServerVersion);
command = new OracleCommand(S"SELECT verMCM, dp0, dp1, dp2, dp3, dp4, AP0, AP1, AP2, AP3, AP4,
AP5, GOL0, GOL1, GOL2, GOL3 FROM SCOTT.SPDMCM", connection);
dataReader = command->ExecuteReader();
while(dataReader->Read())
{
Console::WriteLine(S"{0} {1} {2} {3}", dataReader->get_Item("verMCM"), dataReader->get_Item("dp0"),
dataReader->get_Item("AP0"), dataReader->get_Item("GOL0"));
}dataReader->Close();
–
–
–
–
–
–
–
–
–
–
–
connection->Close();
}
catch(Exception* e)
{
Console::WriteLine("Error occured: {0}", e->Message);
if (dataReader && !dataReader->IsClosed)
dataReader->Close();
if (connection->State == ConnectionState::Open)
connection->Close();
}
return 0;
}
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Conclusions
z
z
z
z
z
z
z
Use varchar2 for char data
Use number for numeric data
Use BLOB for binary data
Use binary data only if necessary
Tests have shown ORACLE will not be the
bottleneck
Use SQL *Plus, iSQL *Plus or OEMC to view
the data in the Database
Use C++ with OCCI for handling the data
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Thank you for your attention
z Any questions are welcome !
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Appendixes
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
z
„Connect Identifier:“
Connect Identifier (= Name der Instanz) für die Datenbank, steht
hinter ‚@‘
z
„Sysdba User:“
Name eines Users mit Sysdba-Rechten, anschließend wird auch das Passwort
für diesen User abgefragt (zur Geheimhaltung des Passworts sieht man nicht, was man eintippt)
z
„Please enter Password of user sysdba_user:“ Passwort für den User mit Sysdba-Rechten (zur
Geheimhaltung des Passworts sieht man nicht, was man eintippt)
z
„Do you want to create a new schema user and a new tablespace (yes/no)?“
Abfrage ob ein
neuer „RDB-User“ angelegt werden soll oder ein bestehender alter User als RDB-User verwendet
werden soll – bitte „yes“ oder „no“ angeben
z
„Please enter Name of new or existing User:“ Name des bestehenden oder neu zu erzeugenden RDBUsers
z
„Please enter Password of user schema_user:” Passwort des RDB-Users
z
„New PVSS Application User:” Name des neu zu erzeugenden Application-Users („App-User“)
z
„New Password of user app_user:”
Passwort des App-Users
z
„Do you want to backup datafiles with RMAN or operating system commands (OS)?
z
ATTENTION: ASM only works with RMAN! Please enter RMAN or OS (rman/os): '
Abfrage, ob Backup über Operating System Commands („copy” oder „cp”) oder über
RMAN („backup“) erfolgt.
z
Falls ASM zur Speicherung der Datenbankdateien verwendet wird muss RMAN gewählt werden.
z
Bitte „rman“ oder „os“ angeben.
z
„What operating system do you use (unix/win)?”
Abfrage nach dem Betriebssystem, bitte
“unix” oder “win” eingeben.
z
nur für Unix-Systeme:
z
„Oracle Home Directory (ORACLE_HOME/BIN/) - Please Enter With Terminating Slash:”
Pfad zum Oracle-Bin Verzeichnis
z
Bsp: /home/oracle/product/10.1.0/db_1/bin
z
“Zip backupped archives (yes/no)?”
Backup – sollen sie (mit ZIP) komprimiert werden
oder nicht
z
„start_with_of_seq_arc_archive” Anfangswert für die Archivnummerierung
z
„maxvalue_of_seq_arc_archive” Höchstwert für die Archivnummerierung
z
bei Abfrage „RDB-User neu“ = no:
z
„Existing Tablespace of schema_user:”
Wenn kein neuer User angelegt werden soll, wird ein
bestehender Tablespace für die RDB-Basistabellen und –daten verwendet
z
bei Abfrage „RDB-User neu“ = yes:
z
„Path to Datafile of new Tablespace (Enter with terminating slash or backslash!):” Wenn ein neuer
User angelegt werden soll, wird mit ihm auch ein neuer Tablespace angelegt (Name = „ts_“+Name
des Users): Bitte geben Sie den Speicherort des neuen Tablespace an.
bei DCS
Abfrage
„RDB-User neu“ = yes:
13.06.2005 zALICE
Workshop
Svetozár Kapusta
z
„Path to Datafile of new temporary Tablespace (Enter with terminating slash or backslash!):”
Security, Users, Privileges
Pick a good password for SYS, SYSTEM,
DBSNMP and SYSTEM accounts and/or
z restrict web access from outside
z If you need to use the database not only for
PVSS – grant only necessary privileges to
users
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Creating users
z
z
Connect to your database
Type and run SQL query:
–
–
–
–
–
–
–
–
–
–
–
–
z
CREATE USER "SPDUSER" PROFILE "DEFAULT" IDENTIFIED BY “USERPASS" DEFAULT TABLESPACE
"USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT UNLIMITED TABLESPACE TO " SPDUSER“
GRANT "CONNECT" TO " SPDUSER“
GRANT "RESOURCE" TO " SPDUSER “
GRANT ALTER SESSION TO " SPDUSER “
GRANT CREATE MATERIALIZED VIEW TO " SPDUSER “
GRANT CREATE SYNONYM TO " SPDUSER “
GRANT CREATE TABLE TO " SPDUSER “
GRANT INSERT ANY TABLE TO " SPDUSER “
GRANT SELECT ANY DICTIONARY TO " SPDUSER “
GRANT SELECT ANY TABLE TO " SPDUSER “
GRANT UPDATE ANY TABLE TO " SPDUSER “
Or:
http://localhost:5500/em
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta
Oracle Terminology
Schema
z Table
z Tablespace
z Index
z View
z Materialized view
z Synonym
z
13.06.2005 ALICE DCS Workshop
Svetozár Kapusta