Move Datafile to new location (With Minimal Downtime)

My database.

?

RMAN> report schema;   using target database control file instead of recovery catalog Report of database schema for database with db_unique_name AOCDB   List of Permanent Datafiles =========================== File Size(MB) Tablespace           RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1    710      SYSTEM               ***     /u01/app/oradata/aocdb/system01.dbf 2    750      SYSAUX               ***     /u01/app/oradata/aocdb/sysaux01.dbf 3    1260     UNDOTBS1             ***     /u01/app/oradata/aocdb/undotbs01.dbf 4    2363     USERS                ***     /u01/app/oradata/aocdb/users01.dbf 5    1024     REST                 ***     /u01/app/oradata/aocdb/rest01.dbf 6    1024     INDX_REST            ***     /u01/app/oradata/aocdb/indx_rest01.dbf   List of Temporary Files ======================= File Size(MB) Tablespace           Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1    96       TEMP                 32767       /u01/app/oradata/aocdb/temp01.dbf

⇒ While database is up and running I will use RMAN to copy datafile of SYSTEM tablespace to another location.

?

RMAN> copy datafile 1 to '/u01/app/oradata/loc2/system01.dbf';   Starting backup at 15.05.2012 19:35:23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=126 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oradata/aocdb/system01.dbf output file name=/u01/app/oradata/loc2/system01.dbf tag=TAG20120515T193524 RECID=76 STAMP=783372953 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 Finished backup at 15.05.2012 19:35:59

⇒ Now I will shutdown database and start in mount mode.

?

RMAN> shutdown immediate;   database closed database dismounted Oracle instance shut down   RMAN> startup mount;   connected to target database (not started) Oracle instance started database mounted   Total System Global Area    1071333376 bytes   Fixed Size                     1318172 bytes Variable Size                352322276 bytes Database Buffers             713031680 bytes Redo Buffers                   4661248 bytes

⇒ Switch datafile to copy.

RMAN> switch datafile 1 to copy;   datafile 1 switched to datafile copy "/u01/app/oradata/loc2/system01.dbf"

⇒ Now to perform quick recover.

?

RMAN> recover datafile 1;   Starting recover at 15.05.2012 19:38:55 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=154 device type=DISK   starting media recovery media recovery complete, elapsed time: 00:00:01   Finished recover at 15.05.2012 19:38:57

⇒ That’s it! Open database.

?

RMAN> alter database open;   database opened

⇒ Little check that I’ve moved datafile.

?

1

RMAN> report schema;   Report of database schema for database with db_unique_name AOCDB   List of Permanent Datafiles =========================== File Size(MB) Tablespace           RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1    710      SYSTEM               ***     /u01/app/oradata/loc2/system01.dbf 2    750      SYSAUX               ***     /u01/app/oradata/aocdb/sysaux01.dbf 3    1260     UNDOTBS1             ***     /u01/app/oradata/aocdb/undotbs01.dbf … …

⇒ After switching datafiles, old datafile become copy so it is safe to drop it.

?

RMAN> list copy of datafile 1;   List of Datafile Copies =======================   Key     File S Completion Time     Ckp SCN    Ckp Time ------- ---- - ------------------- ---------- ------------------- 77      1    A 15.05.2012 19:38:15 5250088    15.05.2012 19:37:03         Name: /u01/app/oradata/aocdb/system01.dbf         RMAN> delete copy of datafile 1;   released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=154 device type=DISK List of Datafile Copies =======================   Key     File S Completion Time     Ckp SCN    Ckp Time ------- ---- - ------------------- ---------- ------------------- 77      1    A 15.05.2012 19:38:15 5250088    15.05.2012 19:37:03         Name: /u01/app/oradata/aocdb/system01.dbf       Do you really want to delete the above objects (enter YES or NO)? YES deleted datafile copy datafile copy file name=/u01/app/oradata/aocdb/system01.dbf RECID=77 STAMP=783373095 deleted datafile copy

So the only downtime was period during bouncing database and recovering datafile.