AnsweredAssumed Answered

Schema Migration issue 7.0.0 P02 to 7.0.2

Question asked by AJ Santos on May 24, 2017
Latest reply on Jun 2, 2017 by AJ Santos

Hello experts - 

 

While testing an upgrade procedure our team ran into an issue at the tail end with the following error (UI Error). This was unexpected as we chose to migrate the DB in the installer.

 

Upon further inspection it seems that the installer.sh may have failed the migration with the second error log posted.

 

Any suggestions as to what is causing the error?

 

System Info

RHEL 6.8

Soft appliance w/ RSA supplied local DB

Patch from: 7.0.0.107432 P02

Patch to: 7.0.2.

 

-----------------Web UI Error------------

Schema Migration is required.
   Database version is 7.0.0.93958. Server version is 7.0.2.126845.

 

Found invalid object(s) in the database. Please check the log for error(s).

 

ORA-01451: column to be modified to NULL cannot be modified to NULL
ORA-06512: at line 3

 

Schema Migration completed in 0h 0m 18s with errors.

 

/home/oracle/wildfly/standalone/tmp/vfs/temp/tempa6e920c7ec98c7a8/content-f5b8d8d3449a3381/contents/aveksa.war/WEB-INF/database/updates/7.0/ACM-55209.sql(1):
BEGIN
 – Hack - Disable null checks for now
 execute immediate 'ALTER TABLE WP_JOB_MONITOR MODIFY (PROCI_ID NUMBER(38,0) NULL)';
 execute immediate 'ALTER TABLE WP_JOB_MONITOR MODIFY (PROCI_DB NVARCHAR2(4) NULL)';
END;
java.sql.SQLException: ORA-01451: column to be modified to NULL cannot be modified to NULL
ORA-06512: at line 3

 

   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
   at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
   at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
   at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
   at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
   at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1707)
   at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1670)
   at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:310)
   at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:375)
   at com.aveksa.migration.jdbctool.SQLFileExecutor.execute(SQLFileExecutor.java:221)
   at com.aveksa.migration.jdbctool.SQLFileExecutor.execute(SQLFileExecutor.java:107)
   at com.aveksa.migration.jdbctool.SQLFileExecutor.execute(SQLFileExecutor.java:98)
   at com.aveksa.migration.jdbctool.IncrementalUpdate.update(IncrementalUpdate.java:481)
   at com.aveksa.migration.jdbctool.MigrateSchema.execute(MigrateSchema.java:140)
   at com.aveksa.migration.jdbctool.CheckDatabase.migrateSchema(CheckDatabase.java:1375)
   at com.aveksa.migration.jdbctool.CheckDatabase.migrateSchema(CheckDatabase.java:1349)
   at com.aveksa.migration.jdbctool.CheckDatabase.runMigration(CheckDatabase.java:2045)
   at com.aveksa.migration.jdbctool.CheckDatabase.run(CheckDatabase.java:1991)
   at java.lang.Thread.run(Thread.java:745)

 

------- /home/oracle/database/log/Migrate.log Error--------
SQL/line#: /home/oracle/database/updates/7.0/ACM-58461.sql(15):
Start time [Wed May 24 11:41:45 EDT 2017]
DECLARE
v_date DATE;
v_dayofweek Varchar2(120);
tz varchar2(50) := trim(System_Settings.getStringValue('TimeZone'));

BEGIN
IF (Migration_Utils_Pkg.DataDoesNotExist('T_SYSTEM_SETTINGS', 'PARAMETER = ''PurgeMaxRunTimeHours''')) THEN
INSERT INTO T_SYSTEM_SETTINGS(PARAMETER, VALUE) VALUES ('PurgeMaxRunTimeHours', '4');
END IF;
IF (Migration_Utils_Pkg.DataDoesNotExist('T_SYSTEM_SETTINGS', 'PARAMETER = ''DataPurgingScheduledTask''')) THEN
INSERT INTO T_SYSTEM_SETTINGS(PARAMETER, VALUE) VALUES ('DataPurgingScheduledTask', 'TRUE');
-- default schedule
IF (Migration_Utils_Pkg.DataDoesNotExist('T_SYSTEM_SETTINGS', 'PARAMETER = ''DataPurgingDatabaseUpdateSchedule''')) THEN
-- August 1st 2015 was a Saturday, so get the closest Saturday's 1AM local time:
v_date := trunc(sysdate) + to_number(to_char(to_date('8/1/2015', 'mm/dd/yyyy'), 'D') - to_char(sysdate, 'D')) + 1.0/24.0;

-- Since we're running the migration right now, we need to give at least 24 hours
-- before the schedule is going to kick in. Otherwise, fastforward to the next Saturday.
if v_date - sysdate < 1.0 then
v_date := v_date + 7.0;
end if;

select to_char(to_date('8/1/2015', 'mm/dd/yyyy'), 'D') into v_dayofweek from dual;

if tz is not null then
-- Set the timezone to ACM's setting:
execute immediate 'alter session set time_zone = '''||tz||'''';
end if;

tz := extract(timezone_abbr from current_timestamp);

if tz = 'UNK' then
-- Use EST as the last resort:
tz := 'EST';
end if;

dbms_output.put_line('TZ='||tz);


INSERT INTO T_SYSTEM_SETTINGS(PARAMETER, VALUE) VALUES ( 'DataPurgingDatabaseUpdateSchedule', '
<com.aveksa.common.scheduler.rules.CronRule>
<year>*</year>
<month>*</month>
<dayOfMonth>-</dayOfMonth>
<dayOfWeek>'||v_dayofweek||'</dayOfWeek>
<hour>1</hour>
<minute>0</minute>
<ruleStartDate>' || to_char(v_date, 'yyyy-mm-dd hh24:mi:ss ') || tz || '</ruleStartDate>
</com.aveksa.common.scheduler.rules.CronRule>
' );


-- Create Task List to Notify Admin about new scheduled Data Purging job
INSERT INTO T_AV_TASK_LIST (ID, MEU_ID, MSG, DATA1, DATA2, DATA3, URL, CREATED_ON, COMPLETED_ON)
VALUES( TASKLIST_SEQUENCE.NEXTVAL, 0, 'DATAPURGE_SCHEDULED', NULL, NULL, NULL, NULL, SYSDATE, NULL);

END IF;
END IF;

END;
java.sql.SQLDataException: ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 27

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1707)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1670)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:310)
at com.aveksa.migration.jdbctool.SQLFileExecutor.execute(SQLFileExecutor.java:221)
at com.aveksa.migration.jdbctool.SQLFileExecutor.execute(SQLFileExecutor.java:107)
at com.aveksa.migration.jdbctool.SQLFileExecutor.execute(SQLFileExecutor.java:98)
at com.aveksa.migration.jdbctool.IncrementalUpdate.update(IncrementalUpdate.java:481)
at com.aveksa.migration.jdbctool.MigrateSchema.execute(MigrateSchema.java:140)
at com.aveksa.migration.jdbctool.MigrateSchema.execute(MigrateSchema.java:64)
at com.aveksa.cli.CreateMigrateSchemaModule.runCommandImpl(CreateMigrateSchemaModule.java:164)
at com.aveksa.cli.BaseDatabaseCLIModule.runCommand(BaseDatabaseCLIModule.java:60)
at com.aveksa.cli.Main.run(Main.java:350)
at com.aveksa.cli.Main.main(Main.java:390)
[0:00:00] File Completion Time: /home/oracle/database/updates/7.0/ACM-58461.sql
[0:00:44] Total Migration Time
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 27

Outcomes