Restoring a Corrupted Non System Tablespace using RMAN WITHOUT SHUTTING DOWN THE DATABASE

Assumptions
1. Non-system tablespace is corrupted or the datafile of the tablespace is deleted/corrupted.
2. Required archivelogs (‘sysdate – x’) are present in the archive log dest file system.

Restore Tablespace
run{
allocate channel ch1 type ‘SBT_TAPE’;
set newname for datafile 5 to ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’;
RESTORE TABLESPACE BECUBIC_INDEX;
switch datafile 5;
release channel ch1;
}

RMAN> run{
allocate channel ch1 type ‘SBT_TAPE’;
set newname for datafile 5 to ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’;
RESTORE TABLESPACE BECUBIC_INDEX;
switch datafile 5;
release channel ch1;
}2> 3> 4> 5> 6> 7>

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: sid=143 devtype=SBT_TAPE
channel ch1: NMO v4.5.0.0

executing command: SET NEWNAME

Starting restore at 23-OCT-09

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /restore1/oracle/SID/SID_BECUBIC_INDEX1.ora
channel ch1: reading from backup piece SID_20091021_700874809_1551_1.dbf
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/23/2009 12:54:50
ORA-19870: error reading backup piece SID_20091021_700874809_1551_1.dbf
ORA-19573: cannot obtain exclusive enqueue for datafile 5

Alert Log Errors

Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 12:55:36 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
Fri Oct 23 12:55:36 2009
File 201 not verified due to error ORA-01157
Fri Oct 23 12:55:36 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 12:55:36 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
File 201 not verified due to error ORA-01157
Fri Oct 23 12:55:36 2009
ORA-1116 signalled during: alter tablespace BECUBIC_INDEX offline…
Fri Oct 23 12:58:07 2009
alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ offline
Fri Oct 23 12:58:07 2009
Completed: alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ offline
Fri Oct 23 12:58:21 2009
alter tablespace BECUBIC_INDEX offline
Fri Oct 23 12:58:21 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 12:58:21 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 12:58:21 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
Fri Oct 23 12:58:21 2009
File 201 not verified due to error ORA-01157
Fri Oct 23 12:58:21 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 12:58:21 2009
Errors in file /restore1/oracle/SID/bdump/SID_dbw0_7245958.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 – see DBWR trace file
ORA-01110: data file 201: ‘/data/oracle/t01/SID/SID_temp_01.dbf’
File 201 not verified due to error ORA-01157
Fri Oct 23 12:58:21 2009
ORA-1191 signalled during: alter tablespace BECUBIC_INDEX offline…
Fri Oct 23 12:59:10 2009
Full restore complete of datafile 5 /restore1/oracle/SID/SID_BECUBIC_INDEX1.ora. Elapsed time: 0:00:06
checkpoint is 22678264

Offline The Tablespace

SQL> alter tablespace BECUBIC_INDEX offline;
alter tablespace BECUBIC_INDEX offline
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

SQL> alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ offline;

Database altered.

SQL> alter tablespace BECUBIC_INDEX offline;
alter tablespace BECUBIC_INDEX offline
*
ERROR at line 1:
ORA-01191: file 5 is already offline – cannot do a normal offline
ORA-01110: data file 5: ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’

Restore Again

allocated channel: ch1
channel ch1: sid=143 devtype=SBT_TAPE
channel ch1: NMO v4.5.0.0

executing command: SET NEWNAME

Starting restore at 23-OCT-09

released channel: ch1

Recovery

sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Oct 23 12:55:15 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning and Data Mining options

SQL> alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ offline;

Database altered.

sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on Fri Oct 23 12:59:29 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning and Data Mining options

SQL> recover tablespace BECUBIC_INDEX;
ORA-00279: change 22678264 generated at 10/21/2009 23:26:50 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/SID_1_679_659723824.dbf
ORA-00280: change 22678264 for thread 1 is in sequence #679

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ online;
alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’
SQL> recover datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’;
ORA-00279: change 22678264 generated at 10/21/2009 23:26:50 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/SID_1_679_659723824.dbf
ORA-00280: change 22678264 for thread 1 is in sequence #679

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 22678301 generated at 10/21/2009 23:28:11 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/SID_1_680_659723824.dbf
ORA-00280: change 22678301 for thread 1 is in sequence #680
ORA-00278: log file ‘/restore1/oracle/SID/SID_1_679_659723824.dbf’ no
longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 22678307 generated at 10/22/2009 14:46:07 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/SID_1_1_700929967.dbf
ORA-00280: change 22678307 for thread 1 is in sequence #1
ORA-00278: log file ‘/restore1/oracle/SID/SID_1_680_659723824.dbf’ no
longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 22713455 generated at 10/23/2009 12:51:36 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/SID_1_2_700929967.dbf
ORA-00280: change 22713455 for thread 1 is in sequence #2
ORA-00278: log file ‘/restore1/oracle/SID/SID_1_1_700929967.dbf’ no
longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

Bringing the Tablespace online again
SQL> alter database datafile ‘/restore1/oracle/SID/SID_BECUBIC_INDEX1.ora’ online;

Database altered.

SQL> alter tablespace BECUBIC_INDEX online;

Tablespace altered.

SQL> select TABLESPACE_NAME,status from dba_tablespaces where TABLESPACE_NAME=’BECUBIC_INDEX’;

TABLESPACE_NAME STATUS
—————————— ———
BECUBIC_INDEX ONLINE

Doyensys

A technology service company that provides innovative solutions around the oracle platform With Laser focus on customer delight as the primary success parameter.