oracle 11g RAC – ASM : move / rename datafile

13 Oct

###########################################
oracle 11g RAC – ASM : move / rename datafile
###########################################
1. list datafile
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Mon Sep 29 14:08:24 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
——————————————————————————–
+DATA1/node/datafile/users.259.666184225
+DATA1/node/datafile/undotbs1.258.666184225
+DATA1/node/datafile/sysaux.257.666184225
+DATA1/node/datafile/system.256.666184225
+DATA1/node/datafile/example.267.666184357
+DATA1/node/datafile/undotbs2.268.666184499
+DATA1/node/datafile/soe.dbf
+DATA1/node/datafile/soeindex.dbf
8 rows selected.

2. list diskgroup
SQL> SELECT name FROM v$asm_diskgroup;
NAME
——————————
DATA1
DATA2
DATA3
DATA4

3. move / rename datafile
a. offline datafile
SQL> ALTER DATABASE DATAFILE ‘+DATA1/node/datafile/users.259.666184225’ OFFLINE;
Database altered.
b. rman copy
RMAN> COPY DATAFILE ‘+DATA1/node/datafile/users.259.666184225’ TO ‘+DATA2/users.dbf’;
Starting backup at 29-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=113 instance=node1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA1/node/datafile/users.259.666184225
output file name=+DATA2/users.dbf tag=TAG20080929T141528 RECID=2 STAMP=666713737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-SEP-08
Starting Control File and SPFILE Autobackup at 29-SEP-08
piece handle=+DATA1/node/autobackup/2008_09_29/s_666713738.322.666713747 comment=NONE
Finished Control File and SPFILE Autobackup at 29-SEP-08
RMAN> exit
Recovery Manager complete.

c. list asmcmd
[oracle@node1 ~]$ ORACLE_SID=+ASM1; export ORACLE_SID
[oracle@node1 ~]$ asmcmd
ASMCMD> exit
[oracle@node1 ~]$ asmcmd -p
ASMCMD [+] > ls -la +DATA2/
Type      Redund  Striped  Time             Sys  Name
Y    NODE/
N    users.dbf => +DATA2/NODE/DATAFILE/USERS.256.666713737
ASMCMD [+] > exit
[oracle@node1 ~]$ exit
exit

d. Rename datafile
SQL> ALTER DATABASE RENAME FILE
‘+DATA1/node/datafile/users.259.666184225’
TO ‘+DATA2/users.dbf’;

e. rman switch datafile
SQL> host
[oracle@node1 ~]$ rman target /
RMAN> SWITCH DATAFILE ‘+DATA2/users.dbf’ TO COPY;
datafile 4 switched to datafile copy “+DATA2/users.dbf”
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$ exit

f. recover datafile (optional)
SQL> RECOVER DATAFILE ‘+DATA2/users.dbf’;
Media recovery complete.

g. online datafile
SQL> ALTER DATABASE DATAFILE ‘+DATA2/users.dbf’ ONLINE;
Database altered.

4. new list datafile
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
——————————————————————————–
+DATA2/users.dbf
+DATA1/node/datafile/undotbs1.258.666184225
+DATA1/node/datafile/sysaux.257.666184225
+DATA1/node/datafile/system.256.666184225
+DATA1/node/datafile/example.267.666184357
+DATA1/node/datafile/undotbs2.268.666184499
+DATA1/node/datafile/soe.dbf
+DATA1/node/datafile/soeindex.dbf
8 rows selected.

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: