Subject: HOW TO MAKE A COPY OF A DATABASE ON THE SAME UNIX MACHINE
Author: Tim Berry-Hart
Last Revision Date: 17 July 1996
HOW TO MAKE A COPY OF A DATABASE ON THE SAME UNIX MACHINE
----------------------------------------------------------
INTRODUCTION
------------
The following article deals with creating a copy of a database on the same
unix machine. A database might need to be copied in order to duplicate the
production system, for example for testing purposes. It is recommended that
the reader refer also to the following documentation:
-Oracle 7 Server Administrators Guide
Performing full backups: Chapter 18 page 18-7
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.
Note: If you are using Oracle 7.1.6 on Sun Solaris 2.X there is a serious
bug with CREATE CONTROLFILE.
1. 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.
a. SQLDBA
b. CONNECT INTERNAL, STARTUP MOUNT
c. 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
-----------------------------
a. 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. The file names will be in single quotes and separated
by commas after the words LOGFILE and DATAFILE, e.g:
CREATE CONTROLFILE REUSE DATABASE "FRITZ" RESETLOGS ARCHIVELOG
MAXLOGFILES 6
MAXLOGMEMBERS 2
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'/oracle/tberryha/fritz/log_disk1/fritzlog1v713.dbf',
'/oracle/tberryha/fritz/log_disk2/fritzlog1v713.dbf'
) SIZE 50K,
GROUP 2 (
'/oracle/tberryha/fritz/log_disk1/fritzlog2v713.dbf',
'/oracle/tberryha/fritz/log_disk2/fritzlog2v713.dbf'
) SIZE 50K
DATAFILE
'/oracle/tberryha/fritz/fritz_system/fritz_system01.dbf' SIZE 8M,
'/oracle/tberryha/fritz/fritz_data/fritz_data01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_rollback/fritz_rollback01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_temp/fritz_temp01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_data/fritz_data02.dbf' SIZE 5M,
'/oracle/d2/V7141/dbs/x' SIZE 1M
;
It is also possible to obtain a listing of the files of the database
by executing the following sql commands:
SQLPLUS username/password
Note: The user must have sufficient privileges to be able to see
the dba views 'sys.dba_data_files', 'sys.v$logfile' and in addition
the database must be open.
SPOOL files.log
SELECT file_name FROM sys.dba_data_files ORDER BY tablespace_name;
SELECT member FROM sys.v$logfile
SPOOL OFF
Note: This will create a spool file called 'files.log' which
will record the results of the previous commands.
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 can be 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
which will contain the names of the controlfiles for the database.
3. BACKUP EXISTING DATABASE
------------------------
Shutdown instance via SQLDBA, SHUTDOWN NORMAL, and then take full cold
backup of:
a. All the files identified in step 2 above.
b. All parameter files.
Note: the main parameter file will usually be called init<SID>.ora, in
addition to which there may also be 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
---------------------------
Copy all parameter files, controlfiles, and all files noted in step 1 above
to their new location taking care to preserve ownership and permissions.
When the database has been copied, it will not be possible to use the same
SID and therefore the ORACLE_SID environment variable must
be changed to a new SID, and 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).
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 CREATE CONTROLFILE command is prepared.
a. 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.
b. 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 (
'/old_path/old_logfile_name1',
'/old_path/old_logfile_name2'
) SIZE 50k
would become:
LOGFILE
GROUP 1 (
'/new_path/new_logfile_name1',
'/new_path/new_logfile_name2'
) SIZE 50k
and
DATAFILE
'/old_path/old_file_name1' SIZE 5M,
'/old_path/old_file_name2' SIZE 10M
;
would become:
DATAFILE
'/new_path/new_file_name1' SIZE 5M,
'/new_path/new_file_name2' SIZE 10M
;
7. 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:
a. 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 unix command from within the C shell:
%setenv ORACLE_SID NEW_SID
b. SQLDBA
c. CONNECT INTERNAL, 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.