Month: August 2013

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.

# 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

ORA_EDITION=${DEPLOY_EDITION}
sqlplus / as sysdba

– or –

sqlplus / as sysdba edition=${DEPLOY_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

You have your editions, and you can tell which edition each session is currently using, now how do you control that?

First we’ll look at sessions that you control. If you, or code under your control, initiates the edition, you can control the edition with an ALTER SESSION command, like this:

alter session set edition=MYEDITION;

There are shortcuts to this. When using sqlplus, you can set the ORA_EDITION env var or pass the edition on the command line like this:

sqlplus / as sysdba edition=MYEDITION

Note that although the SYS user cannot have editions enabled, it is still true that every login session has a current edition, since it may access objects owned by users that do have editions enabled.

Some other tools may allow you to set an edition for a database connection. This is really all just making the alter sessions command automatic.

You can change the edition in a session as often as you wish, but you cannot be in a place where changing the edition would make the current context invalid. For example, you may not change the edition inside PL/SQL because then you might be executing code from the old edition in the context of the new edition. I know you were thinking of login triggers to change the edition, but you can forget that.

So what about sessions that you don’t control in any way? If they are already running, you cannot change their edition. Think about the havoc that would wreck if it was allowed!

You can however influence the edition that they connect with. I say influence because the session can override your setting with an alter session of their own. The main way you do this is by changing the default edition for the entire database. Think of the default edition as the stable, steady state between code deploys. With no other intervention, all sessions connect as this edition and everything is consistent.

What if you want only certain sessions to connect with the newly deployed code in a non-default edition? The ideal way is to have those sessions make the alter session command, but if you don’t want to do that you have one final option, cluster-ready services.

The -t option to the srvctl modify service command will allow you to specify the edition for that service. Be careful in implementing this, it doesn’t always work reliably, but it’s the best option for controlling the connections of general apps. For one thing, please resist the temptation to use the dbms_services package to change the edition. It has all the options needed and will change the edition in the DBA_SERVICES view, but it will not change it in CRS correctly. Another bug is that if you try to unset the edition with the srvctl command (with -r “”), that will remove it from CRS (as shown by srvctl config), but leave it in the data dictionary, so then you must use the services package as a second step to truly clear it back to use the default edition.

My recommendation is that you not alter the edition of a service to effect this change, but create separate services for each edition and change the service name in the connect details the session will use. Editing the tnsnames.ora on the webserver for example.

So now you have 2 editions that have identical objects, one of them is set as the database default edition. How can you tell which edition a session is using?

If you are controlling the session and can run SQL, just run this:

SELECT sys_context(‘USERENV’, ‘CURRENT_EDITION_NAME’) edition FROM dual;

If you want to see what the current edition of other running sessions, try this:

SELECT o.object_name edition, s.*
FROM gv$session s
LEFT OUTER JOIN dba_objects o ON (s.session_edition_id = o.object_id);

Want to know the what the default edition is set to? Run this:

SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’;

Combining those, if you want to find all the sessions that are using a non-default edition, this will give you that information:

SELECT o.object_name edition, s.*
FROM gv$session s
LEFT OUTER JOIN dba_objects o ON (s.session_edition_id = o.object_id)
WHERE o.object_name NOT IN (SELECT property_value FROM database_properties WHERE property_name = ‘DEFAULT_EDITION’);

So now you know what edition a session is running, next time we’ll talk about how to change the edition and control how other sessions connect.