Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vinod.

Asked: January 30, 2005 - 9:12 am UTC

Last updated: December 17, 2015 - 1:26 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Tom,
Scenario:
Win 2000 , Oracle 8.1.6, No Archivelog
I need to move the datafile "G:SAARV2DATAVLMAIN01.dbf" to "L:SAARV2DATAVLMAIN01.dbf"
The steps that I am going to follow, is it correct. Since it is for the first time, bit worried. Please correct me if I am wrong.
Alter database backup controlfile;
Shutdown immediate;
Startup nomount;
Alter tablespace TS_VLMAIN rename datafile G:SAARV2DATAVLMAIN01.dbf to L:SAARV2DATAVLMAIN01.dbf;
Move G:SAARV2DATAVLMAIN01.dbf L:SAARV2DATAVLMAIN01.dbf
Alter database open;
Alter database backup controlfile;
Are these steps correct.
Rd/s
Vinod


and Tom said...

no, that is too much.

just

alter tablespace ts_vlmain offline;

host out and move the files

alter database rename file 'old_name' to 'new_name';
alter tablespace ts_vlmain online;



old_name and new_name are the fully qualified file names.

Then, backup your controlfile.

Rating

  (42 ratings)

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

Comments

Moving datafile (non system)

Vinod, February 06, 2005 - 3:56 am UTC

This has been very usefull tip. Have move the datafiles smoothly, but is there any way to move the system datafile in the same scenario. I know there is no way that one can offline the system tablespace. But then how can we move the system datafiles to different location.

Tom Kyte
February 06, 2005 - 4:16 am UTC

you can either 

a) rebuild the control files or...

b) 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
 
Total System Global Area  122754516 bytes
Fixed Size                   452052 bytes
Variable Size              88080384 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
 
SQL> !ls -l oradata/ora9ir2/system01.dbf
-rw-r--r--    1 ora9ir2  ora9ir2  828383232 Feb  6 04:12 oradata/ora9ir2/system01.dbf
 
SQL> !mv oradata/ora9ir2/system01.dbf oradata/ora9ir2/system.dbf
 
SQL> alter database rename file '/home/ora9ir2/oradata/ora9ir2/system01.dbf'
  2  to '/home/ora9ir2/oradata/ora9ir2/system.dbf';
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> select file_name from dba_data_files where tablespace_name = 'SYSTEM';
 
FILE_NAME
--------------------------------------------------------------------------------
/home/ora9ir2/oradata/ora9ir2/system.dbf



(b) is probably easier.  (a) is totally valid and works well when you want to "mass rename" the files in the database (you can edit the create control file system generated by alter database backup controlfile to trace) 

good for use

yakub ali khan, February 07, 2005 - 1:46 am UTC

this is really very nice indeed, i would to know one more answer, what is the command used to find out the second maximum salary of the employee in the company, plzz let me know this,

Tom Kyte
February 07, 2005 - 4:49 am UTC

plzz? what is that.


and what does the "second highest salary" have to do with moving a datafile?


if you have my book "expert one on one oracle", read the chapter on analytics.

!mv??

reader, February 08, 2005 - 9:52 am UTC

should'nt
!mv oradata/ora9ir2/system01.dbf oradata/ora9ir2/system.dbf

be

!cp oradata/ora9ir2/system01.dbf oradata/ora9ir2/system.dbf

instead?
i am not sure what flavor of unix/linx you are using..

Tom Kyte
February 09, 2005 - 1:19 am UTC

no, i was renaming the file from system01 to system

I didn't want a copy, i wanted a rename and mv is "rename"

still not clear

reader, February 09, 2005 - 10:14 am UTC

since you renamed the system file while the database is in mount mode, should'nt this be a problem as oracle dictionary still thinks the file name is system01.dbf as you have changed at the os level but not in the database yet?

am i missing something here?

thanks.

Answer to "reader from CT,USA "

Logan Palanisamy, February 09, 2005 - 1:54 pm UTC

When the DB is in mounted state, only the control file is open. None of the data files (*.dbf, including system*.dbf) are in use or open. You can move them or do whatever you want with them.

It is only when you open the database (alter database open), the dbf files are accessed.

Hope that answers your question.

Tom Kyte
February 09, 2005 - 3:43 pm UTC

thanks!!!

a reader

A reader, February 09, 2005 - 5:21 pm UTC

i just wanted to ask that the senerio which u gave at the top of this page (moving datafile) will also work with version 9i or not....Thanks.

alex

Tom Kyte
February 10, 2005 - 12:53 am UTC

"U" did not answer this question, I did. I've yet to meet this "u" person, but people seem to credit him/her alot.

Anyway - yes, 9i, 10g, 7.0 - yes, it works in all of the releases.

alex

alex, February 10, 2005 - 7:10 pm UTC

"U" is that great great intelligent Tom


Tom Kyte
February 11, 2005 - 7:52 pm UTC

why do they use "U"?

If they meant "me", they could use "Tom" or "you" -- "u" is, well, a letter in the alphabet, not a word, something you might use when chatting with a friend via SMS phone messages or instant messenger.

U :)

Nathan, February 15, 2005 - 5:55 am UTC

Tom,
Thanks for detailed options suggested.

People seem to get lazier by the day and want to use short forms for all the words ... SMS messaging and instant messaging probably would give change the way we right. But then the first to come up with such short words for various words are programmers when they started declaring shortened variables :)

Americans also changed spelling for colour and programme :)

Tom Kyte
February 15, 2005 - 3:25 pm UTC

and we use the letter Z when appropriate ;)



MOVE DATAFILES and DATAGUARD in 9i

Nathan, February 16, 2005 - 12:27 pm UTC

Tom,

I have a 9i Dataguard setup and Standby file management parameter is set to Auto .

I did this

ALTER TABLESPACE PERFSTAT_DAT READ ONLY;

*cp /export/rrobin/oradata/STBM/perfstat_dat01.dbf /export/data/oradata/STBM/perfstat_dat01.dbf

ALTER TABLESPACE PERFSTAT_DAT OFFLINE;


ALTER DATABASE RENAME FILE '/export/rrobin/oradata/STBM/perfstat_dat01.dbf' TO '/export/data/oradata/STBM/perfstat_dat01.dbf'


ALTER TABLESPACE PERFSTAT_DAT ONLINE;

ALTER TABLESPACE PERFSTAT_DAT READ WRITE;


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


rm /export/rrobin/oradata/STBM/perfstat_dat01.dbf



* could have used move but metalink note suggested cp so that we can compare the sizes before and after ? Would mv be faster ?

Now what I missed to consider or rather assumed was this changes would be reflected as I had set the Standby_file_management parameter but I cannot see this file on my standby machine even after the logs were applied .

What should I do now ?

As of now the Stanby is actually laggin behind the production by a few hours ( This is insert intensive system as we load 40 million rows every day )

Thanks

Tom Kyte
February 16, 2005 - 2:36 pm UTC

mv would be faster if you were moving the file inside the same "mount point" -- if /export/rrobin and /export/data were the "same disk" -- no move would be done, it would just be a rename.


the file won't "move" on the standby, were you expecting it to "move"? (the file mgmt stuff is for "new" files, files on the standby need not be named the same as on the production machine)

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/manage_ps.htm#1018710 <code>

Datguard and File move

Nathan, February 16, 2005 - 12:44 pm UTC


Sorry Tom,

I was in a hurry and sent a incomplete question. here is the additional information

Since cp / mv is a host command and oracle would have no knowledge of that this cannot happen on dataguard. my question is should i stop applying logs on standby issue and issue alter database rename commands on the dataguard and mv the datafiles involved and start applying the logs again

Anything else I should do ?

Regards
Nathan

Tom Kyte
February 16, 2005 - 2:38 pm UTC

see above.

cp and mv

Nathan, February 17, 2005 - 3:59 am UTC

Thanks Tom,
Cleared my doubts on cp and mv . I expected to see error messages after i moved the datafiles on production i.e I expected the Alter tablespace rename command to get reflected on the Standby but was puzzled when the recovery continued .

Then I stopped recoery and attempted to issue the rename command which failed since standby_file_management = Auto . I then changed it to manual issued the rename command and then proceeded set it back again and started log application again .

Thanks Tom .



mv Vs cp

John, March 29, 2005 - 11:16 am UTC

I always felt it best to use 'cp' instead of 'mv' when you
can
1. Afford the disk space to hold 2 copies of the file
2. Do not have sufficient knowledge of the underlying disk architecture.

When a mv is issued to physically copy files across disks , There are risks involved. Any opinions ?



Tom Kyte
March 29, 2005 - 11:20 am UTC

what risks? a mv on a single volume is "rename directory entry"

across volumes it is a cp (to move) + unlink (to remove old copy)

the unlink happens after the successful cp.

file moved to different folder, but "df -k ." still says 100% used.

Sanjay Dholakiya, March 30, 2005 - 1:40 pm UTC

I followed exact same instructions mentioned in this thread.
1. took tablespace offline
2. moved datafile using !mv command
3. renamed old file to new file
4. brought back tablespace online.

after moving 4.5 GB size datafile to different mount on different hard disk, when I run "df -k ." from old file system, I get 100% used, I was expecting it to go down.

I am running database 10g version on Redhat Linux AS 3.0, any idea what could be the reason for not reflecting the file move in "df -k ." command ?

thanks in advance.

Tom Kyte
March 30, 2005 - 1:58 pm UTC

someone/something still has it open. do you have lsof?


[root@xtkyte-pc root]# lsof -l /home/ora9ir2/oradata//ora9ir2/control01.ctl
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
oracle 26369 502 16u REG 3,2 1630208 2245358 /home/ora9ir2/oradata//ora9ir2/control01.ctl
oracle 26371 502 16u REG 3,2 1630208 2245358 /home/ora9ir2/oradata//ora9ir2/control01.ctl
oracle 26373 502 16uW REG 3,2 1630208 2245358 /home/ora9ir2/oradata//ora9ir2/control01.ctl
oracle 26385 502 16u REG 3,2 1630208 2245358 /home/ora9ir2/oradata//ora9ir2/control01.ctl
oracle 26387 502 16u REG 3,2 1630208 2245358 /home/ora9ir2/oradata//ora9ir2/control01.ctl


yes, you are right !

Sanjay Dholakiya, March 30, 2005 - 4:58 pm UTC

using lsof command, I saw that one of the connection was (somehow) using this file. I bounced the database and that corrected the problem.

Thanks.

- Sanjay

how do I know if a tablespace is empty?

jim, March 31, 2005 - 2:15 pm UTC

Tom:

I need to developer a scirpt to drop all empty tablespaces and its related files.

How do i know if a tablespace is empty?

Currently I am checking to see if there is an entry in dba_segment, if not, then I think the tablespace is empty.
Is this check good enough?


Tom Kyte
March 31, 2005 - 3:10 pm UTC

sure, and if you just "drop tablespace t" and it isn't empty, it'll fail.


seems funny to have to script this though (funny as in "doesn't feel right")

another quesiton

jim, March 31, 2005 - 3:27 pm UTC

Thanks alot for your prompt response,
It is amazingly fast. :-)

I know that "drop tablespace my_tbs" will do the trick.
but we want to drop the related datafile as well.

Ideally if oracle provide
drop tablespace my_tbs including datafiles
That would be great, but I know this is reality.

Q1)so you think dba_segment is good enough?
Q2)do i need to worry that some times in the future, oracle might introduce some content in a tablespace that is not recorded in dba_segment? if not, then i will go ahead provide the script


Tom Kyte
March 31, 2005 - 3:52 pm UTC

well, it does if you have "with contents and datafiles"

but it makes is more "dicey", you had better be sure there is nothing in there you want. DBA_SEGMENTS should get that for you. I cannot think of a thing that would not be registered in there. truly paraniod, query dba_extents -- but I don't think that is necessary.

drop tablespace

Greg, March 31, 2005 - 4:56 pm UTC

Tom - I was testing your drop tablespace theory, and found:

 1* create tablespace x datafile '/tmp/xxx.dbf' size 10m
SQL> /
Tablespace created.

SQL> drop tablespace x with contents and datafiles;
drop tablespace x with contents and datafiles
                  *
ERROR at line 1:
ORA-02173: invalid option for DROP TABLESPACE

So I checked with Don B., and he said that in his experience over the years, he has found that:
SQL> drop tablespace x including contents and datafiles;

Tablespace dropped.

is yet another silver bullet.    
:-)

sorry - couldn't resist

 

Tom Kyte
March 31, 2005 - 5:21 pm UTC

whoops "including" ;)



now it is time to create tablespace

jim, April 01, 2005 - 1:00 pm UTC

Tom, as part of the script, I need to create some tablespace as well, the size can go up to more than 100G.
and the box have plenty of CPU and memory.
Question: how to write the scirpt to generate those tablespace as quick as possible?

my first try:
create tablespace in sequencial. meaning:
1) create tablespace tbs1
2) create tablespace tbs2
.....

my second try:
open a new sqlplus windown or database connection, whatever,
create tablespace tbs1.
while the first session is running, open another sqlplus window, issue create tablespace tbs2. etc.

but it seems the speed is about the same, any idea?
how can we achieve the parallel of the creation?

Tom Kyte
April 01, 2005 - 1:10 pm UTC

create tablespace is "serial"

so, if you want to maximize the create tablespace, what you do is create a bunch of tablespaces (tbs1,tbs2,...) with small files, that'll create fast.

Then either alter into them in parallel to add more "big" files, or resize their files in parallel.

(by in parallel i mean by using multiple sqlplus sessions or dbms_job)

How about online?

Invisible, April 05, 2005 - 5:44 am UTC

I have no need to do this, but I was wondering if it can be done...

Is it possible to move the contents of a particular tablespace while people are still using it? (e.g., you just installed a new HD, and you want to move the DB files off the disk they're on to reduce I/O contention or something.)

You don't actually need to know what tablespace an object is in to access it, do you? Best thing I can think of is to create another tablespace on the new disk, and ask Oracle to move all the tables, etc. into this new tablespace. (There is a command to do that I take it? Think I remember seeing it here somewhere...)

As far as I can tell, you're never ever going to manage to move the SYSTEM tablespace without a shutdown though. ;-)


Tom Kyte
April 05, 2005 - 7:34 am UTC

dbms_redefinition, an online reorg tool.

see the plsql supplied packages guide and the admin guide

search for dbms_redefinition on this site for examples.


system is not moving period.

Didnt worked

Asim Naveed, August 08, 2005 - 5:19 am UTC

Please refer to your first followup in this discussion
which start as.....

you can either 

a) rebuild the control files or...

b) 

SQL> shutdown immediate;
Database closed..............................

I did the method (b) on my Oracle 9i Rel. 2 database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Now on OS prompt
C:\>copy c:\data1.dbf d:\data1.dbf

Then

SQL> startup mount
ORACLE instance started.
 
Total System Global Area  122754516 bytes
Fixed Size                   452052 bytes
Variable Size              88080384 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
 
SQL> alter database rename file 'C:\data1.dbf'
  2  to 'D:\data1.dbf';
 
Database altered.

<-------------Now when I say -------------->
 
SQL> alter database open;
 
It gives me
ORA-01113: file needs media recovery 


The only difference in your example and mine is that 
my rename involves hard drive change and your rename
is on the same hard drive. Is this the reason of error?

Thanks 

Tom Kyte
August 08, 2005 - 8:27 am UTC

are you sure you did shutdown immediate. and that the copy worked.

are c:data1.dbf and d:data1.dbf "the same"

Moving of datafile

Kumar, October 06, 2005 - 12:43 am UTC

Scenario:
Win 2000 Server , Oracle 9.2.0.6, Archivelog
I have OS disk space issue on my primary DB in one of the drives (449 mb) which contain most of my datafiles. I plan to move one big datafile from the existing drive to another drive which has space.
I plan to take the following steps:

1. Backup the controlfile.
2. Shutdown the database (primary and secondary)
3. Move the datafile to new location. (in both primary and secondary)
4. Startup nomount (in both)
5. alter database rename file 'old_path' to 'new_path';
6. alter database open
7. Backup Controlfile
8. Take cold backup.

