Dick, Thanks for this script. I will have to wait out for the Dev cluster to be ready to test it, though. Cheers Alan.- On Wed, Mar 23, 2011 at 11:15 AM, Goulet, Richard < Richard.Goulet@xxxxxxxxxxx> wrote: > Joel, > > The following works for me: > > declare > h1 NUMBER := 0; > h2 varchar2(1000); > ex boolean := TRUE; > fl number := 0; > link varchar2(100) := 'DBSRC.US'; > schema_exp varchar2(1000) := 'in(''<user1>'',''<user2>'')'; > schema varchar2(100) := 'refresh'; > blksz number := 0; > SUCCESS_WITH_INFO exception; > begin > utl_file.fgetattr('DATA_PUMP_DIR', schema||'.log', ex, fl, blksz); > if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',schema||'.log'); > end if; > h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => > 'SCHEMA', remote_link => link, job_name => upper(schema)||'_EXP', version => > 'COMPATIBLE'); > dbms_datapump.set_parallel(handle => h1, degree => 6); > dbms_datapump.add_file(handle => h1, filename => schema||'.log', > directory => 'DATA_PUMP_DIR', filetype => 3); > dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value > => 0); > dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', > value => 1); > dbms_datapump.set_parameter(handle => h1, name => > 'TABLE_EXISTS_ACTION', value=>'SKIP'); > dbms_datapump.metadata_filter(handle=>h1, > name=>'SCHEMA_EXPR',value=>schema_exp); > dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => > 0); > dbms_datapump.wait_for_job(handle=>h1, job_state=>h2); > exception > when SUCCESS_WITH_INFO THEN NULL; > when others then > h2 := sqlerrm; > if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0); > end if; > dbms_output.put_line(h2); > end; > > > Dick Goulet > Senior Oracle DBA/NA Team Leader > > > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Guillermo Alan Bort > *Sent:* Wednesday, March 23, 2011 10:04 AM > *To:* Joel.Patterson@xxxxxxxxxxx > *Cc:* deshpande.subodh@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx > > *Subject:* Re: How do you refresh your databases? > > Joel, > > I like your idea to move the tables to a special tablespace and use skip > tablespace. This is 11g, of course. > > IT Security won't approve the change if we have the data on QA at any > point, so we have to stop it from replicating. > > Data Masking would work as well, but I am not sure we can implement it > in such short notice in a 2TB database. > > This needs to be a repeatable process, we expect to refresh this data > every few months. > > Right now I am looking into datapump, but as this is 11.1 it's going to > be annoying at best. We are upgrading to 11.2 soon, so I will be able to > test this process more thoroughly and not in a prod environment (we are > building the 11.2 dev environment) > Alan.- > > > On Wed, Mar 23, 2011 at 8:47 AM, <Joel.Patterson@xxxxxxxxxxx> wrote: > >> The easiest in my opinion if possible is the RMAN duplicate, then just >> drop the tables after via script before changing passwords. I assume you >> change passwords from production if the data is that sensitive. >> >> >> >> An alternative as well is to alter table and move it to a tablespace that >> isolates all the tables, and then rman and skip that tablespace option >> solves the issue. >> >> >> >> Once you have done either option once, you have the tools to repeat it >> easily. >> >> >> >> Joel Patterson >> Database Administrator >> 904 727-2546 >> ------------------------------ >> >> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: >> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Subodh Deshpande >> *Sent:* Wednesday, March 23, 2011 12:30 AM >> *To:* cicciuxdba@xxxxxxxxx >> *Cc:* oracle-l-freelists >> *Subject:* Re: How do you refresh your databases? >> >> >> >> is this one time task or going to be periodic one.. >> >> database refresh rman duplicate is one way and it will copy entire so >> exclusion on particular object basis won't be possible >> >> >> >> schema refresh is one option..from the source prepare table list and >> prepare dmps with rows if structure of tables are same >> >> on the target empty thosr tables disable constraints, import the data and >> enable the constraints..if structures are not same those object will throw >> errors, after refresh, compare schems with toad kind of tool.. >> >> >> >> trasport tablespace is also an available option..and please check version >> specific errors or bug history on MOS >> >> >> >> thanks.. >> >> subodh >> >> On 23 March 2011 01:30, Guillermo Alan Bort <cicciuxdba@xxxxxxxxx> wrote: >> >> List, >> >> We have to refresh a QA database from production (to have some real >> data) but we need to exclude all the PCI/PII from the refresh (it absolutely >> cannot be copied from Prod to QA). In addition, we have GG replication on >> Prod so anything we do is subject to that. >> >> What would be your approach? >> >> My idea was rman duplicate, but those pci/pii tables rule this out. >> >> Oh, GG cannot be used due to firewall restrictions, we only have database >> ports and SSH from Prod to QA >> >> any ideas? >> Alan.- >> >> >> >> >> -- >> ============================== >> DO NOT FORGET TO SMILE TODAY >> ============================== >> > >