Making the oracle deploy script environment-independent
Already not one correct article has been written about the necessity and advantages of storing the source codes of database schemas in version control systems (such as CVS, SVN, TFS, etc.), as well as maintaining deploy scripts.
I will not repeat myself, but we will analyze one of the specific aspects of this process.
It’s no secret that a normally set development process consists of development itself (Dev), internal testing (QA), acceptance testing by end users (UAT) and, directly, “Production”. Life cycle details may vary in individual cases, but this is not essential for the topic of the article.
Sometimes (and often in the author’s experience) it happens that the environments on which the different stages of this cycle take place may differ for one reason or another. Differences can be anything. From different tablespaces to differences in schema names, DBLinks, and other individual features. How to effectively solve this trouble, we will consider in this article.
Next to the main deploy.sql script (which calls other scripts with the source objects of the database directly), put a folder in which a set of files for each environment with a set of individual parameters - define-s, would be stored.
For each environment, he will be his own.
Further in the source code of your database, all that remains is to use substitution variables, for example:
Thus, your scripts will be indifferent to the environment, and you can more confidently say that having worked on a test environment, they will also work reliably on UAT (for example). As a result, you do not change the source codes yourself for each specific environment, thereby removing the risk of the human factor: typos, forgetfulness, the probability of distraction.
The risks of this, the error will get into the substitution parameter file itself, leaves already after 1-2-3 iterations of environment collection (and it is usually not so difficult to subtract the substitution file).
It remains only to correctly call all these scripts in the right order and with minimal headache for further support. Here, at first glance, a small question comes in: how to name these settings files. We used to call them that way:
dev.sql
qa.sql
uat.sql
prod.sql
But now we have abandoned this practice, and we call it by the TNS name of the environment.
What does it give? We have a .sh script of this kind (I leave only the essence):
Three parameters come to the script input (in this simplified version, when you have only one circuit): login, password and TNS alias. Considering that many prescribe
names.default_domain = world,
then we bring everything to a single format (in our case: we cut off the potentially appearing ".WORLD" ). As a result, our parameter file name remains (by the name TNSalias) which will need to be called.
It remains to write an example of deploy.sql itself for completeness.
By introducing a similar standard into your development process, you will forget once and for all about the difference in your environment, which, believe me, greatly reduces the “headache” at the last moment - at the time of deployment.
PS: I would like to share with the habrosociety some skills and best-practices that I acquired over the years of working with databases. I would like to open a series of short articles based on examples with analysis of various interesting, in my opinion, cases, tasks and pitfalls that I have encountered.
I will not repeat myself, but we will analyze one of the specific aspects of this process.
It’s no secret that a normally set development process consists of development itself (Dev), internal testing (QA), acceptance testing by end users (UAT) and, directly, “Production”. Life cycle details may vary in individual cases, but this is not essential for the topic of the article.
Sometimes (and often in the author’s experience) it happens that the environments on which the different stages of this cycle take place may differ for one reason or another. Differences can be anything. From different tablespaces to differences in schema names, DBLinks, and other individual features. How to effectively solve this trouble, we will consider in this article.
Next to the main deploy.sql script (which calls other scripts with the source objects of the database directly), put a folder in which a set of files for each environment with a set of individual parameters - define-s, would be stored.
DEFINE DATA_TS = DEV_DATA_TS
DEFINE INDEX_TS = DEV_DATA_TS
DEFINE Some_Source_data_dbl = Dev_dbl_source.world
-- ... и т.д.
For each environment, he will be his own.
Further in the source code of your database, all that remains is to use substitution variables, for example:
create table my_table (sample_col VarChar(2 char) ) tablespace &DATA_TS;
....
select * from dual@&Some_Source_data_dbl;
Thus, your scripts will be indifferent to the environment, and you can more confidently say that having worked on a test environment, they will also work reliably on UAT (for example). As a result, you do not change the source codes yourself for each specific environment, thereby removing the risk of the human factor: typos, forgetfulness, the probability of distraction.
The risks of this, the error will get into the substitution parameter file itself, leaves already after 1-2-3 iterations of environment collection (and it is usually not so difficult to subtract the substitution file).
It remains only to correctly call all these scripts in the right order and with minimal headache for further support. Here, at first glance, a small question comes in: how to name these settings files. We used to call them that way:
dev.sql
qa.sql
uat.sql
prod.sql
But now we have abandoned this practice, and we call it by the TNS name of the environment.
What does it give? We have a .sh script of this kind (I leave only the essence):
# ... здесь заголовки отвечающие за ключи, help, реакцию на отсутствие параметров итд
par_file = ${3//.WORLD/}
sqlplus $1/$2@$3 @deploy.sql $par_file
# ... здесь отработка выхода с ошибкой
Three parameters come to the script input (in this simplified version, when you have only one circuit): login, password and TNS alias. Considering that many prescribe
names.default_domain = world,
then we bring everything to a single format (in our case: we cut off the potentially appearing ".WORLD" ). As a result, our parameter file name remains (by the name TNSalias) which will need to be called.
It remains to write an example of deploy.sql itself for completeness.
-- Создаём лог файл
spool _deploy.log
-- запускаем набор подстановок на основании переданного
-- нам par_file в первом парамере вызова:
@./defines/&1
@../ddl/some_table_create.sql
@../data/some_other_deploy_activity.sql
@./validate_invalid_objects.sql
@./run_post_deployment_checks.sql
spool off
exit
By introducing a similar standard into your development process, you will forget once and for all about the difference in your environment, which, believe me, greatly reduces the “headache” at the last moment - at the time of deployment.
PS: I would like to share with the habrosociety some skills and best-practices that I acquired over the years of working with databases. I would like to open a series of short articles based on examples with analysis of various interesting, in my opinion, cases, tasks and pitfalls that I have encountered.