[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                    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

SCOTT@book> alter database datafile 6 offline ;
Database altered.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile ;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  LAST_CHANGE# LAST_TIME           OFFLINE_CHANGE# ONLINE_CHANGE# STATUS     NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ---------- --------------------------------
    1            2001360 2016-10-19 15:28:30                     0                                                              2001356        2001357 SYSTEM     /mnt/ramdisk/book/system01.dbf
    2            2001360 2016-10-19 15:28:30                     0                                                              2001356        2001357 ONLINE     /mnt/ramdisk/book/sysaux01.dbf
    3            2001360 2016-10-19 15:28:30                     0                                                              2001356        2001357 ONLINE     /mnt/ramdisk/book/undotbs01.dbf
    4            2001360 2016-10-19 15:28:30                     0                                                              2001356        2001357 ONLINE     /mnt/ramdisk/book/users01.dbf
    5            2001360 2016-10-19 15:28:30                     0                                                              2001356        2001357 ONLINE     /mnt/ramdisk/book/example01.dbf
    6            2001360 2016-10-19 15:28:30                     0 2016-10-12 08:59:30      2001718 2016-10-19 15:31:06         2001356        2001357 RECOVER    /mnt/ramdisk/book/sugar01.dbf

6 rows selected.

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

2.建立测试：

SYS@book> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

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.

-改名sugar01.dbf，不然会一起恢复。
$ mv sugar01.dbf sugar01.dbf_xxx

SYS@book> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'

--做恢复时会自动online离线的数据文件。

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

SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 2002063 generated at 10/19/2016 15:32:28 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_8_925658910.dbf
ORA-00280: change 2002063 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo02.log
Log applied.
Media recovery complete.

--这个时候不再open打开数据库。
SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@book> alter database open resetlogs;
Database altered.

--move 归档文件。
$ cd /u01/app/oracle/archivelog/book
$ mkdir backup
$ mv *.dbf backup/

--改名sugaar01.dbf 回来
$ cd /mnt/ramdisk/book
$ mv sugar01.dbf_xxx sugar01.dbf

RMAN> recover datafile 6 ;
Starting recover at 2016-10-19 15:39:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/19/2016 15:39:49
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed datafile 6
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 6 belongs to an orphan incarnation
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'

--数据文件6已经不属于这个incarnation。

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            2002068 2016-10-19 15:36:06                7           2002065 ONLINE                  176 YES /mnt/ramdisk/book/system01.dbf                     SYSTEM
    2            2002068 2016-10-19 15:36:06             1834           2002065 ONLINE                  174 YES /mnt/ramdisk/book/sysaux01.dbf                     SYSAUX
    3            2002068 2016-10-19 15:36:06           923328           2002065 ONLINE                   95 YES /mnt/ramdisk/book/undotbs01.dbf                    UNDOTBS1
    4            2002068 2016-10-19 15:36:06            16143           2002065 ONLINE                  173 YES /mnt/ramdisk/book/users01.dbf                      USERS
    5            2002068 2016-10-19 15:36:06           952916           2002065 ONLINE                   93 YES /mnt/ramdisk/book/example01.dbf                    EXAMPLE
    6            2001360 2016-10-19 15:28:30          1730665           2001357 OFFLINE                  52 YES /mnt/ramdisk/book/sugar01.dbf                      SUGAR
6 rows selected.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  LAST_CHANGE# LAST_TIME           OFFLINE_CHANGE# ONLINE_CHANGE# STATUS     NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ---------- --------------------------------------------------
    1            2002068 2016-10-19 15:36:06                     0                                                              2002064        2002065 SYSTEM     /mnt/ramdisk/book/system01.dbf
    2            2002068 2016-10-19 15:36:06                     0                                                              2002064        2002065 ONLINE     /mnt/ramdisk/book/sysaux01.dbf
    3            2002068 2016-10-19 15:36:06                     0                                                              2002064        2002065 ONLINE     /mnt/ramdisk/book/undotbs01.dbf
    4            2002068 2016-10-19 15:36:06                     0                                                              2002064        2002065 ONLINE     /mnt/ramdisk/book/users01.dbf
    5            2002068 2016-10-19 15:36:06                     0                                                              2002064        2002065 ONLINE     /mnt/ramdisk/book/example01.dbf
    6            2001360 2016-10-19 15:28:30                     0 2016-10-12 08:59:30      2002068 2016-10-19 15:36:06         2002064        2002065 OFFLINE    /mnt/ramdisk/book/sugar01.dbf
6 rows selected.

--修改数据块的scn=LAST_CHANGE#=2002068.

BBED> p /d kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      2001360

--顺便看看RESETLOGS_ID在数据块的位置，不知道是否也需要修改

SYS@book> select * from v$database_incarnation ;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS     RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------------- ----------------------- ------------------- ---------- ------------ ------------------ --------------------------
           1                 1 2013-08-24 11:37:30                       0                     PARENT        824297850                  0 NO
           2            925702 2015-11-24 09:11:12                       1 2013-08-24 11:37:30 PARENT        896605872                  1 NO
           3           2001357 2016-10-19 15:28:30                  925702 2015-11-24 09:11:12 PARENT        925658910                  2 NO
           4           2002065 2016-10-19 15:36:06                 2001357 2016-10-19 15:28:30 CURRENT       925659366                  3 NO

BBED> p /d dba 1,1 kcvfh.kcvfhrlc
ub4 kcvfhrlc                                @112      925659366

BBED> p /d dba 6,1 kcvfh.kcvfhrlc
ub4 kcvfhrlc                                @112      925658910

BBED> assign dba 6,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas=2002068
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas                                 @484      0x001e8c94

BBED> sum apply
Check value for File 6, Block 1:
current = 0x012a, required = 0x012a

RMAN> recover datafile 6 ;
Starting recover at 2016-10-19 15:48:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/19/2016 15:48:24
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed datafile 6
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 6 belongs to an orphan incarnation
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'

--不行。

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,6);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
    1            2002068 2016-10-19 15:36:06                7           2002065 ONLINE                  176 YES /mnt/ramdisk/book/system01.dbf                     SYSTEM
    6            2002068 2016-10-19 15:28:30          1730665           2001357 OFFLINE                  52 YES /mnt/ramdisk/book/sugar01.dbf                      SUGAR

--修改为 2002065-1=2002064 看看。这个是我以前的测试，要修改到RESETLOGS_CHANGE#-1的值。

BBED> set dba 6,1
        DBA             0x01800001 (25165825 6,1)

BBED> p /d kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      2002068

BBED> assign dba 6,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas=2002064;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 kscnbas                                 @484      0x001e8c90

BBED> sum apply
Check value for File 6, Block 1:
current = 0x012e, required = 0x012e

RMAN> recover datafile 6 ;
Starting recover at 2016-10-19 15:53:51
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-10-19 15:53:51

BBED> p /d dba 6,1 kcvfh.kcvfhrlc
ub4 kcvfhrlc                                @112      925659366

BBED> p /d dba 1,1 kcvfh.kcvfhrlc
ub4 kcvfhrlc                                @112      925659366

--这样RESETLOGS_ID也恢复了。实际上如果按照前面的恢复直接修改kcvfh.kcvfhrlc=925659366,应该也可以。

时间： 2024-10-24 16:12:44

[20161019]数据文件offline与open resetlog的相关文章

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

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

20160331数据文件offline与open resetlogs2

[20160331]数据文件offline与open resetlogs3.txt --接上面的测试.链接: --关机做一个冷备份,便于重复测试.取出冷备份,重复测试: --做一些必要的清理清除归档. 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------

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

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

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

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