I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

Both servers are Windows 2003 with Oracle Database 9.2.0.1.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

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.

1 comment

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy