After applying cumulative patch 6.7.0b398(6.7.3) NAC with Oracle DB takes longer time during startup

book

Article ID: 221578

calendar_today

Updated On:

Products

CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

We noticed that while applying cumulative 6.7.0b398(6.7.3) the NAC startup sequence takes longer. Can you please suggest if it's something unexpected or expected behavior as part of this cumulative.

 

Cause

This is only specific to NAC running with Oracle DB

Migration Script Details:

There is a migration script in the patch 6.7.0b398 i.e. 6.7.0.398 patch which is V6.8.0.0.202006051400000__long_raw_to_blob_-_ORACLE.sql. This script was added in scope of the defect NOLIO-3030 - Problem of peak of CPU on DB server. This script performs the following actions:

      • Changing type of the LONG RAW columns to the BLOB type
      • Rebuilds indexes that contain LOB columns

It may take more than usual time to run these actions on the big DB and NAC being available for an end user.

Estimation: A DB size of +300GB it took ~40 minutes for completion of above script execution. Please note the time taken for completion is determined not only by DB size but also the hardware specification of DB server, ongoing activities on DB along with any performance parameters configured by DBA on DB instance.

Environment

Release : 6.7.0b398(6.7.3)

Component : CA RELEASE AUTOMATION ADMINISTRATION

DB: Oracle (Only)

Resolution

Oracle Only

DB status Validation: To validate if the RA is able to complete the DB migration successfully. To check what is going on in the DB after the Nolio server is started with running the following script:

select v.sid, 
       v.serial#,
       v.username,
       v.status,
       v.server,
       v.schemaname,
       v.osuser,
       v.process,
       v.machine,
       v.program,
       v.module,
       v.logon_time,
       v.event,
       v.wait_class,
       v.state,
       s.*
  from v$session v
  left outer join v$sql s on s.sql_id = v.sql_id
 where v.username = '[!!!USER_NAME!!!]';

Note:: The above script must be executed with the user that has access privileges to the v$session and v$sql views. A System or sysdba user have these privileges. by default. This script allows us to check/monitor sessions running in the DB and will allow us to understand what is being executed with the flyway migration script.