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: y.p.chen@hotmail.com blog: http://freelists.spaces.live.com
Comment