DBA Hub

📋Steps in this guide1/5

Move datafile from file system to ASM disk in 11g DBACLASS

Move datafile from file system to ASM disk in 11g. datafile mistakenly create in local filesystem instead of ASM. To fix it follow below steps.

oracle clusteringintermediate
by OracleDba
14 views
1

Overview

PROBLEM: One of the junior DBA mistakenly created a datafile in local filesystem instead of ASM disk group in RAC database 11g. But he is quick to realize his mistake. Below steps need to be followed to move the datafile to ASM disk group . SOLUTION :
2

Section 2

1. Make the datafile offline; 2. Connect to RMAN and copy datafile to ASM DISKGROUP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL>  select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+NEWTST/TESTDB2/DATAFILE/system.260.934026715
+NEWTST/TESTDB2/DATAFILE/sysaux.261.934026717
+NEWTST/TESTDB2/DATAFILE/undotbs1.262.934026719
+NEWTST/TESTDB2/DATAFILE/undotbs2.264.934026727
+NEWTST/TESTDB2/DATAFILE/users.265.934026729
/export/home/oracle/user02.dbf

alter database datafile '/export/home/oracle/user01.dbf' offline;

RMAN>  copy datafile '/export/home/oracle/user02.dbf' to '+NEWTST/TESTDB2/DATAFILE/user02.db';

Starting backup at 12-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 instance=TESTDB21 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/export/home/oracle/user02.dbf
output file name=+NEWTST/TESTDB2/DATAFILE/user02.db tag=TAG20170212T182216 RECID=1 STAMP=935778137
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-FEB-17
3

Section 3

3. Rename the datafile in control file 4. Switch the data file to copy

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
SQL> alter database rename file '/export/home/oracle/user02.dbf' to '+NEWTST/TESTDB2/DATAFILE/user02.db';

Database altered.

RMAN> SWITCH DATAFILE '+NEWTST/TESTDB2/DATAFILE/user02.db' to copy;

using target database control file instead of recovery catalog
datafile 7 switched to datafile copy "+NEWTST/TESTDB2/DATAFILE/user02.db"
4

Section 4

5. Recover the datafile 6. Make the datafile online:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
RMAN> recover datafile '+NEWTST/TESTDB2/DATAFILE/user02.db';

Starting recover at 12-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 instance=TESTDB21 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 12-FEB-17

SQL> alter database datafile '+NEWTST/TESTDB2/DATAFILE/user02.db' online;
Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+NEWTST/TESTDB2/DATAFILE/system.260.934026715
+NEWTST/TESTDB2/DATAFILE/sysaux.261.934026717
+NEWTST/TESTDB2/DATAFILE/undotbs1.262.934026719
+NEWTST/TESTDB2/DATAFILE/undotbs2.264.934026727
+NEWTST/TESTDB2/DATAFILE/users.265.934026729
+NEWTST/TESTDB2/DATAFILE/user02.db

7 rows selected.
5

Section 5

In oracle 12c, we can move the datafile online with one line.

Code/Command (click line numbers to comment):

1
2
3
SQL>  alter database move datafile  '/export/home/oracle/user01.dbf' to '+NEWTST/TESTDB2/DATAFILE/user01.dbf';

Database altered.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!