Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Revisions: Issue #35628 All Revisions ] Back to Issue ]
Summary 0035628: ORA: sometimes update.database after install.source detects db changes
Revision 2017-03-28 10:02 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

The problem is caused by Oracle's virtual columns [1] which are included within the CRC used to check local changes, as their names might not be consistent, they can cause this situation. (See below for more details)

== Detailed analysis ==

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 
'diff values' end 
from db_checksums where old_crc = new_crc and obj_type='col' 
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

type name                        same values
---- --------------------------- -----------
col  AD_MODEL_OBJECT.UPDATEDBY   same values

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40), 
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' 
else 'diff values' end 
from db_checksums where old_crc != new_crc and obj_type='col' 
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')

order by 2;

type name                                  same values
---- ------------------------------------- -----------
col  AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID  same values

-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) !=  CAST(old_value AS VARCHAR2(100));


0

-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
       case when old_value is null then 'null' else 'not null' end as old, 
       case when new_value is null then 'null' else 'not null' end as new
       from db_checksums where old_value is null or new_value is null
       order by 1, 2;

OB_TYPE 	     OB_NAME										      OLD      NEW
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 
-------- --------
col		     AD_MODEL_OBJECT.SYS_STSE58YAH1UUV2MU99DR						      null     not null
col		     AD_MODEL_OBJECT.SYS_STSZ64L#G7R$IW913WO7						      null     not null
col		     AD_ORG.SYS_STSBTFDMLW$XN5GQX0VD5WXNN9						      null     not null
col		     C_TAXCATEGORY.SYS_STST77VG34JNVJ$RBGUB46						      null     not null
col		     C_UOM.SYS_STST77VG34JNVJ$RBGUB46USK1						      null     not null
col		     GL_CATEGORY.SYS_STST77VG34JNVJ$RBGUB46US						      null     not null
col		     M_LOCATOR.SYS_STST77VG34JNVJ$RBGUB46USK1						      null     not null
col		     OBPOS_APP_PAYMENT.SYS_STSV4HY32RGA6QC_6Y						      null     not null
col		     OBUIAPP_PARAMETER.SYS_STSDD52J8SWA3Y97GL						      null     not null

9 rows selected.

-- SYS_% columns
select table_name, column_name,data_default from user_tab_cols where column_name like 'SYS\_%' escape 
'\' order by 1,2

TABLE_NAME															 COLUMN_NAME										  DATA_DEFAULT
-------------------------------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
AD_MODEL_OBJECT 														 SYS_STSE58YAH1UUV2MU99DR0UZYX# 							  SYS_OP_COMBINED_HASH("ISACTIVE","OBJECT_TYPE")

AD_MODEL_OBJECT 														 SYS_STSZ64L#G7R$IW913WO7OO2#VE 							  SYS_OP_COMBINED_HASH("ISACTIVE","ACTION","ISDEFAULT","AD_PROCESS_ID")

AD_ORG																 SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 							  SYS_OP_COMBINED_HASH("AD_ORG_ID","AD_CLIENT_ID","ISACTIVE")

C_BPARTNER															 SYS_NC00097$										  UPPER("NAME")
C_BPARTNER															 SYS_NC00098$										  UPPER("REFERENCENO")
C_BPARTNER															 SYS_NC00099$										  UPPER("VALUE")
C_ORDER 															 SYS_NC00102$										  UPPER("DOCUMENTNO")
C_TAXCATEGORY															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

C_UOM																 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

FIN_PAYMENT															 SYS_NC00043$										  "RET_HIGVOL_ORACLE"."OBEQUALS"("GENERATED_CREDIT","USED_CREDIT")

GL_CATEGORY															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")


TABLE_NAME															 COLUMN_NAME										  DATA_DEFAULT
-------------------------------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
M_INOUT 															 SYS_NC00066$										  UPPER("DOCUMENTNO")
M_LOCATOR															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

M_PRODUCT															 SYS_NC00110$										  UPPER("NAME")
M_PRODUCT															 SYS_NC00111$										  UPPER("UPC")
M_PRODUCT															 SYS_NC00112$										  UPPER("VALUE")
OBPOS_APP_PAYMENT														 SYS_STSV4HY32RGA6QC_6Y4KE76DMR 							  SYS_OP_COMBINED_HASH("VALUE","FIN_FINANCIAL_ACCOUNT_ID","NAME")

OBUIAPP_PARAMETER														 SYS_STSDD52J8SWA3Y97GLJ1AA8GM5 							  SYS_OP_COMBINED_HASH("AD_REFERENCE_ID","AD_REFERENCE_VALUE_ID","OBUIAPP_PROCESS_


18 rows selected.



[1] https://blogs.oracle.com/sql/entry/ora_54033_and_the_hidden [^]
Revision 2017-03-28 10:00 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 
'diff values' end 
from db_checksums where old_crc = new_crc and obj_type='col' 
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

type name                        same values
---- --------------------------- -----------
col  AD_MODEL_OBJECT.UPDATEDBY   same values

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40), 
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' 
else 'diff values' end 
from db_checksums where old_crc != new_crc and obj_type='col' 
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')

order by 2;

type name                                  same values
---- ------------------------------------- -----------
col  AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID  same values

-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) !=  CAST(old_value AS VARCHAR2(100));


0

-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
       case when old_value is null then 'null' else 'not null' end as old, 
       case when new_value is null then 'null' else 'not null' end as new
       from db_checksums where old_value is null or new_value is null
       order by 1, 2;

OB_TYPE 	     OB_NAME										      OLD      NEW
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 
-------- --------
col		     AD_MODEL_OBJECT.SYS_STSE58YAH1UUV2MU99DR						      null     not null
col		     AD_MODEL_OBJECT.SYS_STSZ64L#G7R$IW913WO7						      null     not null
col		     AD_ORG.SYS_STSBTFDMLW$XN5GQX0VD5WXNN9						      null     not null
col		     C_TAXCATEGORY.SYS_STST77VG34JNVJ$RBGUB46						      null     not null
col		     C_UOM.SYS_STST77VG34JNVJ$RBGUB46USK1						      null     not null
col		     GL_CATEGORY.SYS_STST77VG34JNVJ$RBGUB46US						      null     not null
col		     M_LOCATOR.SYS_STST77VG34JNVJ$RBGUB46USK1						      null     not null
col		     OBPOS_APP_PAYMENT.SYS_STSV4HY32RGA6QC_6Y						      null     not null
col		     OBUIAPP_PARAMETER.SYS_STSDD52J8SWA3Y97GL						      null     not null

9 rows selected.

-- SYS_% columns
select table_name, column_name,data_default from user_tab_cols where column_name like 'SYS\_%' escape 
'\' order by 1,2

TABLE_NAME															 COLUMN_NAME										  DATA_DEFAULT
-------------------------------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
AD_MODEL_OBJECT 														 SYS_STSE58YAH1UUV2MU99DR0UZYX# 							  SYS_OP_COMBINED_HASH("ISACTIVE","OBJECT_TYPE")

AD_MODEL_OBJECT 														 SYS_STSZ64L#G7R$IW913WO7OO2#VE 							  SYS_OP_COMBINED_HASH("ISACTIVE","ACTION","ISDEFAULT","AD_PROCESS_ID")

AD_ORG																 SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 							  SYS_OP_COMBINED_HASH("AD_ORG_ID","AD_CLIENT_ID","ISACTIVE")

C_BPARTNER															 SYS_NC00097$										  UPPER("NAME")
C_BPARTNER															 SYS_NC00098$										  UPPER("REFERENCENO")
C_BPARTNER															 SYS_NC00099$										  UPPER("VALUE")
C_ORDER 															 SYS_NC00102$										  UPPER("DOCUMENTNO")
C_TAXCATEGORY															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

C_UOM																 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

FIN_PAYMENT															 SYS_NC00043$										  "RET_HIGVOL_ORACLE"."OBEQUALS"("GENERATED_CREDIT","USED_CREDIT")

GL_CATEGORY															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")


TABLE_NAME															 COLUMN_NAME										  DATA_DEFAULT
-------------------------------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
M_INOUT 															 SYS_NC00066$										  UPPER("DOCUMENTNO")
M_LOCATOR															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

M_PRODUCT															 SYS_NC00110$										  UPPER("NAME")
M_PRODUCT															 SYS_NC00111$										  UPPER("UPC")
M_PRODUCT															 SYS_NC00112$										  UPPER("VALUE")
OBPOS_APP_PAYMENT														 SYS_STSV4HY32RGA6QC_6Y4KE76DMR 							  SYS_OP_COMBINED_HASH("VALUE","FIN_FINANCIAL_ACCOUNT_ID","NAME")

OBUIAPP_PARAMETER														 SYS_STSDD52J8SWA3Y97GLJ1AA8GM5 							  SYS_OP_COMBINED_HASH("AD_REFERENCE_ID","AD_REFERENCE_VALUE_ID","OBUIAPP_PROCESS_


18 rows selected.

Revision 2017-03-28 09:57 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 
'diff values' end 
from db_checksums where old_crc = new_crc and obj_type='col' 
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

type name                        same values
---- --------------------------- -----------
col  AD_MODEL_OBJECT.UPDATEDBY   same values

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40), 
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' 
else 'diff values' end 
from db_checksums where old_crc != new_crc and obj_type='col' 
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')

order by 2;

type name                                  same values
---- ------------------------------------- -----------
col  AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID  same values

-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) !=  CAST(old_value AS VARCHAR2(100));


0

-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
       case when old_value is null then 'null' else 'not null' end as old, 
       case when new_value is null then 'null' else 'not null' end as new
       from db_checksums where old_value is null or new_value is null
       order by 1, 2;
Revision 2017-03-28 09:56 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 
'diff values' end 
from db_checksums where old_crc = new_crc and obj_type='col' 
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

type name                        same values
---- --------------------------- -----------
col  AD_MODEL_OBJECT.UPDATEDBY   same values

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40), 
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' 
else 'diff values' end 
from db_checksums where old_crc != new_crc and obj_type='col' 
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')

order by 2;

type name                                  same values
---- ------------------------------------- -----------
col  AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID  same values
Revision 2017-03-28 09:53 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),old_value,new_value, 
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 
'diff values' end 
from db_checksums where old_crc = new_crc and obj_type='col' 
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40), 
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' 
else 'diff values' end 
from db_checksums where old_crc != new_crc and obj_type='col' 
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')

order by 2;
Revision 2017-03-28 09:52 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
<code>
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),old_value,new_value,
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 'diff values' end
from db_checksums where old_crc = new_crc and obj_type='col'
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40),
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 'diff values' end
from db_checksums where old_crc != new_crc and obj_type='col'
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')
order by 2;
</code>
Revision 2017-03-28 09:51 by alostale
Description Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.



Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker