Transcript Slide 1
Software Development using
Production Data
By Karen Ambrose
Wellcome Trust Sanger Institute (WTSI, UK)
Monday 28th 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 (25th 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 cooperating production application systems.
Result of multi-developer effort over a period of 10 years.
Abstract sequencing
pipeline map
Mapping
Restriction
digests
Dna_reception
Subcloning
Picking
Prepping
Sequencing
Finishing
Assembly
Devmin
Corf
Post sequence processing
External trace
ETS
Archives
Exoseq
EST
Back end
Epigenomics
Internal trace
Checking
Gull/kit
Viral
sequencing
Genetrap
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 – 1st 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
Manual copies of datasets
PHYSICAL
PHYSICAL
STANDBY
STANDBY
DATABASE
DATABASE
(100GB)
DEVELOPMENT
DATABASE
(20GB)
ORACLE 9i (9.2.0.5) on Compaq Tru64 UNIX V5.1
Smoke and Mirrors - Setup
Production/
Primary DB
Public
synonyms
Private
synonyms
Across db
links.
Production
schema
Development
DB
DS1
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
Production/
Primary DB
Public
synonyms
Private
synonyms
Across db
links.
Production
schema
Triggers replace FK
which reference R/O
production tables
Database links
Development
DB
DS1
T
T1
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
Production/
Primary DB
Public
synonyms
Private
synonyms
Across db
links.
Production
schema
S
Triggers replace FK
which reference R/O
production tables
Database links
Development
DB
DS1
T
T1
SS1
T1@
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
Production/
Primary DB
Production
schema
S
Public
synonyms
Triggers replace FK
which reference R/O
production tables
Database links
Development
DB
DS1
T
T1
SS1
Private
synonyms
Across db
links.
T1@
Database links
Development schemas
with read/write
Tables
Application Login
● Run the software application using the “special_” new user
login.
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
Current dataset
available for R/O
access.
Less disk space
required for
development database.
Quick setup.
Disadvantages
Possibility of leakage
into production
database.
Possible performance
issues across database
links.
Less developer
autonomy.
Logical Standby – 2nd 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
Archive Redo Logs
PRODUCTION/
PRIMARY
DATABASE
(100GB)
PHYSICAL
PHYSICAL
STANDBY
STANDBY
DATABASE
DATABASE
(100GB)
SQL
apply
LOGICAL
STANDBY
DATABASE
(120GB)
Archive Redo Logs
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
Logical
Standby DB
Archived redo
log transfer
Production
schema
Production
schema
S
SQL
apply
T
T1
Development schema areas
Private synonyms.
Local objects with R/W access.
Triggers replace FK.
Use public synonyms for R/O
table access.
DS1
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
-----------------------------P_DEBUG
P_USERNAME
Type
---------------------BOOLEAN
VARCHAR2(30)
In/Out Default?
------ -------IN
DEFAULT
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
------------------------------ ----------------------P_DEBUG
BOOLEAN
P_USERNAME
VARCHAR2(30)
P_MODE
VARCHAR2
In/Out Default?
------ -------IN
DEFAULT
IN
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
-----------------------------LOCAL_TABLE
REFERENCE_TABLE
REFERENCE_DBLINK
DEBUG
Type
----------------------VARCHAR2
VARCHAR2
VARCHAR2
NUMBER
In/Out Default?
------ -------IN
IN
IN
DEFAULT
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
------------------------------ ----------------------VAL
VARCHAR2
REFOWNER
VARCHAR2
REFTABLE
VARCHAR2
REFCOL
VARCHAR2
REFDBLINK
VARCHAR2
DEBUG
NUMBER(38)
In/Out Default?
------ -------IN
IN
IN
IN
IN
DEFAULT
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
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.
Disadvantages
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
Records SQL apply events
DBA_LOGSTDBY_LOG
Details archived logs processed
V$LOGSTDBY_STATS
SQL apply statistics
V$LOGSTDBY_PROCESS
Processes involved with SQL apply
process
V$LOGSTDBY_PROGRESS
Progress made by SQL apply
process
V$LOGSTDBY_STATE
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)
Questions?
Contact [email protected], if
you require more information.