Data Refresh while 21 CFR is enabled

[CNC Guy]

Hi People,

Does anybody have any information on how to do a Data refresh from PROD to UA/PY/TEST enviroments while Auditing is enabled i.e. 21 CFR is anabled on PROD?

Basically we have around 10 table audited in PROD using 21 CFR. The problem is everytime we have to do a data refresh from PROD to PY we have to turn off auditing in PROD, do a PROD restart and then have the data refreshed (this is done by our DBA's and they do a schema level refresh). Once the data is refreshed we again enable Auditing and do another PROD restart.

We wish to avoid turning off and turning on of Audit since they required our PROD to be restarted everytime.

Is there a way we can refresh without turning it off since if we refresh without turning off it will copy the _ADT tables, the triggers, the views and A tables and this will create 1+1 (before and after) records in PY.

Please advise.

Thanks,
CNC Guy
E1 8.11
Oracle 10G
Solaris

 

[JEMILLER]

Assuming that you only have DB auditing running in production, what I suggest is that you execute the data refresh(via datapump, transportable tablespaces, etc.) as-is and then execute scripts to back-out the 21 CFR changes.

Taking F0101 as an example you would:

drop F0101 view
drop Audit-related columns from F0101_ADT
rename F0101_ADT to F0101
drop A0101

In essence you create a script to do exactly what the disable auditing application process does.

 

[CNC Guy]

Thanks for the advise Miller. But I think what you are referring to are post refresh tasks i.e. refresh the complete schema from PROD to CRP and then do these tasks i.e. drop F0101 view, drop Audit-related columns from F0101_ADT, rename F0101_ADT to F0101 and drop A0101.

But the problem is when the refresh is taking place wound't there me millions of records created in Audit tables in CRP (I assume it does not write in PROD) since at that time the tiggers are active on the table in PY. And would't that elongate the refresh process as well?

Basically we are looking for a medium where we copy selectively the non audit tables and then the audit tables wihout having to touch the 21 CFR setup.

 

[JEMILLER]

Sorry, I usually use transportable tablespaces to refresh environments which is an all or nothing approach. The site where I most often do this kind of refresh has a nice big and fast SAN so I am a little spoiled.

Please correct me if I have got your requirement wrong: You want to refresh CRP with PD transactional data and lose all the audit data generated by database auditing. (DB auditing is not enabled for PY, right?)

In your case, you would need to execute your refresh selectively excluding all Axxxx tables. Datapump can move a whole schema excluding objects. You will get some validation errors during the process as datapump will move F0101_ADT over (with triggers) but not A0101. This will cause the triggers to become invalid. It is not a big deal since you will be dropping the triggers.

So how about this for an updated procedure:

Refresh PY business data and control tables from PD using datapump while excluding all ten Axxxx audit tables

For each table that is audited in PD
drop Fxxx view
drop audit triggers from Fxxx_ADT
drop Audit-related columns from Fxxx_ADT
rename Fxxx_ADT to Fxxx

 

[CNC Guy]

Thanks again Justin but I think I still don't get the plot

Your understanding of the requirement is correct i.e. we need to copy all business data and control table schema (we are on Oracle 10G) from PROD (PRODDTA, PRODCTL) to CRP(CRPDTA,CRPCTL). This as of now is done by our DBA's and is done as a complete refresh. Hence we need to turn off CFR on PROD before we refresh and then turn back in after refresh.

What we want is since CFR isn't enabled in PY (We can do that if we want though to copy data), when we copy from PROD to CRP only the F0101 (for e.g) gets copied with its data and nothing else. Normally if we tell DBA to do a selective dump they will copy the F0101_ADT, the triggers, the F0101 view and A0101 table.

Now even if we tell them not to copy the A0101 table and the F0101 view (not sure if it is possible), the F0101_ADT and the triggers are still copied to CRP and when the data is being copied into CRP the triggers will be fired millions of times . isn't it?

I was looking at a old thread here and somebody said what can be done is copy selectively the non audit tables through database native tools and then use R98403 to copy the Audit tables. Is that possible? I've read on Oracle that even R98403 will not copy since the target (CRP) is non CFR aware !!

Gosh.. I am not sure if we can do it but I don't want to give up !!

 

[JEMILLER]

I get your point and I wasn't thinking clearly. Hit me with the question a couple of times and eventually the old brain will spit out all the answers.

You need to tell datapump to exclude the triggers by specifying EXCLUDE=TRIGGER. Without the trigger the audit data will not be generated during import.

If you want to get your head around everything that datapump can do take a look at this Oracle powerpoint:

http://www.tcoug.org/Archive/Winter2007/TCOUG_Data_Pump.ppt

 

[CNC Guy]

Thanks Justin. I can see some light coming through the dark tunnel now

So as I understand now using this Datapump we can specify 3 things while exporting i.e. EXCLUDE Triggers, EXCLUDE A Tables and EXCLUDE the FXXX views. this ways the export file will only have the FXXX_ADT tables. So once these are then imported to CRP (they will not create any audit records since the triggers aren't copied) the only thing post refresh we will need to do is Change the Structure of F0101_ADT back to F0101 and that's it. Is that so? Any other changes that we need to do? I believe we will need to restart CRP after changing this.

Also this task i.e. changing the strucure back to original F0101. Can this be done using a script. This looks like a tricky part since I am not too much into the DB stuff.

Also finally this Datapump, does it work with 10G? I will need to check with my DBA's what tool are they using.

You are the man of the moment Justin and I think I can get out of this with your help !!

 

[JEMILLER]

I think you have got it.

Data Pump definitely works with 10G but I know a lot of shops are still using import/export. Data Pump is similar to the SQL Server DTS or SSIS. Data Pump jobs run inside the database as opposed to an external program. If your DBAs are not familiar with Data Pump it sounds like now is the time for them to get familiar with it

As for renaming and restructuring (dropping columns) the F0101_ADT you can definitely do it with a script. Further you could create a database job that executes the Data Pump job and then calls a stored procedure to do the table rename and drop columns.

I would agree that bouncing the services would be the way to go. Actually you really should execute this process with the PY services (and JAS instance if you are using web clients) down. Of course I am assuming that you have a separate set of services or a separate server handling non-production/PY.

 

[CNC Guy]

Yes I too think I've got it now. Just talked to my DBA's and guess what. they are indeed using Datapump for export/ import so some respite !!

But I guess I will need to test this somehow before performing a full refresh. Is there a way? Can we do an INCLUDE instead of EXCLUDE?

Also this EXCLUDE TRIGGER. Is it for all Triggers or do we have to supply the Trigger list to the DBA's. Basically I am trying to figure out what all information I need to give to the DBA's. I am sure you can help. (I hope I am not getting greedy here

)

Also this change of Structure. I guess we will need to write a couple of ALTER TABLE statements (I think all that has to be done is drop that additional column and change the name back to A0101. Is that it )

Thanks once again Justin. Looks like the shore is near !!

 

[brother_of_karamazov]

I spent some time trying to make my normal, push-button refresh process work with CFR.

The closest I got was to execute the refresh using a backup/restore method. Drop the triggers related to CFR that came over with the Prod database that refer to PRODDTA schemas/owners. Recreate them from a hard-coded sql statement. Drop the views elated to CFR that came over with the Prod database that refer to PRODDTA schemas/owners. Recreate them with a hard-coded statement.

Do the other refresh stuff.


I never liked having the hard-coded triggers and views since it means that if a CFR table audit is added someone has to remember to modify the trigger and view creation. Since my refresh design goals always include extensibility without code modification, I was not happy with this.

If someone can figure out how to write the SQL code to go through the text of the views and the triggers to replace PRODDTA to CRPDTA it could be automated. Until then, I (and I suppose others) are stuck with changing the triggers and views associate with CFR via hard-coded script.


A note: it is important that if you utilize the backup/restore method of refresh you either change the triggers or disable them. Otherwise, there is the possibility that you are triggering events from your JDE_CRP database into your JDE_PRODUCTION database.

If anyone wants to see the code I use to drop and re-create the triggers and views, let me know and I will post it on my blog.


[ QUOTE ]
Hi People,

Does anybody have any information on how to do a Data refresh from PROD to UA/PY/TEST enviroments while Auditing is enabled i.e. 21 CFR is anabled on PROD?

Basically we have around 10 table audited in PROD using 21 CFR. The problem is everytime we have to do a data refresh from PROD to PY we have to turn off auditing in PROD, do a PROD restart and then have the data refreshed (this is done by our DBA's and they do a schema level refresh). Once the data is refreshed we again enable Auditing and do another PROD restart.

We wish to avoid turning off and turning on of Audit since they required our PROD to be restarted everytime.

Is there a way we can refresh without turning it off since if we refresh without turning off it will copy the _ADT tables, the triggers, the views and A tables and this will create 1+1 (before and after) records in PY.

Please advise.

Thanks,
CNC Guy
E1 8.11
Oracle 10G
Solaris

[/ QUOTE ]

 

[Larry_Jones]

Jeff,

what database and version are you referencing? Since you mention backup/restore I tend to think you are talking iSeries/DB2 versus the OP's Oracle 10G database.

 

[brother_of_karamazov]

I am referring to SQL but the concept is the same- Auditing creates triggers and views that have PRODDTA embedded in the trigger/view. If one uses anything other than R98403 (auditing aware BTW) these views and triggers are copied to the refreshed database and still refer to Production as you are aware.


Options:

1- Use R98403 as it is CFR-aware

2- Create a script to run through the database, finding triggers and views related to CFR and remove them, set the table names back, etc.

3- Disable all triggers and remove all views after refresh, set tables names back

4- Manually drop/recreate the triggers and views with CRPDTA after the refresh

5- Create (and maintain) identical CFR auditing in PY and disable/enable after refresh.

6- Drink beer and wait for your stimulus money


[ QUOTE ]
Jeff,

what database and version are you referencing? Since you mention backup/restore I tend to think you are talking iSeries/DB2 versus the OP's Oracle 10G database.

[/ QUOTE ]

 

[Larry_Jones]

Re your latest questions:

1. You can do an INCLUDE but its an exclusive include. Only the specified Object Type(s) and optionally specific objects would be included in the Export/Import.

2. Both the INCLUDE and EXCLUDE parameters allow inclusion/exclusion based on Object Type. Hence EXCLUDE=TRIGGER excludes all triggers. Warning: impdb has a "Feature" that if no objects of the type specified in the exclude cause are in the export dump file then the import will cancel / not run.

3. This doesn't really help you since you are 10G but 11G has the ability to rename tables as part of the import process :-( Without 11G you and Justin are correct - followup SQL will be needed to rename table, drop column, etc.

Another feature of Oracle's datapump is the ability to filter data imported using the QUERY parameter, 2 very good candidates for this are the F00165 table and the F0911 table. In F00165 you should eliminate the embedded OLE objects in the test/crp environment because the F00165 entries are links to imported attached objects. If a test/crp user deletes these media objects then the production OLE objects would be deleted. So we make sure these entries are removed from F00165.

In datapump the parameter looks like this:
QUERY="CRPDTA.F00165:WHERE GDQUNAM <> 'OLEQUE'"
QUERY="CRPDTA.F0911:WHERE GLFY >= 8"

Good Luck

 

[CNC Guy]

Thanks Larry,Jeff.

I think I again went back a bit by your comments though. It doesn't sound like a piece of cake as I thought it would

The problem is in our setup the DBA's work independently than the CNC's and hence we need to co-ordinate very properly to avoid any issues. The plan I can make at this time is this. Please let me know the pros and cons of this.

Objective : Refresh the schemas CRPDTA, CRPCTL from PRODDTA,PRODCTL

Steps:
1) Inform the DBAs about the list of Axxx tables, Fxxx Views( the ones corresponding to the _ADT tables) and the Triggers on the _ADT tables. As per them they would EXCLUDE these objects while exporting using DATAPUMP to a file.

2) After exporting, the dump file would only have all the business data tables (of course the _ADT tables are there but minus the triggers, A tables and Views).

3) They would them import this file to CRPDTA, CRPCTL

4) Post refresh what we would have in CRP would be all good data and no triggers or audit tables or views.

5) The only thing we would need to do is write an ALTER TABLE script to drop the columns from _ADT tables and change the name back to normal for e.g. F0101_ADT to F0101.

5) Refresh the PY JDE / JAS services.

Does this sound like a good plan or are there any issues?

@Jeff- Also please elaborate on the Filter of data on F00165 and F0911. I get the point on F000165 but what about F0911?

@Justin -> Awaiting your views on the above plan as well.

Thanks guys.

 

[Larry_Jones]

Sounds good,

the reason for filtering data on F0911 (and F0902, F42199, etc) is just to reduce the amount of data transferred from production to CRP. CRP/TEST shouldn't need historical data more than 'X' years old, and its expensive in terms of time, CPU and disk to carry all that old data into a test environment. Just restricting F0911 and F0902 to the last full fiscal year + current year saves us gigabytes and time. Unless you have a need to look at historical data from 5 years ago why include it?

P.S. - the name is Larry not Jeff

 

[brother_of_karamazov]

It's easy to keep straight - Larry is the intelligent one.

[ QUOTE ]
P.S. - the name is Larry not Jeff

[/ QUOTE ]

 

[CNC Guy]

Thanks Larry (Apologies for incorrectly addressing you). I think addressing multiple people together has its share of confusions

