Friday, November 11, 2005
Cloning a database.
I got asked a question yesterday which required me to revisit some old knowledge. The question was this.
I have a 9i database on server1 and I want to clone it to server2. I do have a cold backup of the database from server1 to restore to server2 but the directory structure is slightly different. In other words how do I duplicate a database from one location to another.
My initial answer was as follows.
I'd do this using the 9i RMAN duplicate database functionality since its much easier than the old way and you can pester Support if you mess it up. If you did want to do this Tim Hall has a how-to on his site and Howard Rogers has a windows example on his. The questioner though hadn't really used RMAN and wanted to know how to do this using the old fashioned approach.
A bit of memory searching later and this is what I came up with.
Backup the source control file to trace using
Hand edit the resulting trace file to create a create controlfile sql script. You'll want to make the following changes.
Change RESUSE
Change file locations as appropriate
Create a new parameter file for the database. You can do this directly from the existing spfile if you have one as follows.
Hand edit the resulting init.ora, you'll want to change all occurrences of the old database name to the new one.
Copy the password file if you are using one to a copy with the appropriate new name.
Copy the datafile backups from your cold backup. NB you don't need the tempfiles.
Set the ORACLE_SID environment variable to the new name.
If you are running on windows create the new OracleService using
Run your create controlfile script. The final steps are all shown below.
And your new database is up and running.
4 Comments
I have a 9i database on server1 and I want to clone it to server2. I do have a cold backup of the database from server1 to restore to server2 but the directory structure is slightly different. In other words how do I duplicate a database from one location to another.
My initial answer was as follows.
I'd do this using the 9i RMAN duplicate database functionality since its much easier than the old way and you can pester Support if you mess it up. If you did want to do this Tim Hall has a how-to on his site and Howard Rogers has a windows example on his. The questioner though hadn't really used RMAN and wanted to know how to do this using the old fashioned approach.
A bit of memory searching later and this is what I came up with.
Backup the source control file to trace using
alter database backup controlfile to trace;Hand edit the resulting trace file to create a create controlfile sql script. You'll want to make the following changes.
Create a new parameter file for the database. You can do this directly from the existing spfile if you have one as follows.
SYS @ train>create pfile='c:\oracle\10.1.0\db\database\initclone.ora' from spfile;Hand edit the resulting init.ora, you'll want to change all occurrences of the old database name to the new one.
Copy the password file if you are using one to a copy with the appropriate new name.
Copy the datafile backups from your cold backup. NB you don't need the tempfiles.
Set the ORACLE_SID environment variable to the new name.
If you are running on windows create the new OracleService
oradim -new -sid Run your create controlfile script. The final steps are all shown below.
C:\oracle\10.1.0\admin\train\scripts>oradim -new -sid clone -pfile 'c:\oracle\10.1.0\db\database\initclone.ora'
Instance created.
C:\oracle\10.1.0\admin\train\scripts>set oracle_sid=clone
C:\oracle\10.1.0\admin\train\scripts>sqlplus /nolog
SQL*Plus: Release 10.1.0.4.0 - Production on Fri Nov 11 09:30:36 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
@ >connect / as sysdba
Connected to an idle instance.
SYS @ clone>@createclone
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 788908 bytes
Variable Size 95417940 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes
Control file created.
Database altered.
Tablespace altered.
And your new database is up and running.
4 Comments:
At least in my environment file locations have what I consider to be a database name in them (I follow a modified OFA), and I do tend to put copied files in the new locations.
As far as cloning onto new machines goes, I guess that my way of thinking goes like this. If I am moving a database I'll do backup and restore (actually in these SAN days I might not even do that). If I am cloning a database, it is because I need to use a copy of an existing database for a new purpose, in this situation I'd want the new name to reflect the new purpose.
RMAN is of course miles better, and easier.
As far as cloning onto new machines goes, I guess that my way of thinking goes like this. If I am moving a database I'll do backup and restore (actually in these SAN days I might not even do that). If I am cloning a database, it is because I need to use a copy of an existing database for a new purpose, in this situation I'd want the new name to reflect the new purpose.
RMAN is of course miles better, and easier.
One other thing to consider is to change the dbid. When cloning a database without using rman, you end up with the same dbid for two databases. This will prevent you from registering the second database with the rman catalog. If you never intend to use rman, this is not an issue. However, since changing the dbid is a supported task in Oracle 9, why not? It can be accomplished using the nid tool that is in the ORACLE_HOME/bin directory.
Post a Comment
# posted by 
: 7:56 PM, November 16, 2005
: 7:56 PM, November 16, 2005
