Bug 472563 - Satellite: db-control extend throws ORA-27038 (file exists) errors due to unused or mis-ordered datafiles in dba_data_files
Satellite: db-control extend throws ORA-27038 (file exists) errors due to unu...
Status: CLOSED CURRENTRELEASE
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Server (Show other bugs)
502
All Linux
medium Severity medium
: ---
: ---
Assigned To: Michael Mráka
Sayli Karmarkar
:
Depends On:
Blocks: 456985
  Show dependency treegraph
 
Reported: 2008-11-21 13:48 EST by Xixi
Modified: 2010-10-23 02:07 EDT (History)
3 users (show)

See Also:
Fixed In Version: sat530
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2009-09-10 16:30:59 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Xixi 2008-11-21 13:48:52 EST
Description of problem:

We've had several reports of the following error when doing a "db-control extend DATA_TBS":

"Extending DATA_TBS... DBD::Oracle::db do failed: ORA-01537: cannot add data file '/rhnsat/data/rhnsat/data_15.dbf' - file already part of database (DBD ERROR: OCIStmtExecute) [for Statement "ALTER TABLESPACE DATA_TBS ADD DATAFILE '/rhnsat/data/rhnsat/data_15.dbf' SIZE 512000 K"] at /usr/lib/perl5/site_perl/5.8.5/Dobby/DB.pm line 139."

This is caused by mis-ordered datafiles (in dba_data_files) or unused datafiles so that when what db-control tries to create what it thinks should be the next datafile in sequence, it already exists.

Version-Release number of selected component (if applicable):
Various, definitely 5.x.

Creating BZ per mmraka.

How reproducible:
Unknown.

Steps to Reproduce:
1. At least for unused datafiles the only possibility which comes my mind: do backup, extend database, then restore backup and db-control does not remove the new data file that had been created.
2. Run db-control extend DATA_TBS.
3.
  
Actual results:
Error as stated above.

Expected results:
db-control should remove or re-use unused datafiles, and handle file ordering better so it won't try to create a data file that already exists.

Additional info:

Examples:
In the case of mis-ordered datafiles, dba_data_files shows -

SQL> select file_name from dba_data_files order by file_id;

FILE_NAME
--------------------------------------------------------------------------------
/rhnsat/data/rhnsat/system.dbf
/rhnsat/data/rhnsat/undo_01.dbf
/rhnsat/data/rhnsat/undo_02.dbf
/rhnsat/data/rhnsat/tools_01.dbf
/rhnsat/data/rhnsat/users_01.dbf
/rhnsat/data/rhnsat/data_01.dbf
/rhnsat/data/rhnsat/data_02.dbf
/rhnsat/data/rhnsat/data_03.dbf
/rhnsat/data/rhnsat/data_04.dbf
/rhnsat/data/rhnsat/data_05.dbf
/rhnsat/data/rhnsat/data_06.dbf
/rhnsat/data/rhnsat/data_07.dbf
/rhnsat/data/rhnsat/data_08.dbf
/rhnsat/data/rhnsat/data_09.dbf
/rhnsat/data/rhnsat/data_10.dbf
/rhnsat/data/rhnsat/data_11.dbf
/rhnsat/data/rhnsat/undo_03.dbf
/rhnsat/data/rhnsat/data_15.dbf
/rhnsat/data/rhnsat/data_12.dbf
/rhnsat/data/rhnsat/data_13.dbf
/rhnsat/data/rhnsat/data_14.dbf
/rhnsat/data/rhnsat/system_02.dbf

[snip]
FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/rhnsat/data/rhnsat/data_15.dbf
      18 DATA_TBS                        524288000      64000 AVAILABLE
        18 NO           0          0            0  524156928       63984

/rhnsat/data/rhnsat/data_12.dbf
      19 DATA_TBS                        524288000      64000 AVAILABLE
        19 NO           0          0            0  524156928       63984

/rhnsat/data/rhnsat/data_13.dbf
      20 DATA_TBS                        524288000      64000 AVAILABLE
        20 NO           0          0            0  524156928       63984

