測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復
測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復。
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
- DG歸檔日誌缺失恢復
- 【BBED】丟失歸檔檔案情況下的恢復
- 對歸檔模式下CLEAR 未歸檔日誌後恢復資料庫的一點看法模式資料庫
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 測試，ogg從歸檔日誌中抽取資料
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- PostgreSQL 歸檔日誌SQL
- 歸檔日誌挖掘
- oracle歸檔日誌Oracle
- Oracle歸檔日誌清理Oracle
- 歸檔oracle alert日誌Oracle
- 14. 日誌歸檔
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- oracle 刪除過期的歸檔日誌Oracle
- 批量註冊歸檔日誌
- oracle資料庫歸檔日誌量陡增分析Oracle資料庫
- Rman在run命令塊裡臨時設定歸檔目錄，歸檔日誌能否恢復主要看預設的歸檔路徑
- ArgoWorkflow教程(四)---Workflow & 日誌歸檔Go
- logminer異機挖掘歸檔日誌
- 通過DataWorks資料整合歸檔日誌服務資料至MaxCompute進行離線分析
- RMAN-ERROR：因為找不到過期和丟失的歸檔日誌而備份失敗Error
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- oracle11G歸檔日誌管理Oracle
- rman 還原歸檔日誌(restore archivelogRESTHive
- PostgreSQL的xlog/Wal歸檔及日誌清理SQL
- 在Oracle中，如何定時刪除歸檔日誌檔案？Oracle
- Sqlserver系統資料庫和使用者資料庫日誌檔案全部丟失的恢復SQLServer資料庫
- oracle rman備份歸檔日誌需要先切換日誌嗎Oracle
- dg丟失歸檔，使用rman增量備份恢復
- Oracle歸檔日誌暴增排查優化Oracle優化
- 【SQL】Oracle 歸檔日誌暴增原因分析SQLOracle
- Oracle設定多個歸檔路徑生成多份歸檔日誌，Rman備份時也只備份其中的一份歸檔日誌Oracle
- 通過RMAN設定standby接收日誌後主庫歸檔日誌才可刪除
- 達夢資料庫DM8之刪除歸檔日誌檔案資料庫
- 如何快速找到備份過最近、最大序號的歸檔日誌