Dear Guru's,
DB_VERSION-10.2.0.4
OS_VERSION-WINDOWS 2003 SERVER
I have noticed a datafile is offline from v$recover_file
15:05:08 prod >@db
NAME OPEN_MODE LOG_MODE HOST_NAME
--------- ---------- ------------ --------------------
PROD READ WRITE ARCHIVELOG PRODHOST
Elapsed: 00:00:00.51
15:05:10 prod >select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ---------
20 OFFLINE OFFLINE 1160523233 19-JAN-12
Elapsed: 00:00:00.06
15:05:10 prod >select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 151722179 07-JUL-09
2 NOT ACTIVE 151722231 07-JUL-09
3 NOT ACTIVE 151722144 07-JUL-09
4 NOT ACTIVE 151722408 07-JUL-09
5 NOT ACTIVE 151721658 07-JUL-09
6 NOT ACTIVE 151721658 07-JUL-09
7 NOT ACTIVE 151721893 07-JUL-09
8 NOT ACTIVE 151721893 07-JUL-09
9 NOT ACTIVE 151721893 07-JUL-09
10 NOT ACTIVE 151721893 07-JUL-09
11 NOT ACTIVE 151721893 07-JUL-09
12 NOT ACTIVE 151721658 07-JUL-09
13 NOT ACTIVE 151721658 07-JUL-09
14 NOT ACTIVE 151721658 07-JUL-09
15 NOT ACTIVE 151721781 07-JUL-09
16 NOT ACTIVE 151721781 07-JUL-09
17 NOT ACTIVE 151721781 07-JUL-09
18 NOT ACTIVE 151721781 07-JUL-09
19 NOT ACTIVE 151721781 07-JUL-09
21 NOT ACTIVE 151721611 07-JUL-09
22 NOT ACTIVE 151722063 07-JUL-09
23 NOT ACTIVE 0
24 NOT ACTIVE 0
25 NOT ACTIVE 0
24 rows selected.
Elapsed: 00:00:00.17
15:05:10 prod >select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
30804
Also because of which my export backup log showing following message :
. . exporting table XDF
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: 'E:\PROD\DATA\RMAN01.DBF'
Hi,
Tried to recover data file 20 of tablespace RMAN
RMAN> recover tablespace 'RMAN';
Starting recover at 02-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=192 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=191 devtype=DISK
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/02/2012 16:29:09
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 29881 lowscn 1162657672 found to restore
RMAN-06025: no backup of log thread 1 seq 29880 lowscn 1162614694 found to restore
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 29879 lowscn 1162613529
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 29878 lowscn 1162607230
Also
SQL> recover datafile 20;
ORA-00279: change 1160523233 generated at 01/19/2012 11:53:51 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVES\PROD_1_29777_717158136.LOG
ORA-00280: change 1160523233 for thread 1 is in sequence #29777
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'E:\ARCHIVES\PROD_1_29777_717158136.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified
ORA-00308: cannot open archived log 'E:\ARCHIVES\PROD_1_29777_717158136.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified
The required archive is of 20-Jan-2012.Since the retention policy of the Primary is of 5 days,the archive is no longer available on the disk.
I have also tired to find the archive on DR server's but it has deleted.
The backup regularly goes onto the tape.
Also this tablespaces RMAN is created long way back , but we no longer using catalog for storing backup information.
prod >select username,default_tablespace from dba_users where username like '%RMAN%';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
RMAN1 RMAN1
RMAN RMAN
both RMAN1 and RMAN users has 91 tables.--Metadata tables
i.e.prod >select count(1) from tab;
COUNT(1)
----------
91
- The RMAN tablespace does not contains any application data
The DATAFILE 20 of RMAN tablespace is currently in OFFLINE state.
How do i handle the recovery ..
I am planning to dropp the RMAN tablespace since it does not contain bussiness data.
I have also verified the backup.log of RMAN ,which clearly shows
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 1 23:30:02 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
using target database control file instead of recovery catalog
RMAN> run
2> {
3> change archivelog all crosscheck;
4> crosscheck backup;
5> backup database plus archivelog;
6> backup as copy current controlfile;
7> }
8> exit
Kindly suggest your views on the same.
Thanks in advance.
Edited by: user12000301 on Feb 2, 2012 6:02 AM
Edited by: user12000301 on Feb 2, 2012 6:05 AM