How to Manually Clone a Database to Another Node

posted Mar 16, 2011, 11:52 AM by Sachchida Ojha
Step by step guide about how to manually clone or restore the database to a different host.

1) Prerequisites
   ----------------
 
   - The copy of the datafiles must be done with the database closed or the database must be in ARCHIVELOG  mode,
   
2) init.ora or spfile and controlfile
  ----------------------------------------------------
   - You need to copy the init.ora or spfile file to the target host
     and locate it in ORACLE_HOME\dbs
 
  - Copy the controlfile(s), all the datafiles and all the archivelogs generated, to the target host.

    If database is open then, you need to put all the tablespaces in backup mode, before starting the copy:

        ALTER TABLESPACE <TABLESPACE_NAME> BEGIN BACKUP;

            >>> copy all the tablespace datafiles

        ALTER TABLESPACE <TABLESPACE_NAME> END BACKUP;

      ==> Do this for all the tablespces

3) Set the oracle environment
   -------------------------
 
  C:\> set ORACLE_SID=<SID>
  C:\> sqlplus "sys/manager as sysdba"
  
    - Check the init.ora  parameters that reference location
    control_files             = <duplicate db control file(s)>
    background_dump_dest      = <duplicate db bdump>
    core_dump_dest            = <duplicate db cdump>
    user_dump_dest            = <duplicate db udump>
    log_archive_dest_1        = <duplicate db arch dump location>

 
4) Set up a password file for the duplicated database
     orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<...>

5) If Windows Platform, create a new NT service for the duplicate database  using oradim.

  oradim -new -sid <SID> -intpwd <password> -maxusers 10 -startmode manual -spfile  '<spfile>'
       or
  oradim -new -sid <SID> -intpwd <password> -maxusers 10 -startmode manual -pfile  '<pfile>'

 

5)  Startup the database in mount status
 
    SQL> startup mount
    SQL> Rename any of the datafiles to the new location, if necessary:
       ALTER DATABASE RENAME FILE
                 '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
                 TO
                 '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
 
    SQL> Rename the logfiles to the new location if necessart
        alter database rename file
            '<host A location>' to '<host B location>';
     
6)  Check that all the datafiles are in the right location:
    select file#, name from v$datafile;


7) Perform incomplete recovery:
     SQL> recover database using backup controlfile until cancel;
  
    Forward the database applying archived redo log files until you decide
    to stop recovery by typing cancel at the prompt (assuming that you have
    required archived redo log files in the log_archive_dest directory)
    You may archive the source database redo log files and apply them at
    the target database if required.
  
     SQL> alter database open resetlogs;
  
8) In Windows platforms, if you want that the database will start automatically then edit the registry:
   
     regedit
       go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEX
       change the key :  ORA_<SID>_AUTOSTART=TRUE
Comments