Move datafile to different location

http://www.orafaq.com/wiki/Move_datafile_to_different_location

Database is in ARCHIVELOG mode
Take the datafile offline with the "ALTER DATABASE DATAFILE '/old/location' OFFLINE;" command.
Copy or move the datafile to its new location.

SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';
SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;

Database is in NOARCHIVELOG mode
Shutdown the database
Copy or move the datafile to its new location.

Start SQL*Plus, do a "STARTUP MOUNT" and rename the file:
SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> ALTER DATABASE OPEN;

Check current location of datafiles

If the database is offline you can still check the current location of the datafiles and archive log mode.
SQL> select log_mode from v$database;
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;

Note that the location of data files (and other fixed tables/views) is stored in the control files, and the location of the control files is stored in the init file (pfile or spfile).

No Comments

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *