Re: Copying live to test database

From: Phil Hoggins <philh_at_sequent.com>
Date: 19 May 1998 08:31:55 GMT
Message-ID: <01bd82fe$c8973a20$d875549e@w-philh>

There is a quicker way to achive database copying but it is more technically involved.

1. From your live database issue the command; ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This will create an ascii version of the control file in the udump area.

2. Shutdown your live database.

3. Copy all the datafile, redo log files and control files to the dev location and rename them accordingly.

4. Edit the trace file created at 1. to look something like this. ( this example is for a database on raw volumes.). The name of the database needs to be changed to reflect the dev name and the names of all files need to be amended to point to the new names created at 3.

connect internal
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE connect internal STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE CAPPP RESETLOGS NOARCHIVELOG     MAXLOGFILES 32
    MAXLOGMEMBERS 4
    MAXDATAFILES 1000
    MAXINSTANCES 8
    MAXLOGHISTORY 400
LOGFILE

  GROUP 1 '/dev/vx/rdsk/rest/capp_rdo00'  SIZE 199M,
  GROUP 2 '/dev/vx/rdsk/rest/capp_rdo01'  SIZE 199M,
  GROUP 3 '/dev/vx/rdsk/rest/capp_rdo02'  SIZE 199M

DATAFILE
'/dev/vx/rdsk/rest/capp_sys00',
'/dev/vx/rdsk/rest/capp_rbs00',
'/dev/vx/rdsk/rest/capp_d01_00',
'/dev/vx/rdsk/rest/capp_d01_01',
'/dev/vx/rdsk/rest/capp_d02_00',
'/dev/vx/rdsk/rest/capp_d02_01',
'/dev/vx/rdsk/rest/capp_d03_00',
'/dev/vx/rdsk/rest/capp_d03_01',
'/dev/vx/rdsk/rest/capp_d04_00',
'/dev/vx/rdsk/rest/capp_d04_01',
'/dev/vx/rdsk/rest/capp_d05_00',
'/dev/vx/rdsk/rest/capp_d05_01',
'/dev/vx/rdsk/rest/capp_d06_00',
'/dev/vx/rdsk/rest/capp_d06_01',
'/dev/vx/rdsk/rest/capp_tmp00',
'/dev/vx/rdsk/rest/capp_tmp01',

;
# Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN RESETLOGS; 5. Run the above file from svrmgrl and bingo you have a development database the same as live in a fraction of the time for export import.

6. Restart the live database.

I have done this to recreate a 100Gb data warehouse in under an hour where export/import took a day.

MTaylo1987 <mtaylo1987_at_aol.com> wrote in article <1998051820021400.QAA12939_at_ladder01.news.aol.com>... > I would like a method whereby I can copy all the data in my Live database to
> the test one. Iam aware an export/import wo do the job but it is rather long

> winded.
> 
> Are there any other ways of achieving this.
> 
> Note The test database will not be in use at the time the copy occurrs.
> 

Received on Tue May 19 1998 - 03:31:55 CDT