Move datafile Oracle with sql command with RMAN

13 Sep

================================================================
Move datafile Oracle with sql command with RMAN
================================================================
1. catat lokasi tablespace dan datafile
[oracle@iscdd iscdd]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd iscdd]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Sep 14 17:42:23 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ISCDD (DBID=2058697900)

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    480      SYSTEM               ***     /u02/oradata/iscdd/iscdd/system01.dbf
2    30       UNDOTBS1             ***     /u02/oradata/iscdd/iscdd/undotbs01.dbf
3    240      SYSAUX               ***     /u02/oradata/iscdd/iscdd/sysaux01.dbf
4    0        USERS                ***     /u02/oradata/iscdd/iscdd/users01.dbf
5    100      EXAMPLE              ***     /u02/oradata/iscdd/iscdd/example01.dbf
6    2        SENIN_TS             ***     /u02/oradata/iscdd/iscdd/senin_ts01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    20       TEMP                 32767       /u02/oradata/iscdd/iscdd/temp01.dbf

2. test memindahkan TABLESPACE users, dengan terlebih dulu membuat TABLESPACE users offline
RMAN> SQL ‘ALTER TABLESPACE users OFFLINE NORMAL’;

3. lakukan copy dengan host, jika pakai linux tambahkan cp
RMAN> copy datafile ‘/u02/oradata/iscdd/iscdd/users01.dbf’ to ‘/u02/oradata/users01.dbf’;

4. mecatat di controlfile
RMAN> switch datafile ‘/u02/oradata/iscdd/iscdd/users01.dbf’ to COPY;
datafile 4 switched to datafile copy “/u02/oradata/users01.dbf”

5. Membuat TABLESPACE users online
RMAN> SQL ‘ALTER TABLESPACE users ONLINE’;

6. periksa perubahan yang terjadi
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    480      SYSTEM               ***     /u02/oradata/iscdd/iscdd/system01.dbf
2    30       UNDOTBS1             ***     /u02/oradata/iscdd/iscdd/undotbs01.dbf
3    240      SYSAUX               ***     /u02/oradata/iscdd/iscdd/sysaux01.dbf
4    5        USERS                ***     /u02/oradata/users01.dbf
5    100      EXAMPLE              ***     /u02/oradata/iscdd/iscdd/example01.dbf
6    2        SENIN_TS             ***     /u02/oradata/iscdd/iscdd/senin_ts01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    20       TEMP                 32767       /u02/oradata/iscdd/iscdd/temp01.dbf

7. test untuk dikembalikan seperti semula
RMAN> SQL ‘ALTER TABLESPACE users OFFLINE NORMAL’;
RMAN> switch datafile ‘/u02/oradata/users01.dbf’ to copy;
datafile 4 switched to datafile copy “/u02/oradata/iscdd/iscdd/users01.dbf”

RMAN> SQL ‘ALTER TABLESPACE users ONLINE’;
sql statement: ALTER TABLESPACE users ONLINE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 09/14/2008 18:05:25
RMAN-11003: failure during parse/execution of SQL statement: ALTER TABLESPACE users ONLINE
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/u02/oradata/iscdd/iscdd/users01.dbf’

(kok jadi error ????)

RMAN> RECOVER DATAFILE ‘/u02/oradata/iscdd/iscdd/users01.dbf’;
Media recovery complete.
RMAN> SQL ‘ALTER TABLESPACE users ONLINE’;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: