联系：手机/微信(+86 17813235971) QQ(107644445)
标题：File #N is offline, but is part of an online tablespace
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在看一个客户的数据库恢复日志的时候发现类似警告(File #N is offline, but is part of an online tablespace.),以前没有注意,这次通过试验来重现该部分内容
创建表空间
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.
写入数据
SQL> create table t_readonly tablespace readonly as select * from dba_objects; Table created.
read only 表空间
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.
备份数据库
[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 current 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=10 STAMP=926802793 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_504_d1k57w6s_.arc RECID=11 STAMP=926802812 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_505_d1k57xj1_.arc RECID=12 STAMP=926802813 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_506_d1k580hj_.arc RECID=13 STAMP=926802816 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_507_d1k5j4q0_.arc RECID=14 STAMP=926803076 archived log file name=/opt/oracle/flash_recovery_area/TEST/archivelog/2016_11_01/o1_mf_1_508_d1k5j4yq_.arc RECID=15 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 current 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
清理环境还原数据库
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
还原数据库
[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 an 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 not processed because file is offline datafile 6 not 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.
检查数据库文件
通过Oracle Database Recovery Check很明显发现,这里看到文件状态是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 not processed because file is read-only <<<<=====注意 datafile 8 not processed because file is read-only <<<<=====注意 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=16 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=17 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 打开数据库
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日志信息
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 is not set Tue Nov 01 21:29:59 2016 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Dictionary check beginning File #7 is offline, but is part of an online tablespace. data file 7: '/home/oracle/.oradata/test/readonly01.dbf' Successfuly brought file #7 online. File #8 is offline, but is part of an 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
这里看到了我们期待的警告
File #7 is offline, but is part of an online tablespace.
data file 7: ‘/home/oracle/.oradata/test/readonly01.dbf’
Successfuly brought file #7 online.
File #8 is offline, but is part of an online tablespace.
data file 8: ‘/home/oracle/.oradata/test/readonly02.dbf’
Successfuly brought file #8 online.
结论：如果数据库的表空间之前是read only,然后resetlogs操作就会有类似提示(File #N is offline, but is part of an online tablespace).这样的整个恢复过程不影响read only表空间中的数据