Murali Oracle DBA

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-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

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

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

801661731

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

RMAN image copy .. switching database to image copy backup.

SQL>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 23 15:09:10 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1828716544 bytes
Fixed Size                  2041368 bytes
Variable Size            1258297832 bytes
Database Buffers          553648128 bytes
Redo Buffers               14729216 bytes
Database mounted.

labrman01(MYDB) /ora_backup/backups/MYDB
rman>

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 23 15:10:13 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: MYDB (DBID=2563884143, not open)

RMAN> connect catalog rcat10g/rcat10g@rcat;

connected to recovery catalog database

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp"
datafile 2 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm"
datafile 3 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr"
datafile 4 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib"
datafile 5 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij"
datafile 6 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm"
datafile 8 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm"
datafile 9 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib"
datafile 10 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq"
starting full resync of recovery catalog
full resync complete

RMAN> run{
set until time "to_date('08/23/2010 15:00:00','mm/dd/yyyy hh24:mi:ss')";
recover database;
}

executing command: SET until clause

Starting recover at 23-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1092 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00010: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc
archive log thread 1 sequence 7 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000007_0727545786.arc
archive log thread 1 sequence 8 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000008_0727545786.arc
archive log filename=/adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-AUG-10

RMAN> run{
sql 'alter database open resetlogs';
}

sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;

List of Database Incarnations

DB Key IncKey DB Name DB ID       STATUS Reset SCN     Reset Time
------ ------ ------- ---------- ------- -------------- ----------
761    774    MYDB    2563884143 PARENT 1              29-OCT-07
761    762    MYDB    2563884143 PARENT 10513166137703 21-JUL-10
761    4636   MYDB    2563884143 PARENT 10513166276407 20-AUG-10
761    5187   MYDB    2563884143 PARENT 10513166280927 20-AUG-10
761    6336   MYDB    2563884143 PARENT 10513166285247 20-AUG-10
761    6786   MYDB    2563884143 CURRENT 10513166407407 23-AUG-10
761    1681   MYDB    2563884143 ORPHAN 10513167403561 20-AUG-10

Oracle DBA Murali

Tabs

Tuesday, January 13, 2015

How to Move datafile into Oracle ASM using RMAN

RMAN switch database to copy

Translate

Live Feed

Country Count