Bug 563902

Summary: 012-2-rhnPackageFile-data.sql causes UNDO_TBS overflows for very large Satellite databases
Product: Red Hat Satellite 5 Reporter: Milan Zázrivec <mzazrivec>
Component: UpgradesAssignee: Michael Mráka <mmraka>
Status: CLOSED CURRENTRELEASE QA Contact: Martin Minar <mminar>
Severity: medium Docs Contact:
Priority: low    
Version: unspecifiedCC: cperry, gkhachik, jhutar, mkoci, mminar, mmraka
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard: Fixed in the 5.4.0 Release - GA'd 2010-10-27
Fixed In Version: oracle-server-scripts-10.2.0-32 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2010-10-28 15:04:01 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Bug Depends On:    
Bug Blocks: 487678, 608757    

Description Milan Zázrivec 2010-02-11 14:49:58 UTC
Description of problem:
This bug report is to track problems with schema upgrade scriptlet
012-2-rhnPackageFile-data.sql, introduced in Spacewalk 0.8.

It's aim is to update rhnPackageFile and rhnChecksum tables with 
correct sha256 checksums and checksum ids. For very large
customer databases (> 20GB DATA_TBS), this schema upgrade scriptlet
is very likely to run out of standard UNDO_TBS tablespace size (500 MB).

For very large rhnPackageFile tables (~ 50M rows), this update
needs further optimizations.

Version-Release number of selected component (if applicable):

How reproducible:

Steps to Reproduce:
1. Satellite 5.3.0 and earlier with very large rhnPackageFile table
(~ 50M rows)
2. Upgrade to newer Satellite version
Actual results:
You run out of standard - sized UNDO_TBS during schema upgrade.

Expected results:
You won't run out of standard - sized UNDO_TBS during schema upgrade.

Additional info:
At the time of writing this bug, I'm referring to non-existent, future
version of Satellite product.

Comment 2 Michael Mráka 2010-03-15 11:22:31 UTC
We made several attempts to optimize sql upgrade script for current minimal UNDO (500MB) and TEMP (250MB)  size unfortunately all of them took more then 24 hours to complete.
As this is an unacceptable long upgrade window for customers we agreed to change minimal sizes to 1GB UNDO and 1GB TEMP space.
After this modified upgrade script completed in about 4hrs (on ~ 50M row rhnPackageFile table).

Comment 3 Michael Mráka 2010-03-15 11:24:02 UTC
Spacewalk git:
commit 32f607647e4c0214233d6de15c05a5f5f41ec542
    563902 - tuned sql upgrade script
    we have to fit into 1GB TEMP and 1GB UNDO here
commit dbf50d25c3839a113ec9065254cc8c776e0d8bad
    563902 - let's make default TEMP and UNDO 1GB
    and save us from many troubles during upgrades

Comment 4 Michael Mráka 2010-03-15 12:35:04 UTC
Make sure upgrade instructions for satellite >5.3.X contain:

Prior running schema upgrade (spacewalk-schema-upgrade) extend both UNDO and TEMP_TBS tablespaces to at least 1GB.

Comment 8 Garik Khachikyan 2010-10-11 13:10:23 UTC

the instruction about extending UND_TBS and TEMP_TBS both to 1GB is written in both rhel4, rhel5 rhn-upgrade documents.

Comment 9 Clifford Perry 2010-10-28 15:04:01 UTC
The 5.4.0 RHN Satellite and RHN Proxy release has occurred. This issue has been resolved with this release. 

RHEA-2010:0801 - RHN Satellite Server 5.4.0 Upgrade

RHEA-2010:0803 - RHN Tools enhancement update

RHEA-2010:0802 - RHN Proxy Server 5.4.0 bug fix update

RHEA-2010:0800 - RHN Satellite Server 5.4.0

Docs are available: