23 March 2011

Steps to move db from one server to another server in SOLARIS,AIX,LINUX

Steps to move db from one server to another server in SOLARIS,AIX,LINUX :

Pre-requisites:

*OS should be same version in both servers

Step 1: Check whether source db Oracle version software is installed in target server.

 If not, install same Oracle software version in target server.

Ex: If 10.2.0.3 in Source server, install 10.2.0.3 in target server also

Step 2: Copy the following files from source server to target server

        1. control files
        2. redo log files
        3. datafiles

Step 3:
Copy init<sid>.ora/spfile<sid>.ora and orapw<sid> from source server to target server in $ORACLE_HOME/dbs folder

Step 4: Rename control file location in init<sid>.ora.

If spfile<sid>.ora is only there, then create pfile from spfile and rename then create spfile from pfile.

Step 5: In $ORACLE_BASE/admin folder create folder with name <SID>.

        Under this folder create adump, bdump, cdump and udump.

Ex: /oracle/home$cd admin

    /oracle/home/admin$mkdir <SID>

Go to folder created with SID

/oracle/home/admin/<sid>$mkdir adump,bdump,cdump,udump

Step 6: Startup mount

SQL>sqlplus "/as sysdba"
SQL>startup nomount
SQL> alter database mount;

Step 7: Modify following script to generate rename datafile:

select 'alter database rename file ''' || name || ''' to ''' || '/<datafile_dir>' || substr(name,6,length(name)) || ''';' from v$datafile;

select 'alter database rename file ''' || member || ''' to ''' || '/<datafile_dir>' || substr(member,6,length(member)) || ''';' from v$logfile;

Step 8: rename the datafile, redo log file

Run the scripts generated in Step 7.

Step 9: Rename temp file.

For renaming temporary tablespace :

select 'alter database rename file ''' || name || ''' to ''' || '/<datafile_dir>' || substr(name,6,length(name)) || ''';' from v$tempfile;

If it is Oracle 9i or less, then create new temporary tablespace.

If existing Temporary tablespace is TEMP and datafile is temp01.dbf, then create tablespace with another name :

SQL>CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/data/temp02.dbf' SIZE 500M;

SQL>alter database default temporary tablespace <new temporary tablespace name>;

Then drop the old temporary tablespace.

Step 10:

SQL>alter database open
  
Step 11:

Make necessary changes in listener.ora and tnsnames.ora and start the listener.

1 comment:

  1. This is amazing. Opened my eyes 👁️👁️

    ReplyDelete