Forum Stats

  • 3,733,742 Users
  • 2,246,813 Discussions
  • 7,856,862 Comments

Discussions

Any grant command taking 10-12 seconds during catctl to 12.1.0.2

The steps followed to perform the upgrade were based on "Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) ( Doc ID 1503653.1 )".

After running pre-upgrade steps, including executing preupgrd.sql script, the script catctl has been initialized from 12c Oracle Home:

nohup $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql > log_upgrade_sinnprod1.log &

After 2 hours, catctl was still on phase #1. During the process, 3 main logs were actively monitored:

- alert.log (no upgrade related information or errors, other than the expected messages)

- catctl execution log (Informational only, showing catctl progress on phase #1

- $ORACLE_HOME/diagnostics/catupgrd0.log

One detail caught up our attention:

At $ORACLE_HOME/diagnostics/catupgrd0.log log, every single grant command, without exception, was taking the minimum of 10 seconds to complete, and maximum of 12.

Another database has been successfully upgraded and catctl ran in about 40 minutes.

I did a: grep "grant" $ORACLE_HOME/diagnostics/catupgrd0.log | wc -l

on this other database (successfully upgraded, 0 issues) and the result number was 5430. But this 5430 grant commands were executed in 1 milisecond. On the other hand, based on the current problematic scenario, the estimate is:

5430 * 10 seconds / 60 "Minutes" / 60 "Hours" --> 15 hours (just for catctl complete the grant commands). This estimate not even considering the possible issues that might incur on the next catctl phases.

Additional summary information:

* dictionary, fixex_objects, and sys schema stats were collected prior upgrade.

Platform: RHEL 6.8 x86_64

Source Database Version: 10.2.0.5 (PSU Patch 16056270 - 10.2.0.5.11)

Target Database Version: 12.1.0.2 (12.1.0.2.161018 bundle patch)

Anyone else has faced this problem?

Can it really be related only to grant slow execution? if so, is there any troubleshooting step?

Already tried flushing shared_pool, collecting stats, relinking Oracle Home....

Tagged:
This discussion has been closed.