Oracle Database Discussions

Hi experts,

I was trying to recover with the same SCN that I used before to recover; it was a good recovery, but we import the wrong data; therefore, we wanted to go back to the clean data base before the import and recover again with the same SCN.

I followed the instructions recommeneded for me to recover, but I may not reset the right incarnation before recovery. I did not see the SCN in the list of incarnations. Therefore, I selected the incarnation from the last PARENT; I thought it was close to that SCN. I got some errors; therefore, I reset the incarnation again to the last CURRENT incarnation. Despite that I did not see the SCN that I was looking for.

I tried again to recovere with the SCN that I was looking for. It looks like it recovered to that PIT. Do you know a way for me to know which incarnation was the right one, and how to verify the recovery. On my script it was suppose to open with the resetlogs, but it did not do it. I had to open it manually.

Please see the commands and the output below the line

Thanks, Terry
_________________________________________________________

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 XMS 107702786 PARENT 1 19-DEC-06
2 2 XMS 107702786 PARENT 564488 22-JAN-08
3 3 XMS 107702786 PARENT 1018510 06-FEB-08
4 4 XMS 107702786 PARENT 1020066 06-FEB-08
5 5 XMS 107702786 PARENT 1020500 07-FEB-08
6 6 XMS 107702786 PARENT 1020942 07-FEB-08
7 7 XMS 107702786 PARENT 1021290 07-FEB-08
8 8 XMS 107702786 PARENT 1032304 08-FEB-08
9 9 XMS 107702786 CURRENT 1037275 08-FEB-08

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down


RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1224736768 bytes

Fixed Size 2072352 bytes
Variable Size 318767328 bytes
Database Buffers 889192448 bytes
Redo Buffers 14704640 bytes

RMAN> restore controlfile from '+FRA/xms/autobackup/2008_02_08/s_646137494.432.646137495';

Starting restore at 12-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=530 instance=XMS1 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=+FRA/xms/controlfile/current.256.644665667
Finished restore at 12-FEB-08

RMAN>
RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> reset database to incarnation 8;

File Name: +fra/xms/AUTOBACKUP/2008_02_08/s_646153432.456.646153433
File Name: +fra/xms/BACKUPSET/2008_02_12/nnndf0_TAG20080212T103509_0.460.646482911
File Name: +fra/xms/BACKUPSET/2008_02_12/nnndf0_TAG20080212T103509_0.461.646482911
File Name: +fra/xms/BACKUPSET/2008_02_12/ncnnf0_TAG20080212T103509_0.462.646482917
File Name: +fra/xms/BACKUPSET/2008_02_08/nnndf0_TAG20080208T145017_0.445.646152619
File Name: +fra/xms/BACKUPSET/2008_02_08/nnndf0_TAG20080208T145017_0.446.646152619
File Name: +fra/xms/BACKUPSET/2008_02_08/ncnnf0_TAG20080208T145017_0.447.646152633


channel dsk2: starting datafile backupset restore
channel dsk2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/xms/datafile/system.256.644665617
restoring datafile 00004 to +DATA/xms/datafile/users.259.644665617
channel dsk2: reading from backup piece +FRA/xms/backupset/2008_02_08/nnndf0_tag20080208t103740_0.422.646137461
channel dsk1: starting datafile backupset restore
channel dsk1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA/xms/datafile/undotbs1.258.644665617
restoring datafile 00003 to +DATA/xms/datafile/sysaux.257.644665617
restoring datafile 00005 to +DATA/xms/datafile/streams_dat_01_ts.262.644667131
channel dsk1: reading from backup piece +FRA/xms/backupset/2008_02_08/nnndf0_tag20080208t103740_0.410.646137461
channel dsk1: restored backup piece 1
piece handle=+FRA/xms/backupset/2008_02_08/nnndf0_tag20080208t103740_0.410.646137461 tag=TAG20080208T103740
channel dsk1: restore complete, elapsed time: 00:00:15
channel dsk2: restored backup piece 1
piece handle=+FRA/xms/backupset/2008_02_08/nnndf0_tag20080208t103740_0.422.646137461 tag=TAG20080208T103740
channel dsk2: restore complete, elapsed time: 00:00:22
Finished restore at 12-FEB-08

Starting recover at 12-FEB-08

starting media recovery

channel dsk2: starting archive log restore to default destination
channel dsk1: starting archive log restore to default destination
channel dsk2: restoring archive log
archive log thread=1 sequence=3
channel dsk2: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/1tj821cb_1_1
channel dsk1: restoring archive log
archive log thread=1 sequence=11
channel dsk1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/0qj819qv_1_1
channel dsk2: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/1tj821cb_1_1 tag=TAG20080206T173610
channel dsk2: restore complete, elapsed time: 00:00:03
channel dsk2: starting archive log restore to default destination
channel dsk2: restoring archive log
archive log thread=1 sequence=3
channel dsk2: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/2sj83s8m_1_1
channel dsk1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/0qj819qv_1_1 tag=TAG20080206T105423
channel dsk1: restore complete, elapsed time: 00:00:06
channel dsk1: starting archive log restore to default destination
channel dsk1: restoring archive log
archive log thread=1 sequence=3
channel dsk1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/4fj83uha_1_1
channel dsk1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/4fj83uha_1_1 tag=TAG20080207T105954
channel dsk1: restore complete, elapsed time: 00:00:07
channel dsk2: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/2sj83s8m_1_1 tag=TAG20080207T102110
channel dsk2: restore complete, elapsed time: 00:00:10
channel dsk1: starting archive log restore to default destination
channel dsk1: restoring archive log
archive log thread=1 sequence=4
channel dsk1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/8aj86hki_1_1
channel dsk1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/8aj86hki_1_1 tag=TAG20080208T103810
channel dsk1: restore complete, elapsed time: 00:00:01
archive log filename=+FRA/xms/1_4_646137109.dbf thread=1 sequence=4
unable to find archive log
archive log thread=1 sequence=5
released channel: dsk1
released channel: dsk2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/12/2008 11:17:50
RMAN-06054: media recovery requesting unknown log: thread 1 seq 5 lowscn 1037193

RMAN>
RMAN>
[oracle@xms1 bkup_logs]$ cat recovery_20080212.log

Spooling started in log file: /u01/app/oracle/admin/bkupfiles/bkup_logs/recovery_20080212.log

Recovery Manager10.2.0.3.0

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
allocated channel: dsk1
channel dsk1: sid=535 instance=xms1 devtype=DISK

allocated channel: dsk2
channel dsk2: sid=536 instance=xms1 devtype=DISK

executing command: SET until clause
released channel: dsk1
released channel: dsk2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 02/12/2008 11:26:00
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

RMAN> reset database to incarnation 9;

Did the restore and recovery again...... Not sure how to validate which incarnation worked?.....