cancel
Showing results for 
Search instead for 
Did you mean: 

Is it safe to cancel a brspace online reorg - and how do you check progress?

Former Member
0 Kudos

Hi

We are doing some online reorgs and LONG to LOB conversions with the following commands:

brspace -u / -f tbreorg -a long2lob -t EDI40 -e 8

brspace -f tbreorg -t 'EDI40' -NBR -e 8

brconnect -u / -c -f stats -t EDI40 -f collect -e 8

(I don't think a separate tbreorg is required after long2lob conversion actually, it's already done it).

When we come to do this in Production there is the risk that we will run outside the maintenance window. We'd be doing an 'online' reorg but SAP would be down, however, once the maintenance window is complete, we would start SAP up again which may 1) cause problems with the reorg or 2) cause performance problems.


What I want to know is, how do you cancel an online reorg (can you just hit control c? What if the session has timed out and it's running in the background), and is it safe to do so?

Is there any way to see how the reorg is progressing / to estimate how much time there is left until completion?

Thanks

Ross

Accepted Solutions (1)

Accepted Solutions (1)

jairo_pedroza
Explorer
0 Kudos

Hello Ross,

When you convert long 2 lob, it is already reorganized !

In my experience, you can stop reorg online with below: It is safe ! But I recommend to reorganize tables during low production utilization.

stop => stops reorganization

This option enables a "clean" premature termination of the reorganization without all tables being processed. However, the reorganization of individual tables is not cancelled in this case. Instead, the process is terminated before the next table is processed. Therefore this action can take a long time.

646681 - Reorganizing tables with BRSPACE

541538 - FAQ: Reorganization

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

To check progress, I use view GV$SESSION_LONGOPS . See below script

SELECT NULL INST, NULL SID, NULL OPNAME, NULL TARGET, NULL START_TIME, NULL SOFAR, NULL TOTALWORK,

  NULL ELAPSED_S, NULL REMAINING_S FROM DUAL WHERE 1 = 0

UNION ALL (

SELECT NULL INST, NULL SID, NULL OPNAME, NULL TARGET, NULL START_TIME, NULL SOFAR, NULL TOTALWORK,

  NULL ELAPSED_S, NULL REMAINING_S FROM DUAL WHERE 1 = 0

) UNION ALL ( SELECT * FROM (

WITH BASIS_INFO AS

( SELECT

    DECODE(INSTANCE_NUMBER, -1, USERENV('INSTANCE'), INSTANCE_NUMBER) INSTANCE_NUMBER,

    SESSION_ID,

    ONLY_ACTIVE,

    MIN_START_TIME

  FROM

  ( SELECT

      -1 INSTANCE_NUMBER,        /* -1 for current instance, -2 for all instances */

      -1 SESSION_ID,

      'X' ONLY_ACTIVE,

      TO_DATE('01.01.1000 01:00:00', 'dd.mm.yyyy hh24:mi:ss') MIN_START_TIME

    FROM

      DUAL

  )

)

SELECT

  SLO.INST_ID INST,

  SLO.SID,

  SLO.OPNAME,

  SLO.TARGET,

  TO_CHAR(SLO.START_TIME, 'dd.mm.yyyy hh24:mi:ss') START_TIME,

  TO_CHAR(SLO.SOFAR, 99999990) SOFAR,

  TO_CHAR(SLO.TOTALWORK, 999999999990) TOTALWORK,

  TO_CHAR(SLO.ELAPSED_SECONDS, 99999990) ELAPSED_S,

  TO_CHAR(SLO.TIME_REMAINING, 9999999990) REMAINING_S

FROM

  BASIS_INFO BI,

  GV$SESSION_LONGOPS SLO

WHERE

  ( BI.SESSION_ID = -1 OR BI.SESSION_ID = SLO.SID ) AND

  ( BI.INSTANCE_NUMBER = -2 OR BI.INSTANCE_NUMBER = SLO.INST_ID ) AND

  ( BI.ONLY_ACTIVE = ' ' OR SLO.SOFAR < SLO.TOTALWORK ) AND

  SLO.START_TIME >= BI.MIN_START_TIME

));

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

Hope it help you,

Jairo Pedroza

Former Member
0 Kudos

Hi Jairo

Many thanks for the detailed and prompt reply, I shall try the GV$SESSION_LONGOPS view later on today.

However, as regarding stopping/cancelling a reorg, I am only doing one table at a time. Some of these tables are massive (2TB!). I need to know if it is safe to cancel a reorg on one table that is at some unknown stage; if I were to cancel will it just roll back safely?

I see there is a "cleanup" option for tbreorg after failure/cancellation so assume if we were to cancel in this way we'd need to run this command too. But I cannot find any documentation on actually cancelling a table reorg half way through!

One other question - a test reorg was running last night but hadn't finished by the time I had to leave. I simply killed my putty session and this morning have checked the log - it had actually carried on and successfully completed the reorg BUT then complained as it couldn't read from standard input:

BR0280I BRSPACE time stamp: 2012-05-17 18.25.28

BR1102I Number of tables reorganized successfully: 1

BR1142I Tables with the longest duration of reorganization for owner SAPS1P

  Pos. Owner    Table                                 Rows  Duration

                                                              [m:s]

    1  SAPS1P   RFBLG                             10153700    95:54

BR0280I BRSPACE time stamp: 2012-05-17 18.25.28

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

BR0280I BRSPACE time stamp: 2012-05-17 18.25.28

BR0255E Cannot read from standard input

BR0280I BRSPACE time stamp: 2012-05-17 18.25.28

BR0700E Fatal errors occurred - terminating processing...

BR1022I Number of tables processed: 1

BR1004E BRSPACE function 'tbreorg' failed

BR1008I End of BRSPACE processing: seioiuje.tbr 2012-05-17 18.25.28

BR0280I BRSPACE time stamp: 2012-05-17 18.25.28

BR1007I BRSPACE terminated with errors

So has it actually failed / rolled back?

Is there any way to run this in the background so it doesn't need input / can leave running (if I do this work from home there is an issue whereby putty times out, so would be very useful)?

Many thanks


Ross

Former Member
0 Kudos

Ok that had actually worked and done the long to lob reorg anyway, despite the error message!

I found how to do in background anyway, -c force option works well:

nohup brspace -u / -c force -f tbreorg -a long2lob -t ZWNCMOMIGR -e 8 &

However, I ran the script above and it always returns no rows...?

Former Member
0 Kudos

Sorry, I'm impatient - the script IS working now, it just didn't show anything for the first hour of the reorg, but now I'm getting a list of the 16 processes in action... cheers!

jairo_pedroza
Explorer
0 Kudos

Hello Ross,

For tables with more than 1TB, usually I create a new tablespace and start reorganization with new target tablespace.

Nice to heard everything is going well.

Please change this question to answered.

Cheers,

Jairo Pedroza

Former Member
0 Kudos

Thanks Jairo.

It's still not answered though in terms of can I cancel a reorg halfway through a table/is it safe to do...

Cheers

Ross

jairo_pedroza
Explorer
0 Kudos

I got it !!!

During online reorg, brspace will create shadow table. When you stop with cntl+c or the database stopped unexpected, reorg process will rollback automatic. If you identified dirty table in there, you can use cleanup process. In rare cases I needed to use..

It is safe and work properly! I recommend you validate and simulate all procedures in Quality Assurance System, before go to Production System. Even small tables.

Cheers,

Jairo Pedroza

Former Member
0 Kudos

Excellent

Thanks you very much! Much appreciated!!

Answers (0)