How To Copy or Move files in Database Offline Mode

             

1. Before looking at the tasks involved to perform the moving files, let's look at the current files:                                    

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, as the files to be moved are known, the database can be shut down:

2. Create pfile and shutdown DB

 SQL> create pfile from spfile;

File created.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Create directory structure(/home/oracle/databases/ora9) and copy all files from old location to new location.

$ 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

$

$ cp /home/oracle/OraHome1/databases/ora9/redo1.ora   /home/oracle/databases/ora9/redo1.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo2.ora   /home/oracle/databases/ora9/redo2.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo3.ora   /home/oracle/databases/ora9/redo3.ora

$

$ cp /home/oracle/OraHome1/databases/ora9/ctl_1.ora   /home/oracle/databases/ora9/ctl_1.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_2.ora   /home/oracle/databases/ora9/ctl_2.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_3.ora   /home/oracle/databases/ora9/ctl_3.ora

The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.

$ cp /home/oracle/OraHome1/dbs/initORA9.ora /home/oracle/databases/ora9/init.ora

The new location for the control files must be written into the (copied) init.ora file:

/home/oracle/databases/ora9/init.ora

4. Open Pfile and make change control files location

control_files = (/home/oracle/databases/ora9/ctl_1.ora,

                 /home/oracle/databases/ora9/ctl_2.ora,

                 /home/oracle/databases/ora9/ctl_3.ora)

$ sqlplus "/ as sysdba"

5. Startup Database in Exclusive mode with modified controlfile

SQL> startup exclusive mount pfile=/home/oracle/databases/ora9/init.ora

ORACLE instance started.

Total System Global Area  143725064 bytes

Fixed Size                   451080 bytes

Variable Size             109051904 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

6. Rename all files(Database files and Redo log files) in mount stage

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/system.dbf' to '/home/oracle/databases/ora9/system.dbf';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/undo.dbf'   to '/home/oracle/databases/ora9/undo.dbf';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/data.dbf'   to '/home/oracle/databases/ora9/data.dbf';

SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo1.ora'  to '/home/oracle/databases/ora9/redo1.ora';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo2.ora'  to '/home/oracle/databases/ora9/redo2.ora';
SQL> Database altered.

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo3.ora'  to '/home/oracle/databases/ora9/redo3.ora';

SQL> Database altered.

7. Shutdown Database and startup with modified Pfile , Create SP file from current and startup with newly created spfile

SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down.  SQL> startup pfile='/home/oracle/databases/ora9/init.ora' SQL>create spfile from pfile; SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL>startup; ORACLE instance started. Total System Global Area  143725064 bytes Fixed Size                   451080 bytes Variable Size             109051904 bytes Database Buffers           33554432 bytes Redo Buffers                 667648 bytes Database mounted. Database opened.;

8. Finally verify the Control, Data & Redolog files location.

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME    SUBSTR(FILE_NAME,1,70)

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

SYSTEM             /home/oracle/databases/ora9/system.dbf

UNDO               /home/oracle/databases/ora9/undo.dbf

DATA               /home/oracle/databases/ora9/data.dbf

SQL> select member from v$logfile;

MEMBER

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

/home/oracle/databases/ora9/redo1.ora

/home/oracle/databases/ora9/redo2.ora

/home/oracle/databases/ora9/redo3.ora

SQL> select name from v$controlfile;

NAME

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

/home/oracle/databases/ora9/ctl_1.ora

/home/oracle/databases/ora9/ctl_2.ora

/home/oracle/databases/ora9/ctl_3.ora

*********************************************** All The Best ***********************************************