Anyway, so you think the plan's good and will work. The only thing I am still not sure is as you said whether the datapump works on all or none concept/ Whether if we give an EXLUDE for Triggers it will exclude al triggers on the complete schema. What we want is to only exclude the triggers on the _ADT tables plus the A tables and the views (so we wish to specifically exclude objects and not complete object types). Is that possible?

 

[Larry_Jones]

 

[Larry_Jones]

Yes you can specify specific objects or even use wild card character.
EXCLUDE=TABLE:"IN ('F0901','F0902','F0911')"
EXCLUDE=TRIGGER:"LIKE 'F0101%'"

A data pump parameter file can have multiple EXCLUDE/INCLUDE parameter lines like the above example. So if you have a lot of triggers to exclude you have several ways to do it.

 

[CNC Guy]

Thanks mate. I think we are good to go for now as per the previous plan. As of now we will not think of restricting data in the tables. just hope this new plan works successfully and then may be can revisit this restriction of data (BTW, this is a very good point since this would considerably reduce the data size since F0911 is the biggest table).

Also can you suggest on the Alter table script that we will need to run in CRP post refresh. I am not a Oracle guy so this may be a weird query

but is all we need to do is DROP column and can this be done with data in that table)

Thanks once again for your help Larry. Appreciate your assistance.