Oracle introduced the SKIP READONLY option

Until 11gr1 by default, the restore command skipped datafiles associated with read-only tablespaces. If you needed read-only tablespaces restored, then you had to use the “check readonly” command or restore each read-only tablespace individually.
Have a look at the restore syntax on “Oracle Database Backup and Recovery Reference 10g Release 2 (10.2)” Part Number B14194-03  and compare it with the syntax used on “Oracle Database Backup and Recovery Reference 11g Release 1 (11.1) Part Number B28273-03” .

In the latter syntax Oracle introduced the SKIP READONLY option, inverting the previous logic: the SKIP READONLY does not restore read-only files.

Media-recovery-overview

Media recovery is a process to recover database from backup when physical disk failure occur.,in other way loss of physical file which has to be taken care by Oracle DBA

Media failures fall into two general categories: permanent and temporary.

Permanent media failures are serious hardware problems that cause permanent loss of data on the disk. Lost data cannot be recovered except by repairing or replacing the failed storage device and restoring backups of the files stored on the damaged storage device.

Temporary media failures are hardware problems that make data temporarily inaccessible, but do not corrupt the data. Following are two examples of temporary media failures:

  1. A disk controller fails. Once the disk controller is replaced, Oracle can access the data on the disk.
  2. Power to a storage device is cut off. Once the power is returned, the storage device and all associated data is accessible again.

crash-recovery-overview

An instance(crash) failure occurs when your database isn’t able to shutdown normally.
When this happens, your datafiles could be in an inconsistent state meaning they may not
contain all committed changes and may contain uncommitted changes.

Instance failures occur when the instance terminates abnormally. A sudden power failure
or a shutdown abort are two common causes of instance failure.
Oracle uses crash recovery to return the database to a consistent committed state after
an instance failure.

Crash recovery guarantees that when your database is opened, it will contain only transactions
that were committed before the instance failure occurred.

Oracle system monitor will automatically detect whether crash recovery is required.

Crash recovery has two phases : roll forward and rollback

The system monitor will first roll forward and apply to the datafiles any transactions in the
online redo files that occurred after the most recent checkpoint.

Crash Recovery uses redo information found in the online redo log files only
After rolling forward, Oracle will rollback any of those transactions that were never committed.

Oracle uses information stored in the undo segments to rollback (undo) any uncommitted transactions.

When you start your database, Oracle uses the SCN information in the control files and
datafiles headers to determine which one of the following will occur.

Starting up normally.

Performing crash Recovery

Determining that media reocvery is required.

On start up , Oracle check the instance thread status to determine whether crash recover is required .

When the database is open for normal operations ,the thread status is OPEN. When Oracle us shut down normally ,
a checkpoint takes place and the instance thread status is set to CLOSED.

when your instance abnormally terminates the thread status remains OPEN because Oracle didn’t get a chance to
update the status to CLOSED.

On startup , When Oracle detects that an instance thread was abnormally left open, the system monitor process
will automatically perform crash recovery.

The below query , it will usefull to find out whether crash recovery is required.

select a.thread#,b.open_mode,a.status,
CASE
WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery req.’
WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recovery Req.’
WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance already open’
ELSE ‘huh?’
END STATUS
FROM v$thread a,
v$database b,
v$instance c
where a.thread#=c.thread#;

http://mohamedazar.com/2010/06/09/crash-recovery-overview/

ORA-16014: log 3 sequence# 117 not archived, no available destinations

Recovery from the loss of UNARCHIVED Inactive redo log file

db storage full and redo logs archive process hang

Alert Logs

ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: ‘/orcl/oraarch/arch_1_2562_755697044.arc’ (error 354)
(scott)
Committing creation of archivelog ‘/orcl/oraarch/arch_1_2562_755697044.arc’ (error 354)
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Oct 28 13:28:07 2013
ORACLE Instance scott – Archival Error
Mon Oct 28 13:28:07 2013
ORA-16038: log 3 sequence# 2562 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: ‘/u01/app/oracle/oradata/orcl/redo03.log’

Resolution

free space from /u01

most of the space were occupied by listener log file

cd $ORACLE_HOME/network/admin
cat /dev/null > listener.log

database is mounted successfully
then failed with ORA-16038: log 3 sequence# 2562 cannot be archived

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

ALTER DATABASE OPEN;

http://boomslaang.wordpress.com/2008/11/04/recovery-from-the-loss-of-inactive-redo-log-file/
http://itsiti.com/ora-16014-log-sequence-not-archived-no-available-destinations

http://www.shaiksameer.com/2011/04/ora-01567-dropping-log.html

Change the location of datafiles

Note: steps from sys user

— offline the tablespace
SQL>alter tablespace MYTBS offline

— copy the tablespace’s datafile to new location
$ cp /ORASYS/ORCLDB/ORCLDB/MYTBS.dbf /ORADATA1/ORCL/MYTBS.dbf

— delete the tablespace’s datafile from old location
$ cp /ORASYS/ORCLDB/ORCLDB/
$ rm MYTBS.dbf

— rename the tablespace’s datafile in the control files
SQL> alter tablespace MYTBS rename datafile ‘/ORASYS/ORCLDB/ORCLDB/MYTBS.dbf’ to ‘/ORADATA1/ORCL/MYTBS.dbf’

— online the tablespace
SQL> alter tablespace MYTBS online

— Check the status
select tablespace_name,
status
from dba_tablespaces
where tablespace_name=’MYTBS’;

ORA-01157 and ORA-01110 Workaround for NOARCHIVELOG Mode Database

 

OS: RHEL

DB: Oracle Version 10.2.0.1.0

Alert log status

[oracle@myserver ~]$ tail -100f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
Shutting down instance (immediate)
License high water mark = 3
Thu Nov  8 12:14:49 2012
Stopping Job queue slave processes, flags = 7
Thu Nov  8 12:14:49 2012
Job queue slave processes stopped
Waiting for dispatcher ‘D000’ to shutdown
All dispatchers and shared servers shutdown
Thu Nov  8 12:14:51 2012
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL…
Thu Nov  8 12:14:51 2012
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu Nov  8 12:14:58 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =121
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
processes                = 1000
sessions                 = 1105
__shared_pool_size       = 788529152
__large_pool_size        = 16777216
__java_pool_size         = 16777216
__streams_pool_size      = 33554432
nls_length_semantics     = BYTE
resource_manager_plan    =
sga_target               = 1258291200
control_files            = /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/control03.ctl
db_block_size            = 8192
__db_cache_size          = 385875968
compatible               = 10.2.0.1.0
db_file_multiblock_read_count= 16
undo_management          = AUTO
undo_tablespace          = UNDOTBS1
undo_retention           = 900
remote_login_passwordfile= EXCLUSIVE
db_domain                =
dispatchers              = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes      = 10
background_dump_dest     = /u01/app/oracle/admin/orcl/bdump
user_dump_dest           = /u01/app/oracle/admin/orcl/udump
core_dump_dest           = /u01/app/oracle/admin/orcl/cdump
audit_file_dest          = /u01/app/oracle/admin/orcl/adump
db_name                  = orcl
open_cursors             = 300
pga_aggregate_target     = 414187520
_optimizer_connect_by_cost_based= FALSE
aq_tm_processes          = 0
PMON started with pid=2, OS id=11684
PSP0 started with pid=3, OS id=11686
MMAN started with pid=4, OS id=11688
DBW0 started with pid=5, OS id=11690
LGWR started with pid=6, OS id=11692
CKPT started with pid=7, OS id=11694
SMON started with pid=8, OS id=11696
RECO started with pid=9, OS id=11698
CJQ0 started with pid=10, OS id=11700
MMON started with pid=11, OS id=11702
Thu Nov  8 12:14:58 2012
starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
MMNL started with pid=12, OS id=11704
Thu Nov  8 12:14:58 2012
starting up 1 shared server(s) …
Thu Nov  8 12:14:58 2012
ALTER DATABASE   MOUNT
Thu Nov  8 12:15:02 2012
Setting recovery target incarnation to 1
Thu Nov  8 12:15:02 2012
Successful mount of redo thread 1, with mount id 2714131202
Thu Nov  8 12:15:02 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Nov  8 12:15:02 2012
ALTER DATABASE OPEN
Thu Nov  8 12:15:02 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_11690.trc:
ORA-01157: cannot identify/lock data file 33 – see DBWR trace file
ORA-01110: data file 33: ‘/u01/app/oracle/oradata/orcl/netvertex640.dbf’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN…

[oracle@aaa-build ~]$
[oracle@aaa-build ~]$
[oracle@aaa-build ~]$

file not found in os

[oracle@aaa-build ~]$ ll /u01/app/oracle/oradata/orcl/netvertex640.dbf
ls: /u01/app/oracle/oradata/orcl/netvertex640.dbf: No such file or directory

db status:-

[oracle@aaa-build ~]$
[oracle@aaa-build ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Nov 8 12:19:25 2012

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 open_mode from v$database;

OPEN_MODE
———-
MOUNTED

Solution:-


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     1527
Current log sequence           1529
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/netvertex640.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Dat

Troubleshooting ORA-16038 & ORA-19809

 

ORA-16038: log 2 sequence# 816 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
‘/u01/app/oracle/oradata/eliteaaa/redo02.log’

SQL>shut abort

SQL>alter system set db_recovery_file_dest_size=4G scope=both

SQL>alter system archive log all to ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog’;

SQL>startup nomount

SQL>alter database mount

SQL>alter database open

SQL>alter system set log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ORCL/archivelog’ SCOPE=BOTH;

 

Production Database down ORA-00333: redo log read error block 3656 count 7994

Product Oracle Server – Enterprise Edition
Product Version – 11.2.0.2
Operating System – Oracle Solaris on x86-64 (64-bit) OS Version- 10
Problem Description – Database not able to come in READ WRITE Mode

ORA-00333: redo log read error block 3656 count 7994

==========ALERT LOGS ===================================

Sat Nov 03 05:00:03 2012
Thread 1 advanced to log sequence 2032 (LGWR switch)
Current log# 1 seq# 2032 mem# 0: /u01/home/oracle/u01/app/oracle/oradata/orcl/redo01.log
Sat Nov 03 05:00:03 2012
Archived Log entry 2025 added for thread 1 sequence 2031 ID 0x4fa0c79f dest 1:
Sat Nov 03 05:00:04 2012
LOGMINER: End mining logfile for session 1 thread 1 sequence 2031, /u01/home/oracle/u01/app/oracle/oradata/orcl/redo03.log
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 2032, /u01/home/oracle/u01/app/oracle/oradata/orcl/redo01.log
Sat Nov 03 08:36:25 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =60
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u01/home/oracle/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 1000
memory_target = 6560M
control_files = "/u01/home/oracle/u01/app/oracle/oradata/orcl/control01.ctl"
control_files = "/u01/home/oracle/u01/app/oracle/fast_recovery_area/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/backup/oraarch"
log_archive_format = "arch_%t_%s_%r.arc"
db_recovery_file_dest = "/u01/home/oracle/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4062M
transactions = 600
undo_tablespace = "UNDOTBS1"
sec_case_sensitive_logon = FALSE
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
global_names = TRUE
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "/u01/home/oracle/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 800
diagnostic_dest = "/u01/home/oracle/u01/app/oracle"
Sat Nov 03 08:36:30 2012
PMON started with pid=2, OS id=1349
Sat Nov 03 08:36:30 2012
PSP0 started with pid=3, OS id=1351
Sat Nov 03 08:36:32 2012
VKTM started with pid=4, OS id=1353 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Sat Nov 03 08:36:32 2012
GEN0 started with pid=5, OS id=1357
Sat Nov 03 08:36:32 2012
DIAG started with pid=6, OS id=1359
Sat Nov 03 08:36:32 2012
DBRM started with pid=7, OS id=1361
Sat Nov 03 08:36:32 2012
DIA0 started with pid=8, OS id=1363
Sat Nov 03 08:36:32 2012
MMAN started with pid=9, OS id=1365
Sat Nov 03 08:36:32 2012
DBW0 started with pid=10, OS id=1367
Sat Nov 03 08:36:32 2012
LGWR started with pid=11, OS id=1369
Sat Nov 03 08:36:32 2012
CKPT started with pid=12, OS id=1371
Sat Nov 03 08:36:32 2012
SMON started with pid=13, OS id=1373
Sat Nov 03 08:36:32 2012
RECO started with pid=14, OS id=1375
Sat Nov 03 08:36:32 2012
MMON started with pid=15, OS id=1377
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Nov 03 08:36:32 2012
MMNL started with pid=16, OS id=1379
starting up 1 shared server(s) ...
DISM started, OS id=1385
ORACLE_BASE from environment = /u01/home/oracle/u01/app/oracle
Sat Nov 03 08:36:36 2012
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1356888724
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Nov 03 08:36:41 2012
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Started redo scan
Aborting crash recovery due to error 333
Errors in file /u01/home/oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1391.trc:
ORA-00333: redo log read error block 3656 count 7994
Errors in file /u01/home/oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1391.trc:
ORA-00333: redo log read error block 3656 count 7994
ORA-333 signalled during: ALTER DATABASE OPEN...
Sat Nov 03 08:48:29 2012
db_recovery_file_dest_size of 4062 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Nov 05 12:07:21 2012
ARCH: STARTING ARCH PROCESSES
Mon Nov 05 12:07:21 2012
ARC0 started with pid=22, OS id=12211
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Nov 05 12:07:22 2012
ARC1 started with pid=23, OS id=12225
Mon Nov 05 12:07:22 2012
ARC2 started with pid=24, OS id=12227
Mon Nov 05 12:07:22 2012
ARC3 started with pid=25, OS id=12229
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Nov 05 14:32:20 2012

=========================================================================================================

 -v$datafile_header-

SQL> SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;  2    3    4    5    6    7

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME             COUNT(*)
------- ------------------ ------------------- ----------------
ONLINE            35135011 03-11-2012 05:00:03                9

-v$archived_log-

SQL> SELECT recid,
  2  thread#,
  3  sequence#,
  4  name,
tO_CHAR(first_change#, '999999999999999') as first_change#,
  5    6  to_char(NEXT_CHANGE#, '999999999999999') as next_change# ,
  7  archived,
  8  deleted,
  9  TO_CHAR(completion_time, 'DD-MM-YYYY HH24:MI:SS') as completed,
 10  status
 11  FROM v$archived_log;

 RECID          THREAD#        SEQUENCE# NAME                                               FIRST_CHANGE#    NEXT_CHANGE#     ARC DEL COMPLETED           S
 2025                1             2031 /backup/oraarch/arch_1_2031_777416481.arc                  35109311         35135011 YES NO  03-11-2012 05:00:03 A

v$recover_file

SQL> SELECT *
FROM v$recover_file  2
  3  ;

FILE# ONLINE  ONLINE_ ERROR                                                        CHANGE# TIME
---------------- ------- ------- ----------------------------------------------------------------- 
1 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
2 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
3 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
4 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
5 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
6 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
7 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
8 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03
9 ONLINE  ONLINE                                                  35135011 03-11-2012 05:00:03

v$datafile

SQL> SELECT name,
  2  file#,
  3  status,
  4  enabled,
  5  creation_change#,
  6  TO_CHAR(creation_time, 'DD-MM-YYYY HH24:MI:SS') as creation_time,
  7  TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
  8  TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
  9  TO_CHAR(offline_change#, '999999999999999') as offline_change#,
 10  TO_CHAR(online_change#, '999999999999999') as online_change#,
 11  TO_CHAR(online_time, 'DD-MM-YYYY HH24:MI:SS') as online_time,
 12  TO_CHAR(bytes, '9,999,999,999,990') as bytes
 13  FROM v$datafile
 14  ORDER BY checkpoint_change#;

NAME                                                          FILE# STATUS  ENABLED    CREATION_CHANGE# CREATION_TIME       CHECKPOINT_CHANG CHECKPOINT_TIME     OFFLINE_CHANGE#  ONLINE_CHANGE#   ONLINE_TIME         BYTES
-------------------------------------------------- ---------------- ------- ---------- ---------------- ------------------- ---------------- ------------------- ---------------- ---------------- ------------------- ------------------
/u01/home/oracle/u01/app/oracle/oradata/orcl/syst                1 SYSTEM  READ WRITE               13 11-09-2010 06:33:30         35135011 03-11-2012 05:00:03           908013           908014 08-03-2012 21:01:21        891,289,600
em01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/sysa                2 ONLINE  READ WRITE             2086 11-09-2010 06:33:58         35135011 03-11-2012 05:00:03           908013           908014 08-03-2012 21:01:21        964,689,920
ux01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/undo                3 ONLINE  READ WRITE           905473 11-09-2010 07:50:33         35135011 03-11-2012 05:00:03           908013           908014 08-03-2012 21:01:21        110,100,480
tbs01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/user                4 ONLINE  READ WRITE            17595 11-09-2010 06:35:16         35135011 03-11-2012 05:00:03           908013           908014 08-03-2012 21:01:21          5,242,880
s01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/stre                9 ONLINE  READ WRITE          1828075 19-03-2012 13:16:23         35135011 03-11-2012 05:00:03                0                0                            374,865,920
ams_tbs.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/elit                6 ONLINE  READ WRITE          1315794 12-03-2012 19:56:29         35135011 03-11-2012 05:00:03                0                0                            104,857,600
eaaa62.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/elit                7 ONLINE  READ WRITE          1696162 18-03-2012 00:41:26         35135011 03-11-2012 05:00:03                0                0                            104,857,600
eaaasync.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/peli                8 ONLINE  READ WRITE          1700589 18-03-2012 01:18:32         35135011 03-11-2012 05:00:03                0                0                            104,857,600
teaaa.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/exam                5 ONLINE  READ WRITE           938011 08-03-2012 21:01:49         35135011 03-11-2012 05:00:03           938011           938016 08-03-2012 21:01:49        348,651,520
ple01.dbf

v$archived_log and v$log

 SQL> SELECT a.recid,
  2  a.thread#,
  3  a.sequence#,
  4  a.name,
  5  tO_CHAR(a.first_change#, '999999999999999') as first_change#,
  6  to_char(a.NEXT_CHANGE#, '999999999999999') as next_change# ,
  7  a.archived,
  8  a.deleted,
  9  TO_CHAR(a.completion_time, 'DD-MM-YYYY HH24:MI:SS') as completed
 10  FROM v$archived_log a, v$log l
 11  WHERE a.thread# = l.thread#
 12  AND a.sequence# = l.sequence#;

RECID          THREAD#        SEQUENCE# NAME  FIRST_CHANGE#    NEXT_CHANGE#     ARC DEL COMPLETED
---------------- ---------------- ---------------- 
2024       1             2030                35099904         35109311 YES YES 02-11-2012 23:24:23
2025       1             2031 /backup/oraarch/arch_1_2031_777416481.arc                  35109311         35135011 YES NO  03-11-2012 05:00:03

v$datafile_header

SQL> SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MM-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MM-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
ORDER BY checkpoint_change#;

NAME                                                          FILE# STATUS  ERROR                                                             CREATION_CHANGE# CREATION_TIME       CHECKPOINT_CHANG CHECKPOINT_TIME     RESETLOGS_CHANGE RESETLOGS_TIME      BYTES
-------------------------------------------------- ---------------- ------- ----------------------------------------------------------------- ---------------- ------------------- ---------------- ------------------- ---------------- ------------------- ------------------
/u01/home/oracle/u01/app/oracle/oradata/orcl/syst                1 ONLINE                                                                                  13 11-09-2010 06:33:30         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        891,289,600
em01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/sysa                2 ONLINE                                                                                2086 11-09-2010 06:33:58         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        964,689,920
ux01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/undo                3 ONLINE                                                                              905473 11-09-2010 07:50:33         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        110,100,480
tbs01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/user                4 ONLINE                                                                               17595 11-09-2010 06:35:16         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21          5,242,880
s01.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/stre                9 ONLINE                                                                             1828075 19-03-2012 13:16:23         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        374,865,920
ams_tbs.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/elit                6 ONLINE                                                                             1315794 12-03-2012 19:56:29         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        104,857,600
eaaa62.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/elit                7 ONLINE                                                                             1696162 18-03-2012 00:41:26         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        104,857,600
eaaasync.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/peli                8 ONLINE                                                                             1700589 18-03-2012 01:18:32         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        104,857,600
teaaa.dbf

/u01/home/oracle/u01/app/oracle/oradata/orcl/exam                5 ONLINE                                                                              938011 08-03-2012 21:01:49         35135011 03-11-2012 05:00:03           908014 08-03-2012 21:01:21        348,651,520
ple01.dbf

Solution

Action Plan-1

RMAN> run
{
allocate channel c1 device type disk;
restore database;
recover database;
alter database open;
};

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=958 device type=DISK

Starting restore at 05-NOV-12

skipping datafile 1; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/system01.dbf
skipping datafile 2; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf
skipping datafile 3; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf
skipping datafile 4; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/users01.dbf
skipping datafile 5; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/example01.dbf
skipping datafile 6; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/eliteaaa62.dbf
skipping datafile 7; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/eliteaaasync.dbf
skipping datafile 8; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/peliteaaa.dbf
skipping datafile 9; already restored to file /u01/home/oracle/u01/app/oracle/oradata/orcl/streams_tbs.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 05-NOV-12

Starting recover at 05-NOV-12

starting media recovery
media recovery failed
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/05/2012 20:24:12
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-00333: redo log read error block 3656 count 440

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure, connect, convert, copy, create, crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list, mount, open, print, quit, recover, register, release, repair, replace, report, reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, startup, switch, transport, unregister, upgrade, validate, {, "
RMAN-01007: at line 0 column 2 file: standard input

RMAN>

Action Plan-2

bash-3.2$ echo $ORACLE_SID
orcl
-bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Nov 5 21:37:03 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: orcl (DBID=1335942047, not open)

RMAN>

RMAN> recover database noredo;

Starting recover at 05-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK

Finished recover at 05-NOV-12

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/05/2012 21:37:41
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/home/oracle/u01/app/oracle/oradata/orcl/system01.dbf'

RMAN>

Action Plan-3

-bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Nov 5 21:58:58 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: orcl (DBID=1335942047, not open)

RMAN> run
{
set until sequence 2031 thread 1;
restore database;
}2> 3> 4> 5>

executing command: SET until clause

Starting restore at 05-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK

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 /u01/home/oracle/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/home/oracle/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/home/oracle/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/home/oracle/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/home/oracle/u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/home/oracle/u01/app/oracle/oradata/orcl/eliteaaa62.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/home/oracle/u01/app/oracle/oradata/orcl/eliteaaasync.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/home/oracle/u01/app/oracle/oradata/orcl/peliteaaa.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/home/oracle/u01/app/oracle/oradata/orcl/streams_tbs.dbf
channel ORA_DISK_1: reading from backup piece /backup/rmanbackups/orcl_DB_4fnovdmc_143_1.rman
channel ORA_DISK_1: piece handle=/backup/rmanbackups/orcl_DB_4fnovdmc_143_1.rman tag=TAG20121029T120635
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05

SQL> recover automatic database until cancel;
ORA-00279: change 34478813 generated at 10/29/2012 12:06:36 needed for thread 1
ORA-00289: suggestion : /backup/oraarch/arch_1_1990_777416481.arc
ORA-00280: change 34478813 for thread 1 is in sequence #1990
ORA-00278: log file '/backup/oraarch/arch_1_1990_777416481.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log '/backup/oraarch/arch_1_1990_777416481.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

RCA

the current sequence needed to clear the fussiness should apply the changes in the online redo which is corrupted.

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
---------------- ---------------- ---------------- ---------------- --- ---------------- ----------------
1 1 2032 1 NO CURRENT 35135011 <<<<<<<<<<<<<<< this is the corrupted redo
3 1 2031 1 YES INACTIVE 35109311
2 1 2030 1 YES INACTIVE 35099904