Bug 1175637 - The migration from PostgreSQL to Oracle 10g fails
Summary: The migration from PostgreSQL to Oracle 10g fails
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Upgrades
Version: 570
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Tomas Lestach
QA Contact: Martin Korbel
URL:
Whiteboard:
Depends On:
Blocks: sat570-upgrades
TreeView+ depends on / blocked
 
Reported: 2014-12-18 09:14 UTC by Martin Korbel
Modified: 2015-01-13 10:26 UTC (History)
2 users (show)

Fixed In Version: spacewalk-utils-2.3.2-13
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2015-01-13 10:26:06 UTC
Target Upstream Version:
Embargoed:


Attachments (Terms of Use)

Description Martin Korbel 2014-12-18 09:14:43 UTC
Description of problem:
The migration process from Sat5.7 embPG -> Sat5.7 extOracle 10g fails with error
"ERROR at line 1:
ORA-00923: FROM keyword not found where expected"
tlestach + tkasparek have investigated it and the problem is SQL command "WITHIN GROUP", what can use from the version 11g or higher.

Version-Release number of selected component (if applicable):
Satellite-5.7.0-RHEL6-re20141212.0

How reproducible:
100%

Steps to Reproduce:
1. We have got a Satellite 5.7 with embPG DB + a machine with Oracle 10g DB
2. Follow these steps:
> yum upgrade -y
> /usr/bin/spacewalk-schema-upgrade
> yum install spacewalk-utils
> yum remove rhn-upgrade -y  # bz1171675
> rhn-satellite stop
> service postgresql92-postgresql start
# make a dump
> spacewalk-dump-schema --to=oracle  > /tmp/spacewalk-postgresql.dump
# disable postgresql
> service postgresql92-postgresql stop
> chkconfig postgresql92-postgresql off
> yum remove spacewalk-postgresql
> yum install spacewalk-oracle # You should be sure, the dependencies are installed from the correct channel.
# Configure DB
> setenforce 0 # problem with selinux
> spacewalk-setup --db-only --external-oracle
> NLS_LANG=AMERICAN_AMERICA.UTF8 sqlplus spaceuser/XXX@//external-oracle-db.redhat.com/xrhnsatx.world < /tmp/spacewalk-postgresql.dump
...
SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13  SELECT mi.index_name, mi.table_name, mi.tablespace_name, LISTAGG ( mic.column_name, ', ' )
         *
ERROR at line 3:
ORA-06550: line 3, column 10:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 2, column 14:
PL/SQL: SQL Statement ignored


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
...

Actual results:
SQL error

Expected results:
No SQL error

Additional info:
This is problematic SQL command from dump:
SELECT mi.index_name, mi.table_name, mi.tablespace_name, LISTAGG ( mic.column_name, ', ' ) WITHIN GROUP ( ORDER BY mic.column_name ) index_columns FROM tmp_migration_indexes mi, tmp_migration_index_columns mic WHERE mi.index_name = mic.index_name GROUP BY mi.index_name, mi.table_name, mi.tablespace_name;

Comment 1 Tomas Lestach 2014-12-19 13:23:21 UTC
Taking ...

Comment 2 Tomas Lestach 2014-12-19 14:54:55 UTC
Making the sql Oracle 10g compatible ...

spacewalk.git: bc73c805c4765aa7542db3d379d92ccd415a4da7

Comment 7 Martin Korbel 2015-01-06 13:07:46 UTC
VERIFIED Satellite-5.7.0-RHEL6-re20150105.1 (spacewalk-utils-2.3.2-13.el6sat).

Reproducer comment 0.

Comment 8 Clifford Perry 2015-01-13 10:26:06 UTC
With the release of Red Hat Satellite 5.7 on January 12th 2015 this bug is
being moved to a Closed Current Release state. 

The Satellite 5.7 GA Errata:
 - https://rhn.redhat.com/errata/RHSA-2015-0033.html 

Satellite 5.7 Release Notes:
 -
https://access.redhat.com/documentation/en-US/Red_Hat_Satellite/5.7/html-single/Release_Notes/index.html

Satellite Customer Portal Blog announcement for release:
 - https://access.redhat.com/blogs/1169563/posts/1315743 

Cliff

NOTE: This bug has not been re-verified (moved to RELEASE_PENDING) prior to release. We assume that the bug has indeed been fixed and not regressed since we initially verified it. Please re-open in the future if needed.


Note You need to log in before you can comment on or make changes to this bug.