Bug 509112

Fixed according to option 1, satellite.git, SATELLITE-5.3:
b5cc09fa98b8280221f5763b3759819b770fe11b

Updated documentation / sql scripts are in rhn-upgrade-5_3_0_15-1.

rhn-upgrade-5.3.0.15-1

The instructions above are contained in the upgrade docs now. Following these instructions results in an upgrade from 520 to 530 without error.

Here is what is currently in the 1b.txt file:

2. Conversion of database schema.

   NOTE: If you are using external database you have to upgrade it now!
         Please consult your DBA. Do not continue untill your database
         is upgraded.

   NOTE: If you are using 64-bit embedded database -- see output of
           file /opt/apps/oracle/web/product/10.2.0/db_1/lib/libclntsh.so.10.1 \
           | awk '{print $3}'
         log in as oracle user and run:
           cd /etc/sysconfig/rhn/satellite-upgrade/
           export ORACLE_SID=rhnsat
           sqlplus '/ as sysdba' @satellite-oracle-64bit-fix.sql
         Log out to become root again.

   Convert database schema (your DBA may want to do this for you):
   NOTE: these scripts generate log files. After running a script, inspect
         the corresponding log file for errors. Consult your Red Hat
         representative if any errors are encountered.
         ***These scripts and their corresponding log files need to be
            archived indefinitely.***

   NOTE: as a part of schema upgrade, a function based index to lookup in
         TIME_SERIES table will be created. For the index creation to complete
         successfully, you need to make sure there's enough free space in
         database temporary tablespace. Space requirements in temporary tablespace
         may vary and will depend on the current size of TIME_SERIES table. In
         case there is not enough free space in temporary tablespace, its size
         needs to be extended.

         To find out how much free space in temporary tablespace is needed:
           cd /etc/sysconfig/rhn/satellite-upgrade
           sqlplus -S dbusername/dppassword@dbSID @satellite-time_series-space.sql

         If you use external database, consult your DBA.

         If you use embedded database, do following to find out current free space
         in temporary tablespace:
           su - oracle
           cd /etc/sysconfig/rhn/satellite-upgrade
           ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql
         In case there's less free space in temporary tablespace than required,
         do following:
           su - oracle
           db-control extend TEMP_TBS
         Make sure the above steps gained enough free space in temporary tablespace
         and use db-control command again if necessary.

   Verify that database tablespaces have some free space. The database
   schema upgrade scripts below may insert/update some data and cause
   the tablespaces to run out of space if it is close to full. The
   db-control report command can be useful for quick review.

   Ensure that all satellite services except database are stopped:
      /usr/sbin/rhn-satellite stop

   If you are using embedded database, start it with:
      service oracle start

   Upgrade your database schema using spacewalk-schema-upgrade command
   (see manual page for spacewalk-schema-upgrade for more information):
      /usr/bin/spacewalk-schema-upgrade

   ** THE FINAL VERSION:
        - run command rhn-schema-version
        - that should match the version of installed satellite-schema package:
          rpm -q --qf '%{version}-%{release}\n' satellite-schema

   Should this database schema conversion process fail (for example if it
   runs out of disk space), restore the database from backup, fix the
   problem, and rerun this step.

Release Pending
rhn-upgrade.noarch 0:5.3.0.21-1.el5sat                                        

rhn-satellite-5-upgrade-scenario-1b.txt
step 2 has the Time Series table information.

FAILQA 
I created sat520 and insterted 53 290 656 records to time_series table [1].

I followed the upgrade doc and got:
[root@xen15 satellite-upgrade]# sqlplus -S rhnsat/rhnsat@rhnsat @satellite-time_series-space.sq

Required space (MB)
-------------------
          1016.4386

I run:
  db-control extend TEMP_TBS 
several times till I got over 1016 MB. In fact I end on 1250 MB.

Then I run:
 spacewalk-schema-upgrade
and in other terminal I run:
 watch ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql
First it allocate fast a lot of TEMP_TBS and then speed of allocation slowed down.
Nevertheless it went past 200 MB of free space, which means that calculation is wrong.
I then extended TEMP_TBS few times again and it stops on:
TABLESPACE                        MB_TOTAL    MB_FREE
------------------------------- ---------- ----------
TEMP_TBS                              1750        521

