Prev: tablespace management
Next: Connections which go bad
From: Johne_uk on 11 Jan 2010 09:40
Hi

I have a tablespace called EXT_DATA which has 4 datafiles. When I
created the 4th datafile I accidentally prefixed it with a period and
want to remove this as it is causing issues with my backup scripts. I
brought the tablespace offline and issued the following command.

alter tablespace ext_data rename datafile '/data1/oradata/
oranc1/.ext_data_4.dbf' to '/data1/oradata/oranc1/ext_data_4.dbf';

However, it does not work as the period causes issues.

alter tablespace ext_data rename datafile '/data1/oradata/
oranc1/.ext_data_4.dbf' to '/data1/oradata/oranc1/ext_data_4.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01141: error renaming data file 15 - new file
'/data1/oradata/oranc1/ext_data_4.dbf' not found
ORA-01110: data file 15: '/data1/oradata/oranc1/.ext_data_4.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

I'm sure there is a simple answer to renmaing this datafile but I
simply can't find it. Any offers off assistance would be greatly
appreciated.

Thanks in advance
From: ddf on 11 Jan 2010 09:51
On Jan 11, 9:40 am, Johne_uk <edg...(a)tiscali.co.uk> wrote:
> Hi
>
> I have a tablespace called EXT_DATA which has 4 datafiles. When I
> created the 4th datafile I accidentally prefixed it with a period and
> want to remove this as it is causing issues with my backup scripts. I
> brought the tablespace offline and issued the following command.
>
> alter tablespace ext_data rename datafile '/data1/oradata/
> oranc1/.ext_data_4.dbf' to '/data1/oradata/oranc1/ext_data_4.dbf';
>
> However, it does not work as the period causes issues.
>
> alter tablespace ext_data rename datafile '/data1/oradata/
> oranc1/.ext_data_4.dbf' to '/data1/oradata/oranc1/ext_data_4.dbf'
> *
> ERROR at line 1:
> ORA-01525: error in renaming data files
> ORA-01141: error renaming data file 15 - new file
> '/data1/oradata/oranc1/ext_data_4.dbf' not found
> ORA-01110: data file 15: '/data1/oradata/oranc1/.ext_data_4.dbf'
> ORA-27037: unable to obtain file status
> SVR4 Error: 2: No such file or directory
> Additional information: 3
>
> I'm sure there is a simple answer to renmaing this datafile but I
> simply can't find it. Any offers off assistance would be greatly
> appreciated.
>
> Thanks in advance

That simple answer is:

1) Copy or rename the datafile at the O/S level BEFORE attempting to
rename it in the database. Take the tablespace offline, copy the
datafile to the proper name, rename the datafile in the database,
recover the tablespace and then bring the tablespace online. You
should then be able to drop the old datafile at the O/S level.
'Renaming' the datafile in the database merely updates the data
dictionary, it does NOT rename files at the O/S level.


David Fitzjarrell
From: Johne_uk on 11 Jan 2010 10:01

> That simple answer is:
>
> 1) Copy or rename the datafile at the O/S level BEFORE attempting to
> rename it in the database.  Take the tablespace offline, copy the
> datafile to the proper name,  rename the datafile in the database,
> recover the tablespace and then bring the tablespace online.  You
> should then be able to drop the old datafile at the O/S level.
> 'Renaming' the datafile in the database merely updates the data
> dictionary, it does NOT rename files at the O/S level.
>
> David Fitzjarrell


Thank you very much David all sorted now. I'd wrongly assumed that
10G would also perfom the action at OS level as it does at deleting
tablespace datafiles when a tablespace is dropped.

Best regards
John
 | 
Pages: 1
Prev: tablespace management
Next: Connections which go bad