Email: [email protected] 7 x 24 online support!
Put hot backup datafiles online without recovery
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
old 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from
dba_extents where SEGMENT_NAME=upper('&1')
new 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from
dba_extents where SEGMENT_NAME=upper('t1')
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TEST 0 5 9 8
@>@exts t2
old 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from
dba_extents where SEGMENT_NAME=upper('&1')
new 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from
dba_extents where SEGMENT_NAME=upper('t2')
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ---------- ---------- ---------- ----------
TEST 0 5 17 8
@>shutdown abort
ORACLE instance shut down.
@>!rm /u03/oradata/9204/chen/data01.dbf
@>!rm /u03/arch/9204/*
@>startup
ORACLE instance started.
Total System Global Area 470881660 bytes Fixed Size 451964 bytes Variable Size 369098752 bytes
Database Buffers 100663296 bytes Redo Buffers 667648 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
DBA 0x01400001 (20971521 5,1)
BBED>
BBED> print kcvfh
struct kcvfh, 360 bytes @0
…
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
ub4 kscnbas @140 0x000a6f4e
ub2 kscnwrp @144 0x0000
ub4 kcvcptim @148 0x2691e91e
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x0000003a
ub4 kcrbabno @160 0x00000002
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x0000003a
ub2 kscnwrp @160 0x0002
ub4 kcrtrtim @164 0x08990010
…
ub4 kcvfhcpc @176 0x00000011
ub4 kcvfhrts @180 0x2691cc5e
ub4 kcvfhccc @184 0x00000010
struct kcvfhbcp, 36 bytes @188 – hot backup information
struct kcvcpscn, 8 bytes @188
ub4 kscnbas @188 0x000a6f4e
ub2 kscnwrp @192 0x0000
ub4 kcvcptim @196 0x2691e91e
ub2 kcvcpthr @200 0x0001
union u, 12 bytes @204
struct kcvcprba, 12 bytes @204
ub4 kcrbaseq @204 0x0000003a
ub4 kcrbabno @208 0x00000002
ub2 kcrbabof @212 0x0010
struct kcvcptr, 12 bytes @204
struct kcrtrscn, 8 bytes @204
ub4 kscnbas @204 0x0000003a
ub2 kscnwrp @208 0x0002
ub4 kcrtrtim @212 0x08990010
…
BBED>
BBED> dump /v dba 5,1 offset 140 count 48
File: /u03/oradata/9204/chen/data01.dbf (5)
Block: 1 Offsets: 140 to 171 Dba: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: 1 Offsets: 176 to 223 Dba: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
DBA 0x00400001 (4194305 1,1)
BBED> print kcvfh
struct kcvfh, 360 bytes @0
…
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
ub4 kscnbas @140 0x000ac128
ub2 kscnwrp @144 0x0000
ub4 kcvcptim @148 0x2691f314
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x00000041
ub4 kcrbabno @160 0x0000007b
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x00000041
ub2 kscnwrp @160 0x007b
ub4 kcrtrtim @164 0x09110010
…
ub4 kcvfhcpc @176 0x0000006b
ub4 kcvfhrts @180 0x2691eb08
ub4 kcvfhccc @184 0x0000006a
struct kcvfhbcp, 36 bytes @188
struct kcvcpscn, 8 bytes @188
ub4 kscnbas @188 0x00000000
ub2 kscnwrp @192 0x0000
ub4 kcvcptim @196 0x00000000
ub2 kcvcpthr @200 0x0000
union u, 12 bytes
struct kcvcprba, 12 bytes @204
@204
ub4 kcrbaseq @204 0x00000000
ub4 kcrbabno @208 0x00000000
ub2 kcrbabof @212 0x0000
struct kcvcptr, 12 bytes @204
struct kcrtrscn, 8 bytes @204
ub4 kscnbas @204 0x00000000
ub2 kscnwrp @208 0x0000
ub4 kcrtrtim @212 0x00000000
… BBED>
BBED> dump /v dba 1,1 offset 140 count 48
File: /u03/oradata/9204/chen/system01.dbf (1)
Block: 1 Offsets: 140 to 187 Dba: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: 1 Offsets: 176 to 223 Dba: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: 1 Offsets: 148 to 195 Dba: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: 1 Offsets: 156 to 203 Dba: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: 1 Offsets: 160 to 207 Dba: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: 1 Offsets: 164 to 211 Dba: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 Area 470881660 bytes Fixed Size 451964 bytes Variable Size 369098752 bytes
Database Buffers 100663296 bytes Redo Buffers 667648 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 Area 470881660 bytes Fixed Size 451964 bytes Variable Size 369098752 bytes
Database Buffers 100663296 bytes Redo Buffers 667648 bytes
@>CREATE CONTROLFILE REUSE DATABASE "CHEN" NORESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 5
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 '/u03/oradata/9204/chen/redo01.log' SIZE 10M,
10 GROUP 2 '/u03/oradata/9204/chen/redo02.log' SIZE 10M,
11 GROUP 3 '/u03/oradata/9204/chen/redo03.log' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
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'
19 CHARACTER SET US7ASCII 20 ;
CREATE CONTROLFILE REUSE DATABASE "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
@>CREATE CONTROLFILE REUSE DATABASE "CHEN" NORESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 5
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 '/u03/oradata/9204/chen/redo01.log' SIZE 10M,
10 GROUP 2 '/u03/oradata/9204/chen/redo02.log' SIZE 10M,
11 GROUP 3 '/u03/oradata/9204/chen/redo03.log' SIZE 10M
12 -- STANDBY LOGFILE
13 DATAFILE
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'
19 CHARACTER 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
@>alter database rename file
'/opt/app/oracle/product/9.2.0/dbs/MISSING00005' to '/u03/oradata/9204/chen/data01.dbf';
Database altered.
@>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
5 OFFLINE OFFLINE 704808 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
@>select owner,OBJECT_NAME,object_type from dba_objects where object_name='T2';
OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------ TEST T2 TABLE
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
msn: [email protected]
Copyright © 2013, 2023, parnassusdata.com. 版权所有。dbrecover.com
沪公网安备 31010802001377号