【Oracle】使用BBED跳過丢失的歸檔

在recover datafile的過程當中如果丢失了需要的歸檔将使得recover無法進行，使用bbed工具可以跳過丢失的歸檔進行recover datafile。

實驗過程如下：

[email protected]>select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

[email protected]>create tablespace bbed_test_tbs

2 datafile '/u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf' size 20M;

Tablespace created.

[email protected]>create table bbed_test1 tablespace bbed_test_tbs as select * from dba_objects;

Table created.

[email protected]>create table bbed_test2 tablespace bbed_test_tbs as select * from dba_objects;

Table created.

[email protected]>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

[email protected]>select file#||' '||name||' '||bytes from v$datafile;

FILE#||''||NAME||''||BYTES

--------------------------------------------------------------------------------

1 /u01/app/oracle/oradata/ORCL/system01.dbf 503316480

2 /u01/app/oracle/oradata/ORCL/undotbs01.dbf 36700160

3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf 262144000

4 /u01/app/oracle/oradata/ORCL/users01.dbf 5242880

5 /u01/app/oracle/oradata/ORCL/example01.dbf 104857600

6 /u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf 20971520

6 rows selected.

使用rman備份datafile 6

[[email protected] bbed]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 19 21:33:16 2014

connected to target database: ORCL (DBID=1356549586)

RMAN> backup datafile 6;

Starting backup at 19-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf

channel ORA_DISK_1: starting piece 1 at 19-JUN-14

channel ORA_DISK_1: finished piece 1 at 19-JUN-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_06_19/o1_mf_nnndf_TAG20140619T213426_9t73x2s8_.bkp tag=TAG20140619T213426 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 19-JUN-14

然後在庫裡修改datafile 6中存儲的資料

[email protected]>delete from bbed_test1;

50316 rows deleted.

[email protected]>commit;

Commit complete.

[email protected]>alter system switch logfile;

System altered.

[email protected]>alter system switch logfile;

System altered.

[email protected]>alter system switch logfile;

System altered.

然後關閉資料庫删除datafile 6

[email protected]>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

[[email protected] ORCL]$ ls

bbed_test_tbs01.dbf control03.ctl redo02.log system01.dbf users01.dbf

control01.ctl example01.dbf redo03.log temp01.dbf

control02.ctl redo01.log sysaux01.dbf undotbs01.dbf

[[email protected] ORCL]$ mv bbed_test_tbs01.dbf bbed_test_tbs01.dbf.bak

啟動資料庫：

[email protected]>startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 83887696 bytes

Database Buffers 197132288 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf'

資料庫無法啟動因為此時datafile 6丢失，使用rman的備份恢複資料檔案，嘗試打開資料庫

RMAN> restore datafile 6;

Starting restore at 19-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_06_19/o1_mf_nnndf_TAG20140619T213426_9t73x2s8_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_06_19/o1_mf_nnndf_TAG20140619T213426_9t73x2s8_.bkp tag=TAG20140619T213426

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 19-JUN-14

[email protected]>alter database open;

alter database open

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf'

此時報datafile 6需要進行recover。

這時我們删除歸檔，然後嘗試recover datafile 6

[[email protected] archivelog]$ cd 2014_06_19/

[[email protected] 2014_06_19]$ ls

o1_mf_1_3_9t73hdco_.arc o1_mf_1_5_9t740dxd_.arc

o1_mf_1_4_9t74035o_.arc o1_mf_1_6_9t740sv7_.arc

[[email protected] 2014_06_19]$ rm -f *

[[email protected] 2014_06_19]$ ls

[email protected]>recover datafile 6;

ORA-00279: change 507768 generated at 06/19/2014 21:34:26 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_06_19/o1_mf_1_4_%u_.arc

ORA-00280: change 507768 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_06_19/o1_mf_1_4_9t7403

5o_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

無法恢複，提示需要的歸檔檔案不存在

Dump出檔案頭：

[email protected]>alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

[email protected]>[email protected]>oradebug setmypid;

Statement processed.

[email protected]>oradebug tracefile_name

/u01/app/oracle/admin/ORCL/udump/orcl_ora_9065.trc

檢視dump檔案：

DATA FILE #1:

(name #7) /u01/app/oracle/oradata/ORCL/system01.dbf

creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1

tablespace 0, index=1 krfil=1 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:56 scn: 0x0000.0007c39c 06/19/2014 21:37:19

Stop scn: 0x0000.0007c39c 06/19/2014 21:37:19

Creation Checkpointed at scn: 0x0000.00000009 06/30/2005 19:10:11

thread:0 rba:(0x0.0.0)

這時system資料檔案的，然後我們使用bbed将datafile 6的scn和system資料檔案的scn改為一緻。

BBED> set dba 6,1

DBA 0x01800001 (25165825 6,1)

BBED> map

File: /u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf (6)

Block: 1 Dba:0x01800001

------------------------------------------------------------

Data File Header

struct kcvfh, 676 bytes @0

ub4 tailchk @8188

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x0007bf78

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x32b46ee2

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x00000004

ub4 kcrbabno @504 0x0000a54a

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> m /v 9cc3 offset 484

BBED-00201: invalid switch (/v)

BBED> m /x 9cc3 offset 484

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

File: /u01/app/oracle/oradata/ORCL/bbed_test_tbs01.dbf (6)

Block: 1 Offsets: 484 to 995 Dba:0x01800001

------------------------------------------------------------------------

9cc30700 0000e7b7 e26eb432 0100f50d 04000000 4aa50000 10000000 02000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

0a000a00 0a000100 00000000 00000000 00000000 02008001 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 6, Block 1:

current = 0x862e, required = 0x862e

然後回到資料庫recover datafile 6,嘗試打開資料庫。

[email protected]>recover datafile 6;

Media recovery complete.

[email protected]>alter database open;

Database altered.

【Oracle】使用BBED跳過丢失的歸檔

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List