I am trying to copy Oracle Database from my production server onto another server.
Both servers are Windows 2003 with Oracle Database 18.104.22.168.0. I have executed the following:
- Used the command "Alter database backup controlfile to trace" from my production database server;
- Edited the trace file and build "c1.sql" and "c2.sql" as attached; and
- From a cold backup, copied all the redo logs, control files and data files to a folder on the target server.
More on copying Oracle Database
How to recover after copying an Oracle Database
How to copy an Oracle Database
When doing a command prompt to connect to "sqlplus," I am unable to connect. I'm getting a TNS error despite manually modifying the tnsnames to input the new database. So I went into Database Configuration Assistant (DBCA) to manually create the new database, "trspr23." Only then was I able to log in to sqlplus as "sysadmin." However, when I do a "startup nomount" and execute CREATE CONTROLFILE I get the following error:
CREATE CONTROLFILE SET DATABASE "TRSPR23" RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01970: You must specify a database name for CREATE CONTROLFILE
So when I am copying a production database onto a new server with a new name, do I need to initially create the "newdb" on the new server? How can I surpass this error?
To copy Oracle Database to another server, the first thing to do is use the DBCA to create a database with this name on the server. Doing so will get the server ready for the database. This includes setting up the service in Windows to run this instance.
Once that database is running, do SHUTDOWN IMMEDIATE (leave the Windows service running) and then remove the files created for this database. Also remove any parameter file and password file in the %ORACLE_HOME%\database. Now, put your new parameter file and new password file in that same directory. The parameter file will point to the new control file locations.
Because you restored the control files, find them and rename them so that they do not get overwritten and so that the CREATE CONTROLFILE command will not fail because the files exist.
Then, perform startup nomount and explicitly denote the parameter file you want to use. Use something like the following:
STARTUP NOMOUNT PFILE='c:\directory\initorcl.ora';
I often find it beneficial to use a PFILE (which is text-based) until the database is up, in case I have to quickly modify a parameter and then convert to a SPFILE later. It is also a good idea to explicitly denote the PFILE here because this may be different from the one specified in the Windows service or the default one in ORACLE_HOME. To avoid any confusion, explicitly denote my parameter file here.
I didn't see anything wrong with the CREATE CONTROLFILE command. But strip out this line:
SET STANDBY TO MAXIMIZE PERFORMANCE
You don't have a standby database here, at least not yet.
In that script, also remove the following line:
RECOVER DATABASE USING BACKUP CONTROLFILE
Make sure the script contains only the CREATE CONTROLFILE command. The other two commands are simple enough to enter manually so as to ensure they are correct. Once you have successfully created the controlfile, you will need to issue this RECOVER command instead:
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
Then enter CANCEL right away when prompted for a redo log and open with resetlogs .
The other reason to put only the CREATE CONTROFILE command in that script is because this error could be due to a simple syntax error. Other commands in that file can make it hard to diagnose. I did not see an issue when looking at your CREATE CONTROFILE command, but you might have a comma or quote mark out of place, so double-check.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading