Transcript Slide 1
Software Development using Production Data
By Karen Ambrose Wellcome Trust Sanger Institute (WTSI, UK) Monday 28 th April 2008 – OLSUG, BOSTON, MA
Disclaimer
The information contained within this presentation is based on systems at the Wellcome Trust Sanger Institute.
Overview
About the Wellcome Trust Sanger Institute (WTSI) What do we do?
Case Study – Sequencing pipeline Development requirements Smoke and Mirrors Logical intervention – Logical Standby Questions?
About the WTSI
One of the leading genomics centres in the world.
Founded in 1993 by the
Wellcome Trust
(major funding provider) and the UK
Medical Research Council
(MRC). Formerly the Sanger Centre.
Named after the double Nobel prize-winning biochemist Dr. Fred Sanger
What do we do?
Human Genetics
e.g. Cancer studies, SNP, WTCCC, Copy Number Variation (CNV)
Model Organism & Pathogen Genetics
Mouse, Zebrafish, Pathogens
What do we do?
Model Organism Sequencing
– e.g. Human, Mouse, Yeast, Worm
Bioinformatics
– Analysis, Annotation, Data Storage, Data Processing etc.
What do we do?
Current Finished Sequence Total: 3,684,395,290 bases (25 th April 2008) Data produced is made freely available to researchers worldwide.
Case Study – Sequencing Pipeline
High throughput sequencing application pipelines to support laboratory practices.
Mixture of new bespoke and legacy systems.
Multiple Oracle databases support a collection of co operating production application systems.
Result of multi-developer effort over a period of 10 years.
Abstract sequencing pipeline map
Finishing Restriction digests Assembly Mapping Dna_reception Subcloning Viral sequencing EST Back end Picking Prepping Sequencing Post sequence processing Exoseq Devmin Corf Checking Internal trace Epigenomics External trace Genetrap Gull/kit ETS Archives Chromoview EPS
Development requirements
Availability of “
up to date
” datasets for development testing.
Developer flexibility and autonomy.
Stable and robust development environment.
Ability to test component parts in isolation.
Easily define, store, recreate and test use cases.
Smoke and Mirrors – 1
st
solution
Using the production database system with an additional development database to support read/write processes.
Smoke and Mirrors - Architecture
PRODUCTION/ PRIMARY DATABASE (100GB)
Archive Redo Logs
PHYSICAL PHYSICAL STANDBY STANDBY DATABASE DATABASE (100GB) DEVELOPMENT DATABASE (20GB)
Manual copies of datasets
ORACLE 9i (9.2.0.5) on Compaq Tru64 UNIX V5.1
Smoke and Mirrors - Setup
Public synonyms Production/ Primary DB
Production schema
Development DB
DS1
Private synonyms Across db links.
SS1
● Setup new
“special_”
user on the production database
i.e. SS1
● Setup new user on the development database
i.e. DS1
Smoke and Mirrors - Setup
Public synonyms Production/ Primary DB
Production schema
Triggers replace FK which reference R/O production tables Database links Development DB T T1
DS1
Private synonyms Across db links.
SS1
Development schemas with read/write Tables ● Isolate read/write (r/w) and read only (r/o) table access for an application system.
● Foreign keys replaced with triggers (where applicable).
Smoke and Mirrors - Setup
Public synonyms Production/ Primary DB
Production schema
S
SS1
T1@ Triggers replace FK which reference R/O production tables Database links Development DB T T1
DS1
Private synonyms Across db links.
Database links Development schemas with read/write Tables ● For r/w access - Setup private synonyms over database links to the new development user.
● For r/o access – Use public synonyms from production schemas or create private synonyms.
Smoke and Mirrors - Setup
Public synonyms Private synonyms Across db links.
Production/ Primary DB
Production
S
schema SS1
T1@ Triggers replace FK which reference R/O production tables Database links Development DB T T1
DS1
Development schemas with read/write Tables Database links
Application Login
● Run the software application using the
“special_”
login.
new user
Smoke and Mirrors - Setup
Run SQL check to ensure there are no leakages using the new
special_
user.
select * from all_tab_privs where grantee in (select granted_role from user_role_privs where username = (SELECT USER FROM DUAL)) UNION select * from all_tab_privs where GRANTEE = (SELECT USER FROM DUAL) UNION select * from all_tab_privs where GRANTEE = 'PUBLIC' and TABLE_SCHEMA not in ('SYS', 'SYSTEM', ‘WMSYS’, 'EXFSYS', 'DMSYS', 'XDB’) and PRIVILEGE != 'SELECT';
Smoke and Mirrors
Advantages Disadvantages Current dataset available for R/O access.
Less disk space required for development database.
Quick setup.
Possibility of leakage into production database.
Possible performance issues across database links.
Less developer autonomy.
Logical Standby – 2
nd
solution
Using the logical standby technology, the development schemas and production schemas are within the same database without possibility of leakage onto the production database.
Logical Standby - Architecture
PRODUCTION/ PRIMARY DATABASE (100GB)
Archive Redo Logs
PHYSICAL STANDBY DATABASE DATABASE (100GB) SQL apply
Archive Redo Logs
LOGICAL STANDBY DATABASE (120GB) ORACLE 10GR2 (10.2.0.2) on SUSE LINUX (x86_64) SLES9
Logical Standby - Setup
Prepare the Production DB to support a logical standby.
Determine support for Datatypes and storage attributes.
Ensure table rows can be uniquely identified.
Logical Standby – Setup
Production DB - Dataguard parameters
db_name=‘DB1’ db_unique_name=‘DB1’ log_archive_config='DG_CONFIG=(DB1,DB3)' log_archive_dest_3='service=DB3 valid_for=(online_logfiles,primary_role) db_unique_name=DB3 optional reopen=15' log_archive_dest_state_3=ENABLE
Logical Standby - Setup
Create physical standby using RMAN
Duplicate target database for standby dorecover;
Stop Redo apply.
Build redo dictionary on production db
execute DBMS_LOGSTDBY.BUILD
Logical Standby – Setup
Physical Standby - Dataguard parameters
*.db_name = ‘DB1’ *.db_unique_name=‘DB3‘ *.fal_client=‘DB3' *.fal_server=‘DB1' *.standby_file_management='AUTO'
Logical Standby - Setup
On the Standby Transition the Physical to a Logical standby Alter database recover to logical standby new_dbname; Shutdown and amend the pfile parameters
.
*.db_name=‘DB3‘ *.standby_archive_dest='/oracle/lnnn/logstby/DB3'
Logical Standby – Setup
●Create a new password file.
●Create new SPFILE.
●Open database resetlogs.
●Commence the SQL apply process.
Alter database start logical standby apply;
Logical Standby – Development Setup
Alter Dataguard level to
STANDBY
.
Create separate tablespaces for new development users and objects.
Assign space quotas for each new user’s development schema.
Create new development user accounts in a standard format. *
Logical Standby - Development Setup
Create tables in development schema for read write (r/w) access.
Use tables maintained by SQL apply process for read only (r/o) access. Foreign keys are replaced with triggers (where applicable). ** Run application with development user login.
Logical Standby – Development Setup
Production/ Primary DB
Production schema
Logical Standby DB Archived redo log transfer
SQL apply Production schema
S
T
T1
DS1 Development schema areas
Private synonyms.
Local objects with R/W access.
Triggers replace FK. Use public synonyms for R/O table access.
Application login
Public synonyms R/O R/W
Development Area - Setup
Create additional development users in a standard format using the
USER_ADMINISTRATION
package
PROCEDURE CREATE_USER Argument Name Type In/Out Default?
----------------------------- --------------------- ----- ------- P_DEBUG BOOLEAN IN DEFAULT P_USERNAME VARCHAR2(30) IN
Development Area - Setup
PROCEDURE DROP_USER Argument Name Type In/Out Default?
------------------------------ ---------------------- ----- ------- P_DEBUG BOOLEAN IN DEFAULT P_USERNAME VARCHAR2(30) IN PROCEDURE VALIDATE Argument Name Type In/Out Default?
------------------------------ ---------------------- ----- ------- P_DEBUG BOOLEAN IN DEFAULT P_USERNAME VARCHAR2(30) IN P_MODE VARCHAR2 IN
Development Area – Setup
Package Usage Examples:
exec USER_ADMINSTRATION.CREATE_USER(TRUE,’DEV_user’); Output as follows: CREATE USER DEV_user IDENTIFIED BY user DEFAULT TABLESPACE DEV_TB_01 TEMPORARY TABLESPACE DEV_TMP_01 QUOTA 1024M ON DEV_TB_01 GRANT RESOURCE, CONNECT, DEV_USER_ADMINISTRATION TO DEV_user CREATE SYNONYM DEV_user.QUICK_FK_TRIGGER_PACKAGE GRANT EXECUTE ON QUICK_FK_TRIGGER_PACKAGE TO DEV_user
Development Area - Setup
NO foreign keys permitted from the development sandbox to SQL apply maintained schemas. 2 solutions: Copy the required table locally and create the FK within the development area. Use a package which replaces FK's with triggers.
Development Area - Setup
Create triggers to replace foreign keys between developer sandboxes and schemas maintained by SQL apply process using the
QUICK_FK_TRIGGER
package.
PROCEDURE QUICK_CREATE_FK_TRIG Argument Name Type In/Out Default?
----------------------------- ---------------------- ----- LOCAL_TABLE VARCHAR2 IN ------- REFERENCE_TABLE VARCHAR2 IN REFERENCE_DBLINK VARCHAR2 IN DEFAULT DEBUG NUMBER IN DEFAULT
Development Area – Setup
Package Usage Examples:
exec QUICK_FK_TRIGGER_PACKAGE. QUICK_CREATE_FK_TRIG('DEV_user.table_name.column_na
me
',‘ref_user.table_name.column_name',[db.domain|NULL],1); e.g.QUICK_FK_TRIGGER_PACKAGE.QUICK_CREATE_FK_TRI
G('DEV_USER.FINISH_BATCH.PROJECTNAME',‘REF_USER.PR
OJECT.PROJECTNAME','testdb.world',1);
Development Area - Setup
CREATE OR REPLACE TRIGGER AG_PROJECTNAME_FINISH_B_BR_IU BEFORE INSERT OR UPDATE ON FINISH_BATCH FOR EACH ROW DECLARE FK_ENTRY FINISH_BATCH.PROJECTNAME%TYPE; BEGIN
--------------------------------------- -- AUTO GENERATED TRIGGER FROM THE -- QUICK_FK_TRIGGER_PACKAGE --
-- NAME: AG_PROJECTNAME_FINISH_B_BR_IU
-- AUTH: kva -- DATE: 05-DEC-07 ----------------------------------------
FK_ENTRY:= :NEW.PROJECTNAME
IF FK_ENTRY IS NOT NULL THEN QUICK_FK_TRIGGER_PACKAGE.
QUICK_FK_CHECKS (FK_ENTRY,’REF_USER',‘PR OJECT',‘PROJECTNAME',‘TESTDB.WORLD'); END IF; END AG_PROJECTNAME_FINISH_B_BR_IU;
Development Area - Setup
PROCEDURE QUICK_FK_CHECKS Argument Name Type In/Out Default?
------------------------------ ---------------------- ----- ------- VAL VARCHAR2 IN REFOWNER VARCHAR2 IN REFTABLE VARCHAR2 IN REFCOL VARCHAR2 IN REFDBLINK VARCHAR2 IN DEFAULT DEBUG NUMBER(38) IN DEFAULT
Development Area - Setup
PROCEDURE QUICK_FK_CHECKS
If no DBLINK specified
SELECT projectname FROM ref_user.project WHERE
projectname
= ‘PROJECT1’;
If DB link specified
SELECT projectname FROM [email protected] WHERE
projectname
= ‘PROJECT1;
EXCEPTION RAISED FOR MISSING ENTRY
RAISE_APPLICATION_ERROR(-20000,'Entry does not exist for PROJECT1 - MISSING ENTRY IN ref_user.project.projectname');
Development Area - Setup
Any attempt to alter the schema being maintained by the SQL apply process, raises the following error:
ORA-16224: DATABASE GUARD IS ENABLED
Logical Standby
Advantages Disadvantages Current dataset for RO access.
All development contained within one database.
More developer autonomy.
No direct interaction with the production DB. Full copy of production data.
More disk space required (1 database plus development area) Security of sensitive production data.
Maintenance & Monitoring
Skipping sensitive schemas by executing the
DBMS_LOGSTDBY.SKIP
package.
exec dbms_logstdby.skip('SCHEMA_DDL',‘schema','%'); exec dbms_logstdby.skip('DML',‘schema','%');
Maintenance & Monitoring
Various views to monitor the processes which maintain the Logical Standby database.
DBA_LOGSTDBY_EVENTS DBA_LOGSTDBY_LOG V$LOGSTDBY_STATS V$LOGSTDBY_PROCESS V$LOGSTDBY_PROGRESS V$LOGSTDBY_STATE Records SQL apply events Details archived logs processed SQL apply statistics Processes involved with SQL apply process Progress made by SQL apply process Current state of SQL apply process
Summary
Both solutions provide access to current datasets for application integration and development testing. The Smoke and mirrors solution is relatively quick to setup.
Logical standby is a more stable solution and provides more development flexibility and scaling.
Conclusion
The Logical Standby has currently proved to be a more stable and popular solution for our immediate development requirements.
Future Plans
Investigation of Rapid Application Testing (RAT), new in Oracle 11g.
Web References
LOGICAL STANDBY CREATION -10gR2 (ORACLE) http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/cre ate_ls.htm#SBYDB00300 LOGICAL STANDBY BY Julian Dyke http://64.233.183.104/search?q=cache:2VixdNn9HN0J:julian.dyke.user
s.btopenworld.com/com/Presentations/LogicalStandby.ppt+logical+stan dby+-+julian+Dyke&hl=en&ct=clnk&cd=2&gl=uk LOGICAL STANDBY MANAGEMENT -10gR2 (ORACLE) http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ls.
htm
Acknowledgements
DBA group
@Sanger Institute (UK)
Production Development group
@Sanger Institute (UK)
Wellcome Trust Sanger Institute (UK)