Move Database to another location

 

Assumption - Move C/R/D files from 

                                C:\Oracle\19c\oradata\delme

                                to

                                D:\oracle\oradata\delme



Change Location Of System, Temporary or Rollback Tablespace’s Datafiles

• shutdown immediate;
• startup mount;
• MOVE datafiles to New Location from OS level
• Invoke below ALTER commands

alter database rename file 'C:\Oracle\19c\oradata\delme\SYSAUX01.DBF' to 'D:\oracle\oradata\delme\SYSAUX01.DBF';

alter database rename file 'C:\Oracle\19c\oradata\delme\SYSTEM01.DBF' to 'D:\oracle\oradata\delme\SYSTEM01.DBF';

alter database rename file 'C:\Oracle\19c\oradata\delme\TEMP01.DBF' to 'D:\oracle\oradata\delme\TEMP01.DBF';

alter database rename file 'C:\Oracle\19c\oradata\delme\UNDOTBS01.DBF' to 'D:\oracle\oradata\delme\UNDOTBS01.DBF';

alter database rename file 'C:\Oracle\19c\oradata\delme\USERS01.DBF' to 'D:\oracle\oradata\delme\USERS01.DBF';

Moving RedoLogs

alter database open;

alter database add logfile group 4 ('D:\oracle\oradata\delme\redo041.log','D:\oracle\oradata\delme\redo042.log') size 50m;

alter database add logfile group 5 ('D:\oracle\oradata\delme\redo051.log','D:\oracle\oradata\delme\redo052.log') size 50m;

alter database add logfile group 6 ('D:\oracle\oradata\delme\redo061.log','D:\oracle\oradata\delme\redo062.log') size 50m;

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

Moving control files

SQL > Create pfile from spfile;
Update Controlfile parameters;

SQL> create spfile from pfile;
Move the controlfiles to New location;

SQL> startup;

Moving individual non system datafile

alter tablespace USERS offline;
Move datafile to New Location

alter tablespace USERS rename datafile 'C:\oracle\19c\oradata\delme\USERS01.DBF' to 'D:\oracle\oradata\delme\USERS01.DBF';
alter tablespace USERS online;