Manually Clone A Database

Manually Clone a Database DOC Id :458450.1 Applies to : Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2.0 .How to clone a database manually, without using RMAN Steps to clone the database The following steps can be used to clone a database on the same server or on another server, We will clone a database with name proddb the cloned database will have the name clonedb, and clonedb will reside on the same server as proddb, The cloning process mainly has 2 steps, 1. Take a cold backup of the production database 2. Restore the backup on to the clone destination and startup the clone database Optionally you can rename the clone database, before starting it up. the steps below describe the process of cloning a database and also renaming the database. Perform Cold Backup of the Production database 1. Note down the datafile, controlfile and redologfile locations on proddb OS> sqlplus / as sysdba SQL> select name from v$datafile; SQL> select member from v$logfile; SQL> show parameter control_files 2. Shutdown the production database proddb OS> sqlplus / as sysdba SQL> shutdown immediate NOTE : Ensure clean shutdown. 3. Copy the database files (datafiles, logfiles, controlfiles and parameter file) into the clone destination. Using the information gathered in step 1, change directory to the production database directories

OS> cd /u01/<path_to_proddb_files> Note : for simplicity sake we'll assume all files are in the same directory OS> cp /u01/<path_to_proddb_files>/ /u01/<path_to_clonedb_files>/ .. .. repeat the same for all datafiles, controlfiles and redologfiles .. OS> cp $ORACLE_HOME/dbs/initproddb.ora $ORACLE_HOME/dbs/initclonedb.ora 4. Modify the parameter CONTROL_FILES in the pfile for clonedb to reflect the new path of the control files. Using your favorite text editor edit the pfile to reflect the new path of the control files vi initclonedb.ora .. .. CONTROL_FILES = '/u01/clonedb/control/control01.ctl','/u01/clonedb/control/control02.ctl' .. ...

Rename the database as per requirements and start the clone database We rename the database only if needed, if you plan to retain the same name, the whole process simplifies to taking a cold backup and restoring it in the target location and starting up the database, however renaming becomes mandatory if the cloning happens on the same server and in the same Oracle Home. If the database version is less than version 9.2, please go to section "Renaming database with version lower than 9.2" Note : Ensure DB_NAME is still proddb in initclonedb.ora, clonedb pfile, Please note that the production database should be shutdown throughout the clone process, this is because we are cloning the database on the same server and using the same Oracle Home, if either one is different, database can be started immediately after copying the files. 1. Mount the database and rename the datafiles and redolog files.

export ORACLE_SID=clonedb OS> sqlplus / as sysdba OS> startup mount OS> alter database rename file '/u01/<path_to_proddb_files>/' to '/u01/<path_to_clonedb_files>/' 2. Rename the database with the dbnewid (nid) utility For information on how to use the DBNEWID utility, please refer this note Note 224266.1 Title: How to Change the DBID and the DBNAME by using NID NOTE : If you are using a database of version lesser than 9.2 then the DBNEWID (NID) utility cannot be used as it was introduced only from 9.2 3. Edit the pfile and update the new database name vi initclonedb.ora ---> Modify parameter DB_NAME to reflect the new database name. also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME as needed. 4. Open the clone database. 5. Startup and open the production database. Note : These steps may not be applicable to databases using OMF files as there are restrictions on renaming the datafiles, controlfiles and logfiles when they are Oracle Managed, it is Likely that you will receive a ORA-1276 when you rename a OMF file.

Renaming database with version lower than 9.2

Editing the trace generated in 1 of this section, ensure to use the SET Note : In this case it is not required to copy controlfilesstep or redologfiles to set the as they will be created newly, also note that when usingoption this method to new database name. rename the database, the production database can be started Example immediately following the copying of the datafiles to the clone CREATE CONTROLFILE SET DATABASE location. "CLONEDB" RESETLOGS NOARCHIVELOG 1. Generate a controlfile backup trace file with the command -- SET STANDBY TO MAXIMIZE SQL> alter database backup controlfile to trace as '/path/filename.trc'; PERFORMANCE MAXLOGFILES 16 2. Edit the pfile and update the new database name MAXLOGMEMBERS 2 MAXDATAFILES 30 vi initclonedb.ora MAXINSTANCES 1 MAXLOGHISTORY 454 ---> Modify parameter DB_NAME to reflect the new database name. LOGFILE also ensure you make necessary changes to the environment for GROUP 1 ORACLE_SID, ORACLE_HOME '/home/oracle/databases/clonedb/redo1.ora' as needed. SIZE 100M, GROUP 2 3. Make a script to create a new controlfile '/home/oracle/databases/clonedb/redo2.ora' SIZE 100M, GROUP 3 '/home/oracle/databases/clonedb/redo3.ora' SIZE 100M -- STANDBY LOGFILE DATAFILE '/home/oracle/databases/clonedb/system.dbf', '/home/oracle/databases/clonedb/undo.dbf', '/home/oracle/databases/clonedb/data.dbf' CHARACTER SET WE8ISO8859P1 ; File saved as createcontrol.sql 4. Connect to the clone instance and start in the NOMOUNT stage. export ORACLE_SID=clonedb sqlplus /nolog SQL> conn / as sysdba SQL> startup nomount SQL> @createcontrol.sql Control File Created. SQL> alter database open resetlogs ;

How to Change the DBID and the DBNAME by using NID Introduction ============ The NID (New Database ID)is a new utility introduced with Oracle 9.2.  The NID utility allows you to change only the DBNAME, or only the DBID or both  DBNAME and DBID in the same command.   1. If you change the DBID you must open the database with the RESETLOGS       option, which re­creates the online redo logs and resets their sequence      to 1.    2. If you change the DBNAME without changing the DBID then this does not       require you to open with the RESETLOGS option, so database backups and      archived logs are not invalidated.  You must change the DB_NAME  initialization      parameter after a database name change to reflect the new name.  Also,       you may have to re­create the Oracle password file.  If you restore an old      backup of the control file (before the name change, then you should      use the initialization parameter file and password file from before the       database name change.   Purpose ======= It is useful to troubleshoot from the situation when duplicate database ID's cause errors such as the following.   RMAN­20002: target database already registered in recovery catalog         Cause: target database is already registered in the recovery catalog        Action: If the target database is really registered, there is no need to                register it again. Note that the recovery catalog enforces that                all databases have a unique DBID. If the new database was created               by copying files from an existing database, it will have the same               DBID as the original database and cannot be registered in the                same recovery catalog.   RMAN­20011, 1, "target database incarnation is not current in recovery catalog"   RMAN­20009, 1, "database incarnation already registered"   ­or­   SQLPLUS> alter database mount standby database;   alter database mount standby database   *   ORA­01102: cannot mount database in EXCLUSIVE mode Restrictions and Usage Notes ============================ The DBNEWID utility has the following restrictions:

 ­ The utility is available only on the UNIX and Windows NT operating systems.   ­ The NID executable file should be owned and run by the Oracle owner because     it needs direct access to the datafiles and control files.   ­ If another user runs the utility, then set the user ID to the owner of the  datafiles and control files.   ­ The DBNEWID utility must access the datafiles of the database directly through  a local connection.    Although DBNEWID can accept a net service name, it cannot change the DBID of a  nonlocal database.   ­ To change the DBID of a database, the database must be mounted and must have  been shut down consistently     prior to mounting. In the case of an Oracle Real Application Clusters database,  the database must be    mounted in NOPARALLEL mode. i.e set the init parameter CLUSTER_DATABASE=FALSE  and then mount the database   ­ You must open the database with the RESETLOGS option after changing the DBID.   ­ Note that you do not have to open with the RESETLOGS option after changing only  the database name.   ­ No other process should be running against the database when DBNEWID is  executing.     If another session shuts down and starts the database, then DBNEWID aborts.   ­ All online datafiles should be consistent without needing recovery.   ­ Normal offline datafiles should be accessible and writable.    If this is not the case, you must drop these files before invoking the DBNEWID  utility.   ­ All read­only tablespaces must be accessible and made writable at the operating  system level prior    to invoking DBNEWID. If these tablespaces cannot be made writable (for example,  they are on a CD­ROM),    then you must unplug the tablespaces using the transportable tablespace feature  and then plug them back    in the database before invoking the DBNEWID utility (see the Oracle9i Database  Administrator's Guide).   ­ You can only specify REVERT when changing only the DBID.   Change Only the DBID ====================   1. Backup the database   2. SHUTDOWN IMMEDIATE of the database   3. STARTUP MOUNT   4. Open one session and run NID with sysdba privileges      % nid TARGET=SYS/password@test_db    5. Shutdown IMMEDIATE of the database   6. Set the DB_NAME initialization parameter in the initialization parameter       file to the new database name    7. Create a new password file    8. Startup of the database with open resetlogs Example: ======== 1. C:\>set ORACLE_SID=TEST1BY

   C:\>sqlplus "/as sysdba"    SQL*Plus: Release 9.2.0.1.0 ­ Production on Tue Dec 24 11:16:52 2002    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.    Connected to an idle instance.    SQL> startup pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora    ORACLE instance started.    Total System Global Area  135338868 bytes    Fixed Size                   453492 bytes    Variable Size             109051904 bytes    Database Buffers           25165824 bytes    Redo Buffers                 667648 bytes    Database mounted.    Database opened. 2. check the DBID before change     SQL> select dbid,name,open_mode,activation#,created from v$database;          DBID NAME      OPEN_MODE  ACTIVATION# CREATED    ­­­­­­­­­­ ­­­­­­­­­ ­­­­­­­­­­ ­­­­­­­­­­­ ­­­­­­­­­    1395399949 TEST1BY   READ WRITE  1395404134 10­SEP­02 3. SQL> shutdown immediate;    Database closed.    Database dismounted.    ORACLE instance shut down. 4. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora    ORACLE instance started.    Total System Global Area  135338868 bytes    Fixed Size                   453492 bytes    Variable Size             109051904 bytes    Database Buffers           25165824 bytes    Redo Buffers                 667648 bytes    Database mounted.    SQL>exit 5. execute NID    C:\>nid target=sys/oracle@TEST1BY    DBNEWID: Release 9.2.0.1.0 ­ Production    Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.    Connected to database TEST1BY (DBID=1395399949)    Control Files in database:      D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL    Change database ID of database TEST1BY? (Y/[N]) => y    Proceeding with operation

   Changing database ID from 1395399949 to 1397190693    Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL ­ modified    Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF ­ dbid changed    Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF ­ dbid changed    Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL ­ dbid changed    Database ID for database TEST1BY changed to 1397190693.    All previous backups and archived redo logs for this database are unusable.    Shut down database and open with RESETLOGS option.    Succesfully changed database ID.    DBNEWID ­ Completed succesfully. 6. SQL> shutdown immediate;    ORA­01109: database not open    Database dismounted.    ORACLE instance shut down. 7. create the new passwordfile 8. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora    ORACLE instance started.    Total System Global Area  135338868 bytes    Fixed Size                   453492 bytes    Variable Size             109051904 bytes    Database Buffers           25165824 bytes    Redo Buffers                 667648 bytes    Database mounted. 9. SQL> alter database open resetlogs;    Database altered. 10. check the new DBID     SQL> select dbid,name,open_mode,activation#,created from v$database;           DBID NAME      OPEN_MODE  ACTIVATION# CREATED     ­­­­­­­­­­ ­­­­­­­­­ ­­­­­­­­­­ ­­­­­­­­­­­ ­­­­­­­­­     1397190693 TEST1BY   READ WRITE  1397188261 10­SEP­02 NOTE: The NID change the OLD DBID 1395399949 to the NEW DBID 1397190693             Change Only the DBNAME ======================   1. Backup the database   2. SHUTDOWN IMMEDIATE of the database   3. STARTUP MOUNT

  4. Open one session and run NID with sysdba privileges      % nid TARGET=SYS/password@test_db DBNAME=test_db2 SETNAME=Y      ­ the value of DBNAME is the new dbname of the database      ­ SETNAME must be set to Y. The default is N and causes the         DBID to be changed also.   5. shutdown IMMEDIATE of the database   6. Set the DB_NAME initialization parameter in the initialization parameter      file to the new database name   7. Create a new password file    8. Startup of the database(without resetlogs)              Change Both DBID and DBNAME ===========================   1. Backup of the database.    2. Shutdown IMMEDIATE of the database   3. STARTUP MOUNT   4. Open one session and run NID with sysdba privileges      % nid TARGET=SYS/password@test_db DBNAME=test_db2      ­ the value of DBNAME is the new dbname of the database   5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the  database    6. Set the DB_NAME initialization parameter in the      initialization parameter file to the new database name.    7. Create a new password file.    8. Startup of the database with open resetlogs    Example: ======== 1. Shutdown the databse    SQL> shutdown immediate;    Database closed.    Database dismounted.    ORACLE instance shut down. 2. Mount the database    SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora    ORACLE instance started.    Total System Global Area  135338868 bytes    Fixed Size                   453492 bytes    Variable Size             109051904 bytes    Database Buffers           25165824 bytes    Redo Buffers                 667648 bytes    Database mounted.    startup mount 3. Run NID utility    C:\>nid target=sys/oracle@test1by dbname=test1by  logfile=d:\oracle\base_test\nid.log

      DBNEWID: Release 9.2.0.1.0 ­ Production    Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.    Connected to database TEST1 (DBID=849281895)    Control Files in database:      D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL    Changing database ID from 849281895 to 1395399949    Changing database name from TEST1 to TEST1BY    Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL ­ modified    Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF ­ dbid changed, wrote new name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF ­ dbid changed, wrote new  name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF ­ dbid changed, wrote new name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF ­ dbid changed, wrote new  name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF ­ dbid changed, wrote new name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF ­ dbid changed, wrote new name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF ­ dbid changed, wrote new name    Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF ­ dbid changed, wrote new name    Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL ­ dbid changed, wrote new  name    Database name changed to TEST1BY.    Modify parameter file and generate a new password file before restarting.    Database ID for database TEST1BY changed to 1395399949.    All previous backups and archived redo logs for this database are unusable.    Shut down database and open with RESETLOGS option.    Succesfully changed database name and ID.    DBNEWID ­ Completed succesfully. 4. Shutdown immediate of the database   5. Modfiy the file init.ora of the database    change the parameter BD_NAME=TEST1 TO DBNAME=TEST1BY 6. create a new password file    Usage: orapwd file= password=<password> entries=<users>    where     file ­ name of password file (mand),     password ­ password for SYS (mand),     entries ­ maximum number of distinct DBA and OPERs (opt),     7. mount the database    SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora    ORACLE instance started.    Total System Global Area  135338868 bytes    Fixed Size                   453492 bytes    Variable Size             109051904 bytes    Database Buffers           25165824 bytes    Redo Buffers                 667648 bytes

   Database mounted. 8. Open the databse with RESETLOGS    SQL> alter database open resetlogs;    Database altered. 9. check the dictionary view for the new DBID and DBNAME    select dbid, name, open_mode, activation#, created from v$database;   Other Useful Information ======================== Except the parameters like LOGFILE=, APPEND, HELP, there is a specific option  REVERT which allows us to go back on the steps of executing.  If the value of  REVERT is YES that means that a failed change of DBID should be reverted but a  successfully completed change of DBID cannot be reverted.  REVERT=YES is only  valid when a DBID change failed.  Default value is NO.   Documentation ============= Oracle9i Database Utilities Release 2 (9.2)

1. Purpose This work instruction document describes how you can create another database from an existing database. The new database will be a clone of the original database, so it will have the same tablespaces, data, users, and all other database objects as the original database from which it was cloned. 2. Scope and Intended Audience A database might need to be copied in order to duplicate the production system, for example for testing purposes. The cloning procedures are used for duplicating any Oracle database (with or without Oracle Clinical data). This method is very similar to an offline cold backup of the database. A mandatory condition for the work instructions described in this document is that the source and the target databases must have the same Operating System version, and the same RDBMS versions. In addition, the ORACLE_HOME path for the target database must be the same with the one used by the source database. For example, the source database prodx1 can be physically located on a machine A with Operating System Solaris 2.8, RDBMS version 8.1.7.4.0, and ORACLE_HOME /u01/app/oracle/product/8.1.7.4.0. The target (cloned) database prodx2 can be created physically on a machine A or B with Operating System Solaris 2.8, RDBMS version 8.1.7.4.0, and ORACLE_HOME /u01/app/oracle/product/8.1.7.4.0. It applies to both Windows and Unix databases. A restriction on this document is that it should not be used to copy a database which is to be backed up using the RMAN (Recovery Manager). RMAN keeps track of databases by their DBID, a number issued solely during the "create database" command. If the copied database is created using Oracle8 or is migrated from Oracle7 to Oracle8, RMAN will not be able to distinguish it from it's original in the RMAN repository (Note: 18070.1). This document applies to everyone who is dealing with databases.

3. How to Clone a Database from an Existing Database In the following steps, we assume the following: •

The source database is an existing database with any kind of data, and for the simplicity of this document is called prodx1

•

The target database is called prodx2 and it will be created as a clone of prodx1

•

We use the OFA structure and all administrative files for each database, are located under: o

On Windows Operating System:

e:\oracle\admin\<SID>\pfile e:\oracle\admin\<SID>\bdump e:\oracle\admin\<SID>\udump e:\oracle\admin\<SID>\cdump

o

On Unix Operating System:

/u01/app/oracle/admin/<SID>/pfile /u01/app/oracle/admin/<SID>/bdump /u01/app/oracle/admin/<SID>/udump /u01/app/oracle/admin/<SID>/cdump where <SID> is the name of a database. •

All files are located on a machine with RAID 5 configuration and all data, index redo log and control files are located under the same directory. For the use of this document, let's assume that all files are located under:

•On Windows

Operating System:

e:\oradata\<SID>\

o

On Unix Operating System:

/u01/oradata/<SID>/ where <SID> is the name of a database

•

All data and index file names have a suffix of .dbf and the redo log files a suffix of .log

•

For all examples presented in Unix, we make use of the Csh shell

3.1 Generic Steps The following steps on this section must be followed for cloning any database regardless of its data. The next section â€Ã…“Oracle Clinical Specific Stepsâ€Ã‚ÂÂ� applies to databases with Oracle Clinical data only. 3.1.1 Backup the Source Database Before attempting to clone the source database, it is necessary to perform a full backup of the database. This will ensure that no data will be lost if the copying of the database is unsuccessful.

3.1.2 Backup the Source Database's Control File to a Trace File Connect to the source database prodx1 via svrmgr or SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Perform a backup of the control file to trace using the following command: SQL> alter database backup controlfile to trace resetlogs; Examples: •

On Windows Operating System:

C:\> set ORACLE_SID=prodx1 C:\> set ORACLE_HOME=e:\oracle\ora81741 C:\> %ORACLE_HOME%\bin\sqlplus 'sys/change_on_install as sysdba'� SQL> alter database backup controlfile to trace resetlogs; •

On Unix Operating System:

% source $ORACLE_HOME/bin/coraenv ORACLE_SID = [oracle] ? prodx1 ORACLE_HOME = [/u01/home/oracle] ? /u01/app/oracle/product/8.1.7.4.0

% $ORACLE_HOME/bin/sqlplus 'sys/change_on_install as sysdba'� SQL> alter database backup controlfile to trace resetlogs; This will create a text file that we'll use later. The text file has the format ora_.trc or <SID>_ora_.trc or ora.trc depending on the Operating System , where is the number of the process that created this file. The trace file is created in the location specified by the initialization parameter USER_DUMP_DEST. To find the value of this parameter, type the following query: SQL> select name, value from v$parameter where name='user_dump_dest'; Based on our assumptions, the corresponding value to USER_DUMP_DEST will be: •

On Windows Operating System:

e:\oracle\admin\prodx1\udump •

On Unix Operating System:

/u01/app/oracle/admin/prodx1/udump Navigate to the directory selected above, identify the most recent .trc file and rename it to recr_con.sql. •

On Windows Operating System:

C:\> move e:\oracle\admin\prodx1\udump\ e:\oracle\admin\prodx1\udump\recr_con.sql •

On Unix Operating System:

% mv /u01/app/oracle/admin/prodx1/udump/ /u01/app/oracle/admin/prodx1/udump/recr_con.sql 3.1.3 Make a List of All of your Data and Redo Log Files

It is absolutely essential to know the names and locations of all data files and redo log files. To do this, issue the following queries (you must connect as a user (i.e. SYS) who has sufficient privileges to see the V$ views): SQL> set pagesize 2000 SQL> spool files.log SQL> select name from v$datafile; SQL> select member from v$logfile; SQL> spool off The first select query will give the full path and file name for each of your data files (data and indexes). The second query will give the same information for all redo log files. Be sure to record this information for future reference. 3.1.4 Perform a "Clean" Shutdown of the Source Database A clean shutdown is necessary in order to make sure all data files and control files are synchronized with the most recent transactions. The following command will give you a clean shutdown: SQL> shutdown immediate; You must connect as a user (i.e. SYS) who has privileges (i.e. SYSDBA) to shut down the database. 3.1.5 Make a Copy of the Database Copy all data and redo log files noted in step 'Make a List of All of your Data and Redo Log Files'� to their new location making sure you preserve ownership and permissions. Do not copy any control file. You must first create the directories to for the target database prodx2, to save the administrative files, the data and log files. Issue the following commands: •

On Windows Operating System:

- Create administrative directories: % mkdir e:\oracle\admin\prodx2 % mkdir e:\oracle\admin\prodx2\bdump % mkdir e:\oracle\admin\prodx2\udump % mkdir e:\oracle\admin\prodx2\cdump % mkdir e:\oracle\admin\prodx2\pfile - Create data file directory: % mkdir e:\oradata\prodx2

- Copy Database Files C:> copy e:\oradata\prodx1\*.dbf e:\oradata\prodx2\ C:> copy e:\oradata\prodx1\*.log e:\oradata\prodx2\ C:> copy e:\oracle\admin\prodx1\pfile\initprodx1.ora e:\oracle\admin\prodx2\pfile\initprodx2.ora C:> copy e:\oracle\admin\prodx1\udump\recr_con.sql e:\oracle\admin\prodx2\udump\recr_con.sql •

On Unix Operating System:

- Create administrative directories: % mkdir /u01/app/oracle/admin/prodx2 % mkdir /u01/app/oracle/admin/prodx2/bdump % mkdir /u01/app/oracle/admin/prodx2/udump % mkdir /u01/app/oracle/admin/prodx2/cdump % mkdir /u01/app/oracle/admin/prodx2/pfile - Create data file directory: % mkdir /u01/oradata/prodx2 - Copy Database Files % cp /u01/oradata/prodx1/*.dbf /u01/oradata/prodx2/ % cp /u01/oradata/prodx1/*.log /u01/oradata/prodx2/ % cp /u01/app/oracle/admin/prodx1/pfile/initprodx1.ora /u01/app/oracle/admin/prodx2/pfile/initprodx2.ora % cp /u01/app/oracle/admin/prodx1/udump/recr_con.sql /u01/app/oracle/admin/prodx2/udump/recr_con.sql - Create symbolic link for the parameter file % ln /u01/app/oracle/admin/prodx2/pfile/initprodx2.ora $ORACLE_HOME/dbs/initprodx2.ora where $ORACLE_HOME is the full path of the RDBMS home installation for prodx1 (i.e. /u01/app/oracle/product/8.1.7.4.0) 3.1.6 Modify the Parameter File for the Cloned Database Using a text editor, revise the initprodx2.ora parameter file to reflect the new values for the target database. •

On Windows Operating System:

e:\oracle\admin\prodx2\pfile\initprodx2.ora •

On Unix Operating System:

/u01/app/oracle/admin/prodx2/pfile/initprodx2.ora

This will involve changing the paths indicated in parameters such as CONTROL_FILES, USER_DUMP_DEST, BACKGROUND_DUMP_DEST, and any other parameter that specifies a path/file location. A listing of an example initprodx2.ora follows. Note, in this example, each line is numbered to aid the reader in correlating and understanding the steps that follow the listing. These line numbers are not actually present in the initprodx2.ora file; they are added for the purpose of example only. Also, a simplistic example (Windows Operating System only Unix is similar) is shown here to make it easier to understand the steps involved. 1.db_name = prodx1 2.db_domain = us.oracle.com 3.control_files = ("e:\oradata\prodx1\control01_prodx1.ctl", "e:\oradata\prodx1\control02_prodx1.ctl") 4.db_block_buffers = 10000 5.db_block_lru_latches = 4 6.shared_pool_size = 25000000 7.shared_pool_reserved_size = 2500000 8.java_pool_size = 20000000 9.log_checkpoint_interval = 10000 10.log_checkpoint_timeout = 0 11.processes = 50 12.log_buffer = 163840 13.# audit_trail = false # if you want auditing 14.timed_statistics = true # if you want timed statistics 15.# max_dump_file_size = 10000 # limit trace file size to 5M each 16.#log_archive_start = true 17.#log_archive_dest_1 = "location=T:\oracle\admin\prodx1\arch" 18.#log_archive_format = %t_%s.dbf 19.# If using private rollback segments, place lines of the following

20.# form in each of your instance-specific init.ora files: 21.rollback_segments = (r01, r02, r03, r04) 22.# Global Naming -- enforce that a dblink has same name as the db it connects to 23.global_names = true 24.# Uncomment the following line if you wish to enable the Oracle Trace product 25.# to trace server activity. This enables scheduling of server collections 26.# from the Oracle Enterprise Manager Console. 27.# Also, if the oracle_trace_collection_name parameter is nonnull, 28.# every session will write to the named collection, as well as enabling you 29.# to schedule future collections from the console. 30.# oracle_trace_enable = true 31.# define directories to store trace and alert files 32.background_dump_dest = e:\oracle\admin\prodx1\bdump 33.#core_dump_dest = e:\oracle\admin\prodx1\cdump 34.user_dump_dest = e:\oracle\admin\prodx1\udump 35.db_block_size = 8192 36.remote_login_passwordfile = exclusive 37.parallel_max_servers = 5 38.# The following parameters are needed for the Advanced Replication Option 39.job_queue_processes = 2 40.job_queue_interval = 60 41.distributed_transactions = 10

42.open_links = 4 43.compatible = 8.1.7.2.0 44.open_cursors=200 45.db_files=96 46.max_enabled_roles=96 47.remote_os_authent=true 48.os_authent_prefix="OPS$" 49.nls_date_format=dd-mon-rrrr 50.sort_area_size=3000000 51.# Make trace files accessible to the world for read 52._trace_files_public=true 53.utl_file_dir = '*' 54.enqueue_resources=2000 You need to modify the initprodx2.ora file for the new database prodx2 by doing the following: Line #s 1, 3, 17, 32, 33, 34: Replace prodx1 with prodx2 The line # 3 has the name and location that you want to use for the new control files. The control files should be given a different name to distinguish them from the prodx1 database. Any parameters of the parameter file will need to be edited to point to the new name of the include file in the new location. In this example we make the assumption that the parameter file doesn't include any. The following shows the modified file listing: 1.db_name = prodx2 2.db_domain = us.oracle.com 3.control_files = ("e:\oradata\prodx2\control01_prodx2.ctl", "e:\oradata\prodx2\control02_prodx2.ctl") 4.db_block_buffers = 10000 5.db_block_lru_latches = 4

6.shared_pool_size = 25000000 7.shared_pool_reserved_size = 2500000 8.java_pool_size = 20000000 9.log_checkpoint_interval = 10000 10.log_checkpoint_timeout = 0 11.processes = 50 12.log_buffer = 163840 13.# audit_trail = false # if you want auditing 14.timed_statistics = true # if you want timed statistics 15.# max_dump_file_size = 10000 # limit trace file size to 5M each 16.#log_archive_start = true 17.#log_archive_dest_1 = "location=T:\oracle\admin\prodx2\arch" 18.#log_archive_format = %t_%s.dbf 19.# If using private rollback segments, place lines of the following 20.# form in each of your instance-specific init.ora files: 21.rollback_segments = (r01, r02, r03, r04) 22.# Global Naming -- enforce that a dblink has same name as the db it connects to 23.global_names = true 24.# Uncomment the following line if you wish to enable the Oracle Trace product 25.# to trace server activity. This enables scheduling of server collections 26.# from the Oracle Enterprise Manager Console. 27.# Also, if the oracle_trace_collection_name parameter is nonnull,

28.# every session will write to the named collection, as well as enabling you 29.# to schedule future collections from the console. 30.# oracle_trace_enable = true 31.# define directories to store trace and alert files 32.background_dump_dest = e:\oracle\admin\prodx2\bdump 33.#core_dump_dest = e:\oracle\admin\prodx2\cdump 34.user_dump_dest = e:\oracle\admin\prodx2\udump 35.db_block_size = 8192 36.remote_login_passwordfile = exclusive 37.parallel_max_servers = 5 38.# The following parameters are needed for the Advanced Replication Option 39.job_queue_processes = 2 40.job_queue_interval = 60 41.distributed_transactions = 10 42.open_links = 4 43.compatible = 8.1.7.2.0 44.open_cursors=200 45.db_files=96 46.max_enabled_roles=96 47.remote_os_authent=true 48.os_authent_prefix="OPS$" 49.nls_date_format=dd-mon-rrrr 50.sort_area_size=3000000 51.# Make trace files accessible to the world for read

52._trace_files_public=true 53.utl_file_dir = '*' 54.enqueue_resources=2000 3.1.7 Modify the Trace Output Script File Likewise, you must modify the recr_con.sql trace script file from Step…“Make a Copy of the Database� which is located under:

•

On Windows Operating System:

e:\oracle\admin\prodx2\udump\recr_con.sql

•

On Unix Operating System:

/u01/app/oracle/admin/prodx2/udump/recr_con.sql This file contains currently the commands for creating the control file for the original database prodx1. A listing of an example control file trace follows. Note, in this example, each line is numbered to aid the reader in correlating and understanding the steps that follow the listing. These line numbers are not actually present in the control file trace; they are added for the purpose of example only. Also, a simplistic example (Windows Operating System only Unix is similar) is shown here to make it easier to understand the steps involved.

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.

Dump file e:\oracle\admin\prodx1\udump\ORA03552.TRC Mon Aug 26 15:04:15 2002 ORACLE V8.1.7.2.1 - Production vsnsta=0 vsnsql=f vsnxtr=3 Windows 2000 Version 5.0 Service Pack 2, CPU type 586 Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production With the Partitioning option JServer Release 8.1.7.2.1 - Production Windows 2000 Version 5.0 Service Pack 2, CPU type 586 Instance name: prodx1 Redo thread mounted by this instance: 1 Oracle process number: 11 Windows thread id: 3552, image: ORACLE.EXE *** SESSION ID:(12.554) 2002-08-26 15:04:15.796 *** 2002-08-26 15:04:15.796

16. 17. 18. 19. 20. 21.

# The following commands will create a new control file and use it # to open the database. # The contents of online logs will be lost and all backups will # be invalidated. Use this only if online logs are damaged. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRODX1" RESETLOGS NOARCHIVELOG 22. MAXLOGFILES 16 23. MAXLOGMEMBERS 2 24. MAXDATAFILES 96 25. MAXINSTANCES 1 26. MAXLOGHISTORY 226 27. LOGFILE 28. GROUP 1 'E:\ORADATA\PRODX1\REDO01.LOG' SIZE 10M, 29. GROUP 2 'E:\ORADATA\PRODX1\REDO02.LOG' SIZE 10M 30. DATAFILE 31. 'E:\ORADATA\PRODX1\SYSTEM01.DBF', 32. 'E:\ORADATA\PRODX1\RBS01.DBF', 33. 'E:\ORADATA\PRODX1\TEMP01.DBF', 34. 'E:\ORADATA\PRODX1\USERS01.DBF', 35. 'E:\ORADATA\PRODX1\TOOLS01.DBF', 36. 'E:\ORADATA\PRODX1\OCL_TEMP1.DBF', 37. 'E:\ORADATA\PRODX1\OCL_APP.DBF', 38. 'E:\ORADATA\PRODX1\OCL_APP_IDX.DBFli> 39. 'E:\ORADATA\PRODX1\OCL_DEF_IDX.DBF', 40. 'E:\ORADATA\PRODX1\OCL_GLIB.DBF', 41. 'E:\ORADATA\PRODX1\OCL_GLIB_IDX.DBF', 42. 'E:\ORADATA\PRODX1\OCL_LI.DBF', 43. 'E:\ORADATA\PRODX1\OCL_LI_IDX.DBF', 44. 'E:\ORADATA\PRODX1\OCL_DCMQ.DBF', 45. 'E:\ORADATA\PRODX1\OCL_DCMQ_IDX.DBF', 46. 'E:\ORADATA\PRODX1\OCL_DCD.DBF', 47. 'E:\ORADATA\PRODX1\OCL_DCD_IDX.DBF', 48. 'E:\ORADATA\PRODX1\OCL_RESP.DBF', 49. 'E:\ORADATA\PRODX1\OCL_RESP_IDX.DBF', 50. 'E:\ORADATA\PRODX1\OCL_DISC.DBF', 51. 'E:\ORADATA\PRODX1\OCL_DISC_IDX.DBF', 52. 'E:\ORADATA\PRODX1\OCL_VRV.DBF', 53. 'E:\ORADATA\PRODX1\OCL_VRV_IDX.DBF', 54. 'E:\ORADATA\PRODX1\OCL_DES.DBF', 55. 'E:\ORADATA\PRODX1\OCL_DES_IDX.DBF', 56. 'E:\ORADATA\PRODX1\OCL_LR.DBF', 57. 'E:\ORADATA\PRODX1\OCL_LR_IDX.DBF', 58. 'E:\ORADATA\PRODX1\OCL_TEST_DATA.DBF', 59. 'E:\ORADATA\PRODX1\OCL_TEST_INDEX.DBF', 60. 'E:\ORADATA\PRODX1\OCL_TEMP_DATA.DBF', 61. 'E:\ORADATA\PRODX1\OCL_DISC_REP_DATA.DBF' 62. CHARACTER SET WE8ISO8859P1 63. ; 64. # Recovery is required if any of the datafiles are restored backups,

65. 66. 67. 68. 69.

# or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE # Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; # No tempfile entries found to add.

You need to modify the trace file script for the new database prodx2 by doing the following: Line #s 1 through 19: Delete the header information and the comments in the script file. Line # 20: Modify the startup command statement to include the initprodx2.ora parameter file as follows: STARTUP NOMOUNT PFILE=e:\oracle\admin\prodx2\pfile\initprodx2.ora Line # 21: Modify the control file command statement to change the database name to the new database, as follows: CREATE CONTROLFILE SET DATABASE "prodx2" RESETLOGS NOARCHIVELOG Line #s 28 and 29: Modify the filenames to point to the names of the redo log files for prodx2. Line #s 31 through 62: Modify the names of the data files to point to the correct names of the data files for prodx2. Line #s 65, 66, and 67: Remove the lines containing the RECOVER DATABASE command and its associated comments. Line #s 68, 70: Delete the lines # 68 and #70 The following shows the modified file listing: 1. 2.

STARTUP NOMOUNT PFILE=e:\oracle\admin\prodx2\pfile\initprodx2.ora CREATE CONTROLFILE SET DATABASE "prodx2" RESETLOGS NOARCHIVELOG 3. MAXLOGFILES 16 4. MAXLOGMEMBERS 2 5. MAXDATAFILES 96 6. MAXINSTANCES 1 7. MAXLOGHISTORY 226 8. LOGFILE 9. GROUP 1 'E:\ORADATA\prodx2\REDO01.LOG' SIZE 10M, 10. GROUP 2 'E:\ORADATA\prodx2\REDO02.LOG' SIZE 10M

11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46.

DATAFILE 'E:\ORADATA\prodx2\SYSTEM01.DBF', 'E:\ORADATA\prodx2\RBS01.DBF', 'E:\ORADATA\prodx2\TEMP01.DBF', 'E:\ORADATA\prodx2\USERS01.DBF', 'E:\ORADATA\prodx2\TOOLS01.DBF', 'E:\ORADATA\prodx2\OCL_TEMP1.DBF', 'E:\ORADATA\prodx2\OCL_APP.DBF', 'E:\ORADATA\prodx2\OCL_APP_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DEF.DBF', 'E:\ORADATA\prodx2\OCL_DEF_IDX.DBF', 'E:\ORADATA\prodx2\OCL_GLIB.DBF', 'E:\ORADATA\prodx2\OCL_GLIB_IDX.DBF', 'E:\ORADATA\prodx2\OCL_LI.DBF', 'E:\ORADATA\prodx2\OCL_LI_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DCMQ.DBF', 'E:\ORADATA\prodx2\OCL_DCMQ_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DCD.DBF', 'E:\ORADATA\prodx2\OCL_DCD_IDX.DBF', 'E:\ORADATA\prodx2\OCL_RESP.DBF', 'E:\ORADATA\prodx2\OCL_RESP_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DISC.DBF', 'E:\ORADATA\prodx2\OCL_DISC_IDX.DBF', 'E:\ORADATA\prodx2\OCL_VRV.DBF', 'E:\ORADATA\prodx2\OCL_VRV_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DES.DBF', 'E:\ORADATA\prodx2\OCL_DES_IDX.DBF', 'E:\ORADATA\prodx2\OCL_LR.DBF', 'E:\ORADATA\prodx2\OCL_LR_IDX.DBF', 'E:\ORADATA\prodx2\OCL_TEST_DATA.DBF', 'E:\ORADATA\prodx2\OCL_TEST_INDEX.DBF', 'E:\ORADATA\prodx2\OCL_TEMP_DATA.DBF', 'E:\ORADATA\prodx2\OCL_DISC_REP_DATA.DBF' CHARACTER SET WE8ISO8859P1 ; ALTER DATABASE OPEN RESETLOGS;

3.1.8 Create the Oracle Service for the New Database (Windows Operating System Only) Set the Oracle environment to the new database prodx2 in the Operating System. Define the following environment variables: ORACLE_HOME Location of Oracle Software ORACLE_SID SID for the new database prodx2 (to be created) Example: C:\> set ORACLE_SID=prodx2 C:\> set ORACLE_HOME=e:\oracle\ora81741

Check whether the initialization parameter REMOTE_LOGIN_PASSWORDFILE in the initprodx2.ora file is set to exclusive or shared. If it is set, then the original database prodx1 was using a password file. Use the oradim command to create a new Oracle SID service for the new database prodx2 with a password file: C:\> %ORACLE_HOME%\bin\oradim -new -sid prodx2 intpwd change_on_install -startmode auto -pfile e:\oracle\admin\prodx2\pfile\initprodx2.ora This command creates the instance. The ultimate goal is to "attach" this instance to the database. The command above will create a password file in the %ORACLE_HOME%\database\pwdprodx2.ora with the change_on_install as the password for the SYS account. If the original database was not using a password file, then use the command: C:\> %ORACLE_HOME%\bin\oradim -new -sid prodx2 -startmode auto -pfile e:\oracle\admin\prodx2\pfile\initprodx2.ora

3.1.9 Update Network Files and Restart the Listener Edit the listener.ora and tnsnames.ora files to mention the newly created instance prodx2. Stop and restart the listener. For Unix only, add an entry for the prodx2 database in the oratab file. In Tru64 and HP-UX , oratab is located in the /etc directory. In Solaris, the oratab file is located in the /var/opt/oracle directory. The line will be similar to this: prodx2:/u01/app/oracle/product/8.1.7.4.0:N NOTE: The instance name is case sensitive. Make sure to match the instance name with the one in the listener.ora, tnsnames.ora and oratab files. 3.1.10 Recreate the Control File, and Open the Database Start the instance and recreate the control file:

•

On Unix Operating System:

Set the Oracle environment to the new database prodx2 in the Operating System. Define the following environment variables:

ORACLE_HOME Location of Oracle Software ORACLE_SID SID for the new database prodx2 (to be created) Example: % source $ORACLE_HOME/bin/coraenv ORACLE_SID = [oracle] ? prodx2 ORACLE_HOME = [/u01/home/oracle] ? /u01/app/oracle/product/8.1.7.4.0 Check whether the initialization parameter REMOTE_LOGIN_PASSWORDFILE in the initprodx2.ora file is set to exclusive or shared. If it is set, then a valid password file should be created as follows: % cd $ORACLE_HOME/dbs % orapwd file=orapwprodx2 password=<SYS account password> entries=5 Connect to the target database's idle instance via svrmgr or SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Example: % $ORACLE_HOME/bin/sqlplus …“sys/change_on_install as sysdba Run the following command: SQL> start /u01/app/oracle/admin/prodx2/udump/recr_con.sql

•

On Windows Operating System:

Set the Oracle environment to the new database prodx2 in the Operating System. Define the following environment variables: ORACLE_HOME Location of Oracle Software ORACLE_SID SID for the new database prodx2 (to be created) Example: C:\> set ORACLE_SID=prodx2 C:\> set ORACLE_HOME=e:\oracle\ora81741 Connect to the target database's idle instance via svrmgr or SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Example: C:\> %ORACLE_HOME%\bin\sqlplus …"sys/change_on_install as sysdba"

Run the following command: SQL> start e:\oracle\admin\prodx2\udump\recr_con.sql This script will do the following: •

Re-create the database control file

•

Reset the online log sequence

•

Mount and open the database

3.1.11 Change the Global Name of the Target Database You must change the global_name of the cloned database by issuing the following statement: SQL> alter database rename global_name to ; In our assumption this statement would be: SQL> alter database rename global_name to prodx2.us.oracle.com; The database prodx2 is now accessible for use. You can start now the source database prodx1. 3.2 Oracle Clinical Specific Steps 3.2.1 Run chown.sql and rxccsd.sql After the cloning process is completed you must change the ownership of the Oracle Clinical objects in the database. The owning location of Oracle Clinical objects in the database will still be what it was in the source database. Depending on whether the source database replicated objects into itself, objects in the cloned database may be owned by more than one owning location. In this step, you want to convert all objects so they are owned by the target database that you are constructing (it is possible that you may want to keep the owning locations just as they were in the source database; if so, skip this step).

• • • • •

Set your current operating system directory to be RXC_TOOLS. Connect to the database as RXC. Run chown.sql. This script will show which objects belong to other databases. Convert the objects from one non-current owning location to the current one. Repeat the previous step until there are no more objects owned by a database other than the current one Run rxccsd.sql. This script will create the study-level code directories if they do not yet exist. If they already exist, then this script will not wreck anything.

3.2.1.1 Runopachown.sql After the cloning process is completed you must run the following script, It is required to update table OPA_SETTINGS using sql opachown.sql for new instace to resolve an issue for this the DCI Forms Definition Enabled and DCI Forms Entry Enabled. Boxes will be checked for the RDC PDF studies. This sql is located in the directory OPA_HOME\oc\45\install.

If you use OC 4.5.0, then follow Note:464323.1 instructions.

Connect to the database as OPA. Run opachown.sql Enter New instace name, when script prompt for new instace name. Convert the records related to the non-current owning location to the current one in table OPA_SETTINGS.

3.2.2 Run oclstate.sql and Generate Table Statistics When the clone process is completed, you must define the Oracle Clinical environment for the database.

• • •

Set your current operating system directory to be RXC_INSTALL Connect to the database as RXC. Run oclstate.sql. This script will correctly define the Oracle Clinical environment for this database. Here are the items to modify:

DB NAME SERVER_OS

Set this to the target SID Set this to the operating system of the RDBMS server for the target database

SERVER_NAME Set this to the name of the RDBMS server

LOCATION CODE

GLIB OWNING LOCATION •

for the target database You have a choice: you can leave the location code as it was at the source database (this would of course be the right choice if you're simply rolling back a database to an earlier state of itself); or you can change the location code to reflect the name of the target database. Do this if youâ€Ã¢„¢ve run chown such that objects owned by the old location are now owned by the new one. Note that the target database name will be the same as the source database name, although their SIDs will differ. Modify this if you have modified the LOCATION CODE

Run the scripts to create table statistics for cost-based-optimization. Connect as user RXC and run the script anarxctab.sql, connect as RXA_DES and run the script anadestab.sql and finally connect as RXA_LR and run the script analrtab.sql.

3.2.3 Run gen_procs Run the gen_procs script (gen_procs.exe on NT) to regenerate all derivation and validation procedures.

• •

Set your current operating system directory to be RXC_TOOLS Enter the following command:

Windows: % gen_procs.exe ALL FULL GENERATE ALL Unix: % gen_procs ALL FULL GENERATE ALL Note that some qualifiers are case-sensitive. For more information on the gen_procs, see "Generating Validation Procedures" in the Oracle Clinical Administrator's Guide

Cloning a Windows/Unix Database (Oracle Clinical ONLY) Document History

Revision Date

Rev

Description of Change

Author

26-Aug-02

A Draft 1 Initial Document Creation John Kizanis

02-Sep-02

A

Final for Approval

John Kizanis

Table of Contents 1. PURPOSE 2. SCOPE AND INTENDED AUDIENCE 3. HOW TO CLONE A DATABASE FROM AN EXISTING DATABASE 3.1 GENERIC STEPS 3.1.1 Backup the Source Database 3.1.2 Backup the Source Database's Control File to a Trace File 3.1.3 Make a List of All of your Data and Redo Log Files 3.1.4 Perform a "Clean" Shutdown of the Source Database 3.1.5 Make a Copy of the Database 3.1.6 Modify the Parameter File for the Cloned Database 3.1.7 Modify the Trace Output Script File 3.1.8 Create the Oracle Service for the New Database (Windows Operating System Only) 3.1.9 Update Network Files and Restart the Listener 3.1.10 Recreate the Control File, and Open the Database 3.1.11 Change the Global Name of the Target Database 3.2 ORACLE CLINICAL SPECIFIC STEPS 3.2.1 Run chown.sql and rxccsd.sql 3.2.2 Run oclstate.sql and Generate Table Statistics 3.2.3 Run gen_procs 1. Purpose This work instruction document describes how you can create another database from an existing database. The new database will be a clone of the original database, so it will have the same tablespaces, data, users, and all other database objects as the original database from which it was cloned. 2. Scope and Intended Audience A database might need to be copied in order to duplicate the production system, for example for testing purposes. The cloning procedures are used for duplicating any Oracle database (with or without Oracle Clinical data). This method is very similar to an offline cold backup of the database. A mandatory condition for the work instructions described in this document is that the source and the target databases must have the same Operating System version, and the

same RDBMS versions. In addition, the ORACLE_HOME path for the target database must be the same with the one used by the source database. For example, the source database prodx1 can be physically located on a machine A with Operating System Solaris 2.8, RDBMS version 8.1.7.4.0, and ORACLE_HOME /u01/app/oracle/product/8.1.7.4.0. The target (cloned) database prodx2 can be created physically on a machine A or B with Operating System Solaris 2.8, RDBMS version 8.1.7.4.0, and ORACLE_HOME /u01/app/oracle/product/8.1.7.4.0. It applies to both Windows and Unix databases. A restriction on this document is that it should not be used to copy a database which is to be backed up using the RMAN (Recovery Manager). RMAN keeps track of databases by their DBID, a number issued solely during the "create database" command. If the copied database is created using Oracle8 or is migrated from Oracle7 to Oracle8, RMAN will not be able to distinguish it from it's original in the RMAN repository (Note: 18070.1). This document applies to everyone who is dealing with databases. 3. How to Clone a Database from an Existing Database In the following steps, we assume the following: • The source database is an existing database with any kind of data, and for the simplicity of this document is called prodx1 •

The target database is called prodx2 and it will be created as a clone of prodx1

•

We use the OFA structure and all administrative files for each database, are located under: o On Windows Operating System:

e:\oracle\admin\<SID>\pfile e:\oracle\admin\<SID>\bdump e:\oracle\admin\<SID>\udump e:\oracle\admin\<SID>\cdump o

On Unix Operating System:

/u01/app/oracle/admin/<SID>/pfile /u01/app/oracle/admin/<SID>/bdump /u01/app/oracle/admin/<SID>/udump /u01/app/oracle/admin/<SID>/cdump where <SID> is the name of a database.

All files are located on a machine with RAID 5 configuration and all data, index redo log and control files are located under the same directory. For the use of this document, let's assume that all files are located under: •On Windows Operating System: e:\oradata\<SID>\ •

o

On Unix Operating System:

/u01/oradata/<SID>/ where <SID> is the name of a database

•

All data and index file names have a suffix of .dbf and the redo log files a suffix of .log

•

For all examples presented in Unix, we make use of the Csh shell

3.1 Generic Steps The following steps on this section must be followed for cloning any database regardless of its data. The next section â€Ã…“Oracle Clinical Specific Stepsâ€Ã‚ÂÂ� applies to databases with Oracle Clinical data only. 3.1.1 Backup the Source Database Before attempting to clone the source database, it is necessary to perform a full backup of the database. This will ensure that no data will be lost if the copying of the database is unsuccessful.

3.1.2 Backup the Source Database's Control File to a Trace File Connect to the source database prodx1 via svrmgr or SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Perform a backup of the control file to trace using the following command: SQL> alter database backup controlfile to trace resetlogs; Examples: •

On Windows Operating System:

C:\> set ORACLE_SID=prodx1 C:\> set ORACLE_HOME=e:\oracle\ora81741 C:\> %ORACLE_HOME%\bin\sqlplus 'sys/change_on_install as sysdba'� SQL> alter database backup controlfile to trace resetlogs; •

On Unix Operating System:

% source $ORACLE_HOME/bin/coraenv ORACLE_SID = [oracle] ? prodx1 ORACLE_HOME = [/u01/home/oracle] ? /u01/app/oracle/product/8.1.7.4.0 % $ORACLE_HOME/bin/sqlplus 'sys/change_on_install as sysdba'� SQL> alter database backup controlfile to trace resetlogs; This will create a text file that we'll use later. The text file has the format ora_.trc or <SID>_ora_.trc or ora.trc depending on the Operating System , where is the number of the process that created this file. The trace file is created in the location specified by the initialization parameter USER_DUMP_DEST. To find the value of this parameter, type the following query: SQL> select name, value from v$parameter where name='user_dump_dest'; Based on our assumptions, the corresponding value to USER_DUMP_DEST will be: •

On Windows Operating System:

e:\oracle\admin\prodx1\udump •

On Unix Operating System:

/u01/app/oracle/admin/prodx1/udump Navigate to the directory selected above, identify the most recent .trc file and rename it to recr_con.sql. •

On Windows Operating System:

C:\> move e:\oracle\admin\prodx1\udump\

e:\oracle\admin\prodx1\udump\recr_con.sql •

On Unix Operating System:

% mv /u01/app/oracle/admin/prodx1/udump/ /u01/app/oracle/admin/prodx1/udump/recr_con.sql 3.1.3 Make a List of All of your Data and Redo Log Files It is absolutely essential to know the names and locations of all data files and redo log files. To do this, issue the following queries (you must connect as a user (i.e. SYS) who has sufficient privileges to see the V$ views): SQL> set pagesize 2000 SQL> spool files.log SQL> select name from v$datafile; SQL> select member from v$logfile; SQL> spool off The first select query will give the full path and file name for each of your data files (data and indexes). The second query will give the same information for all redo log files. Be sure to record this information for future reference. 3.1.4 Perform a "Clean" Shutdown of the Source Database A clean shutdown is necessary in order to make sure all data files and control files are synchronized with the most recent transactions. The following command will give you a clean shutdown: SQL> shutdown immediate; You must connect as a user (i.e. SYS) who has privileges (i.e. SYSDBA) to shut down the database. 3.1.5 Make a Copy of the Database Copy all data and redo log files noted in step 'Make a List of All of your Data and Redo Log Files'� to their new location making sure you preserve ownership and permissions. Do not copy any control file. You must first create the directories to for the target database prodx2, to save the administrative files, the data and log files. Issue the following commands: •

On Windows Operating System:

- Create administrative directories: % mkdir e:\oracle\admin\prodx2 % mkdir e:\oracle\admin\prodx2\bdump % mkdir e:\oracle\admin\prodx2\udump % mkdir e:\oracle\admin\prodx2\cdump % mkdir e:\oracle\admin\prodx2\pfile - Create data file directory: % mkdir e:\oradata\prodx2 - Copy Database Files C:> copy e:\oradata\prodx1\*.dbf e:\oradata\prodx2\ C:> copy e:\oradata\prodx1\*.log e:\oradata\prodx2\ C:> copy e:\oracle\admin\prodx1\pfile\initprodx1.ora e:\oracle\admin\prodx2\pfile\initprodx2.ora C:> copy e:\oracle\admin\prodx1\udump\recr_con.sql e:\oracle\admin\prodx2\udump\recr_con.sql •

On Unix Operating System:

- Create administrative directories: % mkdir /u01/app/oracle/admin/prodx2 % mkdir /u01/app/oracle/admin/prodx2/bdump % mkdir /u01/app/oracle/admin/prodx2/udump % mkdir /u01/app/oracle/admin/prodx2/cdump % mkdir /u01/app/oracle/admin/prodx2/pfile - Create data file directory: % mkdir /u01/oradata/prodx2 - Copy Database Files % cp /u01/oradata/prodx1/*.dbf /u01/oradata/prodx2/ % cp /u01/oradata/prodx1/*.log /u01/oradata/prodx2/ % cp /u01/app/oracle/admin/prodx1/pfile/initprodx1.ora /u01/app/oracle/admin/prodx2/pfile/initprodx2.ora % cp /u01/app/oracle/admin/prodx1/udump/recr_con.sql /u01/app/oracle/admin/prodx2/udump/recr_con.sql - Create symbolic link for the parameter file % ln /u01/app/oracle/admin/prodx2/pfile/initprodx2.ora $ORACLE_HOME/dbs/initprodx2.ora where $ORACLE_HOME is the full path of the RDBMS home installation for prodx1 (i.e. /u01/app/oracle/product/8.1.7.4.0) 3.1.6 Modify the Parameter File for the Cloned Database

Using a text editor, revise the initprodx2.ora parameter file to reflect the new values for the target database. • On Windows Operating System: e:\oracle\admin\prodx2\pfile\initprodx2.ora • On Unix Operating System: /u01/app/oracle/admin/prodx2/pfile/initprodx2.ora This will involve changing the paths indicated in parameters such as CONTROL_FILES, USER_DUMP_DEST, BACKGROUND_DUMP_DEST, and any other parameter that specifies a path/file location. A listing of an example initprodx2.ora follows. Note, in this example, each line is numbered to aid the reader in correlating and understanding the steps that follow the listing. These line numbers are not actually present in the initprodx2.ora file; they are added for the purpose of example only. Also, a simplistic example (Windows Operating System only Unix is similar) is shown here to make it easier to understand the steps involved. 1.db_name = prodx1 2.db_domain = us.oracle.com 3.control_files = ("e:\oradata\prodx1\control01_prodx1.ctl", "e:\oradata\prodx1\control02_prodx1.ctl") 4.db_block_buffers = 10000 5.db_block_lru_latches = 4 6.shared_pool_size = 25000000 7.shared_pool_reserved_size = 2500000 8.java_pool_size = 20000000 9.log_checkpoint_interval = 10000 10.log_checkpoint_timeout = 0 11.processes = 50 12.log_buffer = 163840 13.# audit_trail = false # if you want auditing 14.timed_statistics = true # if you want timed statistics 15.# max_dump_file_size = 10000 # limit trace file size to 5M each 16.#log_archive_start = true 17.#log_archive_dest_1 = "location=T:\oracle\admin\prodx1\arch" 18.#log_archive_format = %t_%s.dbf 19.# If using private rollback segments, place lines of the following 20.# form in each of your instance-specific init.ora files: 21.rollback_segments = (r01, r02, r03, r04) 22.# Global Naming -- enforce that a dblink has same name as the db it connects to 23.global_names = true 24.# Uncomment the following line if you wish to enable the Oracle Trace product 25.# to trace server activity. This enables scheduling of server collections 26.# from the Oracle Enterprise Manager Console.

27.# Also, if the oracle_trace_collection_name parameter is nonnull, 28.# every session will write to the named collection, as well as enabling you 29.# to schedule future collections from the console. 30.# oracle_trace_enable = true 31.# define directories to store trace and alert files 32.background_dump_dest = e:\oracle\admin\prodx1\bdump 33.#core_dump_dest = e:\oracle\admin\prodx1\cdump 34.user_dump_dest = e:\oracle\admin\prodx1\udump 35.db_block_size = 8192 36.remote_login_passwordfile = exclusive 37.parallel_max_servers = 5 38.# The following parameters are needed for the Advanced Replication Option 39.job_queue_processes = 2 40.job_queue_interval = 60 41.distributed_transactions = 10 42.open_links = 4 43.compatible = 8.1.7.2.0 44.open_cursors=200 45.db_files=96 46.max_enabled_roles=96 47.remote_os_authent=true 48.os_authent_prefix="OPS$" 49.nls_date_format=dd-mon-rrrr 50.sort_area_size=3000000 51.# Make trace files accessible to the world for read 52._trace_files_public=true 53.utl_file_dir = '*' 54.enqueue_resources=2000 You need to modify the initprodx2.ora file for the new database prodx2 by doing the following: Line #s 1, 3, 17, 32, 33, 34: Replace prodx1 with prodx2 The line # 3 has the name and location that you want to use for the new control files. The control files should be given a different name to distinguish them from the prodx1 database. Any parameters of the parameter file will need to be edited to point to the new name of the include file in the new location. In this example we make the assumption that the parameter file doesn't include any. The following shows the modified file listing: 1.db_name = prodx2 2.db_domain = us.oracle.com 3.control_files = ("e:\oradata\prodx2\control01_prodx2.ctl", "e:\oradata\prodx2\control02_prodx2.ctl")

4.db_block_buffers = 10000 5.db_block_lru_latches = 4 6.shared_pool_size = 25000000 7.shared_pool_reserved_size = 2500000 8.java_pool_size = 20000000 9.log_checkpoint_interval = 10000 10.log_checkpoint_timeout = 0 11.processes = 50 12.log_buffer = 163840 13.# audit_trail = false # if you want auditing 14.timed_statistics = true # if you want timed statistics 15.# max_dump_file_size = 10000 # limit trace file size to 5M each 16.#log_archive_start = true 17.#log_archive_dest_1 = "location=T:\oracle\admin\prodx2\arch" 18.#log_archive_format = %t_%s.dbf 19.# If using private rollback segments, place lines of the following 20.# form in each of your instance-specific init.ora files: 21.rollback_segments = (r01, r02, r03, r04) 22.# Global Naming -- enforce that a dblink has same name as the db it connects to 23.global_names = true 24.# Uncomment the following line if you wish to enable the Oracle Trace product 25.# to trace server activity. This enables scheduling of server collections 26.# from the Oracle Enterprise Manager Console. 27.# Also, if the oracle_trace_collection_name parameter is nonnull, 28.# every session will write to the named collection, as well as enabling you 29.# to schedule future collections from the console. 30.# oracle_trace_enable = true 31.# define directories to store trace and alert files 32.background_dump_dest = e:\oracle\admin\prodx2\bdump 33.#core_dump_dest = e:\oracle\admin\prodx2\cdump 34.user_dump_dest = e:\oracle\admin\prodx2\udump 35.db_block_size = 8192 36.remote_login_passwordfile = exclusive 37.parallel_max_servers = 5 38.# The following parameters are needed for the Advanced Replication Option 39.job_queue_processes = 2 40.job_queue_interval = 60 41.distributed_transactions = 10 42.open_links = 4 43.compatible = 8.1.7.2.0 44.open_cursors=200 45.db_files=96 46.max_enabled_roles=96

47.remote_os_authent=true 48.os_authent_prefix="OPS$" 49.nls_date_format=dd-mon-rrrr 50.sort_area_size=3000000 51.# Make trace files accessible to the world for read 52._trace_files_public=true 53.utl_file_dir = '*' 54.enqueue_resources=2000 3.1.7 Modify the Trace Output Script File Likewise, you must modify the recr_con.sql trace script file from Step…“Make a Copy of the Database� which is located under: • On Windows Operating System: e:\oracle\admin\prodx2\udump\recr_con.sql

• On Unix Operating System: /u01/app/oracle/admin/prodx2/udump/recr_con.sql

This file contains currently the commands for creating the control file for the original database prodx1. A listing of an example control file trace follows. Note, in this example, each line is numbered to aid the reader in correlating and understanding the steps that follow the listing. These line numbers are not actually present in the control file trace; they are added for the purpose of example only. Also, a simplistic example (Windows Operating System only Unix is similar) is shown here to make it easier to understand the steps involved.

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16.

Dump file e:\oracle\admin\prodx1\udump\ORA03552.TRC Mon Aug 26 15:04:15 2002 ORACLE V8.1.7.2.1 - Production vsnsta=0 vsnsql=f vsnxtr=3 Windows 2000 Version 5.0 Service Pack 2, CPU type 586 Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production With the Partitioning option JServer Release 8.1.7.2.1 - Production Windows 2000 Version 5.0 Service Pack 2, CPU type 586 Instance name: prodx1 Redo thread mounted by this instance: 1 Oracle process number: 11 Windows thread id: 3552, image: ORACLE.EXE *** SESSION ID:(12.554) 2002-08-26 15:04:15.796 *** 2002-08-26 15:04:15.796 # The following commands will create a new control file and use it

17. 18. 19. 20. 21.

# to open the database. # The contents of online logs will be lost and all backups will # be invalidated. Use this only if online logs are damaged. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PRODX1" RESETLOGS NOARCHIVELOG 22. MAXLOGFILES 16 23. MAXLOGMEMBERS 2 24. MAXDATAFILES 96 25. MAXINSTANCES 1 26. MAXLOGHISTORY 226 27. LOGFILE 28. GROUP 1 'E:\ORADATA\PRODX1\REDO01.LOG' SIZE 10M, 29. GROUP 2 'E:\ORADATA\PRODX1\REDO02.LOG' SIZE 10M 30. DATAFILE 31. 'E:\ORADATA\PRODX1\SYSTEM01.DBF', 32. 'E:\ORADATA\PRODX1\RBS01.DBF', 33. 'E:\ORADATA\PRODX1\TEMP01.DBF', 34. 'E:\ORADATA\PRODX1\USERS01.DBF', 35. 'E:\ORADATA\PRODX1\TOOLS01.DBF', 36. 'E:\ORADATA\PRODX1\OCL_TEMP1.DBF', 37. 'E:\ORADATA\PRODX1\OCL_APP.DBF', 38. 'E:\ORADATA\PRODX1\OCL_APP_IDX.DBFli> 39. 'E:\ORADATA\PRODX1\OCL_DEF_IDX.DBF', 40. 'E:\ORADATA\PRODX1\OCL_GLIB.DBF', 41. 'E:\ORADATA\PRODX1\OCL_GLIB_IDX.DBF', 42. 'E:\ORADATA\PRODX1\OCL_LI.DBF', 43. 'E:\ORADATA\PRODX1\OCL_LI_IDX.DBF', 44. 'E:\ORADATA\PRODX1\OCL_DCMQ.DBF', 45. 'E:\ORADATA\PRODX1\OCL_DCMQ_IDX.DBF', 46. 'E:\ORADATA\PRODX1\OCL_DCD.DBF', 47. 'E:\ORADATA\PRODX1\OCL_DCD_IDX.DBF', 48. 'E:\ORADATA\PRODX1\OCL_RESP.DBF', 49. 'E:\ORADATA\PRODX1\OCL_RESP_IDX.DBF', 50. 'E:\ORADATA\PRODX1\OCL_DISC.DBF', 51. 'E:\ORADATA\PRODX1\OCL_DISC_IDX.DBF', 52. 'E:\ORADATA\PRODX1\OCL_VRV.DBF', 53. 'E:\ORADATA\PRODX1\OCL_VRV_IDX.DBF', 54. 'E:\ORADATA\PRODX1\OCL_DES.DBF', 55. 'E:\ORADATA\PRODX1\OCL_DES_IDX.DBF', 56. 'E:\ORADATA\PRODX1\OCL_LR.DBF', 57. 'E:\ORADATA\PRODX1\OCL_LR_IDX.DBF', 58. 'E:\ORADATA\PRODX1\OCL_TEST_DATA.DBF', 59. 'E:\ORADATA\PRODX1\OCL_TEST_INDEX.DBF', 60. 'E:\ORADATA\PRODX1\OCL_TEMP_DATA.DBF', 61. 'E:\ORADATA\PRODX1\OCL_DISC_REP_DATA.DBF' 62. CHARACTER SET WE8ISO8859P1 63. ; 64. # Recovery is required if any of the datafiles are restored backups, 65. # or if the last shutdown was not normal or immediate.

66. 67. 68. 69.

RECOVER DATABASE USING BACKUP CONTROLFILE # Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; # No tempfile entries found to add.

You need to modify the trace file script for the new database prodx2 by doing the following: Line #s 1 through 19: Delete the header information and the comments in the script file. Line # 20: Modify the startup command statement to include the initprodx2.ora parameter file as follows: STARTUP NOMOUNT PFILE=e:\oracle\admin\prodx2\pfile\initprodx2.ora Line # 21: Modify the control file command statement to change the database name to the new database, as follows: CREATE CONTROLFILE SET DATABASE "prodx2" RESETLOGS NOARCHIVELOG Line #s 28 and 29: Modify the filenames to point to the names of the redo log files for prodx2. Line #s 31 through 62: Modify the names of the data files to point to the correct names of the data files for prodx2. Line #s 65, 66, and 67: Remove the lines containing the RECOVER DATABASE command and its associated comments. Line #s 68, 70: Delete the lines # 68 and #70 The following shows the modified file listing: 1. 2.

STARTUP NOMOUNT PFILE=e:\oracle\admin\prodx2\pfile\initprodx2.ora CREATE CONTROLFILE SET DATABASE "prodx2" RESETLOGS NOARCHIVELOG 3. MAXLOGFILES 16 4. MAXLOGMEMBERS 2 5. MAXDATAFILES 96 6. MAXINSTANCES 1 7. MAXLOGHISTORY 226 8. LOGFILE 9. GROUP 1 'E:\ORADATA\prodx2\REDO01.LOG' SIZE 10M, 10. GROUP 2 'E:\ORADATA\prodx2\REDO02.LOG' SIZE 10M 11. DATAFILE

12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46.

'E:\ORADATA\prodx2\SYSTEM01.DBF', 'E:\ORADATA\prodx2\RBS01.DBF', 'E:\ORADATA\prodx2\TEMP01.DBF', 'E:\ORADATA\prodx2\USERS01.DBF', 'E:\ORADATA\prodx2\TOOLS01.DBF', 'E:\ORADATA\prodx2\OCL_TEMP1.DBF', 'E:\ORADATA\prodx2\OCL_APP.DBF', 'E:\ORADATA\prodx2\OCL_APP_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DEF.DBF', 'E:\ORADATA\prodx2\OCL_DEF_IDX.DBF', 'E:\ORADATA\prodx2\OCL_GLIB.DBF', 'E:\ORADATA\prodx2\OCL_GLIB_IDX.DBF', 'E:\ORADATA\prodx2\OCL_LI.DBF', 'E:\ORADATA\prodx2\OCL_LI_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DCMQ.DBF', 'E:\ORADATA\prodx2\OCL_DCMQ_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DCD.DBF', 'E:\ORADATA\prodx2\OCL_DCD_IDX.DBF', 'E:\ORADATA\prodx2\OCL_RESP.DBF', 'E:\ORADATA\prodx2\OCL_RESP_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DISC.DBF', 'E:\ORADATA\prodx2\OCL_DISC_IDX.DBF', 'E:\ORADATA\prodx2\OCL_VRV.DBF', 'E:\ORADATA\prodx2\OCL_VRV_IDX.DBF', 'E:\ORADATA\prodx2\OCL_DES.DBF', 'E:\ORADATA\prodx2\OCL_DES_IDX.DBF', 'E:\ORADATA\prodx2\OCL_LR.DBF', 'E:\ORADATA\prodx2\OCL_LR_IDX.DBF', 'E:\ORADATA\prodx2\OCL_TEST_DATA.DBF', 'E:\ORADATA\prodx2\OCL_TEST_INDEX.DBF', 'E:\ORADATA\prodx2\OCL_TEMP_DATA.DBF', 'E:\ORADATA\prodx2\OCL_DISC_REP_DATA.DBF' CHARACTER SET WE8ISO8859P1 ; ALTER DATABASE OPEN RESETLOGS;

3.1.8 Create the Oracle Service for the New Database (Windows Operating System Only) Set the Oracle environment to the new database prodx2 in the Operating System. Define the following environment variables: ORACLE_HOME Location of Oracle Software ORACLE_SID SID for the new database prodx2 (to be created) Example:

C:\> set ORACLE_SID=prodx2 C:\> set ORACLE_HOME=e:\oracle\ora81741 Check whether the initialization parameter REMOTE_LOGIN_PASSWORDFILE in the initprodx2.ora file is set to exclusive or shared. If it is set, then the original database prodx1 was using a password file. Use the oradim command to create a new Oracle SID service for the new database prodx2 with a password file: C:\> %ORACLE_HOME%\bin\oradim -new -sid prodx2 intpwd change_on_install -startmode auto -pfile e:\oracle\admin\prodx2\pfile\initprodx2.ora This command creates the instance. The ultimate goal is to "attach" this instance to the database. The command above will create a password file in the %ORACLE_HOME%\database\pwdprodx2.ora with the change_on_install as the password for the SYS account. If the original database was not using a password file, then use the command: C:\> %ORACLE_HOME%\bin\oradim -new -sid prodx2 -startmode auto -pfile e:\oracle\admin\prodx2\pfile\initprodx2.ora 3.1.9 Update Network Files and Restart the Listener Edit the listener.ora and tnsnames.ora files to mention the newly created instance prodx2. Stop and restart the listener. For Unix only, add an entry for the prodx2 database in the oratab file. In Tru64 and HP-UX , oratab is located in the /etc directory. In Solaris, the oratab file is located in the /var/opt/oracle directory. The line will be similar to this: prodx2:/u01/app/oracle/product/8.1.7.4.0:N NOTE: The instance name is case sensitive. Make sure to match the instance name with the one in the listener.ora, tnsnames.ora and oratab files. 3.1.10 Recreate the Control File, and Open the Database Start the instance and recreate the control file: •

On Unix Operating System:

Set the Oracle environment to the new database prodx2 in the Operating System. Define the following environment variables:

ORACLE_HOME Location of Oracle Software ORACLE_SID SID for the new database prodx2 (to be created) Example: % source $ORACLE_HOME/bin/coraenv ORACLE_SID = [oracle] ? prodx2 ORACLE_HOME = [/u01/home/oracle] ? /u01/app/oracle/product/8.1.7.4.0 Check whether the initialization parameter REMOTE_LOGIN_PASSWORDFILE in the initprodx2.ora file is set to exclusive or shared. If it is set, then a valid password file should be created as follows: % cd $ORACLE_HOME/dbs % orapwd file=orapwprodx2 password=<SYS account password> entries=5 Connect to the target database's idle instance via svrmgr or SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Example: % $ORACLE_HOME/bin/sqlplus …“sys/change_on_install as sysdba Run the following command: SQL> start /u01/app/oracle/admin/prodx2/udump/recr_con.sql

•

On Windows Operating System:

Set the Oracle environment to the new database prodx2 in the Operating System. Define the following environment variables: ORACLE_HOME Location of Oracle Software ORACLE_SID SID for the new database prodx2 (to be created) Example: C:\> set ORACLE_SID=prodx2 C:\> set ORACLE_HOME=e:\oracle\ora81741 Connect to the target database's idle instance via svrmgr or SQL*Plus using an account that has the SYSDBA, SYSOPER, or DBA role granted to it. Example: C:\> %ORACLE_HOME%\bin\sqlplus …"sys/change_on_install as sysdba"

Run the following command: SQL> start e:\oracle\admin\prodx2\udump\recr_con.sql This script will do the following: •

Re-create the database control file

•

Reset the online log sequence

•

Mount and open the database

3.1.11 Change the Global Name of the Target Database You must change the global_name of the cloned database by issuing the following statement: SQL> alter database rename global_name to ; In our assumption this statement would be: SQL> alter database rename global_name to prodx2.us.oracle.com; The database prodx2 is now accessible for use. You can start now the source database prodx1. 3.2 Oracle Clinical Specific Steps 3.2.1 Run chown.sql and rxccsd.sql After the cloning process is completed you must change the ownership of the Oracle Clinical objects in the database. The owning location of Oracle Clinical objects in the database will still be what it was in the source database. Depending on whether the source database replicated objects into itself, objects in the cloned database may be owned by more than one owning location. In this step, you want to convert all objects so they are owned by the target database that you are constructing (it is possible that you may want to keep the owning locations just as they were in the source database; if so, skip this step). • • •

Set your current operating system directory to be RXC_TOOLS. Connect to the database as RXC. Run chown.sql. This script will show which objects belong to other databases. Convert the objects from one non-current owning location to the current one.

• •

Repeat the previous step until there are no more objects owned by a database other than the current one Run rxccsd.sql. This script will create the study-level code directories if they do not yet exist. If they already exist, then this script will not wreck anything.

3.2.1.1 Runopachown.sql After the cloning process is completed you must run the following script, It is required to update table OPA_SETTINGS using sql opachown.sql for new instace to resolve an issue for this the DCI Forms Definition Enabled and DCI Forms Entry Enabled. Boxes will be checked for the RDC PDF studies. This sql is located in the directory OPA_HOME\oc\45\install.

If you use OC 4.5.0, then follow Note:464323.1 instructions. Connect to the database as OPA. Run opachown.sql Enter New instace name, when script prompt for new instace name. Convert the records related to the non-current owning location to the current one in table OPA_SETTINGS.

3.2.2 Run oclstate.sql and Generate Table Statistics When the clone process is completed, you must define the Oracle Clinical environment for the database. • • •

Set your current operating system directory to be RXC_INSTALL Connect to the database as RXC. Run oclstate.sql. This script will correctly define the Oracle Clinical environment for this database. Here are the items to modify:

DB NAME SERVER_OS

SERVER_NAME LOCATION CODE

Set this to the target SID Set this to the operating system of the RDBMS server for the target database Set this to the name of the RDBMS server for the target database You have a choice: you can leave the location code as it was at the source database (this would of course be the right choice if you're simply rolling back a database to an earlier state of itself); or you can change the location code to reflect the name of the target database. Do this if youâ€Ã¢„¢ve run chown such that objects owned by the old location are now owned by the new one. Note that the target database name will be

the same as the source database name, although their SIDs will differ. GLIB OWNING Modify this if you have modified the LOCATION LOCATION CODE • Run the scripts to create table statistics for cost-based-optimization. Connect as user RXC and run the script anarxctab.sql, connect as RXA_DES and run the script anadestab.sql and finally connect as RXA_LR and run the script analrtab.sql. 3.2.3 Run gen_procs Run the gen_procs script (gen_procs.exe on NT) to regenerate all derivation and validation procedures. • •

Set your current operating system directory to be RXC_TOOLS Enter the following command:

Windows: % gen_procs.exe ALL FULL GENERATE ALL Unix: % gen_procs ALL FULL GENERATE ALL Note that some qualifiers are case-sensitive. For more information on the gen_procs, see "Generating Validation Procedures" in the Oracle Clinical Administrator's Guide