Anybody know OCOPY?

For more than ten years now SAP has provided a unified way to administer the Oracle database via the BR*Tools (Backup/Recovery).

Wether you like the tools or not (if you read We all hate the  BR*TOOLS, don’t we? about it, perhabs you’ll at least get used to them) they are used in every SAP installations that runs on Oracle. Although these tools got improved and enhanced in many ways over the years there is one thing that seemed to be fundamentally stable:
This is also mentioned in SAP note 968507 – Considerable enhancements to backups using BR*Tools 7.00.

For those interested how I analyzed this: I used FILEMON from the well-known SYSINTERNALS toolkit (http://www.microsoft.com/technet/sysinternals/default.mspx ).
On Windows systems the tools of this toolkit are really helpful to dig a level deeper.

Best regards,
Lars

Verification Speed….

I recently had to handle a case where a big and important system was rendered unusable by database corruptions. Due to some bad luck and faulty procedures, there was no good backup available. Even worse: the corruption had been detected via transaction abort in the SAP.

This case reminded me of the importance to perform regular consistency checks on the database – AT LEAST ONCE IN THE BACKUP CYCLE!

Why is this so important? Well, as soon as there is no backup available any more than a possibly detected corruption may be hard or impossible to solve.

Anyhow: many customers don’t do consistency checks and the main argument is, that it puts too much additional pressure on the system.

Therefore I decided to go for a quick test. What option of block consistency checks to the least harm to the system?

I tested two methods: dbverify, the old standard approach and rman validate check. The test machine is rather untypical: my Laptop (Pentium M@1.6 GHz, WinXP). To get realistic results for your environment just go ahead and perform the tests yourself.

With the recent version of the BRTOOLS (see note 1016173) both approaches are supported so it may be interesting to know which to choose.

The test case is build up by having one session doing some database work that is both I/O and CPU intensive (see scripts below). In a second command prompt, I started the database verification and compared the timings. These are the results:

                      no check     dbv     rman
      script duration     8:48    9:31     10:34
verification duration        -    1:42      5:32

In terms of runtimes of course not checking anything is the fastest option.
The second place goes to the verification with dbv as well for the script duration as for the check itself.
Last is the verification with rman.

So you should go for dbv, shouldn’t you? It’s unfortunately not that easy.

DBV always has to be executed as an additional operation and reports also errors on blocks that are currently not used by the database and thus won’t do any harm.
The reporting facility of DBV is rather weak – if there are several corrupt blocks found, it can get very uncomfortable to create an overview of affected segments.

RMAN, on the other hand, provides easy to query from database views that make it a “walk in the garden” to query views like V$COPY_CORRUPTION, V$BACKUP_CORRUPTION and V$DATABASE_BLOCK_CORRUPTION.
The downside is – beside the longer runtime – the fact that RMAN needs the DB_BLOCK_CHECKSUM parameter to be set to (at least) TRUE and that it only checks blocks that already got a checksum. If the database got created on Oracle 10g this is always the case but for databases with lower start releases, there still can be blocks without checksums.
The major advantage of RMAN is that it can perform the checks when backups are taken on the fly. That does not make restore/recovery tests with subsequent consistency checks superfluous but it gives a higher security level about the blocks read for the backup.
Here is a similar script like the one I used:

set timing on
create table test as
select a.object_type||a.owner||a.object_name as DATA
from dba_objects a, dba_objects b where rownum <=100000;
alter table test add (data2 char(200));
update test set data2=data;
create index t1 on test (data);
create bitmap index t2 on test (data2);
exec dbms_stats.gather_table_stats(user, 'TEST');
drop table test;
For the verification runs I used
brbackup -c force -w only_dbv

and

brbackup -c force -w only_rmv

Have fun trying it out on your systems!

Best regards,

Lars

Get me my data back! QUICK!

Ok, so your data is gone. You/the report you ran executed a DELETE statement and performed a COMMIT. What to do now?

It is the productive system. So there is no place to discuss that it may have been a great idea to test the report first here. What’s needed is a solution.

The overall solution: point-in-time (p.i.t.) recovery of the database. But this has MANY disadvantages. Think of downtimes, think of work to be redone, think of all the other connected systems that won’t get set back in time. A whole bunch of inter-system-consistency problems will rise.

If you want to “undo” just the changes to the single table you perhaps may have a chance to get the system back to a consistent state again. The idea here is: there are several thousand tables in the SAP database. Only one of them got messed up, so the problem is rather local. Perhaps the solution can be local as well.

An often heard solution for this “one-table-recovery”-problem is: do a p.i.t.-recovery of the database to a second instance up to the time before the data got deleted, export the table from there and import the data back into the productive database. This procedure works (often) but is rather complicated and complex. This is a task of several hours.

We will try to reduce this time to a few minutes now.

We will try to create a new table with the same structure than our messed up table and copy all the data we can get from old data block versions into this new table. To do so we will utilize the ORACLE FLASHBACK QUERY functionality.

STEP ONE

Keep calm. Keep calm. Keep calm! One cannot overstate the importance of keeping his mind focused in a situation like this.

STEP TWO

Stop changing data on the table – right away! Best would be: stop changing in the database at all. To get the data back we will need the OLD block versions of the table, that is: the UNDO blocks of this table. If there is further work on the database – even if not on our table – it may overwrite the necessary UNDO blocks.

STEP THREE

a) Logon to the database as the SAP SCHEMA owner, e.g. SAPR3, SAPERP, SAPPRD

b) Create a copy of our table structure. I will call the table “AAA” for the sake simplicity here:

create table "AAA_COPY" as SELECT * FROM "AAA" where rownum<1;

c) Copy the data from the old block versions to the copy table. For this example let’s assume the data was deleted today at 13:45.

Get current date:

select to_char(trunc(sysdate), 'dd.mm.yyyy')  as time from dual;

TIME
----------
08.05.2007

Insert old data into copy table:

insert into "AAA_COPY" as select * from "AAA" as of timestamp to_timestamp('08.05.07 13:45:00', 'dd.mm.yy hh24:mi:ss');

If no error like ORA-1555 came up, then: cheer up!

d) VERY important: Don’t rename the copy table now to replace the original one. It currently just “looks” like the original one, but it is not similar to it. We defined no Constraints, no Defaultvalues no indexes. We’ve to copy over the data back into the original table.

Pay attention to the fact that this may lead to duplicate entries, depending on how keys for the specific table are generated.

If the original data was not deleted, but updated in a wrong way, then you will have to remove the wrong rows first or write a UPDATE statement to change the data back.

So as you can see, it is fairly easy to get back the accidentally deleted or changed data back.

Nevertheless this is not a silver bullet for all “user errors” concerning data in tables. Plus it has many constraints like: not useable on tables with LONG or LOB columns, depends on UNDO-information that might have already been deleted, no automatic consistency check from application side.

Nevertheless: it’s a quick and easy solution that seems to be used not too often, since it’s not so well known yet. Best thing to do to get used to this: try it out!

Use your test system, go and create a dummy table and fill it with data. Delete this data and get it back. Once you get used to it, it’s really no big deal when the next emergency call about deleted data comes in.

Best regards,

Lars

p.s.: Here is a link for further reading:

Oracle® Database Application Developer’s Guide – Developing Flashback Applications\  \