Oracle Error file #N is offline, but are part of online tablespace

A similar warning was found while looking at a client's database recovery log (File #N is offline, but was part of a online tablespace.), not previously noticed, this time by experimenting to reproduce the content
Create a table space
sql> Create tablespace readonly datafile '/home/oracle/.oradata/test/readonly01.dbf ' size 128M;

Tablespace created.

Sql> alter tablespace readonly add datafile '/home/oracle/.oradata/test/readonly02.dbf ' size 128M;

Tablespace altered.
Write Data
Sql> CREATE TABLE t_readonly tablespace readonly as SELECT * from Dba_objects;

Table created.
Read Only table space
Sql> Select COUNT (*) from t_readonly;

COUNT (*)
----------
72226

sql> alter system switch logfile;

System altered.

Sql>/

System altered.

Sql>/

System altered.

Sql> alter tablespace ReadOnly Read only;

Tablespace altered.

sql> alter system switch logfile;

System altered.

Sql>/

System altered.

Sql>/

System altered.

Sql>/

System altered.
Backing up a database
[Oracle@localhost ~]$ Rman Target/

Recovery manager:release 11.2.0.1.0-production on Tue Nov 1 21:15:51 2016

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

Connected to target Database:test (dbid=2210907828)

Rman> Backup Database format '/home/oracle/full_%u.rman ';

Starting backup at 01-nov-16
Using target database control file instead of recovery catalog
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=197 Device Type=disk
Channel ora_disk_1:starting full DataFile backup set
Channel ora_disk_1:specifying DataFile (s) in backup set
Input datafile file number=00001 name=/home/oracle/.oradata/test/system01.dbf
Input datafile file number=00002 name=/home/oracle/.oradata/test/sysaux01.dbf
Input datafile file number=00005 name=/home/oracle/.oradata/test/test01.dbf
Input datafile file number=00006 name=/home/oracle/.oradata/test/test02.dbf
Input datafile file number=00007 name=/home/oracle/.oradata/test/readonly01.dbf
Input datafile file number=00008 name=/home/oracle/.oradata/test/readonly02.dbf
Input datafile file number=00003 name=/home/oracle/.oradata/test/undotbs01.dbf
Input datafile file number=00004 name=/home/oracle/.oradata/test/users01.dbf
Channel ora_disk_1:starting piece 1 at 01-nov-16


Channel ora_disk_1:finished piece 1 at 01-nov-16
Piece Handle=/home/oracle/full_03rjrp0t_1_1.rman tag=tag20161101t211613 comment=none
Channel Ora_disk_1:backup set complete, elapsed time:00:00:25
Channel ora_disk_1:starting full DataFile backup set
Channel ora_disk_1:specifying DataFile (s) in backup set
Including control file in backup set
Including current SPFILE in backup set
Channel ora_disk_1:starting piece 1 at 01-nov-16
Channel ora_disk_1:finished piece 1 at 01-nov-16
Piece Handle=/home/oracle/full_04rjrp1m_1_1.rman tag=tag20161101t211613 comment=none
Channel Ora_disk_1:backup set complete, elapsed time:00:00:01
Finished backup at 01-nov-16

Rman> SQL ' alter system archive log current ';
Backup as compressed backupset archivelog all format '/home/oracle/arch_%t_%u.rman ' delete input;

SQL Statement:alter System Archive log Current

Rman>
Starting backup at 01-nov-16
Current log Archived
Using channel Ora_disk_1
Channel ora_disk_1:starting compressed archived log backup Set
Channel ora_disk_1:specifying archived log (s) in backup set
Input archived log thread=1 sequence=494 recid=1 stamp=926802386
Input archived log thread=1 sequence=495 recid=2 stamp=926802386
Input archived log thread=1 sequence=496 recid=3 stamp=926802389
Input archived log thread=1 sequence=497 recid=4 stamp=926802693
Input archived log thread=1 sequence=498 recid=5 stamp=926802693
Input archived log thread=1 sequence=499 recid=6 stamp=926802696
Input archived log thread=1 sequence=500 recid=7 stamp=926802787
Input archived log thread=1 sequence=501 recid=8 stamp=926802789
Input archived log thread=1 sequence=502 recid=9 stamp=926802792
Input archived log thread=1 sequence=503 recid=10 stamp=926802793
Input archived log thread=1 sequence=504 recid=11 stamp=926802812
Input archived log thread=1 sequence=505 recid=12 stamp=926802813
Input archived log thread=1 sequence=506 recid=13 stamp=926802816
Input archived log thread=1 sequence=507 recid=14 stamp=926803076
Input archived log thread=1 sequence=508 recid=15 stamp=926803077
Channel ora_disk_1:starting piece 1 at 01-nov-16
Channel ora_disk_1:finished piece 1 at 01-nov-16
Piece Handle=/home/oracle/arch_20161101_05rjrp45_1_1.rman tag=tag20161101t211757 comment=none
Channel Ora_disk_1:backup set complete, elapsed time:00:00:03
Channel ora_disk_1:deleting archived log (s)
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_494_d1k4tkot_.arc RECID= 1 stamp=926802386
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_495_d1k4tln7_.arc RECID= 2 stamp=926802386
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_496_d1k4tot5_.arc RECID= 3 stamp=926802389
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_497_d1k544w3_.arc RECID= 4 stamp=926802693
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_498_d1k545wc_.arc RECID= 5 stamp=926802693
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_499_d1k548bm_.arc RECID= 6 stamp=926802696
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_500_d1k5734v_.arc RECID= 7 stamp=926802787
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_501_d1k5752s_.arc RECID= 8 stamp=926802789
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_502_d1k578c2_.arc RECID= 9 stamp=926802792
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_503_d1k579hy_.arc RECID= Ten stamp=926802793
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_504_d1k57w6s_.arc RECID= One stamp=926802812
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_505_d1k57xj1_.arc RECID= stamp=926802813
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_506_d1k580hj_.arc RECID= stamp=926802816
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_507_d1k5j4q0_.arc RECID= stamp=926803076
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_508_d1k5j4yq_.arc RECID= stamp=926803077
Finished backup at 01-nov-16

rman> backup format '/home/oracle/ctl_%t_%u.rman ' current controlfile;

Starting backup at 01-nov-16
Using channel Ora_disk_1
Channel ora_disk_1:starting full DataFile backup set
Channel ora_disk_1:specifying DataFile (s) in backup set
Including control file in backup set
Channel ora_disk_1:starting piece 1 at 01-nov-16
Channel ora_disk_1:finished piece 1 at 01-nov-16
Piece Handle=/home/oracle/ctl_20161101_06rjrp75_1_1.rman tag=tag20161101t211933 comment=none
Channel Ora_disk_1:backup set complete, elapsed time:00:00:01
Finished backup at 01-nov-16
Clean Environment Restore Database
rman> shutdown immediate;

Database closed
Database dismounted
Oracle instance shut down

Rman> exit


Recovery Manager complete.

[Oracle@localhost. oradata]$ MV Test test_20161101
[Oracle@localhost. oradata]$ mkdir Test
Restore Database
[Oracle@localhost ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.1.0 Production on Tue Nov 1 21:21:09 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to a idle instance.

sql> startup Nomount;
ORACLE instance started.

Total System Global area 2421825536 bytes
Fixed Size 2215744 bytes
Variable Size 1795162304 bytes
Database buffers 603979776 bytes
Redo buffers 20467712 bytes
Sql> exit
Disconnected from Oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options
[Oracle@localhost ~]$ Rman Target/

Recovery manager:release 11.2.0.1.0-production on Tue Nov 1 21:21:22 2016

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

Connected to target database:test (not mounted)

rman> restore Controlfile from '/home/oracle/ctl_20161101_06rjrp75_1_1.rman ';

Starting restore at 01-nov-16
Using target database control file instead of recovery catalog
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=63 Device Type=disk

Channel ora_disk_1:restoring Control File
Channel Ora_disk_1:restore complete, elapsed time:00:00:01
Output File Name=/home/oracle/.oradata/test/control01.ctl
Finished restore at 01-nov-16

Rman> ALTER DATABASE Mount;

Database mounted
Released Channel:ora_disk_1

rman> Restore Database;

Starting restore at 01-nov-16
Starting implicit crosscheck backup at 01-nov-16
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=63 Device Type=disk
Crosschecked 3 Objects
Finished implicit crosscheck backup at 01-nov-16

Starting implicit crosscheck copy at 01-nov-16
Using channel Ora_disk_1
Finished implicit crosscheck copy at 01-nov-16

Searching for all files in the recovery area
Cataloging files ...
No files cataloged

Using channel Ora_disk_1

DataFile 5 processed because file is offline
DataFile 6 processed because file is offline
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/home/oracle/.oradata/test/system01.dbf
Channel ora_disk_1:restoring datafile 00002 to/home/oracle/.oradata/test/sysaux01.dbf
Channel ora_disk_1:restoring datafile 00003 to/home/oracle/.oradata/test/undotbs01.dbf
Channel ora_disk_1:restoring datafile 00004 to/home/oracle/.oradata/test/users01.dbf
Channel ora_disk_1:restoring datafile 00007 to/home/oracle/.oradata/test/readonly01.dbf
Channel ora_disk_1:restoring datafile 00008 to/home/oracle/.oradata/test/readonly02.dbf
Channel ora_disk_1:reading from backup Piece/home/oracle/full_03rjrp0t_1_1.rman
Channel Ora_disk_1:piece Handle=/home/oracle/full_03rjrp0t_1_1.rman tag=tag20161101t211613
Channel ora_disk_1:restored backup Piece 1
Channel Ora_disk_1:restore complete, elapsed time:00:00:15
Finished restore at 01-nov-16

Rman> exit


Recovery Manager complete.
Checking database files
Read_only1
It is clear from Oracle Database Recovery Check that the file status is read only.
Recover database
[Oracle@localhost ~]$ Rman Target/

Recovery manager:release 11.2.0.1.0-production on Tue Nov 1 21:28:14 2016

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

Connected to target Database:test (dbid=2210907828, not open)

rman> Recover database;

Starting recover at 01-nov-16
Using target database control file instead of recovery catalog
Allocated Channel:ora_disk_1
Channel ora_disk_1:sid=63 Device Type=disk
DataFile 7 processed because file is read-only <<<<===== note
DataFile 8 processed because file is read-only <<<<===== note

Starting Media recovery

Channel ora_disk_1:starting archived log restore to default destination
Channel ora_disk_1:restoring archived Log
Archived Log thread=1 sequence=507
Channel ora_disk_1:restoring archived Log
Archived Log thread=1 sequence=508
Channel ora_disk_1:reading from backup Piece/home/oracle/arch_20161101_05rjrp45_1_1.rman
Channel Ora_disk_1:piece Handle=/home/oracle/arch_20161101_05rjrp45_1_1.rman tag=tag20161101t211757
Channel ora_disk_1:restored backup Piece 1
Channel Ora_disk_1:restore complete, elapsed time:00:00:01
Archived log File Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_507_d1k63pj8_.arc thread =1 sequence=507
Channel default:deleting archived log (s)
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_507_d1k63pj8_.arc RECID= stamp=926803702
Archived log File Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_508_d1k63pww_.arc thread =1 sequence=508
Channel default:deleting archived log (s)
Archived log file Name=/opt/oracle/flash_recovery_area/test/archivelog/2016_11_01/o1_mf_1_508_d1k63pww_.arc RECID= stamp=926803702
Unable to find archived log
Archived Log thread=1 sequence=509
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR message STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of recover command at 11/01/2016 21:28:24
Rman-06054:media recovery requesting unknown archived log for thread 1 with sequence 509 and starting SCN of 8933048

Rman> exit
Resetlogs Open the database
Sql> Set NUMW 16
Sql> SELECT Status,
checkpoint_change#,
2 3 checkpoint_time,last_change#,
COUNT (*) row_num
From V$datafile
GROUP by Status, checkpoint_change#, checkpoint_time,last_change#
Order by status, checkpoint_change#, Checkpoint_time; 4 5 6 7

STATUS checkpoint_change# checkpoin last_change# row_num
------- ------------------ --------- ---------------- ----------------
ONLINE 8932792 01-nov-16 2
ONLINE 8933048 01-nov-16 3
SYSTEM 8933048 01-nov-16 1

Sql> SELECT Status,
2 checkpoint_change#,
3 Checkpoint_time,fuzzy,
4 Count (*) row_num
5 from V$datafile_header
GROUP by Status, checkpoint_change#, Checkpoint_time,fuzzy
Order by status, checkpoint_change#, Checkpoint_time;
6 7
STATUS checkpoint_change# checkpoin Fuz row_num
------- ------------------ --------- --- ----------------
ONLINE 8932792 01-nov-16 NO 2
ONLINE 8933048 01-nov-16 NO 4

sql> ALTER DATABASE open resetlogs;

Database altered.
Alert Log Information
Tue Nov 01 21:29:56 2016
ALTER DATABASE open Resetlogs
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00312:online Log 1 Thread 1: '/home/oracle/.oradata/test/redo01.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00312:online Log 1 Thread 1: '/home/oracle/.oradata/test/redo01.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 2 of thread 1
Ora-00312:online Log 2 thread 1: '/home/oracle/.oradata/test/redo02.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 2 of thread 1
Ora-00312:online Log 2 thread 1: '/home/oracle/.oradata/test/redo02.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/home/oracle/.oradata/test/redo03.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/home/oracle/.oradata/test/redo03.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Resetlogs after incomplete recovery UNTIL change 8933048
Resetting resetlogs activation ID 2210869684 (0X83C731B4)
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00312:online Log 1 Thread 1: '/home/oracle/.oradata/test/redo01.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00312:online Log 1 Thread 1: '/home/oracle/.oradata/test/redo01.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 2 of thread 1
Ora-00312:online Log 2 thread 1: '/home/oracle/.oradata/test/redo02.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 2 of thread 1
Ora-00312:online Log 2 thread 1: '/home/oracle/.oradata/test/redo02.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/home/oracle/.oradata/test/redo03.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_ORA_14479.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/home/oracle/.oradata/test/redo03.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Tue Nov 01 21:29:58 2016
Setting recovery target incarnation to 3
Tue Nov 01 21:29:58 2016
assigning activation ID 2224900353 (0x849d4901)
Lgwr:starting ARCH Processes
Tue Nov 01 21:29:58 2016
ARC0 started with pid=20, OS id=14486
Arc0:archival started
Lgwr:starting ARCH Processes COMPLETE
Arc0:starting ARCH Processes
Tue Nov 01 21:29:59 2016
ARC1 started with pid=21, OS id=14488
Tue Nov 01 21:29:59 2016
ARC2 started with pid=22, OS id=14490
Arc1:archival started
Tue Nov 01 21:29:59 2016
ARC3 started with pid=23, OS id=14492
Arc2:archival started
Arc1:becoming the ' no FAL ' ARCH
Arc1:becoming the ' no SRL ' ARCH
Arc2:becoming the Heartbeat ARCH
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0:/home/oracle/.oradata/test/redo01.log
Successful open of Redo thread 1
MTTR advisory is disabled because fast_start_mttr_target are not set
Tue Nov 01 21:29:59 2016
Smon:enabling Cache Recovery
Successfully onlined Undo tablespace 2.
Dictionary Check Beginning
The File #7 is offline, but are part of the online tablespace.
Data file 7: '/HOME/ORACLE/.ORADATA/TEST/READONLY01.DBF '
Successfuly brought file #7 online.
The File #8 is offline, but are part of the online tablespace.
Data file 8: '/HOME/ORACLE/.ORADATA/TEST/READONLY02.DBF '
Successfuly brought file #8 online.
Tue Nov 01 21:29:59 2016
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_DBW0_14226.TRC:
Ora-01157:cannot identify/lock data file 201-see DBWR trace file
Ora-01110:data file 201: '/HOME/ORACLE/.ORADATA/TEST/TEMP01.DBF '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/OPT/ORACLE/DIAG/RDBMS/TEST/TEST/TRACE/TEST_DBW0_14226.TRC:
Ora-01186:file 201 Failed verification tests
Ora-01157:cannot identify/lock data file 201-see DBWR trace file
Ora-01110:data file 201: '/HOME/ORACLE/.ORADATA/TEST/TEMP01.DBF '
File 201 not verified due to error ORA-01157
Dictionary Check Complete
Verifying file header compatibility for 11g tablespace encryption.
Verifying 11g file Header compatibility for tablespace encryption completed
smon:enabling TX Recovery
Re-creating TEMPFILE/HOME/ORACLE/.ORADATA/TEST/TEMP01.DBF
Database Characterset is Al32utf8
No Resource Manager Plan Active
Replication_dependency_tracking turned off (no async multimaster replication found)
Starting background Process QMNC
Tue Nov 01 21:30:00 2016
QMNC started with pid=24, OS id=14494
Logstdby:validating Controlfile with logical metadata
Logstdby:validation Complete
Arc3:archival started
Arc0:starting ARCH Processes COMPLETE
Completed:alter Database Open Resetlogs
Here we see the warning we're looking forward to.
The File #7 is offline, but are part of the online tablespace.
Data file 7: '/HOME/ORACLE/.ORADATA/TEST/READONLY01.DBF '
Successfuly brought file #7 online.
The File #8 is offline, but are part of the online tablespace.
Data file 8: '/HOME/ORACLE/.ORADATA/TEST/READONLY02.DBF '
Successfuly brought file #8 online.
Conclusion: If the table space of the database was read only before the resetlogs operation there would be a similar hint (File #N is offline, but was part of the online tablespace). This entire recovery process does not affect read on LY data in the table space

Original from: http://www.xifenfei.com/2016/11/read-only-tablespace-resetlogs.html