Yaping's Weblog

August 29, 2008

Put datafiles online without recovery

Filed under: Oracle — Yaping @ 2:55 am
Tags: ,

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 .

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

 

 

@>select name,STATUS,RECOVER,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT 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 ‘…?………..

 

 <16 bytes per line>

 

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……….?….

 

 <16 bytes per line>

 

 

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

 

 <32 bytes per line>

 

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

 

 <32 bytes per line>

 

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

 

 <32 bytes per line>

 

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

 

 <32 bytes per line>

 

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

 

 <32 bytes per line>

 

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

 

 <32 bytes per line>

 

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

 

 <32 bytes per line>

 

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: