Skip to Main Content
  • Questions
  • Edition based redefinition- Data handelling

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shaina.

Asked: January 17, 2017 - 12:31 pm UTC

Last updated: February 25, 2017 - 1:34 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,

I am using EBR to upgrade from one application version to another with zero downtime. Definitely, this includes migration DB scripts to be run to migrate from one version to another. The requirement is that the migration scripts have some DMLs for tables like insert and update. As I have read, the changed data will reflect across editions. This will not serve the purpose of me upgrading the application and running both old and new applications together.
Please suggest a work around for this which does not include code changes and does not affect database size.

Also, I want to know that how can we have multiple schema users to access same database with different editions.

Thanks & Regards,
Shaina

and Connor said...

In all cases, whether it be migration data or new data that the old version should not see, or old data that the new version should not see, the premise is the same:

Editioning views are responsible for ensuring that appropriate insulation of *columns* data between versions takes place. But they do not include things like WHERE clauses etc to restrict or reveal data. So in your case, you would implement this by having a second instance of the table(s) in question, and editioning views over each.

Cross-edition triggers can then be used if data must be replicated/transformed/etc from one version to another.

I dont see a lot of value in the statement:

"Please suggest a work around for this which does not include code changes and does not affect database size."

because if you are changing an application, then by definition, you are already *changing* something.

With regard to:

"how can we have multiple schema users to access same database with different editions."

this can be achieved via login trigger to set the edition, or via service defintition.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Need some clarification

Shaina Sahni, January 18, 2017 - 4:20 am UTC

Hi,

Thank you for the swift response.
Can you please explain this -
"So in your case, you would implement this by having a second instance of the table(s) in question, and editioning views over each".

Regards,
Shaina
Connor McDonald
January 19, 2017 - 12:57 am UTC

Editioning views are restricted predominantly to exposing columns etc. See

http://www.oracle.com/technetwork/database/availability/edition-based-redefinition-1-133045.pdf

for details on this. Hence you cant just create such a view with a WHERE clause (which of course you would want in order to restrict what data is seen).

To workaround this you can do

Edition 1:
view V on table1

Edition 2:
view V on table2

and use triggers to keep the two tables in sync (not a trivial undertaking)

on more clarification

Rajeshwaran, Jeyabal, January 18, 2017 - 10:07 am UTC

....
"So in your case, you would implement this by having a second instance of the table(s) in question, and editioning views over each". 
....


I think, Connor was suggesting something like this.
demo@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> grant create session ,
  2       create procedure,
  3       create trigger,
  4       create view,
  5       create any edition,
  6       drop any edition,
  7       create table,
  8       create sequence,
  9       create job
 10  to demo;

Grant succeeded.

rajesh@ORA12C> alter user demo enable editions;

User altered.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C>
demo@ORA12C> show edition

EDITION
------------------------------
ORA$BASE
demo@ORA12C> select * from emp;

     EMPNO     DEPTNO
---------- ----------
         1         10
         2         10

2 rows selected.

Let's say that as part of deployment, we need to add a couple of rows in EMP table which should **NOT** be visible to the application.
So you need a copy of table (emp2) along with an editioning view. ( Preparing the schema for EBR )
demo@ORA12C> create table emp2 as select * from emp;

Table created.

demo@ORA12C> rename emp to emp_tab;

Table renamed.

demo@ORA12C> create or replace
  2  editioning view emp
  3  as
  4  select empno,deptno
  5  from emp_tab ;

View created.


Creating the edition along with Forward edition trigger(so that each changes on EMP from application will be cascaded to EMP2)
demo@ORA12C> create edition E1 as child of ORA$BASE;

Edition created.

demo@ORA12C>
demo@ORA12C> alter session set edition=E1;

Session altered.

demo@ORA12C> create or replace trigger emp_fwdedition
  2  before insert
  3  on emp_tab
  4  for each row
  5  FORWARD CROSSEDITION
  6  begin
  7     insert into emp2(empno,deptno)
  8             values (:new.empno,:new.deptno);
  9  end emp_fwdedition;
 10  /

Trigger created.

demo@ORA12C>

Now the changes in EMP from application side, will be cascaded to EMP2 (due to the presence of Forward edition trigger).
demo@ORA12C>
demo@ORA12C> alter session set edition=ORA$BASE;

Session altered.

demo@ORA12C> show edition

EDITION
------------------------------
ORA$BASE
demo@ORA12C> insert into emp(empno,deptno) values(3,10);

1 row created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select * from emp;

     EMPNO     DEPTNO
---------- ----------
         3         10
         1         10
         2         10

3 rows selected.

demo@ORA12C> alter session set edition=E1;

Session altered.

demo@ORA12C> show edition

EDITION
------------------------------
E1
demo@ORA12C> select * from emp2;

     EMPNO     DEPTNO
---------- ----------
         1         10
         2         10
         3         10

3 rows selected.

demo@ORA12C>

Now as part of Deployment activities, we could connect to the new edition E1 and do record inserts on EMP2 - which will not be exposed/available to EMP object on BASE edition.
demo@ORA12C> show edition

EDITION
------------------------------
E1
demo@ORA12C> insert into emp2(empno,deptno) values(4,20);

1 row created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select * from emp2;

     EMPNO     DEPTNO
---------- ----------
         1         10
         2         10
         3         10
         4         20

4 rows selected.

demo@ORA12C> alter session set edition=ORA$BASE;

Session altered.

demo@ORA12C> select * from emp;

     EMPNO     DEPTNO
---------- ----------
         3         10
         1         10
         2         10

3 rows selected.

demo@ORA12C>

then as part of clean up activity, we could do the below steps.
drop trigger emp_fwdedition;
drop table emp_tab;
drop editioning view emp;
rename emp2 to emp;
drop edition ORA$BASE

