[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,應該也可以。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2126744/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 20160331資料檔案offline與open resetlogs
- [20161019]資料檔案offline後恢復到那個scn
- 20160331資料檔案offline與open resetlogs3S3
- 20160331資料檔案offline與open resetlogs2
- oracle 資料檔案offlineOracle
- OFFLINE和DROP資料檔案的理解
- 表空間OFFLINE和資料檔案OFFLINE的區別
- 表空間offline,資料檔案offline 的區別(ZT)
- 利用offline datafile檔案方式遷移資料
- 資料檔案OFFLINE的3種情況
- 表空間與資料檔案的offline和online操作
- 資料檔案、表空間offline用法及區別
- 資料檔案offline後unusable索引造成的問題索引
- 檔案與資料
- Data Guard 主端OFFLINE資料檔案和表空間
- [20160329]bbed修復offline的資料檔案.txt
- 重建控制檔案與 datafile offline,tablespace read only
- 資料檔案實驗操作datafile的create/offline/drop/rename等操作
- 轉載-表空間和資料檔案offline的影響分析
- online/offline 表空間和資料檔案需謹慎！
- 在open狀態下恢復丟失的資料檔案
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- 非歸檔模式下恢復利用offline drop命令誤刪除的資料檔案模式
- 臨時資料檔案 offline 對於匯入匯出的影響
- [20151028]理解資料檔案offline+drop.txt
- 有關 SCN 和 RESETLOG的一些資料
- 在open狀態下恢復未備份的資料檔案
- 資料檔案合併與拆分
- JAVA檔案與資料流（1）Java
- resetlog理解
- [20180202]備庫資料檔案offline.txt
- [20161012]資料檔案offline馬上執行recover
- 資料檔案offline後，再online時，提示需要介質恢復。
- Oracle單個資料檔案損壞，在Rman命令裡設定表空間、資料檔案offline方式來恢復最方便Oracle
- 案例：在open狀態下恢復未備份的資料檔案
- ORA-00279異常處理_offline資料檔案缺失日誌檔案問題一鍵修復
- Oracle 表空間與資料檔案Oracle
- 新增資料檔案與恢復cf