SVN repository: insert repository here

How to use and usage pointers: insert things here

Notes on CalibDataClass and structure of script: notes here

Creating development copy of calibration database

Whenever doing experiments or development with the calibration database we should use a replica of the database that is not replicated to CERN. They don't like it when we use the database too much. Large inserts or deletes are frowned upon and cause excessive latency with streams replication.

The "exp" command doesn't work due to BINARY_FLOAT columns in our tables. Have to use "expdp" (export data pump). These are the directions I followed:

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

Step by step:

(Connect using "sqlplus SYS as SYSDBA")

1. Create directory pointer to hold schema dump: " create or replace directory muoncal_export as '/atlas/data19/bmeekhof/calibdb' ". Also create the directory for real. The dump will be performed as user "oracle" which has default group "umatlas" so make the directory writable by either the user or group.
2. Run the dump command including each schema:

    "expdp system/passw schemas=ATLAS_MUONCALIB_UM_2013, ATLAS_MUONCALIB_UM_META directory=muoncal_export dumpfile=muoncal2013.dmp logfile=log_muoncal2013.log 

3. Run the import command remapping to new schemas:

    impdp system/SNeutrino99 schemas=ATLAS_MUONCALIB_UM_2013,ATLAS_MUONCALIB_UM_META REMAP_SCHEMA=ATLAS_MUONCALIB_UM_META:ATLAS_MUONCALIB_UM_META_TEST  
      REMAP_SCHEMA=ATLAS_MUONCALIB_UM_2013:ATLAS_MUONCALIB_UM_TEST directory=muoncal_export dumpfile=muoncal2013.dmp logfile=log_muoncal2013.log 

4. In the oracle enterprise manager, select the ATLAS_MUONCALIB_UM_TEST user. Set ATLAS_MUONCALIB_READER as proxy user. Then "create like" the same user with _W. Set ATLAS_MUONCALIB_WRITER as proxy user for this user. Set any password.
5. Do the same thing for the ATLAS_MUONCALIB_UM_META_TEST user.
6. Grant object privileges for ATLAS_MUONCALIB_UM_TEST to have SELECT on all tables in the schema of the same name. Grant SELECT,UPDATE,INSERT,DELETE for the _W user in that schema. Same for _META user in _META schema.
7. Grant SELECT privileges for the ATLAS_MUONCALIB_READER user to the new _META schema AND to the new _TEST schema. The reader user is used without proxy though we did set it up to be possible in our test setup. The current setup with our regular production database is accessed directly via this user and proxy users are not used for reading (this includes the calibration database observer). I wanted to have the program setup be exactly the same in both cases.
8. I setup the read proxy users under the theory that maybe someday the real setup will get "fixed" (if it is wrong) to use a proxy user for reading too. I always had thought this was supposed to be the case but perhaps I misunderstood the intent of the configuration. The step could be skipped.

Set active schema in metadata table

In addition, I also altered the data in the meta-data table to reflect our test schema. This isn't so important for calibdata since it is somewhat "dumb" and uses the database schema defined in the dbinfo.py file instead of reading from the meta data table. The writer account is arrived at by simply appending _W to the schema defined in the dbinfo file. A sample dbinfo file is listed on this page also.

sqlplus SYS as SYSDBA
insert into ATLAS_MUONCALIB_UM_META_TEST.MDT_DATA_SCHEMA values ('ATLAS_MUONCALIB_UM_TEST','ATLAS_MUONCALIB_UM_TEST_W', 1,0,'');
update ATLAS_MUONCALIB_UM_META_TEST.MDT_DATA_SCHEMA set ACTIVE=0 where SCHEMA_NAME='ATLAS_MUONCALIB_UM_2012');

Screenshots of final configurations for users

Calibdata tool configuration file setup for test database

We're still using the same reader and writer users as the regular database but using them to proxy for the test schemas instead of the production schemas. Passwords remain unchanged from using the production schemas. It would be possible to setup also test reader/writer users to proxy for the test schemas. It is necessary to preserve the proxying scheme in our test setup because the calibdata tool (at the moment) has that expectation coded into the database connection method.

#!/usr/bin/python

# db writer, db reader
dbw = "ATLAS_MUONCALIB_WRITER"
metadb_w = dbw + "[ATLAS_MUONCALIB_UM_META_TEST_W]"
dbr = "ATLAS_MUONCALIB_READER"
# logins with meta proxy fail 
metadb_r = dbr

# meta database, should never change for a site
metadb = "ATLAS_MUONCALIB_UM_META_TEST"

# db reader password, db writer password
# writing to calib db will use proxy user constructed after we query the writer for a given schema
dbr_password = ""
dbw_password = ""

# have to choose which schema to write into when we make head_id
#calibdb = "ATLAS_MUONCALIB_UM_2009"
.
calibdb = "ATLAS_MUONCALIB_UM_TEST"

# these are not needed, use proxy user method
#calibdb_r_password = ""
#calibdb_w_password = ""

# we are assuming /etc/tnsnames.ora is all set
# copy from muoncal.grid.umich.edu if needed
tnsname = "MUONCAL"
sitename = "MI"  

-- BenMeekhof - 16 Jul 2012