Copying Oracle Databases


Copying databases in Oracle can be done in a number of ways. The method which will be discussed in this document is that of copying and Oracle instance database directory without the use of the export and import command functions, specifically in Oracle 7.34 on NT 4.0.

  1. Using exp?? and imp?? to export and import a database from one database to another. The export and import command utilities should not require anything further than themselves.

  2. Install Oracle and copy a the database subdirectory from another Oracle installation into the new installation. This option does not utilise the export and import command utilities. This is the option that this document will discuss. In general one would only use this option when not other is available. This option is complex and involves changes to the Oracle instance and the database files in the newly installed installation.

A Clean Machine

This involves ensuring that the new installation is a clean machine, ie. devoid of all aspects of the Oracle database prior to installation. Obviously this only applies if the machine previously had an Oracle installation and for some reason that installation was removed. It can sometimes be required to completely remove Oracle from a machine due to serious problems. Complete removal of Oracle is an extreme solution. If this stage has been reached then call Oracle support first since there may always be a much easier solution. If you can not solve the problem a single or a number of support calls could get you a simple solution without the drastic step of complete reinstallation.

How to Clean the Machine for Oracle7

The intention in this case is to provide a devoid of Oracle software. This is how to remove all traces of Oracle software from a Windows NT system. Editing of Windows NT registry variables is required. Be very careful. There is absolutely no Undo option in the Registry Editor. Make sure that you are logged in as the administrator.

These are the general steps.

  1. Perform a clean shutdown of the database. Stopping the OracleService does NOT perform a clean shutdown of the database.
  2. Stop all Oracle services.
  3. Use the Oracle Installer to remove installed Oracle software.
  4. Delete the ORACLE_HOME directory.
  5. Delete any Oracle INI files (e.g., ORADIM73.INI, OEMTBAR.INI) from the Windows NT boot directory (specified by the WINDIR environment variable, e.g., C:\WINNT40).
  6. Remove the HKEY_LOCAL_MACHINE | SOFTWARE | ORACLE branch from the Registry.
  7. Remove all Oracle services from HKEY_LOCAL_MACHINE | SYSTEM | CurrentControlSet | Services.
  8. Remove the Oracle for Windows NT shortcut folder.
  9. Remove the ORACLE entries from the NT environment.
  10. Reboot the system.

Stop and Delete Existing Oracle Services

Run the Services Control Panel to stop the installed Oracle services. Select the Oracle services and click stop. Whenh done close the Services Control Panel. It is required to stop the services for the previous database and then create and start services for the new database.

Be sure to verify that if you have database instances that are created; there exist services in the Services Control Panel with a name like OracleService<SID> (unique instance System Identifier) and OracleStart<SID> . If so remove the instances with the appropriate shown below. Note that <SID> is the instance name. If the instance name is not what you expected then you may have to alter registry entries and Oracle environment variables to change later on.

Use ORADIM73 for Oracle7, ORADIM80 for Oracle8 and ORADIM for Oracle8i. Note never use the GUI Instance Manager. This GUI tool has actually been de-supported in Oracle8i and probably for a very good reason. Note that output from the ORADIM?? process is stored in a file called c:\orant\rdbms73\oradim73.log. A similar file probably exists for Oracle8i. Use this log file. It will tell you about success or failure of commands.

ORADIM?? -DELETE -SID <SID>

The command above should remove all instance related Oracle services. If the command does not function properly try the following.

ORADIM?? -DELETE -SRVC <service_name>

If there is still a problem you will find a file in the database directory called PWD<SID>.ORA. Rename or remove this file and proceed to use the

ORADIM?? -DELETE -SID <SID>
command once again. If this fails aswell simply remove the services using the services editor in the control panel. Use this final option as a lst resort. Why is unknown; yhat is what the documentation states.

Deinstall Oracle Products

Uninstall using the Oracle Installer program. Note that there is a Y2K bug-fix Oracle installer available. This patch is currently available at http://205.227.44.220/server/patchsets/wgt_tech/server/windowsNT/utilities/installer. The version is 3.3.1.2.4. Note that this Y2K patched installer must be executed from itself and get its data files from the installation CD. Do not use Autorun on the CD or execute any other installation executable from the CD otherwise you will not be using the Y2K patched Oracle installer. This installer has been known to crash whilst creating the default database during installation. Call Oracle support for details. Select all of the installed products at the product screen and click the remove button to uninstall. Some errors may result. Some parts of the installation will be read only and undeleteable. This probably will have something to do with running services and other processes which could inhibit the deletion of sections of Oracle. The Oracle Installer program may have to be re-executed anumber of times. There is always the possiblity of completely removing the Oracle home directory aswell. However, you will then have to manually remove Oracle entries from the Windows NT Registry.

The Oracle Home Directory

If the Oracle home directory has not already been removed then do this now. Once again if permissions are inhibitive then Oracle service processes are probably responsible. Stop those services. The Oracle7 default directory is ORANT.

Oracle .INI Files Removal

Delete any Oracle INI (configuration) files that may exist in the Windows NT boot directory or anywhere else on the machine. Find the files using the windows Find Files or Folders from thje Find option on the Start menu. Make sure you are removing files which are relevant to Oracle. Also make sure that all these INI files are removed because there will be problems if not. Specific Oracle INI files for 32-bit installations include ORADIM73.INI and ORADIM80.INI.

Registry Keys Removal

The Registry can be editied using the ORADIM?? utility or by using REGEDIT.EXE. The following registry keys are required to be removed.

  1. HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. Remove the Oracle key called ORACLE.
  2. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services. Delete the Oracle services.
  3. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall.

