Datacentre Support Reference Guides

Visit our online shop for ALL your parts, components, laptops, electronics and gadgets
 

Oracle: Renaming databases

 
 
 

Renaming/moving oracle databases can be done for any number of reasons. A couple of reasons are:

  1. Moving a database into production
  2. Duplicating a database for testing
  3. Duplicating a database for development

There are basically two ways to do this. First, a full database export/reimport, and second, alter database backup controlfile to trace.

According to Oracle DBA 101 (page 97-102), there are advantages and disadvantages to both methods.

Export/reimport:

  1. Can take a considerable amount of time depending on the amount of data.
  2. Can resize datafiles, recreate tables with different storage parameters, etc.

Alter database approach

  1. Will take significantly less time than the export/import.
  2. Can't resize datafiles or alter table storage parameters.

Database Export/Import Procedures

  1. Export the entire database (full=y)
  2. Create a new init.ora with a new database name.
  3. Create a new initial database containing at least a system, rollback, and temporary tablespaces.
  4. If you want to change the size or location of the datafiles, pre-create them. If you're importing the database on the same system from which it was exported and you don't precreate the datafiles, the import will fail. Hopefully, your original database won't be corrupted in the process.
  5. Import the export dump with ignore=y
  6. Run the catalog, catproc, and any other Oracle provided scripts as appropriate.

Alter database procedures

(Note: there's some things in the following that I'm not clear on; have to play around with it when I get a chance...)
  1. Generate the trace file to create the new control file using
    alter database backup controlfile to trace

  2. Edit the trace file.
    1. Remove or comment everything from line 1 to the line that states

      # The following commands will create a new control file and use it.

    2. Alter the line that states reuse database ${SID} to set database ${NEW_SID}
    3. Edit locations for the copied datafiles as appropriate.

  3. Backup the original database.
  4. Change the environment variables to point to the new instance.
  5. Copy/move the datafiles as appropriate.
  6. Run the trace file in Server Manager, connected as internal. (Huh?)
  7. Run the alter database rename global_name to ${NEW_DB}; command if your database is part of a global network.
  8. Rename the init.ora nad config.ora files as appropriate.
  9. Edit the configuration files that are instance name specific such as the oratab, listener.ora and tnsnames.ora.