/rhnsat/data/rhnsat/data_14.dbf
      21 DATA_TBS                        524288000      64000 AVAILABLE
        21 NO           0          0            0  524156928       63984
[snip]

In the case of un-used datafiles, dba_data_files shows (no data_10.dbf) -

SQL> select file_name FILENAME, status STATUS, bytes BYTES from dba_data_files where tablespace_name = 'DATA_TBS' order by file_ID;

FILENAME
--------------------------------------------------------------------------------
STATUS         BYTES
--------- ----------
/rhnsat/data/rhnsat/data_01.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_02.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_03.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_04.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_05.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_06.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_07.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_08.dbf
AVAILABLE  524288000

/rhnsat/data/rhnsat/data_09.dbf
AVAILABLE  524288000

9 rows selected.

ls shows -

-bash-3.00$ ls -l /rhnsat/data/rhnsat/
total 7016997
-rw-r-----  1 oracle dba   4644864 Nov 18 16:55 control_01.ctl
-rw-r-----  1 oracle dba   4644864 Nov 18 16:55 control_02.ctl
-rw-r-----  1 oracle dba   4644864 Nov 18 16:55 control_03.ctl
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_01.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_02.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_03.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_04.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_05.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_06.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_07.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_08.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 data_09.dbf
-rw-r-----  1 oracle dba 524296192 Nov 12 09:37 data_10.dbf
-rw-r-----  1 oracle dba 209715712 Nov 18 16:54 redo_1001.log
-rw-r-----  1 oracle dba 209715712 Nov 18 16:54 redo_1002.log
-rw-r-----  1 oracle dba 209715712 Nov 18 16:54 redo_1101.log
-rw-r-----  1 oracle dba 209715712 Nov 18 16:54 redo_1102.log
-rw-r-----  1 oracle dba 262152192 Nov 18 16:54 system.dbf
-rw-r-----  1 oracle dba 262152192 Nov 18 11:06 temp_01.dbf
-rw-r-----  1 oracle dba 134225920 Nov 18 16:54 tools_01.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 undo_01.dbf
-rw-r-----  1 oracle dba 524296192 Nov 18 16:54 undo_02.dbf
-rw-r-----  1 oracle dba 134225920 Nov 18 16:54 users_01.dbf
-bash-3.00$
Comment 1 Xixi 2008-11-21 13:55:08 EST
Workarounds:

For mis-ordered datafiles - 

1. make sure working backup of database exists.
2. query "select file_name from dba_data_files" to see what the next datafile should be named.
3. manually extend database with file name decided from above and substituting that in the command in the db-control failure error message, e.g., "ALTER TABLESPACE DATA_TBS ADD DATAFILE '/rhnsat/data/rhnsat/data_16.dbf' SIZE 512000 K"

For un-used datafiles -

1. make sure working backup of database exists.
2. query "select file_name from dba_data_files" to make sure the datafile from the error is not listed, and then confirm by doing a "ls -l /rhnsat/data/rhnsat/", the unused datafile should have a last modified date older than the others.
3. manually re-add/re-use that data file, e.g., "alter tablespace data_tbs add datafile '/rhnsat/data/rhnsat/data_10.dbf' size 512m reuse;"
Comment 3 Michael Mráka 2008-11-28 09:38:39 EST
Fixed in Spacewalk git repo:
commit 0088b61b5c592bcb8738bb25799f0248133d38db
Date:   Fri Nov 28 15:27:22 2008 +0100
    472563 - un-used datafiles - reuse already created datafiles
commit 5374370509d1baf30cbc445a8f648cb4fd08d5c0
Date:   Fri Nov 28 15:24:36 2008 +0100
    472563 -  mis-ordered datafiles - order files by name (not id)

Package spacewalk-web-0.4.5-1 (spacewalk-dobby-0.4.5-1).
Comment 5 Michael Mráka 2009-01-22 08:50:10 EST
You hit independent bug 479600. :(
Please retry with Satellite-5.3.0-RHEL4-re20090119.1.
Comment 8 Brandon Perkins 2009-09-10 16:30:59 EDT
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/RHEA-2009-1434.html

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