Q
Problem solve Get help with specific problems with your technologies, process and projects.

Cloning a production database into a development server

I want to clone my production database into my development server. Both are the same Oracle version but different OS versions. Now I have created a new instance of SKBDBSVR and edited all the environment settings. I'm receiving errors.

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."
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.

This was last published in October 2005

Dig Deeper on Oracle database installation, upgrades and patches

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close