How to move a datafile that was created on the OS to ASM.
Issue:
Datafile created on the OS instead of in ASM
Given:
RAC 3 nodes
tablespace has multiple datafiles
Check the status of the file:
select df.file#,to_char(df.creation_time,'mm-dd-yyyy hh24:mi:ss') created,df.name,ts.name,df.status from v$datafile df,v$tablespace ts where df.ts#=ts.ts# and df.file#=127;
RMAN> connect target /
connected to target database: DBNAME (DBID=2919937482)
RMAN> copy datafile 127 to '+DBNAME_DATA_01';
Starting backup at 10-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=854 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00127 name=/icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs/DBNAME_DATA_01
output file name=+DBNAME_DATA_01/DBNAME/datafile/apps_ts_tx_idx.480.801661731 tag=TAG20121210T114844 RECID=54 STAMP=801661781
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
Finished backup at 10-DEC-12
Starting Control File and SPFILE Autobackup at 10-DEC-12
piece handle=+DBNAME_FRA_01/DBNAME/autobackup/2012_12_10/s_801661785.5682.801661787 comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-12
RMAN> switch datafile 127 to copy;
datafile 127 switched to datafile copy "+DBNAME_DATA_01/DBNAME/datafile/apps_ts_tx_idx.480.801661731"
RMAN> exit
Recovery Manager complete.
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:50:32 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
>rman
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 10 11:51:22 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: DBNAME (DBID=2919937482)
RMAN> recover datafile 127;
Starting recover at 10-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3679 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3955 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=4524 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=4799 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=5082 instance=DBNAME1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished recover at 10-DEC-12
RMAN> exit
Recovery Manager complete.
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:52:06 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter database datafile 127 online;
Database altered.
SQL>
DBNAMEb01cdp(DBNAME1) /icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs
>rm DBNAME_DATA_01
Issue:
Datafile created on the OS instead of in ASM
Given:
RAC 3 nodes
tablespace has multiple datafiles
Check the status of the file:
select df.file#,to_char(df.creation_time,'mm-dd-yyyy hh24:mi:ss') created,df.name,ts.name,df.status from v$datafile df,v$tablespace ts where df.ts#=ts.ts# and df.file#=127;
FILE# CREATION_TIME NAME
TS_NAME
STATUS
----- ------------------- --------------------------- -------------- ------
127 12-10-2015 05:06:27 dbhome_1/dbs/DBNAME_DATA_01 APPS_TS_TX_IDX ONLINE
----- ------------------- --------------------------- -------------- ------
127 12-10-2015 05:06:27 dbhome_1/dbs/DBNAME_DATA_01 APPS_TS_TX_IDX ONLINE
RMAN> connect target /
connected to target database: DBNAME (DBID=2919937482)
RMAN> copy datafile 127 to '+DBNAME_DATA_01';
Starting backup at 10-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=854 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00127 name=/icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs/DBNAME_DATA_01
output file name=+DBNAME_DATA_01/DBNAME/datafile/apps_ts_tx_idx.480.801661731 tag=TAG20121210T114844 RECID=54 STAMP=801661781
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
Finished backup at 10-DEC-12
Starting Control File and SPFILE Autobackup at 10-DEC-12
piece handle=+DBNAME_FRA_01/DBNAME/autobackup/2012_12_10/s_801661785.5682.801661787 comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-12
RMAN> switch datafile 127 to copy;
datafile 127 switched to datafile copy "+DBNAME_DATA_01/DBNAME/datafile/apps_ts_tx_idx.480.801661731"
RMAN> exit
Recovery Manager complete.
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:50:32 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FILE# CREATION_TIME
NAME
TS_NAME
STATUS
----- ------------------- ------------------------ ---------------- -------
127 12-10-2015 05:06:27 +apps_ts_tx_idx.480. APPS_TS_TX_IDX RECOVER
----- ------------------- ------------------------ ---------------- -------
127 12-10-2015 05:06:27 +apps_ts_tx_idx.480. APPS_TS_TX_IDX RECOVER
801661731
>rman
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 10 11:51:22 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: DBNAME (DBID=2919937482)
RMAN> recover datafile 127;
Starting recover at 10-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3679 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3955 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=4524 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=4799 instance=DBNAME1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=5082 instance=DBNAME1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished recover at 10-DEC-12
RMAN> exit
Recovery Manager complete.
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:52:06 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FILE# CREATION_TIME
NAME
TS_NAME
STATUS
----- ------------------- ------------------------ ---------------- -------
127 12-10-2015 05:06:27 +apps_ts_tx_idx.480. APPS_TS_TX_IDX OFFLINE
----- ------------------- ------------------------ ---------------- -------
127 12-10-2015 05:06:27 +apps_ts_tx_idx.480. APPS_TS_TX_IDX OFFLINE
801661731
SQL> alter database datafile 127 online;
Database altered.
SQL>
FILE# CREATION_TIME
NAME
TS_NAME
STATUS
----- ------------------- ------------------------ ---------------- -------
127 12-10-2015 05:06:27 +apps_ts_tx_idx.480. APPS_TS_TX_IDX ONLINE
----- ------------------- ------------------------ ---------------- -------
127 12-10-2015 05:06:27 +apps_ts_tx_idx.480. APPS_TS_TX_IDX ONLINE
801661731
DBNAMEb01cdp(DBNAME1) /icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs
>rm DBNAME_DATA_01