Oracle Applications Release 10.7 Database Copy Procedures

Author: Andy Rivenes (arivenes@appsdba.com)
Copyright AppsDBA Consulting, All Rights Reserved.
Last Updated: August 9, 1999

Overview

These guidelines are meant to provide the information necessary to create a copy of an Oracle Applications database on another similar machine. By similar we mean of the same platform and operating system. Typically this would involve copying a production database and creating or refreshing a development database on another machine or location. The sections that deal with the database are generic to the Oracle Server and can be used regardless of whether the database is used for Oracle Applications or not. The Oracle Applications specific sections refer to the System Administration tasks that should be performed after copying an Oracle Applications database, and changes to custom scripts that are provided with our installations and will already exist for a system that has been installed by us. The steps necessary for refreshing the Oracle Applications product files have also been added. This step is not necessary if the database is being refreshed into an existing environment or the Oracle Application's environment is the same as the source database.

Recently these procedures have been restructured to document the steps necessary to make the database copy using either a full backup (this is the method this paper originally documented) or an online backup.

Backup The Source Database

In order to copy the database to another location (e.g. as in a refresh of production data into development) either a full backup or an online backup must be taken of the source database. A full backup is defined as a physical copy of the database files after a normal shutdown of the database. An online backup is defined as a physical copy of all the database datafiles while the database is open and operational. It should be noted that there are specific procedures that should be followed when taking an online backup (e.g. ALTER TABLESPACE ... BEGIN/END BACKUP). See the Oracle documentation on backups and recovery for further details. Additionally, an online backup will require the source database's archived redo log files from the point of the first datafile backup through the last datafile backup.

In addition to all database datafiles, the init.ora and an ASCII copy of the control file for the source database will be needed. The actual control files are not needed as the ASCII copy will be used to create a new control file to be used with the new database, and the online redo log files are not needed as the new database will be created with the RESETLOGS command. File permissions and ownership should be noted and this should be accounted for when performing the backup and restore.

The following commands will create the ASCII control file (these commands should be issued from the source database):

This will create a trace file in the user dump directory of the source database on the database server. This file can now be edited in order to create the SQL script that will be necessary in later steps. The appropriate changes needed are detailed in the section "Create Control File Script" section.

Create The Target UNIX Environment

All database file directories will need to be created in the target environment. It should be noted that these directories do not need to be named the same as the source database's directories.  The Oracle Server software should be equivalent (e.g. $ORACLE_HOME) to that used for the source database.

The $ORACLE_BASE/admin directory, and its sub-directories, for the new database should also be created to provide a destination for the dump and configuration directories (see OFA standards).

Restore The Source Files To The Target Machine

Once the source database backup has been completed the files backed up can be restored into the desired directories on the target machine. File ownership and permissions should be verified after the restore. If there was an existing database that will be overwritten this database should be shutdown and backed up. Don't forget to shut down the concurrent manager processes also. Once backed up, the datafiles, control files, and online redo log files associated with the database to be refreshed can be deleted. All trace files can also be removed and if there are archive log files they should be backed up to tape and deleted.

Create Control File Script

After the source database's datafiles have been restored to their target directories, or these directories are known, the ASCII control file that was created can be modified. The following changes will need to be made:

1. Remove all comments from the file (the Version 7.1 Server Manager product does not support comments).
2. Rename the online redo log files and data files' directories to their new names.
3. Replace the option REUSE DATABASE <dbname> with SET DATABASE <new_dbname> where <dbname> is the source database name and <new_dbname> is the new target database name.

Note: We now recommend removing the REUSE option to avoid errors when increasing the size of the controlfile.

4. Replace NORESETLOGS with RESETLOGS. Required when the SET DATABASE command is used.
5. Verify that the ARCHIVELOG or NOARCHIVELOG option is appropriate for the target database.

Note: ARCHIVELOG will be required if an online backup was made.

6. Remove all further comments and commands. This includes the RECOVER DATABASE command and the ALTER DATABASE OPEN command at the bottom of the file.
7. Save this file as a SQL script with a name like new_control.sql. It is probably easiest to locate this file in the $ORACLE_HOME/dbs directory of the target database.

Modify The init.ora

