1 . Choose TTS1 tablespace for testing, datafile id 5, and create database copy .
SYS@TTS> select file_id, file_name,tablespace_name from dba_data_files;
FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
1
/u02/oradata/TTS/system01.dbf
SYSTEM
2
/u02/oradata/TTS/sysaux01.dbf
SYSAUX
3
/u02/oradata/TTS/undotbs01.dbf
UNDOTBS1
4
/u02/oradata/TTS/users01.dbf
USERS
5
/u02/oradata/TTS/tts1_01.dbf
TTS1
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/dbs> rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 26 15:36:14 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TTS (DBID=1769654909)
RMAN> backup as copy database;
Starting backup at 26-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/TTS/system01.dbf
output file name=/u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-SYSTEM_FNO-1_0ao5givs tag=TAG20130326T153628 RECID=6 STAMP=811093008
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/TTS/sysaux01.dbf
output file name=/u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-SYSAUX_FNO-2_0bo5gj0l tag=TAG20130326T153628 RECID=7 STAMP=811093044
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/TTS/undotbs01.dbf
output file name=/u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-UNDOTBS1_FNO-3_0co5gj1p tag=TAG20130326T153628 RECID=8 STAMP=811093071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u02/oradata/TTS/tts1_01.dbf
output file name=/u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-TTS1_FNO-5_0do5gj2j tag=TAG20130326T153628 RECID=9 STAMP=811093076
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u02/oradata/TTS/users01.dbf
output file name=/u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-USERS_FNO-4_0eo5gj2m tag=TAG20130326T153628 RECID=10 STAMP=811093078
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-MAR-13
Starting Control File and SPFILE Autobackup at 26-MAR-13
piece handle=/u01/app/oracle/product/11.2.0/db_2/dbs/c-1769654909-20130326-01 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-13
RMAN> exit
Recovery Manager complete.
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/dbs> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 26 15:43:56 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@TTS> select * from dba_users where username='TRANP';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
PASSWORD E AUTHENTI
-------- - --------
TRANP 62 OPEN 17-SEP-13
TTS1 TEMP 21-MAR-13 DEFAULT
DEFAULT_CONSUMER_GROUP
10G 11G N PASSWORD
SYS@TTS> grant select any dictionary to tranp;
Grant succeeded.
3. backup datafile 5 copy .
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/dbs> rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 26 15:46:20 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TTS (DBID=1769654909)
RMAN> backup as copy datafile 5 format '/home/oracle/tts1_copy.dbf';
Starting backup at 26-MAR-13
using target database control file instead of recovery catalog
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=00005 name=/u02/oradata/TTS/tts1_01.dbf
output file name=/home/oracle/tts1_copy.dbf tag=TAG20130326T154704 RECID=11 STAMP=811093624
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-MAR-13
Starting Control File and SPFILE Autobackup at 26-MAR-13
piece handle=/u01/app/oracle/product/11.2.0/db_2/dbs/c-1769654909-20130326-02 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-13
RMAN> exit
Recovery Manager complete.
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/dbs> ls -l /home/oracle/tts1_copy.dbf
-rw-r----- 1 oracle asmadmin 20979712 Mar 26 15:47 /home/oracle/tts1_copy.dbf
4. make some changes to the tablespace.
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/dbs> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 26 15:47:39 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@TTS> conn tranp/oracle123
Connected.
TRANP@TTS> create table idx03 as select * from dba_indexes;
Table created.
TRANP@TTS> conn / as sysdba
Connected.
SYS@TTS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------
NEXT_TIME
---------
1 1 7 52428800 512 1 YES INACTIVE 977828 26-MAR-13 977857
26-MAR-13
2 1 8 52428800 512 1 YES INACTIVE 977857 26-MAR-13 977892
26-MAR-13
3 1 9 52428800 512 1 NO CURRENT 977892 26-MAR-13 2.8147E+14
SYS@TTS> alter system switch logfile;
System altered.
SYS@TTS> alter system switch logfile;
System altered.
SYS@TTS> alter system switch logfile;
System altered.
SYS@TTS> alter system switch logfile;
System altered.
5. offline the tablespace and switch to datafilecopy
SYS@TTS> alter tablespace tts1 offline;
Tablespace altered.
SYS@TTS> exit
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/dbs> cd /u02/oradata/TTS
orarac2poc:TTS:/u02/oradata/TTS> ls
control01.ctl redo02.log system01.dbf undotbs01.dbf
control02.ctl redo03.log temp01.dbf users01.dbf
redo01.log sysaux01.dbf tts1_01.dbf
orarac2poc:TTS:/u02/oradata/TTS> mv tts1_01.dbf tts1_01.dbf.bak
orarac2poc:TTS:/u02/oradata/TTS> rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 26 15:54:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TTS (DBID=1769654909)
RMAN> list copy of datafile 5;
using target database control file instead of recovery catalog
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - -------------------- ---------- --------------------
11 5 A Mar 26 2013 15:47:04 978864 Mar 26 2013 15:47:04
Name: /home/oracle/tts1_copy.dbf
Tag: TAG20130326T154704
9 5 A Mar 26 2013 15:37:56 714646 Mar 21 2013 10:25:00
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-TTS1_FNO-5_0do5gj2j
Tag: TAG20130326T153628
RMAN> list copy ;
specification does not match any control file copy in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - -------------------- ---------- --------------------
6 1 A Mar 26 2013 15:36:48 977990 Mar 26 2013 15:36:28
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-SYSTEM_FNO-1_0ao5givs
Tag: TAG20130326T153628
7 2 A Mar 26 2013 15:37:24 977999 Mar 26 2013 15:36:53
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-SYSAUX_FNO-2_0bo5gj0l
Tag: TAG20130326T153628
8 3 A Mar 26 2013 15:37:51 978014 Mar 26 2013 15:37:30
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-UNDOTBS1_FNO-3_0co5gj1p
Tag: TAG20130326T153628
10 4 A Mar 26 2013 15:37:58 978024 Mar 26 2013 15:37:58
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-USERS_FNO-4_0eo5gj2m
Tag: TAG20130326T153628
11 5 A Mar 26 2013 15:47:04 978864 Mar 26 2013 15:47:04
Name: /home/oracle/tts1_copy.dbf
Tag: TAG20130326T154704
9 5 A Mar 26 2013 15:37:56 714646 Mar 21 2013 10:25:00
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/data_D-TTS_I-1769654909_TS-TTS1_FNO-5_0do5gj2j
Tag: TAG20130326T153628
List of Archived Log Copies for database with db_unique_name TTS
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - --------------------
1 1 6 A Mar 26 2013 03:00:40
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch1_6_810655966.dbf
2 1 7 A Mar 26 2013 15:30:11
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_7_810655966.dbf
3 1 8 A Mar 26 2013 15:31:26
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_8_810655966.dbf
4 1 9 A Mar 26 2013 15:32:58
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_9_810655966.dbf
5 1 10 A Mar 26 2013 15:48:58
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_10_810655966.dbf
6 1 11 A Mar 26 2013 15:48:59
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_11_810655966.dbf
7 1 12 A Mar 26 2013 15:49:01
Name: /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_12_810655966.dbf
RMAN> sql 'alter tablespace tts1 online';
sql statement: alter tablespace tts1 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/26/2013 15:55:23
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace tts1 online
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/TTS/tts1_01.dbf'
RMAN> switch datafile 5 to copy ;
datafile 5 switched to datafile copy "/home/oracle/tts1_copy.dbf"
RMAN> recover datafile 5;
Starting recover at Mar 26 2013 15:59:33
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_9_810655966.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_10_810655966.dbf
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_11_810655966.dbf
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_12_810655966.dbf
archived log file name=/u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_9_810655966.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/11.2.0/db_2/dbs/arch/1_10_810655966.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at Mar 26 2013 15:59:34
RMAN> sql 'alter tablespace tts1 online';
sql statement: alter tablespace tts1 online
RMAN> exit
Recovery Manager complete.
6. verification
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 26 16:00:06 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@TTS> conn tranp/oracle123
Connected.
TRANP@TTS> select table_name from user_tables;
TABLE_NAME
------------------------------
T01
T02
IDX03
TRANP@TTS> select count(*) from dba_indexes;
COUNT(*)
----------
3491
TRANP@TTS> select count(*) from idx03;
COUNT(*)
----------
3491
TRANP@TTS> select file_id,file_name, tablespace_name from dba_data_files;
FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
1
/u02/oradata/TTS/system01.dbf
SYSTEM
2
/u02/oradata/TTS/sysaux01.dbf
SYSAUX
3
/u02/oradata/TTS/undotbs01.dbf
UNDOTBS1
4
/u02/oradata/TTS/users01.dbf
USERS
5
/home/oracle/tts1_copy.dbf
TTS1
7. simulate to switch back to the datafile's orginal location after disaster.
TRANP@TTS> conn / as sysdba
Connected.
SYS@TTS> alter tablespace tts1 offline;
Tablespace altered.
SYS@TTS> !cp -p /home/oracle/tts1_copy.dbf /u02/oradata/TTS/tts1_01.bdf
SYS@TTS> !ls -l /u02/oradata/TTS/tts1*
-rw-r----- 1 oracle oinstall 20979712 Mar 26 16:01 /u02/oradata/TTS/tts1_01.bdf
-rw-r----- 1 oracle asmadmin 20979712 Mar 26 15:50 /u02/oradata/TTS/tts1_01.dbf.bak
SYS@TTS> alter database rename file '/home/oracle/tts1_copy.dbf' to '/u02/oradata/TTS/tts1_01.bdf';
Database altered.
SYS@TTS> select file_id,file_name, tablespace_name from dba_data_files where tablespace_name='TTS1';
FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
5
/u02/oradata/TTS/tts1_01.bdf
TTS1
SYS@TTS> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SYS@TTS> alter tablespace tts1 online;
Tablespace altered.
SYS@TTS> select count(*) from tranp.idx03;
COUNT(*)
----------
3491
SYS@TTS> select status from dba_tablespaces where tablespace_name='TTS1';
STATUS
---------
ONLINE
Optionally, do the following to put the files back into their original location:
(1). Create an image copy of the data file in the original location.
(2). Take data files offline.
(3). SWITCH TO ... COPY
(4). Bring data files online.
References:
