oracle database migration by cold backup

March 04, 2013

Below are the all steps which should be follow from db prospective. This is done by cold backup of Oracle Database :-

On Existing Server :- Check the kernel parameters , memory , Oracle Software version ,Oracle_Base,Oracle_home, these should be same on new server .

1. Login to db as sys as sysdba .
2. select file_name, tablespace_name, status from dba_data_files;
3. select * from v$recover_file;
4. select name from v$controlfile;
5. show parameter control_file
6. Create pfile from spfile;
7. Size of the temp files :-
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
8. Group , members and Size of redo files:-
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

9. Take backup of jobs , db links .
10. Shutdown immediate
11. Cd $ORACLE_HOME/dbs and see the created newly created pfile
12. Copy all the data files , control files , password file , pfile ,listener.ora and tnsnames.ora to new location

On the Production new server :-

1. Edit the newly created pfile according to location and set the location of control file, db_domain, db_recovery_file_dest, db_recovery_file_dest_size, sga_max_size, sga_target according to server need.
2. Mount the database with new pfile.
3. Rename Data File :
Alter database rename file ‘<old_location>’ to ‘<new_location>’;

For Redo log file:

Alter database rename file ‘<old_location>’ to ‘<new_location>’;

For Temp Table space temp file :

Alter tablespace temp add tempfile ‘<new_location>’;
4. Open database with command alter database open;
5. Select * from file_name, tablespace_name, status from dba_data_files;
6. select * from v$recover_file;
7. select name from v$controlfile;
8. show parameter control_file
9. create spfile from pfile;
10. shutdown immediate;
11. startup
12. configure listener, tnsnames.ora file
13. restore jobs and dblinks according to others servers