Steps to perform config db cleanup in Oracle

Nick shared this question 51 days ago
Answered

Hi,

After deleting YF repository in Oracle, and reinstalling the application (8.02), it is giving the following error:

...

Creating sequences

Creating sequence NextDocumentSequence

Creating sequence NextEventSequence

Creating sequence NextPerformanceSequence

Creating sequence NextSystemSequence

Creating sequence NextTestingSequence

Creating sequence NextTextEntitySequence


Updating database

Applying Liquibase Schema Updates:

Change Set com/hof/patch/db-schema-changelog-8.0.xml::YFN-13781 - Enable pruning on a separate metric::tgleeson failed. Error: ORA-00942: table or view does not exist

[Failed SQL: INSERT INTO CachedLineMetric (CachedLineId, FieldTemplateId, AggregateType)

SELECT CachedLineId, MetricFieldTemplateId, MetricAggregationType

FROM CachedLine]

liquibase.exception.MigrationFailedException: Migration failed for change set com/hof/patch/db-schema-changelog-8.0.xml::YFN-13781 - Enable pruning on a separate metric::tgleeson:

Reason: liquibase.exception.DatabaseException: ORA-00942: table or view does not exist

[Failed SQL: INSERT INTO CachedLineMetric (CachedLineId, FieldTemplateId, AggregateType)

SELECT CachedLineId, MetricFieldTemplateId, MetricAggregationType

FROM CachedLine]

...

Going by the error, it seems the table is not getting laid down, but we are unsure why. Is it because of the previous db cleanup activity is missing something?

Do you have any recommendations on how to resolve this issue?

Thanks,

Nick

Comments (1)

photo
1

Do you have the steps relating to all the tables/sequences/store procedure etc that needs to be cleaned up?

photo
1

Hi Nick,

Thanks for reaching out. I'm not able to find a pre-existing ticket or task exactly like this, and this is of course unexpected behavior, but what I can say is that for some reason, it looks like the CACHEDLINEMETRIC table has not been inserted into your database.

You can manually insert this table via the following query:

CREATE TABLE
        <yourSchema>.CACHEDLINEMETRIC
        (
              CACHEDLINEID NUMBER(38),
              FIELDTEMPLATEID INTEGER,
              AGGREGATETYPE VARCHAR2(40)
          )
If you attempt installation, and head into your RDBMS you should see a bunch of tables already inserted, but this one (and several others) will be missing, but if you manually insert it and re-run the installation, it will append the required tables during the installation process.

If you run into more errors like the one above, you'd just need to manually insert the tables as necessary (you can compare against another Oracle instance to see what the data types would be... the error log itself will show you the table name and field names).

I don't see how a cleanup process would have deleted this table, at least not one we'd recommend. There shouldn't be a cleanup process that touches Stored Procedures or Sequences either.

The only cleanup queries we have to provide will handle the following:

-Reports, along with any dependencies that were set as 'ARCHIVED'

-Reports, views and filters that were set as 'DELETED' along with any dependencies

-Orphaned Document records

But will not delete tables, Stored Procedures, or Sequences. I've attached that SQL file in case you're interested in that.

Please let me know if this gives you what you're looking for and whether you have any follow-up questions or concerns.

Regards,

Mike

photo
1

Hi Nick,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Nick,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

photo
1

Hi Mike,

That was very helpful.

Thanks for sharing the info and your support.

Regards,

Nick

photo
1

Hi Nick,

You're welcome!

Regards,

Mike

photo