Cloning the database to another server with raw data files

Question

I'm trying to clone the database of server 1 to server 2 with raw data files. I'm using Linux 7 and Oracle 11g R2. I have the instance running with same SID in Server 2 but file structure is different. The database is about 85 GB. I have got the data files, control file, redolog files, spfile and init.ora. Can someone explain me the way to clone in this way ?

Answer 1

One can use rman to duplicate a database. Here is the Database Backup and Recovery User's Guide.

But now let's assume that you have a copies of these files that where made when the database was down and where already copied to the places of the target system.

On the target server 1 you have the same type of perating system as on the source server 1. On the target server you have the same Oracle version with the same patch level as on the source server. You can check this by setting the oracle environment and executing the command

$ORACLE_HOME/OPatch/opatch lsinventory

and compare the output of both servers.

If you don't have the right environment you can clone the Oracle software from the source server using Oracle Universal Installer.

So let's assume that you have the correct software installed and set the correct environment of the Oracle instance of the target server.

what happens if you do a startup nomount of the database in sqlplus?

sqlplus first searches for

${ORACLE_HOME}/dbs/pfile${ORACLE_SID}.ora

if such a file does not exist it searches for

 ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

and oracle is started with the parameters given in this files. I am not 100% sure about that. Maybe sqlplus starts oracle executable and the oracle executable searches for these files. But that does not matter for our purposes.

So the parameters of these file should have sensible values. This means, the pathes in these files should point to the correct files.

If not, you have to change them. The init.ora file can be modified with a text editor. An spfile must be transformed to a init.ora file by executing

create pfile='/tmp/myfile.txt' from spfile='&absolute_spfile_path';

in sqlplus while the instance is not running. &absolute_spfile_path must be substituted by the correct path.

then you can edit the file /tmp/myfile.tx with a text editor. You can change the pathes, if an instance name is in the file it must be modified, too. Memory parameters can be adapted. Underline parameter written by the instance should be removed, e.g. '_sga_target' and similar parameters.

and then create a modified spfile by issuing

create spfile='&absolute_spfile_path' from pfile='/tmp/myfile.txt';

Before you start you can make a copy of your files so that you can start again when something went wrong.

Make a copy of the init.ora and spfile.ora, the control files, the redo log files and the datafile of th SYS, SYSAUX and undo tablespaces. the datafile of the remaining tablespaces make readonly with the appropriate Linux command(chmod) so they can't be modified by oracle. Tempfiles are not needed from the source database.

Now you can start the instance with

startup mount

check the alert log if everything is ok. If there are errors then repair tthem.

If everything is fine then mount the dtabase

alter database mount;

Now the control file is read. You can rename all files now. In the control file their old name is stored. you can see this if you do a

select name
from v$datafile;

issue an

alter database rename file '&old_name' to '&new_name'; 

until all datafile are removed.

The same command can be used to rename the log files.

select member
from v$logfile;

shows you the names of the redo log files in the controlfile.

You can put the tempfiles offline to avoid eror messages when openinf the database

select name 
from v$tempfile;

shows you then namesof the temp files.

alter database '&tempfileName' offline;

puts the file offline.

Now you can open the database

alte database open;

Now add new temp files with correct path and size to your temp tablespaces.

alter tablespace TEMP add tempfile ...;

and then remove the old ones

alter tablespace TEMP drop tempfile ...;

If you have made the daatafiles read only then shutdown the database, make the files read/write and start the database again.

Now you are done.

Answer 2

As a side note here, in case you want to copy all the files, it is not a "must" to stop the DB, you can use :

alter database begin backup;

{copy datafiles}

alter database end backup;

This way you will have the DB available and you will be able to copy the datafiles and clone in the way it was explained before.