moving a database

what utility do i use to move a database from mount point /u01 to /u02 in linux?

I assume you already have the /u01 and /u02 mount points in the file system?

If all you want to do is move the files around (datafiles, control files, etc) you just need to copy the files to the new location and let Oracle know about it.

I don't think you can move the control files without shutting down the database, copying the /u01 control files to /u02, and modifying your init.ora parameter.

Log files can't be renamed either as far as I recall, you need to create new files in /u02 then you can drop the files in /u01, you can do this while the database is open.

The datafiles can be done while the database is open as well but ideally you would be the only one connected doing work.

To move a datafile you need to

alter tablespace xxx offline normal;
host cp /u01/datafile /u02/datafile
alter database rename datafile '/u01/datafile' to '/u02/datafile';
alter tablespace xxx online;

I'll add small script to get it started for all your tablespaces, it won't work verbatim because it can only select the current filenames, not where you want them to go. What I did was just spool this to a file and manually edit it (with some creative search and replace) so it copies/renames the original to the new. Also my script ignores a few tablespaces, because I already had them created in the new database, you might want to remove the where clauses and move them all.

The script will spool the ren_tblspc.sql, which is what you manually edit so the source/destinations are correct and then you can run it. Note it takes all tablespaces offline at the same time so you probably want to make sure you're the only one in the database. If you wanted to write it in pl/sql you could make it do one at a time.


set heading off;
set feedback off;
set pagesize 0;
set echo off;
set long 4000;
spool ren_tblspc.sql
select 'alter tablespace ' || tablespace_name || ' offline normal;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM', 'TEMP', 'RBS', 'USERS');

select 'host cp ' || '''' || file_name || ''' to ''' || file_name || ''';'
from dba_data_files
where tablespace_name not in ('SYSTEM', 'TEMP', 'RBS', 'USERS');

select 'alter tablespace ' || tablespace_name || ' rename datafile ''' || file_name || ''' to ''' || file_name || ''';'
from dba_data_files
where tablespace_name not in ('SYSTEM', 'TEMP', 'RBS', 'USERS');

select 'alter tablespace ' || tablespace_name || ' online;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM', 'TEMP', 'RBS', 'USERS');
spool off;
quit;

What about using the immigration utility?
Would that also do it?

The migration utility is used when changing the datafiles for use by a different release of Oracle, it's not just for moving files around.

From what I got out of your original post all you want to do is move files from /u01 to /u02 and let Oracle know about it. If you need to do more let us know.

FYI, you can copy your redo log files from /u01 to /u02 AS LONG AS THEY ARE NOT BEING USED, and issue:
alter database rename file '/u01/redo01.rdo' to '/u02/redo01.rdo'.
You then switch the logfile, wait until the next logfile is done archiving, and copy it as well. If you can bring your database down, you copy the files from /u01 to /u02, startup the database in mount mode, rename the files, and open the database.

ok, say I prefer shutting down the database and copying the files to /u02
what exactlty are the files that I need to copy over, and also, how do I make oracle to read from the database files from /u02 now that Ive moved them and theyre not in their orignal location?

(Assumes oracle software will not be moved.)

1. Shutdown database (normal or immediate)
2. copy all datafiles (from v$datafile) from /u01 to /u02
3. copy all control files (from v$controlfile) from /u01 to /u02
4. copy all log files (from v$logfile) from /u01 to /u02
5. edit init.ora or config.ora and change location of control_files parameter from /u01 control files to /u02 control files.
6. startup mount - the database will now read enough info from the init.ora file to manipulate the control files.
7. alter database rename file '/u01/file1.dbf' to '/u02/file2.dbf' for all your data files and log files.
8. alter database open
9. shutdown
10. startup