Re: Copy of an Oracle database

From: Kurt Sun <sunk_at_focushope.edu>
Date: Sat, 21 Jul 2001 21:36:28 GMT
Message-ID: <390d95fb.0107130944.3a839d39@posting.google.com>

kurt-erich.finger_at_otelo-online.de (Kurt-Erich Finger) wrote in message news:<de2fe149.0107130449.4aa6f1b8_at_posting.google.com>...
> Hello,
>
> I tried to copy an existing database to a new location on the same server
> (Win NT, Oracle 8.0.5)
>
> I did an "alter database backup controlfile to trace, shut down the database
> and copied all datafile and logfiles to the new location.
> In the trace file I re-set the ID and changed the path:
>
> STARTUP NOMOUNT
> CREATE CONTROLFILE set DATABASE "PLAY" RESETLOGS NOARCHIVELOG
> MAXLOGFILES 32
> MAXLOGMEMBERS 5
> MAXDATAFILES 254
> MAXINSTANCES 2
> MAXLOGHISTORY 955
> LOGFILE
> GROUP 3 'D:\training\LOGORCL1B.ORA' SIZE 10M,
> etc.
> DATAFILE
> 'D:\training\SYS1ORCL.DBF',
> 'D:\training\RBS1ORCL.DBF',
> etc
> ;
> ALTER DATABASE OPEN resetlogs;
>
> When I ran the script I got:
>
> CREATE CONTROLFILE set DATABASE "PLAY" RESETLOGS NOARCHIVELOG
> ORA-01503: create controlfile failed
> ORA-01158: database already mounted.
>
> The database was not mounted.
> Running the script after SHUTDOWN gave the same result.
>
> A database PLAY already existed in \training, but I deleted all files.
> The INITPLAY also points to the right directory.
>
> What did I do wrong?
>
>
> Thanks in advance
>
> Kurt-Erich Finger

HTH
Regards, Kurt

The following article deals with creating a copy of a database on the same
WinNT machine. A database might need to be copied in order to duplicate the
production system, for example for testing purposes.

OVERVIEW OF PROCEDURE

Before copying the database to a new location, it is necessary to perform a
full cold backup of the database, whilst the database is shutdown. This will
ensure that no data will be lost if the copying of the database is unsuccessful.

WARNING

Creating a copy of a database involves usage of the CREATE CONTROLFILE command
(explained below). If this command is not executed correctly it could corrupt
the production database. If this happens, and if the files in question are
important, this will mean that the original database will need to be restored
from a backup.

OBTAIN DATABASE INFORMATION FROM CONTROLFILE

In order to move the database, it is necessary to create a script containing
information about the files of the database.

This is done by executing the following commands.

SVRMGRxx
CONNECT INTERNAL
STARTUP MOUNT PFILE=<fullpath>
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

This will create a trace file in the trace file directory. The file will have the extension ".trc" and is located either in the directory defined by the initialization parameter 'user_dump_dest', or, if this is not
defined in "ORACLE_HOME/rdbms/log," the file should be copied to a name such
as "ccf<NEW_SID>.sql," where <NEW_SID> is to be the "ORACLE_SID" of the copied
database.

2.     IDENTIFY FILES TO BACKUP/COPY
       -----------------------------

Identify database and log files

The CREATE CONTROLFILE command in the file "ccf<NEW_SID>.sql" can then be used to identify the various database files and redo log files that need
to be backed up/copied. The file names will be in single quotes and separated
by commas after the words LOGFILE and DATAFILE, e.g:

        CREATE CONTROLFILE REUSE DATABASE "TARGET" RESETLOGS          
         ARCHIVELOG
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXDATAFILES 254
        MAXINSTANCES 1
        MAXLOGHISTORY 449
        LOGFILE
        GROUP 1 'C:\ORANT84\TARGET\DATABASE\LOGTARG1.ORA'  SIZE 1M,
        GROUP 2 'C:\ORANT84\TARGET\DATABASE\LOGTARG2.ORA'  SIZE 1M
        DATAFILE
          'C:\ORANT84\TARGET\DATABASE\SYS1TARG.ORA',
          'C:\ORANT84\TARGET\DATABASE\RBS1TARG.ORA',
          'C:\ORANT84\TARGET\DATABASE\USR1TARG.ORA',
          'C:\ORANT84\TARGET\DATABASE\TMP1TARG.ORA',
          'C:\ORANT84\TARGET\DATABASE\INDX1TARG.ORA',
          'C:\ORANT84\TARGET\DATABASE\TOOLTARG.ORA'
      ;

b. Identify controlfiles

        This can be done either by referring to the "init<SID>.ora"
        'control_files' parameter or, from 7.0.16 onwards, the table
        "sys.v$controlfile," used to identify the controlfiles of the 
        database via the following statement:

        SPOOL control.log
        SELECT name FROM v$controlfile;
        SPOOL OFF

This will create a file called "control.log" in the current directory that will contain the names of the controlfiles for the database.

3. BACKUP EXISTING DATABASE

Shutdown instance via SVRMGRxx, SHUTDOWN NORMAL, and then take a full cold
backup of:

All the files identified in step 2 above.
All parameter files.

Note: The main parameter file will usually be called "init<SID>.ora"

              in addition to other parameter files.  These will be 
              identified by 'ifile'(included file) parameters in the 
              "init<SID>.ora". These additional parameter files are
usually
              called "config<SID>.ora".

4. MAKE A COPY OF THE DATABASE

Make sure database is shutdown immediate and all services are stopped before copying.

Copy all parameter files and all files noted in step 2 above to their new
location, taking care to preserve ownership and permissions. The copied
"init<SID>.ora" must be renamed to "init<NEW_SID>.ora," and any parameter files pointed to by an 'ifile' parameter (e.g. parameter files such
as "config<SID>.ora") should be renamed to incorporate the "NEW_SID" (i.e.
"config<NEW_SID>.ora").

The datafiles and redo log files from step 2 above also need to be renamed to reflect the <NEW_SID>.

5. SET UP PARAMETER FILES FOR THE COPIED DATABASE

Edit the value of the "control_files" parameter in the "init<NEW_SID>.ora"
to be the name and location that you want to use for the new control files.
The controlfiles should be given a different name to distinguish them from the old database. In addition, change the "DB_NAME" parameter in the
"init<NEW_SID>.ora" to be an appropriate name for the new database. Any
'ifile' parameters of the parameter file will need to be edited to point
to the new name of the include file in the new location.

6. PREPARE THE 'CREATE CONTROLFILE COMMAND' FOR THE COPIED DATABASE

In order to establish the new database in the new location, the CREATE CONTROLFILE command in the file "ccf<NEW_SID>.sql" should be executed. The
following steps illustrate how the CREATE CONTROLFILE command is prepared.

The file "ccf<NEW_SID>.sql" must be edited before use. The CREATE CONTROLFILE command will be preceded by a series of comments and a STARTUP NOMOUNT command. These need to be stripped out of the file. In addition, after the create controlfile command, there will be a number of comments and the commands RECOVER DATABASE and ALTER DATABASE OPEN, which should also be stripped out, leaving just the create controlfile command itself.
The CREATE CONTROLFILE command itself should also be edited. Change the CREATE CONTROLFILE command in 'ccf<NEW_SID>.sql' to have the new database name, and add the word 'SET', e.g:

CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS

becomes

CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS

c. The CREATE CONTROLFILE command also specifies the files which

         make up
        the database, and these must also be changed to name the files
of
        the new database in the new location, e.g:

     LOGFILE
         GROUP 1 (
           'c:\old_path\old_logfile_name1',
       'c:\old_path\old_logfile_name2'
         ) SIZE 50k

        would become:

       LOGFILE
         GROUP 1 (
           'c:\new_path\new_logfile_name1',
        'c:\new_path\new_logfile_name2'
        ) SIZE 50k

        and

       DATAFILE
       'c:\old_path\old_file_name1' SIZE 5M,
      'c:\old_path\old_file_name2' SIZE 10M
       ;

       would become:

   DATAFILE
      'c:\new_path\new_file_name1' SIZE 5M,
         'c:\new_path\new_file_name2' SIZE 10M
       ;

7. USE THE ORADIM UTILITY TO CREATE THE SERVICES FOR THE NEW_SID

The "OracleService<Sid>" and the "OracleStart<Sid>" for the "NEW_SID" are
created by running the following command:

c:> oradimxx -new -sid <new_sid> -intpwd <password> - startmode auto -pfile
<path_name>

8. EXECUTE THE 'CREATE CONTROLFILE' COMMAND FOR THE COPIED DATABASE

Having prepared the create controlfile script, it is now necessary to run
the script from within the new instance. This is done by executing the
following:

At the operating system prompt, change the value of the environment variable "ORACLE_SID" from "OLD_SID" to "NEW_SID". This can be done by using the following command from the DOS prompt:

c:>set oracle_sid=NEW_SID

b. Logon to Server Manager:

SVRMGRxx

c. CONNECT INTERNAL

d. STARTUP NOMOUNT PFILE=<full path>\init<NEW_SID>.ora

d. @ccf<NEW_SID>

Note: If any files which should be specified in the CREATE CONTROLFILE

         command are omitted, these files cannot be added to the new 
         database at a later date.  In addition, if any of the files
specified
         in the CREATE CONTROLFILE command are NOT changed from their
original
         names, then the corresponding files of the original database
will
         become part of the copied database and it will not be
possible to
         restore them to the original database.  If this happens, and
if the
         files in question are important, this will mean that the
original
         database will need to be restored from a backup.

e. ALTER DATABASE OPEN RESETLOGS;

8. MAKE A FULL COLD BACKUP OF THE COPIED DATABASE

SHUTDOWN and take a full cold backup of the database in the new location.
The full cold backup can be done as detailed in steps 2 and 3. Received on Sat Jul 21 2001 - 16:36:28 CDT