DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: error making Control File after cold copy database between servers

  1. #1
    Join Date
    Dec 2014
    Posts
    3

    error making Control File after cold copy database between servers

    Hi,

    I just found this forum when trying to migrate a database between servers. I have gotten stuck on creating a control file, which led me to this forum.

    Some environment info:
    Windows 2008 (64b) to Windows 2008 R2 (64b). Oracle 11.1.0.7 to Oracle 11.2.0.4.

    What I have done so far:

    used shutdown immediate to stop old 11.1.0.7 database
    copied \oradata\\* to backup folder

    Installed 11.2.0.4 on a new virtual machine
    created a database with the same structure

    stopped that database and copied its contents out to another folder
    copied the contents from old database to same location on new server

    Logged in to sqlplus as sysadmin
    tried to startup the database
    It seemed to be starting up, but eventually returned that it could not find the control file

    I was looking for a solution on google and came to a post here:
    http://www.dbasupport.com/forums/sho...-control-files

    I followed the instructions up to the point where you are supposed to execute your ctl.sql file:
    (On the instructions: SQL> @E:\oracle\admin\ORCL\udump\ctl.sql) (yep, I changed to reflect my own directory locations)

    When I got to this, my error was "error SP2-0023: String not found". One more thing to note, I did not have a "udump" folder, so I created on in windows at the same location described by the guide. Also I checked the file locations and names in the directory and compared against how I have them in ctl.sql for case sensitivity and believe they are all correct there. I have not had much luck finding what I am doing wrong today, so I would appreciate any advice!

    Thank you!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If you are migrating a database to another server using a cold backup of the files, you don't need to create a database first. In Windows the oradim.exe command can be used to create database services without running dbca. You need to copy over the init file from the current database. If you use an spfile, you should create a pfile from the spfile. A pfile is an text file that has all of the parameters that a database needs in order to run, and spfile is a binary version of the pfile. You want to make sure that you have all of the current information in the pfile before you migrate it over.

    CREATE spfile FROM pfile;

    Once you move over the right pfile, you want to make sure that the instance you created will use the pfile you copied over. You can use oradim help=y to get the syntax. I haven't worked in Windows for awhile to verify that the syntax is correct. You should also make sure that all of the directories referenced in the pfile are valid and check to see if you have all of your control files. Generally you have two or three control files listed on the same line as control_files. If you are missing one you should be able to copy it from a different location. If a database uses multiple control files, all of the control files need to be the same.

    oradim -startup -sid mysid -starttype SRVC,INST -pfile C:\oracle\product\11.2.0.4\database\init.ora

    https://docs.oracle.com/html/B13831_01/admin.htm

    Since you used a cold backup, you should not need redo. If the database wants redo you should verify that you have the right control files from the other server and that they are all the same. When you start the database you want to startup upgrade, since the database will have an outdated data dictionary compared with the version of Oracle installed. So you will need to manually upgrade the database. Also if the old Oracle version is 32 bit and the new Oracle version is 64 bit you will need to run the script to make the database 64 bit. All the scripts you need should be in %ORACLE_HOME%\rdbms\admin. You just need to find out which scripts to run. Take a look at this document and see if you have what you need.

    https://docs.oracle.com/cd/E11882_01...4.htm#BABBFJGJ

  3. #3
    Join Date
    Dec 2014
    Posts
    3
    Gandolf989,

    Thank you very much for this! I will try this today.

  4. #4
    Join Date
    Dec 2014
    Posts
    3
    I was able to create the SPFile. I moved the file to oracle\product\11.1.0\db_1\database. (It's 11.2.0.4, I just kept the same name as before)
    I entered the command oradim -new -sid sid -srvc OracleService(sid) -syspwd (system password) -startmode auto srvcstart system -pfile (directory to spfile) -spfile

    It returned back "Instance Created" and I do see it as a service now. I am a bit confused about the spfile/pfile stuff... I get what they are, just not sure if I am using them correctly. Also, I am not sure how to connect to this database. I have a service that was created (I think) with a text file showing the parameters of the old database, the old database files live in the same location on the new server as the old server, and the service is running, but the (sid) folder does not show a lock on it in my new server.

    I'm definitely missing something. Gonna grab some lunch and then right back into the fight. Thanks again for all advice!

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    When you install Oracle into a directory there are files in that directory that are hard coded to the path where it was installed. Since applying a major patch such as going from 11.2.0.3 to 11.2.0.4 involves doing a new install to a new directory you should have installed Oracle as C:\oracle\product\11.2.0.4. I don't create Oracle homes under a db_1, or db_2 sub directory, although it should not matter. you should make sure that the version of Oracle that you are running from is the right version. If you run the cmd prompt with admin privileges and navigate to the %ORACLE_HOME%\OPatch directory you can then run opatch lsinventory to get the current version of Oracle. You will probably need to set ORACLE_HOME, LD_LIBRARY_PATH and have the %ORACLE_HOME%\bin directory at the beginning of the path.

    Make sure you keep checking the diag trace directory to look at the alert log. The alert log has information that will help you to get this resolved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width