Or on the primary,can I put the tablespace offline,
move the datafile,
rename the datafile and
put the tablespace online
and on the secondary db, cancel the recovery,
shutdown the secondary,
move the datafile,
startup in mount standby db
alter db rename file 'old path' to 'new path'
Recover the secondary

The application is mission critical and if I put the tablespace offline, what could be the repercussion ?

Since, I am doing it for the first time, I would like your suggestion on the above.





Tom Kyte
October 06, 2005 - 7:43 am UTC

taking the tablespace offline prevents reads and writes of it, nothnig more. I would not shutdown just to do this and the cold backup is not any better than a hot backup - in fact, it is "worse" since it is cold and you have to shutdown.




what for control files and online redologfiles

Asim Naveed, February 11, 2006 - 11:48 am UTC

As you described the procedure for
renaming/moving the datafiles(system and non system)in this
thread, can you please describe the same for renaming/moving
redolog and controlfiles using an example.

Thanks and Regards,

Asim Naveed.

Tom Kyte
February 11, 2006 - 2:39 pm UTC

you would not really rename redolog files, you would just

a) create new ones
b) drop old ones


for controlfiles, you would

a) shutdown
b) move/copy them
c) update your init.ora or spfile
d) open the database again.

Some confirmations

A reader, February 12, 2006 - 4:47 am UTC

A)
Can you please describe the procedure
for creating new online redolog files and
droping old online redolog files. Is there a
command like DROP REDOLOGFILE <filename>.


B)
Please confirm the correctness of the
following procedure for
renaming/moving online REDOLOG files.

1) On SQL prompt:-
Shutdown Immediate;
2) On operating system prompt:-
$ mv /disk1/redolog1.log /disk2/redolog1.log

3) On sql prompt:-
startup mount;
ALTER DATABASE RENAME file '/disk1/redolog1.log' to '/disk2/redolog1.log';
ALTER database open;


C)
Please confirm the statement:-

When the database is in mounted state i.e.

shuddown immediate;
startup mount;

we can rename any file associated with any tablespace
using the ALTER DATABASE RENAME FILE, even if the file is
associated with tablespaces such as SYSTEM tablespace, UNDO tablespace, TEMPORARY tablespace etc.








Tom Kyte
February 12, 2006 - 11:33 am UTC

a)
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:55812348055

alter database command...

---------------------------------------

b) 

1) no, you don't have to shutdown.
2) no, you don't have to move them.
3) no, you don't have to rename them


You want to have the redo on disk2?  Just 

1) create it there
2) drop the old ones

there is no need to shutdown for something like this.  Since in order to do a "move" of a file, you need to have two copies of it for a moment, there is quite simply nothing to be gained from this - just create NEW, remove OLD.
---------------------------

c) 

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1004.htm#sthref3491 <code>

documentation can confirm that.... Yes. It can rename datafiles, tempfiles, redo log files.

Why am I backing up the control file?

James, March 06, 2006 - 2:21 am UTC

Hi Tom!!!!

You said:

-------------------------------------

alter tablespace ts_vlmain offline;
host out and move the files
alter database rename file 'old_name' to 'new_name';
alter tablespace ts_vlmain online;

***Then, backup your controlfile.***

------------------------------------------

Why am I backing up my control file now? After I have made the changes? Wouldn't it be better to backup the control file before I started making changes like this?

Is it good to just back it up in case nothing else is backing it up? Just what am I protecting myself from here?

Kind Regards, I do not understand.






Tom Kyte
March 06, 2006 - 8:38 am UTC

You need a backup of a control file after making structural changes.

One would assume you have a backup from before.
After a structural change, you need a new backup (in case you lose them, you need the ones that represent how the database looks NOW, not then)

Moving Tablespace to Network Storage

Scott Horowitz, April 27, 2006 - 11:42 am UTC

Hi Tom,
We are running 10gR1 with an ASM storage solution currently. We are nearing maximum capacity on this drive because we load PDFs into the database and store as LOBs. My network admin recently purchased a Network Assisted Storage solution. My idea was to move the datafile for this tablespace to this network solution. My network admin had the following reservations: Downtime of the solution, network latency, and high access to the device. Also, how oracle will react if the network drive goes down and cannot locate the datafile.

