Re: System datafile, moving.

"s. hunter" wrote:
>
> Hi,
>
> This is something which I've never had to do before, but I migrated a
> database from 7.3.3 to 8.0.5 last year. I didn't delete 7.3.3
> $ORACLE_HOME because there were other databases using that version of
> oracle.
>
> I now DO want to get rid of it, but my system.dbf file is still in the
> 7.3.3 $ORACLE_HOME/dbs (oops!). Could someone please tell me the
> easiest way to move it to the 8.0.5 ORACLE_HOME?
>
> Thanks!
>
> Sarah

Sarah:

Four easy steps:

1. Shutdown the instance
2. Use operating system commands to move the datafile.
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
4. Open the Database

Step 1: Easy shutdown the 8.0.5 instance.

  SVRMGR> connect internal;
  SVRMGR> shutdown;
  SVRMGR> exit;

Step 2: Move the datafile(s) you want using OS commands.

  UNIX Example:
  % mv /u09/app/oradata/TESTDB/system01.dbf /u10/app/oradata/TESTDB/system01.dbf

Step 3: Mount the database and use the ALTER DATABASE to rename the file within the database.

  SVRMGR> connect internal
  SVRMGR> startup mount TESTDB
  SVRMGR> ALTER DATABASE RENAME FILE
       2> '/u09/app/oradata/TESTDB/data01.dbf' TO
       3> '/u10/app/oradata/TESTDB/data01.dbf';

  IMPORTANT: Do not disconnect after this step is complete;   stay logged in to the database and proceed to Step 4.

Step 4: Open the database.

  Now that the database knows how to
  find the moved file, the instance can start.

  SVRMGR> alter database open;

Hope this helps. Keep in mind that there are other ways of moving datafiles while keeping the instance up, (by taking the tablespace offline),
but those procedures cannot be used for the SYSTEM tablespace or for tablespaces containing active rollback segments or temporary segments.

Cheers,
-- jeff

Jeffrey M. Hunter                   Email:  jhunter_at_fore.com

Senior Database Administrator       Office: 724.742.7435

Marconi Communications              Pager:  888.704.5610

2000 FORE Drive                     Fax:    724.742.6800

Warrendale, PA 15086-7566           Web:    www.marconi.com

-------------------------------------------------------------