moving a database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: moving a database

  1. #1
    Join Date
    Jul 2000
    Posts
    70
    what utility do i use to move a database from mount point /u01 to /u02 in linux?

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    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.

    <font face="courier">
    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;
    </font>

  3. #3
    Join Date
    Jul 2000
    Posts
    70
    What about using the immigration utility?
    Would that also do it?

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jul 2000
    Posts
    70
    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?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    (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
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width