Home > Ask the Oracle Database / Applications Experts > Questions & Answers > Cloning a production database into a development server
Ask The Oracle Expert: Questions & Answers
EMAIL THIS

Cloning a production database into a development server

Maria Anderson EXPERT RESPONSE FROM: Maria Anderson

Pose a Question
Other Oracle Categories
Meet all Oracle Experts
Become an Expert for this site


Oracle tips, scripts, and expert advice
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


>
QUESTION POSED ON: 18 October 2005
I want to clone my production database into my development server. Both are the same Oracle version but different OS versions (production on Windows NT and development on a Windows 2003 server). I have given the following commands (on the production command prompt -- is it right, or do I have to run the following commands in the development environment after copying all the data and redo log files? Please answer this first.):
  1. connect internal/Oracle as sysdba
  2. alter database backup controlfile to trace; (So the trace file has been generated in production in the path defined by init.ora. I copied this trace file in my new development server's "c:oracleadminskbdbsvrudump")
I do not have any database on my development server, only the Oracle server installed.

Now I have created a new instance of SKBDBSVR and edited all the environment settings. Then, I issued the following command:

@c:oracleadminskbdbsvrudumpora00355.sql   after connecting internal
login
This is giving the following errors:
ORA-01503 create control file failed
ORA-01158 database already mounted
If you want to see the create ctrl files command, it is given below. My ctrl.sql file:
CREATE CONTROLFILE REUSE DATABASE "PRODUCT1" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 4
    MAXDATAFILES 254
    MAXINSTANCES 1
    MAXLOGHISTORY 453
LOGFILE
  GROUP 1 (
    'D:ORACLEORADATAPRODUCT1REDO01.LOG',
    'D:ORACLEORADATAPRODUCT1REDO03.LOG'
  ) SIZE 2M,
  GROUP 2 (
    'C:ORACLEORADATAPRODUCT1REDO02.LOG',
    'C:ORACLEORADATAPRODUCT1REDO04.LOG'
  ) SIZE 2M
DATAFILE
  'D:ORACLEORADATAPRODUCT1SYSTEM01.DBF',
  'D:ORACLEORADATAPRODUCT1RBS01.DBF',
  'D:ORACLEORADATAPRODUCT1USERS01.DBF',
  'D:ORACLEORADATAPRODUCT1TEMP01.DBF',
  'C:ORACLEORADATAPRODUCT1INDX01.DBF',
  'D:ORACLEORADATAPRODUCT1DR01.DBF',
  'D:ORACLEORADATAPRODUCT1PRODUCT1_STATIC.DBF',
  'D:ORACLEORADATAPRODUCT1PRODUCT1_DYNAMIC.DBF',
  'D:ORACLEORADATAPRODUCT1PRODUCT1_HIGH_DYNAMIC.DBF'
CHARACTER SET WE8ISO8859P1
;
Then I changed the database name as "SKBDBSVR" in place of "Product1."


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google



RELATED CONTENT
Oracle error messages
Why am I receiving Oracle memory allocation errors?
Oracle tutorial library: SearchOracle.com's learning guides
Oracle error 6550 may mean incorrect Oracle export version
Why does the archive log in Oracle give me the ORA-16032 error?
Are we getting the ORA-00382 error because we're at maximum block size?
How do I solve the ORA-00257 error in Oracle?
Solving common Oracle errors guide
Error during RMAN backup
ORA-12560 error with Oracle 10g Instant Client
Unable to view Oracle tables in NetBeans

Oracle database installation, upgrades and patches
Oracle's Java database continues push into embedded database market
How to use the Oracle Database Upgrade Assistant (DBUA)
Oracle delivers database fixes in Critical Patch Update
How to get the most out of Toad for Oracle 10
Coca-Cola Bottling swaps out Oracle for DB2
Oracle renews push into embedded open source software market
Oracle releases new database, says 11g upgrade will cut costs
Comparing servers for Oracle database 11g upgrades
Choosing the right server hardware is all about choosing the right software
The best of the Oracle 11g-ready servers

Oracle database replication guide
Setting up backups with data replication
Merging records in Access and Oracle
Cloning database with hot backup
Setting up multi-master replication in Oracle 10g
Partial database replication setup
Creating report from snapshots
Steps for database replication
Replication using LogMiner
Working with materialized views
Create a database clone using an open backup

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
10g  (SearchOracle.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


I will assume that you are trying to do this from either an offline (cold) backup of your production system or that your production system is down while you are copying the datafiles across to the development server. You can do this using an online backup or RMAN but I don't think that's the case here.

At a high level, the procedure looks mostly correct except there are a few key steps missing.

On the development server, create the admin directories (bdump, udump, cdump, etc.) and the directories where the datafiles will reside.

Log in to the production database and type in 'alter database backup controlfile to trace;'. You will want to copy that file over to the development server and edit it appropriately. Change the database name, as well as the datafile information. Also, change the first line to read:

CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS
Copy over the production datafiles (either from a cold backup or after shutting down production) but don't bother copying over any tempfiles, redo logs or controlfiles.

Log in to the development server, and set your environment appropriately. If you are doing this on a Windows operating system, create the service for the new database.

Start SQL*Plus and perform the following steps:

connect sys as sysdba
startup nomount;
@createcontrolfilescript.sql
alter database open resetlogs;
Don't forget to add any tempfiles to the TEMP tablespace since these would not have been copied over. Also, if you will be using RMAN to perform backups on this database, change the DBID as your development database will now have the same DBID as production.

Finally, if you are using a version older than Oracle 8.1.5 and continue to see the errors above, there is a reported bug that may require you to restart the service and then continue with creating the controlfiles.




Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice



Oracle White Papers: Fusion Middleware
HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2003 - 2009, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts