[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,应该也可以。
