20160331数据文件offline与open resetlogs2

[20160331]数据文件offline与open resetlogs3.txt

--接上面的测试.链接:

--关机做一个冷备份,便于重复测试.取出冷备份,重复测试:
--做一些必要的清理清除归档.

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--session 1:
update t set name='aaaa' where id=1;
commit ;

--session 2:
update t set name='bbbb' where id=2;
--不提交。

--session 3:
SYS@book> alter database datafile 7 offline ;
Database altered.

--session 2:
commit ;

alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;

--切换出全部redo到归档。

SYS@book> shutdown abort ;
ORACLE instance shut down.

2.重新打开数据库看看：

SYS@book> startup mount
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
    1        13227501245 2016-03-31 11:56:45                7       13227286650 ONLINE              1006 YES /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227501245 2016-03-31 11:56:45             1834       13227286650 ONLINE              1002 YES /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227501245 2016-03-31 11:56:45           923328       13227286650 ONLINE               922 YES /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227501245 2016-03-31 11:56:45            16143       13227286650 ONLINE              1006 YES /mnt/ramdisk/book/users01.dbf    USERS
    5        13227501245 2016-03-31 11:56:45           952916       13227286650 ONLINE               919 YES /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227501245 2016-03-31 11:56:45          1314508       13227286650 ONLINE               935 YES /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227500869 2016-03-31 11:55:43      13227207527       13227286650 OFFLINE               26 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

--如果我使用 recover database using backup controlfile until cancel;恢复后，不能在使用open打开，必须加入open resetlogs参数。
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501245 generated at 03/31/2016 11:56:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_22_907434361.dbf
ORA-00280: change 13227501245 for thread 1 is in sequence #22

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13227501251 generated at 03/31/2016 11:56:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_23_907434361.dbf
ORA-00280: change 13227501251 for thread 1 is in sequence #23
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_22_907434361.dbf' no longer needed for this recovery

ORA-00279: change 13227501257 generated at 03/31/2016 11:56:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501257 for thread 1 is in sequence #24
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_23_907434361.dbf' no longer needed for this recovery

ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'

SYS@book> @ &r/logfile
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           GROUP# STATUS     TYPE       MEMBER                                             IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
     1       1        22    52428800       512       1 YES ACTIVE       13227501245 2016-03-31 11:56:45  13227501251 2016-03-31 11:56:47      1            ONLINE     /mnt/ramdisk/book/redo01.log                       NO
     2       1        23    52428800       512       1 YES ACTIVE       13227501251 2016-03-31 11:56:47  13227501257 2016-03-31 11:56:47      2            ONLINE     /mnt/ramdisk/book/redo02.log                       NO
     3       1        24    52428800       512       1 NO  CURRENT      13227501257 2016-03-31 11:56:47 2.814750E+14                          3            ONLINE     /mnt/ramdisk/book/redo03.log                       NO

--还需要 /mnt/ramdisk/book/redo03.log.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                            TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------- ---------------
    1        13227501257 2016-03-31 11:56:47                7       13227286650 ONLINE                 1006 YES /mnt/ramdisk/book/system01.dbf  SYSTEM
    7        13227500869 2016-03-31 11:55:43      13227207527       13227286650 ONLINE                   26 YES /mnt/ramdisk/book/tea01.dbf     TEA

--另外可以发现数据文件7在recover时已经设置为online.人为再次设置为offline.

SYS@book> alter database datafile 7 offline ;
Database altered.

--继续恢复.

SYS@book> alter database datafile 7 offline ;
Database altered.
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501257 generated at 03/31/2016 11:56:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501257 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo03.log
Log applied.
Media recovery complete.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                            TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------- ---------------
    1        13227501259 2016-03-31 11:56:47                7       13227286650 ONLINE                 1007 NO  /mnt/ramdisk/book/system01.dbf  SYSTEM
    7        13227500869 2016-03-31 11:55:43      13227207527       13227286650 OFFLINE                  26 YES /mnt/ramdisk/book/tea01.dbf     TEA

--注意一个细节,数据文件7 是offline状态.也就是在mount状态设置offline,在恢复时不会在变成online.这样恢复就不像前面那样需要seq=20,21,22,23 归档.
--不知道这个细节oracle如何控制的???
--恢复仅仅需要/mnt/ramdisk/book/redo03.log,而数据文件7的CHECKPOINT_CHANGE#依旧等于13227500869.也就是没有进行任何恢复.
SYS@book> alter database open resetlogs;
Database altered.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                           TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- ------------------------------ ---------------
    1        13227501263 2016-03-31 12:03:48                7       13227501260 ONLINE                 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM
    7        13227500869 2016-03-31 11:55:43      13227207527       13227286650 OFFLINE                  26 YES /mnt/ramdisk/book/tea01.dbf    TEA

--数据文件7的RESETLOGS_CHANGE#与数据文件1的RESETLOGS_CHANGE#不一致.

SYS@book> alter database datafile 7 online ;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01190: control file or data file 7 is from before the last RESETLOGS
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

--不要被ORA-01190提示蒙骗,实际上10g以后可以跨resetlogs恢复,只要归档日志全部都在.
SYS@book> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 7 belongs to an orphan incarnation
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

--这次不行,因为我前一次恢复数据文件7CHECKPOINT_CHANGE#,RESETLOGS_CHANGE#仅仅相差1,完成了前面的恢复.所以recover datafile 7 ok.
3.尝试使用rman恢复:
--我尝试使用 恢复到resetlog之前13227501259.

recover datafile 7 until scn 13227501259;

reset database to incarnation 4;
recover datafile 7 until scn 13227501259;

--都不行,视乎提示要使用旧的控制文件.恢复过程略....仅仅注意我使用的是冷备份,要使用如下方式:

SYS@book> alter database datafile 1,2,3,4,5,6 offline ;
Database altered.

SYS@book> recover database using backup controlfile until change 13227501259;
ORA-00279: change 13227500866 generated at 03/31/2016 08:53:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_20_907434361.dbf
ORA-00280: change 13227500866 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13227501237 generated at 03/31/2016 11:56:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_21_907434361.dbf
ORA-00280: change 13227501237 for thread 1 is in sequence #21
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_20_907434361.dbf' no longer needed for this recovery

ORA-00279: change 13227501245 generated at 03/31/2016 11:56:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_22_907434361.dbf
ORA-00280: change 13227501245 for thread 1 is in sequence #22
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_21_907434361.dbf' no longer needed for this recovery

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'

ORA-01112: media recovery not started

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                        TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- --------------------------- ----------------
    1                  0                                    0                 0 OFFLINE                0
    7        13227501259 2016-03-31 11:56:47      13227207527       13227286650 ONLINE                25 NO  /mnt/ramdisk/book/tea01.dbf TEA

--然后要拷贝回去.步骤忽略.

4.继续恢复:

SYS@book> startup mount
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                            TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
    1        13227501915 2016-03-31 12:05:34                7       13227501260 ONLINE              1010 NO  /mnt/ramdisk/book/system01.dbf  SYSTEM
    7        13227501259 2016-03-31 11:56:47      13227207527       13227286650 OFFLINE               25 NO  /mnt/ramdisk/book/tea01.dbf     TEA

alter database open;

SYS@book> alter database datafile 7 online ;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01190: control file or data file 7 is from before the last RESETLOGS
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--不要再次蒙骗,仅仅差1.

SYS@book> recover datafile 7 ;
Media recovery complete.

SYS@book> alter database datafile 7 online ;
Database altered.

SYS@book> select rowid,t.* from scott.t ;
ROWID                        ID NAME
------------------ ------------ ---------
AAAWgeAAHAAAACLAAA            1 aaaa
AAAWgeAAHAAAACLAAB            2 bbbb
AAAWgeAAHAAAACLAAC            3 text
AAAWgeAAHAAAACLAAD            4 text

--没有任何丢失.
--从这些测试也说明,在恢复执行alter database open resetlogs,一定要确定是否有数据文件还处在offline的状态.避免不必要的麻烦.

时间： 2024-10-22 14:47:18

20160331数据文件offline与open resetlogs2的相关文章

临时数据文件 offline 对于导入导出的影响

临时数据文件 offline 对于导入导出的影响 sys@ORACL> alter database tempfile 'd:\oracle\oradata\oracl\temp01.dbf' offline; 数据库已更改. sys@ORACL> ================================================tempfile offline的情况. 1 导出少量数据时,没有报错,当导出大量数据时,会报EXP-00068: 表空间 TEMP 脱机 C:\Use

[20161019]数据文件offline后恢复到那个scn

[20161019]数据文件offline后恢复到那个scn号.txt --前一天别人问的问题,如果数据文件offline时,online要恢复,一般恢复到scn是多少,是offline时的scn吗? --总不见得如果长时间offline,要应用许多归档日志吧,通过测试说明问题: 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ----------------------------

[20161012]数据文件offline马上执行recover

[20161012]数据文件offline马上执行recover.txt --前几天看的1篇文章,提到数据文件offline,应该养成随手执行recover习惯.保证下一次online时,不需要恢复. --如果offline很久,忘记online了,而归档日志已经不存在,该如何跳过应用日志,online数据文件呢? --以前也做过一些测试,再重复测试看看.(注意:这样恢复存在数据丢失的风险) 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING           

[20161019]数据文件offline与open resetlog

[20161019]数据文件offline与open resetlog.txt --上午做了数据文件offline后恢复到那个scn号,恢复到该数据文件的LAST_CHANGE#的scn值. --如果数据文件做了offline,在以后数据库做了open resetlog后,如何online呢?如果归档存在没有问题, --但是10g开始支持跨resetlog的恢复.自己以前也做了类似的测试. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING            

[20151028]理解数据文件offline+drop.txt

[20151028]理解数据文件offline+drop.txt --前几天做删除数据文件的恢复测试,自己在理解offline drop的方式存在错误,做一个记录: The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlin

数据文件offline后unusable索引造成的问题

最近在做一个oracle入库速度测试时,想到将最近一个小时的索引放到内存中(表是按小时分区)是不是会提升入库的速度,索引的维护对io是一个不小的开销: 不过这个方案如果要使用的话数据库必须是 oracle 12c,因为在当前小时结束后,需要将相关索引移出内存,让下一个小时的索引留在内存,这样内存的使用情况基本是一个定量: 而在移动的过程中不能对业务有影响,这样需要用到12c的新功能,在线移动数据文件. 测试的结果是入库速度有很明显的提升,入库速度是之前的几倍,但这个不是本文的重点:本文的重点是在

[20160329]bbed修复offline的数据文件.txt

[20160329]bbed修复offline的数据文件.txt --测试数据库,不小心将一个数据文件offline了,archivelog也删除了(主要磁盘空间紧张,做了一次整理). --自己测试修复看看,顺便做一个记录. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------

数据文件、表空间offline用法及区别

对数据库的脱机包括数据文件的脱机和对表空间的脱机,表空间脱机实际就是表空间对应的所有数据文件脱机. 1.         数据文件OFFLINE 数据文件添加到表空间之后不能够被删除的,没有语法支持这么做,如果想不使用该数据文件,唯一是将数据文件设置为OFFLINE状态.执行以下步骤将数据文件设置为OFFLINE状态: 1)         如果是归档模式可以执行如下SQL设置数据文件的状态为OFFLINE: ALTER DATABASE DATAFILE 'XXXX.DBF' OFFLINE;

迁移数据文件到ASM【转】

1.迁移数据文件到ASM 1)数据库一致性情况下迁移: 将数据库启动到mount状态,生成rman copy 语句,然后在rman中执行: SQL> startup mount SQL> select q'(copy datafile ')'||name||q'(' to '+oradata';)' from v$datafile; Q'(COPYDATAFILE')'||NAME||Q'('TO'+ORADATA';)' -----------------------------------