RMAN> sql 'alter tablespace tts1 offline';
sql statement: alter tablespace tts1 offline
RMAN> sql 'alter tablespace tts1 online';
sql statement: alter tablespace tts1 online
RMAN> sql 'alter tablespace tts1 offline immediate';
sql statement: alter tablespace tts1 offline immediate
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/28/2013 14:02:55
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace tts1 online
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u02/oradata/TTS/tts1_01.bdf'
RMAN> recover tablespace tts1 ;
Starting recover at Mar 28 2013 14:03:11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at Mar 28 2013 14:03:12
RMAN> sql 'alter tablespace tts1 online';
sql statement: alter tablespace tts1 online
A more likely scenario in real world is particular disk/filesystem is not available, thus we need to relocate those affected data files.
1. backup the tablespace TTS1
RMAN> report schema;
Report of database schema for database with db_unique_name TTS
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** /u02/oradata/TTS/system01.dbf
2 600 SYSAUX *** /u02/oradata/TTS/sysaux01.dbf
3 370 UNDOTBS1 *** /u02/oradata/TTS/undotbs01.dbf
4 5 USERS *** /u02/oradata/TTS/users01.dbf
5 20 TTS1 *** /u02/oradata/TTS/tts1_01.bdf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/oradata/TTS/temp01.dbf
RMAN> backup tablespace tts1;
Starting backup at Mar 28 2013 14:29:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oradata/TTS/tts1_01.bdf
channel ORA_DISK_1: starting piece 1 at Mar 28 2013 14:29:59
channel ORA_DISK_1: finished piece 1 at Mar 28 2013 14:30:00
piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG201303 28T142958_8o7rv78f_.bkp tag=TAG20130328T142958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at Mar 28 2013 14:30:00
Starting Control File and SPFILE Autobackup at Mar 28 2013 14:30:00
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_03_28/o1_mf_s_811261800_8o 7rv8gp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at Mar 28 2013 14:30:01
RMAN> list backup of tablespace tts1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
24 Incr 0 204.51M DISK 00:01:20 Mar 27 2013 15:27:08
BP Key: 24 Status: AVAILABLE Compressed: YES Tag: TAG20130327T152547
Piece Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_03_27/o1_mf_nnnd0_TAG20130327T152547_8o57qx5l_.bkp
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
5 0 Incr 1021037 Mar 27 2013 15:25:49 /u02/oradata/TTS/tts1_01.bdf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
28 Incr 1 312.00K DISK 00:00:31 Mar 27 2013 15:32:29
BP Key: 28 Status: AVAILABLE Compressed: YES Tag: TAG20130327T153155
Piece Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_03_27/o1_mf_nnnd1_TAG20130327T153155_8o583gsl_.bkp
List of Datafiles in backup set 28
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
5 1 Incr 1021321 Mar 27 2013 15:31:58 /u02/oradata/TTS/tts1_01.bdf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
33 Full 2.05M DISK 00:00:01 Mar 28 2013 14:30:00
BP Key: 33 Status: AVAILABLE Compressed: YES Tag: TAG20130328T142958
Piece Name: /u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T142958_8o7rv78f_.bkp
List of Datafiles in backup set 33
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
5 Full 1101322 Mar 28 2013 14:29:59 /u02/oradata/TTS/tts1_01.bdf
2. Simulate the disk is gone.
RMAN> sql 'alter tablespace tts1 offline immediate';
sql statement: alter tablespace tts1 offline immediate
RMAN> set newname for datafile '/u02/oradata/TTS/tts1_01.bdf' to '/home/oracle/tts1_01.dbf';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03031: this option of set command needs to be used inside a run block
RMAN> run {
2> set newname for datafile '/u02/oradata/TTS/tts1_01.bdf' to '/home/oracle/tts1_01.dbf';
3> restore tablespace tts1;
4> switch datafile all;
5> recover tablespace tts1;
6> sql 'alter tablespace tts1 online ' ;
7> }
executing command: SET NEWNAME
Starting restore at Mar 28 2013 14:33:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/tts1_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T142958_8o7rv78f_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T142958_8o7rv78f_.bkp tag=TAG20130328T142958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at Mar 28 2013 14:33:38
datafile 5 switched to datafile copy
input datafile copy RECID=24 STAMP=811262018 file name=/home/oracle/tts1_01.dbf
Starting recover at Mar 28 2013 14:33:38
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at Mar 28 2013 14:33:38
sql statement: alter tablespace tts1 online
RMAN> report schema;
Report of database schema for database with db_unique_name TTS
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** /u02/oradata/TTS/system01.dbf
2 600 SYSAUX *** /u02/oradata/TTS/sysaux01.dbf
3 370 UNDOTBS1 *** /u02/oradata/TTS/undotbs01.dbf
4 5 USERS *** /u02/oradata/TTS/users01.dbf
5 20 TTS1 *** /home/oracle/tts1_01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/oradata/TTS/temp01.dbf
3. after disaster, put the datafile to original location;
RMAN> backup as copy datafile 5 format '/u02/oradata/TTS/tts1_01.bdf';
Starting backup at Mar 28 2013 14:37:41
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/tts1_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/28/2013 14:37:42
ORA-19504: failed to create file "/u02/oradata/TTS/tts1_01.bdf"
ORA-27038: created file already exists
Additional information: 1
RMAN> host ' rm /u02/oradata/TTS/tts1_01.bdf ';
host command complete
RMAN> backup as copy datafile 5 format '/u02/oradata/TTS/tts1_01.bdf';
Starting backup at Mar 28 2013 14:37:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/tts1_01.dbf
output file name=/u02/oradata/TTS/tts1_01.bdf tag=TAG20130328T143759 RECID=26 STAMP=811262280
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at Mar 28 2013 14:38:00
Starting Control File and SPFILE Autobackup at Mar 28 2013 14:38:00
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_03_28/o1_mf_s_811262280_8o7sb92t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at Mar 28 2013 14:38:01
RMAN> sql 'alter database datafile 5 offline ' ;
sql statement: alter database datafile 5 offline
RMAN> switch datafile 5 to copy;
datafile 5 switched to datafile copy "/u02/oradata/TTS/tts1_01.bdf"
RMAN> sql 'alter database datafile 5 online ' ;
sql statement: alter database datafile 5 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/28/2013 14:40:30
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 5 online
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u02/oradata/TTS/tts1_01.bdf'
RMAN> recover datafile 5 ;
Starting recover at Mar 28 2013 14:40:47
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at Mar 28 2013 14:40:47
RMAN> sql 'alter database datafile 5 online ' ;
sql statement: alter database datafile 5 online
RMAN> report schema;
Report of database schema for database with db_unique_name TTS
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** /u02/oradata/TTS/system01.dbf
2 600 SYSAUX *** /u02/oradata/TTS/sysaux01.dbf
3 370 UNDOTBS1 *** /u02/oradata/TTS/undotbs01.dbf
4 5 USERS *** /u02/oradata/TTS/users01.dbf
5 20 TTS1 *** /u02/oradata/TTS/tts1_01.bdf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/oradata/TTS/temp01.dbf
RMAN> exit
Recovery Manager complete.
4. Verification
orarac2poc:TTS:/home/oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 28 14:42:10 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 table_name ,owner from dba_TABLEs where tablespace_name='TTS1';
TABLE_NAME OWNER
------------------------------ ------------------------------
T01 TRANP
T02 TRANP
IDX03 TRANP
SYS@TTS> select count(*) from tranp.t01;
COUNT(*)
----------
65949
Popular Posts
-
configured "CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK; " when RMAN> delete noprompt archivelog u...
-
I have one PT env using storage sync for database sync. Thought need to re-register DB after DB refresh, so unregister and register again....
-
[symptoms] orarac2poc:ORCL2:/home/oracle> . ./oms_agent_env orarac2poc:ORCL2:/home/oracle> $AGENT_HOME/bin/emctl start agent Oracl...
-
SQL> select * from dba_data_files where tablespace_name='TESTBS'; FILE_NAME ...
-
As I have CPU issue with physical standby database in previous post, I have to remove it before I have the solution. In 12c Enterprise Man... -
after rounds of recovery testing, I believer there are tons of obsolete files in FRA. There are some stubborn datafile copy can't be de...
-
This installation extended to my home hour, since both Postgresql and Zabbix are new to me. Followed few articles without much difficulty.... -
Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second f...
-
RMAN> show all; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # de...
-
I hit the error when using RMAN duplicate command to create a physical standby database, with purpose of using same directory structure in...