By Chen
In this scenario, instance crashes after one tablespace (non-system) be taken hot backup, datafile within this tablespace and all archived logs are lost. I change the backup file header data directly with BBED and put the file online skip recovery through the missing archived logs.
This approach may cause data not inconsistent and lost. But it’s maybe significant if there’re no other methods to extract data from the backup files or no data changed since backup.
Now I demonstrate it. First, load some data into the tablespace and backup it, then simulate the instance crash, the file within this tablespace and all archived logs lost also.
@>alter tablespace data end backup; Tablespace altered.
@>alter system switch logfile; System altered.
...
@>create table test.t2 tablespace data as select * from test.t1; Table created.
@>@exts t1
Old1:SelectOWNER, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKSFrom
Dba_extents where SEGMENT_NAME=upper('&1')
New1:SelectOWNER, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKSFrom
Dba_extents where SEGMENT_NAME=upper('t1')
OWNEREXTENT_IDFILE_IDBLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TEST0598
@>@exts t2
Old1:SelectOWNER, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKSFrom
Dba_extents where SEGMENT_NAME=upper('&1')
New1:SelectOWNER, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKSFrom
Dba_extents where SEGMENT_NAME=upper('t2')
OWNEREXTENT_IDFILE_IDBLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TEST05178
@>shutdown abort
Oracle instance shut down.
@>! rm /u03/oradata/9204/chen/data01.dbf
@>! rm /u03/arch/9204/*
@>startup
Oracle instance started.
Total System Global Area470881660 bytes Fixed Size451964 bytes Variable Size369098752 bytes
Database Buffers100663296 bytes Redo Buffers667648 bytes Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u03/oradata/9204/chen/data01.dbf'@>alter database datafile '/u03/oradata/9204/chen/data01.dbf' offline; Database altered.
@>ALTER DATABASE OPEN; Database altered.
@>select FILE#||' '||NAME||' '||BYTES files from v$datafile; FILES
------------------------------------------------------------
1 /u03/oradata/9204/chen/system01.dbf 251658240
2 /u03/oradata/9204/chen/undotbs01.dbf 67108864
3 /u03/oradata/9204/chen/test01.dbf 10485760
4 /u03/oradata/9204/chen/assm01.dbf 10485760
5 /u03/oradata/9204/chen/data01.dbf 10485760
@>shutdown immediate Database closed.
Database dismounted. Oracle instance shut down.
@>! cp/u03/oradata/9204/chen/data01.dbf.bak
/u03/oradata/9204/chen/data01.dbf
In the next steps, I modify file# 5 header information with BBED. Here I change five parts data: checkpoint SCN, checkpoint time, checkpoint number, rba and hot backup information. I refer to system file to obtain these information.
BBED> set dba 5,1
DBA0x01400001 (20971521 5,1)
BBED>
BBED> print kcvfh
Struct kcvfh, 360 bytes@0
...
Struct kcvfhckp, 36 bytes@140
Struct kcvcpscn, 8 bytes@140
Ub4 kscnbas@1400x000a6f4e
Ub2 kscnwrp@1440x0000
Ub4 kcvcptim@1480x2691e91e
Ub2 kcvcpthr@1520x0001
Union u, 12 bytes@156
Struct kcvcprba, 12 bytes@156
Ub4 kcrbaseq@1560x0000003a
Ub4 kcrbabno@1600x00000002
Ub2 kcrbabof@1640x0010
Struct kcvcptr, 12 bytes@156
Struct kcrtrscn, 8 bytes@156
Ub4 kscnbas@1560x0000003a
Ub2 kscnwrp@1600x0002
Ub4 kcrtrtim@1640x08990010
...
Ub4 kcvfhcpc@1760x00000011
Ub4 kcvfhrts@1800x2691cc5e
Ub4 kcvfhccc@1840x00000010
Struct kcvfhbcp, 36 bytes@188 – hotBackup information
Struct kcvcpscn, 8 bytes@188
Ub4 kscnbas@1880x000a6f4e
Ub2 kscnwrp@1920x0000
Ub4 kcvcptim@1960x2691e91e
Ub2 kcvcpthr@2000x0001
Union u, 12 bytes@204
Struct kcvcprba, 12 bytes@204
Ub4 kcrbaseq@2040x0000003a
Ub4 kcrbabno@2080x00000002
Ub2 kcrbabof@2120x0010
Struct kcvcptr, 12 bytes@204
Struct kcrtrscn, 8 bytes@204
Ub4 kscnbas@2040x0000003a
Ub2 kscnwrp@2080x0002
Ub4 kcrtrtim@2120x08990010
...
BBED>
BBED> dump /v dba 5,1 offset 140 count 48
File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1Offsets:140 to171Dba:0x01400001
-------------------------------------------------------
4e6f0a00 0000d90a 1ee99126 0100d90a l No....ù..é.&..ù.
3a000000 02000000 10009908 02000000 l:...............
00000000 11000000 5ecc9126 10000000 l ........^ì.&....
<16 bytes per line>
BBED> dump /v dba 5,1 offset 176 count 48
File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1Offsets:176 to223Dba:0x01400001
------------------------------------------------------- 11000000 5ecc9126 10000000 4e6f0a00 l ....^ì.&....No..
0000d90a 1ee99126 0100d90a 3a000000 l ..ù..é.&..ù.:...
02000000 10009908 02000000 00000000 l ................
<16 bytes per line>
BBED> set dba 1,1
DBA0x00400001 (4194305 1,1)
BBED> print kcvfh
Struct kcvfh, 360 bytes@0
...
Struct kcvfhckp, 36 bytes@140
Struct kcvcpscn, 8 bytes@140
Ub4 kscnbas@1400x000ac128
Ub2 kscnwrp@1440x0000
Ub4 kcvcptim@1480x2691f314
Ub2 kcvcpthr@1520x0001
Union u, 12 bytes@156
Struct kcvcprba, 12 bytes@156
Ub4 kcrbaseq@1560x00000041
Ub4 kcrbabno@1600x0000007b
Ub2 kcrbabof@1640x0010
Struct kcvcptr, 12 bytes@156
Struct kcrtrscn, 8 bytes@156
Ub4 kscnbas@1560x00000041
Ub2 kscnwrp@1600x007b
Ub4 kcrtrtim@1640x09110010
...
Ub4 kcvfhcpc@1760x0000006b
Ub4 kcvfhrts@1800x2691eb08
Ub4 kcvfhccc@1840x0000006a
Struct kcvfhbcp, 36 bytes@188
Struct kcvcpscn, 8 bytes@188
Ub4 kscnbas@1880x00000000
Ub2 kscnwrp@1920x0000
Ub4 kcvcptim@1960x00000000
Ub2 kcvcpthr@2000x0000
Union u, 12 bytes
Struct kcvcprba, 12 bytes@204
@204
Ub4 kcrbaseq@2040x00000000
Ub4 kcrbabno@2080x00000000
Ub2 kcrbabof@2120x0000
Struct kcvcptr, 12 bytes@204
Struct kcrtrscn, 8 bytes@204
Ub4 kscnbas@2040x00000000
Ub2 kscnwrp@2080x0000
Ub4 kcrtrtim@2120x00000000
... BBED>
BBED> dump /v dba 1,1 offset 140 count 48
File: /u03/oradata/9204/chen/system01.dbf (1)
Block: 1Offsets:140 to187Dba:0x00400001
------------------------------------------------------- 28c10a00 00000000 14f39126 01000000 l (á.......ó.&....
41000000 7b000000 10001109 02000000 l A...{...........
00000000 6b000000 08eb9126 6a000000 l ....k....?.&j...
<16 bytes per line>
BBED> dump /v dba 1,1 offset 176 count 48
File: /u03/oradata/9204/chen/system01.dbf (1)
Block: 1Offsets:176 to223Dba:0x00400001
------------------------------------------------------- 6b000000 08eb9126 6a000000 00000000 l k....?.&j.......
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
I change file# 5 header data according to the above output.
BBED> modify /x 14f39126 dba 5,1 offset 148 File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1Offsets:148 to195Dba:0x01400001
------------------------------------------------------------------------
14f39126 0100d90a 3a000000 02000000 10009908 02000000 00000000
11000000
5ecc9126 10000000 4e6f0a00 0000d90a
<32 bytes per line>
BBED> modify /x 41 dba 5,1 offset 156
File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1Offsets:156 to203Dba:0x01400001
------------------------------------------------------------------------
41000000 02000000 10009908 02000000 00000000 11000000 5ecc9126
10000000
4e6f0a00 0000d90a 1ee99126 0100d90a
<32 bytes per line>
BBED> modify /x 7b dba 5,1 offset 160
File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1Offsets:160 to207Dba:0x01400001
------------------------------------------------------------------------
7b000000 10009908 02000000 00000000 11000000 5ecc9126 10000000
4e6f0a00
0000d90a 1ee99126 0100d90a 3a000000
<32 bytes per line>
BBED> modify /x 10001109 dba 5,1 offset 164
File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1Offsets:164 to211Dba:0x01400001
------------------------------------------------------------------------
10001109 02000000 00000000 11000000 5ecc9126 10000000 4e6f0a00
0000d90a
1ee99126 0100d90a 3a000000 02000000
<32 bytes per line> Calculate checkpoint number.
0x3a -> 0x11 0x41 -> 0x18
Clear hot backup information, it’s not necessary, but I did here.
Now I prepare to put the hot backup file online.
@>startup mount
Oracle instance started.
Total System Global Area470881660 bytes Fixed Size451964 bytes Variable Size369098752 bytes
Database Buffers100663296 bytes Redo Buffers667648 bytes Database mounted.
@>alter database backup controlfile to trace; Database altered.
@>shutdown
ORA-01109: database not open Database dismounted.Oracle instance shut down.
@>! rm /u03/oradata/9204/chen/*.ctl
@>STARTUP NOMOUNT
Oracle instance started.
Total System Global Area470881660 bytes Fixed Size451964 bytes Variable Size369098752 bytes
Database Buffers100663296 bytes Redo Buffers667648 bytes
@>CREATECONTROLFILEREUSEDATABASE"CHEN"NORESETLOGS ARCHIVELOG
2--SET STANDBY TO MAXIMIZE PERFORMANCE
3MAXLOGFILES 5
4MAXLOGMEMBERS 5
5MAXDATAFILES 100
6MAXINSTANCES 1
7MAXLOGHISTORY 226
8LOGFILE
9GROUP 1 '/u03/oradata/9204/chen/redo01.log'SIZE 10M,
10GROUP 2 '/u03/oradata/9204/chen/redo02.log'SIZE 10M,
11GROUP 3 '/u03/oradata/9204/chen/redo03.log'SIZE 10M
12-- STANDBY LOGFILE
13DATAFILE
14'/u03/oradata/9204/chen/system01.dbf',
15'/u03/oradata/9204/chen/undotbs01.dbf',
16'/u03/oradata/9204/chen/test01.dbf',
17'/u03/oradata/9204/chen/assm01.dbf',
18'/u03/oradata/9204/chen/data01.dbf'
19CHARACTER SET US7ASCII 20;
CREATECONTROLFILEREUSEDATABASE"CHEN"NORESETLOGS ARCHIVELOG
*
ERROR at line 1
ORA-01503: CREATE CONTROLFILE failed
ORA-01229: data file 5 is inconsistent with logs
ORA-01110: data file 5: '/u03/oradata/9204/chen/data01.dbf'@>! rm /u03/oradata/9204/chen/*.ctl
@>CREATECONTROLFILEREUSEDATABASE"CHEN"NORESETLOGS ARCHIVELOG
2--SET STANDBY TO MAXIMIZE PERFORMANCE
3MAXLOGFILES 5
4MAXLOGMEMBERS 5
5MAXDATAFILES 100
6MAXINSTANCES 1
7MAXLOGHISTORY 226
8LOGFILE
9GROUP 1 '/u03/oradata/9204/chen/redo01.log'SIZE 10M,
10GROUP 2 '/u03/oradata/9204/chen/redo02.log'SIZE 10M,
11GROUP 3 '/u03/oradata/9204/chen/redo03.log'SIZE 10M
12-- STANDBY LOGFILE
13DATAFILE
14'/u03/oradata/9204/chen/system01.dbf',
15'/u03/oradata/9204/chen/undotbs01.dbf',
16'/u03/oradata/9204/chen/test01.dbf',
17'/u03/oradata/9204/chen/assm01.dbf'
18--'/u03/oradata/9204/chen/data01.dbf'
19CHARACTER SET US7ASCII 20;
Control file created.
@>ALTER DATABASE OPEN; Database altered.
@>select file_name from dba_data_files; FILE_NAME
---------------------------------------------------------------------------------------
-------------
/u03/oradata/9204/chen/system01.dbf
/u03/oradata/9204/chen/undotbs01.dbf
/u03/oradata/9204/chen/test01.dbf
/u03/oradata/9204/chen/assm01.dbf
/opt/app/Oracle/product/9.2.0/dbs/MISSING00005
@>alterDatabaseRenameFile
'/opt/app/Oracle/product/9.2.0/dbs/MISSING00005'To '/u03/oradata/9204/chen/data01.dbf';
Database altered.
@>select * from v$recover_file;
FILE# ONLINEONLINE_ ERRORCHANGE# TIME
---------- ------- ------- ------------------------------ ---------
5 OFFLINE OFFLINE704808 19-FEB-08
@>alter database recover datafile '/u03/oradata/9204/chen/data01.dbf'; Database altered.
@>alter database datafile '/u03/oradata/9204/chen/data01.dbf' online; Database altered.
@>select * from test.t1;
ID
----------
1
2
3
4
5
@>select * from test.t2; select * from test.t2
*
ERROR at line 1
ORA-08103: object no longer exists@>selectOwner, OBJECT_NAME, object_typeFromDba_objectsWhere object_name='T2';
OWNEROBJECT_NAMEOBJECT_TYPE
---------- ------------------------------ ------------------ TESTT2TABLE
Now we can access objects within the tablespace before it took hot backup, but all data are lost loaded after it backup.
References
Disassembling the Oracle Data Block
Advanced Backup, Restore, and Recover Techniques Recovery architecture Components
Blog:http://freelists.spaces.live.com
DBRECOVER Recovery Options
For Oracle incidents, start with the DBRECOVER for Oracle trial to verify table visibility, row previews, and export readiness on copied datafiles. For MySQL and InnoDB incidents, DBRECOVER for MySQL is free software and can inspect.ibd files, ibdata1, and database directories locally.
When the case is urgent, preserve the original files first, work from copies, and contact paid emergency support with the database version, platform, error messages, file list, and recovery objective.