Or simply
Alexandre, March 31, 2003 - 8:24 am UTC
database mounted...
alter database rename file 'file_old_dir_and_old_name' to 'file_new_dir_and_new_name';
database opened...
March 31, 2003 - 9:18 am UTC
simply -- not if you have to mass rename all of the files which is what I assumed they were doing. then it would be easier in my opinion to create the control files -- what about log files as well?
yes, that would work for the test case I provided.
No matter
Alexandre, March 31, 2003 - 3:37 pm UTC
According Tony´s problem...
Quotes
Last week , I added a disk in the system, and by some reason by E drive
containing Oracle DB became F, and new drive became E
Quotes
and even there were "a thousand" of files...
SIMPLY
database mounted...
select 'alter database rename file '||chr(39)||name||chr(39)||' to '||chr(39)||'F'||substr(name,2)||chr(39)||';'
from v$datafile;
database opened...
for redologs, repeat the same query above just replacing name for member and v$datafile for v$logfile...
Cheers,
Alexandre
March 31, 2003 - 3:41 pm UTC
to each their own -- me, I've always prefered the control file route and a global change in vi... sometimes it's easier then sql...
Datafile
Tony, March 31, 2003 - 3:55 pm UTC
I think the guy's solution is more reasonable, simple, clean -- on the fly. Thanks though.
Alex, March 31, 2003 - 5:09 pm UTC
Tony, you missed the point.
1. "Backup controlfile to trace" must be your routine procedure.
2. Instead of generating several scripts, you have only one for simple search/replace
3. You have ?text? copy of your control files, which you can use for tons of situations.
4. As always in programming/troubleshooting there are many ways to do the thing, but
few to do that optimal way, with deep vision.
Reply to Alex - USA
Alexandre, April 01, 2003 - 11:39 am UTC
Dear Alex,
Our friend Tony is not missing the point...
Quotes
1. "Backup controlfile to trace" must be your routine procedure.
Quotes
I do agree...
Quotes
2. Instead of generating several scripts, you have only one for simple
search/replace
Quotes
Several scripts ??? What several scripts ??? My solution was resumed to a simple query...
Quotes
3. You have "text" copy of your control files, which you can use for tons of
situations.
Quotes
I agree too... but if the situation does not claim to rebuild controlfile, why am I going to rebuild it, by the way, the simple query I´ve suggested, internally works renaming the datafiles inside the controlfile without rebuilding it manually... There are "tons" of information inside the controlfile that Tony would not like very much to loose, for example, RMAN historical data... if he uses it...
Quotes
4. As always in programming/troubleshooting there are many ways to do the thing,
but few to do that optimal way, with deep vision.
Quotes
Optimal way ???
Deep Vision ??? Sounds a bit "apple polisher" stuff...
I don´t know if this matches Tony´s case, but what if Tony were doing backups with RMAN without catalog and his database crashes right after recreating the controlfile ??? Hummmmmmm... Your several scripts to recover ... caused by "recreating controlfile"...
Tom, thanks for all,
Alexandre
A reader, May 18, 2003 - 12:11 am UTC
It seems that if your have log files too on "new" volume, then you need to run two "select" statements.
It is good practice to keep historical archive of control files, just for simple journaling ( Oh! on that day I moved these files).
Without the recovery catalog in place however, these RMAN features are not available:
* Tablespace point-in-time recovery
* Stored scripts
* Recovery when the control file is lost or damaged
That is why Oracle strongly recommends that you use the recovery catalog (good bloody practice too).
Rename Datafiles
Evan, October 18, 2003 - 9:34 pm UTC
I needed a one page description of how to do it. Tahiti had the syntax, but Tom gave me the "warm and fuzzy". You always have an intelligent way and a good reason to do it that way.
Thanks, Tom
Renaming datafile while the database is open
Himnish Narang, July 01, 2004 - 6:42 am UTC
Dear Tom,
Is there any method by which we can rename a datafile while the database is in open status.
thanx for your time.
Himnish
July 01, 2004 - 11:03 am UTC
such 'maintenace' is an offline operation:
ops$tkyte@ORA9IR2> alter database rename file '/tmp/t.dbf' to '/tmp/tt.dbf';
alter database rename file '/tmp/t.dbf' to '/tmp/tt.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 17 - file is in use or recovery
ORA-01110: data file 17: '/tmp/t.dbf'
Thriu, July 15, 2004 - 6:14 am UTC
Hi Tom,
If you can pleaase go through the steps for renaming a db using spfile.
Is this correct:
a. alter database backup controlfile to trace;
b. modify init.ora : db_name and instance_name to new name.
c. create spfile from pfile;
d. shutdowm immediate.
e. Create new password file using new sid. orapwd..
f. Go to tracefile and change the reqd lines.
g. conn as sysdba and run the controlfile script
h. alter database open.
i. Check db_name from v$database and sid from v$thread.
Thanks
July 15, 2004 - 12:33 pm UTC
Datafile offline still in use
jp, May 04, 2005 - 7:19 am UTC
Dear Tom,
I have a database in archivelog mode, and I am trying to rename a datafile,
If I am correct steps are
1. Datafile offline ( alter database datafile .. offline)
2. Rename the file ( Rename the file in os )
3. Modify the controlfile (alter database rename file..)
4. Recover datafile ( recover datafile ...)
5. Datafile online ( alter database datafile ... online)
I put the databafile offline, but when I tried to rename it, I am still getting 'file is in use'. am I missing something?
thanks
May 04, 2005 - 9:40 am UTC
1) alter TABLESPACE_CONTAINING_DATAFILE offline;
2) rename the file
3) ALTER DATABASE to rename the file
4) alter TABLESPACE_CONTAINING_DATAFILE online;
done. take the tablespace offline, rename file, alter database to update the controlfile, bring tablespace back on line. no recovery or anything like that.
but if this is windows, windows is strange, you might
sys@ORA10G> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10G> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\tkyte>rename c:\oracle\oradata\ora10g\USERS01.DBF USERS.DBF
C:\Documents and Settings\tkyte>sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.3.0 - Production on Wed May 4 09:33:02 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup mount
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 788968 bytes
Variable Size 296744472 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
idle> alter database rename file 'c:\oracle\oradata\ora10g\USERS01.DBF' to 'c:\oracle\oradata\ora10g\USERS.DBF';
Database altered.
idle> alter database open;
Database altered.
windows vs Unix
jp, May 06, 2005 - 2:12 pm UTC
that means that because of windows, I cannot rename the datafile on offline mode, if I am on Unix I dont need to shutdown the database to rename the datafile?
thanks for your help
May 06, 2005 - 5:07 pm UTC
I don't work with windows enough to definitively answer that.
But on unix, you definitely can.
I just did a
o offline tablespace
o exit sqlplus
o renamed file
o went back in and renamed the file with alter database
o brought tablespace back online
we have to wait for all threads to close the file in windows - it could have just been my session that had it open still and simply exiting close it.
tkyte@ORA10G> alter tablespace users offline;
Tablespace altered.
tkyte@ORA10G> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\tkyte>rename c:\oracle\oradata\ora10g\users01.dbf users.dbf
C:\Documents and Settings\tkyte>plus
C:\Documents and Settings\tkyte>sqlplus tkyte/tkyte @z:\stuff\sqlstuff\login.sql
SQL*Plus: Release 10.1.0.3.0 - Production on Fri May 6 17:05:28 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
tkyte@ORA10G> alter database rename file 'c:\oracle\oradata\ora10g\USERS01.DBF' to 'c:\oracle\oradata\ora10g\USERS.DBF';
Database altered.
control file re-creation
whizkid, May 07, 2005 - 11:24 am UTC
<Quote from Alexandre post>
I agree too... but if the situation does not claim to rebuild controlfile, why am I going to rebuild it, by the way, the simple query I´ve suggested, internally works renaming the datafiles inside the controlfile without
rebuilding it manually... There are "tons" of information inside the controlfile that Tony would not like very much to loose, for example, RMAN historical data... if he uses it...
</Quote>
This I had never realised. If we do not use RMAN catalog, all the RMAN information is stored in the control files. So if we re-create the control files, all the RMAN information is lost. Why doesn't Oracle dump the information when we do a control file to trace? So this means that all the previous RMAN backup's are rendered useless?
May 07, 2005 - 11:29 am UTC
because the 'to trace' simply builds a create controlfile statement, there aren't commmand to be run to put that stuff back in.
the backups are not rendered useless -- the rman guides show you how to make use of them, it is just more manual, you have less recorded information.
I agree, if you are using rman and relying on the controlfiles, don't rebuild them.
control file contents
whizkid, May 07, 2005 - 11:54 am UTC
Was just reading the docs for the contents of control file. There's lots of information which we can lose in case we re-create the control file. Understand that the information not appearing in backup control file to trace cant be generated through command, but just out curiousity, is there any way to read the control file using any binary editor? Would of course make a copy of it and read from that copy.
May 07, 2005 - 11:58 am UTC
most of the controlfile information is exposed via v$ views.
binary to ascii format - unix
whizkid, May 07, 2005 - 12:09 pm UTC
this is not related to the original question or any of the follow-ups but when I spoke of binary this question came up in my mind. you are open for new questions now but this is not worth a new question so i'm asking it here. basically a unix os question.
when we ftp a ascii file from a windows to aix box in binary mode it appends ^M at the end of every line.
for eg:
------
create or replace procedure sync_all_ctx_indexes^M
as^M
l_err long;^M
begin^M
--------
this does not happen in linux. is there a way to convert this binary file to ascii? or do we have to remove the ^M from all lines manually?
Thanks!
May 07, 2005 - 12:17 pm UTC
it isn't appending anything -- it is leaving it be.
On windows, the end of line is chr(13)||chr(10) (carriage return, linefeed)
On not windows, it is just linefeed.
the ^M is the carriage return.
if you want them not to be there, you don't binary ftp, that is what ftp does -- converts CR/LF -> LF from windows -> unix and LF -> CR/LF from unix to windows!
Oh......
whizkid, May 07, 2005 - 12:25 pm UTC
Ok.. but this does not happen in Linux?? The problem is that this file is not properly read (we ftpd the hosts file in this format and suddenly we had network issues becoz it was unable to resolve the hostname). ftp again in non-bi format is the final solution but just wondering whether i can remove that using any vi commands
:%s/^M//g
the above does not match any patterns. if we go to the end of line and delete the character (x in vi), it deletes it. sorry to take your time in trival issues.
May 07, 2005 - 12:29 pm UTC
it does and will happen in linux, if you binary ftp a TEXT file from windows that has cr/lf in it to linux, it'll have the same issue (i see it all of the time)
in vi, you would:
:1,$s/^V^M//g
that is
colon 1 comma dollar sign s / control-v ENTER //g
^^^^^^^^^ one keystroke -- ctl-v
^^^^^^^^^ one keystroke -- hit ENTER
generally works.
Genius!!
whizkid, May 07, 2005 - 12:45 pm UTC
You are a genius Tom! It took me 4 attempts to get the combination of keystrokes right but I finally succeeded!! I learn something new about Oracle here daily. Now today I have learnt something new about Unix also! Thanks a lot!
P.S. It does not happen in Linux. Am tempted to paste the results and hence will. (have got addicted to this method ;))
ftp> op
To 10.1.3.231
Connected to 10.1.3.231.
220 (vsFTPd 1.2.0)
User (10.1.3.231:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> bi
200 Switching to Binary mode.
ftp> put i:\sync_indx.txt
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 701 bytes sent in 0.00Seconds 701000.00Kbytes/sec.
ftp>
vi the file in linux
------
create or replace procedure sync_all_ctx_indexes
as
l_err long;
begin
for x in ( select u.name idx_owner,
idx_name idx_name
from sys.user$ u, dr$index
where idx_owner# = u.user#
and u.name <> 'CTXSYS' )
loop
begin
ctx_ddl.sync_index( x.idx_owner || '.' || x.idx_name );
exception
when others then
l_err := l_err || sqlerrm;
end;
end loop;
if ( l_err is not null )
then
raise_application_error( -20001, 'Errors ' || l_err );
end if;
end;
/
-------------
ftp> op
To 10.5.1.1
Connected to 10.5.1.1.
220 FTP server ready.
User (10.5.1.1:(none)): oracle
331 Password required for oracle.
Password:
230-1 unsuccessful login attempt since last login.
230-Last unsuccessful login: Sat May 7 22:13:58 IST 2005 on ftp from ::ffff:10.1.3.104
230-Last login: Sat May 7 22:13:44 IST 2005 on /dev/pts/3 from 10.1.3.104
230 User oracle logged in.
ftp> bi
200 Type set to I.
ftp> put i:\sync_indx.txt
200 PORT command successful.
150 Opening data connection for sync_indx.txt.
226 Transfer complete.
ftp: 701 bytes sent in 0.00Seconds 701000.00Kbytes/sec.
ftp> by
vi file in aix
------
create or replace procedure sync_all_ctx_indexes^M
as^M
l_err long;^M
begin^M
for x in ( select u.name idx_owner,^M
idx_name idx_name^M
from sys.user$ u, dr$index^M
where idx_owner# = u.user#^M
and u.name <> 'CTXSYS' )^M
loop^M
begin^M
ctx_ddl.sync_index( x.idx_owner || '.' || x.idx_name );^M
exception^M
when others then^M
l_err := l_err || sqlerrm;^M
end;^M
end loop;^M
^M
if ( l_err is not null )^M
then^M
raise_application_error( -20001, 'Errors ' || l_err );^M
end if;^M
end;^M
/^M
^M
-----
does the code look familiar? It's borrowed from this site!
May 07, 2005 - 1:44 pm UTC
you shouldn't be using binary in the first place for a text file pulled from windows to any unix variant. period.
is this a problem...
Craig, July 28, 2005 - 6:02 pm UTC
Tom,
I was resizing some datafiles to something more appropriate to free up some disk space. When I issued the alter database command, I received an ora-01516. Seemed pretty strange since I listed the db files on the filesystem and did a copy/paste into the alter database command. I investigated further and found the following:
$ sqlplus /
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Jul 28 17:37:35 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> select filename from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/ora01/oradata/mrdss/system01.dbf
/ora01/oradata/mrdss/undotbs01.dbf
/ora01/oradata/mrdss/cwmlite01.dbf
/ora01/oradata/mrdss/drsys01.dbf
/ora01/oradata/mrdss/example01.dbf
/ora01/oradata/mrdss/indx01.dbf
/ora01/oradata/mrdss/odm01.dbf
/ora01/oradata/mrdss/tools01.dbf
/ora01/oradata/mrdss/users01.dbf
/ora01/oradata/mrdss/xdb01.dbf
/ora01/oradata/mrdss/MRDSS.dbf
FILE_NAME
--------------------------------------------------------------------------------
/ora01/oradata/mrdss/mrdss2.dbf
/ora01/oradata/mrdss/mrdss3.dbf
/ora01/oradata/mrdss/mrdss4.dbf
/ora02/oradata/mrdss/MRDSSUSERS.dbf
/ora02/oradata/mrdss/mrdss5.dbf
16 rows selected.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
$ ls -l /ora01/oradata/mrdss
lrwxrwxrwx 1 oracle oinstall 20 Oct 19 2004 /ora01/oradata/mrdss -> /ora02/oradata/mrdss
$ ls -l /ora02/oradata/mrdss
total 36151136
-rw------- 1 oracle oinstall 1048584192 Jul 27 17:15 MRDSS.dbf
-rw------- 1 oracle oinstall 2097160192 Jul 28 17:35 MRDSSUSERS.dbf
-rw------- 1 oracle oinstall 1728512 Jul 28 17:39 control01.ctl
-rw------- 1 oracle oinstall 1728512 Jul 28 17:39 control02.ctl
-rw------- 1 oracle oinstall 1728512 Jul 28 17:39 control03.ctl
-rw------- 1 oracle oinstall 20979712 Jul 27 17:15 cwmlite01.dbf
-rw------- 1 oracle oinstall 20979712 Jul 27 17:15 drsys01.dbf
-rw------- 1 oracle oinstall 125837312 Jul 27 17:15 example01.dbf
-rw------- 1 oracle oinstall 26222592 Jul 27 17:15 indx01.dbf
-rw------- 1 oracle oinstall 1048584192 Jul 28 17:05 mrdss2.dbf
-rw------- 1 oracle oinstall 2097160192 Jul 28 14:18 mrdss3.dbf
-rw------- 1 oracle oinstall 2097160192 Jul 27 17:15 mrdss4.dbf
-rw------- 1 oracle oinstall 2097160192 Jul 27 17:15 mrdss5.dbf
-rw------- 1 oracle oinstall 20979712 Jul 27 17:15 odm01.dbf
-rw------- 1 oracle oinstall 104858112 Jul 28 17:39 redo01.log
-rw------- 1 oracle oinstall 104858112 Jul 26 15:43 redo02.log
-rw------- 1 oracle oinstall 104858112 Jul 27 16:45 redo03.log
-rw------- 1 oracle oinstall 450895872 Jul 28 17:35 system01.dbf
-rw------- 1 oracle oinstall 514859008 Jul 7 13:29 temp01.dbf
-rw------- 1 oracle oinstall 1048584192 Dec 23 2004 temp02.dbf
-rw------- 1 oracle oinstall 10493952 Jul 27 17:15 tools01.dbf
-rw------- 1 oracle oinstall 6427779072 Jul 28 17:37 undotbs01.dbf
-rw------- 1 oracle oinstall 26222592 Jul 27 17:15 users01.dbf
-rw------- 1 oracle oinstall 49160192 Jul 27 17:15 xdb01.dbf
$
Do I need to change this so that the filenames in the data dictionary match where they actually are on disk? If so, whats the procedure? Im developing a backup/recovery plan and wonder if this is going to inhibit RMAN in any way.
As always, thank you much for your insight!
July 29, 2005 - 8:11 am UTC
Symbolic links can be very dangerous like that (confusing at a human level). They work ok at the file level but the directory level is sort of confusing.
since /ora01/oradata/mrdss is a pointer to ora02, I would do something like:
o create the alter database rename file statements to rename /ora01/oradata/mrdss/mrdss2.dbf to /ora02/oradata/mrdss/mrdss2.dbf and so on
o shutdown
o startup mount
o do the renames
o alter database open
and get rid of the symbolic link all together.
You do know that having all 3 control files in the same directory "isn't a good idea", they should be on 3 separate physical devices (same with your logs...)
making progress...
Craig, July 29, 2005 - 10:40 am UTC
Tom,
Thanks again for the prompt reply. I'm doing my best to get my new employer in a much better place with their Oracle environment. I'm not sure if you can tell from previous posts, but I'm working under a less-then-ideal set of circumstances. Yes, I'm trying to convince them to move their control files to multiple devices along with the online redo logs.
I've made them aware of the consequences (disk explodes w/ controlfiles & redo and no bkups - there is no database to recover), but my management believes they can clone another "replicated" server, fix the broken box, put that image on it and take off.
I'm fighting on several fronts: database design, hardware, network, space management, bkup/recovery, and managements misconceptions of how Oracle really works. I'm using your suggestions and Oracle documentation as my ammunition and feel like I'm making some progress, slowly. Anyway, I'm sure my circumstances are no different then hundreds/thousands of other Oracle professionals "out there".
July 29, 2005 - 2:37 pm UTC
I don't understand why they have 3 control files then :)
A reader, October 18, 2006 - 12:01 pm UTC
Renaming a datafile which has // instead of a single /
Ritesh, October 01, 2007 - 3:11 am UTC
Hi Tom,
I can't seem to rename the file. I've tried to ' ' and "" it and that didn't work either.
alter database rename file
'/fs-101//databases/test/admin.dbf' to '/fs-101/databases/test/admin.dbf';
alter database rename file
'"/fs-101//databases/test/admin.dbf"' to '/fs-101/databases/test/admin.dbf';
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile
How to rename this file?
Thanks
October 03, 2007 - 2:38 pm UTC
ops$tkyte%ORA10GR2> create tablespace double datafile '/tmp//test.dbf' size 1m;
Tablespace created.
ops$tkyte%ORA10GR2> select file_name from dba_data_files where tablespace_name = 'DOUBLE';
FILE_NAME
------------------------------
/tmp/test.dbf
are you sure they are in there?
select the file name and DUMP() it too.
// problem
ritesh, October 03, 2007 - 11:11 pm UTC
Hi Tom, forget to tell you it is with version Oracle 9i.
Thanks
Renaming a file with a junk character.
Pankaj, January 16, 2008 - 6:00 am UTC
Hello Tom,
This is regarding renaming the datafiles on a unix platform.
When a file was created it was created with some junk characters as the user may have used some backspace etc while creating the file.
In the trace of the control file the name of the file occurs as 'sysaux03.dv^Hbf' where as on the Unix level the name is as 'sysaux03.dbf'.
I am getting the error ora- 01511, ora- 01516 while renaming the above datafile. ( Note the file is offline while renaming )
Is there some way or syntax to rename such files ??
Regards
Pankaj
January 16, 2008 - 3:47 pm UTC
It worked for me to just rename the file? (^? is a delete, unix shows just a ? in the filename when I ls it) I used vi to create the command - ^V<delete> put it into the file and ran it in sqlplus
ops$tkyte%ORA10GR2> create tablespace test datafile '/tmp/test^?test.dbf' size 1m reuse;
Tablespace created.
ops$tkyte%ORA10GR2> !ls -l /tmp/testtest.dbf
ls: /tmp/testtest.dbf: No such file or directory
ops$tkyte%ORA10GR2> !ls -l /tmp/tes*.dbf
-rw-rw---- 1 ora10gr2 ora10gr2 1056768 Jan 16 16:00 /tmp/test?test.dbf
ops$tkyte%ORA10GR2> alter tablespace test offline;
Tablespace altered.
ops$tkyte%ORA10GR2> !su
Password:
[root@localhost tkyte]# cd /tmp
<b>be careful with this command!!! make sure test*.dbf only resolves to a single file...</b>
[root@localhost tmp]# mv test*.dbf testtest.dbf
[root@localhost tmp]# exit
exit
ops$tkyte%ORA10GR2> alter database rename file '/tmp/test^?test.dbf' to '/tmp/testtest.dbf';
Database altered.
To be extra careful, use -i parameter
Stew Ashton, January 17, 2008 - 5:53 am UTC
January 17, 2008 - 11:18 am UTC
I was worried about moving a bunch of files into a directory too :) that would be really bad...