oracle修復探索ORACLE不完全恢復之--基于cancel的恢復 第二篇
瀏覽量： 次 發(fā)布日期：2023-08-11 21:27:18
基于cancel 的不一致性恢復（歸檔丟失） 第二篇
?? 主要適用于：基于Cancel的不完全恢復適用場景：Recover時，所需的某個歸檔日志損壞，或主機斷電，current狀態(tài)的聯(lián)機日志損壞。
?
創(chuàng)建測試表
??? 創(chuàng)建wwl002表，切換日志，再創(chuàng)建新的wwl003表，主機斷電，刪除當前日志，模擬文件損壞。
SQL> conn wwl/wwl
Connected.
?
SQL> select * from tab;
TNAME????????????????????????? TABTYPE? CLUSTERID
------------------------------ -----------------
WWL001???????????????????????? TABLE
?
SQL> create table WWL002 as select *from wwl001;
Table created.
?
SQL> conn / as sysdba
Connected.
?
切換日志
SQL> alter system switch logfile;
System altered.
?
后再創(chuàng)建第二張表
SQL> conn wwl/wwl
Connected.
?
SQL> create table wwl003 as select *from wwl001;
Table created.
?
查看當前日志組，確定當前活動的日志組，是組4
SQL> conn / as sysdba
Connected.
SQL> set line 200
SQL> select * from v$log;
?
???GROUP#??? THREAD#? SEQUENCE#?????BYTES??? MEMBERS ARC STATUS?????????? FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- -------------------- --- ---------------- ------------- ---------
??? ?????4????????? 1????????? 2?134217728????????? 2 YES ACTIVE???????????????? 1716929 30-JUL-12
????????5????????? 1????????? 1?134217728????????? 2 YES INACTIVE?????????????? 1692728 27-JUL-12
????????6????????? 1????????? 3?134217728????????? 2 NO? CURRENT??????????????? 1720396 30-JUL-12
????????7????????? 1????????? 0?134217728????????? 2 YES UNUSED?????????????????????? 0
?
定位當前日志組的日志文件，有兩個。
?SQL> col member format a30
SQL> select * from v$logfile;
?
???GROUP# STATUS? TYPE??? MEMBER???????????????????????? IS_
---------- ------- ------------------------------------- ---
????????7???????? ONLINE? /DBBak2/oradata/WWL/redo7a.log NO
????????7???????? ONLINE? /DBBak2/oradata/WWL/redo7b.log NO
????????6???????? ONLINE? /DBBak2/oradata/WWL/redo6a.log NO
????????6???????? ONLINE? /DBBak2/oradata/WWL/redo6b.log NO
????????5 STALE?? ONLINE? /DBBak2/oradata/WWL/redo5a.log NO
????????5 STALE?? ONLINE? /DBBak2/oradata/WWL/redo5b.log NO
???????? 4???????? ONLINE?/DBBak2/oradata/WWL/redo4a.log NO
???????? 4???????? ONLINE?/DBBak2/oradata/WWL/redo4b.log NO
?
8 rows selected.
?
刪除當前日志組文件，模擬在線事務丟失：
SQL> !rm -f/DBBak2/oradata/WWL/redo4a.log
SQL> !rm -f/DBBak2/oradata/WWL/redo4b.log
?
模擬服務器斷電
SQL> shutdown abort;
ORACLE instance shut down.
?
恢復步驟:
1、嘗試啟動數(shù)據(jù)庫的時候報當前日志丟失。
SQL> startup
ORACLE instance started.
?
Total System Global Area? 100663296 bytes
Fixed Size????????????????? 1217884 bytes
Variable Size????????????? 88083108 bytes
Database Buffers???????? ???8388608 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
ORA-00313: open failedfor members of log group 4 of thread 1
ORA-00312: online log 4thread 1: '/DBBak2/oradata/WWL/redo4b.log'
ORA-27037: unable toobtain file status
Linux Error: 2: No suchfile or directory
Additional information: 3
ORA-00312: online log 4thread 1: '/DBBak2/oradata/WWL/redo4a.log'
ORA-27037: unable toobtain file status
Linux Error: 2: No suchfile or directory
Additional information: 3
?
2、嘗試Clear redo4
SQL> alter database clear logfile group4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recoveryof instance WWL (thread 1)
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4a.log'
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4b.log'
?
3、在當前庫做基于Cancel的不完全恢復
SQL> recover database until cancel;
ORA-00279: change 1716930 generated at07/30/2012 11:03:51 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf
ORA-00280: change 1716930 for thread 1 isin sequence #2
?
?
Specify log: {
auto
ORA-00279: change 1720396 generated at07/30/2012 13:37:21 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf
ORA-00280: change 1720396 for thread 1 isin sequence #3
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf' no longer needed forthis recovery
?
?
ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
?
?
ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
?
做完恢復之后必須使用resetlogs選項打開數(shù)據(jù)庫：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
?
? ??恢復之后，使用Resetlogs選項，仍無法打開數(shù)據(jù)庫，提示數(shù)據(jù)文件不一致，System表空間需要進一步的恢復。
??? 當前日志損壞時，不能基于當前的數(shù)據(jù)庫做不完全恢復。只能用以前的備份，做一個基于Cancel的不完全恢復。
?
重建下控制文件：
SQL> CREATE CONTROLFILE REUSE DATABASE"WWL" RESETLOGS? ARCHIVELOG
???MAXLOGFILES 16
???MAXLOGMEMBERS 3
???'/DBBak2/oradata/WWL/redo4a.log',
???MAXDATAFILES 100
???MAXINSTANCES 8
?GROUP 5 (
???MAXLOGHISTORY 292
LOGFILE
?GROUP 4 (
???'/DBBak2/oradata/WWL/redo4a.log',
???'/DBBak2/oradata/WWL/redo4b.log'
?GROUP 6 (
? )SIZE 128M,
?GROUP 5 (
???'/DBBak2/oradata/WWL/redo5a.log',
???'/DBBak2/oradata/WWL/redo5b.log'
???'/DBBak2/oradata/WWL/redo7b.log'
? )SIZE 128M,
?GROUP 6 (
???'/DBBak2/oradata/WWL/redo6a.log',
???'/DBBak2/oradata/WWL/redo6b.log'
? )SIZE 128M,
?GROUP 7 (
???'/DBBak2/oradata/WWL/redo7a.log',
???'/DBBak2/oradata/WWL/redo7b.log'
? )SIZE 128M
-- STANDBY LOGFILE
DATAFILE
?'/DBBak2/oradata/WWL/system01.dbf',
?'/DBBak2/oradata/WWL/undotbs01.dbf',
?'/DBBak2/oradata/WWL/sysaux01.dbf',
?'/DBBak2/oradata/WWL/users01.dbf',
?'/DBBak2/oradata/WWL/wwl001',
?'/DBBak2/oradata/WWL/wwl002',
?'/DBBak2/oradata/WWL/wwl003'
CHARACTER SET ZHS16CGB231280
?34? ;
?
Control file created.
?
再次打開，結果還是不行
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
?
可以嘗試使用_allow_resetlogs_corruption隱含參數(shù)來打開數(shù)據(jù)庫
SQL> alter system set"_allow_resetlogs_corruption"=true scope=spfile;
System altered.
?
修改完參數(shù)之后重啟數(shù)據(jù)庫到mount狀態(tài)
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
?
SQL> startup;
ORACLE instance started.
?
Total System Global Area? 100663296 bytes
Fixed Size??????????????? ??1217884 bytes
Variable Size????????????? 88083108 bytes
Database Buffers??????????? 8388608 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
?
同樣以resetlogs模式啟動數(shù)據(jù)庫
SQL> alter database open resetlogs;
Database altered.
?
一定記得關閉該參數(shù)
SQL> alter system set"_allow_resetlogs_corruption"=FALSE scope=spfile;
System altered.
?
讓參數(shù)關閉生效，再次啟動數(shù)據(jù)庫
SQL> startup force;
ORACLE instance started.
?
Total System Global Area? 100663296 bytes
Fixed Size????????????????? 1217884 bytes
Variable Size????????????? 88083108 bytes
Database Buffers??????????? 8388608 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
Database opened.
兆柏數(shù)據(jù)恢復公司?
檢查_allow_resetlogs_corruption隱含參數(shù)是否關閉，確定已經(jīng)關閉
SQL> show parameter_allow_resetlogs_corruption
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ------------------------------
_allow_resetlogs_corruption????????? boolean???? FALSE
SQL>
?
因為重建了控制文件，默認是沒有制定temp表空間，這里制定下：
SQL> ALTER TABLESPACE TEMP01 ADDTEMPFILE '/DBBak2/oradata/WWL/temp01.dbf' REUSE;
Tablespace altered.
?
檢查數(shù)據(jù)
SQL> conn wwl/wwl
Connected.
?
?
兆柏數(shù)據(jù)恢復公司SQL> select * from tab;
TNAME????????????????????????? TABTYPE? CLUSTERID
-----------------------oracle修復------- -----------------
WWL001???????????????????????? TABLE
WWL002???????????????????????? TABLE
?
可以看到表WWL002存在，表WWL003不存在。因為WWL003的創(chuàng)建，是保存在當前REDO日志中的，而當前REDO日志損壞，所有當前日志中保存的操作全部丟失了。
??? 歸檔日志、或者REDO日志損失，數(shù)據(jù)庫就只能恢復到丟失的日志之前了。
oracle修復
重要數(shù)據(jù)丟失請聯(lián)系兆柏數(shù)據(jù)恢復公司
. oracle證書,開啟數(shù)據(jù)庫專業(yè)之旅的鑰匙
. 固態(tài)硬盤數(shù)據(jù)恢復一般多少錢,固態(tài)硬盤修復手把手教你救治不認盤的固態(tài)
. oracle歸檔日志設置,Oracle數(shù)據(jù)庫歸檔日志設置詳解
. 數(shù)據(jù)庫修復,數(shù)據(jù)庫修復的重要性
. oracle ｄｒｏｐ表數(shù)據(jù)恢復,Oracle數(shù)據(jù)庫中ｄｒｏｐ表數(shù)據(jù)恢復方法詳解
. oracle 刪除數(shù)據(jù)文件,Oracle 數(shù)據(jù)文件刪除指南
. win11家庭版裝Oracle,oracle官方網(wǎng)站
. 什么是oracle數(shù)據(jù)庫,什么是Oracle數(shù)據(jù)庫？
. 如何查看oracle錯誤日志,Oracle數(shù)據(jù)庫錯誤日志查看指南
. oracle數(shù)據(jù)庫執(zhí)行計劃怎么看,查看與分析技巧
. oracle刪除的數(shù)據(jù)怎么恢復,Oracle數(shù)據(jù)庫中刪除數(shù)據(jù)恢復指南
. oracle一個實例多個數(shù)據(jù)庫,Oracle數(shù)據(jù)庫實例與多個數(shù)據(jù)庫的配置與管理
. ora-01578:oracle 數(shù)據(jù)塊損壞,oracle數(shù)據(jù)塊損壞
. mysql數(shù)據(jù)庫修復,從預防到實戰(zhàn)
. oracle恢復歸檔日志文件,Oracle數(shù)據(jù)庫恢復歸檔日志文件的方法與技巧
QQ客服