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.
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.
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.
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.
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.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.
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.
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.
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.
The Registry can be editied using the ORADIM?? utility or by using REGEDIT.EXE. The following registry keys are required to be removed.
Remove Oracle Start Menu options and any other references to Oracle programs such as desktop icons.
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.
Literally, copy the database directory from one Oracle installation to the other. The new database could have been installed with or without
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.
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
There may be a requirement to change the INTERNAL password and update the
OracleStart
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;
ORADIM?? -NEW -SID <SID> -INTPWD <password> -STARTMODE <mode> -PFILE <pfile>
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.
If there are any errors or both services were not created then delete the existing services before you can recreate them.
ORADIM?? -STARTUP -SID <SID> -USRPWD <password> -STARTTYPE <type> -PFILE <pfile>
Review the ORADIM?? log for errors.
ORADIM?? -SHUTDOWN -SID <SID> -USRPWD <password> -SHUTTYPE <type> -SHUTMODE <mode>
Review the ORADIM?? log for errors.
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.
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.
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.
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.
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;
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.
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.
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>