Solved

Unresponsive while deploying file enable_rowkey.SQL - APPS9-UPD17 to APPS10-UPD13 Upgrade

  • 27 October 2021
  • 2 replies
  • 158 views
surtlk
Rank
Userlevel 1
Badge +3

Hi & Good day to everyone,

 

I’m currently executing the upgrade scripts where it is running standstill  in the middle of the upgrade, when executing  ‘enable_rowkey.sql’.

 

The output of the _enable_rowkey.log is as follows: (it’s still holding the log file for writing)

Loading rowkey for CUSTOMER_ORDER_LINE_TAB
Table CUSTOMER_ORDER_LINE_TAB loaded at 2021-10-15 20:21:56
Loading rowkey for EQUIPMENT_OBJECT_TAB
Table EQUIPMENT_OBJECT_TAB loaded at 2021-10-15 20:21:56
Loading rowkey for FND_ROLE_TAB

 

 

This is my problem. I will let you know troubleshooting steps done in this regard in my below comments.

 

Any advice is highly appreciated.

 

Best Regards,

Supun Ranatunga.

icon

Best answer by surtlk 28 October 2021, 13:37

View original
This topic has been closed for comments

2 replies

surtlk
Rank
Userlevel 1
Badge +3

Here is my follow-up comment to let you know troubleshooting steps up to now.

 

First I tried running the upgrade with Rowkeys enabled for 3 hours.

Once the upgrade came to the standstill point above, I checked active sessions to the database and it showed the session where the query showed as:

BEGIN
   Database_SYS.Enable_Rowkey_Table('FND_ROLE_TAB');
   Dbms_Output.Put_Line('Table FND_ROLE_TAB loaded at ' || TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
END;

 

Now the row count of this table ‘FND_ROLE_TAB’ was 132 rows, which I assume is fairly low count & should not take this amount of time to finish. 

 

Then since I have all backups available, I killed the session to see what will happen. Once I killed the session, rest of the upgrade steps began to execute but with errors from time to time.

 

Second time I ran the upgrade with Rowkeys Disabled

 

Again we met with the same issue mentioned. Stuck with the log showing:

Enabling of all rowkeys is not selected. Only keys designed as mandatory will be enabled.
 
Loading rowkey for CUSTOMER_ORDER_LINE_TAB
Table CUSTOMER_ORDER_LINE_TAB loaded at 2021-10-21 11:21:57
Loading rowkey for EQUIPMENT_OBJECT_TAB
Table EQUIPMENT_OBJECT_TAB loaded at 2021-10-21 11:21:57
Loading rowkey for FND_ROLE_TAB
 

 

This is the current status. I will investigate myself as well.

If I manage to find a solution, I will update here.

 

May the force be with you & Best Regards,

Supun Ranatunga.

surtlk
Rank
Userlevel 1
Badge +3

Hi All,

 

I managed to find the cause.
The upgrade is executed on a multi-tenant database, using the pluggable database as the upgrade DB.

Now the pluggable DB had it’s job_queue_processes set to 16 when I checked.

However the Container Database (CDB) had been set to job_queue_processes=0

 

It appears although the upgrade scripts are executed on the PDB, for some tables to enable rowkeys, job_queue_processes of the CDB must be enabled as well.

 

Otherwise the rowkey enabling will partially progress and appear hanged at a certain table (as shown in the _enable_rowkey.log)

 

Solution:

  1. Log into the CDB as SYS
  2. Execute the following query to set job_queue_processes

ALTER SYSTEM SET job_queue_processes=10;

 

Now check the _enable_rowkey.log again and you will notice gradually it’s getting completed and other scripts begin to execute.

 

P.S.

While checking on this issue I noticed that sometimes there could be a bug where enabling rowkeys can get hung up when the DB is massive.

For this instance however, that was not the case.

 

Thanks & Best Regards,
Supun Ranatunga.

Terms & ConditionsCookie settings