Recover from loss of single current online redo log file

Recover from loss of single current online redo log file

RMAN> list backupset summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
7 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
8 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
9 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
10 B A A DISK 11-AUG-10 1 1 NO TAG20100811T141822
11 B A A DISK 11-AUG-10 1 1 NO TAG20100811T141822
RMAN> exit

Recovery Manager complete.
[oracle@crisreplication ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 11 14:19:01 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select member from v$Logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/trialrep/redo02.log
/u01/app/oracle/oradata/trialrep/redo01.log
/u01/app/oracle/oradata/trialrep/redo03.log
SQL> SQL>
SQL>
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> select group#, member from v$logfile order by 1;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/trialrep/redo01.log
2
/u01/app/oracle/oradata/trialrep/redo02.log
3
/u01/app/oracle/oradata/trialrep/redo03.log

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@crisreplication ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 11 14:21:18 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 1267164 bytes
Variable Size 318769700 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/trialrep/redo03.log'

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 INACTIVE
3 16 50 1 CURRENT
2 14 50 1 INACTIVE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@crisreplication ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 11 14:24:14 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TRIALREP (DBID=1985177645, not open)

RMAN> run {
2> set until sequence 16;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause
using target database control file instead of recovery catalog
Starting restore at 11-AUG-10

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/trialrep/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/trialrep/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/trialrep/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/rman0bll30b1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/rman0bll30b1_1_1 tag=EB64LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/trialrep/system01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/trialrep/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/trialrep/bv1to1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/rman0all30b1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/rman0all30b1_1_1 tag=EB64LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46

Finished restore at 11-AUG-10

Starting recover at 11-AUG-10
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 15 is already on disk as file /arch/1_15_726755127.dbf
archive log filename=/arch/1_15_726755127.dbf thread=1 sequence=15
media recovery complete, elapsed time: 00:00:04

Finished recover at 11-AUG-10

database opened