Re: Database copying

From: Vikram Goel <vgoel_at_pts.mot.com>
Date: 1996/06/12
Message-ID: <4pn73c$edj_at_lserv1.paging.mot.com>


Rich,
try the following

The following steps outline the exact steps to rename a Oracle instance's name. This was prepared by a request from Duanne Piper.

WARNING: These steps must be followed exactly. Failure to follow steps, or

          do the backups as directed could result in partial or complete data 
          and or instance loss.

SEQUENTIAL STEPS: These steps must be followed in sequence.

  1. Shutdown normal the instance on which this process has to be applied.
  2. Make a full COLD backup of the instance, including datafiles, logfiles, archived logfiles, all copies of the control files, the init<sid>.ora, config<sid>.ora files, and the original crdb<sid>.sql and crdb2<sid>.sql files.
  3. Startup the instance in restricted session mode.
  4. Issue the following command from sqldba:

        "ALTER DATABASE BACKUP CONTROLFILE TO TRACE;" 5) A trace file will be produced in the location specified by the initialization

   parameter user_dump_dest. File will be a ora_nnnnn.trc file. Make a copy of this    file and call it new_ctl_file_<sid>.sql. 6) The file produced in step 5 should be edited to remove all comment lines, the

   'DATABASE' clause should be changed to 'SET DATABASE', the value of the    'SET DATABASE' clause should be changed to the new instance name. An example    is:

     "CREATE CONTROLFILE REUSE SET DATABASE <new_name> 
      NOARCHIVELOG
      MAXLOGFILES 32
      MAXLOGMEMBERS 2
      MAXDATAFILES 30
      MAXINSTANCES 8
      MAXLOGHISTORY 800
      LOGFILE
        GROUP 1 '/u01/oradb/PQTST/log1.dbf'  SIZE 4M,
        GROUP 2 '/u01/oradb/PQTST/log2.dbf'  SIZE 4M,
        GROUP 3 '/u01/oradb/PQTST/log3.dbf'  SIZE 4M
      DATAFILE
        '/u02/oradb/PQTST/system.dbf' SIZE 70M,
        '/u05/oradb/PQTST/rbs.dbf' SIZE 100M,
        '/u03/oradb/PQTST/temp.dbf' SIZE 100M,
        '/u04/oradb/PQTST/users1.dbf' SIZE 200M,
        '/u05/oradb/PQTST/users2.dbf' SIZE 200M,
        '/u02/oradb/PQTST/indexes1.dbf' SIZE 70M,
        '/u03/oradb/PQTST/indexes2.dbf' SIZE 70M;

Notes: At this time the values for the 'MAX..." parameters should not be changed.

7) Shutdown normal the instance.
8) Make a copy of all the instance control file, in a secure location.
9) Start up database using the following in sqldba: 

	"STARTUP NOMOUNT"

  1. Execute the file created in step 6, from sqldba. Ensure that spooling is on to a file.
  2. Issue the following command in sqldba:

        ALTER DATABASE OPEN RESETLOGS;"

  1. After the instance opens, shutdown normal the instance
  2. Take a complete COLD backup of all database files, as in step 2.
  3. Change the parameter 'DB_NAME' in the config<sid>.ora file to the new name of the instance
  4. Startup the instance normally.
  5. Issue the following command when instance is open: "ALTER DATABASE RENAME GLOBAL_NAME TO <new_name>;"
  6. Shutdown the database normal.
  7. Change the name of the init<old_sid>.ora and config<old_sid>.ora files to init<new_sid>.ora and config<new_sid>.ora respectively. Change the the parameter 'ifile' in file init<new_sid>.ora to point to the correct config<new_sid>.ora.
  8. Change the instance name in the 'oratab' , 'listener.ora', 'tnsnames.ora' files. In addition if there are any database links pointing to this instance from other instances, they should be changed at the instance they were created. Change the entry for the old name in all Oracle 'tnsnames.ora' file for all boxes.
  9. Congratulations, you have achieved the easiest way to rename a sid..
  10. Verify access and run a complete check of the newely named instance.

SPECIAL NOTES:

  1. If the purpose is to change the value of MAXDATAFILES, or other 'MAX...' parameters, follow the same steps, but change the required parameter value(s).
  2. In case Oracle returns a error message 'ORA-00213' size mismatch, do the following,
    • Move all copies of the control files to a hold location.
    • Reissue the create CONTROLFILE command.

NOTE: To make a backup on a new server, copy the datafiles etc to the correct

      locations, do the create of the control file on the original sid. edit and copy
      this file to the new server, follow the steps above.

--
Vikram Goel                                 Motorola email: vgoel_at_pts.mot.com
Sr. Oracle DBA - Consultant
Aerotek Inc.                                My email:  vgoel_at_emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426

In article <31BE18FF.32CA_at_macromedia.com>, Rich Headrick <rheadric_at_macromedia.com> writes:
>Anyone got an easy way of making a complete copy of a database, but
>having a different sid name
>
>Thanks,
>
>Rich
Received on Wed Jun 12 1996 - 00:00:00 CEST

Original text of this message