Re: Making a Working Copy of a Database

David,

You can try using the below.

The following steps outline the exact steps to rename a Oracle instance's name.

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.

I WOULD APPRECIATE FEEDBACKS. Vikram.

--
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 <dewing.51.0010452C_at_gasou.edu>, dewing_at_gasou.edu (David Ewing) writes:


>I would like to find the easiest way  to create a brand new


>database that is an exact copy of another, but with a different

>oracle sid.  We have the need to occassionaly copy our PREP

>database to a TEST database.  Currently, we create the new

>database with orainst, and then import in the PREP database.

>We always seem to have problems with referential integrity on

>many of the tables.  The alter table add constraint blows up on

>31 of the tables being imported.  

>

>I was wondering if there was a way to just copy the physical

>files that make up the database to another file system, and

>then bring up the new database pointed at these new files.

>This would be so much quicker.  Keep in mind, I do not want

>to disturb the original PREP database, but create TEST as

>an exact copy.  Thanks in advance.

>

>Oracle 7.1.4

>AIX 3.2.5  Rs6000-59H