Re: How do you refresh your databases?

  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_at_parexel.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Guillermo Alan Bort
> *Sent:* Wednesday, March 23, 2011 10:04 AM
> *To:* Joel.Patterson_at_crowley.com
> *Cc:* deshpande.subodh_at_gmail.com; oracle-l_at_freelists.org
>
> *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_at_crowley.com> 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_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Subodh Deshpande
>> *Sent:* Wednesday, March 23, 2011 12:30 AM
>> *To:* cicciuxdba_at_gmail.com
>> *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_at_gmail.com> 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
>> ==============================
>>
>
>

--
http://www.freelists.org/webpage/oracle-l