How to copy DBs between two servers using Hot Backup

On the source server:

1. Change the environment:
   
   setenv ORACLE_SID HOT
   
   
2. Connect as user SYS to SQL*Plus:

3. oracle@ayevenunxmavtst1:/database_jery>sqlplus 'sys as sysdba'
   
   
4. Create INIT.ORA file using the following command:
   
   SQL> create pfile from spfile;
   
   
5. Run the following script to perform the Hot Backup of the source database:
   
   
   

DEFINE BACKUP_HOME = /database_jery/HOT/hot_backup  set pages 5000  set linesize 600  set head off  set trimout on  alter system switch logfile;  alter system switch logfile;  alter system switch logfile;  spool hot_backup.sql  select 'PROMPT --> Copying '||file_name||' file ... '||chr(10)||         'ALTER TABLESPACE '||tablespace_name||' BEGIN BACKUP;'||CHR(10)||         'host cp '||file_name||' &BACKUP_HOME'||chr(10)||         'ALTER TABLESPACE '||tablespace_name||' END BACKUP;'  from dba_data_files  order by tablespace_name, file_name  /  spool off  @hot_backup.sql  alter database backup controlfile to trace;  alter system checkpoint;  alter system switch logfile;  alter system switch logfile;  alter system switch logfile;

6. Go to user_dump_dest directory (/oracle/admin/HOT/udump) and copy a new trace file to the BACKUP_HOME directory(/database_jery/HOT/hot_backup)
   
   cd /oracle/admin/HOT/udump
   cp hot_ora_26979.trc /database_jery/HOT/hot_backup/control.sql

7. Copy all the database file to the Target Server:
   
   cd /database_jery/HOT/hot_backup
   scp * oracle@ayevenunxmavtst2:/database_tom/HOT/
   
   cd /database_jery/HOT/archive
   scp * oracle@ayevenunxmavtst2:/database_tom/HOT/archive/
   
   

cd /oracle/ora92/dbs
scp initHOT.ora oracle@ayevenunxmavtst2:/oracle/ora92/dbs/

On the target server:

1. Change the environment:
   
   setenv ORACLE_SID HOT
   
   
2. Go to /oracle/ora92/dbs/ and change all the appearances of database_jery to database_tom in initHOT.ORA file.
   
   
3. Create the password file in the same directory:
   
   orapwd file=orapwHOT password=man entries=10
   
   
4. Create the following directories on the Target Server:
   
   mkdir -p /oracle/admin/HOT/udump
   mkdir -p /oracle/admin/HOT/bdump
   mkdir -p /oracle/admin/HOT/cdump
   mkdir -p /database_tom/HOT/archive
   
   
5. Run the following script in SQL*Plus:
   
   
   

oracle@ayevenunxmavtst2:/database_jery>sqlplus 'sys as sysdba' SQL> STARTUP NOMOUNT  ORACLE instance started.  Total System Global Area  202869048 bytes  Fixed Size                   731448 bytes  Variable Size             167772160 bytes  Database Buffers           33554432 bytes  Redo Buffers                 811008 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "HOT" RESETLOGS  ARCHIVELOG   2  --  SET STANDBY TO MAXIMIZE PERFORMANCE   3      MAXLOGFILES 5   4      MAXLOGMEMBERS 3   5      MAXDATAFILES 100   6      MAXINSTANCES 1   7      MAXLOGHISTORY 226   8  LOGFILE   9    GROUP 1 '/database_tom/HOT/redo11.log'  SIZE 20M,  10    GROUP 2 '/database_tom/HOT/redo21.log'  SIZE 20M,  11    GROUP 3 '/database_tom/HOT/redo31.log'  SIZE 20M  12  -- STANDBY LOGFILE  13  DATAFILE  14    '/database_tom/HOT/system01.dbf',  15    '/database_tom/HOT/undotbs01.dbf',  16    '/database_tom/HOT/indx01.dbf',  17    '/database_tom/HOT/tools01.dbf',  18    '/database_tom/HOT/users01.dbf'  19  CHARACTER SET WE8ISO8859P1  20  ; Control file created. SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; ORA-00279: change 260198 generated at 03/23/2008 08:44:06 needed for thread 1 ORA-00289: suggestion : /database_tom/HOT/archive/1_24.log ORA-00280: change 260198 for thread 1 is in sequence #24 ORA-00278: log file '/database_tom/HOT/archive/1_24.log' no longer needed for this recovery ORA-00308: cannot open archived log '/database_tom/HOT/archive/1_24.log' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL; Media recovery cancelled. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/database_tom/HOT/temp01.dbf' SIZE 100m; Tablespace altered.