Subject:            Copying an Oracle7 Database on OpenVMS
Author:            DHAYTER
Modified:           11 Mar 96 07:19:29           



This article provides succinct details of the necessary steps required to
make a copy of an Oracle7 database on OpenVMS (VAX or ALPHA).

WARNING:  It is recommended that databases created by this method are NOT
          used in a live 'production' situation.   If a copy database is
          required for such a live situation, the recommended method is
          via EXPORT and IMPORT.   The following steps should only be
          carried out by an experienced Oracle Database Administrator who
          is also very familiar with OpenVMS.

For the purposes of this article we'll assume that the database to be
copied has the name LIVEDB and the sid LIVE.  We'll also assume also that
the copy database has the name COPYDB and the sid COPY.

1.  Ensure that your machine has sufficient resources (disk space,
    global pages and global sections) to support a second copy of your
    database.

    Global pages & sections should be checked as follows:

             Ensure that you have at least 10 free global sections
             and sufficient contiguous free global pages to cover
             the size of your SGA:

             $ WRITE SYS$OUTPUT F$GETSYI("FREE_GBLSECTS")
             $ WRITE SYS$OUTPUT F$GETSYI("CONTIG_GBLPAGES")

2.  With the LIVEDB database up and running issue the following commands
    in SQL*Plus (connected as SYS) or SQLDBA (connect internal):
    ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
    SPOOL LIVEDB_FILES.LIS
    SELECT NAME FROM V$DBFILE
    UNION
    SELECT MEMBER FROM V$LOGFILE;
    UNION
    SELECT NAME FROM V$CONTROLFILE;
    EXIT

    Note:  V$CONTROLFILE is not available in versions before 7.0.16.

3.  SHUTDOWN (NORMAL) and take a cold backup of the LIVEDB database
    including all data, redo log and control files listed in
    LIVEDB_FILES.LIS.

4.  Copy ALL the data and redolog files listed in LIVEDB_FILES.LIS to the
    designated new location(s) for the COPYDB database.

    Note:  Whilst it is not strictly necessary to copy the redo log files
           (see note in section 10), it is suggested for completeness.

5.  Restart the LIVEDB database.

6.  Use ORACLEINS to create a new database called COPYDB with the sid COPY
    but abandon the database creation right at the last minute, ie. when
    you see the following message and prompt:

  Continuing will initialize your database.

  Do you want to continue (Y/N)? [Y]      <-- answer with N
                                                         ***

    (The steps within ORACLEINS required to reach this point are: 3,2,1)

7.  Find the files ORA_ROOT:[DB_COPYDB]ORAUSER_COPYDB.COM and
    ORA_DB_COPYDB.COM and execute them.  This will establish all the 
    required logical names for the environment of the new database.

8.  Copy the *INIT*.ORA files from the DB_LIVEDB directory into the
    DB_COPYDB directory and modify everything which refers to the LIVEDB
    database (eg. DB_NAME, CONTROLFILES, USER_DUMP_DEST etc).  Ensure that
    the <node>_LIVE_INIT.ORA file is renamed <node>_COPY_INIT.ORA.
    Also ensure that the parameter DB_NAME exists within one of the
    *INIT*.ORA files (preferably in INIT.ORA) and that it is correctly
    defined as the name of the new database.

9.  Copy the trace file created in step 2 to CREATE_COPY_CONTROLFILE.SQL
    and use the editor cut out everything except the STARTUP NOMOUNT and
    the CREATE CONTROLFILE statements (in their entirity).  Modify the
    latter to CREATE CONTROLFILE REUSE SET DATABASE COPYDB RESETLOGS ...
    (note the addition of the word SET as well as the change of name).
    Also modify the file locations specified within the CREATE CONTROLFILE
    statement to use the new (COPYDB) file locations.

    Note: If the above file locations are not modified accurately,
    everything will end in tears.

10. $ SQLDBA LMODE=Y
    SQLDBA> CONNECT INTERNAL
    SQLDBA> @CREATE_COPY_CONTROLFILE.SQL
    SQLDBA> RECOVER DATABASE USING BACKUP CONTROLFILE;
                (need to cancel this by responding with CANCEL)
    SQLDBA> ALTER DATABASE OPEN RESETLOGS;
    SQLDBA> SHUTDOWN NORMAL
    SQLDBA> EXIT

    If step 10 fails during STARTUP MOUNT or CREATE CONTROLFILE due to
    resource problems (global pages, quota problems etc), correct this
    situation and restart step 10.

    Note: The RESETLOGS option causes the redo logs to be re-initialised.
          If redo logs are not present at this point they are created.

11. $ SQLDBA LMODE=Y
    SQLDBA> CONNECT INTERNAL
    SQLDBA> STARTUP
    SQLDBA> SELECT FILE_NAME FROM DBA_DATA_FILES;
             (to check that datafiles are pointing to the correct location)
    SQLDBA> SELECT MEMBER FROM V$LOGFILE;
             (to check that logfiles are in the correct location)

12. Re-establish the LIVEDB environment (ORAUSER_LIVEDB and ORA_DB_LIVEDB)

13. Check the locations of redo logfiles and datafiles in LIVEDB using the
    same SELECT statements as step 11 above.