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.
- connect internal/Oracle as sysdba
- 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")
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 loginThis is giving the following errors:
ORA-01503 create control file failed ORA-01158 database already mountedIf 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."
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" RESETLOGSCopy 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.
