Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Monday, December 10, 2012

oracle Move datafile into ASM


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

FILE#    CREATION_TIME        NAME                TS_NAME        STATUS
127    12-10-2012 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-2012 05:06:27    +apps_ts_tx_idx.480.801661731   APPS_TS_TX_IDX    RECOVER


>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-2012 05:06:27    +apps_ts_tx_idx.480.801661731   APPS_TS_TX_IDX    OFFLINE


SQL> alter database datafile 127 online;

Database altered.

SQL>

FILE#    CREATION_TIME        NAME                TS_NAME        STATUS
127    12-10-2012 05:06:27    +apps_ts_tx_idx.480.801661731   APPS_TS_TX_IDX    ONLINE


DBNAMEb01cdp(DBNAME1)  /icm01/u0001/app/oracle/product/11.2.0/dbhome_1/dbs
>rm DBNAME_DATA_01

No comments: