測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復
測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復。
1 構建測試資料檔案。
SQL> create tablespace arct datafile '/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf' size 50m;
Tablespace created.
SQL> create user arct identified by oracle default tablespace arct;
User created.
SQL> grant dba to arct
2 ;
Grant succeeded.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/bbedt/backup
Oldest online log sequence 62
Next log sequence to archive 64
Current log sequence 64
SQL>
SQL> create table t1 (id number ,name varchar2(10));
Table created.
SQL> insert into t1 values(1,'ey');
1 row created.
SQL> commit;
Commit complete.
檢視資料檔案編號
1 /home/oracle/bbedt/oradata/YBBEDT/system01.dbf
2 /home/oracle/bbedt/oradata/YBBEDT/sysaux01.dbf
3 /home/oracle/bbedt/oradata/YBBEDT/undotbs01.dbf
4 /home/oracle/bbedt/oradata/YBBEDT/users01.dbf
5 /home/oracle/bbedt/oradata/YBBEDT/example01.dbf
6 /home/oracle/bbedt/oradata/YBBEDT/tbs16k001.dbf
7 /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
7 rows selected.
Rman備份資料檔案
[oracle@irac01 bbedt]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 22 16:30:05 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YBBEDT (DBID=2362208854)
RMAN> backup datafile 7;
Starting backup at 2018/03/22 16:30:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
channel ORA_DISK_1: starting piece 1 at 2018/03/22 16:30:19
channel ORA_DISK_1: finished piece 1 at 2018/03/22 16:30:20
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/06suee4r_1_1 tag=TAG20180322T163018 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018/03/22 16:30:20
RMAN>
檢視歸檔資訊：
-rw-r----- 1 oracle asmadmin 39093760 Mar 20 01:00 1_60_969623729.dbf
-rw-r----- 1 oracle asmadmin 41044480 Mar 20 22:24 1_61_969623729.dbf
-rw-r----- 1 oracle asmadmin 37861376 Mar 21 11:05 1_62_969623729.dbf
-rw-r----- 1 oracle asmadmin 41761792 Mar 21 22:25 1_63_969623729.dbf
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/bbedt/backup
Oldest online log sequence 62
Next log sequence to archive 64
Current log sequence 64
切換日誌：
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL> /
System altered.
SQL>
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/bbedt/backup
Oldest online log sequence 68
Next log sequence to archive 70
Current log sequence 70
SQL>
目前歸檔日誌：
-rw-r----- 1 oracle asmadmin 41761792 Mar 21 22:25 1_63_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_65_969623729.dbf
-rw-r----- 1 oracle asmadmin 2560 Mar 22 16:32 1_66_969623729.dbf
-rw-r----- 1 oracle asmadmin 30178816 Mar 22 16:32 1_64_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_67_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_68_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_69_969623729.dbf
SEQUENCE#
----------
60
61
62
63
65
66
64
67
68
69
43 rows selected.
2 破壞，刪除歸檔日誌：
[oracle@irac01 backup]$ rm -rf 1_64_969623729.dbf
[oracle@irac01 backup]$ rm -rf 1_65_969623729.dbf
[oracle@irac01 backup]$ rm -rf 1_66_969623729.dbf
[oracle@irac01 backup]$
將資料檔案離線：
SQL> alter database datafile 7 offline;
Database altered.
SQL>
[oracle@irac01 backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 22 16:44:56 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YBBEDT (DBID=2362208854)
RMAN> restore datafile 7;
Starting restore at 2018/03/22 16:45:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/db_1/dbs/06suee4r_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/06suee4r_1_1 tag=TAG20180322T163018
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2018/03/22 16:45:08
3 模擬報錯現象，Online資料檔案時出現報錯：
SQL>
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf'
SQL> recover datafile 7;
ORA-00279: change 23812090 generated at 03/22/2018 16:30:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/bbedt/backup/1_64_969623729.dbf
ORA-00280: change 23812090 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/oracle/bbedt/backup/1_64_969623729.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
4 模擬恢復過程，使用BBED修改資料檔案頭跳過丟失的歸檔日誌進行恢復：
本次實驗跳過被刪除的歸檔日誌，直接透過更改資料檔案塊頭，使資料庫從低67號資料檔案開始恢復。
SQL> select to_char(sequence#,'xxxxxxxxxxxxxx'),to_char(first_change#,'xxxxxxxxxxxxxx') from v$archived_log where sequence#=67;
TO_CHAR(SEQUENC TO_CHAR(FIRST_C
--------------- ---------------
43 16b5830
SQL>
BBED> set filename '/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf';
FILENAME /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
BBED> set block 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x016b57fa
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x39e7389b
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000040
ub4 kcrbabno @504 0x0000e627
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> set count 32
COUNT 32
BBED> dump
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 484 to 515 Dba:0x00000000
------------------------------------------------------------------------
fa576b01 00000000 9b38e739 01000000 40000000 27e60000 10006c65 02000000
<32 bytes="" per="" line="">
a）修改檢查點時間，修改一號塊偏移量為484的位置：
BBED> modify /x 30586b01
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 484 to 515 Dba:0x00000000
------------------------------------------------------------------------
30586b01 00000000 9b38e739 01000000 40000000 27e60000 10006c65 02000000
<32 bytes="" per="" line="">
BBED> sum apply
Check value for File 0, Block 1:
current = 0x8c20, required = 0x8c20
BBED>
BBED> set offset 500
OFFSET 500
BBED> dump
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 500 to 531 Dba:0x00000000
------------------------------------------------------------------------
40000000 27e60000 10006c65 02000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
b）修改序列號
BBED> modify /x 43
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 500 to 531 Dba:0x00000000
------------------------------------------------------------------------
43000000 27e60000 10006c65 02000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> sum apply
Check value for File 0, Block 1:
current = 0x8c23, required = 0x8c23
完成恢復
SQL> recover datafile 7
ORA-00279: change 23812144 generated at 03/22/2018 16:30:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/bbedt/backup/1_67_969623729.dbf
ORA-00280: change 23812144 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
1 構建測試資料檔案。
SQL> create tablespace arct datafile '/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf' size 50m;
Tablespace created.
SQL> create user arct identified by oracle default tablespace arct;
User created.
SQL> grant dba to arct
2 ;
Grant succeeded.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/bbedt/backup
Oldest online log sequence 62
Next log sequence to archive 64
Current log sequence 64
SQL>
SQL> create table t1 (id number ,name varchar2(10));
Table created.
SQL> insert into t1 values(1,'ey');
1 row created.
SQL> commit;
Commit complete.
檢視資料檔案編號
1 /home/oracle/bbedt/oradata/YBBEDT/system01.dbf
2 /home/oracle/bbedt/oradata/YBBEDT/sysaux01.dbf
3 /home/oracle/bbedt/oradata/YBBEDT/undotbs01.dbf
4 /home/oracle/bbedt/oradata/YBBEDT/users01.dbf
5 /home/oracle/bbedt/oradata/YBBEDT/example01.dbf
6 /home/oracle/bbedt/oradata/YBBEDT/tbs16k001.dbf
7 /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
7 rows selected.
Rman備份資料檔案
[oracle@irac01 bbedt]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 22 16:30:05 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YBBEDT (DBID=2362208854)
RMAN> backup datafile 7;
Starting backup at 2018/03/22 16:30:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
channel ORA_DISK_1: starting piece 1 at 2018/03/22 16:30:19
channel ORA_DISK_1: finished piece 1 at 2018/03/22 16:30:20
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/06suee4r_1_1 tag=TAG20180322T163018 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018/03/22 16:30:20
RMAN>
檢視歸檔資訊：
-rw-r----- 1 oracle asmadmin 39093760 Mar 20 01:00 1_60_969623729.dbf
-rw-r----- 1 oracle asmadmin 41044480 Mar 20 22:24 1_61_969623729.dbf
-rw-r----- 1 oracle asmadmin 37861376 Mar 21 11:05 1_62_969623729.dbf
-rw-r----- 1 oracle asmadmin 41761792 Mar 21 22:25 1_63_969623729.dbf
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/bbedt/backup
Oldest online log sequence 62
Next log sequence to archive 64
Current log sequence 64
切換日誌：
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL> /
System altered.
SQL>
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/bbedt/backup
Oldest online log sequence 68
Next log sequence to archive 70
Current log sequence 70
SQL>
目前歸檔日誌：
-rw-r----- 1 oracle asmadmin 41761792 Mar 21 22:25 1_63_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_65_969623729.dbf
-rw-r----- 1 oracle asmadmin 2560 Mar 22 16:32 1_66_969623729.dbf
-rw-r----- 1 oracle asmadmin 30178816 Mar 22 16:32 1_64_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_67_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_68_969623729.dbf
-rw-r----- 1 oracle asmadmin 1024 Mar 22 16:32 1_69_969623729.dbf
SEQUENCE#
----------
60
61
62
63
65
66
64
67
68
69
43 rows selected.
2 破壞，刪除歸檔日誌：
[oracle@irac01 backup]$ rm -rf 1_64_969623729.dbf
[oracle@irac01 backup]$ rm -rf 1_65_969623729.dbf
[oracle@irac01 backup]$ rm -rf 1_66_969623729.dbf
[oracle@irac01 backup]$
將資料檔案離線：
SQL> alter database datafile 7 offline;
Database altered.
SQL>
[oracle@irac01 backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 22 16:44:56 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YBBEDT (DBID=2362208854)
RMAN> restore datafile 7;
Starting restore at 2018/03/22 16:45:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/db_1/dbs/06suee4r_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/06suee4r_1_1 tag=TAG20180322T163018
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2018/03/22 16:45:08
3 模擬報錯現象，Online資料檔案時出現報錯：
SQL>
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf'
SQL> recover datafile 7;
ORA-00279: change 23812090 generated at 03/22/2018 16:30:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/bbedt/backup/1_64_969623729.dbf
ORA-00280: change 23812090 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/oracle/bbedt/backup/1_64_969623729.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
4 模擬恢復過程，使用BBED修改資料檔案頭跳過丟失的歸檔日誌進行恢復：
本次實驗跳過被刪除的歸檔日誌，直接透過更改資料檔案塊頭，使資料庫從低67號資料檔案開始恢復。
SQL> select to_char(sequence#,'xxxxxxxxxxxxxx'),to_char(first_change#,'xxxxxxxxxxxxxx') from v$archived_log where sequence#=67;
TO_CHAR(SEQUENC TO_CHAR(FIRST_C
--------------- ---------------
43 16b5830
SQL>
BBED> set filename '/home/oracle/bbedt/oradata/YBBEDT/arct01.dbf';
FILENAME /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf
BBED> set block 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x016b57fa
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x39e7389b
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000040
ub4 kcrbabno @504 0x0000e627
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> set count 32
COUNT 32
BBED> dump
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 484 to 515 Dba:0x00000000
------------------------------------------------------------------------
fa576b01 00000000 9b38e739 01000000 40000000 27e60000 10006c65 02000000
<32 bytes="" per="" line="">
a）修改檢查點時間，修改一號塊偏移量為484的位置：
BBED> modify /x 30586b01
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 484 to 515 Dba:0x00000000
------------------------------------------------------------------------
30586b01 00000000 9b38e739 01000000 40000000 27e60000 10006c65 02000000
<32 bytes="" per="" line="">
BBED> sum apply
Check value for File 0, Block 1:
current = 0x8c20, required = 0x8c20
BBED>
BBED> set offset 500
OFFSET 500
BBED> dump
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 500 to 531 Dba:0x00000000
------------------------------------------------------------------------
40000000 27e60000 10006c65 02000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
b）修改序列號
BBED> modify /x 43
File: /home/oracle/bbedt/oradata/YBBEDT/arct01.dbf (0)
Block: 1 Offsets: 500 to 531 Dba:0x00000000
------------------------------------------------------------------------
43000000 27e60000 10006c65 02000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> sum apply
Check value for File 0, Block 1:
current = 0x8c23, required = 0x8c23
完成恢復
SQL> recover datafile 7
ORA-00279: change 23812144 generated at 03/22/2018 16:30:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/bbedt/backup/1_67_969623729.dbf
ORA-00280: change 23812144 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29135257/viewspace-2152134/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 在歸檔模式下丟失日誌檔案的恢復模式
- 丟失已歸檔日誌檔案下恢復資料庫資料庫
- 非歸檔丟失日誌檔案的恢復
- oracle dg 歸檔日誌恢復情況Oracle
- oracle歸檔日誌丟失後的資料庫恢復Oracle資料庫
- Oracle控制檔案在缺失歸檔日誌的情況下的恢復Oracle
- 沒備份，歸檔日誌存在，丟失資料檔案的恢復
- 跳過歸檔日誌的非常規恢復（一）
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 非歸檔模式下，丟失日誌檔案的一次恢復過程模式
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- 無歸檔日誌恢復rman資料
- Oracle主庫歸檔丟失，備庫日誌有gap，在不重建備庫的情況下，恢復備庫Oracle
- DG歸檔日誌缺失恢復
- 【BBED】丟失歸檔檔案情況下的恢復
- sqlplus 下恢復active 日誌丟失的情況SQL
- 恢復歸檔日誌檔案的常用方法
- 歸檔模式下的日誌檔案丟失的解決方法模式
- 【恢復】Redo日誌檔案丟失的恢復
- 非歸檔下日誌檔案丟失的處理辦法
- 冷備份+歸檔日誌的恢復
- Oracle RMAN 不完全恢復（只有資料檔案備份，丟失歸檔日誌備份）Oracle
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 對歸檔模式下CLEAR 未歸檔日誌後恢復資料庫的一點看法模式資料庫
- 歸檔模式下資料檔案丟失的恢復模式
- 誤刪歸檔日誌除導致備份歸檔日誌失敗
- 【DATAGUARD】物理dg在主庫丟失歸檔檔案的情況下的恢復(七)
- Oracle叢集資料庫中恢復歸檔日誌Oracle資料庫
- 歸檔日誌
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 非歸檔模式下非當前日誌檔案的丟失的恢復模式
- oracle丟失日誌檔案的恢復( 轉)Oracle
- REDO日誌損壞,非歸檔模式資料檔案恢復模式
- [Logmnr]對歸檔日誌進行資料探勘
- 從備份集恢復歸檔日誌的測試與說明
- 丟失當前current重做日誌檔案下恢復資料庫資料庫
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- Oracle歸檔日誌比聯機重做日誌小很多的情況總結Oracle