Menus

Remove Oracle Start Menu options and any other references to Oracle programs such as desktop icons.

Environment Variables

Remove any applicable Oracle environment variables. Oracle does make additions to the PATH variables. When completed reboot the server. Registry changes in particular usually require a reboot.

Copying the Database Directory

Literally, copy the database directory from one Oracle installation to the other. The new database could have been installed with or without

Messing Around with Instances

Each instance of the database on the Windows NT server must be uniquely identified by a System IDentifier (SID). On Windows NT, this is a string that is one to four alphanumeric characters long. The SID can be composed of only letters and numbers.

Probably the easiest method of copying a database into a new Oracle installation without the use of the export and import command utilities is the install Oracle with the default database. One could also create an empty database. This second option may actually be easier since the database could be named to the required name. The default database installation has the instance name ORCL and the database is called ORACLE. When copying another database into the default database installation on has to change the instance and database names for the newly copied in database. This includes Registry changes.

Creating Services for the New Database

Database services may need to be recreated.

There may be a requirement to create a new database by running scripts or entering the create database commands manually. Before this can be done the database service for the new SID must exist.

The OracleStart service no longer automatically starts the database when booting the server. If the database can be started manually recreating the database services will usually resolve this issue.

There may be a requirement to change the INTERNAL password and update the OracleStart service so it starts the database with the new INTERNAL password.

If you are recreating existing database services the password file for the instance will be recreated. If the value for the INIT.ORA parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE and you have granted other users SYSDBA and/or SYSOPER privileges you will need to regrant these privileges to the users. The following query, executed by a user with DBA privileges, will display the users granted the SYSDBA and/or SYSOPER privileges as shown below.

SELECT * FROM V$PWFILE_USERS;

Creating Services

ORADIM?? -NEW -SID <SID> -INTPWD <password> -STARTMODE <mode> -PFILE <pfile>

Review the Log

Review the ORADIM log file for any errors.In some cases the existing password file cannot be erased by ORADIM, typically if the properties of the file have been altered. There will be an error in the ORADIM log file to this effect. Move, rename, or delete the existing password file, PWD<sid>.ORA located by default in <oracle_home>\DATABASE. This is normally a hidden file so you may need to change the View options in Explorer to show hidden files.

Verify Service Creation

The OracleService<SID> service will be started if you specified AUTO for the start mode. The OracleStart<SID> service will not be started as ORADIM does not know whether these services are for an existing database or for one that has not yet been created.

If there are any errors or both services were not created then delete the existing services before you can recreate them.

Starting Services and the Database Instance

ORADIM?? -STARTUP -SID <SID> -USRPWD <password> -STARTTYPE <type> -PFILE <pfile>

Review the ORADIM?? log for errors.

Shutting Down the Database Instance and Services

ORADIM?? -SHUTDOWN -SID <SID> -USRPWD <password> -SHUTTYPE <type> -SHUTMODE <mode>

Review the ORADIM?? log for errors.

Important Things to Verify the Copied Database

The Internal Password

The Oracle installation default database allows user name internal password setup on installation. The user name internal for any database will be found in files in the database directory called strt<SID>.cmd and stop<SID>.cmd. Only the strt<SID>.cmd file appears to exist after installation of Oracle7. This file will have an ORADIM?? command with a USRPWD parameter. The value of this password parameter is the internal user name password. If these files do not exist in the database directory you are copying and you do not know the internal password you may be in trouble.

Database Files Encryption

Note that all database data files are encrypted. There is no way to edit these files unless you know the encryption format. O)racle knows how to do this.

SVRMGR23.EXE

This program can be used to verify not only that the database is running but also to verify that all database instance and parameter values are set correctly.

Once all the Above is Verified

Start up the new Oracle instance in a DOS shell.

SET ORACLE_SID=<instance_name>

Then execute the SVRMGR23.EXE server manager utility and enter the following.

connect internal/<password>;
startup mount pfile=&;ltfull path\init<SID>.ora;
alter database <database_name> open;

Now do some more verification as follows.

Inside SVRMGR23.EXE

Execute the following commands and ensure that all relevant instance and database names are correctly show as you would expect. The control file selection ensure you are seeing the correct control file and the thread view ensures you are seeing the correct database and that instance and database names match.

SELECT * FROM V$CONTROLFILE;
SELECT * FROM V$THREAD;

Check the Registry

At this stage it would be expedient to check values in the Registry to ensure that both instance and database names are consistent with what you expect. These entries are found in the areas of the Registry as discussed earlier in this document.

Updating the Oracle Listener

The listener service is a process which will sit on the Oracle database server and wait for calls to that database instance. The easiest way to update the listener for your newly copied database is to copy the listener files from the previous database. These files are located in c:\orant\network\admin. All of the files should be copied. The minimun files should be eventdef.tcl, listener.ora, sqlnet.ora and tnsnames.ora. There may be additional files. If so, copy them too if the client machines mcan not contact your database server. Otherwise the listener files can be configured using the SQL Net Easy Configuration Utility.


Useful Goodies

tnsping <SID> - pings the listener processes on the server.

lsnctrl start <SID> - starts the instance listener on the server.

oemctrl start oms - starts up the Oracle Enterprise Manager Server process (service) on the server.

Oracle Enterprise Manager - post installation readme file - d:\oracle\oem81\sysman\doc\readme\oamreadme.txt

Connection string for OEM - <Hostname>:1521:<SID>