Re: Moving SYSTEM datafile

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 18 Dec 2002 09:12:15 +1100
Message-ID: <AaNL9.5181$jM5.15152@newsfeeds.bigpond.com>

Think about it.

Can your data dictionary *ever* be offline, with the rest of the database operational?

Nope.

Therefore, you need to shutdown, move the file yourself using standard operating system commands, then restart to the MOUNT state (because 'alter database' language generally means you are talking to the controlfile... which makes sense in this case, because no Oracle command actually moves datafiles for real on the hard disk. All the 'RENAME datafile 'x' to 'y' command does is to re-jig the pointers to the datafiles stored in the controlfile which SMON reads at startup).

Then you issue the command '"alter database rename file 'x' to 'y';" (where
'x' is the old path/filename and 'y' is the new one).

And then you open the database with an 'alter database open;' command.

Incidentally, there is no such command as 'alter database move datafile', as the following little test demonstrates (on 9i, Windows XP):

SQL> connect / as sysdba
Connected.
SQL> select name from v$datafile;

NAME

---

---

[snip]

D:\ORACLE\ORADATA\OEMREP\ODM01.DBF
D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF
D:\ORACLE\ORADATA\OEMREP\USERS01.DBF

SQL> alter tablespace tools offline;
Tablespace altered.

SQL> alter database move datafile 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF' to
'd:\tools01.dbf';

alter database move datafile 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF' to
'd:\tools01.dbf'

                                                                    *

ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

SQL> alter database move file 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF' to
'D:\TOOLS01.DBF';

alter database move file 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF' to
'D:\TOOLS01.DBF'

                    *

ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database rename file 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF' to
'd:\to

ols01.dbf';
alter database rename file 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF' to
'd:\tools01

.dbf'
*
ERROR at line 1:

ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 7 - new file 'd:\tools01.dbf' not found
ORA-01110: data file 7: 'D:\ORACLE\ORADATA\OEMREP\TOOLS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

The last command there is the correct one, ('alter database rename file') but fails (at least on a Windows system) because Oracle checks the proposed new location for the prior existence of the specified datafile... and since I didn't actually move the file to D:\, it's not there at the time of the check, and the whole thing therefore blows up. Proving that you are responsible for actually moving or renaming the datafile yourself, and no Oracle command provides that bit of functionality.

Regards
HJR "Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:ee6vvu0lb6iqk7ciit7omtk4vsuq508tfd_at_4ax.com...

> I can "move" an Oracle datafile by using the statement
> alter database move datafile "Path-to-datafile" to "new-path"
> if I put the corresponding Tablespace offline and actually copy the
> file before setting it online again.
>
> Can I do that with the SYSTEM datafile?
>
> Thanks
> Rick Denoire

Received on Tue Dec 17 2002 - 16:12:15 CST