Skip to main content
Solved

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

  • October 27, 2021
  • 2 replies
  • 184 views
surtlk
Do Gooder (Employee)
Forum|alt.badge.img+3
  • surtlk
  • Do Gooder (Employee)
  • 4 replies

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.

Best answer by surtlk

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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

2 replies

surtlk
Do Gooder (Employee)
Forum|alt.badge.img+3
  • surtlk
  • Author
  • Do Gooder (Employee)
  • 4 replies
  • October 27, 2021

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
Do Gooder (Employee)
Forum|alt.badge.img+3
  • surtlk
  • Author
  • Do Gooder (Employee)
  • 4 replies
  • Answer
  • October 28, 2021

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.

surtlk
A
2 people like this
  • surtlk
    surtlk
  • A
    AmilaF
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings