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
Windows Recall : How to disable spy mode
4 days ago
No comments:
Post a Comment