Category: Rdb

RMU/Load is interestingly your friend.

I broke my database completely yesterday.

I was reorganizing a huge storage area into 6 different areas.
I forgot to remove one of the indices.
I aborted the ALTER STORAGE MAP.

Attempts to attach to the database informed me that the root storage area was corrupt.

It appears that I had placed a SET TRANSACTION BATCH UPDATE at the beginning of the process. It is well documented that if you abort a BATCH UPDATE transaction you will corrupt your database…I forgot and didn’t look at the commands carefully enough. I had tested this about 5 times on the backup system, but I moved the primary commands out of the test procedure…

Fortunately we have operator assisted backups – a full database backup every day after processing is complete. We take the backup and restore it to our backup system every weekday as well…so the process of restoring our database is pretty simple.

A reminder – we had a number of “FAILED” SQL/Services services. The simple answer was to do SQLSRV$SHUTDOWN followed immediately by SQLSRV$STARTUP.

The database was down for approximately 2 hours.

Read TAR

Metalink 2.0

Need to get special image for other bugfixes

From Ian Smith of Oracle:

If you go the EXPORT and IMPORT route then may I recommend the following:

rmu/extract/item=import

It already generates an IMPORT script.

Modify the IMPORT command to redefine any indices you want to change. IMPORT will replace the definitions in the RBR file with those on the command line. BUT make sure they are defined inside the IMPORT command (i.e. Only one semicolon)

In V7.1 you can RMU/LOAD from the RBR file (V7.1.0.2 and later documented next release 🙂

RMU/LOAD/MATCH_NAME personnel employees pers.rbr

You can then submit batch jobs to load from the RBR after creating the database using IMPORT … NO DATA.

-- Copyright 2002 - Oracle Corporation
-- Example CASE expression to translate Rdb system table
-- type information to SQL text
--

select
  RDB$FIELD_NAME as Column_Name,    !as "Column Name"
  case RDB$FIELD_TYPE
    when 6  then          -- Byte Integer
      'TINYINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 7  then          -- Word Integer
      'SMALLINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 8  then          -- Longword Integer
      'INTEGER' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 9  then          -- Quadword Integer
      'BIGINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 10 then 'REAL'       -- F_floating
    when 27 then 'DOUBLE PRECISION' -- G_floating
    when 14 then          -- Character-coded text
      'CHAR (' ||
      CAST(RDB$FIELD_LENGTH AS VARCHAR(5)) || ')'
    when 37 then          -- Varying Character-coded Text
      'VARCHAR (' ||
      CAST(RDB$FIELD_LENGTH AS VARCHAR(5)) || ')'
    when 21 then 'DECIMAL'      -- Packed decimal string
    when 261 then
      'LIST OF BYTE VARYING (' ||
      CAST(RDB$SEGMENT_LENGTH AS VARCHAR(5)) || ')'
    when 35 then
      -- Absolute Date and Time
      case RDB$FIELD_SUB_TYPE
        when 0  then
          'DATE VMS'
        when 56 then
          'TIME (' ||
          CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
        when 7  then
          'DATE ANSI'
        when 63 then
          'TIMESTAMP (' ||
          CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
        else 'INTERVAL ' ||
          case RDB$FIELD_SUB_TYPE
            when 513 then
              'YEAR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 515 then
              'YEAR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MONTH'
            when 514 then
              'MONTH(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 516 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 524 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO HOUR'
            when 540 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MINUTE'
            when 572 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 520 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 536 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MINUTE'
            when 568 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 528 then
              'MINUTE(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 560 then
              'MINUTE(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 544 then
              'SECOND(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ',' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            else
              '(unknown interval)'
          end
      end
    else
      '(unknown data type)'
  end as Data_Type          ! as "Data Type"
from RDB$FIELDS
where RDB$FIELD_NAME not starting with 'RDB';

Don’t EVER type a control-Y at an “RMU/REPAIR/ABM dbname” command where you have forgotten the /AREA qualifier.

Bad things happen to the RDB$SYSTEM area.

Hopefully completing a full RMU/REPAIR/ABM command will fix the damage.

Logical Area Number – I couldn’t remember this exactly… larea is logical area

select rdb$relation_name from rdb$relations
where rdb$storage_id = :larea_no
union
select rdb$index_name from rdb$indices
where rdb$index_id = :larea_no
union
select rdbvms$map_name from rdbvms$storage_map_areas
where rdb$storage_id = :larea_no;

If have received ABMBITERR messages you will probably be wanting to do this.

ABMBITERR, inconsistency between spam page and bit in
area bitmap in larea page

Explanation: Either an ABM page contains a bit set for a spam page
that does not manage the logical area of the ABM page
or an ABM page does not contain a bit set for a spam
page that does manage the logical area of the ABM page.
The

User Action: Use RMU REPAIR to rebuild the ABM pages.

$ rmu/close/cluster DB

$ rmu/repair/abm DB /area=the-area-name-from-the-select

yesterday’s activity – get the slow query to go faster. Two things

1. $ ASSIGN 4000 RDM$BIND_BUFFERS

changed the size of the database window from 200 buffers by a factor of 20. This allowed very large portions of the thrashing index area to reside in memory (60 I/O per second down to 1)

2. $ SET VOLUME DISK$SCRATCH /EXTENSION=16000

changed the file extend size from 5 – huge increase. Went from 3 extends per second to .03

Result? Approximately 12 hour query down to about 1 hour with zero code changes!

I like that!

rmu/analyze/opt=debug/end=1 DBname/out=logical_areas.dat

select rdbvms$map_name, rdbvms$area_name, rdb$storage_id, rdb$index_id from rdbvms$storage_map_areas

select rdb$relation_name, rdb$storage_id from rdb$relations order by 2