OraFAQ Forum: Backup & Recovery » Incomplete Recovery

Home » RDBMS Server » Backup & Recovery » Incomplete Recovery (Oracle,11.1.6,windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Incomplete Recovery [message #410500]

Sat, 27 June 2009 12:39

SMAli
Messages: 3
Registered: June 2009

Junior Member

Hi ,
I am trying to do point in time recovery using SCN on windows(11.1.6) for lost of current redolog after shutdown abort(removed logfiles).
Here are the steps....

 SQL> select status,group#,first_change# from v$log;
 
STATUS               GROUP# FIRST_CHANGE#
---------------- ---------- -------------
INACTIVE                  1       1952117
CURRENT                   2       1983844
UNUSED                    3             0
 
SQL> conn scott/tiger
Connected.
SQL> create table aa (id number);
 
Table created.
 
SQL> insert into aa values(&1);
Enter value for 1: 1
old   1: insert into aa values(&1)
new   1: insert into aa values(1)
 
1 row created.
 
SQL> /
Enter value for 1: 2
old   1: insert into aa values(&1)
new   1: insert into aa values(2)
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> select status,group#,first_change# from v$log;
 
STATUS               GROUP# FIRST_CHANGE#
---------------- ---------- -------------
INACTIVE                  1       1997211
CURRENT                   2       1997214
INACTIVE                  3       1997209
 
SQL> select max(NEXT_CHANGE#)-1 as "SCN" from V$ARCHIVED_LOG;
 
       SCN
----------
   1997213
 
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
C:\app\sali\oradata\test>rm *.log
 
C:\Users\sali>rman target /
 
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jun 27 11:41:29 2009
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
connected to target database (not started)
 
RMAN> startup nomount
 
Oracle instance started
 
Total System Global Area     426852352 bytes
 
Fixed Size                     1333648 bytes
Variable Size                285214320 bytes
Database Buffers             134217728 bytes
Redo Buffers                   6086656 bytes
 
RMAN> restore controlfile from autobackup;
 
Starting restore at 27-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
 
recovery area destination: C:\app\sali\flash_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: AUTOBACKUP C:\APP\sali\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2
009_06_26\O1_MF_S_690594541_54C5PSC1_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\APP\sali\FLASH_REC
OVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O1_MF_S_690594541_54C5PSC1_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\APP\sali\ORADATA\TEST\CONTROL01.CTL
output file name=C:\APP\sali\ORADATA\TEST\CONTROL02.CTL
output file name=C:\APP\sali\ORADATA\TEST\CONTROL03.CTL
Finished restore at 27-JUN-09
 
RMAN> list incarnation;
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/27/2009 11:43:36
ORA-01507: database not mounted
 
RMAN> alter database mount;
 
database mounted
released channel: ORA_DISK_1
 
RMAN> list incarnation;
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     1988467378       PARENT  1          15-OCT-07
2       2       TEST     1988467378       PARENT  886308     23-JUN-09
3       3       TEST     1988467378       PARENT  1950836    26-JUN-09
4       4       TEST     1988467378       PARENT  1951672    26-JUN-09
6       6       TEST     1988467378       ORPHAN  1952117    26-JUN-09
7       7       TEST     1988467378       CURRENT 1952117    26-JUN-09
5       5       TEST     1988467378       ORPHAN  1952344    26-JUN-09
 
RMAN> reset database to incarnation 4;
 
database reset to incarnation 4
 
RMAN> restore database until scn 1997213;
 
Starting restore at 27-JUN-09
Starting implicit crosscheck backup at 27-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 27-JUN-09
 
Starting implicit crosscheck copy at 27-JUN-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-09
 
searching for all files in the recovery area
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_1_
54DB81TJ_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_2_
54DH6QCR_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_3_
54DH6T9T_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_4_
54DH6WYB_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O1_MF_S_69
0594541_54C5PSC1_.BKP
 
using channel ORA_DISK_1
 
creating datafile file number=6 name=C:\APP\sali\ORADATA\TEST\USER02.DBF
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 00001 to C:\APP\sali\ORADATA\TEST\SYSTEM0
1.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\sali\ORADATA\TEST\SYSAUX0
1.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\sali\ORADATA\TEST\UNDOTBS
01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\sali\ORADATA\TEST\USERS01
.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\sali\ORADATA\TEST\EXAMPLE
01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\sali\FLASH_RECOVERY_AREA\TE
ST\BACKUPSET\2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP
channel ORA_DISK_1: piece handle=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\BACKUPSET\
2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP tag=TAG20090624T103303
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 27-JUN-09
 
RMAN> recover database until scn 1997213;
 
Starting recover at 27-JUN-09
using channel ORA_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 52 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_52_544GG9LY_.ARC
archived log for thread 1 with sequence 53 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_53_544JOW42_.ARC
archived log for thread 1 with sequence 54 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_54_544KG613_.ARC
archived log for thread 1 with sequence 55 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_55_545KFV3K_.ARC
archived log for thread 1 with sequence 56 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_25\O1_MF_1_56_546YP173_.ARC
archived log for thread 1 with sequence 57 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_57_549HVW3T_.ARC
archived log for thread 1 with sequence 58 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_58_549SRJFK_.ARC
archived log for thread 1 with sequence 1 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_1_54BXDKH5_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_2_54BXDLGO_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_3_54BXDR2G_.ARC
archived log for thread 1 with sequence 1 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_1_54BYK6R6_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_2_54BYK8RB_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_3_54BYKG92_.ARC
archived log for thread 1 with sequence 4 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_4_54BYKJ7O_.ARC
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_52_544GG9LY_.ARC thread=1 sequence=52
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_53_544JOW42_.ARC thread=1 sequence=53
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_54_544KG613_.ARC thread=1 sequence=54
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_55_545KFV3K_.ARC thread=1 sequence=55
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
5\O1_MF_1_56_546YP173_.ARC thread=1 sequence=56
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_57_549HVW3T_.ARC thread=1 sequence=57
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_58_549SRJFK_.ARC thread=1 sequence=58
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BXDKH5_.ARC thread=1 sequence=1
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BXDLGO_.ARC thread=1 sequence=2
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BXDR2G_.ARC thread=1 sequence=3
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BYK6R6_.ARC thread=1 sequence=1
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BYK8RB_.ARC thread=1 sequence=2
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BYKG92_.ARC thread=1 sequence=3
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_4_54BYKJ7O_.ARC thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2009 11:58:56
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 5 and starting SCN of 1952116
 
RMAN> alter database open resetlogs;
 
database opened
 
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

Why is that i'm not able to get my table 'AA' after recovery? can someone please explain me what is that i'm missing here.

Thanks

Report message to a moderator