Move datafile Oracle with sql command without RMAN

13 Sep

================================================================
Move datafile Oracle with sql command without RMAN
================================================================
1. catat lokasi tablespace dan datafile
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> column TABLESPACE_NAME format a20;
SQL> column FILE_NAME format a40;
SQL> select TABLESPACE_NAME, FILE_NAME from  dba_data_files;
TABLESPACE_NAME      FILE_NAME
——————– —————————————-
USERS                /u02/oradata/iscdd/iscdd/users01.dbf
SYSAUX               /u02/oradata/iscdd/iscdd/sysaux01.dbf
UNDOTBS1             /u02/oradata/iscdd/iscdd/undotbs01.dbf
SYSTEM               /u02/oradata/iscdd/iscdd/system01.dbf
EXAMPLE              /u02/oradata/iscdd/iscdd/example01.dbf
SENIN_TS             /u02/oradata/iscdd/iscdd/senin_ts01.dbf
6 rows selected.

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

3. lakukan copy dengan host, jika pakai linux tambahkan cp
SQL> HOST cp /u02/oradata/iscdd/iscdd/users01.dbf  /u02/oradata/iscdd/users01.dbf;

4. mecatat di controlfile
SQL> ALTER TABLESPACE users
RENAME DATAFILE ‘/u02/oradata/iscdd/iscdd/users01.dbf’
TO ‘/u02/oradata/iscdd/users01.dbf’;

5. Membuat TABLESPACE users online
SQL> ALTER TABLESPACE users ONLINE;

6. periksa perubahan yang terjadi
SQL> select TABLESPACE_NAME, FILE_NAME from  dba_data_files;
TABLESPACE_NAME      FILE_NAME
——————– —————————————-
USERS                /u02/oradata/iscdd/users01.dbf
SYSAUX               /u02/oradata/iscdd/iscdd/sysaux01.dbf
UNDOTBS1             /u02/oradata/iscdd/iscdd/undotbs01.dbf
SYSTEM               /u02/oradata/iscdd/iscdd/system01.dbf
EXAMPLE              /u02/oradata/iscdd/iscdd/example01.dbf
SENIN_TS             /u02/oradata/iscdd/iscdd/senin_ts01.dbf
6 rows selected.

7. test untuk dikembalikan seperti semula
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
SQL> ALTER TABLESPACE users
RENAME DATAFILE ‘/u02/oradata/iscdd/users01.dbf’
TO ‘/u02/oradata/iscdd/iscdd/users01.dbf’;

*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/u02/oradata/iscdd/iscdd/users01.dbf’
(kok jadi error ????)

SQL> RECOVER DATAFILE ‘/u02/oradata/iscdd/iscdd/users01.dbf’;
Media recovery complete.
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: