20160331資料檔案offline與open resetlogs
[20160331]資料檔案offline與open resetlogs.txt
--昨天不小心導致一個資料檔案offline，而歸檔日誌已經刪除，無法在透過recover恢復到online，透過手工修改online資料檔案。
--連結http://blog.itpub.net/267265/viewspace-2071731/
--這讓我想起來節前跟別人聊天提到一個恢復操作，資料庫無法open，安全起見，透過dg來恢復。設定只讀模式，停止日誌應用，複製文
--件到新機器，建立新的控制檔案，開啟使用open resetlogs，悲劇發生，有一個資料檔案在做恢復時，已經處在offline狀態。結果
--resetlog時，沒有改變資料檔案的RESETLOGS_CHANGE#.提示：
ORA-01190: control file or data file XX is from before the last RESETLOGS
--我當時問出現這種情況如何恢復，我記得對方講透過提升scn來恢復，我當時覺得很奇怪，提到scn能解決這個問題嗎？資料檔案裡面記
--錄RESETLOGS_CHANGE#就能改變嗎？當時由於時間關係，這個測試我一直做，今天比較有空做一些測試：
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
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 13227498083 2016-03-31 08:34:53 7 13227286650 ONLINE 1000 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227498083 2016-03-31 08:34:53 1834 13227286650 ONLINE 996 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227498083 2016-03-31 08:34:53 923328 13227286650 ONLINE 916 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227498083 2016-03-31 08:34:53 16143 13227286650 ONLINE 1000 YES /mnt/ramdisk/book/users01.dbf USERS
5 13227498083 2016-03-31 08:34:53 952916 13227286650 ONLINE 913 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227498083 2016-03-31 08:34:53 1314508 13227286650 ONLINE 929 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227498083 2016-03-31 08:34:53 13227207527 13227286650 ONLINE 22 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
--每一次open resetlog都會改變RESETLOGS_CHANGE#，我這裡當前是13227286650。也可以透過這個檢視查詢v$database_incarnation ：
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 13227285139 2016-03-25 16:45:42 925702 2015-11-24 09:11:12 PARENT 907433142 2 NO
4 13227286650 2016-03-25 17:06:01 13227285139 2016-03-25 16:45:42 CURRENT 907434361 3 NO
--我的測試庫經歷了過幾次1->925702->13227285139->13227286650.感覺這個就是當時切換的scn號。注意後面的RESETLOGS_ID跟
--log_archive_format引數的%r有關.
--還可以透過rman的list incarnation;檢視
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 PARENT 13227285139 2016-03-25 16:45:42
4 4 BOOK 1337401710 CURRENT 13227286650 2016-03-25 17:06:01
--另外檢視引數log_archive_format，裡面的%r表示的就是v$database_incarnation的RESETLOGS_ID,這個引數必須包括%r引數。如果你
--open resetlogs的日誌的seq又從1開始。
SCOTT@book> show parameter log_archive_format
NAME TYPE VALUE
------------------- ------- ---------------
log_archive_format string %t_%s_%r.dbf
$ ll -l /u01/app/oracle/archivelog/book/
total 19952
-rw-r----- 1 oracle oinstall 20403712 2016-03-31 08:43:00 1_18_907434361.dbf
--這些資訊應該儲存在控制檔案中，如果新建控制檔案list incarnation;就看不到了。
--從這裡看出如果open resetlogs 前，最好查詢檢視v$datafile_header確定是否存在表空間或者資料檔案offline.
2.測試：
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 1536K AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SCOTT@book> create table t tablespace tea as select rownum id ,lpad('text',4) name from dual connect by level<=4;
Table created.
SCOTT@book> select rowid,t.* from scott.t ;
ROWID ID NAME
------------------ ---------- --------------------
AAAWgeAAHAAAACLAAA 1 text
AAAWgeAAHAAAACLAAB 2 text
AAAWgeAAHAAAACLAAC 3 text
AAAWgeAAHAAAACLAAD 4 text
SCOTT@book> @ &r/rowid AAAWgeAAHAAAACLAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
92190 7 139 0 7,139 alter system dump datafile 7 block 139 ;
--關機做一個冷備份,便於重複測試.
--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.
3.重新開啟資料庫看看：
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 13227501337 2016-03-31 09:59:13 7 13227286650 ONLINE 1006 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227501337 2016-03-31 09:59:13 1834 13227286650 ONLINE 1002 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227501337 2016-03-31 09:59:13 923328 13227286650 ONLINE 922 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227501337 2016-03-31 09:59:13 16143 13227286650 ONLINE 1006 YES /mnt/ramdisk/book/users01.dbf USERS
5 13227501337 2016-03-31 09:59:13 952916 13227286650 ONLINE 919 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227501337 2016-03-31 09:59:13 1314508 13227286650 ONLINE 935 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227500869 2016-03-31 09:53:40 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 13227501224 generated at 03/31/2016 09:10:29 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501224 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
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-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 INACTIVE 13227501329 2016-03-31 09:59:09 13227501333 2016-03-31 09:59:12 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 23 52428800 512 1 YES INACTIVE 13227501333 2016-03-31 09:59:12 13227501337 2016-03-31 09:59:13 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 24 52428800 512 1 NO CURRENT 13227501337 2016-03-31 09:59:13 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 13227501337 2016-03-31 09:59:13 7 13227286650 ONLINE 1006 YES /mnt/ramdisk/book/system01.dbf SYSTEM
7 13227500869 2016-03-31 09:53:40 13227207527 13227286650 ONLINE 26 YES /mnt/ramdisk/book/tea01.dbf TEA
--另外可以發現資料檔案7在recover時已經設定為online.
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227500869 generated at 03/31/2016 09:53:40 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_20_907434361.dbf
ORA-00280: change 13227500869 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13227501325 generated at 03/31/2016 09:59:08 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_21_907434361.dbf
ORA-00280: change 13227501325 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 13227501329 generated at 03/31/2016 09:59:09 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_22_907434361.dbf
ORA-00280: change 13227501329 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-00279: change 13227501333 generated at 03/31/2016 09:59:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_23_907434361.dbf
ORA-00280: change 13227501333 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 13227501337 generated at 03/31/2016 09:59:13 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501337 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'
--我覺得這個地方是執行恢復資料檔案7.因為前面提示是差seq=24.繼續恢復:
SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501337 generated at 03/31/2016 09:59:13 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501337 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 13227501343 2016-03-31 09:59:24 7 13227286650 ONLINE 1007 NO /mnt/ramdisk/book/system01.dbf SYSTEM
7 13227501343 2016-03-31 09:59:24 13227207527 13227286650 ONLINE 27 NO /mnt/ramdisk/book/tea01.dbf TEA
SYS@book> alter database datafile 7 offline ;
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 13227501343 2016-03-31 09:59:24 7 13227286650 ONLINE 1007 NO /mnt/ramdisk/book/system01.dbf SYSTEM
7 13227501343 2016-03-31 09:59:24 13227207527 13227286650 OFFLINE 27 NO /mnt/ramdisk/book/tea01.dbf TEA
-- 資料檔案7已經offline.
SYS@book> alter database read ;
alter database read
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
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 13227501347 2016-03-31 10:08:00 7 13227501344 ONLINE 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM
7 13227501343 2016-03-31 09:59:24 13227207527 13227286650 OFFLINE 27 NO /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恢復,只要歸檔日誌全部都在.
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 PARENT 13227285139 2016-03-25 16:45:42
4 4 BOOK 1337401710 PARENT 13227286650 2016-03-25 17:06:01
5 5 BOOK 1337401710 CURRENT 13227501344 2016-03-31 10:07:59
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
--沒有任何丟失.
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 13227501347 2016-03-31 10:08:00 7 13227501344 ONLINE 1009 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227501347 2016-03-31 10:08:00 1834 13227501344 ONLINE 1005 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227501347 2016-03-31 10:08:00 923328 13227501344 ONLINE 925 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227501347 2016-03-31 10:08:00 16143 13227501344 ONLINE 1009 YES /mnt/ramdisk/book/users01.dbf USERS
5 13227501347 2016-03-31 10:08:00 952916 13227501344 ONLINE 922 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227501347 2016-03-31 10:08:00 1314508 13227501344 ONLINE 938 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227501818 2016-03-31 10:12:53 13227207527 13227501344 ONLINE 29 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
--注意一個細節,我的recover database using backup controlfile until cancel;資料檔案7已經恢復到CHECKPOINT_CHANGE#=13227501343,而open resetlogs的scn=13227501344,僅僅相差1.
--如果存在一定差距,能恢復嗎? 看一篇帖子.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2073141/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 20160331資料檔案offline與open resetlogs3S3
- 20160331資料檔案offline與open resetlogs2
- [20161019]資料檔案offline與open resetlog
- open resetlogs後資料恢復資料恢復
- oracle 資料檔案offlineOracle
- OFFLINE和DROP資料檔案的理解
- 使用RESETLOGS重建控制檔案恢復資料庫資料庫
- 表空間OFFLINE和資料檔案OFFLINE的區別
- 表空間offline,資料檔案offline 的區別(ZT)
- alter database open resetlogs;Database
- 利用offline datafile檔案方式遷移資料
- 資料檔案OFFLINE的3種情況
- 使用RESETLOGS重建控制檔案恢復資料庫（二）資料庫
- 表空間與資料檔案的offline和online操作
- 資料檔案、表空間offline用法及區別
- 資料檔案offline後unusable索引造成的問題索引
- 檔案與資料
- Data Guard 主端OFFLINE資料檔案和表空間
- Backup And Recovery User's Guide-RMAN資料修復概念-OPEN RESETLOGS操作GUIIDE
- [20160329]bbed修復offline的資料檔案.txt
- 重建控制檔案與 datafile offline,tablespace read only
- 重建控制檔案時resetlogs與noresetlogs的使用情況
- 資料檔案實驗操作datafile的create/offline/drop/rename等操作
- 轉載-表空間和資料檔案offline的影響分析
- online/offline 表空間和資料檔案需謹慎！
- 在open狀態下恢復丟失的資料檔案
- 【BBED】使用BBED修改資料檔案SCN，使該檔案從offline轉變為online
- Summary For Forcing The Database Open With `_ALLOW_RESETLOGS_CORRUPTION`Database
- 非歸檔模式下恢復利用offline drop命令誤刪除的資料檔案模式
- 臨時資料檔案 offline 對於匯入匯出的影響
- [20151028]理解資料檔案offline+drop.txt
- 在open狀態下恢復未備份的資料檔案
- sql 重建控制檔案resetlogs和noresetlogsSQL
- 資料檔案合併與拆分
- JAVA檔案與資料流（1）Java
- [20180202]備庫資料檔案offline.txt
- [20161012]資料檔案offline馬上執行recover
- 資料檔案offline後，再online時，提示需要介質恢復。