Posted on by

Editioning – Deploy code to an inactive edition

Last time I discussed controlling which edition a session connects with. This is crucial when actually deploying code. If you  deploy code to an edition with active sessions, you can disrupt those sessions (which negates the point of editioning), or you may find them blocking your code deploy with locks. To avoid this, may sure that no sessions are using the edition you intend to deploy to, and then make very sure you are in the intended edition before starting to deploy code.
I’m assuming that you will be deploying with scripts run by sqlplus, and that the live sessions are using the default edition. These are the steps you should take before deploying any code:
  1. Determine the correct edition. If you are using the two edition method, simply find the default edition and pick the other one, hereafter known as the “deploy edition”.
  2. Connect as the deploy edition using one of the methods outlined in the previous post.
  3. Verify that the current edition is the deploy edition, and that it is not the default edition.
  4. Verify that no other sessions that use the code you are deploying are using the deploy edition.
You will want to script all of this, not have to run the checks manually, but it’s good to understand how to run the checks for occasions when they fail, so you know how to fix the issues.
Determine the deploy edition
# Get the current default edition
DEF_EDITION=`sqlplus -s / as sysdba <<!
set head off
set feed off
set pages 0
select property_value from database_properties where property_name = ‘DEFAULT_EDITION’;
!`
# now switch the edition name
[ ${DEF_EDITION} = “BLACK” ] && DEPLOY_EDITION=WHITE || DEPLOY_EDITION=BLACK
Connect with the deploy edition
ORA_EDITION=${DEPLOY_EDITION}
sqlplus / as sysdba
– or –
sqlplus / as sysdba edition=${DEPLOY_EDITION}
Verify that we are ok to deploy to the current edition
SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) “Current Edition” FROM dual;
WHENEVER SQLERROR EXIT
SET SERVEROUT ON SIZE UNLIMITED
DECLARE
e_halt_processing EXCEPTION;
v_edition varchar2(35);
  v_sess_cnt pls_integer;
BEGIN
SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) INTO v_edition FROM dual;
— check that we are in the correct edition
IF v_edition <> ‘${DEPLOY_EDITION}’ THEN
DBMS_OUTPUT.PUT_LINE(‘Deploy halted – edition not set properly!!’);
RAISE e_halt_processing;
END IF;
— check that it’s not the default edition
  SELECT property_value INTO v_edition FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;
IF v_edition = ‘${DEPLOY_EDITION}’ THEN
DBMS_OUTPUT.PUT_LINE(‘Deploy halted – this is the default edition !!’);
RAISE e_halt_processing;
END IF;
— check that no app sessions are using the edition we are deploying to
SELECT COUNT(*) INTO v_sess_cnt FROM sys.gv$session s LEFT OUTER JOIN sys.dba_objects o ON (s.session_edition_id = o.object_id)
 WHERE username IN (‘APP1′,’APP2′,’APP3’)  AND s.status <> ‘KILLED’
 AND o.object_name = ‘${DEPLOY_EDITION}’;
  IF v_sess_cnt > 0 THEN
     DBMS_OUTPUT.PUT_LINE(‘Deploy halted – sessions are using this edition !!’);
     RAISE e_halt_processing;
  END IF;
END;
/
WHENEVER SQLERROR CONTINUE