At this point either the old target database init.ora file or the init.ora file that was saved from the source database can be used, depending on desired parameter settings and the changes necessary. In either case the following items need to be verified/changed:

1. Change/verify all directory names.
2. Change/verify the "db_name =" parameter.
3. Change/verify mts parameters.

Rename The Target Database

We are now ready to start the database and rename it to the target name.

Miscellaneous Database Environment Issues

Update oratab File

The oratab file should be updated appropriately with the new database's information.

Update SQL*Net Files

Oracle Applications Changes

AOL Passwords

The Oracle Applications passwords will now be the same as the source database. If the target databases' passwords were different then they will need to be changed within Oracle Applications. This can be accomplished by logging in as System Administrator and navigating to the "Register ORACLE IDs" screen (\NavSecOraReg). It is important that the changing of passwords in this screen is synchronized with the changing of passwords in the database so another session should be created through SQL*Plus and connected as SYSTEM. As the password changes are commited in Oracle Applications the passwords at the database level for the ids involved should also be changed (e.g. ALTER USER xxx IDENTIFIED BY xxx).

Great care must be taken when changing the APPLSYSPUB, APPLSYS, and APPS passwords. If these passwords get out of sync between the application and the database you will not be able to log back into Oracle Applications and you will be enduring a rather painful call to Oracle Support. We recommend that a second Oracle Applications session connected to the System Administration responsibility be kept open during this process just in case there are problems reconnecting.

Note: Release 10.7 requires that the APPLSYS and APPS passwords match in order for the administrative DBA utilities to work (e.g. adctrl, adadmin, etc.).

Concurrent Manager Scripts

Any scripts that are used to start and stop the concurrent manager will need to be modified to reflect the correct password of the userid being accessed. If the AOL passwords are changed back to the original target database this step is not necessary. For databases that we have installed these scripts are located in the $HOME/script directory of the applmgr UNIX userid and are called cmstart and cmstop.

We also include a script that purges the log and out files and this will also have to modified if the passwords are changed. This script is called prglgout.ksh.

Scheduled Concurrent Manager Jobs

Before starting the concurrent managers be sure to evaluate all scheduled jobs and put any on hold that should not be run.

Other AOL Items

Node Name

The Oracle Applications node name should be reset to the name of the database server that is being used. This can be accomplished by logging in as System Administrator and navigating to the "Register Nodes" screen (\NavInsNod). A query run (\QueRun) will bring up the current value and can be changed by overtyping and then performing a screen save (\ScrSav).

Log And Out Files

All log and out files can be purged from the original target system's log and out directories and, unless the log and out files will be brought from the source system, all concurrent requests can be purged in Oracle Applications as well. From the operating system an rm of $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT will remove all of the original log and out files (this assumes a common area is in use). To remove the Oracle Applications report and log file entries in the database the report "Purge Concurrent Request and/or Manager Data" can be run from the System Administrator responsibility. The following parameter values should be set in the "Report Parameters" pop-up window:

Alerts

Check that all periodic and event alerts are either disabled, or that if they run they will not cause any problems. This can be especially true for alerts that send email messages.

Oracle Applications Product Files

The Oracle Applications product files consist of the $APPL_TOP directory and support the concurrent managers, concurrent programs, and in most cases the character mode access to Oracle Applications. The product files can be updated in two ways. The new product file directory can be co-located into an existing "applmgr" account or a whole new "applmgr" account can be created.

It is strongly recommended that each Oracle Applications database have its own unique set of Oracle Applications product files. This is especially critical for SmartClient and NCA environments because patching can cause application failures unless the product files and the database are kept in sync.

1. Existing "applmgr" Account

If the product files are to be located into an existing "applmgr" account then a new $APPL_TOP directory must be created, a new APPLSYS.env setup script must be created (this can be done either by copying and editing an existing script or using the adadmin utility), new $APPLLOG and $APPLOUT directories must be created (assumes use of common directory), and the local $HOME/script files (e.g. <env.ksh>) must be updated.  

2. New "applmgr" Account

If the product files are to be located into a new "applmgr" account then a new UNIX account must be created, and then the entire source "applmgr" account can be copied. The APPLSYS.env setup script and the local $HOME/script files should be updated appropriately.

Appendix A - Sample new_control.sql