Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Om.

Asked: August 16, 2002 - 8:41 am UTC

Last updated: September 21, 2018 - 12:55 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

When we install oracle and create the database by default (not manually) ...the system datafiles are located at a specific
location ..

Is is possible to move these (system tablespace datafiles) datafiles
from the original location to some new location ?

If yes how ?

Best regards,
Om

and Tom said...

Yes, you

alter database backup controlfile to trace

shutdown

move the files

edit the generated CREATE CONTROLFILE statement (found in the user dump destination) and change the filenames

use the generated script you just edited to startup the database.



Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

system tablespace datafiles

Om, August 16, 2002 - 12:09 pm UTC

very useful for me as I never knew it's possible to move
system datafiles.

A reader, August 16, 2002 - 12:34 pm UTC

What is the difference between recreating a controlfile like that and updating an existing controlfile with 'alter database rename...'? I read in recovery manual that when using a re-created controlfile, the recovery operations should use 'using backup controlfile'. That means there is something which differentiates the re-created controlfile from a normal controlfile. Can you explain that?

Also, even if I don't have the 'Create controlfile..' script, assuming that I know the location of all the files and other parameters like maxdatafiles etc, can I not create that script manually and run it to re-create the controlfile? My question is, if the controlfile is easily recreatable like that, why do people put somuch importance to it and multiplex it 5 times? Thanks for your patience. I would appreciate if you can explain this to me..

Tom Kyte
August 16, 2002 - 12:45 pm UTC

Well, in order to do the rename, you need to offline the tablespace. The one tablespace you cannot offline is... SYSTEM. Hence, this method will work (the create controlfile) whereas for this specific case, the alter database would not


The backup controlfile is with a binary controlfile. You have to use "using backup" since the binary controlfile would have different "state of the database" information in it (different scns and such) which would prematurely stop the recovery process (you need to tell us "ignore that data in there, recover until we run out of redo to apply, not when the control file says to stop")

Yes, you can create the create control file statment yourself (obviously -- it is just a script after all).

Why do we say multiplex it? Can you spell downtime.... That file is the bootstrap for us -- it, along with the parameter file, are two files that if you don't have you won't be starting. Do you want to sit there and say -- hmmm, lets see, what files did I have, where are they, etc etc etc. Or during a recovery, do you want to hand us archive by archive to recover OR do you want us to just recover by ourselves?



A reader, August 16, 2002 - 1:36 pm UTC

Thanks, but I still have some doubts:

1. We can use the 'alter database rename ' at mount stage to rename any datafile. Or is it not possible to rename the system datafile like this? Why?

2. What happens to the SCN information in the controlfile when a controlfile is recreated? How will the database sync the SCN with that of the datafiles?

If I issue a 'backup controlfile to <file>' at 8 am and then restore that controlfile binary backup at 10 am and try to open the database, it will give me a control file old error. I understand that it is because the SCN is not in sync. But if I issue a 'backup controlfile to trace' at 8 am and use that script to recreate a new controlfile at 10 am, why doesn't I get the error? Where does it get the SCN information then?

So what is the use of taking a binary copy of the controlfile. Looks like having a 'backup controlfile to trace' script is better than a binary backup. Do you agree? Why/whynot?

Thanks again for your time.

Tom Kyte
August 16, 2002 - 2:21 pm UTC

1) you could but I just always did it with the create controlfile statement.

When moving system -- I do it that way
When moving ANY OTHER tablespace -- i just offline it, move the files, rename the files online it.

2) it just happens.

The control file you create will read the files to figure out what is up.


I agree, I've never used a binary controlfile backup myself.

Relocating a non-system tablespace

A reader, November 13, 2002 - 9:48 am UTC

"When moving ANY OTHER tablespace -- i just offline it, move the files, rename
the files online it".

Hi Tom, Could you please elaborate on your above statement? I have to relocate a tablespace from a cooked datafile to a raw datafile say, from /oradata/bigtbsp01.dbf to /dev/rdsk/bigtbsp01. How would I do that?

Although i know tha above isn't gonna help me much, i am finding your claim to be true that raw doesn't give much benefit over cooked because I have already moved redo logs (and undo tbsp) to raw without seeing any difference i.e. "log file parallel write" is still our top wait event. But i'm under pressure to move data/index to raw as well.

Thanks much.

Tom Kyte
November 13, 2002 - 2:00 pm UTC

offline the tablespace.
move the datafiles (how you do this depends on your OS and all)
rename the datafiles
online the tablespace.

In hind site what I might do here would be:

create new tablespace
MOVE the objects into it (clean em up), alter table move, alter index rebuild
less chance of messing up....



I wouldn't do it if your waits are not related to file writes -- just a waste of your time and energy.

make sure to have FULL AND COMPLETE and tested (emphasis on TESTED) backups before you do this.

also -- look to make sure the logs are on dedicated devices, that you have enough (eg: you don't want ARCH reading the devices LGWR is writing and vice versa -- you need like 5 devices -- arch reads 1/3 and writes 5 while lgwr writes 2/4, lgwr writes 1/3 while arch reads 2/4 and writes 5 and so on.

Make sure your log buffer is sized right.

Make sure your applications are not "over committing" -- comitting before they should (many people are under the 100% false impression that you want to commit ASAP for performance -- quite the OPPOSITE is true, the more you commit the slower you go and the more redo you generate)

How to recover the system tablespace?

Frank, November 28, 2002 - 12:18 pm UTC

Hi Tom,

If I lose my system tablespace, how can I recover it other than recover the whole database? Couldn't find an example from the online doc.

Many thanks.

Tom Kyte
November 29, 2002 - 9:21 am UTC

just restore the system datafiles from the last hot backup (or cold) and recover the database.

it is no different then if you lost "USERS" (except that the database will be down since system is sort of important to us)



Test of rename system datafile

Marcio, February 20, 2003 - 6:44 am UTC

Tom,

I read that you always did --
<quote>
alter database backup controlfile to trace
shutdown
move the files
edit the generated CREATE CONTROLFILE statement (found in the user dump
destination) and change the filenames
</quote>
I'd like to know if this test have any fails:

D:\ORADATA\MRP816>dir
Volume in drive D has no label.
Volume Serial Number is 6CC4-BCAF

Directory of D:\ORADATA\MRP816

07/02/2003 15:08 <DIR> .
07/02/2003 15:08 <DIR> ..
20/02/2003 08:19 113.254.400 USERS01.DBF
20/02/2003 08:19 60.825.600 INDX01.DBF
20/02/2003 08:19 164.896.768 SYSTEM01.DBF <----
20/02/2003 08:19 75.505.664 TEMP01.DBF
20/02/2003 08:19 12.591.104 TOOLS01.DBF
20/02/2003 08:19 209.723.392 RBS01.DBF
20/02/2003 08:20 3.661.824 CONTROL01.CTL
20/02/2003 08:20 3.661.824 CONTROL02.CTL
20/02/2003 08:20 3.661.824 CONTROL03.CTL
19/02/2003 08:59 1.049.088 REDO01.Log
20/02/2003 08:19 1.049.088 REDO02.Log
19/02/2003 08:59 1.049.088 REDO03.Log
14/02/2003 19:28 1.056.768 T01.Dbf

D:\ORADATA\MRP816>ren system01.dbf system.dbf
D:\ORADATA\MRP816>exit

ops$mportes@MRP816> startup mount
ORACLE instance started.

Total System Global Area 94360844 bytes
Fixed Size 70924 bytes
Variable Size 77434880 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
ops$mportes@MRP816> select rpad(name, 50) from v$datafile;

RPAD(NAME,50)
--------------------------------------------------
D:\ORADATA\MRP816\SYSTEM01.DBF
D:\ORADATA\MRP816\RBS01.DBF
D:\ORADATA\MRP816\USERS01.DBF
D:\ORADATA\MRP816\TEMP01.DBF
D:\ORADATA\MRP816\TOOLS01.DBF
D:\ORADATA\MRP816\INDX01.DBF
D:\ORADATA\SRE\DF_SRE01.DBF

7 rows selected.

ops$mportes@MRP816> alter database rename file 'D:\ORADATA\MRP816\SYSTEM01.DBF' to 'D:\ORADATA\MRP816\SYSTEM.DBF';

Database altered.

ops$mportes@MRP816> alter database open;

Database altered.

ops$mportes@MRP816> @conn /
ops$mportes@MRP816>
ops$mportes@MRP816>
ops$mportes@MRP816>
ops$mportes@MRP816> select rpad(name, 50) from v$datafile;

RPAD(NAME,50)
--------------------------------------------------
D:\ORADATA\MRP816\SYSTEM.DBF
D:\ORADATA\MRP816\RBS01.DBF
D:\ORADATA\MRP816\USERS01.DBF
D:\ORADATA\MRP816\TEMP01.DBF
D:\ORADATA\MRP816\TOOLS01.DBF
D:\ORADATA\MRP816\INDX01.DBF
D:\ORADATA\SRE\DF_SRE01.DBF

7 rows selected.

ops$mportes@MRP816> connect / as sysdba
Connected.
ops$mportes@MRP816> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[Here, I put in down the NT service and get up again] I did that to see any problems

ops$mportes@MRP816> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production

C:\tmp\sql>sqlplus /nolog

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Feb 20 08:28:53 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

idle> connect internal/oracle
Connected to an idle instance.
idle>
idle>
idle> startup
ORACLE instance started.

Total System Global Area 94360844 bytes
Fixed Size 70924 bytes
Variable Size 77434880 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
idle> @conn /
ops$mportes@MRP816>
ops$mportes@MRP816>

It's seem to me OK. Have you seem any problem with this method?

Tom Kyte
February 20, 2003 - 7:53 am UTC

that works as well.

But can we move datafiles from one d/b to another

A reader, June 26, 2003 - 12:38 pm UTC

Hi Tom:

On the discussion of moving datafiles. Is it possible to move datafiles belonging to a tablespace from one d/b to a new tablespace in another d/b and point to these datafiles that we copied ?

I know there I can use the TTS option of exp/imp. But other than that is there another way ?

Thanks


Tom Kyte
June 26, 2003 - 1:41 pm UTC

TTS is the only way to take part of DB1 and attach it to DB3

rename system datafile at db mount is better than re-create controlfile

A reader, June 26, 2003 - 4:20 pm UTC

I always like to use the method mentioned by Marcio from Brazil, when renaming/moving system datafile. A potential problem with Tom's re-create controlfile method is that it will wipe out any rman backup information stored in the controlfile, won't it?


Tom Kyte
June 26, 2003 - 4:40 pm UTC

rman information is typically stored in a rman repository isn't it.

it's still better to rename system datafile after mount db

A reader, June 26, 2003 - 5:13 pm UTC

Rman catalog is NOT typically used, in my experience. In 9i, it's even easier to simply use controlfile as the "default" rman repository.

No matter what -

shutdown db -> move system dbf -> startup mount -> rename system dbf -> open db

is better than

backup controlfile to trace -> shutdown db -> move system dbf -> edit controlfile trace backup -> start db with it

The latter process is more error prone, and before using that edited controlfile trace backup, you need to remove the old controlfile, unless you also edit the initialization parameter file (init.ora) to have a different controlfile location specified.

Tom Kyte
June 26, 2003 - 6:09 pm UTC

better be in the eye of the beholder and "not typically" used it "not typically my experience"

No matter what is an overstatement, like saying "never", or "always".

both ways work
both ways are correct
both ways are valid

what about this -- you have 500 datafiles.
they were all in /d01/...., /d02/...., /d03/..... and for whatever reason you have to move them to /u01/..., /u02/...., /u03/.....

Now, all of a sudden that control file trick with the ability to

:1,$s/\/d0/\/u0/

in vi (or editor of choice) looks more "appealing" maybe

everyone -- please use the method that pleases you the most.



if I had 500 dbf to rename, even more appealing to do it "inside the database"

A reader, June 26, 2003 - 8:20 pm UTC

To rename/move 500 datafiles from /d0 to /u0, how about:

(before shutdown and move datafiles)

spool rename.sql
select 'alter database rename file '''|| file_name ||''' to '''/u0/' || substr(file_name, 5) ||''';' from dba_data_files;
spool off

Then shutdown, move datafiles, startup mount, run rename.sql, alter database open.

No editing, no messing around with controlfiles, no loss of rman repository (if it's in the controlfile :-).

what if in UNIX

a reader, August 11, 2003 - 11:10 am UTC

Hi Tom,

what if to rename system datafile in unix environment? I think the 2 ways described above shoulg work without rebuilding whole database. However our production DBA said the Oracle database has to be rebuilt, otherwise the file links get messed up. Is it a true statement?

Tom Kyte
August 11, 2003 - 11:22 am UTC

they are wrong.


the above two methods both work dandy, do it all of the time.

move system datafiles

Billy L. Olson, August 23, 2004 - 3:44 pm UTC

The document was very good, I used it it in UNIX and it worked perfectly.

very useful indeed

Januar V. Simarmata, January 25, 2005 - 5:00 am UTC

This thread is very usefull to me, coz we are going to move our Oracle database to a new Disk Array for better performance. Thanks.

Dawar, June 24, 2005 - 12:36 pm UTC

Tom,

Database Version: 10.1.0.3.0

I need to relocate the non-system segments to a non-system tablespace.

For eg:

Schema: empbkup
Object name: employees
Tablespace : SYSTEM

I would like to changes tablespace from SYSTEM to ABC.

Regards,
Dawar


Tom Kyte
June 24, 2005 - 6:35 pm UTC

alter table move.
alter index rebuild.

this moving datafile discusion is good but....

Remko vd Peppel, February 13, 2006 - 10:21 am UTC

Better to change the script to make it work:

select 'alter database rename file '''|| file_name ||''' to '''/u0/' ||
substr(file_name, 5) ||''';' from dba_data_files;

to:

select 'alter database rename file '''|| file_name ||''' to '||'''/u0/' ||
substr(file_name, 5) ||''';' from dba_data_files;


How about this method to move datafile

Ramesh, July 22, 2010 - 3:19 am UTC

Database is in ARCHIVELOG mode:
-------------------------------

1. Take the datafile offline with the "ALTER DATABASE DATAFILE '/old/location' OFFLINE;" command.
2. Copy or move the datafile to its new location. On Unix this can be
done with the "dd" command. Example:
dd if=/old/location of=/new/location bs=4096
3. SVRMGR> ALTER DATABASE RENAME DATAFILE '/old/location' TO '/new/location';
4. SVRMGR> RECOVER DATAFILE '/new/location';
5. SVRMGR> ALTER DATABASE DATAFILE '/new/location' ONLINE;

Database is in NOARCHIVELOG mode:
---------------------------------

1. Shutdown the database
2. Copy or move the datafile to its new location. On Unix this can be
done with the "dd" command. Example:
dd if=/old/location of=/new/location bs=4096
3. Start Server Manager and do a "STARTUP MOUNT"
4. ALTER DATABASE RENAME DATAFILE '/old/location' TO '/new/location';
5. ALTER DATABASE OPEN;

Tom Kyte
July 23, 2010 - 9:09 am UTC

for no archivelog - just offline the tablespace, then you can offline the datafile, move it, rename it, online it all.

no need to shutdown.

Move Oracle Datafiles

Alex, September 20, 2018 - 11:15 am UTC

Move Oracle Datafiles in NOARCHIVE MODE

Stop the database:

SQL> shutdown immediate;

Copy the datafile to the new directory :

$ cp /u01/data/datafileD1.dbf /u02/data/datafilenew.dbf

Mount database

SQL> startup mount;

For Full Solution please visit below link
https://www.webhackmag.in/2018/09/move-oracle-datafiles-in-noarchive.html
Connor McDonald
September 21, 2018 - 12:55 am UTC

And in 12c and above, just move them online with no outage

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.