I will demostarte the duplcation of an Oracle database to a different server and changing the datafile locations. In my previous blog I showed how to create a database in Windows using Oracle DBCA
The steps to clone
the database will be.Install “Oracle software only” on destination
machine if not already there. I don’t demonstrate it here.Make a RMAN backup of the source database.Copy the RMAN files to the destination machine.Restore the control file and spfile.Restore the database using different file
destination.Change the redo files destination.Change the database name.
Install Oracle
software
If you have the software 11r2(version 11.0.1 or 11.0.2) just
run setup and choose to install software only. If prompted to enter an email
address just ignore it.
Make RMAN backup of
source database.
As you can see it is real simple set oracle_sid equal to
your database SID. Run RMAN and do a clean shutdown of the database(not shutdown abort). Then do a
startup mount as we cannot make a backup with database open unless we are
running in archivelog mode but we are not. You will note that I specify format ‘c:\temp\DEMO_%U’
this tells RMAN where to make the backup and the %U are to use random names as
part of the filenames see the next picture.The backup takes just a few minutes
and are real small that is because it the default database created with DBCA.
That is how I wanted it real small and only 4 tablespaces in the backup.
Copy the RMAN files to the destination machine. Use your favoured
copy command. I used copy and paste from an explorer window. If you copy the
RMAN files to the destination machine copy to the same destination folder as
on the source machine as I assume it as such in this demo.
If you look at the explorer picture above you will notice
there are two files the bigger one the database backup and the smaller file the
control and spfile backup. You can confirm this in the RMAN picture as it tells
you what is backup and to what files are done. This may seem stupid as you
can just create the database on the destination machine but we are demonstrating how simple it is using RMAN and not using the duplicate command.
Restore the control
file and spfile.
In the picture you can ignore the ora-1078 error as it is
expected as there is no spfile. We then need to make a copy of the spfile to a pfile
so that we can edit it and change the destination of the control files before
we restore the controlfile to its new destination. Your pfile is in {ORACLE_HOME}\database in my case this is "C:\oracle\product\11.2.0.1\database" as in picture
Open this file in your editor use wordpad or notepad++ but these files are in Unix format and notepad show the contents as scrambled.
As you can see there ar meany entries in the pfile all these with the __ can be deleted I also delete some other and only left the bear minimum in the pfile. We also need to change the location of the control_files to reflect our TT location.
We need to create the directory c:\oracle\oradata\TT. If the
directory does not exist you will get the following error.
The next screen show that we have created the controlfiles successfully
as specified in the pfile.
Remember that your backup file will be different in your case as the name is randomly created when you specified %U in the backup command myne is c:\temp\DEMO_0205IL34_1_1 .
We now need to find out what the database filenames are and
change the to our TT location with other words “c:\oracle\oradata\TT”
We issue these command in SQLPLUS
sqlplus / as sysdba
column name format a50
select file#,name from v$datafile;
You will get the results more or less the same as mine. On another database there will be more files and their directories will be diffrent these are from our DEMO database.
The number infront of the filename are important and must stay the same.
1 C:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF
2 C:\ORACLE\ORADATA\DEMO\SYSAUX01.DBF
3 C:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF
4 C:\ORACLE\ORADATA\DEMO\USERS01.DBF
In this demo we going to change DEMO to TT but we could just
as well have changed it to c:\mydata\someplace\datafiles
“C:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF” >> “c:\mydata\someplace\datafiles\SYSTEMTBLSPC001.DBF”
Just remember each file and location needs to be unique for
obvious reasons
FILE#
|
NAME
|
NEW NAME
|
1
|
C:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF
|
C:\ORACLE\ORADATA\TT\SYSTEM01.DBF
|
2
|
C:\ORACLE\ORADATA\DEMO\SYSAUX01.DBF
|
C:\ORACLE\ORADATA\TT\SYSAUX01.DBF
|
3
|
C:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF
|
C:\ORACLE\ORADATA\TT\UNDOTBS01.DBF
|
4
|
C:\ORACLE\ORADATA\DEMO\USERS01.DBF
|
C:\ORACLE\ORADATA\TT\USERS01.DBF
|
We need to create a short RMAN script and run it from RMAN.
This script does three parts. 1 it tells RMAN where to restore the files to. 2
It autully reads the backup file(s) and creates and restores all files(this
script will be different if you have more datafiles) 3. This changes the
controlfile so that it knows where to find these files as where done in step 1.
run {
set newname for datafile 1 to
'C:\ORACLE\ORADATA\TT\SYSTEM01.DBF';
set newname for datafile 2 to
'C:\ORACLE\ORADATA\TT\SYSAUX01.DBF';
set newname for datafile 3 to
'C:\ORACLE\ORADATA\TT\UNDOTBS01.DBF';
set newname for datafile 4 to 'C:\ORACLE\ORADATA\TT\USERS01.DBF';
restore database;
switch datafile all;
}
My database restored without errors but now we need to tell Oracle where to recreate the redo logs. We only need to know the file name and the query to do that is.
sqlplus / as sysdba
column member format a50
select member from v$logfile;
Oracle will create the redologs later when we open the database with resetlogs command.
|
Member
|
NAME
|
NEWNAME
|
|
1
|
C:\ORACLE\ORADATA\DEMO\REDO01.LOG
|
C:\ORACLE\ORADATA\TT\REDO01.LOG
|
|
2
|
C:\ORACLE\ORADATA\DEMO\REDO02.LOG
|
C:\ORACLE\ORADATA\TT\REDO02.LOG
|
|
3
|
C:\ORACLE\ORADATA\DEMO\REDO03.LOG
|
C:\ORACLE\ORADATA\TT\REDO03.LOG
|
We use this to create a script to look as follow
alter database rename file
'C:\ORACLE\ORADATA\DEMO\REDO01.LOG' to 'C:\ORACLE\ORADATA\TT\REDO01.LOG';
alter database rename file 'C:\ORACLE\ORADATA\DEMO\REDO02.LOG' to
'C:\ORACLE\ORADATA\TT\REDO02.LOG';
alter database rename file 'C:\ORACLE\ORADATA\DEMO\REDO03.LOG' to
'C:\ORACLE\ORADATA\TT\REDO03.LOG';
The creation of the temp file is displayed in the following picture
Finally we can open our database we have to use the resetlogs option as there are no redo logs. The next command will pause for a while so that Oracle can create them if there are more than 3 log files or thay are big Oracle will take longer to create them
sqlplus / as sysdba
alter database open resetlogs;
Well if you see the above command your database have being cloned successfully.
