Saturday, October 20, 2007

CLONE ORACLE DATABASE ON THE SAME UNIX MACHINE

Print this post
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. This will mean that
the original database will need to be restored from a backup.


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. sqlplus /nolog

b. SQL> connect / as sysdba

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 "FAMY" RESETLOGS ARCHIVELOG
MAXLOGFILES 6
MAXLOGMEMBERS 2
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'/oracle/tberryha/fritz/log_disk1/famylog1v713.dbf',
'/oracle/tberryha/fritz/log_disk2/famylog1v713.dbf'
) SIZE 50K,
GROUP 2 (
'/oracle/tberryha/fritz/log_disk1/famylog2v713.dbf',
'/oracle/tberryha/fritz/log_disk2/famylog2v713.dbf'
) SIZE 50K
DATAFILE
'/oracle/tberryha/fritz/fritz_system/famy_system01.dbf' SIZE 8M,
'/oracle/tberryha/fritz/fritz_data/famy_data01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_rollback/famy_rollback01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_temp/famy_temp01.dbf' SIZE 20M,
'/oracle/tberryha/fritz/fritz_data/famy_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

SQL> SELECT file_name FROM sys.dba_data_files ORDER BY tablespace_name;

SQL> 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 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
-------------------------------------------
Shutdown instance via SQLPlus, SHUTDOWN NORMAL.

Copy all parameter files, controlfiles, and all files noted in step 2 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.Ensure that the parameter DB_BLOCK_SIZE is the same as the originating database, otherwise
when creating the controlfile you may receive: ORA-01160 "file is not a %s"

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:

SQL> CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
becomes
SQL> 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
;

d. After Recreating Controlfile

o Tablespace TEMP is available
o DBA_TEMP_FILES does not show a tempfile

If using ALTER DATABASE BACKUP CONTROLFILE TO TRACE, to get a CREATE CONTROLFILE SCRIPT -
the Datafile for Tablespace TEMP (TEMPFILE) is not listed in

"CREATE CONTROLFILE STATEMENT"

You must add the TEMPFILE manaually after Creating the Controlfile

> Alter database backup controlfile to trace ;

You should check Tracefile: (in udump) to get Statement on how to Add the Tempfile to temp Tablespace

--> see end of tracefile

EXAMPLE:
========
Create Controlfile .....
...
;
...
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/U01/ORADATA/T920/TEMP01.DBF'
SIZE 10485760 REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#

Simply adjust this command (path/name) to your requirements and do for example:

> ALTER TABLESPACE TEMP ADD TEMPFILE '/U01/ORADATA/T920/TEMP01.DBF'
SIZE 10485760 REUSE AUTOEXTEND OFF;


7. CHECK REMOTE_LOGIN_PASSWORDFILE PARAMETER
----------------------------------------------------------------------
Prior to executing the create controlfile script, check whether the initialisation parameter
REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid
passwordfile should exist in ORACLE_HOME/dbs or created using orapwd, otherwise you will
receive an ora-600 [kzsrsdn:1].

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:

a. at the operating system prompt, change the value of the environment variable ORACLE_SID
from OLD_SID to NEW_SID.
b. SQLPlus
c. CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=/<full path>/init<NEW_SID>.ora
d. SQL> @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.

When recreating the controlfile, it is possible to encounter ORA-1565, ORA-1503 and ORA-9782,
"another instance has this database mounted", the ORACLE_SID of the original database is still in the
header of the first datafile of the copy and oracle checks if an sgadef<ORACLE_SID>.dbf still exists
in $ORACLE_HOME/dbs, to workaround this problem shutdown the other database of which you made a
copy prior to issueing the create controlfile command.

NOTE: If you are using online 'hot' backup at this point you should issue:

recover database until cancel using backup controlfile;

Apply all the archives you have

e. SQL> ALTER DATABASE OPEN RESETLOGS

9. 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.

2 comments:

Anonymous said...

The most appropriate solution to achieve this is through RMAN

Venkata Sathish Kadiyala said...

hey dude..its really most worthable site...keep it up..try to maintina apps cloning....other things..really good work

SathishKadiyala
Apps DBA