RE: Testing Refresh Procedure

From: joshc <collier_jw_at_comcast.net>
Date: Tue, 21 Mar 2006 10:33:00 -0800
Message-Id: <20060321183302.CEC552BE1D3@turing.freelists.org>

Kelley,

Use RMAN to do your duplications, it's incredibly easy. Especially if you set the init parameter db_file_name_convert. Here is an example on one of my dev databases.

alter system set db_file_name_convert = '/u01/oradata/dbprod/','/u01/oradata/dbdup/dup_','/u02/oradata/dbprod/','/u02/or adata/dbdup/dup_','/u03/oradata/dbprod/','/u03/oradata/dbdup/dup_' scope=spfile;

You can script the whole thing and it has the added bonus of exercising your RMAN backup sets, which helps validate your recovery plan. I have three files in my duplication process 1. shell script to control process (this script runs on the development server)

    . /home/oracle/environs/dbdup.env
    rm -rf /u03/oradata/dbdup/*
    rm -rf /u02/oradata/dbdup/*
    rm -rf /u01/oradata/dbdup/*

    sqlplus /nolog <<_EOF_
    connect / as sysdba
    shutdown abort;
    startup nomount;
    _EOF_
    rman rcvcat uid/pwd_at_rmancat target sys_at_dbprod Auxiliary / cmdfile

/home/oracle/scripts/duplicate_dbprodf2dbdup.rcv    msglog
/home/oracle/scripts/logs/rmanlog_dup_dbdev.log
    sqlplus /nolog @post_dup_dbdev.sql >
/home/oracle/scripts/logs/post_dup_dbdev.log

2. The RCV file that stores the RMAN commands for the duplication. This will duplicate production to a point in time (yesterday at noon).

     run {
    allocate auxiliary channel ch1 type disk;     allocate auxiliary channel ch2 type disk;     duplicate target database to dbdev until time="(SYSDATE-1)+(12/24)"     LOGFILE
  GROUP 1 (
'/u02/oradata/dbdev/REDO01A.DBF',
'/u03/oradata/dbdev/REDO01B.DBF'

  ) SIZE 1M,
  GROUP 2 (
'/u02/oradata/dbdev/REDO02A.DBF',
'/u03/oradata/dbdev/REDO02B.DBF'

  ) SIZE 1M,
  GROUP 3 (
'/u02/oradata/dbdev/REDO03A.DBF',
'/u03/oradata/dbdev/REDO03B.DBF'

  ) SIZE 1M,
  GROUP 4 (
'/u02/oradata/dbdev/REDO04A.DBF',
'/u03/oradata/dbdev/REDO04B.DBF'

  ) SIZE 1M,
  GROUP 5 (
'/u02/oradata/dbdev/REDO05A.DBF',
'/u03/oradata/dbdev/REDO05B.DBF'

  ) SIZE 1M,
  GROUP 6 (
'/u02/oradata/dbdev/REDO06A.DBF',
'/u03/oradata/dbdev/REDO06B.DBF'

  ) SIZE 1M;     }
3. A post duplication script that changes passwords and anything else you may want, such as dropping database links to other production databases, renaming the global name, creating QA users, populating the temp space with a datafile.

I use NFS to share the location of my RMAN backupsets with my development database and server.

Josh C.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Coleman, Kelley (HAC)
Sent: Tuesday, March 21, 2006 7:59 AM
To: oracle-l_at_freelists.org
Subject: Testing Refresh Procedure

Does anyone have a standard procedure for doing db refreshes that they'd be willing to share? I'd be going from Production down to test and development dbs. These were usually done by a co-worker who recently moved on to greener pastures. He didn't leave any desk procedures, so I've been winging it, but it seems like I'm making it harder than it needs to be.

Should I drop relevant schemas before importing, so it's basically from scratch?

I know the pastures guy would essentially do two imports, one just of table structures - no rows - then one just of data. Does that seem like a sound procedure?

Kelley Coleman
Database Administrator
VA Health Administration Center
Denver, Colorado
303-331-7521-o

Confidentiality Note: This e-mail is intended only for the person or entity to which it is addressed, and may contain information that is privileged, confidential, or otherwise protected from disclosure. Dissemination, distribution, or copying of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this e-mail in error, please notify the sender by reply e-mail, phone, or fax, and destroy the original message and all copies. Thank you

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

Received on Tue Mar 21 2006 - 12:33:00 CST