BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle Recovery 05 - How to use BBED to recovery miss-archivelog file?

2018-06-18

Oracle Recovery 05 - How to use BBED to recovery miss-archivelog file?

Use bbed to skip the miss archivelog file to recover database.

Database env

Linux: OEL7.3 x86-64

[oracle@databasevm001 ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[oracle@databasevm001 ~]$ uname -a
Linux databasevm001 4.1.12-61.1.28.el7uek.x86_64 #2 SMP Thu Feb 23 19:55:12 PST 2017 x86_64 x86_64 x86_64 GNU/Linux
[oracle@databasevm001 ~]$ 

SYS@orcl11g> select * from v$version;

Database: 11.2.0.4

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SYS@orcl11g> 

Prepare Test Scenario

Archive log mode and set path/size for db_recovery_file_dest.

SYS@orcl11g> archive log list
SYS@orcl11g> show parameter recovery

Create tablespace and insert data.

SYS@orcl11g> create tablespace arch_test datafile '/u01/app/oracle/oradata/orcl11g/arch_test01.dbf' size 100m;
SYS@orcl11g> create user test01 identified by oracle default tablespace arch_test;
SYS@orcl11g> grant dba to test01;
SYS@orcl11g> conn test01/oracle
TEST01@orcl11g> create table t1(id int, name varchar2(20));
TEST01@orcl11g> insert into t1 values(1,'aaaaaaaaa');
TEST01@orcl11g> insert into t1 values(2,'bbbbbbbbb');
TEST01@orcl11g> commit;

Backup the tablespace.

SYS@orcl11g> conn / as sysdba
SYS@orcl11g> col file_name for a60
SYS@orcl11g> select file_id,file_name from dba_data_files order by 1;
     9 /u01/app/oracle/oradata/orcl11g/arch_test01.dbf

[oracle@databasevm001 ~]$ rman target /
RMAN> backup datafile 9 format '/home/oracle/backup/tbs09_%U';

Switch log files by multi times.

SYS@orcl11g> select sequence#,status from v$archived_log order by 1 desc;
SYS@orcl11g> alter system switch logfile;
SYS@orcl11g> alter system switch logfile;
SYS@orcl11g> /
SYS@orcl11g> /
.....

[oracle@databasevm001 2018_06_18]$ pwd
/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2018_06_18
[oracle@databasevm001 2018_06_18]$ ls 
o1_mf_1_19_fljyfdkw_.arc  o1_mf_1_21_flk0w8g7_.arc  o1_mf_1_23_flk0wgsg_.arc  o1_mf_1_25_flk11fxl_.arc
o1_mf_1_20_flk0w5cb_.arc  o1_mf_1_22_flk0wdvw_.arc  o1_mf_1_24_flk11dpt_.arc  o1_mf_1_26_flk11m3h_.arc
[oracle@databasevm001 2018_06_18]$ 


SYS@orcl11g> select sequence#,status from v$archived_log order by 1 desc;
SEQUENCE# S
---------- -
		62 D
		29 A
		28 A
		27 A
		26 A
		25 A
......

Delete the No.27 and No.28 archivelog files.

[oracle@databasevm001 2018_06_18]$ pwd
/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2018_06_18
[oracle@databasevm001 2018_06_18]$ rm o1_mf_1_27_flk13ox4_.arc o1_mf_1_28_flk13ppf_.arc
[oracle@databasevm001 2018_06_18]$ 

Offline the tablespace:arch_test(No.9).

select FILE#, CREATION_CHANGE#,CHECKPOINT_CHANGE#,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#,STATUS from v$datafile order by 1;

	FILE# CREATION_CHANGE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
---------- ---------------- ------------------ --------------------- ------------ --------------- -------
		1                7            1648231                     0                            0 SYSTEM
		2             1834            1648231                     0                            0 ONLINE
		3          1411137            1648231                     0                            0 ONLINE
		4            16009            1648231               1509949                            0 ONLINE
		5          1097396            1648231                     0                            0 ONLINE
		6          1435242            1648231                     0                            0 ONLINE
		7          1466248            1648231                     0                            0 ONLINE
		8          1435242            1443325               1467922      1443325               0 ONLINE
		9          1647340            1648231                     0                            0 ONLINE


In the archivelog mode, the "stop scn" of the datafile header will be updated. the datafile's "stop scn" in controlfile is also updated, and the "offline scn" is the same. The "offline scn" = The "stop scn".

archivelog模式下,当数据文件offline时,其对应的数据文件头stop scn会更新,同时controlfile中该datafile的stop scn信息也会更新,此时也会更新offline scn,并且offline scn等于stop scn。

Restore datafile

SYS@orcl11g> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl11g> startup mount;

[oracle@databasevm001 ~]$ rman target /
RMAN> restore datafile 9;

channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl11g/arch_test01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/tbs09_0mt5qcoa_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/tbs09_0mt5qcoa_1_1 tag=TAG20180619T041402
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

SYS@orcl11g> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@orcl11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl11g/arch_test01.dbf'
SYS@orcl11g> 


SYS@orcl11g> recover datafile 9;

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2018_06_19/o1_mf_1_27_flk13ox4_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

#### The archivelog file No.27&No.28 are missed. The recovery should be from No.50.
SYS@orcl11g> select to_char(SEQUENCE#,'xxxxxxxxxxx') seq,to_char(FIRST_CHANGE#,'xxxxxxxxxxx') scn from v$archived_log where SEQUENCE#=29; 
SEQ          SCN
------------ ------------
		1d       192664   ######## -> 64261900
SYS@orcl11g> 

## The list files in BBED tools
SYS@orcl11g> select file#||chr(9)||name||chr(9)||bytes from v$datafile;

Use BBED to modify the datafile’s header

BBED> info
File#  Name                                                        Size(blks)
-----  ----                                                        ----------
	1  /u01/app/oracle/oradata/orcl11g/system01.dbf                     97280
	2  /u01/app/oracle/oradata/orcl11g/sysaux01.dbf                     79360
	3  /u01/app/oracle/oradata/orcl11g/undotbs01.dbf                    12800
	4  /u01/app/oracle/oradata/orcl11g/users01.dbf                     102400
	5  /u01/app/oracle/oradata/orcl11g/data01.dbf                       12800
	6  /u01/app/oracle/oradata/orcl11g/rec_tbs01.dbf                    12800
	7  /u01/app/oracle/oradata/orcl11g/rec_tbs_temp001.dbf              12800
	8  /home/oracle/backup_tts/rec_tbs01.dbf                            12800
	9  /u01/app/oracle/oradata/orcl11g/arch_test01.dbf                  12800

BBED> 
BBED> set file 9 block 1
		FILE#           9
		BLOCK#          1

BBED> 
BBED> map /v
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1                                     Dba:0x02400001
------------------------------------------------------------
Data File Header

struct kcvfh, 860 bytes                    @0       
	struct kcvfhbfh, 20 bytes               @0       
	struct kcvfhhdr, 76 bytes               @20      
	ub4 kcvfhrdb                            @96      
	struct kcvfhcrs, 8 bytes                @100     
	ub4 kcvfhcrt                            @108     
	ub4 kcvfhrlc                            @112     
	struct kcvfhrls, 8 bytes                @116     
	ub4 kcvfhbti                            @124     
	struct kcvfhbsc, 8 bytes                @128     
	ub2 kcvfhbth                            @136     
	ub2 kcvfhsta                            @138     
	struct kcvfhckp, 36 bytes               @484     ### ---->
	ub4 kcvfhcpc                            @140     
	ub4 kcvfhrts                            @144     

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484      ### ----> SCN
	ub4 kscnbas                           @484      0x00192638
	ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x3a5d3433
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
	struct kcvcprba, 12 bytes             @500     
		ub4 kcrbaseq                       @500      0x0000001b ### ----> SEQ
		ub4 kcrbabno                       @504      0x00000002
		ub2 kcrbabof                       @508      0x0000
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> 
BBED> dump /v offset 484 count 32
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1       Offsets:  484 to  515  Dba:0x02400001
-------------------------------------------------------
38261900 00001000 33345d3a 01000000 l 8&......34]:....
1b000000 02000000 00000000 02000000 l ................

<16 bytes per line>

BBED> 

## ->>>>64261900    Modify to this value for SCN 

BBED> dump /v offset 484 count 32
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1       Offsets:  484 to  515  Dba:0x02400001
-------------------------------------------------------
38261900 00001000 33345d3a 01000000 l 8&......34]:....
1b000000 02000000 00000000 02000000 l ................

<16 bytes per line>

BBED> 
BBED> modify /x 64 offset 484
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1                Offsets:  484 to  515           Dba:0x02400001
------------------------------------------------------------------------
64261900 00001000 33345d3a 01000000 1b000000 02000000 00000000 02000000 

<32 bytes per line>

BBED> modify /x 261900 offset 485
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1                Offsets:  485 to  516           Dba:0x02400001
------------------------------------------------------------------------
26190000 00100033 345d3a01 0000001b 00000002 00000000 00000002 00000000 

<32 bytes per line>

BBED> dump /v offset 484 count 32
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1       Offsets:  484 to  515  Dba:0x02400001
-------------------------------------------------------
64261900 00001000 33345d3a 01000000 l d&......34]:....
1b000000 02000000 00000000 02000000 l ................

<16 bytes per line>

BBED> sum apply
Check value for File 9, Block 1:
current = 0xc920, required = 0xc920

BBED> 

## Current SCN is the following value.

SYS@orcl11g> select to_number('1d','xxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('1D','XXXXXXXXXXXXXXXXX')
-----------------------------------
								29
SYS@orcl11g> 

## The value SCN in datafile is the following value.

SYS@orcl11g> select to_number('1b','xxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('1B','XXXXXXXXXXXXXXXXX')
-----------------------------------
								27
SYS@orcl11g> 

## Modifiy the SCN 27 to SCN 29.

BBED> dump /v offset 500
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1       Offsets:  500 to  531  Dba:0x02400001
-------------------------------------------------------
1b000000 02000000 00000000 02000000 l ................
00000000 00000000 00000000 00000000 l ................

<16 bytes per line>

BBED> modify /x 1d offset 500
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1                Offsets:  500 to  531           Dba:0x02400001
------------------------------------------------------------------------
1d000000 02000000 00000000 02000000 00000000 00000000 00000000 00000000 

<32 bytes per line>

BBED> dump /v offset 504
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1       Offsets:  504 to  535  Dba:0x02400001
-------------------------------------------------------
02000000 00000000 02000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................

<16 bytes per line>

BBED> modify /x 0100 offset 504
File: /u01/app/oracle/oradata/orcl11g/arch_test01.dbf (9)
Block: 1                Offsets:  504 to  535           Dba:0x02400001
------------------------------------------------------------------------
01000000 00000000 02000000 00000000 00000000 00000000 00000000 00000000 

<32 bytes per line>

BBED> sum apply
Check value for File 9, Block 1:
current = 0xc925, required = 0xc925

BBED> 

Recover the database again and online the datafile.

SYS@orcl11g> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SYS@orcl11g> alter database datafile 9 online;
Database altered.
SYS@orcl11g> 

The recovery will skip the No.27/No.28 archivelog file!

The datafile’s status

The status list of datafile is the following.

KCCFEFDB 0x0001 /* file read-only, plugged from foreign DB */
KCCFEONL 0x0002 /* file is ONLine */
KCCFERDE 0x0004 /* ReaDing is Enabled */
KCCFECGE 0x0008 /* ChanGing is Enabled */
KCCFEMRR 0x0010 /* Media Recovery Required */
KCCFEGEM 0x0020 /* Generate End hot backup Marker at next open */
KCCFECKD 0x0040 /* File record generated by check dictionary */
KCCFESOR 0x0080 /* Save Offline scn Range at next checkpoint */
KCCFERMF 0x0100 /* Renamed Missing File */
KCCFEGOI 0x0200 /* Generate Off-line Immediate marker */
KCCFECUV 0x0400 /* Checkpoint by instance where UnVerified */
KCCFEDRP 0x0800 /* offline to be DRoPped */
KCCFEODC 0x2000 /* Online at Dictionary Check if read/only tblspc */
KCCFEDBR 0x4000 /* entry created by DBMS_BACKUP_RESTORE */
KCCFETRO 0x8000 /* Transition Read Only */define KCCFEWCC 0x1000 /*

Dump the datafile header.

SYS@orcl11g> alter session set events 'immediate trace name file_hdrs level 10';

-####level <n>
level 1: control file's data file entry
level 2 & 4 : level 1 + generic file header
level 3 or higher: level 2 + data file header
level 10: Most commonly used, It provides the same output as level 3.

SYS@orcl11g> select * from v$diag_info where NAME='Default Trace File'; 

File Type:
KCCTYPCF 1 /* control file */
KCCTYPRL 2 /* redo log file */
KCCTYPDF 3 /* vanilla db file */
KCCTYPBC 4 /* backup control file */
KCCTYPBP 5 /* backup piece */
KCCTYPTF 6 /* temporary db file */
KCCTYPCT 7 /* change tracking file */
KCCTYPFL 8 /* flashback database log file */
KCCTYPAL 9 /* archivelog file */
KCCTYPDC 10 /* datafile copy file */
KCCTYPIR 11 /* incompletely restored db file */
KCCTYPEL 12 /* foreign archivelog file */
KCCTYPLB 13 /* LOB */

To be continue….

Have a good life! 2018/06 via LinHong


Similar Posts

Comments