Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DISASTER RECOVERY: Our experience and questions for Gurus!

Re: DISASTER RECOVERY: Our experience and questions for Gurus!

From: Graham C Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: Thu, 9 Dec 1999 10:07:07 -0600
Message-ID: <82ok35$b73@news.abbott.com>


Deja User wrote in message <82h51r$sjk$1_at_nnrp1.deja.com>...
>We had to do a point-in-time recovery and ran into some unexpected
>scenerio. Your help will be appreciated. (FYI, the most important
>question is at the very end).
>

<SNIP>

Jeff Hunter has already done a first-class job of answering most of the questions posed in this post, I just wanted to touch briefly on the third question reagrding the obtaining of SCNs for DDL operations:

>Third attemp at recovery:
>It was the plain old point-in-time recovery with datafiles copied from
>an old backup. The command used was "recover database until time "1999-
>12-03:11:00:00". Notice that we don't have milliseconds in the command.
>Should we? Anyway, we didn't have the change number prior to the "drop
>tablespace" so we only had the "time" option. THIRD QUESTION: How can a
>change-number be obtained for an operation which you are trying to
>recover upto? For example, in our case, how could we get the change-
>number for the last commit prior to the "drop tablespace" command.

This is a problem I've encountered before, usually when a developer drops a key table that they just spent several hours inserting into - they want to recover to just before the drop, but they dithered before calling me and now can only offer an approximate time for when the drop operation was issued.

The best option here is to recover to the SCN right before the drop, and so we need to find out which SCN that was.....

  1. Locate the redo-logs that were in use at the approximate time of the drop. If the required redo-log is still current issue a switch logfile command to release and archive it.
  2. Dump the logfile to the trace directory.
  3. Using a suitably powerful text editor/browser, browse the dumped log file and look for the DDL operation that dropped the tablespace or table.

When dropping a tablespace, Oracle actually performs a number of DML operations on the tables of the data-dictionary, including FET$, UET$ and others. One table that will be uniquely hit will be TS$ - where tablespace data is held. In this example, TS$ has an object number of 16, although that may differ for other versions/ports of Oracle, and may also change if you modify the SQL.BSQ file.

We can scan the dumped log file looking for update to TS$, by doing a text search for the 'objn' keyword (think it was just obj before Oracle 8 ) with the number 16.

The dump below shows the relevant part of the log file with the object number.

REDO RECORD - Thread:1 RBA: 0x000104.00000006.016c LEN: 0x02bc VLD: 0x01 SCN scn: 0x0000.0000d74d 12/09/99 08:50:08 CHANGE #1 TYP:0 CLS:15 AFN:2 DBA:0x00800002 SCN:0x0000.0000d74c SEQ: 1 OP:5.2
ktudh redo: slt: 0x0004 sqn: 0x00000039 flg: 0x0012 siz: 264 fbi: 0

            uba: 0x00800008.0018.05 pxid: xid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:16 AFN:2 DBA:0x00800008 SCN:0x0000.0000d74b SEQ: 2 OP:5.1

ktudb redo: siz: 264 spc: 1454 flg: 0x0012 seq: 0x0018 rec: 0x05
            xid: 0x0002.004.00000039
ktubl redo: slt: 4 rci: 0 opc: 11.1 objn: 16 objd: 6 tsn: 0
  # my annotation to aid clarity #  ^^^^^^^^
Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No

            rdba: 0x00000000 prev ctl uba: 0x00800008.0018.03 prev ctl max cmt scn: 0x0000.0000d722 prev tx cmt scn: 0x0000.0000d723 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.006.0000001e uba: 0x0080004d.000f.04

                      flg: C---    lkc:  0     scn: 0x0000.0000d748
KDO Op code: URP xtype: XA bdba: 0x004028c3 hdba: 0x0040007a
itli: 2  ispac: 0  maxfr: 1162
tabn: 2 slot: 0(0x0) flag: 0x6c lock: 0 ckix: 0
ncol: 24 nnew: 18 size: 0
col  0: [ 4]  49 4e 44 58
col  1: [ 1]  80
col  2: [ 2]  c1 02
col  3: [ 1]  80
col  4: [ 1]  80
col  5: [ 1]  80
col  6: [ 3]  c2 15 31
col  8: [ 1]  80
col  9: [ 1]  80
col 10: [ 2]  c1 02
col 11: [ 3]  c2 02 16
col 12: [ 2]  c1 06
col 13: [ 2]  c1 06
col 14: [ 1]  80
col 15: [ 2]  c1 33
col 16: [ 2]  c1 02

col 22: [ 1] 80
col 23: [ 4] c3 06 34 09

But how can we be sure this update to TS$ is dropping the tablespace INDX? - look
at the column data for COL 0 ( hex data 49 4E 44 58 ) - translate this to ANSI and
it reveals the tablespace name 'INDX'.

OK, we know where the tablespace drop occured, but what is the SCN that triggered it?
Look at the start of the dump again....

REDO RECORD - Thread:1 RBA: 0x000104.00000006.016c LEN: 0x02bc VLD: 0x01 SCN scn: 0x0000.0000d74d 12/09/99 08:50:08 CHANGE #1 TYP:0 CLS:15 AFN:2 DBA:0x00800002 SCN:0x0000.0000d74c SEQ: 1 OP:5.2

We can see on the second line that this redo record is for SCN 0x0000.0000d74d or 55117 -
but be careful - note the third line references SCN:0x0000.0000d74c - the update to TS$
was not the first operation Oracle did as part of the task of dropping the tablespace -
and so recovering to SCN 55117 may leave you with a tablespace but no tables.

Trace the log file backwards until you reach a record where the SCN referenced on line
two is the same as the one on line 3, in my case it is as follows:

REDO RECORD - Thread:1 RBA: 0x000104.00000002.01cc LEN: 0x00ec VLD: 0x01 SCN scn: 0x0000.0000d74b 12/09/99 08:50:08 CHANGE #1 TYP:0 CLS:16 AFN:2 DBA:0x00800008 SCN:0x0000.0000d74b SEQ: 1 OP:5.1

So the SCN that started this all was 0x0000.0000d74b or 55115. Now you can restore the
backup and roll forward to SCN 55114 - the change right before the drop tablespace was
issued.

Use the above information at your own risk - and always make a backup before you start.

Hope some of that helps.

Graham

--
Empowerment - delegating the responsibility but not the authority.


Opinions expressed do not necessarily reflect those of Abbott Laboratories. Received on Thu Dec 09 1999 - 10:07:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US