Moving/Renaming Of Datafiles In Oracle
Example:/home/oracle/OraHome1/databases/ora9 (actual location)
/home/oracle/databases/ora9 (changed location)
SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;
TABLESPACE_NAME SUBSTR(FILE_NAME,1,70)
------------------------------ ----------------------------------------------------------------------
SYSTEM /home/oracle/OraHome1/databases/ora9/system.dbf
UNDO /home/oracle/OraHome1/databases/ora9/undo.dbf
DATA /home/oracle/OraHome1/databases/ora9/data.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/OraHome1/databases/ora9/redo1.ora
/home/oracle/OraHome1/databases/ora9/redo2.ora
/home/oracle/OraHome1/databases/ora9/redo3.ora
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/OraHome1/databases/ora9/ctl_1.ora
/home/oracle/OraHome1/databases/ora9/ctl_2.ora
/home/oracle/OraHome1/databases/ora9/ctl_3.ora
Now, the database can be shut down:
SQL> shutdown immediate;
The files can be copied to their destination:
$ cp /home/oracle/OraHome1/databases/ora9/system.dbf /home/oracle/databases/ora9/system.dbf
$ cp /home/oracle/OraHome1/databases/ora9/undo.dbf /home/oracle/databases/ora9/undo.dbf
$ cp /home/oracle/OraHome1/databases/ora9/data.dbf /home/oracle/databases/ora9/data.dbf
SQL> startup mount;
SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/system.dbf' to '/home/oracle/databases/ora9/system.dbf';
SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/undo.dbf' to '/home/oracle/databases/ora9/undo.dbf';
SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/data.dbf' to '/home/oracle/databases/ora9/data.dbf';
SQL> shutdown immediate;
SQL> shutdown mount;
SQL> alter database open;
No comments:
Post a Comment