Tuesday, 15 January 2008

SQL Deployment

Example of Directory Structure & SQL Scripts

Set up a directory structure, e.g.



Note : The "sql" folder *MUST* contain at least following scripts :

1. declare.sql
2. rollout.sql
3. rollout_all.sql
4. recompile_invalid_objects.sql

Note : The "sql" folder *MUST* contain atleast one sub-folder with schema name:

eg. SCOTT

SQL Scripts

sql/declare.sql

This script is used to declare database connect string and substitute passwords

REM Defining Connection Details
DEFINE connect_string = orcl10g.world
DEFINE SCOTT_password = tiger
DEFINE HR_password = hr

sql/rollout.sql

* 'rollout.sql' runs declare.sql which DEFINE's substitution variables for all passwords (and for the db connect string). It then connects to each database user in turn and runs the rollout scripts for each schema.

* This script is used to install SQL for particular release (Following example is for Release 2)

@../../2007.12.2.0/sql/declare.sql
spool ../../2007.12.2.0/sql/rollout.spl
Prompt Deploying scripts for Release 2
connect SCOTT/&SCOTT_password@&connect_string
sho user
PROMPT Connected as SCOTT on &connect_string
@../../2007.12.2.0/sql/SCOTT/rollout.sql
@../../2007.12.2.0/sql/recompile_invalid_objects.sql
connect HR/&HR_password@&connect_string
sho user
PROMPT Connected as HR on &connect_string
@../../2007.12.2.0/sql/HR/rollout.sql
@../../2007.12.2.0/sql/recompile_invalid_objects.sql
PROMPT Scripts deployed for Release 2


sql/rollout_all.sql

* The @../../ in the paths works fine on both windows and unix. We have used @../../script.sql so that we can successfully rollout all the releases by running single 'rollout_all.sql' script.

* This script is used to install everything *upto* particular release.

Example of rollout_all.sql for release 2

In the following example, it will deploy SQL scripts for release 1 and 2.

@../../2007.12.1.0/sql/rollout.sql
PROMPT Press any key to deploy release 2 scripts or Ctrl+C to abort
PAUSE
@../../2007.12.2.0/sql/rollout.sql


Example of rollout_all.sql for release 3

In the following example, it will deploy SQL scripts for release 1,2 and 3.

@../../2007.12.1.0/sql/rollout.sql
PROMPT Press any key to deploy release 2 scripts or Ctrl+C to abort
PAUSE
@../../2007.12.2.0/sql/rollout.sql
PROMPT Press any key to deploy release 3 scripts or Ctrl+C to abort
PAUSE
@../../2007.12.3.0/sql/rollout.sql


Note : Please remember to change 'declare.sql' before running above script.

sql/recompile_invalid_objects.sql

This script is used to do the following
* To list invalid objects
* Try to recompile all invalid objects
* To list invalid objects which were not compiled successfully
* To list Objects deployed / re-compiled today
* To list compilation errors (if any) after deployment


prompt +--------------------------------------------+
prompt | List of invalid objects before compilation |
prompt +--------------------------------------------+
column object_type format a30
column object_name format a30
column invalid_obj_cnt format 999
column error_text format a30 word_wrap
set serveroutput on
set pagesize 80
select object_type
, count(*) invalid_obj_cnt
from user_objects
where status <> 'VALID'
group by object_type
order by 1
/
prompt +--------------------------------------------+
prompt | Recompiling invalid objects |
prompt +--------------------------------------------+
BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM user_objects
WHERE status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.object_name || '" COMPILE BODY';
ELSE
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||' "' || cur_rec.object_name || '" COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(cur_rec.object_type || ' : ' || cur_rec.object_name ||' is INVALID');
END;
END LOOP;
END;
/
prompt +--------------------------------------------+
prompt | List of invalid objects after compilation |
prompt +--------------------------------------------+
select object_type
, count(*) invalid_obj_cnt
from user_objects
where status <> 'VALID'
group by object_type
order by 1
/
prompt +---------------------------------------------+
prompt | List of objects deployed / recompiled today |
prompt +---------------------------------------------+
select object_type
, object_name
from user_objects
where subobject_name is null
and (created > trunc(sysdate) or last_ddl_time > trunc(sysdate))
order by object_type, object_name
/
prompt +---------------------------------------------+
prompt | List of Errors |
prompt +---------------------------------------------+
select name
, type
, 'Line '||line||' Position '||position||' - '||text error_text
from user_errors
-- where attribute = 'ERROR' /* Uncomment if deploying to Oracle Database 10g */
order by name
/


sql/HR/rollout.sql

* Use @@ so that the scripts are picked up relative to the directory of the script being run


@@view.sql
@@procedure.sql


sql/SCOTT/rollout.sql

* Use @@ so that the scripts are picked up relative to the directory of the script being run

@@grants.sql


Example of Deployment Instructions

Deployment Instructions for Test Environment

1. Edit declare.sql and substitute the correct database connect string and correct passwords

2. At the command prompt:

$ cd ../2007.12.1.0/sql
$ sqlplus SCOTT/ValidPassword@ValidConnectString
sql> @rollout.sql
sql> exit


NB. The connect string on the command line has to work but is otherwise completely ignored

3. Examine the output (spool file): no errors or warnings should occur

$ cat rollout.spl


Deployment Instructions for Live Environment

1. Edit declare.sql and substitute the correct database connect string and correct passwords. We must modify declare.sql of every releases. In the examples given above, we should modify following declare.sql

../2007.12.1.0/sql/declare.sql
../2007.12.2.0/sql/declare.sql
../2007.12.3.0/sql/declare.sql
..etc


2. At the command prompt:

$ cd ../2007.12.3.0/sql -- Change directory to Last release
$ sqlplus SCOTT/ValidPassword@ValidConnectString
sql> @rollout_all.sql -- Script to rollout SQL scripts of all releases
sql> exit


NB. The connect string on the command line has to work but is otherwise completely ignored

3. Examine the output (spool file): no errors or warnings should occur

$ cat ../2007.12.1.0/sql/rollout.spl
$ cat ../2007.12.2.0/sql/rollout.spl
$ cat ../2007.12.3.0/sql/rollout.spl
.. etc


SQL Script Guidelines
* All SQL script *MUST* start with 'prompt .....'
* SQL script for procedures, functions and packages *MUST* end with 'show error'
* Script should be saved under unix format. So, if you are using UltraEdit to create / modify your script, please use 'File' -> 'Conversions' -> 'Dos to UNIX'
* Name of the script should be lower case. The script name should match oracle object name when possible.
* Package body and package definition should be saved separately.
* Script file extension :

Package Specification .pks
Package Body .pkb
All other scripts .sql


To Do / Can be done
* We are creating declare.sql per release. Due to this, live deployment requires changes to all declare.sql. To avoid this, consider amending 'declare.sql' of first release for every subsequent deployment.
* Consider creating 'PATCH' table per schema. Insert a row with patch_id, timestamp and userid if the patch gets deployed successfully (i.e. without errors). This will allow us to track database changes by querying single table.
* Consider using Ant scripts for SQL deployment.

1 comment:

Oracle Fusion Labs said...

I have gone through your post which seems to be more informative and explained about the “SQL Deployment” in the detailed manner.

Thanxs!

Oracle Applications