MOVE ALL DATAFILES INCLUDING SYSTEM TO DIFFERENT LOCATION

STEPS:

1) Shutdown database after creating pfile

2) Copy/move datafiles, control files to new directory at OS level

3) Startup database in mount.

4) Rename / relocate datafile at database level

5) Open the database

STEP1: SHUTDOWN DATABASE AFTER CREATING PFILE.

SQL> select name from v$datafile;

NAME

——————————————————————————–

/u04/oradata/TEST/system01.dbf

/u04/oradata/TEST/undotbs01.dbf

/u04/oradata/TEST/sysaux01.dbf

/u04/oradata/TEST/users01.dbf

SQL> select name from v$tempfile;

NAME

——————————————————————————–

/u04/oradata/TEST/temp01.dbf

STEP 1: SHUTDOWN THE DATABASE

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

STEP2: COPY DATAFILE CONTROLFILES TO NEW LOCATION AT OS LEVEL

cpsoa1:/u04/oradata/TEST> cp -p system01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p undotbs01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p sysaux01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p users01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> cp -p temp01.dbf /u02/oradata/TEST/

cpsoa1:/u04/oradata/TEST> pwd

/u04/oradata/TEST

STEP3: STARTUP DATABASE IN MOUNT STAGE.

SQL> startup mount

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 2085296 bytes

Variable Size 163581520 bytes

Database Buffers 352321536 bytes

Redo Buffers 6299648 bytes

Database mounted.

STEP4: RENAME OR RELOCATE DATAFILE AT DATABASE LEVEL

SQL> Alter database rename file ‘/u04/oradata/TEST/system01.dbf’ to ‘/u02/oradata/TEST/system01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/undotbs01.dbf’ to ‘/u02/oradata/TEST/undotbs01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/sysaux01.dbf’ to ‘/u02/oradata/TEST/sysaux01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/users01.dbf’ to ‘/u02/oradata/TEST/users01.dbf';

Database altered.

SQL> Alter database rename file ‘/u04/oradata/TEST/temp01.dbf’ to ‘/u02/oradata/TEST/temp01.dbf';

Database altered.

STEP5: OPEN THE DATABASE

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME

——————————————————————————–

/u02/oradata/TEST/system01.dbf

/u02/oradata/TEST/undotbs01.dbf

/u02/oradata/TEST/sysaux01.dbf

/u02/oradata/TEST/users01.dbf

SQL> select name from v$tempfile;

NAME

——————————————————————————–

/u02/oradata/TEST/temp01.dbf