美创科技技术社区

注册

 

发新话题 回复该主题

How to Skip Archived logs for capture [复制链接]

1#


How to Skip Archived logsFrom smenu

Jump to: navigation, search


In this section we will deal with the loss of an archivelog.

For streams it is clear that losing an archive means data will not be replicated. Normally you are supposed to re-export the source site and import in Target site. Since this is not always possible we will explore a different scenario. Needless to say that this is not supported by Oracle.

1) Starting from a sane situation, we will:

    - export the data dictionary    - Stop the capture    - Switch logfile    - insert rows into source, it will go into new logfile    - Switch logfile    - export the data dictionary    - remove the archive between the 2 exports data dictionary.    - restart capture.

Having done this we created a corrupted streams environment that we intend to put back on track with whatever data are left. Let’s explore this:

We build a new data dictionary:

[stream01:SRCV10G]:/home/oracle> cap -build -xset serveroutput onvariable f_scn number;begin_scn := 0;dbms_capture_adm.build(_scn);dbms_output.put_line('the first_scn value is ' || _scn);end;/PL/SQL procedure successfully completed.[stream01:SRCV10G]:/oracle/home> cap -stop CAPTURE_SCOTT -xexecute  DBMS_CAPTURE_ADM.STOP_CAPTURE( capture_name => 'CAPTURE_SCOTT');PL/SQL procedure successfully completed.

Next we connect as sysdba and switch logfile.

SQL> alter system switch logfile;System altered.

Now we insert a ROW that will never be on target site for we intend to delete this archive before the capture may ever process this. Capture will be forced to jump over this archive and its contents if it want to ever restart.

sqlplus  scott/tiger and some sql to insert a new row into scott.t1old   1: insert into t1 values (&max_col1+1,'SRC',systimestamp)new   1: insert into t1 values (      2314+1,'SRC',systimestamp)1 row created.[stream01:SRCV10G]:/home/orcle> view_t1.ksh      2315 SRC                                                2009-11-12 14:32:10.229045      2314 SRC                                                2009-11-12 11:34:41.644761      2313 SRC                                                2009-11-12 11:23:54.914966      2312 SRC                                                2009-11-12 11:04:07.953361      2311 MYSQYPOQBAQOPUKWEVJCFHPHUCLSLMNIZEJBPPJFZLQYJHAGUS 2009-11-09 15:11:37.878694

Now it is time to switch logfile, export again a new data dictionary and delete the in-between archive logs:

SQL> alter system switch logfile ;System altered.

Before removing the archive we need to be sure there are not in active mode or we corrupt and crash our DB: If so, then a ‘clear unarchived logfile’ will speed the call to DBWR.

[stream01:SRCV10G]:/home/orcle> rdl                                                                                                               SizeGroup    THREAD#                    Member                     Archived     Status    SEQUENCE#  FIRST_CHANGE# (MB)----- ---------- --------------------------------------------- ---------- ---------- ---------- -------------- ----    1          1 /oradata/datafiles/SRCV10G/redo01.log         YES        ACTIVE             67        2143941   50    2          1 /oradata/datafiles/SRCV10G/redo02.log         YES        ACTIVE             66        2143738   50    3          1 /oradata/datafiles/SRCV10G/redo03.log         NO         CURRENT            68        2143966   50SQL> alter database clear unarchived logfile group 1 ;Database altered.SQL> alter database clear unarchived logfile group 2 ;Database altered.[stream01:SRCV10G]:/home/orcle> rdl                                                                                                               SizeGroup    THREAD#                    Member                     Archived     Status    SEQUENCE#  FIRST_CHANGE# (MB)----- ---------- --------------------------------------------- ---------- ---------- ---------- -------------- ----    1          1 /oradata/datafiles/SRCV10G/redo01.log         YES        UNUSED              0        2143941   50    2          1 /oradata/datafiles/SRCV10G/redo02.log         YES        UNUSED              0        2143738   50    3          1 /oradata/datafiles/SRCV10G/redo03.log         NO         CURRENT            68        2143966   50

Our archives stands like:

                                                                                 Standby Deleted         Dic   THREAD#  SEQUENCE#      First time             Next time       APP Status      Dest   By Rman REGISTR Beg---------- ---------- --------------------- --------------------- --- ---------- ------- ------- ------- ---         1         70 2009-11-12 14:33:22   2009-11-12 14:33:26   NO      A        NO      NO    ARCH    YES         1         69 2009-11-12 14:33:22   2009-11-12 14:33:22   NO      A        NO      NO    ARCH    NO         1         68 2009-11-12 14:32:29   2009-11-12 14:33:22   NO      A        NO      NO    ARCH    NO         1         67 2009-11-12 14:31:53   2009-11-12 14:32:29   NO      A        NO      NO    ARCH    NO         1         66 2009-11-12 14:31:26   2009-11-12 14:31:53   NO      A        NO      NO    ARCH    NO         1         65 2009-11-12 14:31:24   2009-11-12 14:31:26   NO      A        NO      NO    RMAN    YES         1         64 2009-11-12 14:31:24   2009-11-12 14:31:24   NO      A        NO      NO    ARCH    NO         1         63 2009-11-12 11:25:05   2009-11-12 14:31:24   NO      A        NO      NO    ARCH    NO

