RE: How do you refresh your databases?

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Wed, 23 Mar 2011 10:15:17 -0400
Message-ID: <6B0D50B70F12BD41B5A67F14F5AA887F0F4C1EE7_at_us-bos-mx022.na.pxl.int>



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
Received on Wed Mar 23 2011 - 09:15:17 CDT

Original text of this message