I just want your opinion on this and to know if you know of any databases that work with this type of solution.

Thanks,
Scott

Tom Kyte
April 27, 2006 - 3:25 pm UTC

is this a disk configuration we certify and support?

if so, you move nothing, you add the new disk to the disk group (we rebalance)
you drop the old disk from the disk group (we finish moving)

you are done, data moved, no downtime.


How do you think a database will react upon finding the disk it wants isn't there? It'll offline it - until you fix the problem :)

lsof after moving a datafile

Julius, July 31, 2006 - 2:35 pm UTC

Followup to the move datafile/lsof question
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:32939838390113#37651247824226 <code>

How do you find out who has the datafile open after moving it? I'm having the same issue here (i.e. the disk space is not release back to the os), but the old file has been moved and does not exist anymore, so lsof is not of much help. Thanks!


Tom Kyte
July 31, 2006 - 3:01 pm UTC

are you sure lsof isn't of help? it doesn't show the old path?

Julius, July 31, 2006 - 3:19 pm UTC

Since the original file does not exist anymore, lsof is giving me "No such file or directory" :( Btw, bouncing the database released the disk space, so the problem is gone. But I'm still curious if there is a way to do it without a db restart. Maybe instead of moving I could copy, that way I should be able to use lsof to check for processes reading the original file ...

Tom Kyte
July 31, 2006 - 8:48 pm UTC

next time just try "lsof" and get the entire output - should list all open files by all processes.

move database files

Dawar Naqvi, November 01, 2006 - 4:26 pm UTC

Tom,

I installed Oracle 10.2. on Unix.
we have created file systems for our data/control files.

My question is how to move all control files, redo.log and dbf files to one location to another.

for. eg:
currently there are installed in /opt/oracle/oradata.

now i want to move in /u02/oradata.

I also want to move flashback to /u03.

Please note:
Prior to that I'd like to put DB in archive log mode as below.

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Dawar


Tom Kyte
November 01, 2006 - 6:35 pm UTC

to move redo - create new redo in new location, drop old redo

to move datafiles - that was the crux of the above question, see that.

to move control files - shutdown, move them, update your init.ora to point to them, startup

create new redo

Dawar, November 06, 2006 - 3:41 pm UTC

YOU SAID:

to move redo - create new redo in new location, drop old redo


How to create new redo in new location?


cheers,
Dawar


Tom Kyte
November 06, 2006 - 3:44 pm UTC

the same way you created your redo in the current position. Your DBA will know immediately the alter database command to use.

lsof and unlinked files

Greg Norris, November 10, 2006 - 1:45 pm UTC

<quote>
next time just try "lsof" and get the entire output - should list all open files by all processes.
</quote>

In the specific case of unlinked files, "lsof +L1" can be a very useful variant. It shows only deleted files, and identifies the original filename along with the process using it. Very handy! :)


Tom Kyte
November 10, 2006 - 2:56 pm UTC

thanks, appreciate that!

move database files

Dawar Naqvi, November 15, 2006 - 12:07 pm UTC

Tom,

Could you recommend to use Enterprise manager console to move data files, control files and redo log files from one directory to another?

e.: move files from /u01/app/oracle/abc to /u02/oradata

cheers,
Dawar

Tom Kyte
November 16, 2006 - 3:58 am UTC

this is something you would typically do at the OS level, moving files around.

you would

a) offline the tablespaces
b) move the datafiles, rename the datafiles using alter database
c) you would create new redo log files in new location, then drop old
d) you would shutdown, move the control files, update the init.ora and restart.

moving control files when using spfile

abz, December 20, 2006 - 5:59 am UTC

I have oracle 10g R2. I am NOT using init.ora file,
instead I am using spfile.

What is the procedure for moving control files in this
case. Please note that I have alot of parameters with
non default values in my spfile, so if I CREATE the spfile
from some init.ora file, all changes will be lost.




Tom Kyte
December 20, 2006 - 8:34 am UTC

create pfile from spfile
edit it
startup with pfile
create spfile from pfile




addition to above

abz, December 20, 2006 - 6:01 am UTC