We have 2 dictionary on archive 65 and 70. So we may jump over 66,67,68 and 69 and skip their contents. Once it is done we definitely loose the capacity to replicate row 2315 which was create on source while archive 66 was active:

[stream01:SRCV10G]:/oraadm01/app/oracle/flash_recovery_area/SRCV10G/archivelog/2009_11_12> ll-rw-r----- 1 oracle oinstall  9395712 Nov 12 11:25 o1_mf_1_62_5hqrn1wg_.arc-rw-r----- 1 oracle oinstall  7015424 Nov 12 14:31 o1_mf_1_63_5hr3kd9v_.arc-rw-r----- 1 oracle oinstall     1024 Nov 12 14:31 o1_mf_1_64_5hr3kddm_.arc-rw-r----- 1 oracle oinstall  9426944 Nov 12 14:31 o1_mf_1_65_5hr3kgk7_.arc-rw-r----- 1 oracle oinstall    90112 Nov 12 14:31 o1_mf_1_66_5hr3l90x_.arc-rw-r----- 1 oracle oinstall    10752 Nov 12 14:32 o1_mf_1_67_5hr3mflf_.arc-rw-r----- 1 oracle oinstall    13312 Nov 12 14:33 o1_mf_1_68_5hr3o2mf_.arc-rw-r----- 1 oracle oinstall     1024 Nov 12 14:33 o1_mf_1_69_5hr3o2nt_.arc-rw-r----- 1 oracle oinstall  9414144 Nov 12 14:33 o1_mf_1_70_5hr3o6lt_.arc[stream01:SRCV10G]:/oraadm01/app/oracle/flash_recovery_area/SRCV10G/archivelog/2009_11_12>mv o1_mf_1_69_5hr3o2nt_.arc o1_mf_1_68_5hr3o2mf_.arc o1_mf_1_67_5hr3mflf_.arc  o1_mf_1_66_5hr3l90x_.arc /tmp

Now we may restart the capture, it will be waiting on redo:

[stream01:SRCV10G]:/home/oracle> cap -start capture_scott -x Log                        Captured                   Capture  ID Capture user           Start scn   CHANGE_TIME    Type       Rule set Name   Neg rule set    Status---- -------------------- ------------- -------------- ---------- --------------- --------------- --------  61 STRMADMIN                  2135135 12-11 14:35:33 LOCAL      RULESET$_44                     ENABLED                                                                                                                 Last remote Log                                                        Last system    Last scn      Delay      Last scn      confirmed      Delay  ID Capture name           QUEUE_NAME                          scn         Scanned     Scanned     enqueued     scn Applied  Enq-Applied---- ---------------------- ------------------------------ ------------- ------------- ---------- ------------- ------------- -----------  61 CAPTURE_SCOTT          SCOTT_CAP_Q                          2144965       2143738       1227             0       2143220    -2143220 Log Process                                                                   Row creation                  total msg  total msg  ID   Name          SID    SERIAL# STATE                    Capture at        initial time          Lat(s)   Captured    Enqueue---- -------- ---------- ---------- ------------------------ ----------------- ----------------- ---------- ---------- ----------  61 C001            140         23 WAITING FOR REDO         14:35:47 11/12/09                          263       5100          0

This is expected streams behavior when archives are missing and counters situations is as:

[stream01:SRCV10G]:/home/oracle> cap -lck                                                                               Checkpoint                                                                               Retention    Last scn      Requiered         MaxCapture name               First scn     Start scn  Applied scn   Captured scn    time      Enqueued    Checkpoint scn Checkpoint scn---------------------- ------------- ------------- ------------- ------------- ---------- ------------- -------------- --------------CAPTURE_SCOTT                2135135       2135135       2143220       2143220          1             0        2137328        2143220[stream01:SRCV10G]:/home/oracle> apl   THREAD#  SEQUENCE#      First time             Next time       APP Status      Dest   By Rman REGISTR Beg---------- ---------- --------------------- --------------------- --- ---------- ------- ------- ------- ---         1         70 2009-11-12 14:33:22   2009-11-12 14:33:26   NO      A        NO      NO    ARCH    YES         1         69 2009-11-12 14:33:22   2009-11-12 14:33:22   NO      A        NO      NO    ARCH    NO         1         68 2009-11-12 14:32:29   2009-11-12 14:33:22   NO      A        NO      NO    ARCH    NO         1         67 2009-11-12 14:31:53   2009-11-12 14:32:29   NO      A        NO      NO    ARCH    NO         1         66 2009-11-12 14:31:26   2009-11-12 14:31:53   NO      A        NO      NO    ARCH    NO

Now let’s find the proper new value for the new FIRST_SCN that will allow us to skip the contents of archive 66->69

[stream01:SRCV10G]:/home/oracle> apl -nIf 'Name' is empty then the archive is not on disk anymore   THREAD# Logseq  Start          End             First Change   Next Change   Name---------- ------- -------------- -------------- -------------- -------------- -------------------------------------------------------         1      70 11-12 14:33:22 11-12 14:33:26        2144015        2144611 /oraadm01/app/oracle/flash_recovery_area/SRCV10G/archiv                                                                               elog/2009_11_12/o1_mf_1_70_5hr3o6lt_.arc         1      69 11-12 14:33:22 11-12 14:33:22        2144013        2144015 /oraadm01/app/oracle/flash_recovery_area/SRCV10G/archiv                                                                               elog/2009_11_12/o1_mf_1_69_5hr3o2nt_.arc

The new FIRST_SCN must be into an archive with a dictionary, so here it is 70. But oracle perform a check on FIRST_SCN < DBA_CAPTURE.APPLIED_SCN. this is annoying as it will prevent resetting the FIRST_SCN:

dbms_capture_adm.alter_capture( capture_name => 'CAPTURE_SCOTT',first_scn=> 2020101);DECLARE*ERROR at line 1:ORA-26667: invalid STREAMS parameter FIRST_SCNORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 166ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 212ORA-06512: at line 28

The trick consists in deleting from SYSTEM.LOGMNR_RESTART_CKPT$ all LCR whose SCN is smaller to the first SCN of archive 70, that is 2144015:

SQL> delete from system.logmnr_restart_ckpt$ where spare1 < 2144611 ;4 rows deleted.SQL> commit ;Commit complete.SQL> exec dbms_capture_adm.alter_capture(capture_name => 'CAPTURE_SCOTT',                               first_scn=>2144611,start_scn=>2144611) ;PL/SQL procedure successfully completed.SQL> exit

The new situation is:

[stream01:SRCV10G]:/home/oracle> cap -lck                                                                               Checkpoint                                                                               Retention    Last scn      Requiered         MaxCapture name               First scn     Start scn  Applied scn   Captured scn    time      Enqueued    Checkpoint scn Checkpoint scn---------------------- ------------- ------------- ------------- ------------- ---------- ------------- -------------- --------------CAPTURE_SCOTT                2144611       2144611             0             0          1                            0              0

Now we can restart the capture:

[stream01:SRCV10G]:/home/oracle> cap -start capture_scott  -x Log                        Captured                   Capture  ID Capture user           Start scn   CHANGE_TIME    Type       Rule set Name   Neg rule set    Status---- -------------------- ------------- -------------- ---------- --------------- --------------- --------  61 STRMADMIN                  2144611 12-11 14:39:29 LOCAL      RULESET$_44                     ENABLED                                                                                                                 Last remote Log                                                        Last system    Last scn      Delay      Last scn      confirmed      Delay  ID Capture name           QUEUE_NAME                          scn         Scanned     Scanned     enqueued     scn Applied  Enq-Applied---- ---------------------- ------------------------------ ------------- ------------- ---------- ------------- ------------- -----------  61 CAPTURE_SCOTT          SCOTT_CAP_Q                          2145254       2145084        170             0       2144611    -2144611 Log Process                                                                   Row creation                  total msg  total msg  ID   Name          SID    SERIAL# STATE                    Capture at        initial time          Lat(s)   Captured    Enqueue---- -------- ---------- ---------- ------------------------ ----------------- ----------------- ---------- ---------- ----------  61 C001            140         25 CAPTURING CHANGES        14:39:33 11/12/09                            4        410          0

We insert on source a new ROW and this row must be present on target even if previous row is, from now, forever missing on the same target:

sqlplus  scott/tiger and some sql to insert a new row into scott.t1old   1: insert into t1 values (&max_col1+1,'SRC',systimestamp)new   1: insert into t1 values (      2315+1,'SRC',systimestamp)1 row created.[stream01:SRCV10G]:/home/orcle> view_t1.ksh      2316 SRC                                                2009-11-12 14:40:48.766265      2315 SRC                                                2009-11-12 14:32:10.229045      2314 SRC                                                2009-11-12 11:34:41.644761      2313 SRC                                                2009-11-12 11:23:54.914966      2312 SRC                                                2009-11-12 11:04:07.953361

On target we have :

[stream02RGV10G]:/home/oracle> view_t1.ksh      2316 SRC                                                2009-11-12 14:40:48.766265      2314 SRC                                                2009-11-12 11:34:41.644761      2312 SRC                                                2009-11-12 11:04:07.953361      2311 MYSQYPOQBAQOPUKWEVJCFHPHUCLSLMNIZEJBPPJFZLQYJHAGUS 2009-11-09 15:11:37.878694      2310 LVRVXMSBXMWSWMYCLAISJPDLMACODJAUXIBWMWFTYGPMRPISTG 2009-11-09 15:11:37.877670

As expected row 2315, which is in the deleted archive 66 is missing on target but the replication is restarted with row 2316 with is in archive 70

分享 转发
TOP
发新话题 回复该主题