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.
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,
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..
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.
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
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
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 :)
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
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
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 ?
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.
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?
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
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
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?
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. ;-)
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
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.
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.
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.
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.
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
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
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 ...
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
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
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! :)
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
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.
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.
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
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
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"...
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?
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.
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
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
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
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.