Once EMP2 to rename to EMP, application can still access EMP and see those changes introduced as part of deployment/up-gradation process.

Code change required

Shaina Sahni, January 18, 2017 - 12:11 pm UTC

Hi Rajeshwaran,

Thank you for the detailed explanation.
As I see that my application is running on Tomcat which needs to be upgraded. This upgradation involves code change as well as DDLs and DMLs to be executed on Database.
As per your approach-

If I create a new table EMP2, and at last when I have to access it through select query using my java code, it will require code change because my code is currently fetching data from EMP table.

How can I achieve this? The migration can be multiple times from version to version, I cannot change the freezed code as per EBR again and again. This doesn't seem feasible.
Please help on this.

Regards,
Shaina
Connor McDonald
January 19, 2017 - 1:03 am UTC

Edition *views* are how this is done.

Edition 1: view V as select * from EMP

Edition 2: view V <== same name as select * from EMP2

on Editioning views

Rajeshwaran, Jeyabal, January 19, 2017 - 2:30 am UTC

If I create a new table EMP2, and at last when I have to access it through select query using my java code, it will require code change because my code is currently fetching data from EMP table.


No application changes is required to access EMP2, application still access EMP (which is a editioning view) - this EMP2 will be accessed during deployment activity on EDITION E2 not in the ORA$BASE edition.

Forward Edition trigger will take care about all changes from EMP getting cascaded to EMP2.

It is at the end of deployment activity, this EMP2 will be renamed to EMP, see below.


then as part of clean up activity, we could do the below steps.
drop trigger emp_fwdedition;
drop table emp_tab;
drop editioning view emp;
rename emp2 to emp;
drop edition ORA$BASE
Once EMP2 renamed to EMP, application can still access EMP and see those changes introduced as part of deployment/up-gradation process.

EBR from 11g to 12c

Shaina Sahni, January 23, 2017 - 4:36 am UTC

Hi guys,

Thank you for the solution. It is indeed appreciated.
What if my production DB is running on 11g and I want to move it to 12c using EBR? This will definitely require hardware changes. How can this be achieved?

Regards,
Shaina
Connor McDonald
January 23, 2017 - 12:11 pm UTC

EBR probably isnt the solution for a rolling upgrade to 12c.

For that you're probably looking at either Data Guard or transportable tablespace/database.

Lots of good information here

http://www.oracle.com/technetwork/database/upgrade/upgrading-oracle-database-wp-12c-1896123.pdf


Connection Application with Editions

Shaina Sahni, January 31, 2017 - 8:27 am UTC

Hi,

I have a java application say,version1 running on tomcat. It is connected to oracle DB schema. I have another java application,say version2 on another tomcat setup.Using EBR, I have run my scripts on child edition of my DB schema. How do I connect my application version2 to child edition?

Thanks in advance,
Shaina
Chris Saxon
January 31, 2017 - 4:28 pm UTC

You can specify the edition name using JDBC via the oracle.jdbc.editionName property.

For an example, see:

https://timurakhmadeev.wordpress.com/2010/02/08/jdbc-editions/

oracle.jdbc.editionName property not working

Shaina Sahni, February 23, 2017 - 10:20 am UTC

Hi,
I followed the above link and used oracle.jdbc.editionName property to define the edition name in my java code. But I am still getting default edition ora$base in my output. Please help.

Below is the code snippet-

public Connection getSingleConnection() {
Connection dbConnection = null;
final String METHOD_NAME = "getSingleConnection";
try {
if (_ods_single == null) {
String db_url = Constants.getProperty("datasourceurl");
Properties prop = new Properties();
String db_user = Constants.getProperty("userid");
if (db_user != null)
db_user = BTSLUtil.decrypt3DesAesText(db_user);
String db_password = Constants.getProperty("passwd");
if (db_password != null)
db_password = BTSLUtil.decrypt3DesAesText(db_password);
if (db_url != null) {
_ods_single = new OracleDataSource();
_ods_single.setURL(db_url);
_ods_single.setUser(db_user);
_ods_single.setPassword(db_password);

prop.setProperty("MinLimit", "1"); // the cache size is 5 at
// least
prop.setProperty("MaxLimit", "1");
prop.setProperty("InitialLimit", "1"); // create 3
// connections at
// startup
prop.setProperty("ValidateConnection", "TRUE");
prop.put("oracle.jdbc.editionName", "pretups");

_ods_single.setConnectionCacheProperties(prop);
_ods_single.setConnectionCachingEnabled(true);
if ("Y".equalsIgnoreCase(ONS_EN)) {
_ods_single.setFastConnectionFailoverEnabled(true);
_ods_single.setONSConfiguration(ONS_CONFIG);
}

_occm_single = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
if (_occm_single.existsCache(cacheName_single)) {
_occm_single.removeCache(cacheName_single, 0);
}
_occm_single.createCache(cacheName_single, _ods_single, prop);
} else
return null;
}// end of the m_connectionSinglePool

dbConnection = _ods_single.getConnection();
dbConnection.setAutoCommit(false);
if (_log.isDebugEnabled())
_log.error("getSingleConnection 10G", "DB Connections getting connection from connection pool :" + dbConnection + " active size:" + _occm_single.getNumberOfActiveConnections(cacheName_single) + " cache size:" + _occm_single.getNumberOfAvailableConnections(cacheName_single));

ResultSet rs = dbConnection.createStatement().executeQuery(
"select sys_context('userenv', 'current_edition_name') from dual");
rs.next();
System.out.println(rs.getString(1));

}// end of try
Connor McDonald
February 25, 2017 - 1:34 am UTC

Perhaps try an alter session command instead ?


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library