Which means we need 1229 MB for 50M of records. 
Which means we have to multiply required space by factor 1.21. At least.
I suggest to use factor 1.5 or even 2.

[1] I had to extend both DATA_TBS and UNDO_TBS up to 9 GB for each.


Additionally before this action I had:
[root@xen15 ~]# su - oracle
-bash-3.2$ db-control report
Tablespace                  Size    Used   Avail   Use%
DATA_TBS                   10.7G    6.6G    4.1G    62%

and now had:
-bash-3.2$ db-control report
Tablespace                  Size    Used   Avail   Use%
DATA_TBS                   10.7G    7.5G    3.1G    71%

So I assume that insted of:
 Verify that database tablespaces have some free space.
in rhn-satellite-5-upgrade-scenario-1b.txt, we put something like:
 Verify that database tablespaces have some free space. And make sure that free space in DATA_TBS is at least the same as suggested for TEMP_TBS in previous paragraph.

satellite.git, SATELLITE-5.3: fe72e72ac92c92e0a28bde2f35cec31e076a9fcb

rhn-upgrade-5.3.0.24-1.el4sat & rhn-upgrade-5.3.0.23-1.el5sat

Estimates looks good on RHEL4 (79 GB in /var/satellite and 52M rows in time_series):

[root@pipa03 ~]# su - oracle
-bash-3.00$ cd /etc/sysconfig/rhn/satellite-upgrade
-bash-3.00$ sqlplus -S rhnsat/rhnsat@rhnsat @satellite-temp_tbs-estimate.sql

Space required (MB)
-------------------
            1488.26

-bash-3.00$ ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql

TABLESPACE                        MB_TOTAL    MB_FREE
------------------------------- ---------- ----------
TEMP_TBS                               250        250
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql

TABLESPACE                        MB_TOTAL    MB_FREE
------------------------------- ---------- ----------
TEMP_TBS                              1500       1500

testing procedure, tried 
[root@dhcp-lab-163 newsat]# rpm -qf /usr/bin/satellite-5.3.0-schema-upgrade
rhn-upgrade-5.3.0.24-1.el5sat

#1.
rhel5 x8664, sat520
#2.
81 GB in /var/satellite and 62M rows in time_series
#3.
-bash-3.2$ sqlplus -S rhnsat/rhnsat@rhnsat @satellite-temp_tbs-estimate.sql

Space required (MB)
-------------------
	    1810.75
#4.
7 x `db-control extend TEMP_TBS` = extended to 2000MB
#5.

[root@dhcp-lab-163 newsat]# /usr/bin/satellite-5.3.0-schema-upgrade
Schema upgrade: [rhn-satellite-schema-5.2.0-8] -> [satellite-schema-5.3.0.23-1.el5sat]
Searching for upgrade path: [rhn-satellite-schema-5.2.0-8] -> [satellite-schema-5.3.0.23-1]
Searching for upgrade path: [rhn-satellite-schema-5.2.0] -> [satellite-schema-5.3.0.23]
Searching for upgrade path: [rhn-satellite-schema-5.2] -> [satellite-schema-5.3.0]
Searching for upgrade path: [rhn-satellite-schema-5.2] -> [satellite-schema-5.3]
The path: [rhn-satellite-schema-5.2] -> [satellite-schema-5.3]
Planning to run sqlplus with [/var/log/spacewalk/schema-upgrade/20091001-114123-script.sql]
Hit Enter to continue or Ctrl+C to interrupt: 
Executing sqlplus, the logs are in /var/log/spacewalk/schema-upgrade/20091001-114123-*.
The database schema was upgraded to version [satellite-schema-5.3.0.23-1.el5sat].

--------
passed, estimation was OK

An advisory has been issued which should help the problem
described in this bug report. This report is therefore being
closed with a resolution of ERRATA. For more information
on therefore solution and/or where to find the updated files,
please follow the link below. You may reopen this bug report
if the solution does not work for you.

http://rhn.redhat.com/errata/RHBA-2009-1479.html