ORA-01190&ORA-19909

实验的时候碰到一个错误：

SQL> create table t1 as select rownum rn from dual connect by rownum <= 10000;
create table t1 as select rownum rn from dual connect by rownum <= 10000
                                         *
ERROR at line 1:
ORA-01542: tablespace 'USERS' is offline, cannot allocate space in it

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u01/app/oracle/oradata/inst1/users01.dbf'

Answer:   The ORA-19909 error occurs when you have made an error choosing a restore file that is part of the current (or prior) incarnation of the database.   The oerr utility show this for the ORA-19909 error:

一次 incarnation代表: alter database open resetlogs


SQL> recover tablespace users;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 4 belongs to an orphan incarnation
ORA-01110: data file 4: '/u01/app/oracle/oradata/inst1/users01.dbf'




SQL> alter tablespace users online;                        
alter tablespace users online
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u01/app/oracle/oradata/inst1/users01.dbf'


这时， USERS表空间需要恢复。 通过RMAN恢复表空间

RMAN> restore tablespace users;


Starting restore at 24-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=144 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 00004 to /u01/app/oracle/oradata/inst1/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/12r11rsu_1_1
channel ORA_DISK_1: piece handle=/home/oracle/12r11rsu_1_1 tag=TESTDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-MAR-16


RMAN> recover tablespace users;


Starting recover at 24-MAR-16
using channel ORA_DISK_1
using channel ORA_DISK_2


starting media recovery


archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_23/o1_mf_1_4_ch58cr3z_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_5_ch6h0llh_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/19r1226s_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=18
channel ORA_DISK_2: reading from backup piece /home/oracle/1ar1226t_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/19r1226s_1_1 tag=TAG20160321T162044
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
以下多行输出略过

archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_22_ch6p4ojr_.arc thread=1 sequence=22
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_22_ch6p4ojr_.arc RECID=49 STAMP=907326105
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_23_ch6p4ok2_.arc thread=1 sequence=23
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_23_ch6p4ok2_.arc RECID=47 STAMP=907326104
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_24_ch6p4okc_.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_1_ch6p4or0_.arc thread=1 sequence=1
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_1_ch6p4or0_.arc RECID=46 STAMP=907326103
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_2_ch6p4ors_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_24_ch6p4okc_.arc RECID=48 STAMP=907326105
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_2_ch6p4ors_.arc RECID=45 STAMP=907326103
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/INST1/archivelog/2016_03_24/o1_mf_1_3_ch6p4sl0_.arc RECID=50 STAMP=907326105
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-MAR-16

再把表空间online。
SQL> alter tablespace users online;


Tablespace altered.

参考： http://www.dba-oracle.com/t_ora_19909.htm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-2062997/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-2062997/