... and we all know that spfile is not a text file that
can be edited in any text editor. So we cannot edit the
spfile and change the paths of control file parameter.

Another question:- can we create an init.ora file from
an spfile?

pfile

abz, December 20, 2006 - 1:10 pm UTC

when I
create pfile from spfile in 10g R2.

the init.ora file created is also not editable.


Tom Kyte
December 20, 2006 - 1:23 pm UTC

yes it is.

how about this.

abz, December 20, 2006 - 2:15 pm UTC

Ok how about this procedure.

1- ALTER SYSTEM SET CONTROL_FILES=<new file location>
SCOPE=SPFILE
2- SHUTDOWN IMMEDIATE;
3- MOVE/COPY THE CONTROL FILES TO <new file location>
4- STARTUP



Tom Kyte
December 20, 2006 - 2:18 pm UTC

that'll do it as well.

init ora file

abz, February 02, 2007 - 5:01 am UTC

Here is some cut/paste from my init.ora file. You can see
each line is starting from '*' character, same can be
seen in spfile also. The init.ora file was supposed to be
simple text.

*.audit_file_dest='/splfs1/admin/cdxlive/adump'
*.background_dump_dest='/splfs1/admin/cdxlive/bdump'
*.compatible='10.2.0.2.0'
*.control_files='/data5/cdxlive/control_files/control01.ctl','/data5/cdxlive/control_files/control02.ctl','/data5/cdxlive/control_files/control03.ctl'
*.core_dump_dest='/splfs1/admin/cdxlive/cdump'
*.db_block_size=8192

Tom Kyte
February 02, 2007 - 10:49 am UTC

so? do you have a question or issue? the *. is perfectly fine.

<quote src=Expert Oracle Database Architecture>
...
In a clustered environment, in order to convert from using individual PFILEs to a common SPFILE shared by all, you would merge your individual PFILEs into a single file resembling this:


*.cluster_database_instances=2
*.cluster_database=TRUE
*.cluster_interconnects='10.10.10.0'
*.compatible='10.1.0.2.0'
*.control_files='/ocfs/O10G/control01.ctl','/ocfs/O10G/control02.ctl'
*.db_name='O10G'
¿...
*.processes=150
*.undo_management='AUTO'
O10G1.instance_number=1
O10G2.instance_number=2
O10G1.local_listener='LISTENER_O10G1'
O10G2.local_listener='LISTENER_O10G2'
O10G1.remote_listener='LISTENER_O10G2'
O10G2.remote_listener='LISTENER_O10G1'
O10G1.thread=1
O10G2.thread=2
O10G1.undo_tablespace='UNDOTBS1'
O10G2.undo_tablespace='UNDOTBS2'


That is, parameter settings that are common to all instances in the cluster would start with *.. Parameter settings that are specific to a single instance, such as the INSTANCE_NUMBER and the THREAD of redo to be used, are prefixed with the instance name (the Oracle SID). In the preceding example,

* The PFILE would be for a two-node cluster with instances named O10G1 and O10G2.
* The *.db_name = 'O10G assignment indicates that all instances using this SPFILE will be mounting a database named O10G.
* O10G1.undo_tablespace='UNDOTBS1' indicates that the instance named O10G1 will use that specific undo tablespace, and so on.
</quote>

lsof and process kill

pkshr, February 27, 2008 - 9:16 am UTC

Tom,

You have mentioned to check the open file by using "lsof" - what do you suggest in case where the same process is shared by other files too? e.g while moving or deleting tablespace including contents and datafiles in R2 the file will be deleted from OS but the space is not released - can be seen by "lsof ". The process number seen for such is same with others too and it is used by oracle. Cross checking the process in oracle's session (v$session, v$process) if we see it's still active - can we kill the process?

I face same situation and see the session is in "jobq slave wait"...


Tom Kyte
February 27, 2008 - 11:22 am UTC

it is a job queue process, it is not safe to kill Oracle background processes - no.

Alexander, June 19, 2012 - 12:52 pm UTC

Can you recommend the easiest way to move a database from internal cooked file-systems to SAN cooked file-systems? Would I have to get new SAN fs's created and then rename the datafiles?
Tom Kyte
June 19, 2012 - 1:06 pm UTC

there is no difference to us, the real question is:

how can I move a file from one directory to another.


that is rather easy - if not offline without ASM.

just offline the tablespace
move the datafile(s)
alter the database and rename the file(s)
online the tablespace

system is the only tricky one - that has to be done when the database is shutdown.


Alexander, June 19, 2012 - 1:24 pm UTC

For the sake of keeping our fs naming standards I was investigating what it would take to "move" a file-system from one type of storage to another. A brief chat with a sys admin said it would be possible to create new fs's, copy the files, and then move the mount point. I'm sure there are many ways to accomplish this. Would that be valid also? This isn't production, downtime is not a factor.
Tom Kyte
June 19, 2012 - 3:03 pm UTC

sure, it would be trivial.


say you want to move all of the files from /mnt/old to a new storage area.

mount the new storage area as something like /a_new_mount/tmp

shutdown your database.

copy everything from /mnt/old to /a_new_mount/tmp

unmout /mnt/old and /a_new_mount/tmp

mount the new storage as /mnt/old

startup.

Rename/move in Windows

Jeff L, August 01, 2012 - 3:06 pm UTC

Hi Tom,

I'm running Oracle on Windows and would like to move individual (non-system) datafiles around without shutting down the database. I see many references to doing so in this thread but all comments assume the files aren't locked when it comes time to move the files.

Every time I've tried to leave the database open while moving the datafiles, however, I get an error stating that the files can't be moved because they are in use. The only way that I've been successful is if I shutdown the database. Because of this, it appears that Oracle is maintaining locks on the files as long as the database is open even if the datafiles are offline.

Here's the sequence of commands that I try:

alter database datafile 'path\name' offline;
<Move datafiles>
alter database rename file 'oldpath\oldname'
TO 'newpath\newname';
alter database datafile 'path\name' online;

The <Move datafiles> always fails because they're in use.

How do I get Oracle to release locks on offline datafiles so I can move them?

Thanks!

Jeff L
Tom Kyte
August 01, 2012 - 3:48 pm UTC

It is not that Oracle has locks on the files, it is the way windows works. and since ever thing is a thread, not a process it makes it not really possible to track down the culprit and see if they can be killed.


I don't have a good answer for you - or even a bad one. I don't use windows as a server platform myself, I don't have the hands on with it.

from what I've read - it should be that in 11.1 and above - all of the file handles will be closed for a drop tablespace including contents and datafiles. I'm not sure if that affects an offline tablespace command as well.

Moving Temp Tablespace

Foqia Khan, October 29, 2013 - 5:38 pm UTC

Tom,

I am not sure if you'll see it as a new question or related to the same but please do reply:

Is it possible to move temp tablespace using the guidelines you mentioned above for other data files!

Regards,
Foqia
Tom Kyte
November 01, 2013 - 9:14 pm UTC

just create a new one, reassign people to it, and drop the old one when it is no longer being used. easier than anything else and no downtime required.

you'd need to kick everyone out to "move" them.

how to find offline data file size in oracle 11g

SantoshKumar V K, December 16, 2015 - 10:44 am UTC

Please help me how to find offline data file size in oracle 11g
1. I created tablespace and given size
2. i made it offline
3. i do not know how much size i given to that datafile
4. i want to see the size of the datafile

i am unable to see datafile of size.

kindly suggest me how do i view the offline data file size in oracle 11g

Regards,
Santosh v k



Connor McDonald
December 17, 2015 - 1:26 am UTC

SQL> create tablespace TS datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' size 20m;

Tablespace created.

SQL> alter tablespace TS offline;

Tablespace altered.

SQL> select bytes from dba_data_files where file_name = 'C:\ORACLE\ORADATA\NP12\TS.DBF';

     BYTES
----------


SQL> create or replace directory DF as 'C:\ORACLE\ORADATA\NP12';

Directory created.

SQL> set serverout on
SQL> declare
  2    b bfile := bfilename('DF','TS.DBF');
  3  begin
  4    dbms_output.put_line(dbms_lob.getlength(b));
  5  end;
  6  /
20979712

PL/SQL procedure successfully completed.


More to Explore

Backup/Recovery

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