Well,
I have been playing with dbreplay, and trying to re-run a production workload. In order to capture a production workload, I don't have the luxury of bouncing the database. I start the capture, and do a restore of production that is "roughly right". A point in time recovery close to that point.
Well I've been fighting an issue for the last couple of days. I run a replay, and I find a lot of the my sessions are getting hung up, and I have sequences that have starting values BEFORE the value they were set to at the beginning. At first I thought the flashback somehow messed up my sequences, but now I know it is the replay.
Part of what the "prepare client" process does is this sql step
This step looks to see if the current value of the sequence is out of synch with the value when the capture was done.. If so it resynchs the sequences. Sweet eh ? but maybe not what you wanted.. This is what is actually being executed (when you run a trace).
As you can see it takes the sequences, alters it to subract on the next call, executes the nextval, then resets with an alter. Pretty slick, but very hard to find.
Thank goodness for Logminer to help point me in the right direction as to who was altering my sequence.
Here is some additional advice. To check to see what sequences have been "reset" look at the last_ddl time from dba_objects. Capture the list of what's changed, and then flashback and get the originall ddl.
My process is now prepare clients, reset sequences, then release the clients.
Search words. database replay sequences out of order reset
I have been playing with dbreplay, and trying to re-run a production workload. In order to capture a production workload, I don't have the luxury of bouncing the database. I start the capture, and do a restore of production that is "roughly right". A point in time recovery close to that point.
Well I've been fighting an issue for the last couple of days. I run a replay, and I find a lot of the my sessions are getting hung up, and I have sequences that have starting values BEFORE the value they were set to at the beginning. At first I thought the flashback somehow messed up my sequences, but now I know it is the replay.
Part of what the "prepare client" process does is this sql step
SELECT MAX(FIRST_VALUE), MAX(LAST_VALUE), MIN(FIRST_VALUE), MIN(LAST_VALUE), SEQ_BOW, SEQ_NAME, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE
FROM WRR$_REPLAY_SEQ_DATA R, DBA_SEQUENCES S
WHERE R.SEQ_BOW = S.SEQUENCE_OWNER
AND R.SEQ_NAME = S.SEQUENCE_NAME
GROUP BY SEQ_NAME ,SEQ_BNM, SEQ_BOW, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE
This step looks to see if the current value of the sequence is out of synch with the value when the capture was done.. If so it resynchs the sequences. Sweet eh ? but maybe not what you wanted.. This is what is actually being executed (when you run a trace).
SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" MAXVALUE 1E27 MINVALUE -1E26 INCREMENT BY -789390 NOCACHE
SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" INCREMENT BY 1 NOCACHE
As you can see it takes the sequences, alters it to subract on the next call, executes the nextval, then resets with an alter. Pretty slick, but very hard to find.
Thank goodness for Logminer to help point me in the right direction as to who was altering my sequence.
Here is some additional advice. To check to see what sequences have been "reset" look at the last_ddl time from dba_objects. Capture the list of what's changed, and then flashback and get the originall ddl.
My process is now prepare clients, reset sequences, then release the clients.
Search words. database replay sequences out of order reset
No comments:
Post a Comment