facebook twitter youtube facebook facebook facebook

E-Mail : info@askmlabs.com

Phone : +1.215.353.8306

Home » , » Moving/Renaming database files from ASM to file system

Moving/Renaming database files from ASM to file system

Written By askMLabs on Wednesday, January 26, 2011 | 4:36 PM

In the present article we will move/rename the datafiles which reside on ASM storage to normal file system. There are different ways to achieve this. We can use ASMCMD utility or RMAN  to move the datafiles from ASM to file system and then use the database rename command to update the moved location in the database.

With ASMCMD Utility :
SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
+DG1/testdb/datafile/ts1.256.739201677 AVAILABLE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
+DG1/testdb/datafile/ts1.256.739201677             AVAILABLE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp 'TS1.256.739201677' '/u01/datafiles/ts1.dbf'
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

SQL> alter database rename file '+DG1/testdb/datafile/TS1.256.739201677' to '/u01/datafiles/ts1.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/u01/datafiles/ts1.dbf AVAILABLE




Using RMAN with DB downtime.
SQL> create tablespace ts1 datafile '+DG1' size 20m;

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
+DG1/testdb/datafile/ts1.256.739203881 AVAILABLE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             486539676 bytes
Database Buffers          230686720 bytes
Redo Buffers                5419008 bytes
Database mounted.
SQL>

[oracle@dhcppc1 ~]$ asmcmd ls -lt +DG1/testdb/datafile/
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739203881
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 31 14:29:11 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115, not open)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to '/u01/datafiles/ts1.dbf'
2> ;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739203881
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T142940 RECID=6 STAMP=739204183
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 31-DEC-10

RMAN>

SQL> alter database rename file '+DG1/testdb/datafile/ts1.256.739203881' to '/u01/datafiles/ts1.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/u01/datafiles/ts1.dbf AVAILABLE




Using RMAN without DB downtime :
SQL> create tablespace ts1 datafile '+DG1' size 20m;

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
+DG1/testdb/datafile/ts1.256.739204397 AVAILABLE

SQL> alter tablespace ts1 offline;

Tablespace altered.

SQL>

[oracle@dhcppc1 datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 31 14:34:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2521935115)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to '/u01/datafiles/ts1.dbf';

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739204397
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T143435 RECID=7 STAMP=739204478
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-DEC-10

RMAN>

SQL> alter database rename file '+DG1/testdb/datafile/ts1.256.739204397' to '/u01/datafiles/ts1.dbf';

Database altered.

SQL> alter tablespace ts1 online;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name='TS1';

TABLESPACE_NAME STATUS
--------------- ---------
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/u01/datafiles/ts1.dbf AVAILABLE




Hope it helps

SRI
Share this article :

Related Articles By Category


Labels: ,

+ comments + 2 comments

Reply
Joseph
April 19, 2011 at 10:18 AM

I have a problem with my database. I am upgrading my oracle database from 11.1.0.6 to 11.1.0.7
During this process the server got shutdown accidentally.When i check the crs_stat it is showing all the instances as OFFLINE.When I try to start the database it is showing the error as:
CRS-0215: Could not start resource 'ora.ASDB.ASDB1.inst'.
PRKP-1001 : Error starting instance ASDB2 on node asdb2
CRS-0215: Could not start resource 'ora.ASDB.ASDB2.inst'.
When I try to recreate the control file it is showing error.
Even in the ASM all the files are lost it is showing an empty ORACLE_SID directory (no data files and control files).

Please let me know how to bring this instance up.

Thanks in advance.

Reply
yeah, I have the same problem..
September 28, 2011 at 10:42 AM

no more agree about that!...

[...]Moving/Renaming database files from ASM to file system « Oracle Database 11g Blog[...]...

Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger