on 05-17-2012 3:41 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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...?
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
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.