Mani's Blog

December 6, 2011

Restore READ ONLY Tablespace using RMAN

Filed under: Uncategorized — mani @ 11:11 pm

Restore READ ONLY Tablespace using RMAN

We have a database with one tablespace in readonly.   Ran into following issue while opening up the database.

Restoring the database:

RMAN> alter database mount;

database mounted

RMAN> run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
set until sequence= 11785;
restore database;
}
2> 3> 4> 5> 6> 7> 8>
allocated channel: d1
channel d1: sid=542 devtype=DISK

allocated channel: d2
channel d2: sid=1102 devtype=DISK

allocated channel: d3
channel d3: sid=537 devtype=DISK

allocated channel: d4
channel d4: sid=1099 devtype=DISK

executing command: SET until clause

Starting restore at 06-DEC-11
Starting implicit crosscheck backup at 06-DEC-11
Crosschecked 522 objects
Crosschecked 1 objects
Finished implicit crosscheck backup at 06-DEC-11

Starting implicit crosscheck copy at 06-DEC-11
Finished implicit crosscheck copy at 06-DEC-11

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /local/data/oracle/nfs/DATAMART/autobackup/2011_12_06/o1_mf_s_769191237_7fwht7qw_.bkp

datafile 7 not processed because file is read-only
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +XN_DATA/dsp0001/datafile/xn_etl.259.663804029
restoring datafile 00008 to +XN_DATA/dsp0001/datafile/xn_fido.471.677889679
channel d1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp
channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +XN_DATA/dsp0001/datafile/undotbs1.257.663802853
restoring datafile 00003 to +XN_DATA/dsp0001/datafile/sysaux.256.663802853
channel d2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp
channel d3: starting datafile backupset restore
channel d3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +XN_DATA/dsp0001/datafile/system.258.663802853
restoring datafile 00006 to +XN_DATA/dsp0001/datafile/xn_test_etl.260.663804035
channel d3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp
channel d4: starting datafile backupset restore
channel d4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +XN_DATA/dsp0001/datafile/users.261.663802853
channel d4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp
channel d4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp tag=TAG20111205T235251
channel d4: restore complete, elapsed time: 00:00:01
channel d1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp tag=TAG20111205T235251
channel d1: restore complete, elapsed time: 00:00:26
channel d2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp tag=TAG20111205T235251
channel d2: restore complete, elapsed time: 00:02:11
channel d3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp tag=TAG20111205T235251
channel d3: restore complete, elapsed time: 00:16:17
Finished restore at 06-DEC-11
released channel: d1
released channel: d2
released channel: d3
released channel: d4

Recovering database:

RMAN> recover database;

Starting recover at 06-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1098 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=535 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=534 devtype=DISK
datafile 7 not processed because file is read-only

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_3: starting archive log restore to default destination
channel ORA_DISK_4: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11784
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftqks7j_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=11785
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftynqqx_.bkp
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=11786
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fv5o1fd_.bkp
channel ORA_DISK_4: restoring archive log
archive log thread=1 sequence=11787
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvdmzwf_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftqks7j_.bkp tag=BKP_DATAMART_ARCHL_120611121301
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011784_0001_697357424.oarl thread=1 sequence=11784
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftynqqx_.bkp tag=BKP_DATAMART_ARCHL_120611021300
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011785_0001_697357424.oarl thread=1 sequence=11785
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fv5o1fd_.bkp tag=BKP_DATAMART_ARCHL_120611041302
channel ORA_DISK_3: restore complete, elapsed time: 00:00:04
archive log filename=/arch/DATAMART/5641608e-0000011786_0001_697357424.oarl thread=1 sequence=11786
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvdmzwf_.bkp tag=BKP_DATAMART_ARCHL_120611061301
channel ORA_DISK_4: restore complete, elapsed time: 00:00:05
archive log filename=/arch/DATAMART/5641608e-0000011787_0001_697357424.oarl thread=1 sequence=11787
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_3: starting archive log restore to default destination
channel ORA_DISK_4: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11788
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvmo3cv_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=11789
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvtp2hs_.bkp
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=11790
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw1q1d4_.bkp
channel ORA_DISK_4: restoring archive log
archive log thread=1 sequence=11791
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw8r13r_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvmo3cv_.bkp tag=BKP_DATAMART_ARCHL_120611081305
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011788_0001_697357424.oarl thread=1 sequence=11788
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvtp2hs_.bkp tag=BKP_DATAMART_ARCHL_120611101303
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011789_0001_697357424.oarl thread=1 sequence=11789
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw1q1d4_.bkp tag=BKP_DATAMART_ARCHL_120611121302
channel ORA_DISK_3: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011790_0001_697357424.oarl thread=1 sequence=11790
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw8r13r_.bkp tag=BKP_DATAMART_ARCHL_120611021301
channel ORA_DISK_4: restore complete, elapsed time: 00:00:04
archive log filename=/arch/DATAMART/5641608e-0000011791_0001_697357424.oarl thread=1 sequence=11791
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11792
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fwhs19r_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fwhs19r_.bkp tag=BKP_DATAMART_ARCHL_120611041300
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011792_0001_697357424.oarl thread=1 sequence=11792
archive log filename=/arch/DATAMART/5641608e-0000011793_0001_697357424.oarl thread=1 sequence=11793
unable to find archive log
archive log thread=1 sequence=11794
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2011 17:59:45
RMAN-06054: media recovery requesting unknown log: thread 1 seq 11794 lowscn 1558554671

RMAN>

Issue:

Opening the database:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/06/2011 18:01:05
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+XN_DATA/dsp0001/datafile/xn_datamart.273.665597367’

Solution:

Ran the restore again with check readonly option.

RMAN> restore database check readonly;

Starting restore at 06-DEC-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +XN_DATA/datamart/datafile/xn_etl.269.769192545
restoring datafile 00008 to +XN_DATA/datamart/datafile/xn_fido.271.769192545
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +XN_DATA/datamart/datafile/undotbs1.270.769192545
restoring datafile 00003 to +XN_DATA/datamart/datafile/sysaux.263.769192547
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp
channel ORA_DISK_3: starting datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +XN_DATA/datamart/datafile/system.260.769192547
restoring datafile 00006 to +XN_DATA/datamart/datafile/xn_test_etl.272.769192545
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp
channel ORA_DISK_4: starting datafile backupset restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +XN_DATA/datamart/datafile/users.261.769192545
restoring datafile 00007 to +XN_DATA/dsp0001/datafile/xn_datamart.273.665597367
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp tag=TAG20111205T235251
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp tag=TAG20111205T235251
channel ORA_DISK_2: restore complete, elapsed time: 00:02:23
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp tag=TAG20111205T235251
channel ORA_DISK_3: restore complete, elapsed time: 00:16:29
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp tag=TAG20111205T235251
channel ORA_DISK_4: restore complete, elapsed time: 01:48:60
Finished restore at 06-DEC-11

RMAN>
RMAN>

RMAN> recover database;

Starting recover at 06-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=537 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1098 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=542 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=1103 devtype=DISK
datafile 7 not processed because file is read-only

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2011 21:28:05
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 11793 lowscn 1558553689 found to restore

RMAN> alter database open resetlogs;

database opened

RMAN>

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: