Pages

Wednesday, February 6, 2013

how to move datafiles in oracle 11g

Move or Rename datafiles: Steps same in 10g or 11g

C:\> rman target /

RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace   RB segs Datafile Name
---- -------- ------------ ------- ------------------------
1    1320     SYSTEM       ***     D:\ORADATA\HSBC\SYSTEM01.DBF
3    860      SYSAUX       ***     D:\ORADATA\HSBC\SYSAUX01.DBF
4    7681     USERS        ***     D:\ORADATA\HSBC\USERS01.DBF
5    5864     UNDOTBS01    ***     D:\ORADATA\HSBC\UNDOTBS1.DBF
6    6219     USERS        ***     C:\ORACLE\PRODUCT\10.2.0\ORADATA\HSBC\USERS02.DBF
9    2281     INDX01       ***     D:\ORADATA\HSBC\INDX01.DBF
10   2719     USER01       ***     D:\ORADATA\HSBC\USER01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ---------- ----------- --------------------
1    4229      TEMP      32767       D:\ORADATA\HSBC\TEMP01.DBF

RMAN>

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

RMAN> COPY DATAFILE 6 TO 'D:\ORADATA\HSBC\USERS02.DBF';

RMAN> SWITCH DATAFILE 6 TO COPY;

RMAN> RECOVER TABLESPACE USERS; 

RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';

RMAN> HOST 'del C:\ORACLE\PRODUCT\10.2.0\ORADATA\HSBC\USERS02.DBF';

RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace   RB segs Datafile Name
---- -------- ------------ ------- ------------------------
1    1320     SYSTEM       ***     D:\ORADATA\HSBC\SYSTEM01.DBF
3    960      SYSAUX       ***     D:\ORADATA\HSBC\SYSAUX01.DBF
4    7681     USERS        ***     D:\ORADATA\HSBC\USERS01.DBF
5    5864     UNDOTBS01    ***     D:\ORADATA\HSBC\UNDOTBS1.DBF
6    6219     USERS        ***     D:\ORADATA\HSBC\USERS02.DBF
9    2281     INDX01       ***     D:\ORADATA\HSBC\INDX01.DBF
10   2719     USER01       ***     D:\ORADATA\HSBC\USER01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ---------- ----------- --------------------
1    4229      TEMP      32767       D:\ORADATA\HSBC\TEMP01.DBF

RMAN> EXIT;


No comments:

Post a Comment