Monday, 4 March 2013

oracle database migration by cold backup


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