Oracle Put offline datafiles online without recovery

Put offline datafiles online without recovery By Chen

Here I’ll introduce one method to put offline datafiles to be online without
recovery.
We know Oracle check conrolfile information with the datafile header
information to determine whether this DB is consistent, whether the DB need
crash recover or media recover or open directly.
When tablespaces/datafiles are offline normally, the DB issues checkpoint on
these datafiles and update information on the datafiles header and controlfies.
If these files are offline immediate, only controlfile information is updated. The
files can be put online without recovery if they are offline normally, otherwise
they need recovery.
I take this experiment on noarchive mode DB. The main steps are:
1. Put one datafile offline;
2. Switch logfile, cause this offline datafile miss necessary redo logs to put it
online;
3. Modify this file header directly, advance the miss redo logs;
4. Re-create control file, the main purpose is to get rid of controlfile
information affect
@>conn test/test
Connected.
@>drop table t1;
Table dropped.
@>create table t1 tablespace test as select rownum id from all_objects where
rownum<6; Table created. @>conn /as sysdba
Connected.
@>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/app/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 37
Current log sequence 39
@>select tablespace_name ,file_name from dba_data_files; 
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u03/oradata/9204/chen/system01.dbf
UNDOTBS /u03/oradata/9204/chen/undotbs01.dbf
TEST /u03/oradata/9204/chen/test01.dbf
ASSM /u03/oradata/9204/chen/assm01.dbf
@>alter database datafile '/u03/oradata/9204/chen/test01.dbf' offline drop;
Database altered.
@>desc test.t1
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 ID NUMBER
@>select * from test.t1;
select * from test.t1
 *
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u03/oradata/9204/chen/test01.dbf'
@>select * from v$recover_file;
 FILE# ONLINE ONLINE_ ERROR
CHANGE# TIME
---------- ------- -------
----------------------------------------------------------------- ---------- ---------
 3 OFFLINE OFFLINE
639738 13-FEB-08
@>alter system switch logfile;
System altered.
…
@>alter database datafile '/u03/oradata/9204/chen/test01.dbf' online;
alter database datafile '/u03/oradata/9204/chen/test01.dbf' online
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/u03/oradata/9204/chen/test01.dbf' 
@>alter database recover datafile '/u03/oradata/9204/chen/test01.dbf';
alter database recover datafile '/u03/oradata/9204/chen/test01.dbf'
*
ERROR at line 1:
ORA-00279: change 639738 generated at 02/13/2008 06:09:57 needed for
thread 1
ORA-00289: suggestion : /opt/app/oracle/product/9.2.0/dbs/arch1_39.dbf
ORA-00280: change 639738 for thread 1 is in sequence #39
@>!ls /opt/app/oracle/product/9.2.0/dbs/arch1_39.dbf
ls: /opt/app/oracle/product/9.2.0/dbs/arch1_39.dbf: No such file or directory
@>select
name,STATUS,RECOVER,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_COUN
T from v$datafile_header;
NAME STATUS REC FUZ
CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- ------- --- --- ------------------
----------------
/u03/oradata/9204/chen/system01.dbf ONLINE NO YES
654168 77
/u03/oradata/9204/chen/undotbs01.dbf ONLINE NO YES
654168 77
/u03/oradata/9204/chen/test01.dbf OFFLINE YES YES
639738 37
/u03/oradata/9204/chen/assm01.dbf ONLINE NO YES
654341 45
@>select
name,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,OFFLINE_CHANGE#
from v$datafile;
NAME STATUS
CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
-------------------------------------------------- ------- ------------------
------------ ---------------
/u03/oradata/9204/chen/system01.dbf SYSTEM
654168 0
/u03/oradata/9204/chen/undotbs01.dbf ONLINE
654168 0
/u03/oradata/9204/chen/test01.dbf RECOVER
639738 639868 639725
/u03/oradata/9204/chen/assm01.dbf ONLINE
654341 653877 
Now I use BBED to modify /u03/oradata/9204/chen/test01.dbf file header
information. Here I change four parts data: checkpoint SCN, checkpoint time,
checkpoint number and rba. I refer to system file to obtain the information.
BBED> set dba 3,1
 DBA 0x00c00001 (12582913 3,1)
BBED>
BBED> print kcvfh
struct kcvfh, 360 bytes @0
...
 struct kcvfhckp, 36 bytes @140
 struct kcvcpscn, 8 bytes @140
 ub4 kscnbas @140 0x0009c2fa
 ub2 kscnwrp @144 0x0000
 ub4 kcvcptim @148 0x26899f35
 ub2 kcvcpthr @152 0x0001
 union u, 12 bytes @156
 struct kcvcprba, 12 bytes @156
 ub4 kcrbaseq @156 0x00000027
 ub4 kcrbabno @160 0x000000ab
 ub2 kcrbabof @164 0x0010
 struct kcvcptr, 12 bytes @156
 struct kcrtrscn, 8 bytes @156
 ub4 kscnbas @156 0x00000027
 ub2 kscnwrp @160 0x00ab
 ub4 kcrtrtim @164 0x00000010
...
 ub4 kcvfhcpc @176 0x00000025
 ub4 kcvfhrts @180 0x2689a13d
 ub4 kcvfhccc @184 0x00000024
...
BBED> dump /v dba 3,1 offset 140 count 32
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 140 to 171 Dba:0x00c00001
-------------------------------------------------------
 fac20900 00000000 359f8926 01009162 l ú?......5..&...b
 27000000 ab000000 10000000 02000000 l '...?........... 
 
BBED> set dba 1,1
 DBA 0x00400001 (4194305 1,1)
BBED>
BBED> print kcvfh
struct kcvfh, 360 bytes @0
...
 struct kcvfhckp, 36 bytes @140
 struct kcvcpscn, 8 bytes @140
 ub4 kscnbas @140 0x0009fb58
 ub2 kscnwrp @144 0x0000
 ub4 kcvcptim @148 0x268a4e7e
 ub2 kcvcpthr @152 0x0001
 union u, 12 bytes @156
 struct kcvcprba, 12 bytes @156
 ub4 kcrbaseq @156 0x00000031
 ub4 kcrbabno @160 0x0000000e
 ub2 kcrbabof @164 0x0010
 struct kcvcptr, 12 bytes @156
 struct kcrtrscn, 8 bytes @156
 ub4 kscnbas @156 0x00000031
 ub2 kscnwrp @160 0x000e
 ub4 kcrtrtim @164 0xbfff0010
...
 ub4 kcvfhcpc @176 0x0000004d
 ub4 kcvfhrts @180 0x26899cac
 ub4 kcvfhccc @184 0x0000004c
BBED> dump /v dba 1,1 offset 140 count 32
 File: /u03/oradata/9204/chen/system01.dbf (1)
 Block: 1 Offsets: 140 to 171 Dba:0x00400001
-------------------------------------------------------
 58fb0900 00000000 7e4e8a26 01003495 l X?......~N.&..4.
 31000000 0e000000 1000ffbf 02000000 l 1..........?....
 
BBED> modify /x 58fb0900 dba 3,1 offset 140
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u03/oradata/9204/chen/test01.dbf (3) 
 Block: 1 Offsets: 140 to 171 Dba:0x00c00001
------------------------------------------------------------------------
 58fb0900 00000000 359f8926 01009162 27000000 ab000000 10000000
02000000
 
BBED> modify /x 7e4e8a26 dba 3,1 offset 148
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 148 to 179 Dba:0x00c00001
------------------------------------------------------------------------
 7e4e8a26 01009162 27000000 ab000000 10000000 02000000 00000000
25000000
 
BBED> modify /x 31 dba 3,1 offset 156
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 156 to 187 Dba:0x00c00001
------------------------------------------------------------------------
 31000000 ab000000 10000000 02000000 00000000 25000000 3da18926
24000000
 
BBED> modify /x 0e dba 3,1 offset 160
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 160 to 191 Dba:0x00c00001
------------------------------------------------------------------------
 0e000000 10000000 02000000 00000000 25000000 3da18926 24000000
00000000
 

BBED> modify /x 10 dba 3,1 offset 164
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 164 to 195 Dba:0x00c00001
------------------------------------------------------------------------
 10000000 02000000 00000000 25000000 3da18926 24000000 00000000
00000000
 
The checkpoint number will be calculated according to the above information. 
The sequence in the offline file is 0x27, and checkpoint is 0x25, now the
sequence is 0x31, so the checkpoint number can be 0x2f. I think there’re no
effects if the checkpoint number doesn’t be changed.
0x27 -> 0x25
0x31 -> 0x2f
BBED> modify /x 2f dba 3,1 offset 176
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 176 to 207 Dba:0x00c00001
------------------------------------------------------------------------
 2f000000 3da18926 24000000 00000000 00000000 00000000 00000000
00000000
 
BBED> modify /x 2e dba 3,1 offset 184
 File: /u03/oradata/9204/chen/test01.dbf (3)
 Block: 1 Offsets: 184 to 215 Dba:0x00c00001
------------------------------------------------------------------------
 2e000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000
 
BBED> sum dba 3,1 apply
Check value for File 3, Block 1:
current = 0x615a, required = 0x615a
Now I put this offline file online again through create controlfile and skip to
recovery through the missing redo logs.
@>alter database backup controlfile to trace;
Database altered.
@>shutdown abort
ORACLE instance shut down.
@>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
NOARCHIVELOG
 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 CHARACTER SET US7ASCII
 19 ;
Control file created.
@>RECOVER DATABASE
Media recovery complete.
@>ALTER DATABASE OPEN;
Database altered.
@>desc test.t1
 Name Null? Type
 ----------------------------------------------------- --------
------------------------------------
 ID NUMBER
@>select * from test.t1;
 ID
----------
 1
 2 
 3
 4
 5
We can use this approach to get data back in some situations which data can’t
be got through normal methods.
But it maybe miss some data and the dictionary will mismatch with the actual
data.
References
Disassembling the Oracle Data Block
Advanced Backup, Restore, and Recover Techniques
Recovery architecture Components
msn: [email protected]
blog: http://freelists.spaces.live.com