Description of problem: Solution for performance problems described in bug #494064 was creating a functional index (TIME_SERIES_PROBE_ID_IDX) on top of TIME_SERIES table. When creating this index during schema upgrade on a table that has several millions of rows (according to bug #494064, such customers exist already), schema upgrade fails with ORA-01652: Unable to extend temp segment ... -- in other words, when sorting this index in TEMP_TBS, we will run out of its default size (embedded database). There are several solutions to this problem that I see: 1) Document one additional step in upgrade preparation steps telling the customer to extend the TEMP_TBS tablespace if needed (this would depend on the number of rows in time_series table and we're able to provide exact procedures, selects, numbers ...). I prefer this solution. 2) Create a new empty table, with rows defined like TIME_SERIES, create the functional index on the empty table, insert the data from old table to the new table (committing after every ~100000 records), drop TIME_SERIES and rename the new table. We're able to automate all this, but there's a danger the customer might need to extend DATA_TBS for the new table. 3) Wrap the current schema upgrade script creating TIME_SERIES_PROBE_ID_IDX with an except block and ignore the error. After the schema upgrade completes, instruct the customer to look into the schema upgrade logs and contact the support if the index creation failed. Ugly solution :) Version-Release number of selected component (if applicable): spacewalk-schema-0.5.20-17 rhn-upgrade-5.3.0.14-1 How reproducible: Always Steps to Reproduce: 1. Install < 5.3.0 Satellite and populate the time_series table so that it contains ~ 40 million rows at least. 2. Install rhn-upgrade and upgrade to 5.3.0 Satellite, following the upgrade instructions. Actual results: Schema upgrade for this scenario fails with ORA-01652: Unable to extend temp segment. Expected results: Schema upgrade succeeds. Additional info: N/A
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