Moving control files,dbf files and redo logs from one location to anot

[m2001331]

Dear all,
I have a question - due to serious lack of space in a test server - I need to move the control files,*.dbf and redo logs of a database from one file system to another in the same machine.These file systems are owned by oracle user.
currently i have the above files in
/oracle9i/oradata and now i'd like to move it to /ora.

I have attempted to change the controlfiles = ("ora/control01") in the init<sid>.ora -parameter file.
after making this changes i tried to shutdown and restart the db but it spat out the following error :-
ORA-00205: "error in identifying control file "

I need to move this db to a larger place, but i don't know if i have to this database and recreate.i was hoping that moving these files and restarting would work.
Please advise on what i should do.
thanks a million.

 

[SantaMufasa]

M,

If you have changed the designated location in your init<SID>.ora parameter file to match the current location(s) of your control files, and you are still receiving the diagnostic, "error in identifying control file", then is it possible that your Oracle instance is under the direction of an "spfile" instead of the init<SID>.ora file? (Check by looking in your init<SID>.ora file for an entry that says "spfile = <some name>".)

If you are running under the auspices of an spfile, you should be able to temporarily revert to the init<SID>.ora by removing the "spfile = " entry in your init<SID>.ora file.

If there is no spfile involved, then ensure that the spelling of the path(s) and file names of your control files match the designation of the control files in your init<SID>.ora.

Once you have confirmed that your control files locations match what Oracle (init<SID>.ora) is expecting, then you issue the following command:

startup mount

This means that Oracle is aware of, but has not yet attempted to open, your database data files or on-line redo log files. This is the point at which you advise your Oracle instance of the location change of those two sets of files. You accomplish this advisement with the "ALTER DATABASE RENAME FILE..." command. You can rename all of your data and log files within one issuance of the RENAME command.

If you have not yet moved your database date files and on-line redo log files and if you can successfully bring up your "old" instance (with the files in the original locations), then you can use my script that follows to facilitate your renaming of your files. I call the script "RenameFiles.sql". My script, as you will see, creates two other scripts for you:

1) "TempMV.sh", which issues Unix "mv" commands to actually move files from their original location to the new target location. You run that script from your Unix o/s command prompt.

and
2) "TempRename.sql", which you run from your prompt immediately following your successful "startup mount" command. This script contains the "ALTER DATABASE RENAME FILE..." command.

Following are the "RenameFiles.sql" script and a representative session showing the relocation of my files from one file system to another:

Section 1 -- "RenameFiles.sql" script:

set echo off
set pagesize 0
set heading off
set feedback off
set verify off
set trimspool on
accept oldstr prompt "What string do you want to replace in filenames? "
accept newstr prompt "What should replace the old string in filenames? "
prompt
prompt
spool TempMV.sh
select 'mv '||member||' '||replace(member,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))
   from v$logfile
   where instr(member,'&oldstr')>0;
select 'mv '||file_name||' '||replace(file_name,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))
   from dba_data_files
   where instr(file_name,'&oldstr')>0;
spool off
prompt
prompt Wrote File "TempMV.sh"
prompt
spool TempRename.sql
select 'alter database Rename file' from dual;
select decode(rownum,1,'    ','   ,')||''''||
   file_name||'''' from dba_data_files
   where instr(file_name,'&oldstr')>0;
select '   ,'''||member||'''' from v$logfile
   where instr(member,'&oldstr')>0;
select 'to' from dual;
select decode(rownum,1,'    ','   ,')||''''||
   replace(file_name,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))||''''
   from dba_data_files
   where instr(file_name,'&oldstr')>0;
select '   ,'''||
   ''''||replace(member,ltrim(rtrim('&oldstr')),ltrim(rtrim('&newstr')))||''''
   from v$logfile
   where instr(member,'&oldstr')>0;
select ';' from dual;
spool off
prompt
prompt Wrote File "TempRename.sql"

Be sure to save the above code to a script and run from the script. (Because of the accept/prompt commands, you cannot just copy and paste the above script at the SQL*Plus prompt.) In the example, below, I have changed my file system from "...data1..." to "...data2...":

Section 2 -- Sample invocation of "RenameFiles.sql"

@RenameFiles
What string do you want to replace in filenames? data1
What should replace the old string in filenames? data2


mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/redo03.log /dbsrv/oracle/data2/oradata/SAV92/SAV92/redo03.log
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/redo02.log /dbsrv/oracle/data2/oradata/SAV92/SAV92/redo02.log
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/redo01.log /dbsrv/oracle/data2/oradata/SAV92/SAV92/redo01.log
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/system01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/system01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cwmlite01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cwmlite01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/drsys01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/drsys01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/rbs01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/rbs01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cert01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cert01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/odm01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/odm01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/tfcr01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/tfcr01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cert02.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cert02.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cert03.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cert03.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/tfdb01.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/tfdb01.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cert04.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cert04.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cert05.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cert05.dbf
mv /dbsrv/oracle/data1/oradata/SAV92/SAV92/cert06.dbf /dbsrv/oracle/data2/oradata/SAV92/SAV92/cert06.dbf

Wrote File "TempMV.sh"

alter database Rename file
    '/dbsrv/oracle/data1/oradata/SAV92/SAV92/system01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cwmlite01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/drsys01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/rbs01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cert01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/odm01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/tfcr01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cert02.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cert03.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/tfdb01.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cert04.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cert05.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/cert06.dbf'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/redo03.log'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/redo02.log'
   ,'/dbsrv/oracle/data1/oradata/SAV92/SAV92/redo01.log'
to
    '/dbsrv/oracle/data2/oradata/SAV92/SAV92/system01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cwmlite01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/drsys01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/rbs01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cert01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/odm01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/tfcr01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cert02.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cert03.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/tfdb01.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cert04.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cert05.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/cert06.dbf'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/redo03.log'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/redo02.log'
   ,'/dbsrv/oracle/data2/oradata/SAV92/SAV92/redo01.log'
;

Wrote File "TempRename.sql"

At the point you are ready to execute either of the generated scripts, here are the steps to follow:

1) Shutdown your database:

shutdown immediate

2) Run the first generated script, "TempMV.sh", to move your data files. Ensure that Unix permissions on the "TempMV.sh" script allow for execution.

% TempMV.sh

3) Startup your Oracle database, but only to "mount" status:

startup mount

4) From the prompt, run "TempRename.sql"

@TempRename

5) Oracle your Oracle database for normal access:

SQL> ALTER DATABASE OPEN;

Let us know if any of this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[LKBrwnDBA]

Another option is to re-create the controlfiles; if you have a 'trace' copy of the controlfile done with:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Then all you would have to do:

1) Shutdown database.
2) Copy the files to the new locations.
3) Modify the 'CREATE CONTROLFILE' statement to the new locations.
4) Modify the init${SID}.ora file (controlfile, logs, etc..parameters) to new file locations.
5) STARTUP NOMOUNT;
6) Execute modified 'create controlfile' script.
7) ALTER DATABASE OPEN;



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb