Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle BBED Restoring a File
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Being able to plug in an older version of a file into the current instance could be a huge save in terms of recovery. At one point in time, a file has certain values related to its state, and that state is consistent with other files in the database. When the state is off, Oracle informs the DBA about it right away via one or more error messages, especially the one about a file needing more media recovery. In a normal recovery scenario where media loss has occurred, the recovery consists of restoring a backed up copy of one or more datafiles and then applying archived redo to bring the file to a consistent state.

 

In the bbed recovery scenario, one will not be applying redo, but rather will be jumping the state of the file from a point in the past to a point in time consistent with the rest of the database. The specific part of the file to be edited is the file header. Information will be needed from three structs: kcvfhckp, kcvfhcpc, and kcvfhccc. Published elsewhere are decode tables for many of these items, and these are generally easy to interpret. Kernel-related codes begin with a k, fh looks like file header, and the remainder are related to checkpoints. Specifically, good values from the following are needed:

  • kscnbas – last change SCN

  • kcvcptim – time of the last change

  • kcvfhcpc – checkpoint count

  • kcvfhccc – a checkpoint checker value, which is one less than kcvfhcpc

For file headers, one is interested in the output of the kcvfh struct. The output of “p kcvfh” is long, but it is interesting to browse through the output. It is also interesting how the name of the database (ORCL2 here) appears spelled out.

 

BBED> p kcvfh

struct kcvfh, 676 bytes                     @0

   struct kcvfhbfh, 20 bytes                @0

      ub1 type_kcbh                         @0        0x0b

      ub1 frmt_kcbh                         @1        0xa2

      ub1 spare1_kcbh                       @2        0x00

      ub1 spare2_kcbh                       @3        0x00

      ub4 rdba_kcbh                         @4        0x01800001

      ub4 bas_kcbh                          @8        0x00000000

      ub2 wrp_kcbh                          @12       0x0000

      ub1 seq_kcbh                          @14       0x01

      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)

      ub2 chkval_kcbh                       @16       0x8f50

      ub2 spare3_kcbh                       @18       0x0000

   struct kcvfhhdr, 76 bytes                @20

      ub4 kccfhswv                          @20       0x00000000

      ub4 kccfhcvn                          @24       0x0a200100

      ub4 kccfhdbi                          @28       0x266ecc46

      text kccfhdbn[0]                      @32      O

      text kccfhdbn[1]                      @33      R

      text kccfhdbn[2]                      @34      C

      text kccfhdbn[3]                      @35      L

      text kccfhdbn[4]                      @36      2

 

Here is the test case. A new tablespace (BB), user (BB) and table (EMP) where the table has three rows and three columns from Scott’s EMP table is created. The datafile is bb01.dbf, created with a size of 100K. A backup exists where the three rows are in place. The current instance has had a deletion of all three rows and the object is to restore the data by recovering the older datafile. Therefore, get a new list of files for use within the parameter file, and the copy of bb01.dbf will be the older version. Only two files are really needed – one with a good SCN state, and the older file, but all were listed anyway.

 

1 /opt/app/oracle/oradata/ORCL2/system01.dbf 513802240

2 /opt/app/oracle/oradata/ORCL2/undotbs01.dbf 36700160

3 /opt/app/oracle/oradata/ORCL2/sysaux01.dbf 272629760

4 /opt/app/oracle/oradata/ORCL2/users01.dbf 5242880

5 /opt/app/oracle/oradata/ORCL2/example01.dbf 104857600

6 /opt/app/oracle/oradata/ORCL2/bb01.dbf 106496

 

Replace the file and issue a startup command. One should see the error related to the “bad” file.

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  922746880 bytes

Fixed Size                  1222624 bytes

Variable Size             281020448 bytes

Database Buffers          633339904 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/opt/app/oracle/oradata/ORCL2/bb01.dbf'

 

The current checkpoint number is not necessarily needed, but since it can be pulled directly from the database, see what it is. It will also show up in the file itself courtesy of bbed. The current SCN is at 689110 and the bad file is at 685758.

 

SQL> select distinct checkpoint_change# from v$datafile;

 

CHECKPOINT_CHANGE#

------------------

            689110

 

SQL> select change# from v$recover_file;

 

   CHANGE#

----------

    685758

 

Start a bbed session and print the kcvfhckp struct. The first few lines are shown and the values of interest are the SCN and last time.

 

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes                   @484

   struct kcvcpscn, 8 bytes                 @484

      ub4 kscnbas                           @484      0x000a83d6

      ub2 kscnwrp                           @488      0x0000

   ub4 kcvcptim                             @492      0x2799048e

 

The hexadecimal value 0x000a83d6 should convert to 689110 in base 10 (decimal). Use the scientific calculator on Windows, something on the Internet, or get the decimal value manually. The fun way is by hand. The significant part of the hex value is a83d6. Convert hex to binary where each hex character is xxxx in binary. Now there is:

 

a

8

3

d

6

1010

1000

0011

1101

0110

 

Binary 1010100001111010110 is 689110, so that is good. Now print kcvfhcpc and kcvfhccc. The SYSTEM datafile is used by setting dba 1,1.

 

BBED> p kcvfhcpc

ub4 kcvfhcpc                                @140      0x0000004a

 

BBED> p kcvfhccc

ub4 kcvfhccc                                @148      0x00000049

 

One other, albeit final complication in all of this is taking into account the byte ordering of data on the platform the server is running on. It is big endian versus little endian. The database is running Oracle Enterprise Linux on a PC, so that makes it little endian. The order of the first two values has to be reversed (pair by pair). To summarize the changes to be made to the older file, see the table below.

 

 

Attribute

kscnbas

kcvcptim

kcvfhcpc

kcvfhccc

Value

000a83d6

(d6830a00)

2799048e

(8e049927)

4a

49

Offset

484

492

140

148

 

Use the modify command with /x for hexadecimal editing and a dba of file 6, block 1.

 

When finished with the four modify statements, perform a “sum dba x,1 apply” where “x” is your file number. If after applying the changes one receives an error upon startup, check the SCN values output from:

 

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE; and

SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# 

FROM V$LOG V1, V$LOGFILE V2

WHERE V1.GROUP# = V2.GROUP#;

 

If the SCN for the recovered file is wildly different from the others, the byte ordering of the hex value during the modify command might have been reversed.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  922746880 bytes

Fixed Size                  1222624 bytes

Variable Size             281020448 bytes

Database Buffers          633339904 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01122: database file 6 failed verification check

ORA-01110: data file 6: '/opt/app/oracle/oradata/ORCL2/bb01.dbf'

ORA-01207: file is more recent than control file - old control file

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.